View on GitHub

SqlBuildManager

SQL Build Manager is an all in one tool for building and maintaining a package of SQL scripts to manage builds and updates to your SQL Server database.

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 2015

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

Visual Studio 2017

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

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

Tips on local builds and running unit tests

If you have installed the SqlBuildManager.Services.Host as a windows service in the bin\debug folder of the solution, you will need to run Visual Studio as an administrator. This is because VS will need to run net stop and net start on the service to get the build to complete.

Notes in 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

SQL Express

In order to get some of the unit tests to succeeed, 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.

Remote execution server (running locally)

If you are leveraging a remote execution server (NOTE: this is a depricated feature) you will need to setup this service locally and make sure it is running. The service will need to run under a local administrator account.

Create the local administrator account

You might need to create a new local user on your machine to get the service host permissions to access the database. To do this:

  1. Create a new local account sqlbuildmanager (suggestion) to your machine - remember the password!
  2. Change the account type to Administrator - this is so that the account can start the local service

Add the new local account to SQL Express

  1. Connect to your SQLExpress local server via your favorite tool
  2. Run the following scripts (replace <machinename> with your machine name)
    USE [master];
    CREATE LOGIN [<machinename>\sqlbuildmanager] FROM WINDOWS;
    GO
    USE [msdb];
    CREATE USER [<machinename>\sqlbuildmanager] FOR LOGIN [<machinename>\sqlbuildmanager]
    GO
    sp_addsrvrolemember [<machinename>\sqlbuildmanager], 'sysadmin'
    GO
    

    Install the SqlBuildManager service with the new local account

  3. Open a command prompt as an administrator
  4. Navigate to the bin folder for the SqlBuildManager.Services.Host: SqlBuildManager\SqlBuildManager.Services.Host\bin\Debug
  5. Run the command: SqlBuildManager.Services.Host.exe /install /username=<machinename>\<new admin user> password=<new admin password>
  6. Check to make sure install was successful - open the srevices plugin by typing “Services” in the Windows 10 search bar
  7. On the Services control window, look for SqlBuildManager.Service
  8. Right-click on the service and Start the service

Troubleshooting test errors

If your tests are still failing, check the log file generated by the service. It will be located in SqlBuildManager\SqlBuildManager.Services.Host\bin\Debug\SqlBuildManager.Services.log. Reviewing the logs should give you some insight into the issue.

SQL Package

sqlpackage.exe is needed for the use of the DACPAC features of the tool. It should already be available in the Microsoft_SqlDB_DAC subfolder where you are running your tests. If not, you can install the package from here https://docs.microsoft.com/en-us/sql/tools/sqlpackage-download?view=sql-server-2017. The unit tests should find the executable but if not, you may need to add the path to \SqlBuildManager\SqlSync.SqlBuild\DacPacHelper.cs in the getter for sqlPackageExe.

Setting up a test Azure Environment

Create Test Target databases

To test against Azure databases, you will need some in Azure! The following PowerShell will create an Azure SQL Server, an Elastic Pool and “X” number of databases for that pool. This can be done locally or via the Azure Cloud Shell (http://shell.azure.com)

  1. Create the Resource Group and Server (change your values accordingly)
$ResourceGroupName = "SqlResourceGroup"
$Location = "East US"
$ServerName = "TestServer001"

New-AzResourceGroup  -ResourceGroupName $ResourceGroupName -Location $Location 

New-AzSqlServer -ResourceGroupName $ResourceGroupName -Location $Location -ServerName $ServerName -ServerVersion "12.0" -SqlAdministratorCredentials (Get-Credential)

New-AzSqlServerFirewallRule -AllowAllAzureIPs -ResourceGroupName $ResourceGroupName -ServerName $ServerName

  1. Create the elastic pool
$ElasticPoolName="MyBasicPool2"
New-AzSqlElasticPool -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ElasticPoolName $ElasticPoolName -Edition "Basic" -Dtu 50 
  1. Create databases within the pool for testing
$DatabaseName="SqlDemo001"
New-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ElasticPoolName $ElasticPoolName -DatabaseName $DatabaseName
  1. Or to create a collection of databases you can use
$DatabaseName = "SqlDemo"

For ($i=1; $i -lt 101; $i++) 
{
    $dbNumber = $DatabaseName + $i.ToString("000")
    New-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName -ElasticPoolName $ElasticPoolName -DatabaseName $dbNumber
}

Creating a database target file

To create database target files for a parallel test execution, you can use the following script. Note that this will get every SQL Server and every database. You may want to add some customization to get only those that you want.

$outputFile = "C:\temp\databasetargets.cfg"
$servers = Get-AzResourceGroup | Get-AzSqlServer
foreach($server in $servers)
{
    $dbs = Get-AzSqlDatabase -ResourceGroupName $server.ResourceGroupName -ServerName $server.ServerName | Sort-Object -Property DatabaseName
    
    foreach($db in $dbs)
    {
        if($db.DatabaseName -ne "master")
        {
            $server.FullyQualifiedDomainName + ":client,"+$db.DatabaseName | Out-File -Append $outputFile
        }
    }
}