Introduction
During work on a mock up for the situation of
unittesting of a database, it was a bit bugging me that CI database was only needed for a short term of time of testing and when the testing was done, the database was not needed anymore. Why not creating a database when the test starts en drop the database when the database is not needed anymore? It will save money when the database is not used. So, I decided to make some adjustments in my release pipeline.
The following steps are executed in my release pipeline.
The SQL Database Deploy ARM template
The first step of the release pipeline is creating a SQL database with an ARM template. Now there is some discussion about using this technique. This is a declarative way of creating infrastructure (as-a-service). But, some people advocates the procedural way of creating Infrastructure. I decide to use the ARM template to create my SQL Database. For this purpose, I'm using the template from
Github. This is a Standard template and can be customized to your needs.
{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"sqlAdministratorLogin": {
"type": "string",
"metadata": {
"description": "The administrator username of the SQL Server."
}
},
"sqlAdministratorLoginPassword": {
"type": "securestring",
"metadata": {
"description": "The administrator password of the SQL Server."
}
},
"transparentDataEncryption": {
"type": "string",
"allowedValues": [
"Enabled",
"Disabled"
],
"defaultValue": "Enabled",
"metadata": {
"description": "Enable or disable Transparent Data Encryption (TDE) for the database."
}
},
"location": {
"type": "string",
"defaultValue": "[resourceGroup().location]",
"metadata": {
"description": "Location for all resources."
}
}
},
"variables": {
"sqlServerName": "devhennie",
"databaseName": "AdventureWorksLT2017_CI",
"databaseEdition": "Basic",
"databaseCollation": "SQL_Latin1_General_CP1_CI_AS",
"databaseServiceObjectiveName": "Basic"
},
"resources": [
{
"name": "[variables('sqlServerName')]",
"type": "Microsoft.Sql/servers",
"apiVersion": "2014-04-01-preview",
"location": "[parameters('location')]",
"tags": {
"displayName": "SqlServer"
},
"properties": {
"administratorLogin": "[parameters('sqlAdministratorLogin')]",
"administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]",
"version": "12.0"
},
"resources": [
{
"name": "[variables('databaseName')]",
"type": "databases",
"apiVersion": "2015-01-01",
"location": "[parameters('location')]",
"tags": {
"displayName": "Database"
},
"properties": {
"edition": "[variables('databaseEdition')]",
"collation": "[variables('databaseCollation')]",
"requestedServiceObjectiveName": "[variables('databaseServiceObjectiveName')]"
},
"dependsOn": [
"[variables('sqlServerName')]"
],
"resources": [
{
"comments": "Transparent Data Encryption",
"name": "current",
"type": "transparentDataEncryption",
"apiVersion": "2014-04-01-preview",
"properties": {
"status": "[parameters('transparentDataEncryption')]"
},
"dependsOn": [
"[variables('databaseName')]"
]
}
]
},
{
"name": "AllowAllMicrosoftAzureIps",
"type": "firewallrules",
"apiVersion": "2014-04-01",
"location": "[parameters('location')]",
"properties": {
"endIpAddress": "0.0.0.0",
"startIpAddress": "0.0.0.0"
},
"dependsOn": [
"[variables('sqlServerName')]"
]
}
]
}
],
"outputs": {
"sqlServerFqdn": {
"type": "string",
"value": "[reference(concat('Microsoft.Sql/servers/', variables('sqlServerName'))).fullyQualifiedDomainName]"
},
"databaseName": {
"type": "string",
"value": "[variables('databaseName')]"
}
}
}
Experimenting with the script
The next step was using the "Template Deployment" in Azure to test the script. For this reason, I searched for the resource (New Resource) New Template deployment.
Choose
Build your own template in the editor. This will open a free editing window.
Copy and paste the ARM template in the editing window and press on
Save.
The next step is to press Purchase in order to create the SQL Database with the ARM template.
The result should be that the database is created. If you're satisfied with the template, the next step will be to implement this ARM template in the release pipeline.
Implementing ARM Template in the DevOps pipeline
I've extended the release pipeline with the creation of a SQL database with ARM templates and with a DROP database script. This is depicted below.
In the following window I've included the details of the using the template. There are a couple of options of sourcing the ARM template. I decided to include it in my project. This has the advantage that all my deploymentscripts are stored together with my project code.
Now for dropping the database I (re)used a Powershell script from my previous blog about this subject. I've adjusted it a bit for executing a DROP DATABASE script.
This is the script.
$connectionString = "Server=tcp:devhennie.database.windows.net,1433;Initial Catalog=master;Persist Security Info=False;User ID=hennie;Password=xxxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
$sqlCommand = 'DROP DATABASE AdventureWorksLT2017_CI;'
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
$connection.Open()
Final Thoughts
In this blogpost I've sketched a scenario of creating and dropping a CI database together with the deployment of the code of a project, together with the tSQLt framework and testprocedures.
Advantage is that the database is created for only a short period of time, but disadvantage is that the creation of the database and installing the scripts take some considerable time (~10 minutes) for a small demo database.
Hennie
