Using ApexSQL Diff in Data Mode
One of the key advantages of ApexSQL Diff is that you can not only compare the structures of your databases, but also the data itself in these databases. It therefore combines the best of both worlds as you don’t have to use two different tools, but just one with a consistent GUI.
To switch ApexSQL Diff from Structure to Data Mode, you just have to select the Data group in the Outlook-like taskbar.
Most of the functionality when using ApexSQL Diff is the same as in Structure Mode, so I will focus here just on the main differences. At first it is worth noting that you can decide whether to include “special” data types such as UNIQUEIDENTIFIER, Blob (TEXT, IMAGE, VARCHAR(MAX), and VARBINARY(MAX)) in a comparison or not.
One of the most interesting features when using ApexSQL Diff in Data Mode is its ability to map tables and columns within these tables. To explain what “mapping” in ApexSQL Diff terms means, let’s consider the following scenario:
Typically you compare almost identical databases. Meaning you have a development database that is a more or less exact copy of the production database. You then make your changes to the development database and deploy them after a testing phase to the production database. In this case ApexSQL Diff can automatically map tables since it assumes tables of the same name to have the same meaning. That is, a table “Orders” is very likely to have the same meaning in both databases. This automatic mapping is done by default.
Now, when table names in Source and Destination do not match, that can, amongst other reasons, either mean that the tables do not exist in one of these databases or that tables with the same meaning do exist in both database, but they differ in their names.
Look at the screenshot above. The upper – empty – grid displays mapped table objects. The lower grid displays unmapped objects. Let’s have a closer look at this lower grid:
As you can see there are 4 table objects in the Source schema and 1 in the Destination schema. And as you can further see the tables “MyTable” and “MyTablee” are likely meant to be the same with the exception of the typo. It would be quite easy to rename one table to match the other table name and thus ApexSQL Diff would automatically be able to map them, however I’ll show you that this isn’t actually necessary, although probably desirable. Let’s map “MyTable” with “MyTablee”
To map both tables, just select its counterpart in either the Source or Destination schema name column. In my example I have chosen to select “MyTablee” in the dropdown box of the Destination schema name column.
Once this is done, just click on the Map button to establish the connection.
ApexSQL Diff instantly moves the mapped table pair to the mapped objects grid.
In an ideal world we could now leave things as they are as we assume the table name typo to be the only difference. Given this holds true, ApexSQL Diff would do the rest for us and map the columns in Source and Destination based on their names. Unfortunately it isn’t an ideal world in which we can rely on the column names to be identical in both tables. Before getting into any trouble, we can take action here, and instruct ApexSQL Diff which columns to be matched with each other.
When you scroll the horizontal scrollbar a bit to the right, you will find the “Columns” column. From the dropdown box you can open the dialog in which you can map columns:
Just select the columns to be mapped from the dropdown box and you’re done.
The final result of a column mapping might look like the one in the screenshot above.
Now, the rationale behind the whole mapping thing is to tell ApexSQL Diff what data in which columns of which tables to compare during the comparison process. As I have demonstrated you can either accept the defaults in which tables and columns within those tables are mapped based on their names, or you can take over full control and map tables and the columns within those tables exactly according to your needs.
Next you can choose what data in what object types is to be compared. You can select base tables and indexed views here.
ApexSQL Diff differentiates between four distinct types of compare operations:
- Identical Records
- Different Records
- Missing Records
- Additional Records
Here’s a brief explanation of each type:
- Identical Records: These are rows in table pairs that are identical in their column pair values.
- Different Records: These are rows in table pairs that are different in at least one of their column pair values.
- Missing Records: These are rows in table pairs that only exist in the Source part of the comparison, but not in the Destination.
- Additional Records: These are rows in table pairs that only exist in the Destination part of the comparison, but not in the Source.
Each one is individually selectable depending on your needs.
To complete the Synchronization Wizard, click on the Finish button. This starts the ApexSQL Diff data scripting process. Once this process has finished the “Batch Data Synchronization Script dialog comes up:
This dialog lets you choose how to proceed with the generated script. You can execute it immediately against the Destination part to which ApexSQL Diff has automatically connected to, or save the script to file in order to run it at a later time, or as part of a scheduled job.
Coming back to my running example, I have decided to execute the script against the Destination database. In my case there are 2,048 rows to synchronize, so the whole process doesn’t take too long. Upon completion, ApexSQL Diff informs you about the successful execution:
A cool feature of ApexSQL Diff is the “Row Filter” dialog that you can use to limit the rows being considered in the comparison process. Just enter a valid WHERE clause and click Ok to “create” the filter. It will later on be applied when ApexSQL Diff determines the rows for the comparison.
Another difference when running ApexSQL Diff in Data Mode is shown in the screenshot above. You can choose how certain constraints should be handled by the application. Interesting and useful is also the option to apply different date formats when you need to work with data stored in (SMALL)DATETIME columns in SQL Server. To avoid any issues when synchronizing data between databases with different settings you should always use an unambiguous date format, such as the one shown in the screenshot above.
Reporting is also a bit different when using the Data Mode of ApexSQL Diff. The available options and setting can be seen in the above screenshot. The output is a HTML file that be viewed with a web browser.
Now, that we have initially synchronized the data in both tables, let’s modify again the data in the Source part of the synchronization. I have modified a single row by setting the value in one column from 110 to 130. Running the comparison process again, you can see in the screenshot that ApexSQL Diff correctly detects the difference in that row. You can now decide how this difference should be handled. At this point you can choose between making the Destination identical with the Source of the comparison or vice versa.
The Command Line Interface
ApexSQL Diff hosts, just like any other of their tools, a feature-rich and powerful Command Line Interface (CLI). This CLI can be used to automate any task by creating a batch file that can be executed via the Windows Scheduler. The CLI is certainly a pretty complex topic in and of itself and takes some time to get acquitant with. However, in its most simple, yet probably also most versatile form, you can create your project with the ApexSQL Diff GUI with all its options and settings, save the project and just pass the whole project file to the CLI via the /argfile switch.
Here are some examples on how to use the CLI (taken from the ApexSQL Diff Online Help):
- Compare two databases in structure mode and create SQL synchronization script:
ApexSQLDiff /server1:CS /database1:pubs /server2:CI /database2:pubs2 /output_file:sync.sql - Compare only tables and views of two databases in structure mode and create SQL synchronization script:
ApexSQLDiff /server1:CS /database1:pubs /server2:CI /database2:pubs2 /output_file:sync.sql /o:12 - Compare selected objects of two databases in structure mode and create SQL synchronization script:
ApexSQLDiff /server1:CS /database1:pubs /server2:CI /database2:pubs2 /output_file:sync.sql
/includes:profile_structure.xml - Compare selected objects of two databases in data mode and create SQL synchronization script:
ApexSQLDiff /m:data /s1:CS /d1:pubs /s2:CI /d2:pubs2 /of:sync.sql /i:profile_data.xml - Compare two databases in structure mode and create unicode SQL synchronization script:
ApexSQLDiff /s1:(local) /s2:(local) /d1:pubs /d2:Northwind /enc:u /of:”c:new_result.sql” - Create snapshot of the database
ApexSQLDiff /ms:c:snapshot1.snp /d1:pubs - Compare database and snapshot and create unicode HTML report:
ApexSQLDiff /sn1:c:snapshot1.snp /d2:pubs /enc:u /ot:html - Compare database and snapshot and create unicode SQL synchronization script:
ApexSQLDiff /sn1:c:snapshot1.snp /d2:pubs /enc:u - Compare selected objects of two databases in data mode and create HTML report:
ApexSQLDiff /m:data /s1:CS /d1:pubs /s2:CI /d2:pubs2 /ot:html /of:comp_report.html
/i:profile_data.xml - Compare selected objects of two databases in data mode and create VB synchronization script:
ApexSQLDiff /m:data /s1:CS /d1:pubs /s2:CI /d2:pubs2 /ot:vbs /of:sync.vbs
/i:profile_data.xml - Load project, compare selected objects and create XML report:
ApexSQLDiff /pf:”My Project.xml” /ot:xml /of:sync_report.xml /includes:profile_structure.xml
Summary
ApexSQL Diff provides the following key features:
- Compare and synchronize structure and data differences between SQL Server data sources.
- Version control.
- Object-level restore and recovery.
- Automated execution of comparison processes via a powerful Command Line Interface.
- SQL Server 205 ready.
Advantages
- Ability to compare both structure and data.
- A very rich set of options and setting from which you can choose.
- Economically priced.
- Moderate system requirements.
- Overall easy to use.
Disadvantages
- The reporting features are basic. Though you can export to XML, I would like to see some more options like Exporting to PDF or XLS.
Conclusion
ApexSQL Diff is easy to use, fast, and reliable. It helps you designing a smooth transition of database objects and data from one source to another destination. If you have a need for such a tool (and, as a matter of course, you do have such a need, if you are just half-way professionally managing your SQL Server environment), make sure to stop on the products’ homepage and give the product a try.
]]>