Tag Archives: automation

Mastering database migrations scripts with VSTS

During the lifecycle of our applications it’s very likely that to support new functionalities we need to modify the structure of our database. In this particular post I mean a relational database.

When we deal with a database in a production environment we can’t simply drop an existing database and create a new one with the latest version of the schema.

The database is unlike application code in as much as it contains state, in terms of table-based data,  that needs to be preserved after an upgrade.

drop-create

This is why when dealing with database changes we need to talk about migration scripts.

Migration Scripts

A migration script is a set of SQL commands that changes the database schema or updates data to evolve the database from the version n-1 to the version whitout loss of data.

Migration scripts are typically collected in a dedicated folder in our repository, named sequentially.

The migration scripts must be executed in the specific order to be able to build the n-th version of our database.

The first migration script

The first script in our collection is responsible to create the first version of our db starting from an empty database.

The following is an example of a simple first migration script that we can name 00000001.sql. The example is for a simple database with one schema named Ordering and one table called Customers in the Ordering schema.

CREATE SCHEMA Ordering
GO
CREATE TABLE Ordering.Customers(
    Id INT PRIMARY KEY,
    CompanyName NVARCHAR(100) NOT NULL
)
GO

Create the first migration script from an existing database

If we work on a solution with an existing complex database we need a tool to create our first migration script. Tools are capable of tracking dependencies and relations inside the db schema to calculate a single script that evolves our db to the desired state. There are many tools on the market. In this example we’ll use Red-Gate SQL Compare. With this tool we select our existing db as the source (IC6.ParrishShoes) and our target to sync. As we explained before we need an empty database so we compare our db to that and we obtain our first script.

The script calculated by SQL Compare engine is the following.

/*
Run this script on:

        ..EmptyTarget    -  This database will be modified

to synchronize it with:

        ..IC6.ParrishShoes

You are recommended to back up your database before running this script

Script created by SQL Compare version 13.4.0.6563 from Red Gate Software Ltd at 03/08/2018 23:20:53

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR  0 SET NOEXEC ON
GO
PRINT N'Creating schemas'
GO
CREATE SCHEMA [Ordering]
AUTHORIZATION [dbo]
GO
IF @@ERROR  0 SET NOEXEC ON
GO
PRINT N'Creating [Ordering].[Customers]'
GO
CREATE TABLE [Ordering].[Customers]
(
[Id] [int] NOT NULL,
[CompanyName] [nvarchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
IF @@ERROR  0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK__Customer__3214EC072CF93715] on [Ordering].[Customers]'
GO
ALTER TABLE [Ordering].[Customers] ADD CONSTRAINT [PK__Customer__3214EC072CF93715] PRIMARY KEY CLUSTERED  ([Id])
GO
IF @@ERROR  0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR  0 SET NOEXEC ON
GO
-- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
BEGIN
    DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
    SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
    SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
    EXECUTE sys.xp_logevent 55000, @eventMessage
END
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO

Compile a database

If we run the previous script and we get zero errors we can say that we can compile our database. The same concept apply when we can execute a collection of migration scripts in the correct order without errors.

Being able to compile a database it’s a huge benefit because it enables us to create a database on demand by simply executing a list of script to get the desired version on which we can develop, run test, deploy to a new customer etc. We can also start to do some automation for our database tasks.

Automation with VSTS – Checking our migration scripts

An important part of having a collection of db migrations scripts is that we have to frequently check that this chain of scripts is valid.

We can invest in automation to do that and with VSTS is very simple to get started.

In the following example we create a build process that executes all the migrations scripts in our repository to a target database to check if everything can be completed without errors.

We go to the Build section of VSTS and create a new Build process from an empty process.

We browse in the marketplace the DbUp extension (free!).

DbUp is a .NET library that helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.

2018-08-03_23-40-20

After the installation process of DbUb we can refresh our list of available task and add the DbUp Migration task. With this task we can execute scripts included in a folder of our repository to a target database. The configuration of the connection string is done with VSTS Build variables in the dedicated tab. We configure the script folder path and the Single Transaction strategy, too. This way all the scripts are executed in one single transaction and if we get an error everything will be rolled-back.

2018-08-04_00-06-31

2018-08-04_00-16-49.png

When we complete the configuration of the task we click Save And Queue.

The build process will start and if we’re a bit lucky everything will succeed.

2018-08-04_00-24-07.png

We can see that DbUp applied correctly the scripts by browing the db with SQL Mangament.

  • First 2018-08-04_00-07-34
  • After 2018-08-04_00-12-42

TL; DR

With this tutorial we’ve learned the basics of migrations scripts, how to create them with a tool and how to check if our collection of scripts is valid with automation implemented with VSTS and DbUp.

This is just the start

When we have such powerful tools we can start to think about automated processes that test our chain of scripts in many ways. We can do an “incremental” approach like the example above triggered at every change in the scripts folder. We can schedule a build process that every day at noon will execute all the migration scripts versus a temporary brand new database.

What will you do with this super powers? Let me know in the comments below!

Reference

VSTS DbUp Marketplace – https://marketplace.visualstudio.com/items?itemName=johanclasson.UpdateDatabaseWithDbUp

Red-Gate SQL Compare – https://www.red-gate.com/products/sql-development/sql-compare/index

VSTS for beginners: release your web-app to Azure

In the previous post of this series dedicated to VSTS we talked about continuous integration. Now we’ll talk about publishing our Web-App hosted on Azure with the release management tools provided by VSTS. With this kind of tools we can deploy the latest version of our web-app to Azure in complete automation removing manual procedures and human errors. The setup of Azure will be covered in another blog post.

 

Read more

VSTS for beginners: improve quality with continuous integration in 3 easy steps

In this blog post we’re going to configure a build process in VSTS to enable continuous integration for our ASP.Net Core example web-app.
Continuous integration is a powerful technique to prevent merge-hell and improve quality on the “left” stages of our software production process. In the fast-paced world of development we want to merge into the main line of development the new developed features as soon as possibile to avoid open branches that will cause painful merges. If we keep our unit of work small and focused we’ll have great benefits.

Read more

Azure PowerShell Start Virtual Machine

This is the script I use to start virtual machines in our cloud environment. I write it down here for archive and sharing purposes.

1.png

It is based on the Azure RunAs automation account.

The only thing thing you need to do is to schedule this script daily.


<#
    .DESCRIPTION
        Turns of virtual machines only office days.

    .NOTES
        AUTHOR: Michele Ferracin
#>


# No Sunday, no Saturday.
$wd = (Get-Date).DayOfWeek

if ($wd -eq "Sunday" -or $wd -eq "Saturday") {
 exit 0;
}

$connectionName = "AzureRunAsConnection"
try
{
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName        

    "Logging in to Azure..."
    Add-AzureRmAccount `
        -ServicePrincipal `
        -TenantId $servicePrincipalConnection.TenantId `
        -ApplicationId $servicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}

Start-AzureRmVM -Name "YourVM1" -ResourceGroupName "YourResourceGroup"

Start-AzureRmVM -Name "YourVM2" -ResourceGroupName "YourResourceGroup"