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
13:06