Database tools, both SQL tools and Oracle tools, appear to be abundant in the marketplace today, so much so that one gets quickly overwhelmed when trying to decide on what the best tool for the job is.
Every developer claims to offer the best sql compare tool, the best oracle compare tool, the best data compare tool, the best monitoring tool, the best search tool and so on, however, when you venture one step further and try to learn more you are bound to be disappointed.
What you find is abandoned products that have not been updated in the last 10 years, products that do not work at all or ones that only work for very simple scenarios, websites that provide no way for you to contact anyone, contact emails you never get a response from etc. However, if you are reading this, we assure you that you have come to the right place – you will love our database tools, we guarantee it!
What are the database comparison tools?
There are two main “classes” of database compare tools: 1) schema compare tools; 2) data compare tools.
They can be used together as in the case of database migration or separately depending on the use-case with which you are presented.
Schema comparison tools
Before we jump into explaining what a database schema compare tool is supposed to do, let us clarify the word “schema” used in this context.
Most of you, having reached this page, are likely familiar with the term however, some of you may be more familiar with the term “database structure” or something else.
In one sentence we could define the term “database schema” as the complete set of formal definitions of all database objects, rules, constraints, permissions etc.
As a software / database developer you have experienced firsthand the arduous and risky process of making database schema changes.
As you are programming your software changes you need to create a new table in the database, add a column to an existing table, change the type of a column or just the length, add a constraint to ensure data integrity, create a new database view or update an existing one, create a new stored procedure or update an existing one, create a user-defined function, change user permissions, etc.
Now you are ready to publish the new version of your application and you know that in order for your new version to work properly all the database changes must be published at the same time.
While publishing your application is a painless click that wipes out the old and replaces with the new, the publishing of the database changes is a different story, you can’t just replace the old with the new, you have to make sure that:
In order to ensure the above you will need to prepare a database change script and submit that to the DBA in charge.
Anyone, who has tried to create those scripts manually can attest to the exponentially increased complexity and pain as the number of database changes increases.
That is where a database schema compare tool comes in – if designed and built properly, it makes the deployment of the database schema changes painless, fast, and safe.
- You do not accidentally delete any data from the database
- You do not forget any database changes that you made during the development as those will cause your new code to fail
- You make the database changes in a certain order that respects the database object dependencies otherwise the changes will fail
How do database schema compare tools work? In essence, all the database schema compare tools work, or should work, the same way, that is: compare the source database schema with the target one, identify the differences, build an object dependency tree, generate a safe schema change script that:
Important note: In principle the database schema compare tools can be dbms agnostic, so one cannot be blamed for assuming that a universal schema compare tool could work for all database management systems, be it SQL Server, Oracle, MySQL, DB2 etc., however, as you know, database schemas are very complex and despite the commonalities, very different from one dbms to another, so be aware of tools that claim to support multiple dbms-es.
Of course, there’s probably some use for such tools but to achieve the goals stated in the above description dbms specific tools are necessary.
That is why we offer the Schema Compare for SQL Server and the Schema Compare for Oracle Databases.
- Ensures preservation of the integrity of the database – either all changes are successfully deployed or no changes are made to the target database;
- Ensures no un-intentional data loss
- Sequences the changes respecting the dependency chains
- Is version specific, that is the change script is compliant with the version of the target database
Data comparison tools
Database data compare tools are generally designed to compare the data stored in two databases, generate a safe script to update the target database with some or all the changes, and execute the change script on the target database.
Sounds very simple, right? That is why everybody and their dog have jumped into building a data compare tool, but as always the devil is in the details and that is why most of the database data compare tools are half-baked and generally a waste of time.
The biggest obstacle that is overlooked has to do with size – even a moderate size database will expose many of the available tools as useless, they simply cannot handle any large tables, either the tool crashes or the machine runs out of resources.
Then come the details, can they compare and synchronize views, memory tables, columnstore tables, system-versioned tables, stretched tables, graph tables?
Can they properly handle foreign key constraints? Can they compare databases with different collations?
Do they appropriately handle spaces, approximate numbers, character case? Can they show row differences for large tables without crashing the machine?
Can they handle the execution of large sync scripts? The answer for most of the tools in the market today is NO, they cannot!
Our Data Compare for SQL Server and Data Compare for Oracle Databases do all the above and more.
Fast, simple, robust – they get the job done and come with unparalleled support provided by our team of experts.