How should I perform database maintenance on a 24x7 system
- by solublefish
I'm a software developer who inherited a part-time DBA role. I'm responsible for an application backed by a small, high-volume 24x7 database on SQL Server 2008.
While there's other stuff in the DB, the critical piece is a 50GB, 7.5M row table that serves 100K requests/sec during peak load, and about half that at "night". This is 99%+ read traffic, but the writes are constant, and required.
I need to be able to perform periodic maintenance without a maintenance window. Say an index rebuild, a job to purge old data, Windows Update, or hardware upgrade. Most of the advice I've seen is along the lines of "MAKE a maintenance window." While I appreciate the sentiment, I hope there's another way. If it will solve this problem, I do have the ability to purchase new hardware or modify the database, the clients (a set of web services servers), and much of the application code (ADO.NET + ASP.NET).
I've been thinking along the lines of using the warm spare (or a 3rd server) to do the maintenance, and then "swap" it into production.
1 Synchronize the spare by restoring backups, including a current transaction log.
2 Perform the maintenance tasks.
3 Reconfigure clients to connect to the spare server. Existing connections are finished within a minute or so.
4 The spare server is now the production server.
The problem remaining is that the new production server is now out of date by however long it took to perform maintenance. Is there some way that the original production server can be made to queue up changes and merge them to the spare between steps 2 and 3? Any other ideas?
