August 21, 2007 at 1:31 am
Hi,
I have a SQL Server 2005 (Enterprise Edition) instance running 50 separate databases and I am wanting to setup Mirroring (High Performance) on each of them.
Microsoft does not recommend mirroring a large number databases on a single instance, but I cannot find anywhere exactly what this figure is, do they mean 100 databases per instance, 200 databases per instance?
Thanks in advance.
www.sqlAssociates.co.uk
August 21, 2007 at 1:55 am
Chris,
the offical recommendation of MS is no more than 10 databases per instance. http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
BOL says the same, but only for 32-bit servers, but I don't think that 64-bit makes any difference. Of course the physical limit is depending on the amount of transactions, your hardware and your network infrastructure. I know of cases there they succesfully implemented mirroring for 40 databases.
Anyhow if you really need to insure availability for so many databases clustering might be the better option.
Markus
[font="Verdana"]Markus Bohse[/font]
August 21, 2007 at 2:12 am
Thanks for that Markus. The SQL Server instances are already clustered. The SQL Mirroring option is for DR and will move data from data centre #1 to data centre #2 which is over 100 miles away. There is a 16Mb/s line between the two data centres and initially I will have to move around 140GB, and from then on just the new transactions (the system does execute a massive amount of transactions though).
Any suggestions?
www.sqlAssociates.co.uk
November 25, 2009 at 9:06 am
Mirroring more databases is not a problem. We mirror almost 200 from one server to another, you simply run out of worker threads if you don't change the defaults. The principle needs about 2 additional threads per mirrored database, but the mirror needs 5 additional per database. On each server, we have set the max worker threads to the appropriate number, for the mirror with 180 databases, it's currently set to to 1000. Just take a little more memory. Here is a good link talking about the max worker threads:
http://msdn.microsoft.com/en-us/library/ms187024(SQL.90).aspx
Student of SQL and Golf, Master of Neither
November 26, 2009 at 9:48 am
Make sure you read up on the interplay between clustering and mirroring.
Actually I HIGHLY recommend you get a professional in to help you evaluate your DR need and get things properly set up for you. There are MANY ways you can go wrong here, and your company's data (and thus your company) could be at risk if done incorrectly...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 29, 2009 at 6:42 am
This might help: http://support.microsoft.com/kb/2001270
The thing that I've found the biggest concern is the network speed and reliability. The set up I work with is much smaller than you're talking about, but we have several mirrored databases, two of which have a fairly significant throughput. The network between them is generally very fast and very reliable, so it works well (and we're using synchronous mirroring, so commit time is a factor). Obviously with asynchronous mirroring you don't have to worry about the time it takes to commit on the mirror, but I'd imagine that you'd still want to make sure that the mirror server is as up to date as possible, in the event that you need to force service.
Related issues are index rebuilds/defrags, and data loads; you need to take account of the time and network load these maintenance tasks will require, once you've added mirroring to the mix.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply