Month: June 2016

Continuous Deployment Of Databases : Part 3 – ReadyRoll

This is part 3 in my series on continuous deployment of databases. In this post I’ll be having a quick look at ReadyRoll and using it in continuous database deployment.

Part 1 covers the logic behind the deployment options.
Part 2 covers SqlPackage.exe.
Part 4 covers FlyWay.
VSTS Sql Server Database Deployment Task can be found here.

ReadyRoll, is a tool from Redgate that is migration based, although it also allows creating individual migration scripts based on state comparison. It is pretty easy to use and I was able to get my demo working end to end and running a release from VSTS without ceremony.

Quick Intro To Creating Migration Scripts
Just like Sql Server Data Tools, ReadyRoll is integrated into Visual Studio. You can create the initial project by importing an existing database and adding it to source control. To create your own migration scripts you can add them manually to the Migrations folder in your project or you can make changes to a database using the Visual Studio Server Explorer and then click on the Update button and then Generate Script button to generate the migration script based on the changes made without updating the actual database.

You can also use the state method by making changes to the database directly using Visual Studio Server Explorer or Sql Server Management Studio and then importing the changes back into your project. Use the ReadyRoll DB sync option from the ReadyRoll menu and the Import and Generate Script button to generate a migration script that will be added your Migrations folder. Obviously if the changes to the database involved data manipulation that part of it will not show in the migration. The changes you made can be misunderstood for example renaming a column can show up as adding a new column and deleting the old one via a table recreate.

You can edit the migration scripts and add any steps needed to manipulate data or database objects which makes is better suited for changes that require data manipulation.

Database Baseline
ReadyRoll uses a table called __MigrationLog to keep track of the migrations applied to a database, when you import an existing database this table is created and the current state is used as the baseline for that database. Before you can deploy to a database you have to baseline it first, for example you import your Dev database into ReadyRoll but will be deploying to QA and Production as part of your release for the first time. Read the documentation here for instructions before you try to deploy or the deployment will fail.

Continuous Deployment In Visual Studio Team Services
 To use ReadyRoll in VSTS you have to install the extension from the marketplace, the official getting started guide for ReadyRoll in VSTS is over here. I’m just going to highlight a few things but this will not be a deep dive into getting it working. Continuous Deployment  with ReadyRoll is a two step process, first building and then releasing. The build step will build your ReadyRoll project while comparing it to the target database specified in your build step to generate a deployment package PowerShell file to run the migrations required on the target database. This PowerShell script will be part of the artifacts of the build. You can view the SQL script and a database diff report on your build result page in VSTS.

ReadyRollNewTabs

The next step is to release it, in your release definition add the Deploy ReadyRoll Database Package task and point it to the script created in your build and the target database.

AddReadyRollStep

Take note, in the Package To Deploy setting of the task, point it to the {YourProjectName}_DeployPackage.ps1 file from your artifacts, not the {YourProjectName_Package.sql} file. I hit a snag at this point since the DeployPackage.ps1 file did not exist for me, to fix it I had to go to the properties of the project in Visual Studio and select SQLCMD package file as the output type.

EnablePackage

When To Use State Or Migration
I talked bit about the differences in my first post but after all the research and demos I created I started to see how migrations seem better suited for a smaller number of changes that are complex and require data manipulation where as state based changes work great during development and for a large number of changes especially to the database structure not the data it self. Obviously this depends on your application and the churn in your database and data structure.

Francois Delport
Latest posts by Francois Delport (see all)

Continuous Deployment Of Databases : Part 2 – SqlPackage.exe

This is part 2 in my series on continuous deployment of databases. In this post I’m going to have a quick look at SqlPackage.exe and using it in continuous database deployment.

Part 1 covers the logic behind the deployment options.
Part 3 covers ReadyRoll.
Part 4 covers FlyWay.

Update: The VSTS Sql Server Database Deployment Task using SqlPackage.exe has since been released, you can read more about in this post. If you are using SqlPackage.exe outside VSTS the information in this post will still be relevant.

SqlPackage.exe is a free command line tool from Microsoft that you can use against SQL Server and Azure SQL Server to compare, update, import, export databases to name but a few scenarios. This post is only scratching the surface, the official documentation is here, please read it to get a better understanding of the tool.

SqlPackage.exe is a state based database tool, if you are not familiar with the term please read part 1 of the series here for an explanation. For continuous deployments I will be using a dacpac file as the source state for my database, read more on dacpac files here. The easiest way to generate dacpac files is using Sql Server Data Tools. SSDT is integrated with Visual Studio, when you build the SSDT project the output will be a dacpac file. This also fits in nicely with adding your database to source control and building the proper version of your database along with your other application components. You can read more about SSDT here.

Making Backups
As everyone knows you always make a backup of your database before deploying changes. You can instruct SqlPackage.exe to backup your database by passing the /p:BackupDatabaseBeforeChanges parameter. I assume it will create a backup using the default settings and location of your server but I prefer to rather do it myself. Here is a sample PowerShell script that will backup a database using the SQLPS module and SQL Server Management Objects SMO. Side Note: keep your PowerShell scripts in source control instead of lumping large blocks of script into text boxes in your build or deployment tool.

Now for the fun part, I’m going to explore 3 scenarios that I came across so far and I guess most other developers will as well.

Scenario 1: Automatically update your target database
If you are deploying to a development database or have an extremely informal (but not recommended) production environment, you can use SqlPackage.exe to update your target database by comparing it to your dacpac file and applying the changes on the fly. The syntax for this is:

/Action:Publish /SourceFile:$dacpac_path /TargetConnectionString:$constr

Scenario 2: Generate a script with the changes that will be applied
The more likely choice for production environments would be generating a script that your DBA can scrutinise before applying it to the production database. The syntax for this would be:

/Action:Script /SourceFile:$dacpac_path} `  /TargetConnectionString:$constr /OutputPath:$path_to_scriptfile

For the actual deployment you have a few options, you can pull in the generated script as an artifact of the build or email it to the DBA or drop it in a file location etc and they can apply it manually using their preferred procedure. Alternatively you can still keep the process semi automated and inside your release management tool by adding a deployment step that requires approval from the DBA, this step will use the generated script artifact to deploy the changes to a database.

Scenario 3: Generate a difference report between database
This is usually a post deployment task that will form part of your post deployment testing or sanity check to confirm the database is in the correct state. The SqlPackage.exe tool should always do its job properly but database are precious pets and everyone wants to make 100% sure everything is correct. The syntax is:

/Action:DeployReport /SourceFile:$dacpac-path ` /TargetConnectionString:$constr /OutputPath:$reportoutputfilepath

I combined all the samples into one PowerShell script here.

Populating lookup tables
In your SSDT project you can add pre and post deployment scripts. I use the post deployment scripts with the SQL Merge statement to populate lookup tables, full example from MSDN here. There is a way to add the lookup table(s) as dependent objects but last I read the tables couldn’t contain foreign key relations to tables outside the dependent dacpac.

VSTS SQL Deployment Task
At the time of writing this blog post the official SQL Deployment task for VSTS was in preview and should be released very soon but in the mean time I used these examples scripts in VSTS and they worked so if you are in hurry they will do the job.

Francois Delport
Latest posts by Francois Delport (see all)

Continuous Deployment Of Databases : Part 1

This is part 1 in my series on continuous deployment of databases. In this post I’ll be covering the logic behind the different deployment options.

Part 2 covers SqlPackage.exe.
Part 3 covers ReadyRoll.
Part 4 covers FlyWay.
VSTS Sql Server Database Deployment Task can be found here.

Continuous deployment of databases is a lot more complicated than applications so before we can look at continuous deployment of databases we have to look at managing database changes first. I’m going to give a high level overview of the problem space and methods to deal with database changes before looking at practical examples of continuous deployment of databases in the next post.

The Problem Space
A few of the challenges we face with database changes are:

  • Rolling back: Rolling back files is easy, just restore the previous version from source control, with databases you have to explicitly back them up before hand and restore them if needed and it can take a while for large databases and no one can work while you are restoring.
  • Down Time: It is easy to swap servers in and out of your load balancer to update them but it is not so simple with a database, even in a cluster. It is not impossible with databases just more work, there are solutions like sharding for instance but that is a post on its own.
  • State: Your application files do not contain state, you can recreate them easily while your database contains precious business data. You have to make changes to the structure and existing data without loosing anything. This leads to some teams hand crafting the change scripts for each release which is error prone.
  • Control: In large organisations DBAs are often the only people allowed to make changes to a database and they scrutinise the change scripts provided by developers before implementing them manually. You can’t have automatic migrations or automatically generated scripts executing against the database.
  • Drift: It is not unheard of that databases in production do not match the version developers are using the create the change scripts, especially with applications that are installed at the client premises.

Keeping Track Of Database Changes
The most important step to keep track of your database is adding it to source control, whether you are using specialised database tools or not. This will enable tracking the changes made to your database at specific points in time. Even just having the database create scripts committed each time a change is made will be better than nothing. If you are using the state/declarative method it will also help to solve conflicting changes, for instance two developers renaming the same column in a table at the same time will lead to a merge conflict in your source control system. You can also branch the database just like you do with application code to fix a serious production bug and then later merge the changes back into trunk.

Approaches To Implement Database Changes
Currently there are two ways to handle database changes, state/declarative and migrations, each one with its own strengths and weaknesses:

