SQL Server Directory - YOUR Microsoft SQL Server directory for Microsoft SQL Server 6.5, 7.0, 2000 and 2005 (Yukon).
SQL Server Directory - YOUR Microsoft SQL Server directory for Microsoft SQL Server 6.5, 7.0, 2000 and 2005 (Yukon).
 
Home ] Up ] FAQ ] Links ] Privacy ] Search ]
SQL Server Directory - YOUR Microsoft SQL Server directory for Microsoft SQL Server 6.5, 7.0, 2000 and 2005 (Yukon).
Categories
SQL Books
Consultants
SQL Certification
SQL Downloads
SQL Education
SQL Jobs
Data Mining
Magazines
Product Reviews
Products
Reporting Services
SQL Server 2005
SQL Server 64 bit
SQL Server Katmai
Web Resources
Wise Scripts


Top MS SQL Ads
Transcender
MCDBA Directory
SQL Server Dir.
Transact SQL Dir.

Top JOB Ads
CareerBuilder
Monster
Chicago Jobs
Senior Jobs
Microsoft SQL Server 2000 Developer Edition - LESS than $50!
MSSQL 2000 less $50

Comparison of 12 Microsoft SQL Server 2000 Schema tools

revised 12/7/04 - reviewed on 9/10/04 by Mark Wehmhoefer

Bottom line

The only SQL Server schema compare tool to produce a production ready SQL Server database schema upgrade script and a Microsoft Word document that was acceptable for our solution was SQL Compare by RED Gate.

Change Manager by Embarcadero was a close second in the SQL Server database schema compare tool competition but could not produce a Microsoft Word document in a format desired by our client. However, Change Manager can generate database schema upgrade scripts and HTML reports for many different databases including SQL Server, Oracle, IBM DB2, Sybase and many more.

AdeptSQLDiff by Adept SQL Tools has lots of promise because of it's GUI. If Adept SQL could improve this tool in the critical areas like product speed and database schema update script generation, it could knock off one of the other SQL Server schema compare tool competitors.

Problem

The SQL Server database schema compare tool selected must:

Successfully create a production database schema upgrade script to convert an existing production database to the new beta version of the database. The tool would need to find 130+ table differences and 1,000 plus column differences.
Generate a SQL Server database schema report in Microsoft Word or HTML format that listed the differences between a production SQL Server database and the beta database being distributed. At the time, clients received a cut and paste Microsoft Word document generated from several custom sql scripts. But it was difficult to keep the Microsoft Word document up to date during the entire beta development process of a new SQL Server database. The cut and paste Microsoft Word document would take anywhere from 2 to 4 hours for each beta release of the database and software. With 20 plus beta releases planned, too much time was wasted on creating a new Microsoft Word document for the database schema changes.

Be easy to setup and learn to be used by multiple DBAs. Project was close to general acceptance and little time was available for a learning curve.

Final database schema upgrade scripts and reports generated with little or no manual corrections needed.

Solution

Review 12 SQL Server database schema compare tools to determine if any of the tools could produce a Microsoft word document or HTML document that would be accepted by our client. Of course, the SQL Server database schema tool needed to automate the entire process from the current cut and paste operation.

All 12 SQL Server database schema compare tools were reviewed in 2 days. Less than 1 hours was spent on any of the SQL Server schema compare tools. The tool had to work with little hassle or it was on to the next tool.

Each of the reviewed SQL Server schema tools has a free evaluation download available.

Most of the reviewed SQL Server database schema compare tools would correctly generate a SQL Server upgrade script that would copy data into a temp table and move the data into the new table as needed.

Just about all of the SQL Server schema compare tools would visually show the changes.
Just about all of the SQL Server schema compare tools would also create an update script for the production database to be later upgraded.

SQLCompare v 3.1.7.218 by Red Gate Software Limited

SQL Compare was extremely fast during the SQL Server database compare.

SQL Compare had options to generate the database schema upgrade scripts from the beta database to the production database or vice versa.

SQL Compare correctly generated a SQL Server database upgrade script that could be used in a production environment. Correctly generated ALTER TABLE statements with ALTER COLUMN that would speed up the production database upgrade.

SQL Compare has excellent options to generate HTML or RTF database schema reports. Client was sent the SQL Compare generated Microsoft Word document in RTF format s to consider as a replacement to the cut and paste schema reports in use now. Client and internal managers and developers comments received included: 'Definitely looks good" and "I think this is exactly what customers would want to know.'

SQL Compare can used on SQL Server only.

While SQL Compare is the top pick for the SQL Server database schema compare tool we were looking for, I would like to see Red Gate look at AdeptSQLDiff to see how several other schema compare options could make this tool even better.

Embarcadero Change Manager v 2.50 build 206 by Embarcadero Technologies

Embarcadero Change Manager has been used in production on a daily basis for over 3 years in order to maintain several versions of production databases. While it was the favorite to win this competition, it did not win.

Embarcadero Change Manager was average speed during the SQL Server database compare.

Embarcadero Change Manager correctly generated a SQL Server database upgrade script that could be used in a production environment. Correctly generated ALTER TABLE statements with ALTER COLUMN that would speed up the production database upgrade.

