Introduction
Companies talk all the time about their data and information being "mission-critical," "strategic" and "available 24/7," but reality often doesn't match the claims. SyZLaB, a Norwegian company that develops and sells web publishing and content management systems, doesn�t have the luxury of unsubstantiated claims. SyZLaB's whole business is predicated upon making sure that its 200 SQL Server databases, ranging in size from 10 to 800 MB, are accessible around the clock to nearly 250 customers.
In addition to accessing public information on the web, SyZLaB's customers can store critical documents in SQL databases on Intranet sites. Changes and updates to the approximately 100 tables and 250 stored procedures contained in each of the SQL databases are performed at varying intervals from once a week to once every two months.
These updates used to involve hours of manual scripting to compare a single database to its corresponding image on a different server � 200 separate comparisons in all. Now SyZLaB systems developer Petter Andreassen relies on the SQL Comparison and Synchronization Toolkit (SQL Toolkit) from Red Gate � software that allows him to automate synchronization of all 200 databases with just one script.
"Just a Click of a Button"
The SQL Toolkit allows developers and database administrators to use Red Gate's (http://www.codeproject.com/redir.asp?id=698) tools for SQL Server within the applications they create. The software includes functionality from SQL Compare and SQL Data Compare, for comparing and synchronizing the structure and contents of SQL Server database objects.
The SQL Toolkit permits developers to embed the tools' functionality in their application installation wizards or maintenance programs to automate and schedule comparison and synchronization tasks, eliminating the need for GUI input.
"Prior to using the SQL Toolkit, I had to manually track all the changes and generate scripts to compare each of the databases every time we made an update," Andreassen says. "Now I have significantly reduced the amount of time I spend tracking changes. With just a click of a button, all the databases are compared to one another and a graphical list of changes is generated."
Automatic Comparison and Update
First, Andreassen develops and implements any changes to the main database on SyZLaB's development server. When updates are required, he uses the SQL Compare component of the Toolkit to detect discrepancies between the development database and the reference database on the staging server.
Next, Andreassen migrates all changes to the staging server. Rather than manually scripting update processes for each individual database using GUI input, he wrote a Visual Basic program to access the SQL Toolkit and automatically run the comparison on all 200 databases.
Using ActiveX Data Objects (ADO), a Microsoft application program interface, Andreassen gains access to the databases to execute commands from the SQL Toolkit. After ensuring that everything is running properly, he repeats the process to upgrade the live production server.
"Since our databases act primarily as storage for web solutions, it is critical that our customers have access to the data 24 hours a day, seven days a week," says Andreassen. "It is also paramount that the information contained in all of the databases be up-to-date and accurate. We are now able to migrate changes across all of our databases more quickly and reliably than ever before. This efficiency would have been completely impossible without the SQL Toolkit."
To find out how well the SQL Toolkit will work for you, download a fully functional trial from Red Gate�s website.
