Skip to main content
Version: v1.2.0

Azure SQL

Installation

To have access to the following features, you have to import the module:

PS> Install-Module -Name Arcus.Scripting.SQL

Invoke a database migration

With this script, you can perform database upgrades by providing/adding specific SQL scripts with the right version number. Once a new version number is detected it will incrementally execute this.

While doing so it will create a table "DatabaseVersion". If the DatabaseVersion table doesn't exist it will automatically create it.

This function allows you to trigger a database migration, which will only execute the newly provided SQL scripts, based on the provided version number in each of the scripts. The current version is stored in a table "DatabaseVersion", which will be created if it doesn't exist yet.

ParameterMandatoryDescription
ServerNameyesThe full name of the SQL Server that hosts the SQL Database.
DatabaseNameyesThe name of the SQL Database
UserNameyesThe UserName of the SQL Database
PasswordyesThe Password of the SQL Database
ScriptsFolderno (default: $PSScriptRoot/sqlScriptsThe directory folder where the SQL migration scripts are located on the file system
ScriptsFileFilterno (default: *.sql)The file filter to limit the SQL script files to use during the migrations
DatabaseSchemano (default: dbo)The database schema to use when running SQL commands on the target database

Make sure that the credentials that you provide can write tables to the database + any action that you specify in the SQL scripts. (If the user is a member of the db_ddlamin role, then that user should have the necessary rights)

Example with defaults

PS> Invoke-AzSqlDatabaseMigration `
-ServerName "my-server-name.database.windows.net" `
-DatabaseName "my-database-name" `
-Username "my-sql-username" `
-Password "my-sql-password"
# DB migration 1.0.0 applied!
# Done migrating database. Current Database version is 1.0.0

Example with custom values

PS> Invoke-AzSqlDatabaseMigration `
-ServerName "my-server-name.database.windows.net" `
-DatabaseName "my-database-name" `
-Username "my-sql-username" `
-Password "my-sql-password" `
-ScriptsFolder "$PSScriptRoot/sql-scripts" `
-ScriptsFileFilter "*.MyScript.sql" `
-DatabaseSchema "custom"
# DB migration 1.0.0 applied!
# Done migrating database. Current Database version is 1.0.0

Adding SQL scripts so they can be picked up by the script

  1. In the location where you want to run the script add a folder where the migration scripts will be placed. By default, we're looking in a folder called SqlScripts, but this can be any folder as it is configurable via the ScriptsFolder argument.

  2. Add your database migration scripts in the folder that was created in the previous step. To be recognized by the module, the files must match with the following naming convention: [MajorVersionNumber].[MinorVersionNumber].[PatchVersionNumber]_[DescriptionOfMigration].sql

In practice this can look like this: 1.0.0_Baseline.sql 1.1.0_AddIndexes.sql 1.1.1_PopulateCodetables.sql

When a new migration comes along, just create the new SQL script with a version number one number higher than the previous one.

Compatibility

Semantic versioning of database-migrations is supported since version v0.5. Existing migration scripts that follow the old naming convention will be recognized and will be given this version-number: [VersionNumber].0.0.