Build Server Setup
Azure Pipelines provides access to a range of Microsoft-hosted agents. These can be used to build your SQL Server projects, which gives you some faith in the quality of your software. However, these hosted agents can only deploy to Azure-hosted services such as Azure SQL Database and Azure Analysis Services. SSIS and SSRS projects are left out in the cold.
To effectively work with on-premise SQL Server instances, you need an in-house build server with the Azure Pipelines self-hosted agent installed.
A build server only performs when a developer checks in code, or perhaps overnight during a test run. So virtual machines are ideal candidates to act as build servers. We recommend your build server runs Windows Server 2016 or whatever OS your target servers are running.
To keep everything self contained, we recommend that your build server has the following SQL Server components installed:
- SQL Server Database Engine
- SQL Server Integration Services (SSIS)
- SQL Server Analysis Services in Multidimensional or Tabular mode
Of course, the version of SQL Server running on your build server must match your target platform. So, for example, if your production server is running SQL Server 2012 Standard Edition, then you must have SQL Server 2012 Standard Edition installed on your build server. If you are planning a production server upgrade, then create a second build server with the new target SQL Server version installed.
How to install the agent is well documented by Microsoft, so will not be repeated here. However, we recommend the following:
-
Create a domain-based service account in Active Directory for the build agent to run under (e.g. yourdomain\svcBuildAgent). This account should have a password that never expires, otherwise your builds will stop working when the password expires!
-
Install and configure the Azure Pipelines self-hosted agent in the manner described by Microsoft.
-
Change the VSTS Agent service so that it uses the new service account. The build agent service will appear in Services Management Console (services.msc) as VSTS Agent (YourDomain.AgentName).
-
Provide SysAdmin / Admin privileges to the build agent service account on SQL Server in order for it to be able to successfully deploy databases and tabular cubes. Give the account SysAdmin privileges on your target SQL Server instance and admin privileges on your SQL Server Analysis Services instance. If you choose not to install SQL Server on your build server, then as outlined on Microsoft's guide to Azure Pipelines self-hosted agents, the agent must have "line of sight" connectivity to these target servers.
- Visual Studio 2017 Community Edition which includes numerous components including:
- SQL Server Data Tools (SSDT) for Visual Studio adds support for Analysis Services, Integration Services and Reporting Services to Visual Studio
Annoyingly Visual Studio requires that a user account be logged into Visual Studio to properly license the product - even Community Edition! There are no command-line flags to stop Visual Studio from prompting for a user to login. So ensure you give your build agent service account an email address and connect to the build server using the service account and login to Visual Studio. Alternatively, unlock Visual Studio with a product key which will stop the prompts. Note that connecting to the build server with your own credentials and logging into Visual Studio with your own credentials will not stop the problem as the two accounts are not linked. You need to connect to the build server with the service account and enter some credentials that never expire.
Once done, you should never have the popup problem again. Unless of course the password of the service account expires!
Hence we recommend above that build agent runs under a service account with a password that never expires, otherwise your builds will stop working when the password expires.
Another annoying dialog which will stop your builds is the SSAS Tabular Model Designer which, when opening a model for the first time, will prompt you to select a workspace.
This is easily solved by connecting to the build server using the build agent service account and opening up a tabular mode in Visual Studio. Select integrated workspace and click OK so that you never have this problem again.
Note that by default, the SQL Server installed also installs the old SQLPS PowerShell module rather than the new SqlServer PowerShell module. The conflict between the two can cause no end of problems, so we highly recommend deleting the following environment variable from your build server.
