Use Azure SQL Database with Azure DevOps Server

  • Article

Azure DevOps Server 2022 | Azure DevOps Server 2020 | Azure DevOps Server 2019

Use the steps in this article to configure Azure DevOps Server with Azure SQL Database. This topology has a few additional steps compared with using an on-premises SQL server.

Requirements

  • Azure SQL Database can only be used with Azure DevOps Server 2019 and later versions.
  • You must install the Azure DevOps Server on Azure VM.
  • The VM must have system-managed identity enabled, which is used to authenticate to the Azure SQL database.
  • The VM must be joined to a domain but not a workgroup.
  • Azure SQL databases must be single databases. Managed instances and elastic pools are supported in Azure DevOps Server 2022 and newer.

All General Purpose and Premium SKUs are supported. Standard SKUs S3 and higher also are supported. Basic SKUs and Standard SKUs S2 and below aren't supported. Azure DevOps Server configurations that use Azure SQL Database don't support older SQL Server Reporting Services with SQL Server Analysis Services reporting features. Instead, you can use the Azure DevOps Analytics.

Upgrading to Azure DevOps Server is supported only from Team Foundation Server 2015 and newer when you use Azure SQL Database. Azure SQL Database doesn't support encrypted stored procedures.

Set up Azure SQL Database

  1. Configure a managed identity on your virtual machines. We only support system-managed identities at this time.

    You can run configuration by using all standard mechanisms, including the:

  2. To set up a new Azure DevOps Server instance, create two Azure SQL databases:

    • AzureDevOps_Configuration

    • AzureDevOps_DefaultCollection

      Note

      You can skip this step if you use existing databases to either:

      • Upgrade a new version of Azure DevOps Server.
      • Migrate an up-to-date instance of Azure DevOps Server to Azure SQL Database.

  3. Configure Azure Active Directory authentication for your Azure SQL Database server. Make yourself the Active Directory administrator on the server. You need administrator permissions on the database to complete the remaining configuration steps. You can change this permission later.

  4. Enable your managed identity, or identities if you use multiple servers, to sign in to your Azure SQL database and give it the appropriate permissions. Connect to the database server by using SQL Server Management Studio. Connect by using an Azure Active Directory user with Active Directory authentication. You can't manipulate Azure Active Directory users if you sign in to Azure SQL Database under SQL authentication.

    a. Run the following T-SQL command on the master database:

    CREATE USER [VMName] FROM EXTERNAL PROVIDER
ALTER ROLE [dbmanager] ADD MEMBER [VMName]

    Replace VMName with the name of the virtual machine whose managed identity you add to the database.

    b. Run the following T-SQL command on the configuration and all collection databases:

    CREATE USER [VMName] FROM EXTERNAL PROVIDER  
ALTER ROLE [db_owner] ADD MEMBER [VMName]  
ALTER USER [VMName] WITH DEFAULT_SCHEMA=dbo

Configure Azure DevOps Server

Return to the Azure DevOps Server configuration wizard. If you set up a new instance, select This is a new Azure DevOps Server deployment. If you upgrade or migrate and have existing data in your databases, select I have existing databases to use for this Azure DevOps Server deployment.

When you get to the Database page in the configuration wizard, specify the Azure SQL Database server instance. Typically, the server instance is in the form of SQLInstanceName.database.windows.net.

You now have an Azure DevOps Server instance that runs on Azure SQL Database.