Tag: Databases

VSTS Sql Server Database Deployment Task

In this post I’ll be having a quick look at the VSTS Sql Server Database Deployment Task. You can find it in the marketplace as part of the IIS Web App Deployment Using WinRM package. It is a bit confusing that you have to look for it under the IIS section not the SQL section in the marketplace.

Take note this task is using WinRm, the instructions to enable it is on the marketplace page and includes a link to a PowerShell script you can run to configure WinRM.

Lets take it for a spin. Follow the installation process from the marketplace to install the extension on your account or download it for on-prem TFS servers. You will see the new WinRM – SQL Server DB Deployment tasks in the Deploy tasks menu.sqlsvsts

Add the task to your deployment and set a few properties, this was quick test I did. You will need a task before this one to copy the dacpac file to a location where the agent can reach it.

SampleConfig

I didn’t have ports open on my firewall for the VSTS hosted agent to reach my machine so I installed the VSTS agent locally and set the task to use localhost. You can specify multiple machines or even use Machine Groups, you will find it in the Test hub in VSTS.

The output from the task doesn’t show much about the actual database changes taking place but you will at least know if the deployment failed.

dacpacoutput

Side Note: Don’t put plain text passwords in your build tasks, use variables and mark them as secret by clicking the padlock icon next to the variable value.

Secret

You can find some more information in the readme file for the task, which they include as a link in error messages when a step fails, which is really helpful.

If you are looking for more information around  continuous deployment of databases have a look at these posts in my continuous deployment of databases series.

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

Francois Delport
Latest posts by Francois Delport (see all)

Continuous Deployment Of Databases : Part 4 – Flyway

This is part 4 in my series on continuous deployment of databases. In this post I’ll be having a quick look at FlyWay.

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

In my two previous posts I briefly had a look at two tools for SQL Server Continuous Deployment, SqlPackage.exe and ReadyRoll. I wanted to see what is out there for Oracle, MySQL and other databases especially if you have a situation where your application supports multiple database and you want to do Continuous Deployment against all of them. There are plenty commercial applications for SQL and Oracle that integrate directly with build systems and IDEs but not that much for other databases based on my quick google search.

Flyway
I’m going to take a quick look at Flyway, a migration based tool, bearing in mind I work in .NET and some of the features are Java centric. It is an open source project and looking at the commit history on GitHub it has been around for a long time and is supported by an active development community. At the time of writing it supported 18 databases.

You create migrations using SQL script files or Java code based migrations which comes in handy for very complex data manipulation and handling blobs. It is based on convention over configuration so naming folders and files correctly is very important since the version number which controls the deployment sequence is part of the name. There are various ways to execute the migrations for example using the command line tool or calling the Java API directly, this opens up scenarios like including Flyway as part of your application to keep your database and application in sync. There are also plugins for Maven, Gradle and Ant but not much in terms of .NET.

I’ll quickly touch on the main operations used by the application:
Migrate: The meat of the application, it migrates a schema to the required version by determining the migrations that should be run.
Clean: Wipes the destination database clean, used during development and testing.
Info: Shows the migrations applied with execution time and state and which ones are pending.
Validate: Verify the applied migrations match the ones you have locally.
Baseline: Integrate Flyway with an existing database so only newer migrations will be applied.
Repair: Flyway uses a metadata table to keep track of applied migrations, this will the repair the metadata table.

Tooling
It doesn’t have direct integration with Visual Studio or VSTS but it doesn’t look to difficult to roll your own automated deployments with some scripting to call the command line tool. Similar to the SqlPackage.exe you can call the Flyway command line tool from your release management tool to update the target database directly or generate a report of the changes that will be applied in case a DBA has to scrutinise the changes first.

Oracle Side Note
If you want to use Flyway against Oracle or just want to use Visual Studio in general for Oracle database development have a look at Oracle Developer Tools for Visual Studio. Oracle tools is very similar to Sql Server Data Tools and makes is easy to add your Oracle database to source control. You can also use it to compare Oracle schemas and generate change scripts which can be the base for your Flyway migration scripts.

Francois Delport
Latest posts by Francois Delport (see all)

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)

Getting Started With Azure SQL

In this post I will cover getting started with Azure SQL. Creating your database in the Azure Portal is easy enough, you choose a name for your database, a name for your server or use an existing one, select the collation and specify if you want a blank database or restore from an existing Azure SQL backup.

Before you connect to your SQL instance you have to white list the client IP addresses that will connect to it from outside Azure and/or enable all Azure services to connect to it for access inside the Azure data centre. In the old portal you go to SQL Databases and click on the servers tab and configure. In the preview portal there is a separate SQL Servers item in the main menu and you click on firewall to add your IP.

Logins work a little bit different. In Azure SQL all logins are created in your master database, master will be the default database for the logins and you can’t change it. The initial admin user created for you will have access to master and will work from SSMS without any action on your side. For other users you create you have to select the database to connect to in SSMS by clicking on the options button in the connection screen. If the user really needs access to master, create a user along with the login in master.

To add more users you can use SQL scripts or the Azure User Management Console if you prefer a gui. I used SQL scripts, first you create a login in the master database and optionally a user in the master database and then you create the user on the actual database you want to access.

On master

CREATE LOGIN newlogin WITH password='yourpassword'
GO

--Optional--
CREATE USER newuser FROM LOGIN newlogin WITH DEFAULT_SCHEMA=dbo
GO

On your database

CREATE USER newuser FROM LOGIN newlogin WITH DEFAULT_SCHEMA=dbo
GO

--Roles work the same as boxed SQL --
EXEC sp_addrolemember 'db_owner', 'newuser';
GO