State/Declarative Method:
The state method works by comparing the target database to a source database and then generating a script to bring the target database in sync with the source database. This is best accomplished using a database schema comparison tool.
Pro: Any number of changes made in any sequence by team members will be included since the current state of the source database in source control is the source of truth not the steps taken to reach that state. Works great for databases during the development phase when you can tolerate dropping and recreating tables or columns and large teams making multiple changes at the same time. Less error prone since the scripts are generated by a tool instead of hand crafting them.
Cons: Cannot handle data migrations since the tool works according to the current state, it has no way of knowing how that state was achieved. For example if a new column was created by manipulating data from two existing columns.

Migrations Method:
This method applies a sequence of changes to a database to migrate it to the new state. With this method the migration scripts are the source of truth, the scripts are created by hand (there are exceptions see the comparison of tools later in the post) and keeping the correct sequence is very important.
Pro: Handles data migrations very well since developers can include any data manipulation required in the scripts.
Cons: Most of the time the scripts will be created by hand which is cumbersome and error prone. You have to take all the change scripts created during development and scrutinise them to weed out conflicting changes and you have to keep the correct sequence. For example developer A renames a column in table X and checks in the alter script, later developer B also renames the same column to something different without getting the changes from developer A and checks in his alter script. When you execute all the migrations the change from developer B will fail since the column exists under the new name. It is also cumbersome for all developers to keep their development databases in sync with the changes.

As you can see there is no clear cut solution to the problem and most tools will fix some of the problems but not all, for example Sql Server Data Tools will handle the state based method very well and Entity Framework Code First will handle migrations very well. Some tools like ReadyRoll try to combine both by generating a sequentially numbered migration script based on the current state of your database when you check-in a change.

Francois Delport
Latest posts by Francois Delport (see all)

Azure Network Security Groups

This post is a summary and some links to documentation regarding Azure network security groups, it might useful if you are in need of a quick introduction to the subject.

Exposing a virtual machine to the internet
Before I get into network security groups I want to talk about the way Azure virtual machines are exposed to the internet using the Azure resource manager deployment method. By default when you create a new virtual machine using ARM in the new portal it will have a public IP address, the public IP address will be dynamic so it can change when the machine is restarted, you can make it static if need be for a monthly fee. The VM will not have a DNS name and you can’t specify one in the create VM wizard. You can add the DNS label after the machine is created or you can create the public IP separately with a DNS label and then point the new VM to use the existing public IP when you create it. The default network security group created with the VM will have a rule to allow RDP connections to this VM and some default rules used by Azure infrastructure. You can also use load balancers but that is a different story all together.

What Are Network Security Groups?
 Short version (based on documentation link below): A network security group contains a list of rules that allow or deny traffic to a specified port or port range at a subnet or network interface card level. The rules also take into account the direction of the traffic, so it is not just for inbound traffic, you can also restrict outbound traffic. Applying the network security group at a subnet level will apply the rules to all the network cards in the subnet. Applying the network security group at network card level will only apply to that network card, if you have multiple network cards in a VM each one can have it’s own network security group. When you create your own rules take care not to block traffic to the 168.63.129.16 IP address and also port 1688, these are used by Azure internally for DHCP, DNS, Windows Licensing etc. Take note when using load balancers, your load balancer is backed by a pool of NICs, you should apply the network security group to the backend NIC pool to ensure all traffic passes through to the network security group, even traffic that didn’t pass though the load balancer.

Official documentation, very long but very informative read.

Practical Application Of Network Security Groups
Creating a DMZ is one of the important capabilities we get from network security groups, that said there are various options when creating a DMZ and it gets quite complicated. Options include using network security groups only, using vendor firewall devices and creating custom routes for traffic. I’ll briefly talk about the simplest option using only network security groups. At a very simplified level creating a DMZ for you application involves separating the physical layers of your application based on the traffic they will receive/send and restricting the traffic to the absolute minimum between the layers and the internet. In this example below the IIS front end and SQL back end are on different subnets and each subnet has its own network security group. All traffic from the internet and between subnets are interrogated by the network security groups against their rules. All traffic should be blocked by default and then you add rules to allow specific ports. In this example the allowed traffic are the orange lines for RDP to both subnets, HTTP traffic to the website and SQL traffic between the front end subnet and the back end subnet. There are no rules for the outgoing traffic in this example. Setting up outgoing rules can be a bit tricky since most application can respond on a range of addresses.

Azure Network Security Groups

The links to the official documentation and DMZ examples are below:

Build a Simple DMZ with NSGs

Build a DMZ to protect applications with a Firewall and NSGs

Build a DMZ to Protect Networks with a Firewall, UDR, and NSG

Francois Delport
Latest posts by Francois Delport (see all)