How to make complex SQL database deployments easier

Application deployment is not an easy task. For more complex updates, besides a new version of a database that should be deployed, a new version of the application should also be deployed, and even the environment configuration changes (e.g. IIS settings for a web application or some other server settings) and post-deployment testing might be necessary.

Problems with complex deployments

A complex deployment often involves a lot of manual work, which makes it slow and error prone. While these characteristics are not a big problem in a test or development environment, they are certainly unacceptable in production.

Deployment challenges

The main challenge is to have a precise and quick deployment. The recommended solution is deployment automation. This is especially true for large, mission-critical systems for which it can’t be afforded to have them offline for too long. In the environments where deployments are frequent, there’s one more reason that goes in favor of automation.

Batch and PowerShell scripts

One of the options is to use batch files to execute the deployment steps. The drawback of using batch files is that they don’t provide much flexibility and they simply can’t be used to accomplish some tasks in complex deployments. Especially when it comes to verification, creating reports, and the like.

PowerShell scripts are better than a simple set of command line clauses that are used in batch scripts, but they have a steeper learning curve and still don’t have the flexibility of popular programming languages.

Deployment applications

The most flexible and powerful solution is to create a C# or any high-level model programming language application that bundles all items necessary for deployment, checks the target environment configuration, applies the new configuration, deploys the new application and database versions, and creates a report for all executed actions. Then it can be re-used for other deployments instead of creating a new solution for each deployment.

A potential drawback of this solution is the time needed to create one.

This is where ApexSQL Build can help. ApexSQL Build provides a fully flagged .NET solution that, when built for Visual Studio, creates an application that deploys a set of scripts to a targeted server.

This solution is made so that it can be easily extended with the custom code that covers all other details such as copying new files, creating folders, setting permissions, changing configurations, and basically, anything else needed.

ApexSQL Build is a SQL database deployment tool that builds new databases or updates existing ones by packaging SQL scripts, script folders, database snapshots, and even scripts under source control into a single deployment SQL script, a .NET solution, or an executable installer.

  1. Start ApexSQL Build
  2. If the deployment is to be executed against an existing database, click the Update button under the Database section in the Home tab:

    Update an existing database

  3. Select the C# solution as an output type:

    Selecting the C# project as an output type

  4. In this case the Script folder option is selected as an input source for updating a SQL Server database:

    Choose input source to update SQL Server Database

  5. Click the Browse button and navigate to the folder where the database is scripted
  6. In the Connect to SQL Server step, provide the server name, along with the authentication type and database that will be updated:

  7. In the Update objects step, all objects from the script folder are listed. Select the desired ones for the deployment:

    Database object selection step

  8. In the step Preview, check out the actions that will be taken, and click the Next button:

    Preview the script impact step

  9. In the step Output options, specify the Visual Studio version and the Default database name for the project in the Project tab, whether to use compression or not, and how to handle errors. Leave the Open project in Visual Studio option checked to open the project in Visual Studio as soon as it’s created:

    Output options selection in ApexSQL Build

    Additionally, under the Optional information tab, provide the information that will be added to the About window of the created package and its title:

  10. Once everything is set, click the Finish button
  11. Specify the name and the location for the generated C# solution:

    Entering the name and the location for the generated project

  12. Once the new C# solution is created, it will be opened in Visual Studio for additional customizing and extending with the custom code. The created C# solution already contains small GUI, included scripts, and a set of classes that provide functionality for publishing databases:

    C# project example in Visual Studio

Creating a C# solution for complex deployments can be simplified by using a ready-made C# solution for database deployment. Have ApexSQL Build create it: just specify the database and select objects to be deployed.

April 30, 2013