This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.
Automate all your SQL Server comparison and synchronization tasks
Last month we told you about SQL Compare, a tool for comparing and synchronizing Microsoft SQL Server database structures. If you need to automate the comparison and synchronization of your SQL Server databases, we have a tool, the SQL Comparison and Synchronization Toolkit (SQL Toolkit) that allows you to do just this. Not only does the SQL Toolkit allow you to automate the comparison and synchronization of your SQL Server database structures, but also the data within those databases by using the engines behind SQL Compare and SQL Data Compare.
The SQL Toolkit allows you to:
- Automate routine comparison and synchronization tasks so that they happen at times that you choose.
- Achieve and/or verify replication over multiple databases.
- Automate migration processes within your development environment.
- The right to use the functionality of SQL Compare and SQL Data Compare in the installation and maintenance programs that you ship to your customers.
Using the SQL Toolkit
Following is a very basic example demonstrating how SQL Compare's functionality can be automated. To fully understand what you can do with the SQL Toolkit, you should try it out for yourself.
Creating a new project
Figure 1. Creating a new project
The first thing you need to do is create a new project. We are going to create a "Console Application". However, you could use the SQL Toolkit from within a Windows Form application, an ASP.NET application, or any other .NET application. Our console application is going to synchronize the structure of two databases, WidgetProduction and WidgetStaging, by automating the SQL Compare engine. We will be using C#, but you could choose to use Visual Basic.NET or any other .NET language.
Figure 2a. Add references Figure 2b. Added references
Next you need to add references to the SQL Toolkit DLLs which will then be shown in a list as in figure 2b above.
Figure 3. Create a license file
To be able to redistribute an application to end users, assuming you have a valid SQL Toolkit license, you need to create a license file. To do this create an empty file called "licenses.licx" and then add into it an entry for the class:
RedGate.SQLCompare.Engine.Database
We need to add a couple of lines at the start of the code to indicate that the two assemblies are being used:
using System;
using RedGate.SQL.Shared;
using RedGate.SQLCompare.Engine;
We want to compare and synchronize two databases. To do this we need to create two database objects that represent the two databases involved in the comparison:
Database db1=new Database();
Database db2=new Database();
db1.Register(new ConnectionProperties(".", "WidgetStaging"),
Options.Default);
db2.Register(new ConnectionProperties(".", "WidgetProduction"),
Options.Default);
We could choose to use databases from a previously saved snapshot, which would be useful for an offline comparison, but in this case we have registered databases from a live server.
Note the use of intellisense when you are writing code.
Figure 4. SQL Toolkit has full intellisense capabilities
Although the SQL Toolkit's primary aim is to compare and synchronize databases, we can retrieve information about database tables, stored procedures, and views using the object model (see figure 5).
Figure 5. Object model
The SQL Toolkit provides you with a deep document object model which is like an incredibly fast SQL DMO.
After registering your databases the next step is to compare them. We do this by using the "CompareWith" method:
Differences differences = db1.CompareWith(db2, Options.Default);
This returns a differences collection which contains a summary of the differences between the two databases. We can loop through this collection and print out the summary to screen:
foreach (Difference d in differences)
{
Console.WriteLine("{0} {1} {2}",
difference.Type.ToString(),
difference.DatabaseObjectType.ToString(),
difference.Name);
difference.Selected = true ;
}
We also set the selected property to "true" to indicate that we want to use each difference in the script we are going to generate in the next step.
Once we have a differences collection, we need to instantiate a work object to generate a synchronization script. We call "w.BuildFromDifferences" and pass in "true" as the last parameter since we want to run the generated script on the second database:
Work w=new Work();
w.BuildFromDifferences(differences, Options.Default, true);
This work object now represents the work that needs to be done to synchronize the databases.
The work object exposes interesting properties. For example, the "Messages" collection contains a list of messages describing the work that needs to be done, and the "Execution Block" property represents the SQL that needs to be run to synchronize the two databases.
The next thing to do is to run the script. SQL Compare provides a helper class to do this:
Utils u = new Utils();
u.ExecuteBlock(b, ".", "WidgetProduction");
The last part of the coding is to dispose of the objects we have used:
b.Dispose();
db1.Dispose();
db2.Dispose();
We do this so SQL Compare can free up any resources, such as temporary files, that it might have used.
Figure 6a. Build menu
Next we need to build our application by clicking "Build Solution" in the "Build" menu.
Figure 6b. Building your application
When we build the application we should check to ensure there are no errors. Once this is done we are able to run our application. We do this by pressing the following button:
The application will run.
Once the application has run, you will be able to see a summary of the differences, the steps to run, and the SQL that needs to be run.
Figure 8. Summary of differences and steps to be run
Figure 9. Summary of the SQL to be run
The application will then synchronize the two databases.
This is only one very basic example of what the SQL Toolkit can do. The best way for you to find out if it will suit your needs is to take a look at the help file (includes sample projects), watch our online demo, or try it out for yourself by downloading a trial version.
What’s included with the SQL Toolkit?
The SQL Toolkit gives you access to the APIs of our tools for comparing and synchronizing SQL Server databases, and the right to incorporate them in your code and distribute them in your applications. Included with the SQL Toolkit is a help file which contains:
- A full object model
- Sample projects
- Code snippets
- Clear explanations
A vital tool for developers using SQL Server databases
Automating your Microsoft SQL Server database comparisons and synchronizations is best done with the SQL Toolkit. It will save you a lot of time, and removes potential for human error from the process.
A major new version of this tool was released on December 1st. Read all about it at http://www.red-gate.com/sql/version30x_sql_toolkit.htm.
The best way to see how well this product will work for you is to download a free, fully functional trial from Red Gate’s website or “Check for updates” within SQL Bundle 3.1 and above.
For more information about the SQL Toolkit go to:
http://www.red-gate.com/sql_comparison_and_synchronization_toolkit.htm
