Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
Comparing Two SQL Server Databases
Last updated Mar 28, 2003.
The technical professionals working in Database Management have multiple disciplines such as T-SQL coding, database design, security and platform issues, disaster recovery and others. You can look at the tasks asked of these professionals from two vantages: data tasks and management tasks. Data tasks involve things like programming and Business Intelligence, and management tasks involve things like backups and security.
No matter what the discipline, however, there is one need that most every database professional deals with at some time in their career: comparing two SQL Server databases to find changes or differences. There are many reasons that this task keeps coming up. In some cases the technical professional works in a development shop, either internally or commercially. The need here is pretty obvious — you have to be able to track the changes between product releases to upgrade one software version to the next. Making these change scripts and keeping the order and precedence straight can be very difficult.
Even if you don't develop in your shop, you may have to keep a copy of the schema lying around in case you need to write reports against the data in the commercial products you have that use SQL Server as a back end. When you get an upgrade to that product, it might break the reports you've written if the schema changes. Having a way to compare the schemas against each other gives you ability to track and fix your reports if that happens.
You also might be in a support or consulting role. If you've dealt with users much, you already know that the first question you ask when you hear about a problem is, "Was it working before? What changed?" and you also know the answer is "Yes. And nothing has changed. It just quit working." I've visited many sites where "nothing" has changed, and yet I find massive alterations to a commercially "sealed" database.
So it would seem with all these obvious needs to track database changes that it would be a common practice. Development professionals have a multitude of change management tools they use to track and control their code. It's even built-in to several design studio tools to integrate source-control into the development process. But it is not as common to see the schema of a database checked into or out of that source-control system. Many people, even technical professionals, believe that the database is a sealed product, something that can't or won't be altered by the end user. But it happens all the time.
Relational database systems aren't off the hook in this process either. Most don't offer any native way to track changes to the structure of the database, in a comprehensive way. Most of the time you don't "version" a database. You can enter a number into a table to do so, but the users are often free to edit that table at will, if they have DBA rights, so you really can't trust that number. You're still left with a need to compare two databases for differences.
There are commercial products that you can use to compare databases. The two I've used are Red-Gate's SQL Compare, and Idera's SQLConfig. Both work great, and the Red-Gate solution only costs a few hundred dollars (US) to buy. These are ideal because you just make a few selections and click a "Go" button. The scripts that each tool creates work almost every time, and they even keep the precedence and order correct automatically. Both will even implement the change with the click of a button. If you decide to purchase a solution, read on to find out the basic way they work and how you should use them.
If you decide to write your own comparison tool, you have several choices. In any case the basic process follows the same outline:
- Read the database parameters and database objects
- Script the Data Definition Language (DDL) statements such as CREATE, DROP and so forth for each object to a file or memory location
- Repeat the process for the second database
- Order the objects to line up with each other
- Run a text comparison tool to find any differences
- Create DDL scripts to implement the changes
- Order the DDL scripts
It looks difficult, but up until the last two steps it's really quite easy to do. You don't have to buy anything, and in some cases you don't even have to code anything. Let's take a look a few methods you have to follow the outline.
First, you need to read the database objects. You can do that with Enterprise Manager in SQL Server 2000. You just right-click each object and then select All Tasks, then Generate SQL Script:
Of course, right-clicking each object and creating scripts on the screen is a bit too much work to do if you have more than a few objects. Fortunately, there's a much easier solution. In Enterprise Manager, just right-click the name of the database and click All Tasks and then Generate SQL Script:
You have multiple options that you can set in each tab. In the second tab you set formatting options to include things like descriptive headers and CREATE and DROP options:
In the last tab you can select options to create permissions and indexes:
SQL Server 2005 works in much the same way, but the same selection (All Tasks and then Generate SQL Script) brings up a full Wizard-driven interface with even more options:
Even though there is a wizard that will generate scripts for you, there's an easier way. In a previous series I explained how to use the Data Management Objects (DMO) engine and some simple scripts to call the same process that Enterprise Manager uses. You can use those scripts to create any kind of file you want and call any or all of your database objects. After all, why compare user-created tables to your commercial product? You can use DMO or the newer, more powerful Server Management Objects (SMO) in SQL Server 2005 to do the same thing. Whether you call the scripting engine manually or by using a little coding you can create your scripts.
So that takes care of steps one and two — getting the objects and saving them out to a script. For step three you just repeat the process on the other database, or change your scripts to point to the other database name.
For step four, if you use the scripting method you have more control over the process and can line up the objects automatically by calling the objects to be scripted in the same order. If you use the graphical tools, you'll need to ensure that the order of each object is the same, otherwise the next step won't work properly.
After you've created your scripts, you need to compare each line to see if there are any differences. I normally use a free text-comparison tool such as SED, but I'm an old UNIX guy and I like the command line. If you're more comfortable with a graphical tool, there are several out there, some for free and some not. There are a couple of potential issues here. For one, if you got your objects out of sequence, you'll get all sorts of false-positives. Lots of lines won't match if you're off by just one. The other problem is in the robustness of the comparison. You need to make sure that case doesn't matter, that it doesn't check for white space and that sort of thing.
When you get your differences file, which hopefully isn't very long, you can create your ALTER, DROP or CREATE statements to bring the second in line with the first. You'll have to be careful here, because changing a column might affect the data it contains or it may violate a constraint or relationship.
Informit Articles and Sample Chapters
I've reviewed some of Red-Gate's other tools here. In that review I talk a little more about the perils of database versioning.
You can also do a lot of this comparison right in Transact-SQL code. It's a bit of work, but you can learn more here.