SwisSQL DBChangeManager

If you are a DBA or a Database developer how many times have you needed to compare your schemas between your development, staging and production servers? Apart from comparing, you may need to apply changes to various servers. As there is no native support from SQL Server you may will to consider a third party tool such as DBChangeManger.

Installation and Configuration

Hardware Requirements

Hardware

Minimum

Recommended

Processor

P III – 800 MHz

P4 – 1.0 GHz

RAM

256 MB

512 MB

Disk Space

100 MB

200 MB

Software Requirements

Supported Platforms – SwisSQL DBChangeManager is platform independent as it is a Java application and supports the following operating systems

· Windows 2000 Server / Professional (with Service Pack 6)

· Windows 2003 Server / Workstation

· Windows XP (with Service Pack 2)

· Windows Vista

· RedHat Linux 7.x, Mandrake, Mandriva, Fedora

Supported Browsers – SwisSQL DBChangeManager Client requires one of the following Web browsers to be installed in your system for access

· Internet Explorer 6 and above

· Mozilla Firefox 1.5 and above

Installation is relatively straightforward, the only difference with a standard installation is that it asks for a port number for IIS. By default that port number is 9090 but you can change this during the installation.

Features Tested

After installing DBChangeManager, you will need to execute DBChangeManager from Start->Programs or from the Desktop Shortcut which will be created as part of the installation. First it will start a DBChangeManager service, after the service is started the default internet browser configured in the system will be launched to connect to the client interface.

Next is to login to the DBChangeManager, the default login is dba/dba but this can easily be changed.

The web based client interface (first of its kind amongst the other third party tools available in the market) makes the tool accessible from anywhere through a Web Browser.

Let’s build a small business case. Imagine that we have released a database for production and just after release, the production database and development database should be identical. After the release we have made changes to the development database. Now, the time has come to for another release. One simple way of doing this is to simply transfer the development database to production by means of backup and restore. However, most of the time you cannot do this as this will destroy all the current data and no customer is happy to enter all his data again. The most scientific way of doing this is, is to apply only the changes by means of a script or application. These changes can be either new objects, modified objects or dropped objects. When applying a new script, it should ensure that the current constraint should be maintained. In case, you need to drop an object and recreate it for any reason, you will need to make sure that after your modification the previous data should be there as it was there before applying the script. This is by no means an easy task. We will see how DBChangeManager can help us in above scenario.

In this example advwrk_production is the database that is currently in production and advwrk_development is the database in development where we have made several changes and we need to apply those changes to the advwrk_production database.

Compare Databases

When you click New Comparison, the following page will appear for entering source and target databases which you are going to compare.

In the resources tab, three options are available. They are database, snapshot and script. This means that you can compare your source and target resources which are databases, snapshots or even SQL scripts.

Comparing between two databases is most common and most used option. You can compare databases on two different servers with different authentication, which can be either Windows, or SQL Server. Also you can compare SQL server databases for 2000 and 2005 versions. I have used SQL Server 2005 for this review.

A database snapshot is a read-only, static view of a database. You can create a snapshot by giving a snapshot name and database name and its credentials. The importance of this snapshot is that you can do the same comparison for the snapshot treating it as a database and also do it offline without the need for live database connectivity.