How to deploy to 300 databases across 2 SQL servers?

Brice · March 24, 2023 1:21PM

Currently we are looking at SQL Change Automation for database deployments. I already watched the video on how to do the deployments but have some questions.

Is there a way to make the database list dynamic v hardcoding the names?
How is that done when using Azure CI/CD?
Is it possible to also make the server dynamic?
How does this extend to all environments?

I know our current number of databases is the norm for most people. We are working on a solution to make the database more multi tenant. As many know this is going to be a long process. Any advice or examples would be greatly appreciated. Thanks.

DanC · March 27, 2023 8:58AM

Hi @Brice

Could you link me the video you're referring to in order to help me answer these questions?

For Azure CI/CD please see the following section: https://documentation.red-gate.com/sca/deploying-database-changes/add-ons/azure-devops

Brice · March 27, 2023 11:38AM

Here is a link to the video I had seen.
Multi-Database Deployments with SQL Change Automation using Azure Pipelines - YouTube

DanC · March 27, 2023 12:06PM

Thank you for the added context on the video!

Just want to clarify some other bits before I provide any advice, are you working from a single repository and just need to build and deploy those changes to multiple environments (databases)?

Brice · March 27, 2023 12:08PM

Correct it will be a single repository to multiple databases on 2 different servers. All of the databases use the same schema. They are different customers.

DanC · March 27, 2023 1:05PM

In this case, you could have a single pipeline with multiple release tasks for each environment using the add-ons -https://documentation.red-gate.com/sca/deploying-database-changes/add-ons/azure-devops/use-the-azure-devops-build-extension-with-sql-change-automation-project

Alternatively, you could use the PowerShell instead and use variables which you can pass through at run time for the environments -https://documentation.red-gate.com/sca/reference/powershell-cmdlets

If you wanted some help putting this through a POC, I'd advise getting in contact with some of our partners here: https://www.red-gate.com/partners/find-a-partner

How to deploy to 300 databases across 2 SQL servers?

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk