Take the 2-minute tour ×
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

3 Answers 3

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
1  
@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
2  
... 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.

Thanks

share|improve this answer
    
Why (and whom!) did you thank? –  Vinko Vrsalovic Jul 23 '09 at 6:09

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

Your Answer

 
discard

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.