Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

There are certain database maintenance tasks, like re-organizing indexes, moving files, changing schema, etc, that require disabling any applications that are using the database.

What are some good strategies for working around this, aside from just posting a message on your site like "we will be down from midnight to 4 AM EST for server maintenance"?

share|improve this question
Not all databases require downtime in order to do simple maintenance tasks like those listed. Use one of those, and all these workarounds are unnecessary. –  womble Sep 4 at 3:46

4 Answers 4

If you have a replication / high availability solution then using it to avoid downtime is the obvious choice, have one server upgraded while the other is working and then switch and upgrade the next one.

If you don't have such a structure in place, you can do a mini replication setup in the same server, where you have two copies of each database and upgrade one while the other is working and then synch the old one back. This will still require some downtime, but less than 4 hours.

The third option to avoid keeping both dbs in sync is to take a copy of the database and while one db is being maintained, the copy and the apps using it are in read only mode. After you are done you'd just switch the apps to the upgraded DB and start writing again to the database.

This last option requires of course application support and to make sense (there are applications where a read only mode makes no sense.)

share|improve this answer
Excellent suggestion @Vinko. I remember seeing the "flickr is in read only mode" warning a while back and thinking "wow, how did they do that", but thinking back on it, apart from passivating session data, most web applications will work quite happily without having to change their database. –  Dave Cheney May 15 '09 at 12:18
@Vinko, can you be more detailed about what you mean by "replication/high availability solution"? –  Eric Z Beard May 15 '09 at 13:19
@Eric: Replication is the technique used to have two or more databases in synch, there is more than one way to do this, but a usual one is a master/slave setup, where changes in the master are then replayed in the slave in order to have both DBs equal (Usually, the master and the slave are in different machines, to be able to access the other DB if the server dies). High availability uses replication to provide two or more servers with the same DBs and with a means to switch the server answering requests if one happens to fail. The actual mechanisms used in practice depend a lot on the ... –  Vinko Vrsalovic May 16 '09 at 11:33
... database servers used. You can Google for high availability <your RDBMS> or replication <your RDBMS> for details on the particular existing solutions. Or ask a different question here about that. –  Vinko Vrsalovic May 16 '09 at 11:34

If you're using SQL Server then you can always do index fragmentation removal online from SQL Server 2000 onwards. The command DBCC INDEXDEFRAG always performs an online reorganization. I wrote it specifically as an online alternative to DBCC DBREINDEX.

In SQL Server 2005 onwards, the ALTER INDEX ... REORGANIZE command replaces DBCC INDEXDEFRAG and is also online always. Also in 2005 onwards, with Enterprise Edition, you can do online index rebuilding using ALTER INDEX ... REBUILD ... WITH (ONLINE=ON). There are a couple of very short-term table locks required at the beginning and end of the operation, so it's not as online as REORGANIZE (and mostly-online index rebuild isn't such a good marketing term :-). You can even move indexes to new filegroups using CREATE INDEX ... WITH DROP_EXISTING and specifying ONLINE=ON.


share|improve this answer

The options available to you depend largely on which database engine you are using. You will want to start by taking whatever actions are necessary to enable online backups of your database, preferably allowing writes while the backup is in progress. This typically requires linear logging of transactions, which should also give you the ability to recover your database to a specific point in time by rolling forward through the transaction logs.

Table and index reorgs can be a bit trickier, but hopefully your database engine allows at least read-only access to the objects while they are being reorganized. If not, you may need to come up with a way for your applications to temporarily use a read-only clone of the table. If your DBMS offers little in the way of online maintenance, you will have to make tradeoffs at the application layer in order to redirect it to a partial or complete copy of the data.

Regardless of cost, database replication is almost always a complex feature to manage. Even worse is bi-directional replication, which would theoretically enable your applications to change data on the secondary database even while the primary database is down for maintenance. Replication is not impossible, but it does require a fair amount of planning and testing in order to behave reliably in production.

share|improve this answer

General Maintenance Tasks

Most of the maintenance tasks can be done without taking the website or app offline if you have database replication. You'll remove one DB from the replica-set, apply what you need, and connect it again to your replica-set. While it's off, other DBs will keep the solution running.

Changing Database Schema

When you need to update your database schema, you'll be forced to bring your solution down for some minutes (or to a readonly state) IF the change breaks the old version. If your new schema just creates tables or fields, it will not impact an old version1, so this kind of schema change can be done online2 and using a Blue-Green deployment for your application to achieve a high availability.

If your new schema renames an existing field or remove it, to achieve 100% of uptime, you will need to follow these steps:

Renaming a Field

  1. If you need to rename from A to B, apply a schema change that adds a new field B and duplicates A content. Also, keep A intact.
  2. Deploy a new application that uses the field B and do not use the field A.
  3. Apply a schema change that removes B.

Removing a Field

  1. Do not apply any schema change.
  2. Deploy a new application that doesn't use the field that will be removed.
  3. Apply a schema change that removes the field.

Note 1: some ORM tools, like the .NET Entity Framework, associates each schema change with a migration ID. So, when you deploy a new schema version, old applications will instantly break. It is also avoidable if you disable this check.

Note 2: if your new schema adds a unique constraint, check or foreign key, the alter table command may need some significant time if you have thousand of rows. While the alter table is processing, the table will be locked even for selects and this can lead to some query timeouts depending how big is your data.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.