Hello and welcome to this ApexSQL Data Diff general overview video. ApexSQL Data Diff is a SQL server development tool that is capable of performing comparison and synchronization of data stored in tables or views between different data sources. Data sources include SQL Server Databases, backups, source control projects, and script folders.
ApexSQL Data Diff also supports data synchronization for SQL Azure Databases and SQL Server on Amazon RDS. ApexSQL Data Diff can also automate and schedule data comparisons and synchronization using the Command line interface also known as CLI, propagate data changes from one environment to another, identify and repair lost and/or damaged data, perform row level recovery from backups without restoring, compare databases from SQL Server Management Studio or SSMS, and much more. In case something goes wrong as part of creating a synchronization script, ApexSQL Data Diff can reverse any changes made by automatically creating a backup or rollback script prior to synchronization. It also supports creating executable installation packages that a user can run without installing any other software.
ApexSQL Data Diff consists of three main components: GUI, a Windows application used for manual comparison and synchronization, selecting specific objects and rows, and various options for comparison and synchronization processes. CLI, a console application which allows users to schedule and automate data comparison and synchronization along with creating reports and to run everything unattended.
SSMS add-in, a SQL server Management Studio add-in that allows the user to initiate a comparison of databases or other data sources directly from SSMS with a right click on desired databases in Object Explorer. Set them as Source and Destination and click the Compare button. Please note that SSMS add-in is an optional component that can be selected or de-selected during the installation of ApexSQL Data Diff in the integration step.
Upon starting the application, the project management window will be shown where a new project can be created or to open a previously saved project from the list. To initiate a new project, click the New button. In the first tab of the new project window, the Data Sources tab, source and destination data sources should be set. You can choose between four different data sources for both source and destination: backup, database, script, folder, or source control. For the purpose of this video, databases are set for both source and destination where a destination database will be synchronized with data from the source database.
In the Options tab, you can choose which object types you want to compare and to set specific comparison or synchronization options. For more options. Expand the advanced options and five additional tabs will be shown. The first one is the package tab in which a few options can be set for the executable installation packages, such as compression, error handling, logging and optional package information.
The next tab is the Schema Mapping tab that, by default, matches schemes within the same names in source and destination but also allows for customizing schema mapping pairs. For example, if you have differently named schemas such as Report schema in source and Reporting schema in the destination database, just pick them from the dropdown list in the first row, click the Add button, and all objects that have these two schema names will be paired. All objects that have schemas with the same schema and object names, or which were customized in the Schema Mapping tab, will be shown in the Object Mapping tab. If there are any objects with different schema and/or object names, those will be shown under the unmapped objects list.
In this example, these two tables have already been mapped by schema but they differ in the object name. However if the structure of these objects is the same, select the source and destination object and click the Map button. Also if there are differences in column names, select mapped objects and click the Ellipse button to open the mapping columns window. Here, you can easily map columns in a desired way and custom mapped objects along with their columns will be included in the comparison process.
Once you switch to the Object Filter tab, by default, all automatically mapped objects are checked. The Object Filter tab allows various filtering options for tables and views. The Filter Editor allows specifying conditions for schema and object names in order to filter the list of objects that will be compared. To initiate the Filter Editor, click the Edit Filter button in the top right corner and provide a desired condition. In this example, objects are filtered by schema name so that only objects with schema name, person, are shown.
The custom comparison key allows the user to set the comparison key when there is no primary or foreign key. In order to set the customer comparison key, click the Arrow button for the selected object under the Comparison Key column and pick a column that will be the custom comparison key. The Columns Filter allows the user to include or exclude specific columns for the selected object by clicking the Ellipse button under the Columns column.
The Row Filter allows specifying a condition for the WHERE clause to limit the number of rows that will be compared. To set the condition, select an object from the list and click the Ellipse button under the Row Filter column. In the Row Filter window, specify a desired condition. And in this example, check or uncheck whether to apply the same condition on both source and destination objects. In this example, the condition for the person.address table is set. So instead of comparing all rows, the condition address ID greater than or equal to 1 and address ID less than or equal to 100 limited the number of rows to a subset of just 100.
The last tab is the Additional Scripts tab in which pre- or post-processing scripts can be configured that will be executed before or after the synchronization process. In this example, the embedded script option is used as the pre-processing script and a script to create a table is written. While for the post-processing script, an already created script that will create another table will be loaded using the use this script option.
Once everything is set up, click the Compare button from the bottom right corner of the New Project window and the comparison process will be initiated. After the comparison process is done, the comparison results will be displayed in the Results Grid. In the first Compare tab, all objects that have missing different or additional rows will be shown along with the exact number of rows.
The Equal tab contains objects that are fully equal, all rows are equal. The Incomparable tab contains unmapped objects, the ones with different schema and/or object names that weren't mapped or that exist only in the source or in the destination. In the Results Grid, you can additionally filter objects and their corresponding rows by manually checking or unchecking them. For each object, rows are sorted in five tabs. All rows: missing, different, additional, and equal.
Since ApexSQL Data Diff handles data differently based on whether it is the same, different, added, or missing, let's clarify what each row means and what will be its action in the synchronization process. Equal rows: it does nothing in this case. Missing rows: these rows exist in source but not in the destination database so they are copied. Different rows: these rows exist in both source and destination but they are different. So they will overwrite data in the destination database. Additional rows: these rows exist in destination but not in the source database. So in order to have a copy of the source database, these rows will be deleted. Each difference in the rows is highlighted in the Data Difference View panel.
By clicking on the Export button from the Home tab, the comparison results can be exported into four different outputs: HTML simple and interactive report, Excel report and CSV data export. Let's choose the interactive HTML report and set the report options of what should be included in it. Once the report is generated, open it in a browser and navigate to the expandable parts: tables and data. If both of these are expanded and the desired object is clicked, all data differences for the selected object can be reviewed.
If all desired objects and rows are checked for the synchronization process, click the Synchronize button from the Home tab to initiate the synchronization wizard. The first steps in the synchronization wizard are to confirm the set direction or to change it with a click on the Direction button. The second step is the output option step in which the action can be chosen from the dropdown list along with its corresponding options. Four different actions can be chosen: Create A Synchronization Script, Synchronize Now, Create A C Sharp Solution, Create An Executable Installer.
In this example, the Synchronize Now action is chosen that will perform a direct synchronization on the destination database and options to save a copy of the synchronization script and to create a rollback script as a backup option are checked. In the last step, any warnings and actions can be reviewed in their separate tabs as the last checkup before executing the synchronization.
If this project needs to be repeated, it can be easily automated by creating a batch file that can be scheduled via SQL Server job or task scheduler. To create and save the batch file, click on the Save As Batch button in the bottom left corner of the synchronization wizard. As displayed, the batch script will contain all CLI switches for options that were set along the way as the project was constructed. Additionally, you can automatically create a project file that will replace all project options and encrypt any passwords that you might have for SQL server authentication or for the source control system.
Once you have reviewed and set everything that you need, click the Save button from the bottom right corner of the Save As Batch window and provide the location where it should be saved. After the batch script is saved, you'll be brought back to the last step of the synchronization wizard.
And upon clicking on the Synchronize button, a prompt will be shown in order to notify you that the changes will be made on the destination database. Click the Yes button and the synchronization script will start with execution. Once the synchronization process is done, the Results window will be shown along with all information messages about taken actions.
The execution results can be saved into four different output types: text file, word document, HTML page, and PDF document. Once the execution results are saved and the Close button is clicked in the Results window, the recomparison of data sources will be done and you can check if everything was properly synchronized. Thanks for watching. For more information, please visit apexsql.com.
13:06