February 24, 2011 at 2:42 am
I'm currently looking at mirroring and have set up a mirror on 2 servers with out automatic failover, now when testing this I'm finding if I stop the SQL services on the principal server (replicating a failure on the principal) the mirrored database just sits in a 'Recovering...' state how did I make this database active so I can point the app at this database.
or should I be using replication to achieve a failover?
Hope this makes sense, any help gratefully recieved.
February 24, 2011 at 3:00 am
After you've got the mirror up and running it does say "(Principal, Synchronized)" next to the Principal and "(Mirror, Synchronized)" next to the Mirror? And you do have a Witness up and running? (i.e. when you go to Properties on the the mirrored DB in SSMS and click on Mirroring, the operating mode is set to High Safety with automatic failover (synchronous) ?
Can you manually fail over by running ALTER DATABASE [DBNAME] SET PARTNER FAILOVER on the principal?
Also, if you're using the .NET sql client then you don't need to directly point your app at the other server, you just use the Failover Partner property in your connection string and let it take care of the retrying to the partner should the primary be unavailable.
February 24, 2011 at 3:30 am
Alex Webber (2/24/2011)
After you've got the mirror up and running it does say "(Principal, Synchronized)" next to the Principal and "(Mirror, Synchronized)" next to the Mirror? And you do have a Witness up and running? (i.e. when you go to Properties on the the mirrored DB in SSMS and click on Mirroring, the operating mode is set to High Safety with automatic failover (synchronous) ?Can you manually fail over by running ALTER DATABASE [DBNAME] SET PARTNER FAILOVER on the principal?
Also, if you're using the .NET sql client then you don't need to directly point your app at the other server, you just use the Failover Partner property in your connection string and let it take care of the retrying to the partner should the primary be unavailable.
It say's Principal, Synchronized next to the principal but restoring.. next to the mirror although when looking in the Mirroring monitor both are showing as synchronized. Not using a witness server just want a mirror with out auto failover (not my choice).
February 24, 2011 at 3:36 am
Sorry my bad, I misread your question. In that case without automatic failover (a witness server) you'll need to manually fail over (see my initial response) from the principal to the mirror before you stop the initial principal. This scenario is prefect for if you want to be able to maintain/patch your database servers without downtime, but if something goes bang in the night on the principal you won't be protected by automatic failover.
February 24, 2011 at 3:56 am
but if I take the principal offline the mirror sits saying recovering... and I can't do anything with it can you give any advise how I get the database to an active state?
February 24, 2011 at 4:18 am
Don't forget, in Database Mirroring, the Mirror is constantly in Recovering state. If you kill the Principal without first failing over to the Mirror then it's state will just be exposed as "(Recovering...) instead of "(Mirror, Synchronized / Restoring...)". You'll want to bring the Principal back online and re-establish the Mirroring Session by running ALTER DATABASE [DBNAME] SET PARTNER = 'TCP://mirror server', if that fails you'll need to apply any necessary Log backups to the Mirror, like when you first established the session.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply