Product destination

SQL Schema Sync API is a library of .Net components intended for the synchronization of MS SQL database structures and for the creation of MS SQL database structures on the basis of object model from the application source code. The library can be used with applications of any type including Windows Forms, ASP.NET, WPF, and consol applications. The use of the library doesn’t require installation of additional software on the client side.

Send comments on this topic.

(c) Perpetuum Software LLC


Compatibility

To use SQL Schema Sync API on the client machine the .Net Framework 2.0 or higher should be installed.

The product is fully compatible with Visual Studio.Net 2005/2008.

Current version of SQL Schema Sync API works with SQL Server 2008 databases (including SQL Server Express Edition).


Deployment

In order to integrate the library into your product it is required just to include the PerpetuumSoft.DataModel.dll in your application.

Redistribution of the dll file within your applications doesn’t require additional fees.


Basic classes

In most cases the user directly refers only to three classes of objects: DatabaseSync, DatabaseSynchronizer, and Database. The DatabaseSync component performs all operations related to database structure reverse, comparison and update. The DatabaseSynchronizer class includes all scripts created to change database structure. The Database class serves to represent database structure and it is also responsible for saving and reading this structure into a thread (the so called snapshot).

The PerpetuumSoft.DataModel.MsSql namespace contains classes representing database structure as object graph. Structure of these classes closely corresponds to structure of corresponding database contents and is intuitive to developers. If necessary, it is possible to get full access to database structure though the Database.Objects property.

Detailed information on all SQL Schema Sync API classes can be found in the class reference.


Synchronized database objects

SQL Schema Sync API supports synchronization of the following objects:

  • Schemes
  • Tables
  • Views
  • Functions and stored procedures
  • Triggers
  • DLL triggers
  • Assemblies
  • User-Defined types and CLR types
  • Indexes and XML indexes
  • XML schemes collections
  • Constraints
  • Users, roles and permissions
  • Extended properties
  • Synonyms
  • Rules
  • Default values
  • Database options


Creating database structure from code

In some projects, the first class object is not a database structure, but business objects classes structure or description of abstract metadata. In this situation, synchronization of database structure and the main structure is performed manually and as a rule takes much time.

You can easily automate this process with SQL Schema Sync API by generating database structure as object graph based on your metadata.

If an ORM solution is used in the project, information on database structure is in the system. Writing generation procedure is simple as SQL Schema Sync API objects structure is fully isomorphic to MS SQL Server contents.

For example, let’s create description of database structure including two connected tables with primary keys, computable column, default value for column and custom scheme:

Database database = Database.CreateDatabaseWithSystemObjects();

Schema schema = new Schema("TestSchema");

schema.Owner = database.Objects.GetElement("dbo");

database.Objects.Add(schema);

Table masterTable = new Table("Master", schema);

database.Objects.Add(masterTable);

DataColumn masterIdColumn = new DataColumn("id", new DataType.Int(), false);

masterIdColumn.Identity = new Identity();

masterIdColumn.Identity.Increment = 1;

masterIdColumn.Identity.Seed = 1;

masterTable.Columns.Add(masterIdColumn);

IndexColumn indexColumn = new IndexColumn();

indexColumn.Column = masterIdColumn;

PrimaryKey primaryKey = new PrimaryKey();

primaryKey.Name = "PK_Master";

primaryKey.Clustered = true;

primaryKey.Table = masterTable;

primaryKey.Columns.Add(indexColumn);

database.Objects.Add(primaryKey);

DataColumn masterColumn;

masterColumn = new DataColumn("datetimeCol", new DataType.DateTime(), true);

masterTable.Columns.Add(masterColumn);

masterColumn = new DataColumn("decimalCol", new DataType.Decimal(6, 2), true);

masterTable.Columns.Add(masterColumn);

masterColumn = new DataColumn("intCol", new DataType.Int(), true);

masterTable.Columns.Add(masterColumn);

masterColumn = new DataColumn("nvarcharCol", new DataType.NVarChar(100), true);

masterColumn.Collation = "Latin1_General_CI_AS";

