0

We have the following setup - 2 SQL servers(01 & 02) configured as AlwaysOn with a listener node/alias (LT) pointing to which server is currently primary(01/02).

What would be the best way to configure an Azure DevOps pipeline to deploy changes to the SQL servers. Along with deploying the changes the release pipeline also needs to include steps for restoring a new DB backup from another server.

My thought is to configure everything on listener(LT) this includes running the power shell script to create the AzDo client as well. But what will happen once the servers(Primary & Secondary) are switched?

BDZ
  • 43
  • 8
  • What is the file type of your sql server backup? If the backup is Dacpac file, you could use [SQL Server database deploy task](https://github.com/microsoft/azure-pipelines-tasks/blob/master/Tasks/SqlDacpacDeploymentOnMachineGroupV0/README.md) to restore the backup. And for running the script to deploy changes. this task also supports to run the sql script. – Kevin Lu-MSFT Aug 21 '20 at 05:57
  • Thanks of the suggestion! The file type is in fact dacpac. My inquiry though is on trying to deploy this on a server with AlwaysOn set up and how to setup(and which server to setup) the connection of the remote server and AzDo. – BDZ Aug 22 '20 at 16:35
  • Since your sql server is localhost, you could create a [self-hosted agent](https://learn.microsoft.com/en-us/azure/devops/pipelines/agents/agents?view=azure-devops&tabs=browser#install). Then you could run the SQL Server database deploy task on self hosted agent. In this task, you could set the method (Password or Windows authentication) to connect to the sql server. – Kevin Lu-MSFT Aug 26 '20 at 08:39

0 Answers0