1. Introduction
Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
Although SQL Server Replica Server does a very good job, we do need a customized tool, especially for large database.
SQL Server Management Objects (SMO) is objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications.
Database replication includes database objects and data. We can copy database objects and insert data directly between two SQL servers. But it’s not flexible. The best way is generating SQL creating scripts for database objects and insert scripts for data.
2. Scripting Database Schema Objects
2.1 Reference SMO Assemblies
- C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
- C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
- C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
- C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll
- C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll
2.2 Connect to Source Database and Create Scripter Instance
string connStr;
SqlConnection connection = new SqlConnection(connStr);ServerConnection sc = new ServerConnection(connection);
Server s = new Server(sc);
s.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(Table), "IsSystemObject");
s.SetDefaultInitFields(typeof(View), "IsSystemObject", "IsEncrypted");
s.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject", "IsEncrypted");
s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
Scripter scripter = new Scripter(s);
2.3 Generate Database Creation script
ScriptingOptions options = new ScriptingOptions();
options.DriAll = true;
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.AppendToFile = false;
options.ToFileOnly = true;
options.WithDependencies = false;
options.ContinueScriptingOnError = true;
scripter.Options = options;
options.IncludeIfNotExists = true;
options.ScriptDrops = true;
options.FileName = dbCreateFile;
scripter.Script(new Database[] { db });
options.IncludeIfNotExists = false;
options.ScriptDrops = false;
options.AppendToFile = true;
scripter.Script(new Database[] { db });
2.4 Script Objects
if (db.Schemas.Count > 0)
{
List<Schema> schemas = new List<Schema>();
foreach (Schema schema in db.Schemas)
{
if (schema.IsSystemObject)
continue;
schemas.Add(schema);
}
options.IncludeIfNotExists = true;
options.ScriptDrops = true;
scripter.Script(schemas.ToArray());
options.IncludeIfNotExists = false;
options.ScriptDrops = false;
scripter.Script(schemas.ToArray());
}
if (db.Tables.Count > 0)
{
List<Table> tbls = new List<Table>();
foreach (Table t in db.Tables)
{
if (t.IsSystemObject)
continue;
tbls.Add(t);
}
DependencyTree tree = scripter.DiscoverDependencies(tbls.ToArray(), true);
DependencyWalker depwalker = new Microsoft.SqlServer.Management.Smo.DependencyWalker();
DependencyCollection depcoll = depwalker.WalkDependencies(tree);
tbls.Clear();
foreach (DependencyCollectionNode dep in depcoll)
{
if (dep.Urn.Type != "Table")
continue;
string tName = dep.Urn.GetAttribute("Name");
string schema = dep.Urn.GetAttribute("Schema");
var tbl = db.Tables[tName, schema];
if (tbl == null)
continue;
tbls.Add(tbl);
}
options.IncludeIfNotExists = true;
options.SchemaQualifyForeignKeysReferences = true;
options.ScriptDrops = true;
scripter.Script(tbls.ToArray());
options.AppendToFile = true;
options.IncludeIfNotExists = false;
options.ScriptDrops = false;
scripter.Script(tbls.ToArray());
} options.AppendToFile = true;
if (db.UserDefinedTableTypes.Count > 0)
{
UserDefinedTableType[] utts = new UserDefinedTableType[db.UserDefinedTableTypes.Count];
db.UserDefinedTableTypes.CopyTo(utts, 0);
options.IncludeIfNotExists = true;
options.ScriptDrops = true;
scripter.Script(utts);
options.IncludeIfNotExists = false;
options.ScriptDrops = false;
scripter.Script(utts);
}
if (db.Views.Count > 0)
{
List<View> views = new List<View>();
foreach (View v in db.Views)
{
if (v.IsSystemObject)
continue;
if (_ignoredViews.Contains(v.Name))
continue;
views.Add(v);
}
options.IncludeIfNotExists = true;
options.ScriptDrops = true;
scripter.Script(views.ToArray());
options.IncludeIfNotExists = false;
options.ScriptDrops = false;
scripter.Script(views.ToArray());
}
if (db.StoredProcedures.Count > 0)
{
List<StoredProcedure> procedures = new List<StoredProcedure>();
foreach (StoredProcedure p in db.StoredProcedures)
{
if (p.IsSystemObject || p.IsEncrypted)
continue;
procedures.Add(p);
}
options.IncludeIfNotExists = true;
options.ScriptDrops = true;
scripter.Script(procedures.ToArray());
options.IncludeIfNotExists = false;
options.ScriptDrops = false;
scripter.Script(procedures.ToArray());
}
if (db.Version >= 9 &&
db.CompatibilityLevel >= CompatibilityLevel.Version90)
{
if (db.Triggers.Count > 0)
{
DatabaseDdlTrigger[] triggers = new DatabaseDdlTrigger[db.Triggers.Count];
db.Triggers.CopyTo(triggers, 0);
options.IncludeIfNotExists = true;
options.ScriptDrops = true;
scripter.Script(triggers);
options.IncludeIfNotExists = false;
options.ScriptDrops = false;
scripter.Script(triggers);
}
}
When scripting tables, dependency walker to be used to find the dependency between table. Then use dependency to script tables with the right order.
The DependencyWalker
object is a tool to perform scripting operations that involve dependencies such as identifying dependent relationships. The tool and converts this output to a list.
3. Technical Highlights of WPF SQL Replication Tool
WPF SQL Replication Tool is a wizard tool to help you replicate SQL Server database to different database server. This tool first generates scripts (database schema script, data scripts), then run these scripts to create database, create all database objects and insert data.
3.1 Extended WPF Toolkit Wizard
Extended WPF Toolkit provided an extensive collection of WPF controls, components and utilities for creating WPF applications. The wizard control of Toolkit makes extremely easy to build a wizard.
<xctk:Wizard FinishButtonClosesWindow="True">
<i:Interaction.Triggers>
<i:EventTrigger EventName="Finish">
<i:InvokeCommandAction Command="{Binding CloseCommand}">
<i:InvokeCommandAction.CommandParameter>
<System:Boolean>True</System:Boolean>
</i:InvokeCommandAction.CommandParameter>
</i:InvokeCommandAction>
</i:EventTrigger>
<i:EventTrigger EventName="Cancel">
<i:InvokeCommandAction Command="{Binding CloseCommand}">
<i:InvokeCommandAction.CommandParameter>
<System:Boolean>False</System:Boolean>
</i:InvokeCommandAction.CommandParameter>
</i:InvokeCommandAction>
</i:EventTrigger>
<i:EventTrigger EventName="Next">
<!---->
<cm:InteractiveCommand Command="{Binding NextCommand}" />
</i:EventTrigger>
<i:EventTrigger EventName="PageChanged">
<i:InvokeCommandAction Command="{Binding PageChangedCommand}" CommandParameter="{Binding RelativeSource={RelativeSource Mode=FindAncestor, AncestorType={x:Type xctk:Wizard}}, Path=CurrentPage.Name}" />
</i:EventTrigger>
</i:Interaction.Triggers>
<xctk:WizardPage x:Name="introPage"
Title="Welcome to WPF SQL Replication Wizard Tool"
Description="This Wizard will walk you though to import and export database.">
<xctk:WizardPage.ExteriorPanelContent>
<Image Source="Resources/DBImportExport.png" Width="256" Height="256"/>
</xctk:WizardPage.ExteriorPanelContent>
</xctk:WizardPage>
<v:SelectSourceDB x:Name="selectSourcePage" PageType="Interior"
Title="Choose a Data Source"
Description="Select the source from which to copy data."
NextPage="{Binding ElementName=dbScriptPage}"
PreviousPage="{Binding ElementName=IntroPage}"/>
<v:CreateDBScriptPage x:Name="dbScriptPage" PageType="Interior"
Title="Generate Script"
Description="Genereate DB Schema script, global data script and selected user script."
NextPage="{Binding ElementName=selectDestinationPage}"
PreviousPage="{Binding ElementName=selectSourcePage}"/>
<v:SelectDestinationDB x:Name="selectDestinationPage" PageType="Interior"
Title="Choose a Destination"
Description="Specify where to copy data to."
NextPage="{Binding ElementName=runPage}"
PreviousPage="{Binding ElementName=dbScriptPage}"/>
<v:RunDBScriptPage x:Name="runPage" PageType="Interior"
Title="Run Script"
Description="Run script on destination server"
NextPage="{Binding ElementName=summaryPage}"
PreviousPage="{Binding ElementName=createDBScriptPage}"/>
<v:SummaryPage x:Name="summaryPage" PageType="Interior"
Title="Summary"
Description="List execution result of all tasks"
CanFinish="True"/>
</xctk:Wizard>
3.2 Delegate Command
An ICommand
whose delegates can be attached for Execute(T)
and CanExecute(T)
. It also implements the IActiveAwareinterface
, which is useful when registering this command in a CompositeCommand
that monitors command's activity. Delegate command class is implemented in Prism.
I don’t want to reference Prism to make this tool complicated. So provides an easy implementation of Delegate Command.
public partial class DelegateCommand<T> : ICommand
{
private readonly Action<T> executeMethod = null;
private readonly Func<T, bool> canExecuteMethod = null;
private List<WeakReference> _canExecuteChangedHandlers;
public DelegateCommand(Action<T> executeMethod)
: this(executeMethod, null)
{
}
public DelegateCommand(Action<T> executeMethod, Func<T, bool> canExecuteMethod)
{
if (executeMethod == null && canExecuteMethod == null)
throw new ArgumentNullException("executeMethod", Resources.DelegateCommandDelegatesCannotBeNull);
this.executeMethod = executeMethod;
this.canExecuteMethod = canExecuteMethod;
}
public bool CanExecute(T parameter)
{
if (canExecuteMethod == null) return true;
return canExecuteMethod(parameter);
}
public void Execute(T parameter)
{
if (executeMethod == null) return;
executeMethod(parameter);
}
bool ICommand.CanExecute(object parameter)
{
return CanExecute((T)parameter);
}
public event EventHandler CanExecuteChanged
{
add
{
WeakEventHandlerManager.AddWeakReferenceHandler(ref _canExecuteChangedHandlers, value, 2);
}
remove
{
WeakEventHandlerManager.RemoveWeakReferenceHandler(_canExecuteChangedHandlers, value);
}
}
void ICommand.Execute(object parameter)
{
Execute((T)parameter);
}
protected virtual void OnCanExecuteChanged()
{
WeakEventHandlerManager.CallWeakReferenceHandlers(this, _canExecuteChangedHandlers);
}
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1030:UseEventsWhereAppropriate")]
public void RaiseCanExecuteChanged()
{
OnCanExecuteChanged();
}
3.3 Interactive Command
You should know event trigger can invoke a command. But how pass event arguments to the command? There is no built-in way. We have to subclass its parent (abstract) class: TriggerAction<DependencyObject>.
public class InteractiveCommand : TriggerAction<DependencyObject>
{
protected override void Invoke(object parameter)
{
if (base.AssociatedObject != null)
{
ICommand command = this.ResolveCommand();
if ((command != null) && command.CanExecute(parameter))
{
command.Execute(parameter);
}
}
}
private ICommand ResolveCommand()
{
ICommand command = null;
if (this.Command != null)
{
return this.Command;
}
if (base.AssociatedObject != null)
{
foreach (PropertyInfo info in base.AssociatedObject.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
{
if (typeof(ICommand).IsAssignableFrom(info.PropertyType) && string.Equals(info.Name, this.CommandName, StringComparison.Ordinal))
{
command = (ICommand)info.GetValue(base.AssociatedObject, null);
}
}
}
return command;
}
private string commandName;
public string CommandName
{
get
{
base.ReadPreamble();
return this.commandName;
}
set
{
if (this.CommandName != value)
{
base.WritePreamble();
this.commandName = value;
base.WritePostscript();
}
}
}
#region Command
public ICommand Command
{
get { return (ICommand)GetValue(CommandProperty); }
set { SetValue(CommandProperty, value); }
}
public static readonly DependencyProperty CommandProperty =
DependencyProperty.Register("Command", typeof(ICommand), typeof(InteractiveCommand), new UIPropertyMetadata(null));
#endregion
}
3.4 Reactive extension
The Reactive Extensions (Rx) is a library for composing asynchronous and event-based programs using observable sequences and LINQ-style query operators. Using Rx, developers represent asynchronous data streams with Observables, query asynchronous data streams using LINQ operators, and parameterize the concurrency in the asynchronous data streams using Schedulers. Simply put, Rx = Observables + LINQ + Schedulers.
GetSourceDatabasesCommand = new DelegateCommand<ConnectionSetting>(x =>
{
LastError = string.Empty;
IsBusy = true;
var t = new Task<List<string>>(() =>
{
return GetDatabases(x);
});
t.ToObservable().ObserveOnDispatcher().Subscribe(list =>
{
SourceDatabases = list;
IsBusy = false;
ReportError();
});
t.Start();
});
4. Workflow of WPF SQL Replication Tool
4.1 Welcome
4.2 Choose Source Database
Choose a database which you want to export. Please tick "Import data from source database" option.
We choose "AdventueWorks" as an example.
4.3 Generate DB Schema Script
First select an output folder which is the place scripts output to. Tick "Create Schema" option if you want to generate database schema scripts.
Ok. Now do the work. Click "Generate script" button.
When it completes, you can see the SQL files generated in output folder.
4.4 Choose Destination Server
Choose a database which you want to import. Specify the server name and access authentication. For example, if you want to export to "test" of local SQL Express, the blow is the screenshot. Please note you have to click "Get Default Path" button to get the data file path. This tool doesn’t delete the existing database. Make sure the new database name doesn’t exist on the server if you want to create database.
4.5 Run Script
Tick "Create Database" option to create the database from scratch, and create all objects.
Click "Run immediately" to start import job.
When it completes, "AdventureWorks2" is created successfully with all objects.
4.6 Summary of Tasks
After all works done, you finally get Summary page. The summary page gives you an overview about all tasks you’ve done. Also you can open log file to check.
5. Conclusion
In this article we go through how to use SMO to copy database schema to other server, and how to integrate with WPF and MVVM pattern to implement a light replication tool. I’ll introduce how to export and import data in next article.