ApexSQL DevOps toolkit PowerShell FAQs about initial setup and configuration

Applies to
ApexSQL DevOps toolkit

Summary
This article answers on frequently asked questions regarding ApexSQL DevOps toolkit PowerShell cmdlets initial setup and configuration.

Description

Q: What ApexSQL tools do I need to create a CI or CD pipeline with my build server?

A: That depends on the specific steps e.g. Build, Document, Generate, you want to include.

Here is a table of steps, with a description and the ApexSQL tool you will need:

Step

Description

Tool
required

Method to invoke

Invoke example

Build

Build a database from
repository

ApexSQL Build

Invoke-
ApexSqlBuildStep

Invoke-
ApexSqlBuildStep –
Options $options –
Source $buildFile –
Database $qaDB –
StopOnFail $false

Populate

Populate a database with test data

ApexSQL Generate

Invoke-
ApexSqlPopulateStep

Invoke-
ApexSqlPopulateStep –
Options $options –
Database $qaDB –
RowCount 1000 –
FillOnlyEmptyTables

Audit

Integrate trigger based
auditing in a database

ApexSQL Trigger

Invoke-
ApexSqlAuditStep

Invoke-
ApexSqlAuditStep –
Options $options –
Database $qaDB

Test

Run tSQLt tests against a database

ApexSQL Unit Test

Invoke-
ApexSqlTestStep

Invoke-
ApexSqlTestStep –
Options $options –
Database $qaDB |
Out-Null

Review

Run rules testing against a database

ApexSQL Enforce

Invoke-
ApexSqlReviewStep

Invoke-
ApexSqlReviewStep –
Options $options –
Database $qaDB

Document

Fully
document a database

ApexSQL Doc

Invoke-
ApexSqlDocumentStep

 

Invoke-
ApexSqlDocumentStep –
Options $options –
Database $qaDB –
AsPdf

Schema sync

Create schema sync script against source and target
database

ApexSQL Diff

Invoke-
ApexSqlSchemaSyncStep

Invoke-
ApexSqlSchemaSyncStep
-Options $options –
Source $scriptFolder
-Database
$productionDB

Data sync

Create data sync script against source and target
database

ApexSQL Data Diff

Invoke-
ApexSqlDataSyncStep

Invoke-
ApexSqlDataSyncStep -Options $options -Source $scriptFolder -Database
$productionDB

Note that more tools and steps may be added to the toolkit over time.

Q: What are the tools and system requirements to configure this toolkit?

A: Requirements for configuring this toolkit are:

Q: How do I get/download the PowerShell scripts in the ApexSQL DevOps toolkit?

A: You can download PowerShell scripts from ApexSQL Downloads page, under the ApexSQL DevOps toolkit section, or you can use a the following direct link.

Q: What form are the PowerShell scripts in? Are they cmdlets, open source scripts, available in Windows PowerShell etc?

A: PowerShell scripts used for ApexSQL DevOps toolkit are cmdlets; are open source scripts, that can be edited and customized based on the user needs/preferences. They are also available in Windows PowerShell 5.0, after cmdlets installation.

Q: Once downloaded how do I access the cmdlets?

A: cmdlets can be used by opening them in Windows PowerShell ISE or they could be integrated into one of the supported CI/CD systems, such as Bamboo, Jenkins, TeamCity, and others which could be seen on the ApexSQL DevOps toolkit product page.

Q: Do I need to any additional scripts myself to complete the integration? Are these cmdlets available for copy/download or do I have to write them myself?

A: No additional scripts are required. The only thing that needs to be done is to run the Install.ps1 PowerShell script, that will integrate cmdlets into Windows PowerShell, that needs to be restarted upon installing cmdlets.

If you want to integrate ApexSQL DevOps cmdlets into a CI/CD server, you just need to provide a path to ApexSQL DevOps toolkit PowerShell scripts that will be used for the CI/CD process. You can check out our example scripts that could be used, from the following link.

Q: How do I install ApexSQL DevOps toolkit PowerShell cmdlets?

A: You can check out the complete process in the article on How to download and install ApexSQL DevOps toolkit PowerShell cmdlets.

Q: Do the CI/CD steps that involve an ApexSQL tool all require a pre-configured project file?

A: No. Pre-configured project files can be used for CI/CD steps, but they are not necessary.

Q: How are connections handled?

A: All connection logins are handled via provided information for data source switches in PowerShell scripts or via pre-configured project files that will encrypt all login information. Additionally, other ways of handling credentials can be created by editing the PowerShell scripts and you can check out the article about the ways of handling database/login credentials.

Q: How do I get updates to the ApexSQL DevOps toolkit and update my implementation?

A: Currently, the only updates that you can get is the regular automatic updates for installed applications (ApexSQL Build, ApexSQL Generate, etc.), as they have the update system. As for the new versions of PowerShell scripts used for the CI/CD process, the only way to get updates is to follow the What’s new and What’s next pages, and you can check out our Roadmap for ApexSQL DevOps toolkit, to get informed about any new version.

From future releases of the ApexSQL DevOps toolkit, when ApexSQL Dashboard will be introduced, automatic updates for the ApexSQL DevOps toolkit will provided.

Q: Do I need a build server to create a CI/CD pipeline?

A: No. Build servers are optional and the only thing that you need is access to any SQL Server, that will be used for the CI/CD process.

Q: Are there alternative implementations to PowerShell e.g. build server plug-ins or a client for point and click operation?

A: As of the writing of this FAQ, no. But, future releases will feature build server plug-ins (Bamboo, Jenkins, TeamCity, and Visual Studio Team Service) and ApexSQL Dashboard that will allow easier customization and use of CI/CD pipelines.

Q: How can I define database data source in PowerShell script?

A: You can define database data source using the following command using the Windows authentication: 

$name_of_the_datasource = New-ApexSqlDatabaseConnection -ConnectionName "ConnectionName"-Server"ServerName" -Database "DatabaseName" -WindowsAuthentication

If you using SQL Server authentication you need to use the following set of commands: 

$name_of_the_datasource = New-ApexSqlDatabaseConnection -ConnectionName "ConnectionName"-Server"ServerName" -Database "DatabaseName" -UserName "Unsername" -Password "password"

Q: Are the previous output files overwritten/deleted each time when I run again the same pipeline?

A: No. Every time when you run your pipeline, a new date stamped folder will be created that will contain date stamped output files.

Q: Should I install ApexSQL tools in the same location in order to make cmdlets work?

A: No. You can install ApexSQL tools in different locations, but they need to be installed one the same machine where cmdlets are integrated.

Q: Where can I find cmdlet references upon the integration is finished?

A: When PowerShell ISE is opened, you can find all cmdlet references under the Commands pane, in the Modules drop-down list and they are named ApexSQL_DevOps modules:

Q: How can I initially configure PowerShell scripts to set basic parameters?

A: You can check out the complete process in the article on How to initially configure the ApexSQL DevOps toolkit PowerShell one-click scripts.

Q: Is there a limitation of steps used in a pipeline?

A: No. You can set up as many steps as you want and even to repeat some, if needed.

Q: Is there an obligatory sequence of steps?

A: No. You can set up your own sequence of steps and the sequence provided in our example scripts is the most logical one when all available steps are included.

Q: Do I need to have all ApexSQL tools installed in order to use ApexSQL DevOps toolkit?

A: No. You can install only ApexSQL tools for steps that you’ll include in your pipeline.

Q: How’s ApexSQL DevOps toolkit licensed?

A: ApexSQL DevOps toolkit is free and the only licenses needed are for ApexSQL tools that will be used in your steps. Since CLI is required for each of ApexSQL tools in ApexSQL DevOps toolkit, each used tool needs to have the Professional edition.

 

Related posts:

  1. ApexSQL DevOps toolkit PowerShell scripts initial setup and configuration
  2. How to configure the ApexSQL DevOps toolkit PowerShell scripts for Continuous integration (CI)
  3. ApexSQL DevOps toolkit FAQs about notifications, outputs and summaries
  4. How to download and install ApexSQL DevOps toolkit PowerShell cmdlets
  5. How to integrate the ApexSQL DevOps toolkit PowerShell scripts with VSTS