FAQs - ApexSQL Diff

No - ApexSQL Diff is designed to work exclusively with Microsoft SQL Server. Supported SQL Server versions are SQL Server 7.0, SQL Server 2000 and SQL Server 2005 .

ApexSQL Diff 2005 has been completely re-engineered to allow to compare and synchronize the most of new SQL Server 2005 objects, including XML schema collections, DDL Triggers, Message types, Contracts, Services, Routes, Event notifications, Queues, Synonyms, Partition functions, Partition schemes, Service binding, and the new datatypes (for example, VARCHAR(MAX)).

Yes. ApexSQL Diff works seamlessly with all supported SQL Server versions.

Project files are used to store all information that ApexSQL Diff needs to perform a complete comparison and generate a synchronization script. A project stores server and database connectivity information, all comparison options, object selections (including wildcard-based object filters), results filters, and sync script options.

The project usage allows choosing the comparison and synchronization options once, and reusing them from that point for future comparison or synchronization.

Projects can be opened and processed immediately via the GUI, or edited at any level using the drill-down wizard tree.

ApexSQL Diff allows the user to create snapshots of databases for off-line comparison. A snapshot is a binary file that contains all information about the database structure at a given point of time. You can take snapshots only of a databases' structure - tables and/or views data cannot be stored in a snapshot. You can compare snapshots with live databases in any possible combination.

ApexSQL Diff automatically ignores all properties/attributes unsupported by SQL Server 7.0, like collations, index column sort orders and CASCADE foreign key attributes. But a better way to create SQL Server 7.0 compatible scripts is to compare your database with another SQL Server 7.0 database or a compatible one. In addition you may still need to manually modify generated scripts to remove references to, for example, ascending indexes and computed columns containing user-defined functions.

To be able to roll back changes in case of script errors you should check the Include Transaction Handling option in the Synchronization wizard General Options Tab. This will force ApexSQL Diff to place the script within a transaction to take advantage of SQL Servers' own transactional handling features.

That means, that if an error occurs during a synchronization script execution, ApexSQL Diff rolls back any changes that were made by the script and parses all subsequent statements. The only exception from this rule is in regard to changes in users and roles, since they can't be synchronized within a transaction.

Additionally the Backup of Altered Database option allows restoring to the most recent state if a critical error occurred.

Yes. You may save these settings to a project file, and then choose this project file at the comparison wizard startup. These stored settings can be re-used in Graphical mode as well as in Command Line Interface mode.

No. The user-created objects from these databases can be compared and synchronized but the system objects can be only compared. Use Show System Databases option to show the system databases in the list of the available databases.

It depends upon the process itself. During backup operations, you can compare and synchronize your databases (some operations are restricted by the server there). However, this practice is not recommended because the different processes might slow-down each other and probably cause time-outs. During a restore of your database, you won't be able to make any operations against your database, as only one connection to that database is allowed during this time. Data comparison and synchronizing (structure and data) won't work there, however structure comparison will work.

ApexSQL Diff can provide a post-synchronization summary of all the actions performed by the script, including any errors encountered (when you run the script from the ApexSQL Diff's internal script editor). Before creation of synchronization script the pre-synchronization report is displayed with the full list of the operations being performed by the generated script.

We are currently working on this feature. Currently ApexSQL Diff does not support the decryption of encrypted objects.

ApexSQL Diff has been designed with safety in mind. But in order to have a maximum level of safety, follow these steps:

1. Make sure the Transaction Handling option of the Synchronization Wizard is turned ON.
2. The Backup of Altered Database option should be set to either Use Default Backup Directory or Use This Directory value. This will crate a backup immediately before the synchronization is performed.
3. Set the altered database to Full Recovery Mode. This will allow using recovery tools like ApexSQL Log if any data is lost.

The backup location is dependent on the Backup of Altered Database option of the Synchronize Wizard. If this option is checked, a backup of the database is performed immediately before the synchronization script runs. If Use Default Backup Directory is selected (this is the default setting), the backup is created with a name like "ApexSqlDiffBackup1212007629PM.bak" and stored to the default database backup directory (for example, "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP"). If Use This Directory option was selected then backup is stored to the path you've provided.

Compare It! is no longer needed as an external program in ApexSQL Diff since it is now incorporated into ApexSQL Diff directly. Thus we no longer distribute keys for Compare It! If people still want to use a viewer they can purchase any available, such as Compare it!, Beyond Compare etc. and configure ApexSQL Diff to make external calls to it - but now it is no longer necessary.

You can compare objects owned by different owner. All you need to do, is to map these owners at the Owner/Schema mapping page of the Structure Comparison Wizard. That's it! Now you can compare these objects.

The panel you have noticed serves as indication of the differences that were met during the object comparison.

Difference Codes are the indicators that ApexSQL Diff uses to identify the specific types of differences between objects after performing a structural comparison. All of them are shown at this Difference panel on the Main Grid. You are able to filter the objects by these codes.

When this option is selected, the Ignore Comments comparison wizard option allows to ignore comments in script-based objects. The Ignore White Space option allows you to ignore the differences in white spaces (carriage return, linefeed, tab and space-formatted text) when performing comparisons. We recommend that when you set the Ignore Comments option then the Ignore White Space option should be checked as well in order to avoid false differences in white spaces after removing comments from the object bodies.

This is default behaviour if you uncheck the Ignore Column Order option at the Ignored Table Only option page of the Comparison Project Wizard. This option is checked by default, since the column order shouldn't matter anyway.

If the order of columns is not important for you in the process of the database comparison, check this option.

When comparing an SQL Server 7.0 database with either another SQL Server 7.0 or an SQL Server 2000 databases, ApexSQL Diff automatically ignores all properties/attributes not supported by SQL Server 7.0. So, collations aren’t compared in this case.

Yes, you can filter these objects, either by the object types, difference types and difference codes.

To filter out objects of some type un-check the corresponding buttons on Object Filter Toolbar, each button has an icon indicating the object type that it’s related to and a tooltip hint so you can easily find the appropriate ones. The Difference Status filter is comprised of the 4 rightmost buttons on the Object Filter toolbar. It allows you to show identical, different, master-only and target-only objects in any combination.

The Difference Codes filter is located on the Difference Codes pane on the right–hand of the comparison result grid. This filter allows you to filter the objects by the certain types of structural differences.

Yes. The ApexSQL Diff Structure Comparison Project wizard allows you to set two replication related options:

1. Ignore Replication Triggers – allows ignoring those triggers that were created by SQL Server for replication purposes.
2. Ignore NOT FOR REPLICATION Attribute of Constraints – allows ignoring the NOT FOR REPLICATION argument on constraints.

You may uncheck all unwanted objects on Object Filter page of the Wizard. First check Use Filter box next to the desired object type and then click to the appropriated sub-node in the Treeview. On that page you may select/deselect individual objects for comparison.

Snapshots are binary files of a databases' metadata that can be used for offline/disconnected database comparisons. Snapshots can be compared either with live databases or with another offline snapshot. Note that snapshots usage is allowed only for structures difference comparisons, and not for data comparison and synchronization.

That is evaluated on a case by case basis. Only in cases where the structure is so different that the changes cannot be made via ALTER TABLE statements. If changes can be implemented via an ALTER TABLE statement, changes can be made unobtrusively as in the case where a column is simply renamed. ApexSQL Diff also uses ALTER TABLE statements to synchronize the data types of different columns with the compatible data types. Therefore, if it is possible to synchronize the columns with ALTER TABLE statements, ApexSQL Diff will do so. If it is still necessary to recreate a table in order to synchronize it, ApexSQL Diff stores its data in a temp table and restores the data from there to the new table after recreating it.

No, it doesn't. For time being, there is no possibility to synchronize the diagrams.

Yes, you can. Use Ignore Indexes and Ignore Check/Default Constrains options at Ignored Table Only option page of the Structure Comparison Wizard.

Most likely, you have checked the Ignore Comments option (see Ignored - General option page of the Structure Comparison Wizard). In this case ApexSQL Diff does not take into account comments while comparing stored procedures and functions. If you need such textual objects to be completely identical (including comments), uncheck this option.

Using of DROP/CREATE statements instead of ALTER is determined by ApexSQL Diff itself. Possible reasons of tables re-creating are:

1. There are differences in the file groups.
2. ANSI options specified on object creation are different.
3. After removing/adding of new columns into a destination object this object contains no columns with data anymore.
4. There are destination columns, which must be re-created (for example, in case of incompatible data types in source/destination columns with the equal names).
5. There are differences in the identity columns.
6. The column order in source/destination objects is different.
7. There are XML columns having the differences in XML schema collections.

You can always see the Warnings tab of the Synchronization Wizard at the Script Result step. This tab shows the possible reasons of such cases.

Yes, the ApexSQL Diff will compare tables with different structure. If it is necessary to compare the columns with different names then these columns needs to be mapped before the comparison.

It is possible to include or exclude referential integrity support by using the option "Disable Foreign Keys and Triggers" in the Database Configuration page of the Data Synchronization Wizard.

In data comparisons, ApexSQL Diff divides all compared data records into one of these three categories. 'Missing' rows are those rows that exist only in a source table/view, while 'Different' rows exist only in a destination table. 'Different' rows are those that have been found to exist in both tables, but are different in some specific values in certain columns. A row is found to be different if the comparison keys for the records are the same, but individual data differences exist beyond that. 'Additional' rows are those rows that exist only in the destination table/view.

ApexSQL Diff is restricted by the maximum number of objects in an SQL Server database. However, comparing/synchronizing huge databases will require substantially more time to finish and many resources (server, network and workstation). In such cases it might be a better approach to split the comparison into smaller subprojects each with a limited comparison range.

For this purpose you can:

1. Filter the objects by their object type (Comparison Wizard -> Object Filter).
2. Select the individual objects for comparison (after Object Type Filter being set, the sub-nodes that were added to the Tree-view allow to select individual objects of the appropriate type).
3. If it is necessary to compare and synchronize data in large tables then it might be an option to use the rows filter (specifying a subset of rows by using a WHERE clause) to limit the number of rows to be loaded at the same time. It can substantially save the time and resources.

All these actions can have a positive effect on the duration and the used resources during a comparison process.

Yes, you can. To check a subset of rows for synchronization, after comparison select in the comparison results grid the desired table. After table selection the result pane displays the rows with comparison results for this table. To select the individual row mark the left-most checkbox of the row.

Yes, you can. You can compare/synchronize data in multiple tables at once. Simply choose the desired tables in the Data Comparison Wizard.

ApexSQL Diff offers this feature in Data mode only. Use CSV Data Export command to save the difference to a flat file.

Yes. To compare data in two tables in the same database you need to select this database at the Login step of Comparison Wizard as both Source and Destination data source. In the Data Tables sub-node all tables are displayed as being "mapped". Select the tables that you want to compare and "un-map" them by clicking on the "Unmap" button for each of table. These tables are moved to the un-mapped tables and may be re-mapped with each other. Then re-mapped tables can be compared and synchronized.

When a primary key or unique identifying key exists in both tables then it is automatically considered the comparison key. In case of the absence of such a key the composite key consisting of all the columns (according to the Ignore options) of the table is automatically used.

Yes, you can. The Comparison Key can include the columns specified by the user and should be composed in a way to provide a unique identification of data rows.

Yes, you can do that in the different ways:

1. Enter a WHERE clause condition in the Data Comparison Wizard on the Object Filter page.
2. Choose the rows to be compared in the Data Comparison Wizard filtering them by their status: Identical, Different, Missing or Additional.
3. Check the rows of your tables to be synchronized in the results pane below the Main Grid.

Yes. ApexSQL Diff offers a powerful Command Line Interface that allows you to specify all comparison and synchronization settings through Command Line Interface options. To run ApexSQL Diff in command line mode call the "apexsqldiff.com" or simply "apexsqldiff" executable with options. To get information about the available command line options use "apexsqldiff /?" or "apexsqldiff /help" command.

Yes you can. The /sync option will allow running synchronization script after its generating.

For example:

apexsqldiff /s1:SRV1 /s2:SRV2 /d1:DB1 /d2:DB2 /sync

will create T-SQL script for synchronizing the structure of the databases DB1 and DB2 and execute this script.