masterTable.Columns.Add(masterColumn);

DefaultConstraint masterDefaultConstraint = new DefaultConstraint("decimalColDefaultConstraint", masterTable, masterColumn, "((50))");

database.Objects.Add(masterDefaultConstraint);

Table detailTable = new Table("Detail", database.Objects.GetElement("dbo"));

database.Objects.Add(detailTable);

DataColumn detailIdColumn = new DataColumn("id", new DataType.Int(), false);

detailIdColumn.Identity = new Identity();

detailIdColumn.Identity.Increment = 1;

detailIdColumn.Identity.Seed = 1;

detailTable.Columns.Add(detailIdColumn);

indexColumn = new IndexColumn();

indexColumn.Column = detailIdColumn;

primaryKey = new PrimaryKey("PK_Detail", detailTable);

primaryKey.Clustered = true;

primaryKey.Columns.Add(indexColumn);

database.Objects.Add(primaryKey);

ComputedColumn detailComputedColumn = new ComputedColumn();

detailComputedColumn.Name = "computedCol";

detailComputedColumn.Expression = "([masterId]*(10))";

detailComputedColumn.ExtendedProperties.Add(new ExtendedProperty("compColExtProp", "value"));

detailTable.Columns.Add(detailComputedColumn);

DataColumn detailMasterIdColumn = new DataColumn("masterId", new DataType.Int(), false);

detailTable.Columns.Add(detailMasterIdColumn);

ColumnRef masterIdRef = new ColumnRef(masterIdColumn);

ColumnRef detailMasterIdRef = new ColumnRef(detailMasterIdColumn);

ForeignKey foreignKey = new ForeignKey("FK_Detail_Master", detailTable, masterTable);

foreignKey.Columns.Add(detailMasterIdRef);

foreignKey.ForeignColumns.Add(masterIdRef);

database.Objects.Add(foreignKey);

DataColumn detailColumn = new DataColumn("varCharCol", new DataType.VarChar(), true);

detailColumn.Collation = "Latin1_General_CI_AS";

detailTable.Columns.Add(detailColumn);

During forming database structure from code, it is necessary to pay attention to SQL dependencies between the objects. First of all, this should be applied to the following objects: CheckConstraint, DefaultConstraint and View. As SQL Schema Sync API doesn’t recognize such dependencies automatically, it is necessary to assign them at the stage of forming objects structure by adding corresponding elements in the DependOn collection of every object.

You should also pay attention to the fact that SQL Schema Sync API doesn’t assign default values for such properties as Schema and Owner. In case you create objects featuring the mentioned properties, you should initialize them explicitly.

The delivery package includes demo applications: GenerateDatabaseFromSourceCode which creates database on the basis of object model created in the application source code and GenerateDatabaseFromDataSet, which creates database on the basis of a DataSet.


Updating database

The DatabaseSync.UpdateDatabase method is used for updating database structure.

public void UpdateDatabase(Database sourceDatabase, SqlConnection targetDatabaseConnection, SqlTransaction targetDatabaseTransaction)

public void UpdateDatabase(Database sourceDatabase, string targetConnectionString)

public void UpdateDatabase(string sourceConnectionString, string targetConnectionString)

The method gets database structure previously got by reading a snapshot, or by reversing or gets database structure formed from code or connection string to a database from which structure should be got as the first parameter. The other parameters define parameters for connecting to a database that should be updated. Please, pay attention to the fact that synchronization is performed inside transaction, except for synchronization of principals. That is why in case principals are synchronized, changes rollback won’t be available. For this reason it is impossible to use the UpdateDatabase method together with carry of transaction. In this case it is necessary to use other methods overrides.

The DatabaseSync object will generate the following scripts in order to create database described in the previous chapter.

CREATE SCHEMA [TestSchema] AUTHORIZATION dbo

CREATE TABLE [TestSchema].[Master]([id] INT NOT NULL IDENTITY(1,1),[datetimeCol] DATETIME NULL ,[decimalCol] DECIMAL(6,2) NULL ,[intCol] INT NULL ,[nvarcharCol] NVARCHAR(100) COLLATE Latin1_General_CI_AS NULL )

