Teratrax Database Compare

Teratrax Database Compare has been discontinued. Please refer to the following help file (v2.03) for details on using an existing copy of this product:

 

Help File

Teratrax Database Compare is a database comparison and synchronization tool for SQL Server. It lets you easily pick two SQL Server databases from separate servers or the same server and compare their structure (Schema) and content (Data).

Supported Database Servers

SQL Server 2005
SQL Server 2005 Express
SQL Server 2000
MSDE 2000

Supported Operating Systems

Windows Vista
Windows XP
Windows Server 2003

Teratrax Database Compare requires the Microsoft .NET Framework 2.0

Installation

The install program in Teratrax Database Compare guides you through the installation in simple and easy steps. It installs a copy of the software and creates a shortcut in the Teratrax group in the Start menu.

Schema Projects

To create a new schema project, select New Schema Project from the File menu. This will open the Schema Project dialog where you can enter a project description and define your source and destination databases. The Schema Project dialog also allows you to limit the comparison to certain types of database objects (Tables, Views, Stored Procedures, etc…). The destination database is the database that you want to change in order to make it identical to the source database. After comparing the databases, will be able to generate a synchronization script to render the destination database identical to the source database.

Once the data project is created, you will see a list of all object types alongside the total number of objects that are found in both source and destination databases. The list also indicates the number of objects that exists only in source or destination. Objects that do not exist in destination database will be later created by the synchronization script. Likewise, objects that do not exist in the source database will be later dropped by the synchronization script.

After saving the project, you can run the comparison by selecting Compare from the Actions menu. Once the project have been compared, select Sync Script (Project) from the Actions menu to generate the SQL script needed to render the destination database identical to the source database. If you want to exclude or include a specific object before running the comparison, expand the project node and navigate to the sub node representing the object type (Tables, Views, Stored Procedures, etc…).

Synchronizing Database Structure (Schema)

Once the project has been compared, the Sync Script (Project) item in the Actions menu will be enabled. Select this menu item to generate the synchronization script that will make the destination database identical to the source database. If you want to view the differences between the source and destination databases, navigate to the node representing each object type and expand it. Note that you cannot expand these nodes before you run compare. Under each object type node, you will see five sub nodes:

  • Identical Objects: objects found in both source and destination databases that are identical. These objects will not be part of the synchronization script.

  • Alter In Destination: objects found in both source and destination databases that are different. These objects will be dropped and recreated in the destination database by the synchronization script.

  • Create In Destination: objects found only in the source database. These objects will be created in the destination database by the synchronization script.

  • Drop From Destination: objects found only in the destination database. These objects will be dropped from the destination database by the synchronization script.

  • Excluded From Project: objects that are encrypted are not supported in Teratrax Database Compare. This list also includes DDL triggers if one database (source or destination) is a SQL Server 2000 database and the other is a SQL Server 2005 database that contains DDL triggers. DDL triggers are new in SQL Server 2005. They are not supported in SQL Server 2000. DDL triggers will be included in the project if both source and destination databases are SQL Server 2005 databases.

The synchronization script should always run on the destination database to make identical to the source database.

Schema Project Dialog

The Schema Project dialog allows you to create a new schema project or edit an existing schema project to compare database structural differences. the following is a list of items in the dialog:

 

Description

The description associated with the project acts like a name to identify the project in the Project Explorer. Descriptions are unique in the Schema Project node in Project Explorer.

 

Source Database

  • Server: server name or IP address of the SQL Server instance where the source database resides (For example, ServerName or ServerName\<instance name> for named instances).
  • Windows Authentication: configures Teratrax Database Compare to connect to the source database using a your Windows user account.
  • SQL Server Authentication: configures Teratrax Database Compare to connect to the source database using a SQL Server login and password.
  • Login: the login name used if SQL Server Authentication is selected.
  • Password: the password used if SQL Server Authentication is selected.
  • Database: the source database or master database. Teratrax Database Compare will examine this database to determine how the destination database should look like.

To drop down the list of databases using the keyboard, tab into the database dropdown and press Alt+Down Arrow.

 

Destination Database

  • Server: server name or IP address of the SQL Server instance where the destination database resides (For example, ServerName or ServerName\<instance name> for named instances).
  • Windows Authentication: configures Teratrax Database Compare to connect to the destination database using a your Windows user account.
  • SQL Server Authentication: configures Teratrax Database Compare to connect to the destination database using a SQL Server login and password.
  • Login: the login name used if SQL Server Authentication is selected.
  • Password: the password used if SQL Server Authentication is selected.
  • Database: the destination database. This is the database that you want to render identical to the source database.

To drop down the list of databases using the keyboard, tab into the database dropdown and press Alt+Down Arrow.

 

Objects Included

The Objects Included pane allows you to limit the object types that you wish to compare. Object types are Tables, Views, Stored Procedures, User Defined Functions, DDL Triggers, User Defined Types, Users, Database Roles, Rules, and Defaults.

Data Projects

To create a new data project, select New Data Project from the File menu. This will open the Data Project dialog where you can enter a project description and define your source and destination databases. The destination database is the database that you want to change in order to make it identical to the source database. After comparing the databases, you will be able to generate a synchronization script to render the destination database identical to the source database.

