4

We just moved from SQL Server 2000 to SQL Server 2008.

We were using our own log shipping on 2000 for failover. For 2008, we need to decide to use our own log shipping, built in log shipping, or replication.

We have many databases on our server (400+); some small, some big.

A DB server failure should be rare and we could accept a 15-30 minute data loss. More important is getting the second DB server up fast.

Based on the criteria above, am I better of with log shipping or replication?

If log shipping, is there an easy way to ensure it moves over all DBs?

1
  • According to Paul it appears that the answer is to go down the log shipping route. However manually failing over 400 databases is going to take quite a while, especially if one wants to minimize data loss and not ignore any relevant queued backups. Can anyone suggest some pointers on how one could do this via T-SQL?
    – SeanDav
    Oct 19, 2010 at 15:10

9 Answers 9

13

For all those suggesting mirroring - it's not going to be possible to mirror 400 databases. You'll run out of worker threads way before you hit 400, whether on 32- or 64-bit. There are at least 2 worker threads per database on the principal and at least 3 per database on the mirror.

It's really got nothing to do with the skills of the DBA. It depends entirely on what the HA requirements of the business or application are - these are the driving factors, not what the DBA can cope with. If a more complex solution is required, then getting a DBA that an deal with it is also required. Limiting your HA solution because of the skills of the DBA is nonsensical.

Database mirroring is also not fast-detection and fast-failover - it entirely depends on what the failure is, what the mirroring partner timeout is, what the SEND and REDO queues are. It could be quite a while for a failover to complete one the mirror decides to do become the principal. I've helped many customers implement mirroring (both when at Microsoft when I owned database mirroring along with the rest of the Storage Engine), and since leaving in 2007.

Forget mirroring for that number of databases.

There are some fundamental questions you need to answer before we can give you a recommendation:

  • Do you want to have a redundant copy of the entire databases or just portions?
  • Do you want to be able to use the redundant copies? For writes or just for reads?
  • What's the transaction log generation rate of the databases?
  • What's the network bandwidth between the two sites?

Log shipping is probably the easiest for you, both in terms of setting it up, monitoring it, and being able to bring the redundant copies only quickly and within your data-loss limits. With log shipping you can choose to bring the redundant copies online without finishing restoring all the transaction log backups queued for restore, by accepting data-loss.

You can also effect simple failovers using a mid-tier routing technology, or even something as simple as Windows NLB with a 0/100 config, or switching to 100/0. I've also seen customers using DNS switching to effect failovers when the server name cannot change.

With replication you have to deal with unpredictable latency between the transactions being harvested from the publication db transaction log, hitting the distribution database and then being pushed/pulled to the subscription database by the distribution agent. Replication can also get itself twisted up and be a pain to figure out and reset - log shipping is dead simple.

Given that you've been using your own log shipping, I'm guessing that the tran log generation rate and network bandwidth aren't an issue - I'd stick with log shipping and shy away from replication. Don't even think about database mirroring as it won't work for your volume.

Hope this helps - this is really two day's worth of discussion when I teach HA to the DBAs inside Microsoft - boiled down to 5 mins answering this. Feel free to follow-up with more specific questions in comments.

1
  • * Do you want to have a redundant copy of the entire databases or just portions? Entire. * Do you want to be able to use the redundant copies? For writes or just for reads? Backup sits idle until needed - no reads or writes. * What's the transaction log generation rate of the databases? Not large during the day, at night for 2-3 hours DBs are hit hard. * What's the network bandwidth between the two sites? In the same rack.
    – Peter
    Jun 23, 2009 at 20:00
2

We use Mirroring for up-to-the-second on-site recovery.

For the over the WAN off-site backups (e.g. high latency) we use the built in log shipping.

Note that for EITHER option you have to set this up PER DATABASE which, for 400 DBs, will be a giant pain in the rear.

Do you use a storage array (SAN or NAS)? The easiest method seems to be block level replication via your storage array snaps (with the appropriate SQL Server quiesce plugin) -- that'll capture EVERY database automagically.

2

Here are some additional thoughts:

I don't believe Replication is the way to go. I typically look at Replication as a way to distribute data and not as a DR solution. When trying to replicate 400 databases, you will most likely stress the distributor and publisher roles, which can eventually bring the server to its knees. In addition, it will be a nightmare to implement, manage and handle schema changes as you need to set up a publisher and subscriber for each database.

From a log shipping perspective, yes, it can be done, however again, it will be challenge to implement and manage as you have to implement log shipping on each database. If a failure takes place then you have to bring each database online manually and re-point all your applications. Depending on your workload, most likely the solution will not scale to 400 databases. So please test.

When dealing with this many databases it better to implement a Geo-Cluster solution or use some form of SAN replication and work at the volume or block level. If you don't have a SAN then third party software such as InMage or Doubletake may do the trick.

Remember each technology such as Log Shipping, Database Mirroring or Replication can significantly increase workload and negatively affect the servers performance, therefore, conduct performance testing before rolling into production.

If you decide on log shipping then you can script out the whole process including the failover.


Ross Mistry, SQL MVP

Author - SQL Server 2008 Management and Administration and Windows Server 2008 Unleashed

Twitter - @RossMistry

1

It depends largely on the skill of your DBA.
If you have a skilled DBA then mirroring is the safest option with the quickest failover if you configure it with a witness server then the "client" shouldn't notice anything except a slight lag. If your DBA is less skilled log shipping is the way to go but it has a longer more complicated failover routine which would involve the replay of transactions that have not yet been committed in to the primary data file and have not been transferred. If you are looking to lose no more than 15-30 min of data then mirroring would be your best option.
If you are doing this with only 2 servers you should set it to high security mode, the 3rd optional Witness server can be a SQL2008 express instance so you are only out the minimal hardware cost so don't rule this out as an option.
I have written a guide on how to mirror two SQL 2005 servers that are not members of a domain in this mode and it is available @ http://danmacs.blogspot.com/2009/05/database-mirroring-for-non-domain-ms.html
In this scenario failover is manual (but fast), there is only the possibility of one transaction being lost (assuming that your mirror is operating correctly) BUT the clients that are connecting to it will need to be moved over manually (or via a DNS change if you are doing TCP/IP and not using named pipes, named pipes may very well work but I couldn't say for certain)

4
  • 2
    How's he going to get enough worker threads to mirror 400 databases? 800+ threads on the principal and 1200+ on the mirror... not going to happen. Jun 23, 2009 at 16:17
  • Very good point Paul I see your comments further on down the line, I was mainly referring to the skills of the DBA in managing replication issues etc. Good point well made.
    – Dan
    Jun 23, 2009 at 16:27
  • Thank - no offense I hope - just making a counterpoint :-) Jun 23, 2009 at 16:37
  • Of course no offense. It would only be offensive to me if I didn't learn from it!
    – Dan
    Jun 24, 2009 at 7:43
1

How much money does your company have?

If they're flush and there's a requirement to move all of the databases then a two node active/passive cluster with warm SAN replication to another off site active/passive cluster would do the trick :)

If not then stick with log-shipping - 2008 Enterprise provides you with the benefits of compressed log backups (at the cost of cpu) and all editions of SQL from 2005 onwards have the ability to take log backups concurrently with full backups (which was problematic on 2000 boxes with large volumes of transactional activity).

0

Is the second DB purely for failover or would you want to be able to use at as well e.g. for MI reporting.

Have you looked at mirroring as well?

Do you have a budget for this or are you looking to use the internal tools. I've used both log shipping and replication in the past but am currently using Double Take which has been fantastic so far.

3
  • Second is pure failover.
    – Peter
    Jun 23, 2009 at 15:10
  • Setup and admin will be a pain as Matt has said. If you use something like Double Take you can just build a profile which points to your data file storage area and it will do block level replication of data changes with great latency. I was dubious when i first came across it having been more used to clusters, log shipping and replication but so far it is great - even with high throughput plus you can use it to replicate any other data you may want as well e.g. the SQL binaries, log files etc.
    – Chris W
    Jun 23, 2009 at 15:30
  • Never used Double Take but it sounds neat Jun 23, 2009 at 15:32
0

If you have a lot of time invested in creating the system to manually log ship 400+ databases, and it works, I would stick with it. If it's cobbled together and a pain to keep running (been there), might be time for a change and go with mirroring.

0

If you try to use database mirroring, I think that you will run out of worker threads well before you get to 100 databases, let alone 400+. Have a look at this.

Every mirrored database uses a couple of threads and SQL Server is only configured to run with 255 threads by default. When you run out of worker threads, unfortunate things happen. I've run servers that were configured with more threads (512, IIRC), but it seemed to make MS very nervous. Doing something that make MS nervous tends to end up badly.

I think that you could go with 400 log shipped databases, assuming that there isn't too much log traffic and that you write some tools to help you out with setting them up and monitoring them. The tools could be written in tsql or something like powershell.

The real issues with log shipping, for me, have been more about making sure you have the same logins/packages/linked servers/etc on the other server, making sure that the same jobs are on the failover server (but disabled until you need to enable them, and you need a way to enable and disable them), failing back to the original primary site, how to re-establish the log shipping when a log sequence gets broken for whatever reason, that sort of thing. When doing deployments, it's easy to forget to do the other server.

0

http://msdn.microsoft.com/en-us/library/ms366349(SQL.90).aspx

Says 10 mirrored DBs is the max for a 32 bit machine.

You must log in to answer this question.

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