t-sql - save yourself, comment everything out

Do you have a "miscellaneous" script that contains numerous disparate t-sql statements that you usually run on an ad-hoc mode? If yes then do yourself (and others that depend on you) a favor: select the whole script and click on the "comment out" icon. That way, if you ever happen to accidentally "execute" without first selecting the statement(s) that you really wish to execute nothing bad will happen. Otherwise, in just seconds you may end up creating a huge mess.

What prompted this? It was the desperate face of a very good database programmer who had just "successfully" executed one of those miscellaneous scripts and completely wiped out a couple of production tables, deleted certain rows from other tables and updated a few more. He did not lose his job but those few seconds of “terror” have probably shortened his life expectancy by a few months. It would have never happened if his miscellaneous statements were all commented out.

Are there other lessons one can learn from this story? Yes, plenty of them – there is a lot that the DBA can do to prevent such things from ever happening (just do a search for sql server best practices and you will find a lot of great advice) but the goal of this was to remind you of a very simple measure that everyone who “touches” the database can easily take.

Here is the product plug-in: this was a great showcase for our xSQL Data Compare. Here is what happened next. They mounted the previous night’s backup on a verification instance and applied the logs up to the last log before the "unfortunate event". Next they used xSQL Data Compare to determine what was different and generated a selective synchronization script that basically reversed all the changes that the un-intentional execution of that "miscellaneous" script had caused. Thanks to a reliable backup scheme and to xSQL Data Compare of course, the damage was contained to a couple of hours of work with no data loss but it could have been a real disaster.

Please feel free to leave your comments here.

Automate Deployment of SQL Server Databases

Deploying SQL Server databases to clients can be a pain!

You may be a software publisher and your specialized software has been distributed to hundreds of your clients. For some of those clients you may have had to make custom changes to the database and the application to better meet their specific needs. Every time you need to send out an upgrade of your application you have to go through a laborious process of upgrading those hundreds of clients one by one. To make matters worst some of your clients have moved to SQL Server 2008, some are using SQL Server 2005 and yet some others are still running SQL Server 2000 so you have to worry about version specific scripts as well. Furthermore, your clients don't necessarily have in house SQL Server expertise to follow your technical instructions on running certain T-SQL scripts in a certain sequence etc.

Or, you may be a member of a development team or a SQL Server DBA in a big organization and after QA your application is routinely deployed to multiple locations. Deploying the database changes to all those locations is part of the time-consuming routine for each "target" you schedule the upgrade during the low activity time, you take a backup of the target database and then you start applying the change scripts. Yes, it is painful.

How can I avoid or at least ease this pain?

xSQL Builder allows you to automate the deployment of SQL Server based solutions. You can use it to generate an executable packages that contains all the information and logic required to deploy the SQL Server database to the clients� environments regardless of whether it is a first time deployment or an upgrade from a previous version. The self-contained SQL Server database deployment package can be incorporated into a setup and deployment solution or it can be shipped to clients as a separate application.

What does an xSQL Builder executable package include?

The self-contained, executable package generated by xSQL Builder contains the following components:

• depending on your choice it may contain a snapshot of the schema of the master SQL Server database, or the T-SQL creation script, or the backup of the master database.
• run-time components for performing a SQL Server database comparison and synchronization
• custom T-SQL Scripts that you may want to run before the comparison of the two databases takes place as well as custom T-SQL scripts that you may want to run after the target database has been synchronized with the master database
• logging component that maintains a detailed log of all the events and operations performed on the client's environment during the execution of the deployment package. The executable package created by xSQL Builder generates three logs on the client: the operational log, the synchronization log and the error log.
• notification component that allows you to configure the package to notify you via email in case of completion or failure of the execution of the package on the client

What does the executable package generated by xSQL Builder do on the client environment?

The self-contained, executable package generated by xSQL Builder performs the following actions on the client when executed:

• execute any pre-synchronization custom T-SQL scripts that you have defined
• in case of a new installation it may create the database on the target machine using either the embedded backup of the master database or the creation script depending on what you chose when you generated the package.
• in case of an upgrade it will perform a schema comparison of the target database with the snapshot of the master database that has been embedded in the package
• generate the synchronization script and execute it against the target database
• execute any post synchronization, custom T-SQL scripts you may have defined
• prepare and send notifications which may include the logs for your review in case of failure

In case all goes as planned your database has been deployed almost transparently to the client otherwise you will receive the detailed log that will help you determine what failed and why.

Compare and Synchronize SQL Server Databases

Why compare databases?

Whether you are a developer that uses SQL Server on the back end to support your application or a database administrator in charge of monitoring and maintaining those SQL Server databases you often have to deal with different versions of the same database. For example, you may have a development environment, a testing or QA environment and a production environment and at any given time your database is in different "states" in each of those environment. As you work on implementing changes and additions to your applications you routinely make changes on the development database like adding tables, adding columns, changing types, creating indexes, adding new and modifying existing views, stored procedures, user defined functions etc.

At different points in the development you may need to promote all the work you have done on to the QA environment so that the QA team can start "pocking holes" in the work you have done. As soon as you do that you go back to your development World and continue working on your application and making database changes as the need arises so, it won't be long before the version of the database you are working on does not match the version the QA team is testing and that does not match the version of the SQL Server database that is running on your production servers.

Any professional in this position will keep meticulous records on each version of the database so that at any given time he would be able to tell what is different from one version to the other. That's well and good but it does not take much to realize that, especially if the database is relatively big and changes relatively many, sorting through such records to figure out what changes when (let alone how to change it back if necessary) is like looking for the needle in the haystack. That's why the need to have a tool that compares the SQL Server databases and tells you what is different.
 
How do I compare two databases?

xSQL Object which you can download from this site allows you to compare the schemas of two SQL Server databases with a few clicks, regardless of the location of those databases (as long as you can access those databases). The results of the comparison are displayed on an easy to use grid that shows the objects on the top section and the differences for the selected object on the bottom section. In addition of the side by side display of the selecte objects' scripts it also provides two change scripts - one to make the object on the second database in the comparison the same as the corresponding object in the first database in the comparison and abother change script to go the other way.
 
What do I do after comparing the two databases?

After you have compared the SQL Server databases in question, knowing on which direction you wish to transfer changes (example: if you were comparing the development version of your SQL Server database with the QA version it is likely that you would want to transfer changes from the development version to the QA version) you simply click on the right button (link) and xSQL Object automatically generates the change script required to transfer those changes. The script generated is safe and version specific (this is great for cases when you have different versions of SQL Server running on different environments - for example: you have upgraded the development environment to SQL Server 2005 or SQL Server 2008 but your QA and production environments are still on SQL Server 2000). At this point you can simply review the script to ensure that you do indeed intend to make those changes on the target database and then execute the script directly from xSQL Object's interface.
 
How about the data - the content of two SQL Server databases?

So, what if you have some sort of a distributed SQL Server database or a replicated database or some other scenario where you need to compare the actual data between two SQL Server databases to make sure that the replication has worked correctly or to see what records were changes since the last backup if you are trying to perform some auditing or for some other reason? Well, we thought of that to: xSQL Data Compare allows you to compare the data, the content, of two SQL Server databases, see where the differences are and then transfer all or some of those changes from one database to the other. xSQL Data Compare comes together with xSQL Object and the corresponding command line utilities as part of the xSQL Bundle that you can download from this site.

How much am I going to have to pay for those tools?

Not much at all - in fact it is likely that you may not have to pay anything - xSQL Bundle which includes both xSQL Object and xSQL Data Compare is completely free for SQL Server Express and comes in a free Lite Edition for other editions of SQL Server.

Language a big barrier to penetration

To translate or not to translate that is the question!

For years we have been operating under the apparently false assumption that for most techies English is the common language and when it comes to software tools that target this particular segment of the population, language is not a real barrier. We looked at the number of visits to our site (http://www.xsql.com/) in the last 12 months by country and compared those to the population of each country for some of the developed countries. To make the comparison easier we normalized the numbers using visits from United States as the base. Proportionally to the population of each country for every 1 US originated visit we received:


From English speaking countries

• Ireland -> 1.78
• New Zealand -> 1.55
• Australia -> 1.31
• Canada -> 1.26
• United Kingdom -> 1.19

Non English speaking countries

• Italy -> 0.38
• Austria -> 0.26
• Germany -> 0.23
• Spain -> 0.23
• France -> 0.21
• Japan -> 0.06

Since our tools are mostly tools for SQL Server it would have been nice if we had some idea of the penetration of SQL Server in those countries but, even if we corrected for that the gap between English speaking countries and non English speaking ones is striking and clearly highlights the fact that language is indeed an immense barrier even for our target audience (software programmers and database administrators). Of course, from this it does not necessarily follow that we should all rush to translating our sites and products into 10 different languages – whether that kind of an investment would pay off or not depends on many factors but it is certainly something we need to consider.

iApple an unbelievable iBuzz TM machine

I have never seen another company that is able to come close to apple in terms of iBuzz TM or “buzz ratio” TM (defined as amount of buzz generated / value of the product being launched) – I don’t know how they do it but they do. Their approach to product launching is nothing short of “magical and revolutionary” even when their products are not.

Take the iPad for example – there is not a soul that hasn’t heard of it today, it’s unveiling easily eclipsed some “not so important” stories like the State of the Union, the Davos Forum or the Feds meeting to name a few. So, what’s so “magical and revolutionary” about the iPad? Well, it’s thin the “master” said, you can customize the background to your liking and you can turn the thing sideways… really!
So, how can Jobs get away with telling us that we will be able to change the background on this “ground breaking revolutionary” computer? Would he be able to generate this kind of buzz the next time around when he takes the iPad and makes it iPadXL? The reason says no, but, he has proven time and again that he is able to defy reason, he has the magic and can make us drool in anticipation every time!

xSQL Documenter 3 released

We have just released xSQL Documenter 3 which includes the following new features and enhancements:

• Support for Microsoft Report Server 2005/2008;
• Support for Raima RDM Server;
• Support for SVG diagrams. Now the diagrams can be rendered in all browsers, such as Firefox, Opera, Safari, and Internet Explorer;
• Option to use short names in the documentation instead of the three part names;
• Each table and view column is now added to the CHM index;
• SQL Server only: support for an extended property filter. If you use a filter, only extended properties named in the filter will be fetched.
• Microsoft Analysis Server: support for excluding XMLA and MDX code for objects.

There is no comparison in features or value between xSQL Documenter and other database documentation tools - xSQL Documenter comes way ahead on every category. Generates both compiled CHM and HTML documentation and supports the following platforms:

• Microsoft SQL Server 2000/2005/2008
• Microsoft Analysis Server 2005/2008
• Microsoft Report Server 2005/2008
• Oracle 9i and above
• DB2 8.2 and above
• MySQL 5.0 and above
• Informix IDS 10.0 and above
• Sybase ASE
• Sybase SQL Anywhere 10.0
• PostgreSQL 8.0 and above
• Microsoft Access 97 and above
• VistaDB 3.0 and above
• ENEA Polyhedra 7.0 and above
• Raima RDM Server 8.1

You can download the free trial version from: http://www.xsql.com/download/database_documenter/ The trial version does not expire but simply puts the "trial" notices throughout the documentation. Give it a try - it does not require installation - you will have the most thorough documentation ready in minutes.

Publish database changes to hundreds of clients - software publisher

This is a "how to" written with software publishers in mind but it will benefit other software development groups that may not be classified as software publishers.

Here is the scenario: you have tenths, hundreds or maybe more clients using the software you wrote that utilizes SQL Server on the backend. The first release was painless but all the subsequent releases have given you grief - you send out database upgrade scripts with clear instructions but the users just don't seem to be capable of following those instructions and then they call you..., so you are looking for a way to make this process pain free. Well, you are in luck as nowadays there is a tool for everything. Following, I will provide some guidance for each of the scenarios you may be facing.

1. If you cannot access the clients' servers from your location (most likely scenario)
1. if you have a small number of database versions on the field and each version's schema is "un-touched" - that is you (or the client) did not make any changes to it then the most efficient way to handle this would be to use xSQL Object to compare each previous version to the new "master" version and generate a sync script for each version upgrade and then use Script Executor to package the sync script and any additional scripts you may want to run on the clients' servers into a executable which can then be made part of your installation package;
2. if you have many versions out in the field and what's more critical you may have made (or allowed the client to) customizations for different clients then the approach described above would not work very well, but xSQL Builder was designed exactly with this scenario in mind. In just few easy steps you can generate an executable package that embeds the schema of the master database you wish to publish. When the executable is launched on the client's site it will compare the currently installed version with the embedded master schema and synchronize the target to the master. While doing that it will generate a detailed synchronization log and automatically email it back to you so that you know what happened when the client ran the upgrade. 
2.  If you happened to have access to all your clients' databases then you can easily manage this from your location. Use xSQL Object to generate synchronization scripts for upgrading from version x to the current version, then use Script Executor to create a deployment package - basically add all your target servers (databases) into database groups, add your sync scripts into script containers and then map script containers to databases and execute ALL with a click of a button.

If you happen to face a scenario not covered by the above options please do let us know.

Applies to:

• software publishing
• publishing database schema changes
• synchronizing clients' databases (the schemas) to a master database
• comparing and synchronizing database schemas
• executing multiple scripts against multiple databases