Download a free to trial of Red Gate�s SQL Bundle Developer Edition
SQL Server 2005 is a juggernaut and little will stand in its way. Unless, of course, people find it just too time-consuming to get to grips with. Given the considerable increase in capacity, functionality and features that it delivers over SQL Server 2000, there�s always a chance that smaller companies may feel they can roll along more easily with the SQL Server 2000 installation they�ve gotten used to. Now SQL Server 2005 has grown up into a serious enterprise-level database platform it also tends to behave like one.
For the majority though, the extra power, flexibility and sophistication of the new version seem to be outweighing their concerns. In a December 2005 survey of 280 database developers and IT managers, 82% of respondents had either begun migrating their SQL Server databases up to the new SQL Server 2005 platform or were scheduled to do so within the coming nine months.
One very good reason why a lot of those polled are migrating sooner rather than later is the timely January 06 release of Red Gate�s SQL Bundle Version 4, a set of tools engineered specifically to help them make the trek upstream. Developers and DBAs can now work as productively with SQL Server 2005 as they had gotten used to doing with previous versions, thanks to this full upgrade of the market leading tools arriving within weeks of the launch of Microsoft�s platform.
The new version of SQL Bundle Developer Edition has been fully rewritten to work seamlessly with all of the new database objects, CLR assemblies and XML data types that come with SQL Server 2005 and also delivers major improvements in a host of other areas, especially in intelligent dependency tracking and automatically scripting the most efficient creation SQL. This will often require the unbinding and recreation of dependent tables in an exact sequence.
Although the new version of the SQL Bundle has only been on full release since January 23, plenty of developers have jumped at the upgrade and report considerable time savings:
�We are currently working on a SQL Server 2005 project and SQL Compare 4 has allowed us to keep our schemas, partitions and DDL triggers in line. The new version has been especially useful for comparing synonyms, which we are using to make our different environments transparent to developers. Not only does SQL Compare 4 make my job easier, its benefits are trickling down to other members of my team as well.� Aaron Bernard, Senior Data Architect, Bluestreak Inc.
�I have been using SQL Data Compare and SQL Compare with SQL Server 2005. The tools are just superb and we would be lost without them.� Gregor Suttie, SQL Server Database Developer.
In addition, the new release offers installations of the three principal tools, SQL Compare, SQL Data Compare and SQL Packager in Japanese and German versions.
The new version builds upon the tried and tested success of previous versions. Red Gate SQL Compare was released in 2000 and has, in the five years since then, fundamentally restructured the work patterns of hundreds-of-thousands DBAs and SQL Server developers. At a stroke SQL Compare and its stablemate tools SQL Data Compare and SQL Packager, removed the need to hand script database updates.
"SQL Compare has saved us an awful lot of time and code and allowed us to get our version control organized across our 40 data warehouses. We're working on automating it to ensure our versions don't creep out. SQL Data Compare has given us a first look test approach to ensure we don't get any surprises when upgrading databases. Our warehouses contain around 8 TB of SQL data so they're not trivial.� Mike Hickin, Touch Clarity
"This product has revolutionized the way I manage our distributed development teams and I'm extremely pleased with it�I am literally staggered at how much more efficient it is to perform simple data transportation with SQL Data Compare. In some cases, it is up to 10 times faster to move an entire database with SQL Data Compare than it would be with DTS." Nick Stansbury, Development Manager, Sage Partners Ltd.
The SQL Bundle tools from Red Gate really come into their own in cases where there may be a network of complex and interdependent databases needing constant updating and synchronization. A classic example is that of Leggett and Platt, a $5 billion plus global manufacturing business operating out of Carthage, Missouri, with myriad customers, including Ford, Starbucks, Target, Sony and Lear
Updates: tedious and time-consuming
Leggett & Platt has approximately 20 SQL Servers to handle the data the international facilities use to run their day-to-day business. Each server hosts around five databases. There are two trained SQL Server DBAs on hand at Leggett & Platt�s corporate office in Carthage, Mo., to help with any issues that might arise.
Updating these databases across each location used to be a daunting task for the Leggett & Platt developers. They had to manually create SQL statements and bundle them into an InstallShield application. The InstallShield file was run at each location, where a Visual Basic application created the database, another executed the SQL scripts to create the database objects, and an executable file in the command line imported the data into the tables. These hand-coded scripts left plenty of opportunities for error.
Aside from reducing the chance for errors, Leggett & Platt wanted to ensure the .NET application they created for each database could be executed by someone at each branch, even those with limited SQL experience. It was also crucial that the databases and core data be updated correctly.
An accurate, repeatable process
The best solution for Leggett & Platt was to reinvent its entire data migration process. The company now uses Red Gate Software�s SQL Toolkit to create a repeatable process for updating company databases. SQL Toolkit consists of Red Gate�s SQL Compare, SQL Data Compare and SQL Packager software, together with the ability to program directly to APIs, either from the command line or natively from another application.
�We have a small international IT department and a very heavy workload,� says Dustin Franks, Leggett & Platt project manager. �It was difficult to make time to thoroughly check the SQL statements to ensure they would all work correctly on the branch side. SQL Toolkit�s checks and balances system eliminates the worry and the guesswork.�
Franks and his group first use SQL Compare to take a snapshot of a production database, including all of the data tables. SQL Compare then identifies which tables have been changed between development and production and generates scripts to update the structure of the production database. After all of the table and schema changes have been made to the development database, tested and finalized, the database is moved to production.
For deployment at the remote sites, Franks creates an executable file using a combination of SQL Packager and InstallShield. SQL Packager creates an executable file of the final SQL database tables with data that will be distributed at the customer site, preserving the structure of the database as well as all of the dependencies within the tables. Dependencies include language entries, security items and menus Leggett & Platt establishes for user type definitions.
SQL Packager first guides users through selecting schema and data for the new database, then generates scripts to preserve dependencies. In the final step, the tool automatically packages an executable or .NET project, creating a utility for easy installation.
Franks and his team then write a .NET application for each update that will launch at the customer site. The .NET code will compare the database created by SQL Packager with the database currently in use at the Leggett & Platt branch and generate the scripts needed to update the branches database schema.
To create the final file for remote deployment, the SQL Compare snapshot, SQL Packager executable, and manually authored .NET application are compiled with InstallShield.
Support gets a boost
Once launched at the client site, the InstallShield application creates a clean database from the SQL Packager file that will be used for data synchronization. Then the .NET application compares the database snapshot from InstallShield with the data on-hand using Red Gate�s SQL Data Compare, generating a script that outlines the differences in the two databases. The script is then executed to update the branch�s data to match records in the SQL Packager executable, enabling the on-site databases to be updated without wiping out existing data.
�With SQL Compare, if one of the database transactions fails, they all fail,� says Franks. �The database is rolled back to a clean database. We used to have to repair incomplete databases with a special release or manually delete records and rerun the installation.�
Leggett & Platt�s IT support for customer databases has gotten a much needed boost with the implementation of iScala and Red Gate tools. The small workforce with a heavy workload can now concentrate more on needed updates and Visual Basic code rather than double-checking scripts and creating fixes.
To see how well the tools in the SQL Bundle Developer Edition will work for you, download a fully functional, fully supported trial from www.red-gate.com.
