View on GitHub

SqlBuildManager

SQL Build Manager is an all-in-one database management tool to easily update your fleet SQL Server databases - from one to tens of thousands.

Setting Up an Azure Environment


Getting started - Building Azure Resources

To get started leveraging Batch, Kubernetes, Container Apps or Azure Container Instance, you first need to create and configure resources in the Azure cloud. To automate as much of this as possible, there are PowerShell scripts in the /scripts/templates folder to leverage. See the associated Readme for full descriptions

NOTE: Before using these scripts, you will need to install both the Azure CLI and Kubernetes CLI (az aks install-cli) installed on your machine.

Steps

  1. In a PowerShell window, navigate to the scripts/templates folder
  2. Run the az login command to connect to your Azure account
  3. Run the create_azure_resources.ps1 file. You will be prompted for parameters:
    • -resourcePrefix - the prefix for the Azure resources. Must be 6 or less characters, all lowercase.
    • -Location - the Azure region to deploy the resources (you can get a list of available locations by running az account list-locations -o tsv --query "[].name")
    • -outputPath - optional, will default to the /scr/TestConfig folder, the location that the integration tests will look for configuration files
    • -testDatabaseCount - optional, default is 10. If 0, then the SQL Azure resources will not be created. If you plan on running any integration tests, this number should be greater than 1.

    There are additional optional parameters you can set:

    • -build - whether or not to build the sbm command line code in the sbm.csproj file and upload it to the Azure Batch account
    • deployBatch - whether or not to build and Azure Batch environment. If you don’t plan on using Batcj, you can set this to $false
    • -deployAks - whether or not to build the AKS cluster. If you don’t plan on using Kubernetes, you can set this to $false
    • -includeAci - whether or not to include settings for Azure Container Instances (ACI). If you don’t plan on using ACI, you can set this to $false
    • -deployContainerAppEnv - whether or not to build the an Azure Container App environment . If you don’t plan on using Container Apps, you can set this to $false
    • -deployContainerRegistry - whether or not to create a private Azure Container Registry. This is recommended! To use public images, you can set this to $false

What does the script do?

The create_azure_resources.ps1 script will create the following resources which will be ready to start processing your database builds once it’s complete. Each resources is prefixed with, you guessed it, the value you provided in the -resourcePrefix argument

In addition to creating the resources above it will create the following files in the outputPath location folder. These are used by the SqlBuildManager.Console.ExternalTest project (which is also a great place to look to see execution examples):

  1. settingsfile-batch*.json - batch settings files that contains all of the SQL, Batch, Storage and Service Bus endpoints and connection keys for use in testing. There will also be two files ending with -keyvault.json that will not contain any secrets, but will instead contain the Key Vault name. The secrets will also have been saved to the Key Vault.
  2. settingsfile-aci-*.json - settings files for ACI builds. This will not contain secrets as ACI will always leverage Key Vault.
  3. settingsfile-containerapp-*.json - settings files for Container App builds
  4. settingsfile-k8s-*.json - settings files for Kubernetes builds
  5. settingsfilekey.txt - a text file containing the encryption key for the settings files
  6. databasetargets.cfg - a pre-configured database listing file for use by the integration tests that use an SBM file as a script source
  7. clientdbtargets.cfg - a pre-configured database listing file for use by the integration tests that use a DACPAC as a script source

IMPORTANT: These files can be used as is for the integration testing but are also great reference examples of how to create your own files for production use


Notes on Unit Testing

NOTE: There are currently some concurrency issues with the unit tests. You may get some failures in a full run that will then succeed after running aain, selecting only the failed tests

There are three types of Tests included in the solution:

  1. True unit tests with no external dependency - found in the ~UnitTest.csproj projects
  2. Those that are dependent on a local SQLEXPRESS database - found in the ~.Dependent.UnitTest.csproj projects. If you want to be able to run the database dependent tests, you will need to install SQL Express as per the next section.
  3. Integration tests that leverage Azure resources for Batch and Kubernetes. These are found in the SqlBuildManager.Console.ExternalTest.csproj project. To run these tests, first run create_azure_resources.ps1 with the -testDatabaseCount value >1 (the default is 10). This will create the necessary resources and test config files (in /src/TestConfig folder) needed to run the tests.

SQL Express

In order to get some of the unit tests to succeed, you need to have a local install of SQLExpress. You can find the installer from here [https://www.microsoft.com/en-us/sql-server/sql-server-editions-express] (https://www.microsoft.com/en-us/sql-server/sql-server-editions-express). You should be able to leverage the basic install.


Visual Studio Installer Project

For Visual Studio 2015 and beyond, you will need to install an extension to load the installer project (.vdproj)

Visual Studio 2022

https://marketplace.visualstudio.com/items?itemName=VisualStudioClient.MicrosoftVisualStudio2022InstallerProjects

Visual Studio 2017 and 2019

https://marketplace.visualstudio.com/items?itemName=VisualStudioClient.MicrosoftVisualStudio2017InstallerProjects

Visual Studio 2015

https://visualstudiogallery.msdn.microsoft.com/f1cc3f3e-c300-40a7-8797-c509fb8933b9

If you are having trouble with the installer project loading try disable extension “Microsoft Visual Studio Installer Projects”, reenable, then reload the projects.