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>
<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>
<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>
<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.