A Review Of SQL Examiner
There are many reasons why there may be multiple versions of the same database within an organisation.
- There may, and indeed should be a separation of the live and development versions of the database.
- There may be multiple live deployments, for example in different offices within the same organisation.
- There may be multiple development versions of the database allowing each developer to work separately.
One of the problems arising from such separation is when not only the data within the database is different but also the objects themselves within the database are different.
Identifying the differences between different instances can be fraught with difficulty and yet such identification is essential if deployment from one environment to another is to be achieved successfully.
SQL Examiner is a tool that makes the identification and deployment of these different objects much simpler.
Installation is of the “double-click on the MSI file” variety.
I installed the package on a brand new Dell Dimension 9100 and received a warning message requesting that I upgrade the Windows Installer to version 3.1. After doing so the set up proceeded as expected.
At the end of the installation the package gives the option to create an icon on the Desktop and also the option to launch the package.
The application takes around 6Mb of disk space including sample files.
This review was carried out using MS SQL Server 2005 running on Windows XP Pro SP2.
The test was carried out using two copies of the AdventureWorks sample database.
On starting a new project the user will be faced by a dialogue box similar to the one shown below.
Key points to note is that there are three potential source types.
The server against which you wish to make a comparison.
For SQL2005 users only.
Backup / Detached Data File
For SQL2000 users only.
- Snapshot comparison works for both SQL2000 and SQL2005 users.
- Version 1.2, which should be released by the time this is published, will compare SQL Server 2005 backups.
There is a check box to act as a short cut when the databases to be compared are on the same server. Although strictly not necessary this shows an attention to detail that bodes well for the product.
The objects for comparison are hidden by default but are revealed by clicking on a “More” button.
Clicking the “Compare” button begins the comparison.
The initial comparison window
Once the comparison is complete the user will be faced by a screen divided into 3 main panes.
The object tree pane.
This can be pinned or unpinned as per Visual Studio to allow a larger area to show the object comparison and allow the object tree pane to pop-out when the user moves the mouse over the tab to the left of the screen.
The pane can be free floating or docked on either side of the screen.
Although this is a simple window once again it shows that some serious thought has gone into the user interface.
The buttons from right to left are as follows
The button gives the the option to read either or both databases.
Gives 4 options
Select all objects and select no objects respectively.
Displays the bottom panel of the object tree pane.
At this point it is worth mentioning that font face, size colour and background can be changed to suit the user. Whether you have a visual impairment or not this facility is well worth having.
Object comparison panes
The object comparison panes will be familiar to anyone who has used Visual Source Safe.
On choosing an object the code comparison panes will display similar to those shown below.
The additional lines in the source code are clearly indicated
Again it is worth mentioning that the scripts that are displayed are highly configurable.
The user can choose whether :
- Objects are surrounded by square brackets
- Filegroups are included in the script
- Keywords and/or identifiers are upper case, lower case or title case
- Constraints and indexes are ignored
- User defined types are converted to their base types.
- Dependent objects are also included in the script.
The list above is not exhaustive but gives a flavour for the options involved.
Synchronising the databases
Synchronisation can be achieved in either direction and for specific objects.
It is a minor point but when the package says “sync A to B” I took it to mean “move objects from A into B”. What it actually means is “turn database A into B”. In other words do things the opposite way around to the way that I was expecting.
In mitigation the synchronisation walks the user through a 4 step wizard that clearly outlines what is going to happen so the user would have to be rather careless to perform the wrong task. As this tool is likely to be used by a DBA one would hope that
the necessary care would be taken to make sure that the actual action was the intended action. In any case the package defaults to backing up the changing database before it carries out the changes.
The screen shot below shows the GENERATE tab for the ErrorLog table. The table only exists in Database 1.
The screen shot below shows what command would be executed if you were to sync database 1 to database 2. In other words ErrorLog would be dropped.
By default the package creates a backup of the database to be altered.
The option to take into account existing data is useful if the synchronisation involves structure changes to the data.
Step 2 provides a warning if the synchronisation process will append a field that does not allow NULL values and does not have a default value. In such a situation the package gives several options as to what to do with that column.
Step 3 provides a summary of what is to take place and allows the user to review the script again.
A point to note is that the user does not have to use SQL Examiner to carry out the synchronisation but can save the script, or for MS SQL Server 2000 users export the script to SQL Query Analyser.
Step 4 simply provides the option to rerun the comparison after the synchronisation.
Support and help facilities
The user interface is so straight forward and well thought out that it is perfectly possible to find everything without resorting to the help file.
Should the user resort to the help file they would find that it is equally thorough and well thought out.
Tulasoft do provide a support facility from the web site http://www.sqlaccessories.com/support.aspx which can also be used for product feedback and general comments.
They say that the a member of their support team will contact you within 24 hours but as I did not need the facility during the course of this review I cannot comment on this.
No matter what the product, whether it is a piece of software, a car or white goods the issues you find when using the product on a regular basis are different from those found when performing an evaluation.
I have to say that if the clarity of design and attention to detail that were apparent in my review copy can be used as an indicator I don't think many users will add
the support page to their bookmarks/favourites list in their browser!
I will rate each of the following using a scale from 1 to 5. 5 being the best and 1 being the worst. Comments are in the last column.
|Ease of Use||5||Can find everything without using the help file|
|Technical Support||N/A||Not needed during the review|
|Lack of Bugs||5||None found during the review|
|Documentation||5||Well thought out help file|
|Learning Curve||5||Clarity of user interface means that it is the gentlest of curves|
Web Site: http://www.sqlaccessories.com/
|Single User||US $199.95|
|5 User- license||US $599.95|
|10 User- license||US $999.95|
The recurring theme throughout the package is an attention to detail. Someone has obviously thought long and hard about what they want the package to achieve
and the supporting tasks that need to take place to make the task a success.
The only quibble I can make is the phraseology of the synchronise options but given the clarity of the synchronise wizard and its dialogues I would ignore this issue.
The ultimate test is whether I would spend my budget on this tool and the answer has to be an unqualified yes.