What are some techniques for updating a production server's code base / database schema without causing any downtime?
asked Jan 9, 2011 at 10:31
-
1Good question because I see so many people overlook this. Time is money and downtime never looks good to end-users, regardless of how legit the reason.– Dan McGrathCommented Jan 9, 2011 at 10:58
-
@Dan McGrath: which suppose you actually can have a downtime, I work for a system that is (normally) down only 4 times a year (quarter outage) and for 15 minutes maximum each time (during which time the traffic is queued)... database changes are heavily scrutinized :)– Matthieu M.Commented Jan 9, 2011 at 11:47
-
2This would be a great question for dba.stackexchange.com, which goes into public beta in a few hours.– Larry ColemanCommented Jan 10, 2011 at 13:55
Add a comment
|
4 Answers
Generally, the websites I've worked on that had this sort of requirement were all behind load-balancers, or had separate failover locations. In this sample, I'll presume that you've got a single load balancer, 2 web servers (A & B) and 2 database servers (M & N - usually DB servers are linked via logshipping - at least in the SQL Server world).
- Webserver A to be disconnected from load balancer (so all incoming traffic goes to B).
- Log shipping is stopped (DB Server M is going to get updated first).
- Update Webserver A. Point the configuration to DB Server M.
- Test and verify that the update worked (usually folks are hitting the IP address directly).
- Set the load balancer so that existing sessions continue to go to B. New sessions go to A.
- Wait for all sessions on B expire (might be a half hour or more, usually we watch traffic and have a 1 hour break scheduled).
- Update B and N.
- Test and verify that the update worked.
- Set up log shipping again and test it works.
- Set the load balancer to regular operation.
In a very complicated web applications, what is described as steps 1-5 might take all night and be a 50 page Excel spreadsheet with times and emergency contact numbers. In such situations, updating half the system is scheduled for 6pm to 6am while leaving the system available to users. Handling the update for the DR site is usually scheduled for the following night - just hope nothing breaks the first day.
Where uptime is a requirement, patches are tested first on the QA environment, which ideally is the same hardware as production. If they show no disruption, they can then be applied on the regular schedule, which is usually on the weekend.
-
10How do you propose merging new data from DB M and DB N? They will both have new, updated and deleted records that the other doesn't have. Commented Jan 17, 2017 at 18:56
-
-
@sixtyfootersdude You can use SQL Server Replication Technologies like High Availability (HA) or Group Replication(mySQL)– AminMCommented Jul 8, 2021 at 19:54
For typical databases (Oracle for example) it's possible to alter the database schema while still running queries in parallel. It requires some forward planning though.
Their are some constraints for the change to be applied:
- it should work with the existing code, meaning that the code should deal with both the old and new versions of the schema
- it should not incur such load on the DB that transactions would screech to a halt (I am looking at you
CREATE INDEX) - it should not incur the loss of data (you cannot drop and re-create a table)
In order for the schema to be backward compatible, you usually can ADD or MODIFY a column, you can only DROP something if the existing code does not use it any longer.
If your code cannot handle the change transparently, then change the code before changing the database.
Simple advice on forward planning: always explicit the column names in your DB requests (don't use SELECT * FROM). This way you won't have new columns showing up in old requests.
answered Jan 9, 2011 at 11:54
-
1Actually, for forward planning and adaptability, select * from is infinitely better than listing columns manually. Using explicit column names results in a lot of technical debt in most cases. If your code breaks from new columns, your code is already broken.– Morg.Commented Nov 18, 2013 at 16:20
-
@Morg.: Not really. For security you need to use bind variables, which in the framework I use (at least) requires providing the variables to write to, and there needs to be exactly as many variables as there are output columns, thus
select *means that the code breaks if a new column is added (for lack of a variable to write it to). Of course, this may be the result of using a strongly typed language. Commented Nov 18, 2013 at 17:52 -
Yes really, there is no added security in avoiding select *. It has nothing to do with strongly typed languages and everything to do with very bad design. If your framework cannot handle change seamlessly, it's useless. When I change a column, my application never stops working. When you do it breaks. I don't think there's any question as to which one is more reliable or secure.– Morg.Commented Nov 20, 2013 at 9:23
-
@Morg.: I fail to see how
select *is more reliable and secure. If you used to haveselect one, two from ...then you were only usingoneandtwo; ifthirdis added to the table, then you have no use for it (here), so there is no reason to retrieve it. And if you need to suddenly use it, then you will modify the code, so you might as well modify the query at this point! Commented Nov 20, 2013 at 9:30 -
@Morg.: Well, it seems we are talking past each others, probably because our experiences differ. I work on products where performance is a paramount property, and this means that
selectneed be as selective as possible (and covered by an index) otherwise I am toast (even before the obligatory joins). I am sorry to say, but the approach you are describing was an utter failure on those products. Commented Nov 22, 2013 at 8:55
Not all systems can, it has to be set up in a manner that supports it.
For example, one of our major systems I helped upgrade a few years ago should be available 24/7. It consisted of multiple tiers, including a pure communication tier between the off site User Interface Layer and Business Layer. Due to the way the communication layer was coded, any future changes to the Business Layer or DB schema could be implemented without a real outage. In the worst case scenario, a user would experience a 10-30 second pause as the changes took effect.
If the changes were purely code changes to the business layer, they could be queued up and 'cycled in' with only milliseconds delay.
It could do this because:
- The communication layer could hold messages. This allowed us to have an actual outage on any of the tiers other than the UI Layer without needing to to bring down the UI.
- The Business Layer handled by the MVDB called UniData. This holds all code in memory. After compiling code, you can use a command to force the new object code into memory, replacing the old.
Other techniques involve replication of transactions to another mirror of the existing system. By applying the update to one, switching over and replaying all transactions done between the update and switch. YMMV depending on your systems though.
answered Jan 9, 2011 at 10:41
Here's a different perspective, from the world of embedded database systems and embedded systems. Embedded systems include various network/telecommunications infrastructure equipment, and in this realm they often talk about 99.999% (five 9s) uptime.
We (McObject) are the vendor of the eXtremeDB family of embedded database system products, including eXtremeDB High Availability.
First, understand that "embedded database" means that the database system is a library that is compiled and linked in with your application code; in that sense, it is "embedded" in your application.
With eXtremeDB High Availability, there is a MASTER instance of your application (which might be one or several processes) and one or more REPLICA instances of your application. When a replica establishes a connection to the master, it receives a copy of the master's database through a process called "initial synchronization". This can be done while the master application continues its work. Once syncrhonized, it receives the master's transactions through replication. Therefore, a replica always has current data and can take over (through a process called failover) in the event the master fails.
One feature of the initial synchronization is called "binary schema evolution." In plain English, this means that the process of populating the replica's database will accommodate differences between the replica's database schema and the master's database schema.
In practice, this means that you can build a newer version of your application (with new/dropped tables, new/dropped/changed fields, new/dropped indexes), attach that new version of your application to a master, and then cause that newer replica to become the new master (i.e. force a failover to the new replica so it becomes the master and the old master shuts itself down). Voila, you have migrated your application from version N to N+1, without interrupting the availability of your system. Now you can go about upgrading the old master and any other replicas to version N+1.