May 29, 2012 at 8:33 am
So I am using a witness server instance so that I can take advantage of automatic failover on SQL Server 2008 Standard. I have a mirrored DB in a DR/hot site that is approx 250GB in size, the primary SQL server is at the main location and I just got finished reading the DB mirroring best practices paper from Microsoft published 3/10/06 (couldnt find one newer).
The question that begs to be asked by someone like myself who has never really tested this before is once a "failure" is detected by the witness and it decides to failover to the MIRROR database in the documentation it says that once the PRINCIPAL comes back online it will assume the role of because the former MIRROR database will be the PRINCIPAL. OK, if that's the case how do the roles get reversed while keeping the database active so that the former PRIMARY database can once again be the PRIMARY. There is a drastic difference in server specs between the two, but I believe that the failover server at the hot site can accomidate the load and I want to test that on a slower day here in the office. Another question about failover timeout is by default it says 10 seconds, how can I make that longer through Management Studio/GUI or do I need to run the SQL statement? My concern is that if it's too short or if I reset the primary server for maintenance, but forget to pause the mirroring then I assumed the witness server would pick up on it missing and failover (false alarm)...
May 29, 2012 at 8:44 am
If I'm understanding your question correctly:
To set your primary instance back to being the PRINCIPAL, issue the following command:
-- http://msdn.microsoft.com/en-us/library/bb522476(v=sql.100).aspx
ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO
To change the timeout setting:
ALTER DATABASE [yourDatabase]
SET TIMEOUT 30
GO
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 29, 2012 at 8:47 am
And they should be run on the normal PRIMARY server I assume? I will set the timeout to at least 30 seconds for failover detection. So how does that work if the server needs to be rebooted, do I just pause the mirroring on the PRIMARY server before rebooting so I dont get a false failover?
May 29, 2012 at 8:54 am
The first command:
ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO
should be run on your current PRINCIPAL (which is your desired MIRROR, if I understood correctly). It will set your primary instance back to being the PRINCIPAL role.
The TIMEOUT command can only be run on the principal server, so it'll depend on your sequence of events.
Here's another piece of reference which should help:
http://msdn.microsoft.com/en-us/library/ms191449(v=sql.100).aspx
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 29, 2012 at 9:15 am
I thnk we're speaking the same language, for this example I'll use Server A (Primary/Principal/Main SQL Server), Server B (Backup SQL/MIRROR/hot site), Server C (Secondary SQL Server at Primary location/Witness)
I want to test and see if Server B can withstand the load of Server A's database, so I've already established the mirror and it's currently running with Server C as a witness. In my test I plan to disconnect the NIC after hours on Server A, wait for failover to Server B and access my applications and run some metrics. However when i plug Server A back in I want to make sure it gets the most updated copy from Server B (which I assume it will because it then become the PRIMARY). Then I want to put things back the way they originally were.... sound like that first command should be run on Server A or Server B at the time I want to restore things to the way they were?
May 29, 2012 at 10:03 am
Yeah, we're on the same page. But a couple of things:
If you like, you can test your failover by issuing the 'SET PARTNER FAILOVER' command on ServerA, instead of pulling the NIC. This way you're seeing how the command works and will assist you later down the road if you're planning on doing maintenance on your nodes (check here: http://msdn.microsoft.com/en-us/library/bb677181(v=sql.100).aspx).
However when i plug Server A back in I want to make sure it gets the most updated copy from Server B (which I assume it will because it then become the PRIMARY)
ServerA will not become the PRINCIPAL automatically when you re-establish the network connectivity. It will need to be done manually (sounds you realize that already). In order to make ServerA the PRINCIPAL once again, issue the 'SET PARTNER FAILOVER' command on ServerB (which is your current principal after the failover test).
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 29, 2012 at 10:24 am
Thanks that link also helps alot too. I'm curious if my application's System DSN's pointing to Server A will still work when it's no longer the primary. I figured the application would have a lapse in connection, but I'm curious if it will reconnect without me modifying any of the DSNs.
May 29, 2012 at 10:29 am
This may help:
http://msdn.microsoft.com/en-us/library/ms366348(v=sql.100).aspx
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
June 6, 2012 at 7:04 am
CPSPR (5/29/2012)
Thanks that link also helps alot too. I'm curious if my application's System DSN's pointing to Server A will still work when it's no longer the primary. I figured the application would have a lapse in connection, but I'm curious if it will reconnect without me modifying any of the DSNs.
I believe, if your application is .net framework then the connection also failover.....if not, you(or the application team) need to manually change the connection string.
Regards,
TA
Regards,
SQLisAwe5oMe.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply