March 7, 2011 at 2:46 am
Currently have a 2005 active passive cluster hosting a 500GB db (SQL standard).
Failing over to the passive node may take 30 mins while the DB displays "in recovery" (IIRC)
Mirroring i understand uses a second MDF/LDF to duplicate data.
Does mirroring an active passive cluster mean i require and extra 2 sql nodes ?
Trying to get my head around these two concept but get confused.
The system deals with high number of writes more than anything else. Keeping this data flow incoming is the most important thing.
Thank you for any information.
Scott
March 9, 2011 at 3:33 pm
I only recently have experience with SQL Server database server clustering.
My employer is using Windows Server 2008 R2 and SQL Server 2008 R2 on our new server. We are using an active/passive node configuration.
A failover to either cluster node takes less than 30 seconds, typically about 20-30 seconds. 5 minutes for a failover seems very excessive. I am wondering if you have a cluster configuration problem in Windows Server.
I cannot help you or advise you about database mirroring. I only know what I've read about it.
LC
March 9, 2011 at 10:17 pm
Thank you for the reply. The failover time is down to the large number of transactions i think. 100% sure its configured correctly.
March 10, 2011 at 9:52 am
Hey Scott, you will NOT need two extra nodes to mirror a clustered instance in SQL server. We actually mirror several dbs from separate 3 - 6 node clusters to a single DR instance that is not clustered at my job.
To answer your failover time question, could you provide some hardware specs? I would tend to think that this is your primary issue. Our clusters are hosting SQL instances with multi TB databases, and the longest I have ever witnessed a failover to complete is maybe 45 seconds. Normally, it completes within 5 seconds.
Regards,
Kyle Freeman
March 10, 2011 at 10:34 am
Regarding the failover time, check the throughtput and performance of your drives (log and data) and see if there are high latencies on either. Also consider changing the value of recovery interval so that SQL checkpoints more often (only if your drives can handle it)
I would put money on one of the drives (log or data) showing high latencies.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2011 at 5:48 am
I had a similar issue last year , 15-20 min in recovery ever time there was a fail over or a restart of sql and it was taking longer each time.
The issue had nothing to do with clustering. The problem was with unclosed conversations within service broker. We had millions, we found SQL had to cycle through all of them before bringing the db out of recovery. Once we deleted them and corrected the code so we closed them, We were back to secoonds.
March 11, 2011 at 6:23 am
belucey (3/11/2011)
The issue had nothing to do with clustering. The problem was with unclosed conversations within service broker. We had millions, we found SQL had to cycle through all of them before bringing the db out of recovery. Once we deleted them and corrected the code so we closed them, We were back to secoonds.
Interesting. Thanks for that, something to keep in mind
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2011 at 6:41 am
belucey (3/11/2011)
I had a similar issue last year , 15-20 min in recovery ever time there was a fail over or a restart of sql and it was taking longer each time.The issue had nothing to do with clustering. The problem was with unclosed conversations within service broker. We had millions, we found SQL had to cycle through all of them before bringing the db out of recovery. Once we deleted them and corrected the code so we closed them, We were back to secoonds.
Sorry struggling to get a chance to reply to this in as much detail as i would like. We do have many open connections , some legacy apps that create a connection per packet. This may may explain it. In the process of migrating to new hardware that will exceed our requirements, will test the failover on this hardware with no connections present and feedback.
Regarding mirroring an db on an a/p cluster:
1) passive node is the mirror ? ie i understand you cannot access the mirror db. So should the mirror be on the active or passive node ?
2) assuming the a/p failover is quick on new hardware, what are the benefits of a mirror ? ...
3) what tool do you use to see the number of current open connections ? (i use spotlight , tells me i have 600 users connected , 600 concurrent sessions (i would assume sessions and users are the same thing and this is at a single point in time). I can drill down the sessions and see the connections, majority are sleeping.
Thank you again for your time
Scott
March 11, 2011 at 8:54 am
scott_lotus (3/11/2011)
Sorry struggling to get a chance to reply to this in as much detail as i would like. We do have many open connections , some legacy apps that create a connection per packet.
belucey's not talking about connections. He's talking about Service Broker conversations. If you don't use Service Broker, it's not an issue
Regarding mirroring an db on an a/p cluster:
1) passive node is the mirror ? ie i understand you cannot access the mirror db. So should the mirror be on the active or passive node ?
Errr, don't mirror to the other cluster node. Worthless. You mirror to another server entirely. That other server may or may not be part of a cluster (mirroring doesn't care)
2) assuming the a/p failover is quick on new hardware, what are the benefits of a mirror ? ...
A second copy of the database somewhere. One thing with a cluster is that it has a single point of failure - the drives
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2011 at 9:56 am
To rule out servicebroker
Query to see is servicebroker is enabled:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'DatabaseName'
If it is check to see if there are any unclosed coversations(run against the DB in Question):
select count(*) from sys.conversation_endpoints
Where state = 'DI'
March 11, 2011 at 10:49 am
I think you are a bit confused regarding what actually is dbmirroring.
It is another HA technique for SQLServer.
It needs modification of all connection strings to be effective.
A connection needs to provide "failover partner" information in the connection string.
afaik All clients will have to get SQLNCLI installed to be able to use the automatic failover awareness in the connection.
IMO best path is to read about dbmirroring topology options, then you'll get a clear view on how it defers from clustering and how it can be combined.
IMO it would be best to start figuring out the issues with service broker you experience and fix those from application side.
Maybe my monitoring script can help out :
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2011 at 6:40 pm
GilaMonster (3/11/2011)....One thing with a cluster is that it has a single point of failure - the drives
Gail,
Yes, the Quorum and the MSDTC drives.
Doesn't database mirroring also have at least one point of vulnerability? As I recall, in synchronous mode, a failure of the mirror will lock up the primary?
I've never seen anyone say one way or the other. Which is the least risky technology to use if you want to absolutely avoid the possibility of downtime? Mirroring or clustering?
Thanks,
LC
March 12, 2011 at 3:49 am
crainlee2 (3/11/2011)
GilaMonster (3/11/2011)....One thing with a cluster is that it has a single point of failure - the drives
Yes, the Quorum and the MSDTC drives.
The database drives themselves, unless you're going for SAN-replication and a geo-dispersed cluster
Doesn't database mirroring also have at least one point of vulnerability? As I recall, in synchronous mode, a failure of the mirror will lock up the primary?
No, it shouldn't. Failure of the mirror should not take the principal down, that would be a useless design if it did.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply