Databases are no static objects. Most databases constantly change on a more or less regular basis. Detecting changes and synchronizing two copies of a database is a tedious and no easy task. You can either do it the hard way by reviewing both copies all by yourself object by object or you can make use of some very specialized tools that are made for just this purpose. ApexSQL Diff is one of the few tools in this niche market. And a strong competitor in this market it is as we will see.
ApexSQL DiffThe remainder of this extended spotlight will cover installation of ApexSQL Diff and a review of its key features.
Installation, System Requirements, supported SQL Server versions|
Software Prerequisite: |
MDAC 2.8 (or higher) |
|
Operating System: |
Windows 2000. |
|
CPU: |
450 MHz or faster. |
|
RAM: |
256 MB (512 MB recommended). |
|
Hard Disk Drive Space: |
30 MB free disk space. |
As you can see are the system requirements for ApexSQL Diff quite moderate and almost any modern PC should be able to run the software. You can download the software as a 30-days trial version from the ApexSQL homepage. The download is just about 21 MB in size. For this spotlight I have used the latest publicly available version of ApexSQL Diff which is 2005.10. You can find this version on the product homepage. Note that the software prerequisites mentioned above are not included in the download package. If they aren’t already installed on your machine, you need to download and install them manually. Here are the links to these downloads:
Installation of ApexSQL Diff is no big deal. It is completely wizard-driven, straightforward, and takes only a few minutes, so I won’t need to get into any details here about this process. Once the installation is completed, you are ready to run and explore ApexSQL Diff.
Using ApexSQL Diff in Structure Mode
When you start ApexSQL Diff the “Project Wizard” appears. ApexSQL Diff is working project-oriented. All information about a project is stored in one single XML file with a custom extension. ApexSQL Diff supports the comparison of both structure and data of an SQL Server database and thus projects can either be data or structure comparison projects. For the purpose of this review I will create a structure comparison project and explain most features of ApexSQL Diff using this mode and then switch to data comparison mode and explain only the differences when you run the software in this mode.
One of the first steps in the Project Wizard is the selection of what to compare in this project. That means what databases or snapshots on which server should be used in the comparison. ApexSQL Diff supports the comparison of live databases and database snapshots. Snapshots are literally a snapshot of a database structure at a given point in time. These snapshots are stored in a binary file format on the file system, so that you do not need to have physical access to the server on which the database actually resides, as you can move a snapshot to any location you like and still be able to use it in a comparison. You can compare any possible combination of database and snapshot with each other, which gives the tool a very great flexibility and a broad field of potential usage. When you compare databases, they can either be on the same SQL Server instance, different instances on the same server, or on separated servers.
Before going any further, I think I should explain some terms that you will read throughout this review quite often. Using the ApexSQL Diff terminology, each project has a “Source” and a “Destination”. The “Source” part in a comparison process is that side that typically contains the extensions and modifications, while the “Destination” part is that side which is missing these extensions and modifications and is about to be updated.
As the above screenshot illustrates, you choose the data source type and provide your login credentials.
A smart little feature is the “Clone” button. A click on this button copies the login information you have provided in the Source row to the Destination row, effectively simplifying the login procedure.
One of the clear highlights of ApexSQL Diff to me is the numerous options and settings it offers. You can tweak and tune almost every aspect of your project. Look at the screenshot above and the one below.
There is hardly an option I can think of that is missing. And these are just the general options and settings of a project. There are many more to come when you actually come to select the objects to be compared. I will return to object selection and filtering in just a moment.
Let’s have a closer look at some selected options I believe to be quite interesting.
The first one is “Ignore Column Order”. When you check this option, you instruct ApexSQL Diff to ignore the ordinal position of a column in a table during the comparison. Columns are matched solely by their names. Ever so often you see people asking in communities, how they can add a new column at a specific position in a table. Not that I think there are any really valid reasons for this wish anyway, but when you use ApexSQL Diff there are even less reasons for it.
The next option I would like to explain a bit more detailed is the “Ignore Indexes” option. It is not hard to guess that you use this option when you do not want indexes to be considered during a structure compare project.
As you also can see from the screenshot above, there are a few options marked as checked by default, while others are not. ApexSQL Diff does this automatically for you when creating a new project. You can change this preselected options at any time later on if you want to.
Last option I want to show you is the “Ignore Replication Triggers” option. If you have implemented replication in your environment, you will appreciate this option.
ApexSQL Diff supports a schema/user mapping. It automatically detects if you run the comparison against a SQL Server 2000 (or below) database or a SQL Server 2005 database and adjusts its display. That is, when you use SQL Server 2005, you can map schemas, while when you use earlier versions of SQL Server you map users. As you can see from the above screenshot, I’m using SQL Server 2005 for this spotlight.
Now, what is schema mapping? Well, suppose you have a large database in which not all objects are owned by DBO (user or schema doesn’t matter here anyway). I don’t want to go into a discussion if I think this is desirable or best practise, but just take it as it is. Suppose further that in your development environment every developer is member of the sysadmin group. When you now want to deploy the changes to your production environment in which every object is owned by the user/schema “ApexSQL” you encounter several problems. Any manually generated change script is quite prone to errors as you would need to take care that you include the different object owner before you apply your changes. To avoid this risk right from the start, you can take advantage of the “schema mapping” feature offered in ApexSQL Diff.
This feature allows you to link distinct users/schemas in Source and Destination. In my example above I have mapped the schema “dbo” in my Source database to the schema “ApexSQL” in the Destination database by selecting the users/schemas to be matched from the dropdown boxes.
The “Object Filter” feature is another highlight in ApexSQL Diff. The above screenshot demonstrates that the software fully supports most of the new object types of SQL Server 2005. There are multiple levels of object filtering in ApexSQL Diff. On the first level you can decide if an object type should generally be included in the comparison process or not. If you have decide to include an object type, say tables for example, on the second level you can now decide within the set of all tables in your “Source” database which ones to include.
In the grid on the right you see all table objects in your database. You can now individually decide which of these tables should be included and which one to be left out. ApexSQL Diff offers you to filter objects that match certain patterns. Just type in the pattern to be matched and the tool displays those objects that match these patterns.
In the above example, I have typed in the pattern “m*T”. The result is one match “MyTable”. Checking the table ensures that it is included in the comparison process.
Excurse: As you can see from the above screenshot, I use a rather simple database for this review. Actually it is just populated with a few tables. The DDL for one of these tables you can see in the above screenshot. MyTable is populated with 2,048 rows. Nonetheless I have tested ApexSQL Diff against another test database with several thousands of different database objects. It performs quite fast even on such databases. However I would recommend breaking down comparison projects into a set of smaller subprojects. It can considerably reduce run time and at the same moment increase the maintainability of such a project as a whole.
Once the wizard has collected all information, ApexSQL Diff goes to work and compares Source and Destination of your project.
The result of the comparison process is displayed in the applications’ Main Grid. As you can see has ApexSQL Diff has detected that the table “MyTable” is missing in the Destination database.
A look at the Difference Viewer shows the DDL for the MyTable table object. As there is no pendant for this object in the Destination object the right half in the Difference Viewer is empty. Later on when we will change the table in the Source, I will show you how the Difference Viewer then looks like.
As the table is missing in the Destination database, we want to synchronize both databases. This can be achieved via the context menu of the Main Grid.
This invokes the “Synchronization Wizard”. From the above screenshot you see that even at this point you can change direction of the synchronization. That means that you can switch Source and Destination of the synchronization, if wanted. Additionally you can choose to wrap the soon-to-be generated change script in a transaction to ensure that, when an error somehow occurs during script execution, the script is rolled-back and your database is back in the state before the script started. Another aspect that underpins this security aspect is that ApexSQL Diff allows you to include a BACKUP command for the database before the change script is executed. Now, you don’t need to be a safety freak to would want to have a good known most recent backup of any database before any change to the database is done.
ApexSQL Diff offers you to include the object owners in the generated script. Why? Well, think of it this way. When you include the owner of an object, there is no ambiguity anymore. It is clear for SQL Server and there is no room for interpretation anymore. I believe that it should be best practise to take advantage of this feature.
Actually SQL Server is supposed to keep track of dependencies between the objects of a database itself; however the system table sysdepends that is designed for this purpose isn’t always reliable. So, relying on this table and its information is more or less an adventure. ApexSQL Diff comes along with its own proprietary dependency parser, that doesn’t reply on sysdepends. In the above screenshot you can see that you can instruct ApexSQL Diff to search for dependencies for the objects to be synchronized and also include these additional objects in the change script.
You can have a detailed look at the steps that will be carried out by the generated script. As my example is fairly simple, there are only two steps to be performed. Create the table and then add the PRIMARY KEY constraint. In more complex real world scenarios, you can inspect the script either in order of execution or grouped by object. Interestingly ApexSQL Diff also displays warnings about potential risks associated with the actions being performed. In my example there are no warnings. However, suppose the table already existed in the Destination and we have added just a column and a DEFAULT constraint to the Source. The action to be carried out would look something like this:
As you can see the list of actions is considerably longer than before. On the “Actions by Object” tab the list looks like this:
And finally the warnings displayed by ApexSQL Diff:
I think these warnings are a quite useful feature as it seems that far too many ad-hoc changes to databases are still performed without being aware of the full consequences that these changes might mean. From my own experience in the communities I am always amazed how naive many people perform such changes to their databases and later on wonder when something goes wrong how on earth this could happen.
As I’ve mentioned a little bit earlier, once the synchronization process has taken place, I can see the DDL for MyTable in both panes (Source and Destination) of the Difference Viewer.
Now, suppose you have added another column to the Source database. I’ve named the column “mysecond”. After running the comparison again, the Difference Viewer is displaying the above screen. ApexSQL Diff highlights parts in which Source and Destination differ from each other in a different font color so that you can easily see these differences.
ApexSQL Diff offers also reporting functions. From the above screenshot you can see, what options and settings can be included in the report generation
The output from a report is a HTML file that can be viewed with a web browser. Additionally you can export the output to an XML file for further analysis in some other applications such as Microsoft Excel.
As much as you can customize your projects, you can tailor ApexSQL Diff to your needs on a general level.
The Difference Viewer is a third-party Add-in in ApexSQL Diff. You can use either this one or any other available component. In that case, you just have to provide the path to the executable file of that component along with the required arguments, such as filename.
You can customize the generated output. Available options are: VB Script and T-SQL. That means that the output is either executable via the Scripting Host or from any editor capable of issuing statements against an SQL Server database, such as the built-in SQL editor in ApexSQL Diff, SSMS, or QA. If you opt for T-SQL output you can decide which editor to be launched when scripts are to be displayed.