Eric Brown explains how he used SQL Compare to gain control of database change management in the development of a complex business intelligence application.

I was recently Project Manager on a project designed to turn a client's "paper and Excel spreadsheet" process for management of outsourcing vendors, into a database-driven, real-time, business intelligence application.

It was a complex application, including a Microsoft SharePoint Portal Server Site and full deployment of Microsoft Business Intelligence platform, including Business Scorecard Manager, SQL Server Analysis Services and Reporting Services. Supporting the whole system were three databases:

• An OLTP database, with dozens of web services and hundreds of database objects
• A data warehouse snowflake schema with lots of views and indexes, including a partitioning scheme
• An OLAP cube with many calculated members

The server environment was divided into development, test, and production environments, and we had to move databases and code through each.  It was a requirement that the OLAP cubes, the data warehouse, and the OLTP database were consistent across each environment.

The database synchronization headache

At first, change management wasn't too difficult, as the OLTP and DW databases were relatively uncomplicated. However, as the scope of the project broadened, so the complexity steadily increased, and the change management process became ever more onerous. It was difficult not to make mistakes.

The situation was further complicated by the sheer number of people involved on the project. There were two teams of developers – one team handling the OLTP database, another the DW/OLAP – along with two test teams, and the client. There were, at any given point, as many as 14 people working on the databases and matching applications. To add to the fun, each member of the team was in a different time zone, and each developer specialized in particular groups of functionality. We had one person developing stored procedures and web services, another developing the data warehouse, another doing the OLAP cubes, and so on.

Due to time zones and the complexity of the design, the data warehouse developer was often behind with his development. The OLTP database, on the other hand, lacked a well-defined specification, and iterated many times in a given day, with the developers making adjustments to the OLTP database schema and objects on an "as needed" basis. Let's just say there were many moving parts ;).

Ultimately, we reached a point where the database change management process was becoming…well, unmanageable. Every time the server environments needed to be updated, which was often, we were spending an average of 8 hours manually changing the databases.

We needed to find a way to rein in the time cost for us (and the monetary cost for the client!) and we needed to find a much more controlled method for accepting and moving changes.

The SQL Compare cure

Whilst browsing a log shipping article on Simple-Talk, I saw a mention of SQL Compare, clicked through and downloaded it. I found the tool very easy to use and understand, and decided pretty quickly that we would adopt it as our change management tool.

The tool basically did what it said it would do: it allowed us to efficiently compare two database schemas. This was amazingly helpful when we were moving objects out of Dev and into Test and Production. SQL Compare has two modalities – the comparison mode and the synchronization mode.

By default, the comparison mode compares the entire schema for the two databases in question. We typically used this default mode of operation, as we wanted a complete synchronization of all the objects in the database. However, the options tab includes Filter Objects functionality for ignoring and including features deep into the database. You might use some of these "ignore" capabilities if you were comparing very complicated databases.

At each milestone in development, we stopped development and ran a SQL Compare comparison. The tool generates a list of:

• Objects that only exist in one database (e.g. in Dev but not in Test)
• Objects that exist in both database but are different
• Objects that are identical in both databases

Once we decided what changes to make, we'd move into synchronization mode, and run the Synchronization Wizard. The Synchronization Wizard is easy to follow. You simply deselect objects to be excluded, or leave objects selected, and proceed through to the next screens. The wizard displays the action plan, which is a summary of the changes, the modifications and warnings, if any, as illustrated in the screenshot below.

We were paranoid about changes, and found ourselves reviewing the SQL scripts. Once we were happy, we'd simply run the synchronization.

One thing I worry about in any change management tool is the ability to know what's going on. Upon completion, the Synchronization Wizard displays a report of any errors encountered. We did hit a few errors, but the messaging provided sufficient information to allow us to correct and re-run the synchronization (and the errors were usually on our part).

In the case of a production server, where you can't take it offline to make changes, the last step of the wizard allows you to dynamically open the SQL Server Query Analyzer and execute the synchronization script manually. Alternatively, you could create a SQL Agent Job and run the script in the middle of the night.

We used the SQL scripts to keep a rolling history of the changes to the databases. Additionally, we saved those scripts in a source control system. We had to do that outside of the SQL Compare tool, and I would have liked SQL Compare to have that ability natively (apparently, it's coming in the next version).

Summary

The savings in effort and money we gained from use of the tool more than offset its cost. In fact, it made purchasing the tool a "no-brainer".

The SQL Compare tool gave us the following benefits:

• Highly controllable synchronization – we could ignore all sorts of details about the database objects
• Very fast – once started, even the most complex synchronizations went quickly
• Deep filtering of the synch report results allowed us to not waste time scrolling and pecking around for the information we needed

The Dev team was excited, as it let them off the hook for documenting every single change to the database, and I was thrilled, because database change management became one less headache. The only thing I wished we could use SQL Compare for was the SQL Server Analysis Services Cubes. Additionally, because we had an unusual data set, we had to move the test data by hand, but that wasn't very hard.

In the end, the SQL Compare tool is a very powerful way to manage database object change, by providing a very straightforward, intuitive, user interface, with just the right level of options and extra features. Give it a go, next time you are on a project requiring the management of database change!

Eric Brown began his professional computing career in 1996, as a Product Manager at Multiple Zones International. He then worked for three dot-coms before ending up on the SQL Server Product Team at Microsoft, where he ran "Yukon" readiness. At one point in his early DB years, he cut the edge of SQL Server capabilities by owning a 500 GB data warehouse running SQL Server 7 and 2000. Since leaving Microsoft officially, Eric has worked on his book, SQL Server 2005 Distilled, and started an e-commerce hosting company. He now works for Quilogy as a senior consultant on the Business Intelligence National Practice. Eric has also written a column for SQL Server Magazine, and contributed extensive SQL Server content to MSDN Magazine and MSDN online library. You can contact him with questions or comments at: eric@aboutsqlserver.com.