ALTER TABLE [TestSchema].[Master] ADD CONSTRAINT [PK_Master] PRIMARY KEY CLUSTERED ([id] ASC) WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [TestSchema].[Master] ADD CONSTRAINT [decimalColDefaultConstraint] DEFAULT ((50)) FOR [nvarcharCol]

CREATE TABLE [dbo].[Detail]([id] INT NOT NULL IDENTITY(1,1),[computedCol] AS ([masterId]*(10)),[masterId] INT NOT NULL ,[varCharCol] VARCHAR(50) COLLATE Latin1_General_CI_AS NULL )

ALTER TABLE [dbo].[Detail] ADD CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([id] ASC) WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)

ALTER TABLE [dbo].[Detail] WITH CHECK ADD CONSTRAINT [FK_Detail_Master] FOREIGN KEY (masterId) REFERENCES [TestSchema].[Master] (id) ON UPDATE NO ACTION ON DELETE NO ACTION

EXEC sys.sp_addextendedproperty @name = N'compColExtProp', @value = N'value', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Detail', @level2type = N'COLUMN', @level2name = N'computedCol'

After the scripts are executed database of the following structure will be created on the sql-server:


Database structure reverse

You can get structure of the current database by using the DatabaseSync.ReverseDatabase method.

There are two overloaded versions of this method.

public Database ReverseDatabase(SqlConnection connection, SqlTransaction transaction)

public Database ReverseDatabase(string connectionString)

One of them requires connection instance and transactions as parameters, the other one requires connection string.


Saving and reading database structure

Saving and reading database structure (snapshot) is performed by the Database class Read and Write methods. Overloaded variants of methods allow the use of file, thread or TextReader for storing database snapshot.

public void Write(string fileName)

public void Write(Stream stream)

public void Write(TextWriter writer)

public static Database Read(string fileName)

public static Database Read(Stream stream)

public static Database Read(TextReader reader)

In case the Write() method is executed for the database object created in the section “Creating database structure from code”, you will get an XML-file containing description of all database objects. For example, the computedCol column of the Detail table will be described in the following way:


Progress evaluation

There are events of the DatabaseSync component responsible for getting information on the update or reverse process.

public event EventHandler Progress;

The event is intended to display information on current component actions to the user

The Percent and Action properties of the ProgressEventArgs object are used to display information . The percent property contains amount of executed scripts from the total amount in percent. The Action property describes action being executed. This event makes it possible to abort synchronization canceling all changes in the database srtucture. You just need to set the ProgressEventArgs property value to “true”.

public event EventHandler ScriptExecuting;

The event allows getting of DLL script text before it is executed.

Script text is contained in the ScriptExecuteEventArgs.Text property.

public event EventHandler ScriptExecuted;

The event allows getting of DLL script text after it is executed; it can be used, for example, to write a log file.

Script text is contained in the ScriptExecuteEventArgs.Text property.


Tools for database refactoring

In case you need to make sophisticated changes of database structure, for example, to change column types to non-cast type, you can use the following events:

public event EventHandler DatabaseUpdating;

The event gets DatabaseSynchronizer-type object before all scripts are executed; it can be used to copy data to temporary data structures.

public event EventHandler DatabaseUpdated;

The event is fired after all scripts that synchronize databases are successfully executed; it can be used to convert saved data into new structure.


Tools for skipping objects during synchronization

public event EventHandler SkipSynchronizing;

The event allows you to get corresponding old and new synchronized objects as well as parameter that can be used to disable or enable synchronization of the passed objects.

This event can be used in the following way:

private void MainForm_Load(object sender, EventArgs e)

{

databaseSync.SkipSynchronizing += new EventHandler(databaseSync_SkipSynchronizing);

}

void databaseSync_SkipSynchronizing(object sender, PerpetuumSoft.DataModel.SkipSynchronizingEventArgs e)

