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"?
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.)
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
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.
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:
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.
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.
