We are currently looking at our deploy process, which consists of an application tier and data tier for a web solution.
We'd like to develop a deployment strategy that does not require downtime for our sites.
From an application point of view this is simple enough – we have a load balanced environment, and can turn servers on and off as we deploy the code. However we are not really sure how to approach the database side of things, which at the moment is a bit of a single point of failure.
The database is used for both reads and writes, and it is not really feasible (as far as we can see) to turn either off without bringing sites offline. So we need to keep the database in sync with whichever version of the application is currently servicing requests. We also need to allow enough time for the database deploys to be made, without causing any problems.
Is this a solved problem in DBA circles? Does anyone have any tips, stories or resources that might help us approach this problem? At the moment we are not really sure what our first step in this process should be.
We are using SQL Server 2008
Best Answer
If you're talking SQL Server, I have some specific advice about making database changes backward compatible. The ideas and concepts are quite similar to other platforms, but the code samples won't help much.
The basic idea is this: make your changes in such a way that they don't require downtime and that they don't break existing code. For example, if you add a new parameter to a stored procedure, don't make it required. You can start out by making it optional, this way you can change the application to support the new parameter later, and it doesn't break in the meantime (and the code in the procedure can use logic to decide what to do, depending on whether the parameter was supplied). This can allow you to update multiple applications in a staggered way instead of all at once, and also allows you to change the application to the old behavior without also having to roll back the database changes. You can then make the parameter required after all the applications have been updated to support it.
Big depends here, because I have no idea what kind of scope your database changes encompass, but here are some ideas about how I accomplished this for specific changes:
Make your SQL Server database changes backward compatible when adding a new column
Make your SQL Server database changes backward compatible when dropping a column
Make your SQL Server database changes backward compatible when renaming an entity
Make your SQL Server database changes backward compatible when changing a relationship
That all said, your deploy scripts for databases should be using the same type of methodology as your code, including repeatable and verifiable scripts stored in source control as opposed to hand-written instructions about where to point and click in some UI to make a change. Why should your mentality about how to deploy database changes really be any different from how to deploy code cha