How to deploy to 300 databases across 2 SQL servers?

Brice

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. 

1. Is there a way to make the database list dynamic v hardcoding the names?
2. How is that done when using Azure CI/CD?
3. Is it possible to also make the server dynamic?
4. 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

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

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

DanC

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

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

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