Hello, and welcome to this ApexSQL Diff general overview video. ApexSQL Diff is a SQL server development tool that is capable of comparing and synchronizing database schemas, including tables, views, procedures, functions, and other database objects. Data sources include SQL server databases, backups, source control projects, script folders, and schema snapshots. ApexSQL Diff also supports schema synchronization for ApexSQL database and Amazon RDS for SQL server.
ApexSQL Diff features include the ability to automate and schedule schema comparisons and synchronization using the command line interface, propagate schema changes from one environment to another, identify and repair lost and/or damaged objects from backups without a full restore, recognize all object interdependencies to ensure error-free synchronization scripts, compare databases directly from SQL Server Management Studio, compare SSIS packages, and much more.
ApexSQL Diff consists of three main components-- GUI, a Windows application used for manual comparison and synchronization, and various options for the comparison and synchronization processes. CLI, a console application which allows users to schedule and automate schema comparison and synchronization processes unattended. Add-in-- an add-in that allows initiation of comparison of databases or other data sources directly from SQL Server Management Studio.
Now that we've covered the main components and features of the tool, let's walk through the process of using ApexSQL Diff to compare and synchronize data sources. Upon starting the application, the project management window will be shown, where a new project can be created, or a previously saved project can be opened. To initiate a new project, click the New button and choose between database or SSIS comparison project.
For this video, the Database project will be used. In the first tab of the new project window, the Data sources tab, source and destination data sources are specified. Choose between five different data sources for both source and destination, including database backup, live database, script folder, schema snapshot, or source control project. For the purpose of this video, databases are set for both source and destination, where the destination database will be synchronized with changes from the source database.
In the Options tab, choose to set the specific comparison and synchronization options along with the optional output elements options and managing owners in the synchronization script. For more options, expand the Advanced options. And four additional tabs will be shown. The first one is the package tab in which options can be set for the executable installation packages, such as compression, error handling, logging, and optional package information that will be shown in the Package About window.
Next is the schema mapping tab that by default matches schemas with the same names in the source and destination but also allows for customizing schema mapping pairs. If there are differently named schemas in the compared databases, Report schema in source and Reporting schema in destination database, for example, just pick them from the dropdown list. In the first row. And click the Add button. And all objects that have these two schema names will be paired.
Once the Object filter tab is selected, by default all object types are checked along with the automatically mapped objects. Each object type, along with all its objects, can be included or excluded from the comparison process with a click on the checkbox for the desired object type or for the individual object. In addition to manual checking or unchecking of the object types and objects, the object filter tab allows users to filter objects by the schema or name.
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 the desired condition. In this example, objects are filtered by schema name so that only objects with the schema name Sales are shown. The last tab is the Additional scripts tab, in which pre or post-processing scripts can be configured.
These will be executed before or after the synchronization process, respectively. In this example, the embedded script option is used as the pre-processing script and the script to create the table is added. 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. By default, the comparison results will be grouped by difference type-- different, missing, additional, and equal. In the Results grid, objects can be additionally filtered with a click on the checkbox to manually check or uncheck them. Since ApexSQL Diff handles schemas differently based on whether they are different, missing, additional, or equal, let's clarify what each object difference means, and what will be its resulting action in the synchronization process.
Equal objects-- it does nothing in this case. Missing objects-- these objects exist in source but not in the destination data source. So they are added. Different objects-- these objects exist in both source and destination, but they are different. So the objects script in the destination data source will be overwritten with the script from the Source data source. Additional objects-- these objects exist in destination but not in the data source. So in order to make the data sources the same, these objects will be deleted.
Each difference between compared objects is highlighted in the Script difference view panel. And it can be easily spotted in the navigation bar on its left side. One more feature that can help to quickly determine differences is the differences by type feature. It shows which specific type has differences, which are shown in detail in the Script difference view pane.
In addition to manually checking and checking objects, additional filtering can be done prior to the synchronization process. The object filter panel allows
12:13