How to schedule auto synchronization between two Databases schema?

Generally synchronization process happens on two Databases, Source and Target Database. The source DB schemas copied to target DB and make both DBs are same schema according to Source DB schema.

Disadvantage of the manual synchronization

• ·         It is hard to find each and every change in entire DB and create script 
• ·         It takes huge time
• ·         There is much possibility to occur error 
• ·        It cannot be automated

SQL Delta(SQL Database Compare and Synchronization Tool)

This is third party tool (not from MS) which provides rich features for the synchronization of DBs

• ·         Synchronize the schema of two SQL databases
• ·         Run scheduled comparisons and control SQL Delta using command line scripting.
• ·         Easy to understand the difference. (User friendly GUI)
• ·         Automatically generate and run change scripts
• ·         Comprehensive reporting including difference, schema and data reports

Create Schema differential script file using SQL Delta

This is SQL script file which contains create, alter and drop commands to be executed in Target DB to synchronize both source and target DB.

 The following steps are creating Schema differential script file.

Step: 1(Install SQL Delta)

 Install SQL Delta V5 (http://www.sqldelta.com/pubfiles/SQLDelta_Install_5.1.exe)

Step: 2 (Creating SDP file)

 This is XML configuration file which contains configuration information about source DB, Target DB, compare option, script file path etc. Please check the file content below.

<Project>

<Version>5.0</Version>

<Comment></Comment>

<StartUp>0</StartUp>

<Connections>

<LItem>

<Side>A</Side>

<Connect></Connect>

<ServerType>7</ServerType>

<ServerName>source DB server</ServerName>

Source DB Info

<Port>0</Port>

<User>sa</User>

<Password>************ </Password>

<Database>T1</Database>

<Offline></Offline>

<CompatabilityLevel>-1</CompatabilityLevel>

<ServiceName></ServiceName>

<UseAs></UseAs>

</LItem>

<LItem>

<Side>B</Side>

Target DB Info

<Connect></Connect>

<ServerType>0</ServerType>

<ServerName>target DB server</ServerName>

<Port>0</Port>

<User>sa</User>

<Password>********** </Password>

<Database>T2</Database>

<Offline></Offline>

<CompatabilityLevel>-1</CompatabilityLevel>

<ServiceName></ServiceName>

<UseAs></UseAs>

</LItem>

</Connections>

<Options>

<CompareTables>true</CompareTables>

<CompareViews>true</CompareViews>

<CompareProcedures>true</CompareProcedures>

<CompareTriggers>true</CompareTriggers>

<CompareFunctions>true</CompareFunctions>

<CompareDefaults>true</CompareDefaults>

<CompareRules>true</CompareRules>

<CompareUDTs>true</CompareUDTs>

<CompareUsers>false</CompareUsers>

<CompareRoles>false</CompareRoles>

<CompareCatalogs>false</CompareCatalogs>

<CompareDiagrams>false</CompareDiagrams>

<ComparePermissions>false</ComparePermissions>

<CompareProperties>true</CompareProperties>

<CompareSynonyms>true</CompareSynonyms>

<CompareAssemblies>true</CompareAssemblies>

<CompareXMLSchemaCollections>true</CompareXMLSchemaCollections>

<CompareMessageTypes>true</CompareMessageTypes>

<CompareContracts>true</CompareContracts>

<CompareQueues>true</CompareQueues>

<CompareServices>true</CompareServices>

<CompareRoutes>true</CompareRoutes>

<CompareRemoteServiceBindings>true</CompareRemoteServiceBindings>

<CompareSchemas>true</CompareSchemas>

<ComparePartitionFunctions>true</ComparePartitionFunctions>

<ComparePartitionSchemes>true</ComparePartitionSchemes>

<CompareCertificates>true</CompareCertificates>

<CompareAsymmetricKeys>true</CompareAsymmetricKeys>

<CompareSymmetricKeys>true</CompareSymmetricKeys>

<CompareEventNotifications>true</CompareEventNotifications>

<CompareSequences>true</CompareSequences>

<ComparePackages>true</ComparePackages>

<CompareStoplists>true</CompareStoplists>

<ComparePlanGuides>true</ComparePlanGuides>

<CompareBrokerPriorities>true</CompareBrokerPriorities>

<PreserveColOrder>false</PreserveColOrder>

<IgnoreDefaults>false</IgnoreDefaults>

<IgnoreDefaultNames>true</IgnoreDefaultNames>

<IgnoreBindings>false</IgnoreBindings>

<IgnoreIndexesAndConstraints>false</IgnoreIndexesAndConstraints>

<IgnoreStatistics>false</IgnoreStatistics>

<IgnoreCheckConstraints>false</IgnoreCheckConstraints>

<IgnoreForeignKeys>false</IgnoreForeignKeys>

<IgnoreFillFactors>false</IgnoreFillFactors>

<IgnoreIndexAndConstraintNames>false</IgnoreIndexAndConstraintNames>

<IgnoreWithNoCheck>false</IgnoreWithNoCheck>

<IgnoreCollationOrder>false</IgnoreCollationOrder>

<IgnoreFileGroups>true</IgnoreFileGroups>

<IgnoreFulltextIndexes>true</IgnoreFulltextIndexes>

<IgnoreIdentitySettings>true</IgnoreIdentitySettings>

<IgnoreDataCompression>true</IgnoreDataCompression>

<IgnoreChangeTracking>true</IgnoreChangeTracking>

<IgnoreLockEscalation>true</IgnoreLockEscalation>

<IgnoreReplicationSettings>true</IgnoreReplicationSettings>

<IgnoreComments>true</IgnoreComments>

<IgnoreCRLF>true</IgnoreCRLF>

<IgnoreTabs>true</IgnoreTabs>

<IgnoreSpaces>true</IgnoreSpaces>

<IgnoreCase>true</IgnoreCase>

<IgnoreSETStatements>false</IgnoreSETStatements>

<DropAndRecreate>false</DropAndRecreate>

<ScriptHeader>

      Script to %Action% %ObjectName% in %ApplyServer%.%ApplyDatabase%

      Generated %TimeStamp%

      Please backup %ApplyServer%.%ApplyDatabase% before executing this script

</ScriptHeader>

<IgnoreDataCase>true</IgnoreDataCase>

<IgnoreTimestamps>true</IgnoreTimestamps>

<IgnoreBlobs>false</IgnoreBlobs>

<IgnoreGUIDs>false</IgnoreGUIDs>

<DisableForeignKeys>true</DisableForeignKeys>

<DisableTriggers>true</DisableTriggers>

<DisableIndexesAndConstraints>true</DisableIndexesAndConstraints>

<ShowOnlyModifiedColumns>false</ShowOnlyModifiedColumns>

<MatchNullToEmptyString>false</MatchNullToEmptyString>

<DataScriptHeader>

      Script to %Action% in %ApplyServer%.%ApplyDatabase%

      Generated %TimeStamp%

      Please backup %ApplyServer%.%ApplyDatabase% before executing this script

</DataScriptHeader>

<CompareOwners>true</CompareOwners>

<UseOwnerMapping>false</UseOwnerMapping>

<OwnerMapping></OwnerMapping>

<IgnoreObjectNameCase>true</IgnoreObjectNameCase>

<LoadSystemDatabasesAndObjects>false</LoadSystemDatabasesAndObjects>

<ConnectionTimeout>60</ConnectionTimeout>

<CommandTimeout>600</CommandTimeout>

<RowDeliminiter>0</RowDeliminiter>

<ColumnDeliminiter>0</ColumnDeliminiter>

<TextQualifier>0</TextQualifier>

<SaveFormat>1</SaveFormat>

<DisplayFilters></DisplayFilters>

<UserList/>

</Options>

<Structure>

<SchemaA></SchemaA>

The script file path

<SchemaB></SchemaB>

<Filename>D:\Test.sql</Filename>

<FilenameAlt></FilenameAlt>

<Execute>false</Execute>

<ExecuteAlt>false</ExecuteAlt>

<Direction>B</Direction>

<SelectAll>true</SelectAll>

<SelectModified>true</SelectModified>

<SelectMissing>true</SelectMissing>

<SelectAdded>true</SelectAdded>

</Structure>

</Project>

Save this file name with ‘sdp’ extension (filename.sdp)

Step: 3 (Generate Schema differential script file from command prompt)

In command prompt go to SQL Delta folder and type 

 

After executing the above command in command line prompt, The Schema differential script file will be createdin given path.

Step: 4 (creating batch file to execute the differential script file in Target DB)

The ‘sqlcmd’ is a commandwhich execute the sql query or script file from command prompt. So after creating the differential SQL script file we can execute it by using this command.

Syntax:

sqlcmd -S server-name –U user-name -P password -d database-name -i script-file-path -o "log.txt"

Step: 5 (creating batch file to automate the synchronization process)

I hope you understood how to compare (step:3), create sqldifferential script file (step:3)  and execute the file (step:4) in command prompt.

You can combine Step:3 and Step:4 in to one batch file and place this batch in windows scheduler and set frequency to execute.

If you want change source or target DB name, you can update it on ‘sdp’ configuration file. Refer Step: 2.