At this moment I'm working on an application which is based on an existing SQL server database. This database is precious as it holds a big amount of proven data as well as a large amount of proven business logic in it's stored procedures and triggers. I do not want to discuss here if that is the right place to store business logic; it is there and works well. If it ain't broken..... My application will add new functionality to the database so changes are inevitable. Up till recently I had one central document to list all alterations. This document went to the sa which would do the updates. Which could be difficult; how do you want to make clear you only changed line 127 of viewX ? A better way would to update be a script.
This is where I started working with the Red Gate SQL bundle. In this post I'll explore SQL compare, one of the tools in the bundle. SQL compare compares two databases. These databases can be anywhere: on another machine, or even on two different other machines; it takes two ordinary connection dialogs to get them. Having connected SQL compare will analyze these objects in the databases
- Tables
- Views
- Stored procedures
- Users
- Roles
- Rules
- Defaults
- User Defined Data types (UDT's)
- User Defined Functions (UDF's)
- Full Text catalogs
For each object it generates
- A creation script for the object in database1
- A creation script for the object in database2
- A script to change the version in database1 to the version in database2
- A script to change the version in database2 to the version in database1
The two creation scripts are listed side by side with colored highlighting of the differences
All objects are in one big list. To get an overview you use the Status filter and object filter toolbar. The first one filters on the kind of difference : Identical, Missing, Additional or Different. The object filter speaks for itself. These filters work very well but suffer somewhat from the UI choices made for the tool. Normally you see that a toolbar button is selected by its sunken appearance. Instead SQL compare changes just the border of the button. A button with a border stands for selected, one without for deselected. No big deal but just a little hard to get used to.
Having analyzed the database and generated scripts SQL compare can also apply these changes. The nice thing is that setting which of these changes should be applied is very fine grained. Just (de-) select the checkbox.
I find myself deselecting users and roles. Usually this does not work well. Even if the login is known in the database, SQL server will not correctly recognize the database users and their roles you're trying to import. Now SQL compare can execute the script. It will fire up a wizard. The first step is extremely important. You can either change db1 to be identical to db2 or the other way round. Making the wrong choice would result in a loss of all updates in my new database :o An option you will find in all steps of the wizard is save script. It will result in a big (in my case over 64K) sql script which you can pass to the sa who can run it in (FI) SQA. The quality of the script is good. The whole database change is transacted; in case the update crashes it rolls back. Neat.
So far this might almost sound like magic. It almost is; but real magic is a miracle. What you always should do is re-compare the databases after running the update script. This is an option in the last step of the wizard as well.
In my case I found out SQL compare had missed one weird constraint. So there is some handwork left but compared to my original way of working I'm in heaven. 99% of the work is automated and I can check the results with the same tool. The output of the tools are plain sqlscripts which every dba will accept. Even if they never heard of Red-Gate. In that case they are missing something. This product is really recommended, they are a good friend of Codebetter.