How do you maintain your database without taking your site down?

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

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 version¹, so this kind of schema change can be done online² 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 A.

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.

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.