Simplifying database deployment

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.

Read More

Script Executor is now FREE for personal use

Script Executor is now free for personal use (fully functional) - no credit card required, just download, install and enjoy. Script Executor allows you to organize your t-sql scripts into containers, organize your servers/databases into groups, map script containers to database groups and deploy with one click. At the end of the deployment it generates a clear report showing the results of the execution of each script on each target - a great tool that every database administrator and developer should have on his/her arsenal.

Script Executor supports SQL Server, MySQL and DB2.

Read More

How to create a stored procedure on multiple databases

I noticed this particular question asked on one of the SQL forums the other day, however, this same question can apply to any database objects, like how to create a function on multiple databases, how to create a view on multiple databases, how to create a table on multiple databases, etc. If you think about it just for a bit you will realize that in fact the generic question that covers all those particular cases and more is: how to execute a t-sql script against multiple databases.

Well the best, safest and most efficient way for executing or deploying t-sql scripts on multiple databases is to utilize xSQL Software's Script Executor tool. Here is the breakdown of the time you would need to spend to accomplish your task:

• Download and install Script Executor -> 2 minutes max
• Create a database group and add your servers and databases into the group -> 30 seconds per server
• Create one or more script containers and add your t-sql scripts to those containers -> less than 2 minutes depending on where the scripts are and how you might need to organize them
• Map scripts to databases and set execution priorities -> 2 to 3 minutes depending on how complicated your deployment scenario is.
• (optional) Create a deployment package -> 10 seconds (you would create a deployment package if you wish to executed the scripts from a machine where you might not have the Script Executor tool installed)
• (optional) Create a batch file that executes the deployment package created above OR that executes a saved deployment project from the command line -> 2 minutes
• (optional) Create a scheduled task that executes the batch file -> 1 minute

So, in about 10 minutes you will have created an automated job that deploys the scripts you want to the servers and databases you want, when you want! Now whenever you might wish to deploy one or more scripts that you have created to those databases all you need to do is drop the scripts to the folder(s) to which the Script Containers created above are pointing to and you are done - your scheduled task will take care of the rest.

 

Download Script Executor now and see what you have been missing.

Read More

A better way to license xSQL database deployment tools

Today we are introducing a new, more efficient and much more affordable way to license our database deployment tools, SQL Schema Compare, SQL Data Compare, xSQL Builder and Script Executor - instead of purchasing a perpetual license for each tool you can now purchase a one year Silver Subscription that includes all four tools.

How is the subscription better? The answer is simple - it is a lot cheaper initially, $399 versus $1,497, and the cost differential gets better every year, subscription renewal is only $199 versus the yearly maintenance cost of $250 for the 4 tools.

I have a Comparison Bundle license, can I switch to a subscription? Yes, if you have a Comparison Bundle license you can purchase a Subscription Renewal for only $199. You will need to provide the Comparison Bundle license.

Read More

To automate or not to automate

For every routine task we do there is a tool out there that would help reduce the time and effort required to complete the task as well as ensure consistent and more accurate outcome. So why do we so often choose to forgo the opportunity for improvement? Here are a few reasons that don't need much analysis:

• Job security – the innate fear that automation will render our position as obsolete.
• Resistance to change – we do things this way because we have always done them this way; or, why try to fix something that is not broken.
• Being always under pressure and adapting a short term thinking – right now I need to take care of this task, I don’t have time to spend on researching how to do it faster and better.
• Underestimating the cumulative effect of manual tasks –a task that only takes let's say 15 minutes a day to complete does not “scream” I need automation BUT in one year you will end up spending about 10 full days of work on that task. 
• Overestimating the cost of automation - it will take too much time and money to acquire and implement a solution. 

None of those reasons constitute a good, rational argument to forgo automation. On the other hand there is the other extreme, automating for the sake of automating. The decision on whether to automate a process / task or not should be based on a rigorous and objective method that is based on numbers and not on feelings.

 

To devise such method we need to first answer the question: what will the decision be based on? While there are various scenarios the most common one is that in which the decision will be based purely on financial considerations, that is, will the automation pay off? Will we get a return on the investment?  Here is the list of factors that you will need to consider in order to answer this question:

• Estimated value of "labor" -> VT ($/hour)
• Estimated time to complete task -> TT (hours)
• Estimated frequency of task -> TF (#times / month)
• Estimated time required to identify the right tool for the job -> RT (hours)
• Estimated time required for implementing the automated solution -> AT (hours)
• Estimated initial cost for acquiring the tool -> TIC ($)
• Estimated cost for maintaining the tool -> TMC ($ / year)
• Estimated time required to complete the task after automation ->TTAA

So the question is: how much time will it take to break even? Or, stated differently, how long will it take for this automation to pay for itself?

Let t denote the number of months it will take to reach this "magic" break-even point after which we start making a positive return on our investment. The break-even point will be reached when: 

VT*TT*TF *t = RT*VT + TIC + TMC/12 * t + AT * VT + TTAA*VT*t 

 

So, consequently, the break-even time can be calculated as:

 t = (RT*VT + TIC + AT*VT) / (VT*TT*TF - TMC/12 – TTAA*VT)

 

Let's use this on a real life example. Assume you are a DBA and your weekly routine includes deploying tens of scripts into your large server farm and let’s say that this takes one full hour of your time. Let’s further assume that based on your compensation, benefits, infrastructure etc. your cost to the company is approximately $70 / hour. You estimate that it will take you about 2 hours to identify a solution and another 2 hours to implement the automated solution in your environment. After the automation the one hour task will be reduced down to 6 minutes of monitoring and reviewing the results. As for the estimated costs of the solution let’s use some real numbers – the best solution in the market for this scenario is Script Executor which costs $249 for the license plus $50 / year for the upgrade subscription. So, we have:

• VT = $70 / hour
• TT = 1 hour
• TF = 4 times / month
• RT = 2 hours
• AT = 1 hours
• TIC = $249
• TMC = $50 / year
• TTAA = 6 minutes = 0.1 hours

Break even: t = (2 * 70 + 249 + 1 * 70) / (70 * 1 * 4 – 50 / 12 – 0.1*70) => t ~ 1.7 months

 Note that if TMC/12 + TTAA*VT  >=  VT*TT*TF then the investment will never pay off in pure financial terms so, unless there are other important considerations then this will be a no go.

 

So, what is a good t? That depends, but generally, anything under 6 months is usually a no brainer. However, as there are always other potential investments competing for the same budget you will need to compare the parameters of this with those of the other potential investments to make the right decision.

 

You can download a fully functional free trial of Script Executor from:  http://www.xsql.com/download/package.aspx?packageid=40 

 

Read More

Script Executor now supports SQL 2012

A new build of Script Executor with support for SQL Server 2012 is now available for download. Script Executor allows you to execute multiple t-sql scripts, in the order you desire, against multiple SQL Server, MySQL and DB2 databases. It also allows you to package a set of scripts into an executable that can be easily deployed to remote clients. A command line utility is also included.

Read More