AutosyncDB is designed around the idea of automating database schema updates. The main goal is to automate the database update for each new version of the application. In this case, a new version database is used as a source of sample schema and predefined application data. It can be either an empty database specially built from scratch, or a test one, or any other, which is very useful in comparative analysis, for merging the schema of two databases, etc.
Task-based
AutosyncDB is a task-based. Any manipulations with a database can be performed only within a task. The task itself is a decomposition of the entire database update task, where each step is customizable. The application's task document itself is a task settings tree. The pictures below show the task functional diagram and the task settings tree.
Project-oriented
AutosyncDB has a non-classical project architecture in which tasks act like a project. The project file itself contains the task settings defaults. You can create several tasks for a project and save any of them as this project or as a new one. Each task has its own copy of the project settings. They do not overlap, but they all relate to the same project folder. The project also has an output folder with subfolders for results. Each subfolder is named after its task, so they do not overlap either, but task names have to be unique in the project scope.
This model allows to have different configurations (projects) in the same folder, share nested files between them, and also make one project work simultaneously for multiple databases.
The pictures below show an example of two projects in the same Project1 folder with five opened tasks and output folders, two for
Project1-config1 and three for Project1-config2.
Database schemas comparison and synchronization
Comparison and synchronization is configured by specifying actions, such as Create, Alter, Recreate, Drop, that are allowed to be performed on objects of a certain category, as well as other settings. These settings are used by the comparison algorithm to compare database schemas and build a synchronization protocol along with a difference model. If any action is required on the object to eliminate its differences and this action is allowed for the object's category, then it will be set up for it. AutosyncDB reports the differences for each object in the context of these actions, along with the result of the comparison and the reason for the first action.
Other essential synchronization settings are Resolve dependencies and Resolve conflicts. They tell the algorithm to unwind chains of interdependent objects by re-creating them, as well as drop conflicting ones. In most cases, you must use the Resolve dependencies option to get the correct synchronization script, otherwise hard dependencies between some objects may prevent them from being changed, and the child objects whose parents are being recreated may be lost.
It does not matter to the algorithm whether the task was conceived to compare or sync database schema, but by manipulating the allowed actions and the resolution options, both results can be easily obtained.
Based on the synchronization protocol and the difference model AutosyncDB builds reports: Differences tree view, which shows a compound list of objects from both databases in the context of actions and comparison results; Excel report representing the synchronization protocol; SQL sync script for the target database as a kind of report based on the sync protocol and the object dependencies data.
The pictures below show two Differences panels. The first was built for a task configured to synchronize database schemas and the second to compare database schemas. The sync task did not suppose dropping objects missing in the source database, but it did allow resolving dependencies and conflicts. Instead, all but group actions (Recreate All, Drop duplicates) were allowed in the comparison task, but dependencies and conflicts were not resolving.
Differences panels of synchronization and comparison tasks
As you can see from the pictures the synchronization task has not set any actions for tables and stored procedures missing in the target database, but has set the Recreate action for some identical table keys, constraints, indexes, and view triggers whose parent objects have to be recreated. Instead, the comparison task simply has reported all the differences. The Create action tells us that the object is missing in the target database, the Drop action that in the source, the Alter and the Recreate actions indicate that the objects are different, and the equal sign that they are an identical.
Unlike the comparison algorithm, the application shell recognizes the types of tasks, but it does this in order to indicate whether the task is configured to change the target database or not, and if so, how. That is why AutosyncDB marks a task as a synchronization task if the result synchronization script is configured to be executed at the end. If a custom script is configured to be executed before the comparison and the result synchronization script is not configured, the task is treated as an update task. Otherwise, it is a comparison task. You can find the corresponding hint for the current task on the status line.
Database schemas comparison and synchronization features
AutosyncDB works with online databases and files. It uses sys. views to load
and cache database schemas each time a task is run.
AutosyncDB is cross-server and cross-version. For example, you can synchronize a database schema on SQL Server 2005 to a database schema on SQL Server 2022, or vice versa. Of course, if you use some new features that are not supported by 2005th, you will get the corresponding errors from SQL engine during the script execution.
AutosyncDB always compares the names of database objects in COLLATION, but the comparison of object bodies and computed expressions are customizable. If the Alter database COLLATION option is set, the comparison algorithm will use the default COLLATION of the source database, otherwise the target database.
The application provides support for comparing and synchronizing not only database objects, but also their various parameters such as, for example, ANSI_NULLS, ANSI_PADDING, DATA_COMPRESSION, ONLINE, PAD_INDEX, IGNORE_DUP_KEY, STATISTICS_INCREMENTAL, ALLOW_ROW_LOCKLOCKS, ALLOW_PAGE_PAGE, and many others. Depending on a task configuration, their values can either be inherited from the values in the target database or changed to the values from the source. In many cases, the need for such a change may even lead to the need to re-create the objects themselves, which the application also copes with successfully.
Explore the task settings tree guide in the page menu to learn more about available database comparison and synchronization features.
Show object differences between two schemas
AutosyncDB has a built-in support for visualizing object differences after comparison. It is available for each object through the Differences panel. Also, through it, you can see a synchronization script for the object, if one was required.
The picture below shows the differences between the dbo.SYS2 table schemas.
This result was taken from the above example with two tasks, namely for the synchronization task.
The comparison algorithm considered that the table was changed, and the Recreate action is required
to align the order of the IdExt column.
Also the PasswordHash column has to be added.
It is noteworthy that if the
Tables \ Column order
option had not been set for the task,
the table could be changed simply by using the ALTER statement, without having to re-create it.
Object differences document example
Carry over data
When a new version of an application is deployed along with a new database schema, new predefined data usually come in some of its tables. It can be some kind of catalogs, dictionaries, application data, functional schemas in XML or JSON format, settings, etc. In this case, deployment also means carrying over data, or rather, synchronizing it. AutosyncDB provides Carry over data tech for this purpose.
The application uses an XML file of a certain format to list such tables and describe the rules for them. Based on these rules, a data merge script is generated each time a task is run. AutosyncDB allows to work with the entire set or only a subset of the data in a table, exclude certain columns, define conditions for data selection, match, insert, update, and delete, share or not share IDENTITY values, disable triggers and foreign key constraints, show statistics.
Typically, carry-over data comes from the source database to the data merge script in the form of insert statements
to temporary tables followed by the MERGE statement itself, although it can be reached directly
by select queries if the source database is accessible on the target side.
The carry-over data can also be exported to a database structure file or a database update package file along with
the database schema.
Carry-over data description file example
<?xml version="1.0" encoding="utf-8"?>
<root>
<!-- Just insert rows not matched by the target -->
<merge schema="dbo" name="UserAuthMethods" />
<!-- Make the data the same, do not share identity values, turn off triggers, match by CatId -->
<merge schema="SalesDept" name="PropertyCategories" upd="1" del="1" tgoff="1" iioff="1" mcond="t.CatId = s.CatId" />
<!-- Sync app objects, specified columns only, don't touch user defined objects, force brutal update
(do not compare the values of all updated columns in the condition of the update clause) -->
<merge schema="app" name="main_factory_objects" upd="1" del="1"
cols="[path], is_app_shipped, [class], name, [content]"
scond="ISNULL(is_app_shipped,0) = 1"
dcond="ISNULL(t.is_app_shipped,0) = 1"
ucond="--"
/>
</root>The tech supports all built-in data types as well as user-defined data types, including CLR.
It can handle some bugs with the xml data type, can use OPENQUERY to deal with
in-memory tables and linked servers.
The tech is designed to solve the task of synchronizing data up to several hundred megabytes in binary compressed format, but you can try it under more workload, or experiment with merging data directly using select queries to reach data from a source database on the same or on a linked server.
Export database schema to file
AutosyncDB implements its own zipped .audbs file format for storing database schema and carry-over data. There is no difference between exporting a target or a source database. The export itself is performed as a task by command.
All database objects and entities are exported to a file regardless of the task settings, with the exception of the Security and Permissions settings and the Carry over data setting. AutosyncDB exports only those security and permission entities that are selected. If Carry over data is configured, it will also be exported along with the carry-over data description file. This file will be used instead of the description file in a task when the exported database schema file is using as a source database. This is due to the fact that the exported data itself and its linkage are closely related to the rules for their merge, so neither the data nor the rules can be changed after the export.
Do not use database structure files and package files as the only copy of database schema and predefined data in a version archive. Always have a backup of the database itself or/and the SQL scripts (DDL) to create one from scratch.
Build database update package
Any task can be exported to a package file that AutosyncDB can run in the Package Wizard mode or in the background. In addition, the package file can be exported back as a project. For these purposes, AutosyncDB implements the .audbp file format, which is an extended version of .audbs. The build itself is performed as a task by command.
When a build task runs, the source database schema and the carry-over data, the project file (the task), and any SQL scripts and other files used by the task are exported to a package file. However, the target database is not exported, and its parameter in the exported project file is always cleared. Exporting files that located outside a project folder is not supported.
The task settings tree contains the Package information node with well-known field names for describing the package being created. Their values will be shown in the wizard form when the package is opened.
When AutosyncDB receives a request to open a package file, it first unpacks it to a temporary folder and then opens it from there as a project. As you can see from the picture below, the end user can quickly change the most important task settings to him, without going into details.
Database Update Package Wizard example
Approve target database
The main goal of this feature is to prevent a task from being executed on a wrong database, which, for example,
belongs to another product, or has a different configuration, version, does not meet some internal requirements, etc.
It is mainly used in update packages to check the target database selected by the user.
The check is performed by a custom script that reports the result to the #autosyncdb_target_approval table.
This script is also added at the beginning of the result synchronization script.
Database approval script example
BEGIN TRY
-- Does it look like ours? Check some basic objects
IF OBJECT_ID(N'[dbo].[SYS2]', 'U') IS NULL OR OBJECT_ID(N'[dbo].[Schemas]', 'U') IS NULL OR OBJECT_ID(N'[dbo].[SYS_KernelVersion]', 'U') IS NULL
INSERT INTO #autosyncdb_target_approval(result_code) SELECT 2;
-- Is this configuration correct? Check some peculiar objects
ELSE IF OBJECT_ID(N'[dbo].[DOC310]', 'U') IS NULL
INSERT INTO #autosyncdb_target_approval(result_code) SELECT 3;
-- The version must be greater than or equal to 4.58
ELSE IF CONVERT(hierarchyid, '/' + ISNULL((SELECT TOP 1 VersionString FROM [dbo].[SYS_KernelVersion] WITH(NOLOCK)), '') + '/') < CONVERT(hierarchyid, '/4.58/')
INSERT INTO #autosyncdb_target_approval(result_code) SELECT 4;
-- The version must be less than 4.61
ELSE IF CONVERT(hierarchyid, '/' + ISNULL((SELECT TOP 1 VersionString FROM [dbo].[SYS_KernelVersion] WITH(NOLOCK)), '') + '/') >= CONVERT(hierarchyid, '/4.61/')
INSERT INTO #autosyncdb_target_approval(result_code, result_code_desc) SELECT 5, 'The target database should be version 4.58 till 4.60 for this update.';
-- Let's say the presence of the Name column tells us about some scripts that should have been run earlier.
ELSE IF NOT EXISTS(SELECT TOP 1 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[DOC310]', 'U') AND name = N'name')
INSERT INTO #autosyncdb_target_approval(result_code) SELECT 6;
-- Successfully approved
ELSE
INSERT INTO #autosyncdb_target_approval(result_code) SELECT 0;
END TRY BEGIN CATCH
-- Something went wrong
INSERT INTO #autosyncdb_target_approval(result_code) SELECT 1;
END CATCH;Compatibility
AutosyncDB is compatible with SQL Server 2005 through 2022 and supports all of the most commonly used features. Code generation is designed so that the resulting SQL script is compatible with the lowest supported version, SQL Server 2005. In some cases, the generator may check the target database server version to provide a more efficient script.
As a first step of the task, the application provides a test to check its compatibility with the databases functionality. However, the test only reports well-known issues and should be used as an additional tool. Make decisions based on the list of supported features. In any case, to handle such functionality when synchronizing database schemas, you can use preparatory and final scripts, or manually edit the result synchronization script before execution.
Refer to the Test for compatibility settings here Compare schema of two sql databases and here Compatibility to learn more.
Integration
This allows to automate not only database comparison and synchronization, but also the package building processes. AutosyncDB can be run in a regular mode as an MDI application, in a dialog mode as Package Wizard, or in the background with or without user interaction. The dialog and background modes are single-task modes in which the code returned by the process to the system reports the result of the task. For deeper integration, there is also a window procedure-based interface that implements interprocess interaction between the client application's foreground window and the task process.