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: 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
New–ApexSqlDatabaseConnection 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: 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.
