Approaching Zero: Minimizing Downtime During Deployments
ABSTRACT
Negotiating a downtime for a release can be difficult in this modern, 24x7 world. When downtime means lost revenue, the price of taking a downtime to deploy code can run up quickly. This talk will show you a way to reduce the cost of deployment by reducing or even eliminating required downtime for database code deployment. We will show how to release new database code, update existing stored procedures, refactor and restructure tables, and even perform systemic re-architecture with no downtime and minimal blocking. We will use as a case study one release in which we modified the primary key column on 180 tables and updated over 550 stored procedures without end users noticing a thing. If you want to hit that three nines (or better) service-level agreement, this talk will help get you on the road to reduced downtime.ADDITIONAL MEDIA
No recordings or additional media are available for this talk.SLIDES
Click here to access the slides for this presentation.
The slides are licensed under Creative Commons Attribution-ShareAlike.
DEMO CODE
Click here to access demo code for this presentation.
The source code is licensed under the terms offered by the GPL.
LINKS & FURTHER INFO
Approaching Zero: the Series
- I have an entire series on minimizing application downtime. This talk was developed in conjunction with the blog series.
Overviews
- Michael J. Swart has an outstanding series on 100% online deployments. Michael's approach differs from mine a little bit, so it's definitely good to read his take too.
- Martin Fowler has a nice post on blue-green application deployment.
Locks
- Why creating a foreign key constraint locks the parent table as well as the child table.
- Michael J. Swart shows how pernicious the sch-m lock can be.
- The SQL Skills gang maintains an excellent resource on locks. Read through their articles on LCK_M_SCH_M, LCK_M_IX, LCK_M_SCH_S, and LCK_M_S, the four types of locks most likely to affect you during deployments.
Miscellaneous
- Michael J. Swart shows us how to swap user-defined table types by clever use of sp_rename.
- Andras Belokosztolszki explains why you want to rebuild a table after dropping large columns. Those rebuilds can happen online during the post-release phase.
- Remus Rusanu digs into adding a non-nullable column with a default column and how 2012 made this a metadata operation.
- Kenneth Fisher shows how to use partition switching to add an identity column with near-zero downtime. Kenneth also shows how you can use the same trick for changing the interval.