Once the project is created, you will see a list of tables that are ready to be compared. Teratrax Database Compare maps the source tables to the the destination tables based on table name automatically so you can create and compare a project in a few clicks. To map a source table to a different table name in the destination database, use the dropdown in the destination column to select a different table. Tables that cannot be compared (For example, a table that lacks a primary key) will be flagged with a warning sign to indicate that they will not be compared.

After selecting the tables you want to compare, you can run the comparison by selecting Compare from the Actions menu. Once the tables have been compared, select Sync Script (Project) from the Actions menu to generate the SQL script needed to render the destination database identical to the source database.

Data Synchronization

Once the project is compared, you can click on Sync Script (Project) to generate the necessary SQL statements to synchronize the destination database and make it identical to the source database. If you want to view the differences between the source and destination databases, navigate to the node representing each table and expand it. Note that you cannot expand these nodes before you run compare. Under each table, you will see four sub nodes:

  • Identical Rows: table rows that are identical. These rows will not be part of the synchronization script.

  • Update In Destination: table rows that are different between source and destination databases. UPDATE statements will be included in the synchronization script to make these rows identical in the destination database.

  • Insert In Destination: table rows found only in the source database. INSERT statements will be included in the synchronization script to add these rows to the destination database.

  • Delete From Destination: table rows found only in the destination database. DELETE statements will be included in the synchronization script to remove these rows from the destination database.

The synchronization script should always run on the destination to make identical to the source database. Rows are compared based on primary keys to determine whether they are identical or different. Tables that are found in the source database but not in the destination database will not be listed. These tables can be detected in a schema projects.

Data Project Dialog

The Data Project dialog allows you to create a new data project or edit an existing data project. the following is a list of items in the dialog:

 

Description

The description associated with the project acts like a name to identify the project in the Project Explorer. Descriptions are unique in the Data Project node in Project Explorer.

 

Source Database

  • Server: server name or IP address of the SQL Server instance where the source database resides (For example, ServerName or ServerName\<instance name> for named instances).
  • Windows Authentication: configures Teratrax Database Compare to connect to the source database using a your Windows user account.
  • SQL Server Authentication: configures Teratrax Database Compare to connect to the source database using a SQL Server login and password.
  • Login: the login name used if SQL Server Authentication is selected.
  • Password: the password used if SQL Server Authentication is selected.
  • Database: the source database or master database. Teratrax Database Compare will examine this database to determine how the destination database should look like.

To drop down the list of databases using the keyboard, tab into the database dropdown and press Alt+Down Arrow.

 

Destination Database

  • Server: server name or IP address of the SQL Server instance where the destination database resides (For example, ServerName or ServerName\<instance name> for named instances).
  • Windows Authentication: configures Teratrax Database Compare to connect to the destination database using a your Windows user account.
  • SQL Server Authentication: configures Teratrax Database Compare to connect to the destination database using a SQL Server login and password.
  • Login: the login name used if SQL Server Authentication is selected.
  • Password: the password used if SQL Server Authentication is selected.
  • Database: the destination database. This is the database that you want to render identical to the source database.

To drop down the list of databases using the keyboard, tab into the database dropdown and press Alt+Down Arrow.

User Interface

The main window of Teratrax Database Compare contains two panes: The Project Explorer pane and the Items pane on the right side of the window. The Project Explorer allows you to navigate through schema and data projects and edit your projects to include or exclude certain items from the project. It also allows you to get information about the results of the comparison and whether there are any items that cannot be compared. Depending on the node selected in the Project Explorer, the Items pane will display information relevant to the selected node.

Main Menu

File Menu

  • New Schema Project: Opens the Schema Project dialog where you can create a new schema project and specify the server name and database names for both the source and destination databases.
  • New Data Project: Opens the Data Project dialog where you can create a new data project and specify the server name and database names for both the source and destination databases.
  • Edit Project: Opens the project dialog for you to modify the definition of the project. The name of the project cannot be changed after it has been created.
  • Delete Project: Deletes the project from the Project Explorer and from disk. Projects in Teratrax Database Compare are persisted so that you can revisit them at any time in the future. You can reclaim disk space by deleting the projects that you no longer need and keeping the important ones. Data projects usually use more disk space than schema project.
  • Exit: Closes Teratrax Database Compare.

 

View Menu

  • Toolbar: Hides or shows the main toolbar.
  • Status Bar: Hides or shows the status bar located at the bottom of the screen.

 

Tools Menu

  • Refresh: Refreshes the Items pane.
  • Include: Checks an object for inclusion in the project. You cannot include an object that has a warning icon next to it.
  • Exclude: Removes an object from the project. Removed objects will not be compared.

 

Actions Menu

  • Compare: Starts the project compare process for the objects included in data and schema projects. You can compare a project directly after creating it by highlighting the project name in the Project Explorer and selecting this menu item.
  • Sync Script (Project): Generates the scripts necessary to synchronize the destination database with the source database for data and schema projects. Synchronization scripts should always run on the destination server. A project needs to be compared before you can generate its synchronization script. Once a project is compared, highlight the project name in the Project Explorer then select this menu item.

 

Help Menu

  • Help: Opens the help file.
  • Check For Updates: Allows you to check if there are any updates and service packs available for Teratrax Database Compare.
  • Product Home Page: Opens the home page of the Teratrax Database Compare website.
  • About: Displays information about Teratrax Database Compare.