Which service tier to use?
To determine the DTUs you will need doesn’t look like an exact science at this point and I can understand that since it is a synthetic benchmark. If you are migrating a boxed SQL database you can use this very handy tool to get a starting point. I recommend you setup an alert rule so you will know if your database is approaching the upper limit of the DTUs in the service tier.

Getting Started With Azure SQL

You can even use a webhook to notify you when this alert is triggered and kick off a script for instance to scale up your database automatically.

How to get my data into Azure?
Depending on the size of your database, the easiest method I find is to deploy your existing database from SSMS.

Getting Started With Azure SQL

I didn’t try this method with a very large database yet. I would rather recommend the import database option. First you have to export your boxed SQL database as a bacpac file into your Azure storage account.

Getting Started With Azure SQL

Select the Save to Windows Azure radio button and specify your storage account details. This will upload the bacpac file as a blob to your storage account.

Getting Started With Azure SQL

In the preview portal the Import button is on the SQL Servers menu after you select a server. You point it to your storage account and bacpac file and it will import it into a new database.

If you have a very busy SQL database that can’t be offline for long periods or will be way out of sync by the time you finished the import you can do the initial export and import using bacpac. Then use Azure SQL Data Sync to propagate changes from your boxed SQL server to the Azure SQL database until you can cut over. Currently it is only in preview so be warned.

Transient Errors
Although you can lift and shift your boxed SQL database into Azure SQL without making changes to your app from a SQL compatibility perspective, you usually have to make some small enhancements to cater for transient errors. Transient errors are part of the cloud experience, it is naive not to cater specifically for this scenario. Internet connections can dip, in a multi tenant environment the server could be under strain, the data centre could be migrating you to a different host etc. Luckily there are already frameworks to handle this for you and tools like EF even have it built-in. Here is a very good intro to the subject.

Francois Delport
Latest posts by Francois Delport (see all)

Azure SQL Introduction

Moving your MS SQL databases to Azure SQL might look daunting at first, especially the concerns around compatibility with your applications and security and to a greater extend the fear of anything new or unknown. To help you get started I’m going to give a quick overview of Azure SQL and share some of my experience using it thus far.

NOTE: Not all your applications will be able to run on SQL Azure it is not 100% compatible with on-site/boxed SQL server so you have to determine which of the incompatible features will be a blocker for your applications. That said the applications I moved to the cloud so far didn’t have any problems. The information in this post is from my own experience. There are so many factors impacting your specific database and application requirements that you’ll have to do a thorough analysis of azure SQL for your own applications and run some tests first, especially for very complex, data sensitive applications. For the remainder of the post, Azure SQL refers to Azure SQL Database V12 which is very close to SQL 2014 compatibility. Here is a link that lists the features that are not supported. Azure SQL is only the SQL database engine not SSRS, Analytics and other tools, you can still use SSRS from a boxed SQL instance and target your Azure database and there are other Azure products that like Data Factory to fill the gaps.

Firstly, why would you move to the cloud

  • In most cases it is cheaper to use Azure SQL rather than running a VM with SQL server, take a look at the pricing.
  • High availability, the SLA is 99,99% up time, if you feel brave go find out what you have to go through to get this with boxed SQL server. Azure keeps 3 copies of your data.
  • Disaster recovery is much easier with standard or active geo-replication.
  • It is easy to scale up, you just move to a better service tier and you can still scale out/shard using elastic scale, currently in preview only.

Service Tiers
Azure SQL comes in 3 service tiers Basic, Standard and Premium, the differences are in the transactions per second (DTU), backups and geo-replication options you get. To differentiate the performance levels the transactions per second allowed is expressed in DTUs. It is a synthetic benchmark taking into account disk I/O, CPU load etc and here is a tool to calculate the number of DTUs you need based on your current database load. When you reach your maximum DTUs you won’t get exceptions back from SQL but your queries will take longer and longer until they start to timeout.

  • Basic tier is for very small apps < 2 GB, 5 DTUs (1 level) and daily retained backups for a week.
  • Standard tier is for small/medium apps < 250GB, 10~100 DTUs (4 different levels) and point in time backups retained for 14 days.
  • Premium tier is for apps < 500GB but there is one Premium tier for 1TB databases, 125~1750 DTUs (5 levels) and point in time backups retained for 35 days.

The point in time backups you get with all the tiers allow you to restore the database to any point in time covered by the backups. I didn’t experience it myself but I’ve seen some posts about very long restore times, so test first. If you are concerned about availability and the time it will take to recover rather use geo-replication instead of relying on backups alone.

  • Basic tier does not offer geo-replication.
  • Standard tier offers standard geo-replication, you have only one secondary database in a different datacentre and it is non-readable, charged at a discount to the primary since it is off-line. You have to initiate failover manually in the portal or via a script.
  • Premium tier offers active geo-replication, you can have up to four readable secondaries which means you can run queries against them and use them as a means to scale our your read operations. You have to initiate failover manually in the portal or via a script.

Compatibility 

  • Azure SQL is binary compatible over the wire, you can connect to it from SQL Server Management Studio or your application just like you do for boxed SQL.
  • You can use Visual Studio Database tools and Data Projects to connect to Azure SQL. This is handy to do a schema compare to make sure your database in Azure is identical to the local SQL Server one.
  • You can export your Azure database as a DACPAC and import it into a local SQL Server and vice versa. Using SQL 2014 SSMS you can migrate your local database to Azure SQL.

In my next post I’ll show a quick demo on creating and connecting to an Azure SQL database and highlight some of the differences or surprises you might run into.

Francois Delport
Latest posts by Francois Delport (see all)