May 6, 2015 at 10:14 pm
On a Disaster Recovery Failover test, our normal procedure is to kill connections to the production database, pause SQL Server (to avoid new connections), then take a final transaction log backup (from a query analyzer session opened before the pause) with NORECOVERY, ship that log to DR, apply it in DR & after Restoring the DB w/RECOVERY & switching DNS aliases to DR, off you go, production is running at the DR site. Our standards are that applications must use DNS aliases, making this possible. This is a successful mechanism we've used multiple times for years.
However, tonight on a clustered server, I experienced that when I tried to pause SQL server, the SQL Server service (& agent) went down - didn't pause, just went down with a msg in the errorlog that it was requested. Since I tried this a few times (used Management Studio & SQL Configuration Manager), looks like it's not possible to pause SQL Server on a cluster. Have searched with no good results, haven't found any documentation about this. Can anyone point me to an explanation for this behavior? Would appreciate enlightenment on this, maybe I missed something.
Thanks.
P.S. The DR failover worked OK, I just brought SQL back up with cluster admin & continued. Need to insert a meaningful note in our documentation, however.
May 7, 2015 at 10:56 am
From https://msdn.microsoft.com/en-us/library/ms175549%28v=sql.105%29.aspx:
Use Service Control Manager to start or stop a clustered instance of SQL Server. You cannot pause a clustered instance of SQL Server.
(emphasis mine)
It makes some sense that this isn't possible now that I think about it, but I'd never really thought about it before.
Cheers!
May 7, 2015 at 12:07 pm
Not having implemented clustering in a production environment, but don't you access the clustered instance with a virtual server name, and that it doesn't matter which physical server is actually the active server? Wouldn't this mean that changing the DNS would be unnecessary when failing over to the stand by server in the cluster?
May 7, 2015 at 12:31 pm
I'm not sure if I read that part of the post correctly, but I think the DNS aliasing part was with respect to making DR (log shipped, it sounded like?) live, not the other cluster node.
If I hadn't skipped my coffee this morning, I'd probably be more certain 🙂
May 7, 2015 at 12:33 pm
Jacob Wilkins (5/7/2015)
I'm not sure if I read that part of the post correctly, but I think the DNS aliasing part was with respect to making DR (log shipped, it sounded like?) live, not the other cluster node.If I hadn't skipped my coffee this morning, I'd probably be more certain 🙂
I'm not sure either, that's why I asked. I even reread the initial post, still in the dark.
May 7, 2015 at 2:04 pm
We found that pausing SQL using administrative tools/services worked OK. Not sure why this work around is required.
May 7, 2015 at 2:12 pm
Forgot to mention, this is on a SQL Server 2008 SP4 environment, running Windows 2008 R2.
We're failing over from the DR site back to production tomorrow afternoon, at that time I'll see if the DR server exhibits the same behavior when I try to pause it.
The only message out of the ordinary was on the event log, Event 19019: [sqsrvres] OnlineThread: asked to terminate while waiting for QP.
Thanks.
May 7, 2015 at 2:37 pm
The DNS alias points to the virtual clustered server name. Our DR server is also clustered, has its own virtual name. The DR databases are kept in sync with log shipping - we're reverse shipping at the moment. This is my 3rd reply post, but I'm not seeing my previous entries for some reason.
I probably should've been less verbose on my initial post, without all the background info. The only issue is pausing SQL Server on the cluster, which after testing today on other clusters, seems to work OK.
Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply