ssiscicd

Demo code for SQL Server Integration Services Continuous Integration Continuous Delivery on Microsoft Azure Cloud

ANNOYANCE @MICROSOFT: To compile dockerFiles on Visual Studio 2019, you must have Docker Desktop setup on your VM. However Docker Desktop is not available for Windows Server 2019, only Windows 10. You must use Windows Server 2019 as the base OS for windows containers running on AKS. So despite including container support in VS2019, that support is meaningless when trying to build windows containers for AKS.

ANNOYANCE @MICROSOFT: In order to deploy dacpacs which reference the master db, the master.dacpac (and probably msdb.dacpac) are required to be included in the project. Why is this? It seems like sqlpackage should be smart enough to allow users to circumvent the errors, and assume that a master db exists, as an option.

ANNOYANCE @MICROSOFT: Why does the Powershell@1 task wait for the script to finish and Powershell@2 does not? Very confusing, please explain. "This is a breaking change."

ANNOYANCE @MICROSOFT: Why is not possible to extract the commandOutput easily into a variable when using Kubernetes@1? Not being able to do this means that get and describe are basically worthless when used in this task. You must write a script task instead.

Setup Agent for DevOps Pipeline

1. Create a Dev Test Lab environment
2. Create a new VM within the environment on the Windows Server 2019 Data Center image
3. RDP into the machine
4. Install and configure the tools needed for your build pipeline
   1. Install the SQL Server Data Tools for Visual Studio 2017 using the Standalone installer, https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15#ssdt-for-vs-2017-standalone-installer
      1. Find devenv.exe. On my build box, it is here: "C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\devenv.com"
   2. Install kubectl by following these instructions https://kubernetes.io/docs/tasks/tools/install-kubectl/#install-kubectl-on-windows
      1. I add kubectl to C:\agentTools\kubernetes
      2. Add this to your path
   3. Install OpenJDK because Java is required by Maven (https://jdk.java.net/13/)
      1. I unpack to C:\agentTools\java
      2. Add C:\agentTools\java\jdk-13.0.1\bin to your path
      3. Add the JAVA_HOME env variable with the value C:\agentTools\java\jdk-13.0.1\bin
   4. Install maven by downloading and following these instructions
      1. https://maven.apache.org/download.cgi
      2. https://maven.apache.org/install.html
      3. I unpack to C:\agentTools\maven
      4. Add C:\agentTools\maven\apache-maven-3.6.2\bin to your path
   5. Install the Azure CLI https://docs.microsoft.com/en-us/cli/azure/install-azure-cli-windows?view=azure-cli-latest
   6. Install sqlpackage.exe to deploy dacpacas https://docs.microsoft.com/en-us/sql/tools/sqlpackage-download?view=sql-server-ver15
   7. Install Microsoft® ODBC Driver 17 for SQL Server® - Windows, Linux, & macOS, https://www.microsoft.com/en-us/download/details.aspx?id=56567
   8. Install sqlcmd.exe to run unit tests from self-hosted agent https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15
5. Setup the agent on the VM, so that Azure DevOps Pipelines can run builds on this machine (https://docs.microsoft.com/en-us/azure/devops/pipelines/agents/v2-windows?view=azure-devops)
   1. Pro-tip: set everything up, get it to work, and then commit to killing the VM and using it to create a custom image. Before you do that though, run ./config.cmd remove in powershell, so that you can configure each new instance of the image as a unique build agent in Azure DevOps.

Setup Developer Database/AKS

• https://docs.microsoft.com/en-us/azure/aks/windows-container-cli
• https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-2017

Creates AKS -- note that we removed monitoring

az aks create \ 
    -g <yourResourceGroup> \ 
    --name ssiscicdAKS \
    --node-count 1 \
    --kubernetes-version 1.14.6 \
    --generate-ssh-keys \
    --windows-admin-password $PASSWORD_WIN \
    --windows-admin-username azureuser \
    --vm-set-type VirtualMachineScaleSets \
    --network-plugin azure

Add a windows node pool

    --resource-group <yourResourceGroup> \
    --cluster-name ssiscicdAKS \
    --os-type Windows \
    --name npwin \
    --node-count 1 \
    --kubernetes-version 1.14.6

Configure kubectl to hit our AKS

az aks get-credentials --resource-group <yourResourceGroup> --name ssiscicdAKS