29 June 2018

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Phil Factor

29 June 2018

SQL Change Automation with PowerShell Scripts: getting up-and-running

Provides a PowerShell automation script that will build a SQL Server database from the scripts in source control, document the database and then deploy it as a NuGet package, using it to update the schema of a live database.

Guest post

I’ll demonstrate a PowerShell automation script, to take the contents of a GitHub source control directory for a database, check that it is possible to build the database, and then document the database. Finally, it will create a NuGet package and place the code in it, along with the documentation and use it to update the database schema of an existing database so that it matches the schema of the source control version that we just built and validated. For database automation work, the PowerShell script needs to adopt good practices for passing credentials securely and adopt robust error checks, so that you have more confidence that things went well

I’m on the liberal wing of opinion about how to do database delivery as part of the application. No sooner does one person decide on the royal road to stress-free database delivery, someone else comes up with a wildly different idea that is just as successful. Tools that assist with this process have become a lot more flexible to use, and SQL Change Automation is typical of this trend, having evolved from DLM Automation suite by adding support for migration-based development.

This is the first in a series of articles to illustrate how to use the PowerShell cmdlets of SQL Change Automation to achieve parts of the deployment process. I’ll assume you have everything installed, such as PowerShell, the SqlServer PowerShell module and SQL Change Automation, and that you’ve tried out the basic tutorials in the documentation; they are all worth working through, and once you have them running successfully, you’ll be ready for more.

PowerShell scripting for SQL automation tasks

When you are automating a process, the scripts that you use, whether Bash, command-line or PowerShell, share several principles. Unlike a demonstration example of the use of a Cmdlet, or an illustration of a process in an article, a real script must run unattended, and in it you mustn’t mix data, especially passwords, with process. The script must not report progress and errors to the screen, because a process that runs on the scheduler doesn’t have a screen. Even if there is a screen, the information provided is too ephemeral, and the significance of a strange message seen by the early morning shift operator may be missed. No, it is far better that progress, information and errors should be logged to a file, along with the time and date.

A script should either run to completion or fail leaving the state as it was. Whether it completes or fails, it should mop up (tear down) afterwards. Where possible, it should never just ‘bomb out’.

To present a script like this in an article would make the listing very long and extremely dull, but to present a script without any indication of how a production script should look is misleading. I’ve tried to sketch in some of the requirements and show how one might deal with them, but the main objective is to get you past the pain threshold of getting something working.

What the PowerShell automation script does

The PowerShell script uses the SCA PowerShell cmdlets, along with the SqlServer PowerShell module, to do part of a continuous build process. This script keeps all the information we need in a single PowerShell System.Collections.Hashtable called $config. This keeps things neat, and allows us to store the data separate from the code, when we get to the point that we need to have one script for several tasks, each with their own data values.

The project will be in a local github ‘project’ directory with the object-level SQL source files in it. So that we don’t tax ourselves unduly, we’ll use Northwind. A copy of this project will be attached to the article. SCA will also work happily with a single build script, or an SCA offline schema model, but we’ll use a SQL Compare scripts folder.

Our aim is to test that the project can be built into a database. We could also run some tSQLt unit tests to make sure that the source is good, and maybe also a SQL CodeGuard check that the source contains no code smells, but I thought that at this point we should keep the complexity down.

Checking for database connections

We’ll start by checking that all the servers can be reached, with the connection information provided. The scripts uses the New-DatabaseConnection cmdlet to get an SCA connection object, which we store for use later on, and the Test-DatabaseConnection cmdlet to check the connection.

Checking connections is a more elaborate a process than you might expect because we can’t, in real life, assume that we can use windows authentication, because of Azure, remote servers outside the domain, and other such complications. Connection information will therefor include credentials, which must be stored securely.

We can’t ever keep passwords for SQL Server authentication as part of the static script data. At this stage, we ask for passwords if they aren’t known, and otherwise store them as secure strings on file, in the user area, protected by the workstation security. We can make the connection checks, and abort on failure, rather than wasting time starting the process before discovering a problem that is a complete job-stopper.

Checking for valid paths

Next, will verify that the file locations you specify exist. It is always best to check that paths are valid and react accordingly. If it is the path to the project then you’d probably want to abandon the build right there, whereas files for logs and data can be created before use.

Checking the database source

To check the source, SCA builds the empty database using Invoke-DatabaseBuild. It will be happy to use a localdb version of SQL Server to do this, but I’d much rather not to do it that way because this needs to be the same server installation, (version and features) as the one the database needs. So, instead, we supply a SQL Server instance to act as host for a temporary version of the database. SCA Invoke-DatabaseBuild builds the database and if it succeeds without any problems, then we can proceed. Whatever else, any problems are logged.

Writing the database documentation

Once we know that the database is valid, in the sense that it can be built from source without breaking, the next step is to produce database documentation, with the New-DatabaseDocumentation cmdlet, which uses the code from SQL Doc. It takes the extended properties in the database, and uses dependency tracker, to provide a more useful documentation of the database than is possible by just inspecting the source code.

Creating the NuGet Package for deployment

Next, we use New-DatabaseBuildArtifact to produce the build artefact, a NuGet package, containing the database source, and all the documentation, which we can then view, or install on a webserver. The NuGet package is exported to a file directory, because we don’t have a NuGet Server. The NuGet File can be used by SCA to update a database to the same version.

Updating the SQL Server database to the same version

Now we have a valid build, which we have used to create the NuGet package, we can then synchronise a target database to the same version and save the script that did it, using Sync-DatabaseSchema. For a build, you’ll need to create the empty database on the target server, and then sync it, to update it to match the version in source control.

If this is successful, the script is saved, which will show the team exactly what was changed, to migrate the database from the previous version to the current version. They may also want to change it. It can be used as the basis of a migration script that converts a database from one specific version of the database to another. The advantage of doing a sync is that we can sync with a development server which already has the test data, and ancillaries such as SSIS packages, already in place, saving time.

Importing the project configuration information

I don’t demonstrate this in the script because it would be a distraction but you can read PSON files as PowerShell. You save a file like the one shown in Listing 1, but with the real parameter values filled in.

# The SQL Change Automation project (in source control directory) to validate, test and sync

'Project' = 'MyPathToTheProjectDirectory';

# The directory to store the NuGet Package in.

'BuildArtifact' = 'MyPathToTheArtifactDirectory';

# The directory to store the log in to record any errors

'LogDirectory' = 'MyPathToTheLogDirectory';

# the name of the project and Nuget Package

'PackageId' = 'MyBigProject';

# the version in the Nuget Package

'PackageVersion' = '1.0.0';

'Databases' = @{

'Temporary' = #the temporary database. (leave blank for local)

'ServerInstance' = 'MyServer'; 'Database' = 'master'; 'Username' = 'NeverYouMind';'SQLCompareOptions'='';

};

'Target' = #the target database.

'ServerInstance' = 'MyDevServer'; 'Database' = 'MyDatabase'; 'Username' = 'NeverYouMind';'SQLCompareOptions'='';

}

Listing 1: BuildData.ps1

Then, you read it in to your script and do whatever checks you please.

$config=&'S:\work\programs\powershell\BuildData.ps1'

<# we read in the data as a structure. Then we do some sanity checking to make sure that the data is reasonably viable. #>

$DataError = ''

if ($config.BuildArtifact -eq $null) { $DataError += 'no $config.BuildArtifact, ' };

if ($config.Databases -eq $null) { $DataError += 'no $config.Databases, ' };

if ($config.PackageVersion -eq $null) { $DataError += 'no $config.PackageVersion, ' };

if ($config.PackageId -eq $null) { $DataError += 'no $config.PackageId, ' };

if ($config.LogDirectory -eq $null) { $DataError += 'no $config.LogDirectory, ' };

if ($config.Project -eq $null) { $DataError += 'no $config.Project, ' };

