How to initially configure the ApexSQL DevOps toolkit PowerShell one-click scripts

Applies to
ApexSQL DevOps toolkit

Summary

This article explains the initial configuration of the ApexSQL DevOps PowerShell one-click script to set up basic parameters, before you building a custom CI/CD workflow pipeline.

Description

All the ApexSQL DevOps toolkit cmdlets are available in Windows PowerShell, since you have installed the ApexSQL DevOps PowerShell module. However, before you can start compiling your one-click script to automate your CI or CD process, some basic configuration of the pipeline script itself is needed.

Notification settings

First, we will need to set up the notification settings, and specifically the sender’s e-mail account settings. These settings will be used in the Notify step to send you information about the status of your pipeline. For that, we will use the New-ApexSQLNotificationSettings cmdlet.

Syntax:

New-ApexSQLNotificationSettings [-EmailAddress] <string> [-Password] <string>
[-SmtpServer] <string> [-Port] <int> [-UseSSL]

Required parameters:

EmailAddress: The e-mail address used to login to the e-mail server

Password: The password used with the e-mail address above

SmtpServer: The SMTP server name used by the sender’s e-mail account

Port: The SMTP server port number

Optional switch:

UseSSL: When this switch is added, the Secure Sockets Layer (SSL) protocol will be used to establish a connection to the remote e-mail server.

Example:

$My_notification_settings = New-ApexSQLNotificationSettings 
-EmailAddress "example@email.com" 
-Password "example_password" 
-SmtpServer "smtp.example.com" 
-Port 587 -UseSSL

In the example, above, we have assigned the notifications settings to the variable $My_notification_settings, that can be re-used to apply this set of settings to the Notify step.

Global options

Here we are going to configure some basic options by using the New-ApexSqlOptions cmdlet. These settings include the custom pipeline name, default location for all output files generated by this pipeline, as well as the abovementioned notification settings set.

Syntax:

New-ApexSqlOptions [-PipelineName] <string> [-OutputLocation] <string>
[-NotificationSettings] <ApexSqlNotificationSettings> [-NoSubfolders]

Required parameters:

PipelineName: The name of the current pipeline e.g. MyPipeline

OutputLocation: This is the default folder where all output files and execution results for this pipeline will be saved. By default, the appropriate subfolders will be created in this location and all files created every time this pipeline is ran will be stored in the …\OutputLocation\PipelineName\Timestamp folder.

NotificationSettings: Here we can assign the notification settings we defined earlier by using the $My_notification_settings variable.

Optional switch:

NoSubfolders: By adding this switch, no additional subfolders will be created for this pipeline, and all output files will be saved in the …\OutputLocation folder.

Example 1:

$My_CI_options = New-ApexSqlOptions 
-PipelineName "My_CI_Pipeline" 
-OutputLocation "C:\CICD" 
-NotificationSettings $My_notification_settings

In this example, we assigned this set of options to the variable $My_CI_options. As we omitted the -NoSubfolders switch, all output files will be stored in C:\CICD\My_CI_Pipeline\timestamp

Example 2:

$My_CD_options = New-ApexSqlOptions 
-PipelineName "My_CD_Pipeline" 
-OutputLocation "C:\CICD\all_CD_output" 
-NotificationSettings $My_notification_settings
-NoSubfolders

Here, by adding the -NoSubfolders switch, we explicitly determine that no subfolders will be created, and all output files for every run of My_CD_pipeline will be stored in the C:\CICD\all_CD_output folder

Data sources

The last part of the basic configuration is to setup the data sources that will be used as a source/target for each individual CI or CD step. A data source can be a SQL Server database, SQL script file or a source control repository. For each type of data source, a different cmdlet will be used.

Source control repository:

First, we will setup a connection to the source control repository where your database is being versioned.

In a typical CI pipeline, this connection would be used as a source to start your build. Depending on the version control system you are using, there are different cmdlets supporting Git, Mercurial, Subversion, Perforce and TFS repositories.

The available cmdlets are:

  • New-ApexSqlGitSourceControlConnection
  • New-ApexSqlMercurialSourceControlConnection
  • New-ApexSqlSubversionSourceControlConnection
  • New-ApexSqlPerforceSourceControlConnection
  • New-ApexSqlTfsSourceControlConnection

Let’s look at New-ApexSqlTfsSourceControlConnection:

Syntax:

New-ApexSqlTfsSourceControlConnection [-ConnectionName] <string>
[-Server] <string> [[-Project] <string>] [[-Label] <string>] [-UserName] <string> [-Password] <string>

Required parameters:

ConnectionName: A custom name for this connection

Server: The link to the TFS repository

Username: The username used to connect to this repository

Password: The password for the username defined above

Optional parameters:

Project: The path to a certain source control project inside this repository. If omitted, the default value, $, will be used, meaning that the script files are located in the root directory of the repository

Label: Use this parameter to specify a certain source control label as a source. If omitted, the latest label will be used

Example:

$My_tfs_repo = New-ApexSqlTfsSourceControlConnection 
-ConnectionName "tfs" 
-Server "https://tfs.example.com:12345/tfs/" 
-Project "$/MyProject" 
-UserName "user" 
-Password "password"

In this example, we created the $My_tfs_repo object that can be used as a data source by individual pipeline steps later.

Similar to the New-ApexSqlTfsSourceControlConnection cmdlet, the rest of the source control connection cmdlets can be used for other version control systems.

Quick tip icon

Quick tip:

You can get information about the syntax and available parameters of every cmdlet by using the get-help <cmdlet name> -detailed command in Windows PowerShell.

Database

To setup a connection to a SQL Server database, we will use the

NewApexSqlDatabaseConnection cmdlet. In a common workflow, this connection may be used by individual steps to target a QA or staging database.

Syntax:

Using Windows authentication

New-ApexSqlDatabaseConnection -ConnectionName <string> -Server <string>
-Database <string> -WindowsAuthentication

Using SQL Server authentication

New-ApexSqlDatabaseConnection -ConnectionName <string> -Server <string>
-Database <string> [-UserName <string>] [-Password <string>]

Required parameters:

ConnectionName: A custom name for this connection

Server: The name of the SQL Server instance containing this database

Database: The name of the database we are connecting to

UserName: The SQL Server login name for this SQL Server instance

Password: The password for the login defined above

Optional switch:

WindowsAuthentication: By replacing the -UserName and -Password parameters with this switch you accept this as a trusted connection and the default Windows authentication will be used.

Example:

$My_QA_db = New-ApexSqlDatabaseConnection 
-ConnectionName "QAdb"  
-Server "ServerName" 
-Database "QAdb$($My_CI_options .Timestamp)" 
-WindowsAuthentication

In this example, we create a connection to the QAdb database (the database will be created on this server) and added a timestamp suffix, which is taken by the global options we set up earlier. This way we create a unique database for every pipeline execution.

SQL script file

To use a .sql script file as a data source we will need the New-ApexSqlFileConnection cmdlet. This can be particularly helpful in a scenario where we use the output files of a CI pipeline as input to start a CD pipeline.

Syntax:

New-ApexSqlFileConnection [-ConnectionName] <string> [-FilePath] <string>

Required parameters:

ConnectionName: A custom name for this connection

FilePath: The full path to the .sql script file we want to use as a data source

Example 1 :

$TFS_Build_File = New-ApexSqlFileConnection 
-ConnectionName "buildfile" 
-FilePath "C:\CICD\CI_Pipeline\03_15_2017_20-03-27\tfs_qaDb_Build_script.sql"

Here, we create a connection to a SQL script file created by the CI_Pipeline (variable $TFS_Build_File) that can be used as a source for subsequent pipeline steps.

Example 2 :

$TFS_Build_File = New-ApexSqlFileConnection 
-ConnectionName "buildfile" 
-FilePath "$My_CI_options.BuildScripts[1]"

In this example, we use the file path defined in $My_CI_options. The BuildScripts property is a zero-based array of strings, populated with a new value after each Build step is executed in the current pipeline. So, by using [1] index, we point to the second BuildScript created in $My_CI_Pipeline.

Quick tip icon

Quick tip:

All ApexSQL DevOps toolkit cmdlets support the PowerShell common parameters. You can find more information about them here.

With the cmdlets described above, we explained the initial configuration by setting up the notification settings, global options and data sources for our pipeline. When this is done, we can move on combining the individual steps to create a one-click PowerShell script to run a custom pipeline.