SQL Examiner Frequently Asked Questions
Q: Can I setup SQL Examiner so it runs completely automatically?
A: 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).
Q: What versions of SQL Server does it support? Does it work with SQL Server 2008?
A: Yes, SQL Examiner 2008 works with ALL SQL Server versions from 7.0 to 2008. 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.
Q: Is the code that synchronizes databases safe enough and can I be sure that I will not lose data during the synchronization process?
A: 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.
Q: SQL Examiner detects that two tables are different after the database comparison and offers me to synchronize them though I changed only the order of columns. How can I avoid it?
A: 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.
Q: I select one stored procedure for synchronization, but there appear commands for changing several tables and stored procedures in the synchronization script. Why?
A: 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.
Q: There are two stored procedures in the compared databases and I added comments to one of them, but SQL Examiner marked this pair of procedures as identical. Why?
A: 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.
Q: In one of the compared databases all objects are created by the user named dbo and in the other database they are created by another user. Is it possible to make SQL Examiner recognize dbo.Table1 and user1.Table1 as one and the same object?
A: 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.
Q: Can I compare databases without taking indexes and constraints into account so that SQL Examiner does not offer me to synchronize tables with the same set of columns as different after the comparison?
A: 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.
Q: I synchronized users in two databases, but the added users cannot use their old passwords to access the modified database. Why?
A: 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 Query Analyzer.
Q: It is far from always that you can synchronize tables automatically. For example, it is impossible if you need to add a column with NULL insertions prohibited to a table. How can SQL Examiner help me in this case?
A: 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.
Q: And what if there are no records in the table a column that does not allow NULL values is added to? Why should I waste time on the problem that does not exist?
A: 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).
Q: And what if I rename a column in one of the compared tables? Will SQL Examiner be able to detect that it is the operation of renaming (and not removing the column from one database and adding it to the other one)?
A: 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.
Q: Where is it better to execute the synchronization script: in Query Analyzer or in SQL Examiner?
A: 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.
Q: Can I compare data with SQL Examiner?
A: 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.
Q: What will happen if there is an error during the database synchronization process?
A: 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.
Q: How can I exclude unnecessary objects from the comparison?
A: 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.
Please also have a look at our How To Guides
section for useful tips and articles.
|
