3

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?

Improve this question
5
  • 1
    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, 2010 at 10:18
  • 1
    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, 2010 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, 2010 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, 2010 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, 2010 at 19:20

3 Answers 3

Reset to default
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.

Improve this answer
1
  • 1
    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, 2010 at 19:50
2

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.

Improve this answer
0

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.

Improve this answer
4
  • Wouldn't a clustered setup be more useful for this situation than a warm spare?
    – iPaulo
    Mar 19, 2010 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, 2010 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, 2010 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, 2010 at 19:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .