SQL Examiner Suite 2016Current version: 220.127.116.11
Compares and synchronizes both the database schema and its contents
Yes, SQL Examiner accesses command line interface and allows to automatically perform any actions available in the GUI. You may look through the “Command Line Tool” section of the Help file to learn how to specify the necessary comparison and synchronization options in the command line. To get the full list of all comparison and synchronization options, look though the “Command Line Utility Options” section. To schedule automatic comparison and synchronization of your databases, please use the built-in SQL Examiner Command Line Comparison and Synchronization tool and Windows Scheduled Tasks (Windows Scheduled Tasks may be found in “Control panel” » "Scheduled Tasks" of the Start menu).
Yes, SQL Examiner 2016 works with ALL SQL Server versions from 7.0 to 2014. You can even synchronize databases that are on different versions of MS SQL Server, e.g. one on SQL Server 2005, and the other on SQL Server 2008. And of course, the tool works with all MS SQL Server editions without any restrictions.
You can be absolutely sure that no data will be missed or lost on the stage of either database comparison or database synchronization. You can lose data only when you deliberately make such changes in the database schema. For example, if there is some column in one of the tables absent in the source database and it is present in the target database, this column will be removed along with all the values in it in the process of synchronizing databases. Anyway, you can protect yourself against any data loss if you select the «Create backup of target database» option at the first step of the synchronization wizard.
It happens if you select the «Keep column order» option at the database comparison stage. This option is enabled by default. If the order of columns is not important for you in the process of the database comparison, disable this option on the Comparison options tab of the Project Settings dialog box.
You have the «Synchronize dependent objects» option enabled. In your case it means that during the synchronization process it is also necessary to synchronize database dependent objects. If you want to synchronize only the objects you select, clear the «Synchronize dependent objects» checkbox either in the Options dialog box or in the Synchronization dialog box.
You have the «Ignore comments» option enabled. In this case SQL Examiner does not take into account comments while comparing stored procedures and functions. If you need text objects to be completely identical (including comments), disable this option on the Analyzing/Comparing » Comparison options tab of the Options dialog box. The «Ignore tabs, spaces, etc.» option is usually used together with it.
Of course, it is. To do it, just enable the Ignore schemas (owners) option in the Project Settings dialog box. But keep in mind that this database comparison mode cannot be used in case you have objects with the same name, but with different owners in one database.
Yes you can avoid taking these objects into account during the database comparison by specifying the necessary «Ignore indexes», «Ignore foreign keys», «Ignore check constraints», etc. options on the Exclusions tab of the Project Settings dialog box.
Users' passwords are confidential information and SQL Server does not allow the program to get them and move them between databases. In the process of synchronizing users who use SQL Server Authentication, you will see a warning at the Warnings step of the synchronization wizard that it is impossible to move passwords and you will have to specify them manually. Otherwise an empty string will be used as a password. Or you can save the synchronization script and enter passwords for all users manually before executing it in Microsoft SQL Server Management Studio.
Most often, an attempt to synchronize databases automatically in this case will result in an error, but SQL Examiner detects such a situation and offers you possible ways of solving the problem. For example, if the source table contains a column with the NOT NULL limitation and there is no such a limitation in the target table, you will see a warning that automatic synchronization can result in an error at the second step of synchronization. In this case you can select how to synchronize this column to avoid any errors: you can specify the default value, synchronize it as a column allowing NULL values, use the script to fill the added column or you can avoid adding the column during the synchronization process at all.
You will not have to waste your time on it. If you select the Validate Data option at the first step of the synchronization wizard, SQL Examiner will analyze existing data during the synchronization without additional questions and will warn you only if it is actually impossible to modify the schema without your participation. But mind that in this case the process of generating the synchronization script will take additional time to analyze the existing data (for example, if you add the UNIQUE limitation, the program will have to check if the current data correspond to the new limitation and it may take quite a lot of time if there is a lot of data).
SQL Examiner analyzes the structure of tables during the database comparison and if it detects that columns could have been renamed, you will see the corresponding warning. In this case you can specify the renamed column and the commands for renaming will be included into the synchronization script.
Both variants are absolutely safe, but if you execute it in SQL Examiner, you can ignore a non-critical run-time error, even though it is not recommended. Suppose you synchronize 200 stored procedures, among which there are 10 procedures that have not been used for a long time and they refer to columns that do not exist. Then, if SQL Examiner displays a warning that such a procedure cannot be created, you will not have to interrupt the synchronization, remove this procedure from the list of the synchronized ones and start the synchronization again (besides, you will probably have to repeat these operations 10 times for our example), you can just click "Ignore" after you carefully read the warning message.
SQL Examiner allows you to compare and synchronize SQL database schemas, not data. If you need a software tool that enables data migration, you can use SQL Data Examiner. Also you should learn about SQL Examiner Suite: it allows you to purchase both SQL Examiner and SQL Data Examiner to cover all comparison and synchronization needs and provides with a $100-$500 discount off the total costs.
All synchronization operations, except operations with users and roles are inside one transaction and all changes are automatically rolled back in case of an error.
Beginning with the version 2.0 you can filter objects out of the comparison. Run SQL Examiner and create a new comparison. Check the "Configure advanced settings" option and click "Next". Then select "All but selected objects" in the drop down list and check the objects you want to exclude from the comparison. Or, if you need to compare/synchronize a small number of objects, select "Selected objects only" and check the objects you want to compare. After you’ve done the necessary settings, click "Compare". When the comparison is complete, you’ll be able to access these settings through Tools » Project Settings. Alternatively, you may click objects in the list of objects (in the main window) to filter them out of the comparison and then click "Exclude object from this view" in the context menu.
Yes, SQL Data Examiner accesses command line interface and allows to automatically perform any actions available in the GUI. You may look through the “Command Line Tool” section of the Help file to learn how to specify the necessary comparison and synchronization options in the command line. To get the full list of all comparison and synchronization options, look though the “Command Line Utility Options” section. To schedule automatic comparison and synchronization of your databases, please use the built-in SQL Data Examiner Command Line Comparison and Synchronization tool and Windows Scheduled Tasks (Windows Scheduled Tasks may be found in “Control panel” » "Scheduled Tasks" of the Start menu).
Yes, SQL Data Examiner 2016 works with ALL SQL Server versions from 7.0 to 2014. You can even synchronize databases that are on different versions of MS SQL Server, e.g. one on SQL Server 2005, and the other on SQL Server 2008. And of course, the tool works with all MS SQL Server editions without any restrictions.
In the synchronization process, there could be a temporary interruption of data integrity – for example, there could be a conflict with the foreign key. To avoid this problem, select the Disable Foreign Keys option: foreign keys are automatically disabled at the beginning of synchronization and automatically enabled when the process is finished.
On the second step of synchronization deselect the Use transaction option. Then any changes are made within an individual database transaction. Still, the referential integrity of the data can be broken if synchronization is interrupted.
On the second step of the comparison, use the Columns drop-down list to deselect the columns you do not need compare.
To write such a script you must first create the same tables in a target database (you can use SQL Examiner). Then compare the target and the source databases and save the script.
Yes, you can. SQL Data Examiner allows you to specify any key column to compare tables. The key column doesn’t even have to be a UNIQUE index or a UNIQUE constraint, but in this case the data in the columns should be unique. Also you can specify a composite key – i.e. a key composed of two or more columns. (The values of the specified columns don’t have to be unique, but the combination of the columns should be unique.) If the program finds any duplicated key values in the process of data retrieving/comparison, it interrupts the process and displays a warning.
On the second step of the comparison, in the Table list, the program displays only those tables that exist in both databases and have the same columns. That’s why there are usually fewer tables in the list than exist in the table.
By default, the program uses the primary key constraint as the key column if the constraint exists in both databases. When there is no primary key, the program selects any unique index it comes across. If there are no primary keys or UNIQUE indexes, then no key column is specified.