Embarcadero Change Manager can only generate a summary report as a txt file or HTML file for database schema changes.

Embarcadero Change Manager could not create the RTF file report that was required by the client.

Embarcadero Change Manager can used on many different databases including SQL Server, Oracle, IBM DB2, Sybase and many more.

AdeptSQLDiff v 1.6 build 22 by Adept SQL Tools

AdeptSQLDiff was somewhat fast during the SQL Server database compare.

When I first ran this SQL Server database schema compare tool, I thought the output results were exactly what I wanted. Later I closely compared the generated sql server upgrade script to SQL Compare from Red Gate Software and Embarcadero Change Manager. It did not generate the database upgrade script correctly.

AdeptSQLdiff generated an ALTER COLUMN statement when this scenario occurred: the original database column name DESC1 was renamed to XX_DESC1. Then a new column DESC1 was added to the end of the table. AdeptSQLDiff did not have an option to keep the columns in exact order.

This SQL Server database schema compare tool could not generate an RTF or HTML report.

Speed Change Manager v 1.2.0.0 by Imceda Software

AdeptSQLDiff was somewhat slow during the SQL Server database compare.

AdeptSQLDiff could not generate the database change script. The tool stopped with an error message when trying to generate the database change script.

Imceda Software support responded quickly to get more info on the error message. But I was never sent a resolution to the issue. I assume this issue is still in the support queue for resolution?

XSQL Object version 1.0.5.0 by xSQL Software

XSQL Object was very, very, very slow during the compare.

When XSQL Object completed the database schema comparison, nothing was displayed and then the work station locked up.

XSQL Object was also somewhat hard to setup the SQL Servers and databases to be compared.

DB SynchroComp 3.2.1 (Build 170) by e-dule Software

DB SynchroComp was very, very, very slow during the SQL Server database compare and during the SQL Server database schema upgrade script creation.

DB SynchroComp generated an update script could not have been used to update a production database due to numerous errors in the generated database schema update script.

DB SynchroComp did have a nice compare history option that could be used to save setup options automatically.

dbMaestro v ??? by Extreme Technologies

Only this SQL Server database schema compare tool needed a reboot after setup. The tool then required to create a table on the SQL Server database before the tool would work.

This tool testing was stopped at that point.

SQLDBcompare v1.3.004.0 by Best Soft Tool Inc

SQLDBCompare tool was somewhat slow in speed during the SQL Server database compare.

SQLDBCompare di not create a database schema update script. It wanted to apply the database schema changes automatically to the production database.

SQLDBCompare generated database compare reports that were capable of limited use.

SQLDBCompare would not be useful for a production environment where the database schema update script needs to be reviewed and tested before implementation.

CDB Database Comparator v 3.1.2807.4 - dbBalance® (formerly known as GlobalTech Software Ltd.)

Only SQL Server database schema compare tool to require ODBC connections to be made for each database to be compared. Even when all the databases and the software were located on the same SQL Server.

CDB Database Comparator was somewhat slow in speed during the SQL Server database schema compare.

Generated database schema update scripts and html reports were crippled in the evaluation copy so this tool could not be fully evaluated.

SQL Delta v 2.1 Build 3 by The Australian Software Company Pty Limited

SQL Delta was very, very, very slow during the SQL Server database compare.

This database schema compare tool was cancelled after 15 minutes of activity when trying to create the database upgrade script.

MSSQL Sync Toolkit Lite v 2.3 (build 1194) by Bizkit

MSSQL Sync Toolkit has an annoying nag screen that pops up fairly often.

MSSQL Sync Toolkit was somewhat slow in speed during the SQL Server database compare.

SQL Server database schema upgrade script generated would always generate a table conversion into a temp table instead of generating alter table statements. In a production environment, this tool could cause the SQL Server database schema upgrade to run for hours.
----------------------------------------

Mark Wehmhoefer is a full-time Data Base Administrator with a Fortune 50 healthcare company. He is the webmaster for several websites including: SQL Server Directory, MCDBA Directory and Front Page Help.

Mark has earned these Microsoft certifications: MCDBA, MCSE and MCSE + I. Mark has worked with Microsoft SQL Server since version 4.2 and has worked in various capacities in Information Technologies since 1974. 

 


Home ] Up ] FAQ ] Links ] Privacy ] Search ]

Born: 10/20/02         Aged: 11/25/06

Business: B2B Referral, Industrial Supply Deals, Office Supply Deals

Career: Back To School Mall, Chicago Job Resource, Chicago Job Talk, Diversity Job Resource,
Executive Job Resource, First Job Resource, Freelance Job Resource, Seasonal Job Resource, Senior Job Resource

Hobby: Tradin Stuff, Wild Birding

Shopping: Health Care and Fitness, Just Best Gifts, Just Clearance Sales
Just Comparison Shopping, Just Outlets, Luxury Matters, Value Buyers

Technology: FrontPage Help, MCDBA Directory, SQL Server Directory
Transact SQL Directory, WinFS Directory