{

if (e.OldObject != null && e.OldObject is SchemaObject)

{

if ((e.OldObject as SchemaObject).Schema.Name.ToUpper() == "IgnoredSchema".ToUpper())

e.NeedSynchronizing = false;

}

}

This example shows how to skip synchronization of objects from the IgnoredSchema schema.


Synchronization options

In order to set synchronization, it is possible to use the following DatabaseSync options:

public bool SynchronizeExtendedProperties;

Default value is true. If the property value is set to true, synchronization of extended properties is executed.

public bool SynchronizeCollation;

Default value is true. If the property value is set to true, synchronization of columns Collation property is performed.

public bool SynchronizePrincipals;

Default value is false. If the property value is set to true, synchronization of database principals is performed.

public bool SynchronizePermissions;

Default value is false. If the property value is set to true, synchronization of database principals permissions is performed. This property is ignored if database principals synchronization is not performed.

public bool DisableDdlTriggers;

Default value is false. If the property value is set to true, all database DLL triggers will be disabled during synchronization.

public bool ConstraintsWithCheck;

Default value is true. If the property value is set to true, constraints with the enabled Check option will be synchronized.

public bool SynchronizeDatabaseOptions;

Default value is false. If property value is set to true, database options will be synchronized.


Comparison

During updating database structure comparison of structures is performed automatically, so, in most cases there is no need to invoke the comparison procedure directly. Though, if you need to get information on difference in database structures or get update script without performing update, you can use the DatabaseSync.Compare method.

public DatabaseSynchronizer Compare(Database sourceDatabase, Database targetDatabase)

This method returns the DatabaseSynchronizer type object and allows you to get all scripts created to change database structure.

For example, using the following code will help you get all scripts and save them to the object of the StringBuilder type

StringBuilder stringBuilder = new StringBuilder();

DatabaseSynchronizer dbSync = databaseSync.Compare(sourceDatabase,targetDatabase);

foreach (Script script in dbSync.Scripts)

{

stringBuilder.AppendLine(script.GetText());

}


Interaction with LINQ object model

.Net Framework 3.5 features language-integrated query (LINQ). On of advantages of this new technology is ability to create object model based on database schema. This model is described in the database markup language (.dbml) file that can be created in Visual Studio 2008 (LINQ to SQL classes). LINQ allows to design C#/VB LINQ queries for this conceptual data model.

At the moment it is possible to create databases on SQL server based on the conceptual model and by using built-in Visual Studio 2008 tools. But there is only one disadvantage: it is impossible to update database structure if conceptual model is changed.

SQL Schema Sync API features the ability to convert LINQ to SQL Classes diagrams to PerpetuumSoft.DataModel.MsSql.Database; this allows the conversion of modifications from LINQ to SQL Classes diagrams to database structure on server. And now there is no need to re-create the whole database on server when slight modifications are done to conceptual model.

In order to create Database-type object by conceptual model, you need to invoke the CreateDatabase method of the LinqToSqlDatabaseBuilder-type object. For example, by using the following code you can create Database based on the model described in the LINQ to SQL Classes and to synchronize its structure to database structure on server:

MetaModel model = new AttributeMappingSource().GetModel(typeof(DataClassesDataContext));

Database sourceDB = new LinqToSqlDatabaseBuilder().CreateDatabase(model);

databaseSync.UpdateDatabase(sourceDB, GetConnectionString());

SQL Schema Sync API delivery package contains LinqToSql sample demonstrating this mechanism.


Typical scenarios of use

SQL Schema Sync API can be used in applications of different types and therefore enables different scenarios of use. The initial database structure can be a result of connected database reverse; it can be read from a snapshot stored in a file, application resource on in different form, it can also be generated at run time.

Nevertheless, the most widespread approach is creation and debugging of the database by the developer and the installation package includes only its structure as a snapshot.

The delivery package provides a sample application, illustrating the simplest scenario of the component use. Database structure is edited by the developer in the integrated development environment.

At run time, the application reads database structure and updates working copy of the database subject to this structure.


Destination

SQL Schema Sync is designed to compare and synchronize database schemas. It can be used by both developers and system administrators. Application field is wide; the tool can solve the following problems:

