Simplifying database deployment

---

Download xSQL Compare Bundle, starting from $0!

---

In every discussion I have about the deployment process with other developers or project managers, the same sentence is said over and over again:

Every software development team must have an automated deployment process.

The benefits of automation are obvious to almost everyone, but, for those that are not familiar with the process, they can be summarized to the following statements:

1. Deployment becomes less error-prone.
2. No special knowledge is required to perform the deployment because this knowledge is embedded into the system.
3. Developers can focus on writing code and developing new awesome features
4. Adding new deployment targets is very simple
5. Frequent uploads / releases.

However, if you look at the actual number of teams that have actually automated deployment, you'd be surprised by how small it really is. If deployment automation is such an indisputable necessity, how come most development teams shy away from it? The reason is that, at first sight, it seems like the overhead of setting up and configuring the process is too big to justify the potential benefits. While I don't personally agree with this, I find it very hard to convince teams to automate their deployment simply by explaining the benefits or telling them that the overhead is really not that big. So, in this article I've opted for a slightly different approach. Rather than making an argument in favor of switching to a fully automated deployment right away, I think that simplifying / automating only some parts of the deployment process, makes for a smoother and easier transition.

Let's have a look at how one would simplify the database deployment in this process. Suppose you have a web based transaction processing system that is used by multiple companies. Each company would have its own database on their server so every time the database structure is changed, a change script needs to be run on each database to synchronize their schemas. If there are just one or two databases, doing this manually is not really a problem. But when that number starts to grow, this task becomes very tedious and it's quite possible that the person in charge of the task will make a mistake. Thankfully, there are many tools that greatly simplify this process and one that I personally like to use is xSQL Script Executor. It allows you to build script packages that you can execute in any number of servers / databases. Lets see how this is done. I'm using SQL Server databases in this example, however, the process is exactly the same with the other databases engines supported by Script Executor which are MySQL, DB2 and SQL Server Compact. The only difference is the way the connection is specified.

The first thing to do is to create a new project in which you add a database group (right click on the panel and then click "Add database group"). In this group, I will add all the databases that will be used by right-clicking on the group name and clicking "Add database" which will show the dialog below.

I'll be using the NORTHWIND database in this demo so I named the database group Northwind. Then, I proceeded to add all the databases on which I will be executing the script. Here is the structure of the database group:

All of these databases are currently empty. I used xSQL Schema Compare to compare the NORTHWIND database with one of the empty databases, in order to generate the change script for the empty databases, which will be saved in a .sql file. Next, this file will be added to the 'Scripts' panel in Script Executor. Every script needs to reside in a container so first I created a Script Container named 'NorthwindScripts' by right-clicking on the Scripts panel and choosing Create a new container from the menu. This is where I added the script file generated by Schema Compare as shown in the picture below:

All that is left now is to configure the package mappings and then we're all set to run the script. This is done by clicking Package > Configure. This is where you specify which scripts will be run on each Database group. Since there is only one Database group and one Script container they will automatically be mapped together, however you can easily modify the mappings in cases when there are multiple Database groups and Script containers (refer to the online help). The package configuration looks like this. 

Before I execute the scripts on the Northwind database group, note that you can further customize which scripts are executed on the databases in a database group by checking / unchecking the check boxes in the panel to the right. Then, by clicking the 'Execute' button, all the checked scripts are executed on the databases to create the Northwind database objects on the empty databases I created earlier. As you can see from the screen shot below, the Northwind database's objects are created on the Northwind database on SQLServer2016 which was one of the Servers added to Script Executor.

The last thing that I want to cover in this article is how you can automate the entire database deployment process by using the command line versions of xSQL Schema Compare and Script Executor. Here is what you would do:

1. Generate an XML file that has the configurations for a schema comparison between the development database and one of the live databases. Instead of the synchronization, specify an option on the XML to have the change script saved in a .sql file. Keep in mind to add the Synchronize="false" attribute so that Schema Compare Command Line won't excecute the script (this will be done by Script Executor). Here is how that XML file would look:
   

   <?xml version="1.0" encoding="utf-8" ?>
   <CommandLineParameters xmlns="http://www.xsql.com/sqlschemacmd.xsd">
     <LeftDatabase>
       <SqlServer>(local)</SqlServer>
       <DatabaseName>Left_DB</DatabaseName>
       <TrustedConnection>true</TrustedConnection>
     </LeftDatabase>
     <RightDatabase>
       <SqlServer>(local)</SqlServer>
       <DatabaseName>Right_DB</DatabaseName>
       <TrustedConnection>true</TrustedConnection>
     </RightDatabase>
     <CommandLineSettings>
       <SchemaScriptFile>[PathToSQLFile]</SchemaScriptFile>
       <SchemaLogFile>log.txt</SchemaLogFile>
       <SchemaWarningsFile>warnings.txt</SchemaWarningsFile>
       <ErrorLogName>error.txt</ErrorLogName>
       <CompareSchema Direction="RightAsLeft" Synchronize="false"></CompareSchema>
     </CommandLineSettings>
   </CommandLineParameters>
   

2. Then, create a project in the Script Executor Interface (as explained in this article), where you add all the databases that you want to be updated and the script file whose path you specified on the XML file as in the example above (if the file does not exist, just create an empty .sql file). Here is how the batch file would look:

   "C:\Program Files (x86)\xSQL Software\SQL Server Comparison Bundle v9\xSQLSchemaCmd.exe" PathToXMLFile
   "C:\Program Files (x86)\xSQL Software\Script Executor\ExecCmd.exe" /p:PathToProjectFile
   

Simply by executing these two commands you can transfer any changes from the development schema to any number of live databases you wish.

So there you have it, with xSQL Script Executor and just a few simple steps you can greatly simplify and automate a quite tedious part of the deployment process.