Click here to Skip to main content
15,860,859 members
Articles / Programming Languages / SQL
Article

Red Gate's SQL Toolkit Helps Automate Changes for 200 Databases

16 Mar 20053 min read 25.1K   4  
Using SQL Toolkit to allows developers and database administrators to use Red Gate's tools for SQL Server within the applications they create.

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Red Gate Software Ltd.
United Kingdom United Kingdom
Redgate makes ingeniously simple software used by 804,745 IT professionals and counting, and is the leading Microsoft SQL Server tools vendor. Our philosophy is to design highly usable, reliable tools which elegantly solve the problems developers and DBAs face every day, and help them adopt database DevOps. As a result, more than 100,000 companies use products in the Redgate SQL Toolbelt, including 91% of those in the Fortune 100.
This is a Organisation

1 members

Comments and Discussions

 
-- There are no messages in this forum --