1. Synchronization of two database structures. Ultimate goal of this process is complete match of two database structures or some of their objects (at user discretion). Synchronization of database schemas can be executed not only on “live” databases, but also by using snapshot saved to a file as a source database structure. This allows you to save database snapshot on one computer and then use it to restore the described structure on unlimited number of SQL Servers.

2. Comparison of two databases for structure match. It is possible to use live databases as well as database snapshots.

3. Tracking of changes in existing databases. In order to solve this problem it is possible to use the ability to save database structure as a snapshot. This snapshot can be used to compare with live database to track changes, and, if necessary, to rollback changes made to a database to the structure saved to a snapshot. So, it is possible to manage versions history.

SQL Schema Sync is easy-to-use; it represents convenient user interface, this reduces time required for product evaluation and training. Work of the product consists of separate steps, on which the definite action described in details should be executed.

SQL Schema Sync provides three operation modes:

  • Saving database snapshot;
  • Comparing two databases;
  • Synchronizing two databases.

Select operation mode.

Let’s consider these three modes in detail.


Saving database snapshot

This mode allows getting snapshot from a live database located on MS SQL Server. Received snapshot can be used for the following purposes:

1. The application developed uses in its work database and database structure is changed when releasing the next version of the application. To make application work correctly it is necessary to keep database actual. So, when a new version of the application is installed, database synchronization is executed. Database snapshot included in the delivery package of the product will help easily perform this operation.

2. It is necessary to manage versions history. You can save database snapshot in definite intervals.

3. Rollback to the previous database version. Snapshot saved beforehand can help developers and system administrators avoid the following problem: everything worked ok not long ago, and now this system function doesn’t work properly, but at first sight there were no changes.

4. Transfer of database structure from one server to another.

Executed steps:

  • Selecting connection to a database. It is possible to use existing connections and to create a new one.

It is also possible to select assembly containing DataContext types representing LINQ To SQL types. All found types will be displayed as shown in the figure below. It is necessary to select one of the offered types.

List of existing connections

List of types in the assembly

Creating new connection.

2. Reading structure of the selected database.

3. Selecting file to save database snapshot to:

Click Next and database snapshot is saved to the specified file and is ready for use.


Comparing two database structures

This mode allows comparison of two database structures. Source and target database structures can be received from database snapshot, server or from assembly. Comparison results can be analyzed and used to decide if made changes are useful, if complete synchronization is required, etc.

Executed steps:

  • Selecting connection to source database, snapshot or assembly, storing its structure;
  • Selecting connection to target database, snapshot or assembly, storing its structure;
  • Setting comparison of two databases. On this step, additional settings that influence comparison process and generation of synchronization script are specified.

  • Reading database structures;
  • Comparison list is provided after database comparison is executed. On this step, you can view differences and select objects to generate synchronization script for.

6. On the next step, you can view script that should be executed to keep two databases in sync.

It is possible to save generated script for its further use and modification.


Synchronization of two database structures

Sequence of actions required to synchronize databases is similar to that required for databases comparison except one thing: after the generated script is displayed the next step makes this script execute keeping target database in sync with source database.

After synchronization is executed synchronization log will be displayed; it is possible to save this log to a file.


Using command line

SqlSchemaSync.exe application provides the ability to save database snapshot and update database from the command line.

Command format:

Reverse database: -r{c|p|s|w} StructureFileName Connection

Update database: -u{c|p|s|w} StructureFileName Connection

-d{c|p|s|w} ConnectionFrom ConnectionTo

Compare database: -c{c|p|s|w}{f|c}{f|c} [StructureFileName1 | Connection1] [StructureFileName2 | Connection2] ScriptFile

Where:

c - console output | connection string

p - show progress form

s - silent mode

w - run wizard

f - file with snapshot

The second symbol of the key determines information display mode:

c – display in consol;

p – display progress form;

s – display is disabled;

w – using wizard to execute the selected operation.


System requirements

.NET Framework 2.0 or higher

MS SQL Server 2005/2008 (or SQL Server Express Edition)