November 11, 2009 at 7:31 pm
Have been looking into general high avilability in SQL Server, and as the boss has ruled out clustering, mirroring seems the next best option.
I've been reading a lot the past week about mirroring, and have a few questions as I keep reading conflicting information in articles.
1) If you're using "High Availability" mode with a Principal, Mirror and Witness, where do you point the data source of your applications to? I'm guessing you point it to the Witness so it can decide whether to update the Principal and Mirror or just the Mirror (new Principal) if the old Principal is down? Is that right?
2) This is my tricky part, I have a SQL 2008 Standard server with two instances. The first instance contains 50 databases and the second instance contains 15 databases, and I'd be wanting to setup mirroring on all these databases.
I've read though that Mirroring is very performance intensive in SQL 2005 and its best to keep the number of mirrored databases down to 6 per instance. Is this still the case in SQL 2008, is that statement even valid? I can't find anything on MSDN whether it talks about mirroring performance in SQL 2008.
What are the general things to take note of performance wise when implementing Mirroring?
November 11, 2009 at 9:09 pm
JamesNZ (11/11/2009)
Have been looking into general high avilability in SQL Server, and as the boss has ruled out clustering, mirroring seems the next best option.I've been reading a lot the past week about mirroring, and have a few questions as I keep reading conflicting information in articles.
1) If you're using "High Availability" mode with a Principal, Mirror and Witness, where do you point the data source of your applications to? I'm guessing you point it to the Witness so it can decide whether to update the Principal and Mirror or just the Mirror (new Principal) if the old Principal is down? Is that right?
2) This is my tricky part, I have a SQL 2008 Standard server with two instances. The first instance contains 50 databases and the second instance contains 15 databases, and I'd be wanting to setup mirroring on all these databases.
I've read though that Mirroring is very performance intensive in SQL 2005 and its best to keep the number of mirrored databases down to 6 per instance. Is this still the case in SQL 2008, is that statement even valid? I can't find anything on MSDN whether it talks about mirroring performance in SQL 2008.
What are the general things to take note of performance wise when implementing Mirroring?
1) Point it to Principal. Witness is used for auto fail over to Mirror.
2) You need to transfer all the db's to one instance.
You will need another 2008 instance for the witness server in order to fail over automatically otherwise with 2 instances your fail over process will be manual.
Database Mirroring Best Practices and Performance Considerations
http://technet.microsoft.com/en-us/library/cc917681.aspx
Things to consider when setting up database mirroring in SQL Server
http://support.microsoft.com/kb/2001270
November 11, 2009 at 9:15 pm
Thanks for the response 🙂
1) Point it to Principal. Witness is used for auto fail over to Mirror.
If you point it to the Principal, and the Principal goes down, how will it know to re-direct to the mirror? Seems odd, but if that's the way it works, that's the way it works.
2) You need to transfer all the db's to one instance.
You will need another 2008 instance for the witness server in order to fail over automatically otherwise with 2 instances your fail over process will be manual.
Why can't I have 2 instances on the Principal and 2 instances on the Mirror and 2 instances on the Witness handling it all? Can't you setup mirroring across multiple instances on a Principal and Mirror?
Database Mirroring Best Practices and Performance Considerations
http://technet.microsoft.com/en-us/library/cc917681.aspx
Things to consider when setting up database mirroring in SQL Server
Thanks, those look great.
November 11, 2009 at 9:43 pm
1) Point it to the principal. If you want the benefits of automatic failover in the app then use the native client and set the connection string failover partner to the mirror (but the app should still be coded to handle a connection failure and retry).
2) They can be split across servers. Your limitations lie with the resources on the principal and the mirror, especially network bandwidth although this is alleviated somewhat in 2008. Just bear in mind that because each transaction is effectively committed on the mirror before committing on the principal, lack of resource on the mirror will impact your app. I've read of other people mirroring more databases than you are talking about (I only run around 15 currently) but the degree of write activity plays a big part, and even whether than activity is staggered.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply