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.