Friday, January 22, 2010

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

No comments: