Zero Down Time

bernieconway

We are looking to add zero down time to our deploys. Basically all database column changes will be adds. We are currently using octopus deploy with the "RedGate - Deploy Database Package". Unfortunately the scripts created by this package seem to 
1) create a temp table 
2) copy data to new table
3) delete old table
4) rename the temp table to the new table name.

This script will not allow us to do zero down time.

Is there another way to use Redgate source control and deploy strategies for zero down time?

Thanks!

Ben M

Hey @bernieconway

We now recommend customers try our Pre/Post scripts approach for managing migrations which we recently released - its available on our frequent updates channel of source control ( you just need to opt in to frequent updates)

Take a look here for more info: - https://documentation.red-gate.com/soc6/common-tasks/working-with-pre-post-deployment-scripts

Thanks

chris_godfree

Have you tried using a migration script to control how the deployment is executed? https://documentation.red-gate.com/soc6/common-tasks/working-with-migration-scripts

AlexYates

This is typically a much bigger problem than just buying a tool - it's how you use it.

Yes, in some cases you need to write the scripts to deliberately work around some specific scenarios - this could either be handled using pre- and post-deploys or switching to a migration based source control solution, such as SQL Change Automation. (I mean by using the VS extension that was formerly called ReadyRoll INSTEAD of SQL Source Control. You will still use SQL Change Automation for deployment in either case. Redgate recently renamed stuff and now it's way harder to explain this.)

Generally, however, downtime is caused because of dependencies between different systems that need to be upgraded at the same time and the system cannot run while some parts have been deployed and others have not.

In that case you should look at the 'expand-contract' pattern and consider the wider reaching implications for the way your teams work together and co-ordinate your deployments.

You may find these resources valuable:

https://martinfowler.com/bliki/ParallelChange.html

https://medium.com/continuousdelivery/expand-contract-pattern-and-continuous-delivery-of-databases-4cfa00c23d2e

Apologies in advance - this isn't easy.

way0utwest

Can I ask what changes you're making here? Specifically? Adding a column ought not to do this, but if you are doing other things, this might be an issue.

There's no magic to gettting close to zero downtime. We are bound by the rules of SQLServer, and what we want to do with RG tools is make it easy to do what you'd do manually. Recommending a temp table is sometimes good, sometimes bad,but I'd have to know more here about what to do. However, you need to understand the way that your system works and what method for making changes is apporpriate.

I do think that some of the more complex changes are better handled with pre/post scripts, and I'm glad these got added for this reason.

I also like the SQL Change Automation client in VS, as this gives me complete control and replays the changes in the same way for downstream environments. This lets me control the way we make changes, as I might tackle different column adds in different ways, depending on the table.

You won't get zero downtime. You can get very close, and a table rename is often the quickest way. You can front this with a view as well, and recompiling the view to point to a new table is often even quicker, but there are always some locks being held.

Here I'd actually do this if I needed  the temp table for the Sales table.

1. Make dbo.Sales2 as the new table, with new schema

2. Copy data from dbo.Sales to dbo.Sales2 (this could be one step

3. Rename dbo.Sales to dbo.Sales_Old

4. Rename dbo.Sales2 to dbo.Sales

5. cleanup any data added between step 2 and 3.

If you try to make this one transaction, you won't get zero downtime. If you are trying to add a column and populate all rows with data in the same step, you won't get zero downtime. Actually, you don't get downtime, but blocking.

If you provide more information, we can help determine how to attack this problem.