if ($DataError -ne '') { Throw "Cannot run the application because there is $DataError" }

Listing 2: Running checks for missing build data

One thing that I’ve left out is the PowerShell routine for taking the database version number from source control, or from whatever tool maintains it. I have a dummy version number in the script, but this ought to be over-written with the source-control-specific value. ($ git rev-parse HEAD in Git).

The PowerShell automation script for building and deploying a database

Let’s start with a simple version of the script, but which performs the same basic functions as the more complicated (but also more robust) one I’ll show shortly.

$errorActionPreference = "stop"

#variables that you need to fill in

$TemporaryDatabaseServer = 'MyConnectionStringToTheServer'

$TargetServerInstance = 'MyTargetServerr'

$TargetDatabase = 'MyTargetDatabase'

$TargetUserName = 'MyLogin'

$TargetPassword = 'MyPassword'

$project = "MyPathTotheProject" # The SQL Change Automation project to validate, test and sync

# Validate the SQL Change Automation project

$validatedProject = Invoke-DatabaseBuild $project -TemporaryDatabaseServer $TemporaryDatabaseServer

#this builds the server temporarily to check that it can be done

#produce documentation and the nuget package

$documentation = $validatedProject | New-DatabaseDocumentation -TemporaryDatabaseServer $TemporaryDatabaseServer

$buildArtifact = $validatedProject | New-DatabaseBuildArtifact -PackageId MyDatabase -PackageVersion 1.0.0 -Documentation $documentation

$buildArtifact | Export-DatabaseBuildArtifact -Path "$project\buildArtifacts"

# Sync a database

$deploymentTargetConnection = New-DatabaseConnection -ServerInstance $TargetServerInstance -Database $TargetDatabase -Username $TargetUserName -Password $TargetPassword # Update this to use the blank database created earlier

$ConnectionErrors = @() # to store any connection errors in

$TestResult = Test-DatabaseConnection $deploymentTargetConnection -ErrorAction silentlycontinue -ErrorVariable ConnectionErrors

if ($ConnectionErrors.count -eq 0) #if we couldn't connect

{ $syncResult = Sync-DatabaseSchema -Source $validatedProject -Target $deploymentTargetConnection

$syncResult.UpdateSql

}

else

{write-warning $ConnectionErrors[0]}

Listing 3: The simplified PowerShell script

Obviously, I’ve just assumed a SQL Server login with a (gulp) embedded password in the script and we’re a bit short of error checking. We will need to do better, which sadly will make it all seem rather more complicated. However, as we move on to do more powerful things, the value of the framework we use here will kick in.

In putting values into the script, you will need some caution. Do not put the log file in a Git directory, because Git might lock the file and prevent the script from reporting errors. If you are using Windows authentication for a server, put an empty string into the user name field.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

Trap

{

# Handle the exception

$err = $_.Exception

$MyErrorMessage= $err.Message

while ($err.InnerException)

{

$MyErrorMessage += $err.InnerException

};

"$((Get-Date).ToString()) - Unhandled error '$MyErrorMessage'">>$TheLogFile;

# End the script.

break

}

set-psdebug -strict

$ErrorActionPreference = "stop" #

<#variables that you need to fill in for each project. Normally I have this as a separate file and read the relevant data file in according to the project being built The script adds to the config object as it is executed #>

$config = @{

# The SQL Change Automation project (in source control directory) to validate, test and sync

'Project' = 'MyPathToTheProjectDirectory';

# The directory to store the NuGet Package in.

'BuildArtifact' = 'MyPathToTheArtifactDirectory';

# The directory in which to store the build error log.

'LogDirectory' = 'MyPathToTheLogDirectory';

# the name of the project and Nuget Package

'PackageId' = 'MyBigProject';

# the version in the Nuget Package

'PackageVersion' = '1.0.0';

'Databases' = @{

'Temporary' = #the temporary database. (leave blank for local)

'ServerInstance' = 'MyServer'; 'Database' = 'master'; 'Username' = 'NeverYouMind';'SQLCompareOptions'='';

};

'Target' = #the target database.

'ServerInstance' = 'MyDevServer'; 'Database' = 'MyDatabase'; 'Username' = 'NeverYouMind';'SQLCompareOptions'='';

}

# and some handy constants

$MS = 'Microsoft.SQLServer'

$My = "$MS.Management.Smo"

$errors = 0 #keep a count of the errors we encounter

$TheLogFile = "$($config.LogDirectory)\logfile.txt"

#Load SMO assemblies

$ImportErrors = @()

Import-Module SqlChangeAutomation -ErrorAction silentlycontinue -ErrorVariable +ImportErrors

Import-Module sqlserver -DisableNameChecking -ErrorAction silentlycontinue -ErrorVariable +ImportErrors

if ($ImportErrors.count -gt 0) #if we couldn't import something

{

$ImportErrors | foreach{

"The $($config.'PackageId') build gave an error '$($_)'"

"$((Get-Date).ToString()) - $_">>$TheLogFile;

write-warning $_;

$errors++ #keep a count of the errors

}

#check and if necessary create all directories specified by the config

@("$($config.LogDirectory)", "$($config.BuildArtifact)") | foreach{

# If necessary, create the directory for the artefact

if (-not (Test-Path -PathType Container $_))

{

# we create the directory if it doesn't already exist

New-Item -ItemType Directory -Force -Path $_;

}

@("$($config.Project)") | Foreach{

if (-not (Test-Path -PathType Container $_))

{

"$((Get-Date).ToString()) - the project file directory '$($config.Project)' isn't there">>$TheLogFile

Write-error "the project file directory for $($config.'PackageId'), '$($config.Project)' isn't there"

<a id="post-464606-_Hlk517452254"></a>$errors++ #keep a count of the errors

}

<# We'll start by checking that all the servers can be reached with the connection information. we can quickly ascertain that all the server connection information is OK We can make the connection checks, and abort on failure before wasting time starting the process.#>

$config.Databases.GetEnumerator() | foreach{

$ConnectionErrors = @() # to store any connection errors in

$Database = $_

$db = $Database.Value;

if ($db.username -ieq '')

{

# Crikey, this is easy, windows Passwords. Dont you love 'em?

$db.Connection =

New-DatabaseConnection -ServerInstance $db.ServerInstance -Database $db.Database

}

else

{

<# This is more elaborate a process than you might expect because we can't assume that we can use Windows authentication, because of Azure, remote servers outside the domain, and other such complications. We can't ever keep passwords for SQL Server authentication as part of the static script data. At this stage, we ask for passwords if they aren't known, and otherwise store them as secure strings on file in the user area, protected by the workstation security. #>

#create a connection object to manage credentials

$conn = new-object "$MS.Management.Common.ServerConnection"

$conn.ServerInstance = $db.ServerInstance

$encryptedPasswordFile="$env:USERPROFILE\$($db.Username)-$($db.ServerInstance).txt"

# test to see if we know about the password un a secure string stored in the user area

if (Test-Path -path $encryptedPasswordFile -PathType leaf)

{

#has already got this set for this login so fetch it

$encrypted = Get-Content $encryptedPasswordFile | ConvertTo-SecureString

$Credentials = New-Object System.Management.Automation.PsCredential($db.Username, $encrypted)

}

else #then we have to ask the user for it

{

#hasn't got this set for this login

$Credentials = get-credential -Credential $db.Username

$Credentials.Password | ConvertFrom-SecureString | Set-Content $encryptedPasswordFile

}

$conn.LoginSecure = $false

$conn.Login = $Credentials.UserName

$conn.SecurePassword = $Credentials.Password

#We get the password on the fly for the Cmdlet that creates the SCA connection object for each database

$db.Connection = New-DatabaseConnection -ServerInstance $db.ServerInstance -Database $db.Database -Username $conn.Login -Password $conn.Password

}

#now we can test that the credentials get to the server

$TestResult = Test-DatabaseConnection $db.Connection -ErrorAction silentlycontinue -ErrorVariable ConnectionErrors

if ($ConnectionErrors.count -gt 0) #if we couldn't connect to something

{

write-warning "$($Database.Name;) gave an error $($connectionErrors[0])";

"$((Get-Date).ToString()) - $($Database.Name;) of $($config.'PackageId') gave an error $($connectionErrors[0])">>$TheLogFile;

$errors++

}

if ($errors -eq 0) #if there were errors, then it gives up at this stage and reports the errors.

{

# we now check whether we can buld this without errors

$tempServerConnectionString = $config.Databases.Temporary.Connection.ConnectionString.UnmaskedValue

# Validate the SQL Change Automation project and import it inot a ScriptsFolder object

try

{

$validatedProject = Invoke-DatabaseBuild $config.Project -TemporaryDatabaseServer $tempServerConnectionString -SQLCompareOptions $config.Databases.Temporary.SQLCompareOptions 3>>$TheLogFile

}

catch #could not get the -ErrorAction silentlycontinue -ErrorVariable ConnectionErrors to work

{

$_.Exception.Message

"$($Database.Name;) of of $($config.'PackageId') couldn't be validated because $($_.Exception.Message)" | Foreach{

write-warning $_

"$((Get-Date).ToString()) - $_">>$TheLogFile;

$errors++;

}

if ($errors -eq 0) #if there were errors, then it gives up at this stage and reports the errors.

{

# OK we can now build it

#this section builds the server temporarily to check that it can be done

#Then it produces documentation and the nuget package

if ($validatedProject.GetType().Name -ne 'ScriptsFolder')

{

$TheError = "$($config.PackageId) could not be verified."

write-warning $TheError;

"$((Get-Date).ToString()) - $TheError">>$TheLogFile;

}

else

{

#get the SchemaDocumentation object that we can then add to the nuget package

$documentation = $validatedProject |

New-DatabaseDocumentation -TemporaryDatabaseServer $tempServerConnectionString

if ($documentation.GetType().Name -ne 'SchemaDocumentation')

{

$TheError = "$($config.PackageId) could not be documented."

write-warning $TheError;

"$((Get-Date).ToString()) - $TheError">>$TheLogFile;

}

$buildArtifact = $validatedProject |

New-DatabaseBuildArtifact -PackageId $config.PackageId -PackageVersion $config.PackageVersion -Documentation $documentation

if ($buildArtifact.GetType().Name -ne 'SocBuildArtifact')

{

$TheError = "$($config.PackageId) build artefact could not be created."

write-warning $TheError;

"$((Get-Date).ToString()) - $TheError">>$TheLogFile;

}

$buildArtifact | Export-DatabaseBuildArtifact -Path "$($config.BuildArtifact)" -force

}

# Sync a database

$syncResult = Sync-DatabaseSchema -Source $validatedProject -Target $config.Databases.Target.Connection

# And Save the script

$syncResult.UpdateSql>"$($config.BuildArtifact)\$($config.PackageId)$($config.version).SYNCSQL"

}

if ($errors -gt 0)

{

"$((Get-Date).ToString()) - the build process was aborted">>$TheLogFile;

}

Listing 4: The full PowerShell code (SCABuildAndSync.ps1)

Conclusions

We’ve looked at just a small aspect of SCA, the PowerShell Cmdlets, and just tried a small part of the functionality. There is a lot of power in there. Most of the work in the script is in checking things such as server connections, paths and build scripts. The actual SCA tasks represented by the SCA cmdlets will be as easy as Lego to add in, once there is a viable framework for doing so. To prove this point, I’ll next use the same principles demonstrated here to update an entire test cell, creating the databases wherever necessary and stocking them with masked test data. I’ve already added the module that gives us access to SMO so I’ll be showing you some examples of how we’ll use that to extend SCA’s functionality.

Tools in this post