|
| |
| |
|
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.
|
| |
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 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 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.
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 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 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.
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 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.
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 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 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.
|
|
|
|
|
