SQL Server Comparison Tool User Guide

SQL Server Comparison Tool
Next page >
SQL Server Comparison Tool User Guide
User guide
Next page >


Overview

SQL Server Comparison Tool is the ultimate solution to find differences between SQL Server tables, stored procedures, functions and entire databases.

Very often there is a need to quickly find the differences between two databases.

For example, when updates or enhancements are to be deployed, sometimes there is a need to determine what changed in user's database since the previous update. Or, after finishing development cycle the changes done to the database need to be identified and copied to the master database. Take full advantage of SQL Server Comparison Tool to quickly find the changes!

Why waste precious time on something you can easily do with the software?

Finding differences by hand can be very time consuming and error prone process. Use SQL Server Comparison Tool power to discover all changes!
SQL Server Comparison Tool is the ultimate solution to your database mainenance needs!

What makes SQL Server Comparison Tool your must-have SQL Server utility?

  • No SQL knowledge required.
  • Create your own comparison processes.
  • Let any of 3 comparison modes - single, selective and bulk - do the hard work for you.
  • Tonnes of options to customize your comparisons.
  • Analyze and compare structure of Tables, Procedures, Functions, Views, Triggers and Relationships in any of your SQL Server databases.
  • Compare data stored in tables.
  • Generate detailed reports displaying results of comparisons or analysis
  • Export data differences reports to HTML file or Excel (CSV) file (even if the computer performing analysis doesn't have Excel installed).
  • Filter objects by their ownerships.
  • Perform bulk comparisons and selective comparison.
  • Ignore selected columns during comparisons.
  • Fine tune data comparisons by specifying comparison method (record-by-record or primary key/index comparison).
  • Save ("record") comparison sessions so that they can be re-played later without need of re-entering the parameters.
  • Automate comparisons by using command line parameter.
  • Compare selected parts of object (table, procedure, etc) definitions.
  • Analysis and comparisons can be performed even on databases belonging to different servers.

SQL Server Comparison Tool is an ideal tool for DBAs and developers.

Please note that SQL Server Comparison Tool is very safe, "read-only" program. Using it will never affect any data stored in your databases. SCT is a tool designed to perform analysis and comparisons; it is not designed to change any structure or data.

SQL Server Comparison Tool supports all versions of SQL Server starting with version 7.0 till the latest version.

< Previous page Next page >


Getting Started

Please read the topics bellow to quickly get started with SQL Server Comparison Tool. The introductory topics will give you the brief overview of the SQL Server Comparison Tool concept. It requires just a couple minutes to see how it's easy and fun to analyze SQL Server databases with SQL Server Comparison Tool.

The contents of Getting Started

Adding server to SCT
Removing server from SCT
Keyboard Shortcuts
Toolbar
Getting database information


Adding server to SCT

Adding SQL Server to SCT is pretty much similar to registering SQL Server in such Microsoft tools like SQL Server Enterprise Manager or Query Analyzer
Invoke "Add SQL Server" dialog by selecting menu item Tools -> Add Server. Then just enter data into appropriate boxes and click button OK. SCT will attempt to connect to newly registered server. On successful attempt the process of adding SQL Server to SCT will be finished. In case when the connection fails, appropriate message will be displayed.
Please note that adding or removing server's info to SCT is not affecting in any way the original settings of the server.


Removing server from SCT

Removing (unregistering) SQL Server from SCT is very simple. Invoke appropriate dialog by selecting menu item Tools -> Remove Server, select server you wish to be removed, and click button "Remove".
Please note that adding or removing server's info to SCT is not affecting in any way the original settings of the server.


Keyboard Shortcuts

Action | Press
Display Help file | F1
Connect to the server | F5
Select object type | F6
Compare selected procedures | F7
Compare all objects | F8
Compare data in selected tables | F9
Display database information | F10
Copy selected object structure to the clipboard | Alt + O for Source database Alt + Y for Target database
Copy selected object structure to the file | Alt + P for Source database Alt + I for Target database
View data stored in selected table | Alt + V for Source database Alt + W for Target database
Run saved comparison process | Ctrl + F5
Display Tip of the Day | Ctrl + F1
Display About info | Shift + F1
Close the program | Alt + F4


Toolbar

The toolbar provides quick access to the most common operations and commands.
Using a mouse, you can perform tasks quickly and easily by choosing buttons on the toolbar.

Connect to the Server

1. Connect to the Server
Connects to SQL Server. Please note that before connecting for first time you have to add SQL Server to SCT.
[Top]

Select Object Type

2. Select Object Type
Allows for selection of active objects. By default the selection is set to Tables, but you can change it by expanding the list (by clicking on the small arrow displayed on the right side) and selecting one of the object types: Tables, Procedures, Functions, Views, Triggers, Relationships.
[Top]

Compare Sel. Proc.

3. Compare Sel. Proc.
Performs comparison of currently selected objects (if current object type is Tables, active Tables are being compared; if currently selected objects are Procedures, active Procedures are compared, etc).
[Top]

Compare All Objects

4. Compare All Objects
Displays dialog box which lets you specify which objects you want to compare. Let's assume that all Tables and all Procedures were selected from the list - clicking Compare button starts comparison process, which results in generating detailed report.
[Top]

Compare Data

5. Compare Data
This button works only when current Object Type is set to Tables. Clicking this button results in performing comparison of data stored in selected tables and displaying detailed report. Please note that comparison of data can be performed only if both Tables have the same structure (the same number of columns, all corresponding columns having the same data type).
[Top]

Help

Opens the SQL Server Comparison Tool help file
Please note that SQL Server Comparison Tool has optionally mouseless interface - anything you can do with mouse, you can do also by using just keyboard. For list of shortcuts see Keyboard Shortcuts section.
[Top]


Getting database information

To get information about currently selected database click the button "Database info" or press F10.
SCT will perform analysis of the database and create report with the count and list of all tables, procedures, functions, views, triggers and relationships. The report will also indicate the number of data rows contained in each table.


Structure Comparisons

SQL Server Comparison Tool offers wide range of options when comparing structure of database objects (Tables, Procedures, Functions, Views, Triggers and Relationships).
With SCT you can compare structures in two currently selected objects, in all objects of selected databases, or in selected subset of objects. Bulk and selective comparisons can be performed in Simple mode, which results in the list of objects which differ, or in Details mode, which additionally lists all the differences.
When comparing structure of tables, is it possible to specify which columns should be ignored during comparison (to do that, please right-click the text box displaying the structure if the table and click pop-up menu item 'Ignore Columns').
You can also compare only selected parts of object (table, procedure, etc) definitions. To do that please select pop-up menu item "Compare Highlighted Sections" displayed after right-clicking over the text box displaying the structure of selected object.

The contents of Structure Comparisons

Comparing selected objects
Comparing all objects
Bulk comparisons
Selective comparisons


Comparing selected objects

To compare selected objects:
  • Select the desired object type (Tables, Procedures, Functions, Views, Triggers or Relationships) using combo box on the toolbar
  • Select the objects to be compared
  • Click button "Compare Sel. Proc."
The comparison will be performed and the results will be displayed in Report window. You can export the report to HTML file or to Excel (CSV) file.


Comparing all objects

To compare all objects in both databases:
  • Click button "Compare All"
  • The dialog box will be displayed
  • Select the object types you want to be compared and click button "Compare"
  • The comparison process can take couple of minutes; during this process status of the operation will be displayed.
  • At the end of the process appropriate report will be displayed.


Bulk comparisons

You can compare all objects of given type at once, in one comparison process. To do so please select menu item Structure Comparisons -> Bulk Comparisons, then appropriate sub-menu - Simple or Details, and the type of objects to be compared.
Simple comparisons display only statistics in final report - the list of objects that differ.
Detailed comparisons display statistics and the list of differences for each object that differs across compared databases.

Examples:

Structure Comparisons | Bulk Comparisons | Simple | Compare All Tables
- the structures of all tables in both databases will be compared and the list of different tables will be displayed in final report.
Structure Comparisons | Bulk Comparisons | Details | Compare All Procedures
- all stored procedures in both databases will be compared and the list of different procedures, along with the list of differences inside each of procedures, will be displayed in final report.


Selective comparisons

You can select objects to be compared during comparison process. To do so please select menu item Structure Comparisons -> Selective Comparisons, then appropriate sub-menu - Simple or Details, and the type of objects to be compared.
After specifying type of objects to be compared - tables, procedures, functions, views, triggers or relationships - the window will pop-up displaying the lists of all objects of this type in both databases. Please select the objects to be compared and click button "OK" to continue.
Simple comparisons display only statistics in final report - the list of objects that differ.
Detailed comparisons display statistics and the list of differences for each object that differs across compared databases.

Examples:

Structure Comparisons | Selective Comparisons | Simple | Compare Selected Tables
- the structures of selected tables in both databases will be compared and the list of different tables will be displayed in final report.
Structure Comparisons | Selective Comparisons | Details | Compare Selected Procedures
- selected stored procedures in both databases will be compared and the list of different procedures, along with the list of differences inside each of procedures, will be displayed in final report.


Data Comparisons

SQL Server Comparison Tool enables you to compare data in two currently selected tables, in all tables in selected databases, or in selected subset of tables. Bulk and selective comparisons can be performed in Simple mode, which results in the list of tables which differ, or in Details mode, which additionally lists all the differences.

The contents of Data Comparisons

Comparing data in two tables
Data comparison options
Comparing data in all tables
Exporting data from table to HTML file
Exporting data from table to Excel file


Comparing data in two tables

To compare data stored in two tables:
  • Select appropriate tables in both panels (left panel and right panel)
  • Click button "Compare data"
  • Optionally, select appropriate settings in Data Comparison Options window, where you can customize and fine tune the comparison process.
  • The comparison of data stored will be performed and the results will be displayed in Report window. You can export the report to HTML file or to Excel (CSV) file.


Data comparison options

You can fine tune data comparison options by specifying extra parameters of comparison process.
This step can be skipped. To do so just click button "Continue" and the comparison process will start using default settings or the settings saved previously.
You can turn off display of Data Comparison Options screen - to do so please click menu item Tools -> Options, select tab "Data Comparison" and check off appropriate check box. When this option is set off, you can still trigger displaying the options dialog by pressing SHIFT key while clicking "Compare Data" button on the main screen.

  • Comparison Method

You can choose between two methods used for the comparison: record-by-record or primary key/index comparison. In many cases straight record-by-record comparison is fine. However, there are situations where PK comparison is better choice. Example: with record-by-record method it is possible that one extra record added at the beginning of target table could 'spoil' whole comparison process because compared records would not be matched properly. This problem can easily be overcome by using combination of appropriate comparison settings.

  • Sorting Data

    (applies only to record-by-record comparison)
You can specify the sort order to be used during comparison process - that might be helpful when data in compared tables is not sorted the same way.

  • Comparison Offset

    (applies only to record-by-record comparison)
You can specify from which record the comparison process should start. In other words, you can decide how many records at the beginning of table should be skipped (ignored) by comparison process.

  • Ignore Columns

Some columns can be excluded from comparison. This feature adds more flexibility to comparison process - the ability of relaxing the identity difference, ability of skipping columns added during replication process, etc.


Comparing data in all tables

With SQL Server Comparison Tool you can compare data in all tables with one click of the mouse - just click menu item Data Comparisons -> Compare Data in All Tables.
After comparing data in all tables the program will display summary report, which can be exported to text file or csv (comma separated values) file.


Exporting data from table to HTML file

To export data from table to HTML file:
  • Select desired table
  • Click button "View Data"
  • New window will pop up, displaying all data stored in selected table. Click button "Export"
  • Select appropriate file type (in this case select: "Hyper Text Markup Language (HTML) file")
  • Click button "OK"
  • Select the location and name of the file to be created
  • Click button "Save". The new file will be created.


Exporting data from table to Excel file

To export data from table to Excel (csv) file:
  • Select desired table
  • Click button "View Data"
  • New window will pop up, displaying all data stored in selected table. Click button "Export"
  • Select appropriate file type (in this case select: "Excel - Comma Separated Values (CSV) file")
  • Click button "OK"
  • Select the location and name of the file to be created.
  • Click button "Save". The new file will be created.


Automating Comparisons

SQL Server Comparison Tool offers possibility of saving ("recording") comparison sessions so that they can be re-played later with one click without need of re-entering all the parameters.
You can also run saved comparison processes from command line parameter.

The contents of Automating Comparisons

Saving Comparisons
Command Line Parameter


Saving Comparisons

To save the comparison process:
- click button Save present on all screens displaying results of given comparison process
- enter name of comparison process so you can identify it later
- please note that SCT automatically creates comparison Id when saving the process for first time. This Id is needed when running saved processes through command line parameter.
To re-play comparison process:
- click menu item File -> Run Saved Comparison Process (or press keys Ctrl + F5)
- select from the list the process which you want to be run
- click button "Run"


Command Line Parameter

Running saved comparison process using command line parameter is very simple. The only parameter needed to pass is Comparison Id - all other parameters of given comparison were already defined with the GUI at the time of recording comparison (so that you do not need dozens of command line switches).
Command line parameter has following syntax:
/RunSavedComp n,
where n indicates Id of saved comparison process.
To get the Id of given comparison process:
  • click menu item File -> Run Saved Comparison Process (or press keys Ctrl + F5) to display the list of all saved comparison processes
  • select given comparison process and right-click it
  • read the Id of the process from the pop-up Properties screen.
Here is an example of command line parameter:
C:\Program Files\SQL Server Comparison Tool\sct.exe /RunSavedComp 4
- C:\Program Files\SQL Server Comparison Tool\sct.exe is a path to executable file of SCT
- 4 represents the Id of the comparison process to be run
Program can be run with command line parameters in several ways, one of them is as follows:
  • click Windows "Run..." menu item (displayed after clicking "Start" button)
  • enter the full path of SCT (or drag and drop file "sct.exe" from Windows Explorer to "Run" dialog)
  • enter space after the path, followed by the text "/RunSavedComp n" (without quotation marks, "n" should be replaced with actual process Id)
  • click button "OK"


Ordering & License

You can instantly order the fully licensed version of SQL Server Comparison Tool at any time over the Internet with any major credit card, bank / wire transfer, PayPal, check or cash.
After you purchased a license, you'll receive your personal registration code and the release version of SQL Server Comparison Tool.
Please visit www.sql-server-tool.com for pricing information.

Here's what you can do with paid license of our product:

  • Remove 30 days usage limitation and get rid of the splash screen!
  • 1 year of FREE upgrades.
  • 1 year of FREE support via email. We promise prompt and courteous answers to your questions.
  • 50% discount on all future versions of SCT.
All software purchases will be available for download immediately after your purchase is complete.
We will send an email to you with important information about your order. Please make sure that “@sql-server-tool.com” is in your address book or white list so that you can receive this email.
SQL Server Comparison Tool is licensed on a per user basis. Each user who runs the program must have a license.

The contents of Ordering & License

License agreement


License agreement

Use of SQL Server Comparison Tool is subject to acceptance of the following
Copyright (c) 2002-2014 by AlfaAlfa Software Corporation
SQL Server Comparison Tool(tm) License Agreement
SQL SERVER COMPARISON TOOL VERSION 2.0 END-USER LICENSE AGREEMENT
IMPORTANT-READ CAREFULLY: This SQL Server Comparison Tool End-User License Agreement ("EULA") is a legal agreement between you (either an individual or a single entity) and AlfaAlfa Software Corporation (AS) for the AS SOFTWARE(s) identified above, which includes any associated SOFTWARE components, any media, any printed materials, and any "online" or electronic documentation ("SOFTWARE"). By installing, copying, or otherwise using the SOFTWARE, you agree to be bound by the terms of this EULA. If you do not agree to the terms of this EULA, do not install or use the SOFTWARE. If the SOFTWARE was mailed to you, return the media envelope along with the rest of the package to the location where you obtained it within 30 days from purchase.
1. The SOFTWARE is licensed, not sold.
2. GRANT OF LICENSE.
(a) Evaluation Copy. You may use the SOFTWARE without charge on an evaluation basis for thirty (30)
days from the day that you install the SOFTWARE. You must pay the license fee and register your copy to continue to use the SOFTWARE after the thirty (30) days. If you continue to use the SOFTWARE after the thirty (30) days without paying the license fee you will be using the SOFTWARE on an unlicensed basis.
(b) Redistribution of Evaluation Copy. If you are using SOFTWARE on an evaluation basis you may make copies of the evaluation SOFTWARE as you wish; give exact copies of the original evaluation SOFTWARE to anyone; and distribute the evaluation SOFTWARE in its unmodified form via electronic means (Internet, BBS's, Shareware distribution libraries, CD-ROMs, etc.). You may not charge any fee for the copy or use of the evaluation SOFTWARE itself, but you may charge a distribution fee that is reasonably related to any cost you incur distributing the evaluation SOFTWARE (e.g. packaging). You must not represent in any way that you are selling the SOFTWARE itself. Your distribution of the evaluation SOFWARE will not entitle you to any compensation from AlfaAlfa Software. You must distribute a copy of this EULA with any copy of the SOFTWARE and anyone to whom you distribute the SOFTWARE is subject to this EULA.
(c) Registered Copy. After you have purchased the license for the SOFTWARE, and have received the registration code enabling the registered copy, you are licensed to copy the SOFTWARE only into the memory of the number of computers corresponding to the number of licenses purchased. The primary user of the computer on which each licensed copy of the SOFTWARE is installed may make a second copy for his or her exclusive use on a portable computer. Under no other circumstances may the SOFTWARE be operated at the same time on more than the number of computers for which you have paid a separate license fee. You may not duplicate the SOFTWARE in whole or in part, except that you may make one copy of the SOFTWARE for backup or archival purposes. You may terminate this license at any time by destroying the original and all copies of the SOFTWARE in whatever form. You may permanently transfer all of your rights under this EULA provided you transfer all copies of the SOFTWARE (including copies of all prior versions if the SOFTWARE is an upgrade) and retain none, and the recipient agrees to the terms of this EULA. Registration code is a technical measure of copy protection. Registration codes may not be distributed to any other person or entity except as provided for in this statement. Distribution or disclosure of registration codes may constitute a violation the U.S. Digital Millennium Copyright Act (DMCA), and other applicable state or country laws
3. RESTRICTIONS. You may not reverse engineer, de-compile, or disassemble the SOFTWARE, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation. You may not rent, lease, or lend the SOFTWARE. You may permanently transfer all of your rights under this EULA, provided the recipient agrees to the terms of this EULA. You may not use the SOFTWARE to perform any unauthorized transfer of information (e.g. transfer of files in violation of copyright) or for any illegal purpose.
4. SUPPORT SERVICES. AlfaAlfa Software may provide you with support services related to the SOFTWARE. Use of Support Services is governed by the AlfaAlfa Software polices and programs described in the user manual, in online documentation, and/or other AS-provided materials, as they may be modified from time to time. Any supplemental SOFTWARE code provided to you as part of the Support Services shall be considered part of the SOFTWARE and subject to the terms and conditions of this EULA. With respect to technical information you provide to AlfaAlfa as part of the Support Services, AlfaAlfa may use such information for its business purposes, including for product support and development. AlfaAlfa will not utilize such technical information in a form that personally identifies you.
5. TERMINATION. Without prejudice to any other rights, AlfaAlfa may terminate this EULA if you fail to comply with the terms and conditions of this EULA. In such event, you must destroy all copies of the SOFTWARE.
6. COPYRIGHT. The SOFTWARE is protected by Canadian copyright law and international treaty provisions. You acknowledge that no title to the intellectual property in the SOFTWARE is transferred to you. You further acknowledge that title and full ownership rights to the SOFTWARE will remain the exclusive property of AlfaAlfa and you will not acquire any rights to the SOFTWARE except as expressly set forth in this license.
You agree that any copies of the SOFTWARE will contain the same proprietary notices which appear on and in the SOFTWARE.
7. NO WARRANTIES. ALFAALFA SOFTWARE EXPRESSLY DISCLAIMS ANY WARRANTY FOR THE SOFTWARE. THE SOFTWARE AND ANY RELATED DOCUMENTATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OR MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NONINFRINGEMENT. THE ENTIRE RISK ARISING OUT OF USE OR PERFORMANCE OF THE SOFTWARE REMAINS WITH YOU.
8. LIMITATION OF LIABILITY. IN NO EVENT SHALL ALFAALFA SOFTWARE OR ITS SUPPLIERS BE LIABLE TO YOU FOR ANY CONSEQUENTIAL, SPECIAL, INCIDENTAL, OR INDIRECT DAMAGES OF ANY KIND ARISING OUT OF THE DELIVERY, PERFORMANCE, OR USE OF THE SOFTWARE, EVEN IF ALFAALFA SOFTWARE HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. IN ANY EVENT, AS'S LIABILITY FOR ANY CLAIM, WHETHER IN CONTRACT, TORT, OR ANY OTHER THEORY OF LIABILITY WILL NOT EXCEED THE GREATER OF U.S. $1.00 OR LICENSE FEE PAID BY YOU.
9. MISCELLANEOUS. This EULA is governed by the laws of the province Ontario, Canada.
AS reserves all rights not expressly granted here.
Installing and using SQL Server Comparison Tool signifies acceptance of these terms and conditions of the license.
If you do not agree with the terms of this license you must remove SQL Server Comparison Tool files from your storage devices and cease to use the product.
Should you have any questions concerning this EULA, or if you desire to contact AlfaAlfa Software for any reason, please contact AlfaAlfa Software by electronic mail at: admin@sql-server-tool.com.
Thank you for using the original SQL Server Comparison Tool.
Web: www.sql-server-tool.com
Email: admin@sql-server-tool.com


System requirements

SQL Server Comparison Tool Minimal System Requirements

  • Microsoft Windows 8/7/Vista/XP/2000/ME/98
  • 500 MHz processor
  • 256 MB RAM
  • 50 MB free hard drive space


Technical Support

For the most recent updates of SQL Server Comparison Tool, please check its home page at
www.sql-server-tool.com
Registered users of SCT are entitled to one year of FREE support via email.
Support e-mail:
support@sql-server-tool.com
When reporting problems, please include the following information:
1. What version of SCT you are running (2.0, 1.3, etc)?
2. What version of Windows are you running (Windows 8, Windows 7, etc.)?
3. What version of SQL Server are you running (SQL Server 2012, SQL Server 2008, etc)?
4. Is the problem reproducible? If so, how?


Contacts & Credits

Product website:
www.sql-server-tool.com
Support e-mail:
support@sql-server-tool.com
Developed by:
Dariusz Dziewialtowski-Gintowt
Credits - Special Thanks To:
  • Bob Quinn
  • Jeff Carrington
  • Larry Singleton
  • Ted Clark
< Previous page