Search code examples
sql-serverpowershellcontinuous-integrationredgate

Redgate DLM Automation - deploy database to multiple environments

I am using Redgare DLM Automation for database CI in a SQL Server and Visual Studio Team Services environment. I can easily deploy to multiple databases in a single environment, but apparently DLM Automation does not support multiple environments out of the box. Redgate support suggested using VSTS post-scripts in PowerShell, sqlcmd or something called "account_y" (I'm not sure what this refers to) to potentially add multiple environments.

Has anyone tried using DLM Automation for multiple environments? I have explored the PowerShell CmdLets, looked at SQL Compare options and filters, thought about using VSTS's Tokenizer for script alterations, but am still struggling with how to put all of this together to deploy to more than one environment.

Any experience or guidance would be greatly appreciated.

Thank you!

Solution

  • You definitely can deploy to multiple environments, however the issue of needing different user accounts for different environments is not a trivial problem to solve. Ultimately whatever you source control will be deployed to each environment, so if you need different user accounts then you will need to take care of it yourself by using some sort of post-deployment script.

    I would suggest not source controlling user accounts and then adding a custom step after deployment to add the users - either command line using sqlcmd or the equivalent powershell cmdlets.

    There are some blog posts that go into detail regarding this problem and their answers are probably more detailed than anything I can provide. I'd suggest that you have a read of them.

    I hope this helps.