February 28, 2008 at 1:54 am
I am in the process of designing Disaster Recovery for about 40 databases running on a single clustered instance of SQL Server 2005 Ent 64bit (4 Processes / 12GB RAM / RAID 1+0 SAN). The databases will be servicing websites via MS Office Sharepoint Server 2007.
As it’s a completely new setup we have very little details on expected load, but it is anticipated to be low to medium volume of traffic hitting the sites
I've read on the Microsoft site that Mirroring should be limited to a maximum of 10 databases per instance but I can't find anything on maximum databases using log shipping.
I'd appreciate any feed back on real world setups from people who are mirroring or log shipping lots of DBs and how they find it impacts the performance.
Thanks in advance
February 29, 2008 at 9:26 am
Log shipping or database mirroring don't go for large number of databases they might affecdt the server and network performance.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
March 3, 2008 at 1:05 pm
I currently use log shipping on 30+ databases
with both low and high volume sites
The only issue i have with log shipping is when you update stats or rebuild indexes which may cause a 30 gb file
Database mirror i would hold off until the never to occur sp3
I have had a lot of weird things happen
March 3, 2008 at 1:19 pm
I log ship about 20 databases on a sql 2000 4gB 4 processor server with no probs.
It does make i/o spikey on the backup drive and log drive so important to seperate those out and try to stagger the log jobs.
Space wise you can of course log ship database in bulklogged mode.
Agree with the mirroring limited to max about 10 databases (maybe even less) but you can mix the two of course. Only advantage I can see in mirroring is the lower latency and the fact the log can be truncated without breaking the recovery chain. OK and auto failover possible.
---------------------------------------------------------------------
March 4, 2008 at 2:59 pm
Hi,
I am looking into similar strategy as well. At the moment I have implemented the log shipping on 20+ databases. However I have used 3-rd party software to do backup/restore to speed up and make file smaller - as at the moment it all looks good. About the mirroring - the witness option is kind of scary staff as having error in network it will failover to DR site... but without witness it might be a good option to use asynchronous mirroring.
Best Regards
March 4, 2008 at 3:44 pm
Thanks for the input guys, very much appreciated.
March 5, 2008 at 6:34 am
With regards a Witness Server in a Mirror - a failover will only occur if the Witness server cannot connect to the Principal, but can connect to the Mirror.
If, for example, the whole network breaks, no failover will occur because the Witness cannot connect to the Mirror. In this scenario, the Principal will continue to operate as normal, and the Mirror will re-synchronise when the network is fixed.
At least, in theory..... 🙂
March 5, 2008 at 7:27 am
I thought if you do mirroring from a clustered arrangement if the cluster fails over it will cause mirroring to fail over to the secondary, which is undesired. I could be misremembering this.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 5, 2008 at 8:20 am
erm, you've confused me! Maybe it is just the technical words.
In a Failover Cluster, using Microsoft Cluster Service, each instance of MSSQL is essentially a resource within the Cluster. MSCS takes care of everything.
In a Database Mirror, the Principal and Mirror are monitored by a third server, the Witness. You only need a Witness if you want automatic failover. With these 3 servers, you essentially have a Quorum, where at least 2 servers need to be in contact with each other.
Are we talking at crossed purposes?
March 5, 2008 at 8:28 am
Don't think we are crossed. Just that something tickles the back of my head that if you have mirroring set up with a cluster as primary if the cluster fails over it forces the mirroring to switch to the standby machine (assuming you have a witness and auto-failover enabled in the mirroring setup).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 5, 2008 at 8:44 am
Is that a normal set up? Would you have an MSSQL instance within a Cluster, and also have databases running in that instance mirrored to another instance outside of the Cluster?
March 5, 2008 at 9:03 am
On the face of it this is a valid way to provide additional uptime.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 5, 2008 at 9:17 am
I guess it could and should work, but fault-finding an error would be, err, interesting. Thanks for clarifying.
Andy
March 5, 2008 at 1:33 pm
Yes, there is an issue to be aware of that if you have mirroring with automatic failover running on a cluster, and the cluster fails over from one node to the other, then mirror will react quicker and fail to the mirror server before the 2nd node come online.
March 5, 2008 at 2:00 pm
You can change the mirror timeout duration to possibly avoid this scenario.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply