Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm a software developer who inherited a part-time DBA role. I'm responsible for an application backend 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?

share|improve this question
    
Which SQL Server? ENTERPRISE has special provisions for that - like the ability to rebuild an index WHILE USING IT (i.e. it keeps the old index online while it rebuilds). –  TomTom Mar 19 '10 at 10:18
    
It's currently 2008 Standard Edition. I could upgrade to Enterprise to get online rebuilds, but that'll only handle that specific problem. I'd still be up a creek with any other serious maintenance task. –  solublefish Mar 19 '10 at 10:50
1  
just a side question, if this is 24/7 machine, and can't be turned off, how do you install kernel security updates? I wasn't aware that MS server could do in memory updates of the kernel without a reboot. –  The Unix Janitor Mar 19 '10 at 13:22
    
Eneterprise has some more provisions. Basically if you run 24/7 then you need enterprise.... plus all the other ehterprise tools. –  TomTom Mar 20 '10 at 18:29
    
@user37899 - Our current solution for critical updates or configuration changes is essentially to disable the application for the duration of the update - during which time we lose money every minute. Thus our desire for a better way. –  solublefish Mar 22 '10 at 19:20

3 Answers 3

You will need to read up on the High Availability features offered by SQL 2008. See here for the white paper. There are just too many scenarios to cover.

[climbing on soap box]

You do need maintenance windows though. That does not imply weekly or even monthly, but there will be times you need the server down to make improvements. There are very few systems that are 100% operable over long periods (years), or that need to be. The purpose of maintenance windows is to minimize the downtime and make it predictable, so the business can cope with it. I would advise against dismissing maintenance windows all together. If you do change your configuration by moving to clustering or the like you will certainly have some down time just during the transition, however small. [climbing back down]

Some options:

  1. Mirroring
  2. Clustering
  3. Log Shipping
  4. Online index operations
  5. archiving using online index operations and partitioned tables
  6. Peer to Peer replication

My 2 cents.

share|improve this answer
    
I understand the need for bringing servers down to make changes or improvements. This is the primary reason I'm looking for your help. I'm hoping to find an architecture which will let me run the application on different server(s) while one is offline. I know the very biggest sites get it done, but I don't know how. –  solublefish Mar 22 '10 at 19:50

I'd second mirroring and point out if you REALLY need 24/7 the company should up their budget to include a DBA or consultant to help with this.

Mirroring many times can solve 24/7 needs "good enough" with very little cost beyond a second server.

share|improve this answer

Since that database it is a vital part of the infrastructure, a warm spare would be recommended anyway. And that will enable you to do maintenance as well.

share|improve this answer
    
Wouldn't a clustered setup be more useful for this situation than a warm spare? –  iPaulo Mar 19 '10 at 15:32
    
From the original comment it seems that there is no redundancy at all in the current setup. Probably a clustered setup would be better, you are right. –  Dan Andreatta Mar 19 '10 at 16:33
    
Forget cluster. Cluster has serious problems for 24/7.... for example a crash may leave a database unable to start. You need mirroring, first, with a witness server. THEN you may decide for custering. Clustering first is neglect. I have seen servers crashing and taking the database with them. –  TomTom Mar 20 '10 at 18:30
    
@iPaulo - it might, and that's one thing we're considering, but it doesn't really solve the fundamental problem if I understand it correctly. @Dan - we have no automatic redundancy, but the warm spare via log shipping gets us close. In the event of a failure, we're down a few minutes, which isn't free, but also isn't debilitating. –  solublefish Mar 22 '10 at 19:42

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.