February 22, 2012 at 10:51 am
Hi,
Can any one shed best routes for this:
We have servers in different regoins of the world. LD, NY ,,etc.
All the databases on each region are logshipped to its counter region and vice versa.
e.g. LD (mix of primary and secondary) is log shipped to NY (mix of primary and secondary) and vice versa.
If I want to failover LD to NY or NY to LD what is the best way to do it ?
We are just testing the failovers during maintenance window. The primary will remain primary and secondary will remain secondary.
I know that failing over and failing back will be a nightmare for us, but if anyone has done this in a better way would help me out.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 23, 2012 at 12:27 am
How many log shipped databases ate we talikng about total?
It sounds like your main aim is to provide availability?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 23, 2012 at 7:46 am
20 per instance and 3 instance per server.
It will sound like a lot .. but the hardware is Dell R710 with plenty of memory and we have tested before implementing it.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 23, 2012 at 8:05 am
Have you considered alternative high availabilty solutions such as mirroring? Or is logshipping you method of choice.
Mirroring would provide an easier failover mechanism.
Either way you will need to consider the adminstration of server level components such as logins, server roles, sql agent jobs and linked servers. All of these will need to be implemented on the redundant server in a disabled fashion.
February 23, 2012 at 8:27 am
you would want to bring the DB's online so you will have to issue a command to each DB with the WITH RECOVERY statement and then resolve any issues with logins, linked servers, jobs etc.
then you would want to make a DNS change on the Primary hosts DNS record to point to the Secondary hosts IP, to allow anything connecting to the primary to pass to the secondary.
failing back however will be tricky as you will need to setup logshipping again and redo all of the above, you would probably want to stay at the secondary site until you needed to fail over again.
if it is high availability you are after you could look at clusters, geo-clusters or mirroring etc
February 23, 2012 at 8:55 am
anthony.green (2/23/2012)
you would want to bring the DB's online so you will have to issue a command to each DB with the WITH RECOVERY statement and then resolve any issues with logins, linked servers, jobs etc.then you would want to make a DNS change on the Primary hosts DNS record to point to the Secondary hosts IP, to allow anything connecting to the primary to pass to the secondary.
failing back however will be tricky as you will need to setup logshipping again and redo all of the above, you would probably want to stay at the secondary site until you needed to fail over again.
if it is high availability you are after you could look at clusters, geo-clusters or mirroring etc
Thanks .. I understand the steps for bringing secondary online.
My only question is -- what is the best way to fail back.
We have looked into Mirroring and clustering, but at this moment they are not feasible.
mirroring has its overhead (due to bandwidth between cross region and mirroring that many databases ;-)) and clustering is not an option due to its complexity and cost. We have more than 1500+ sql servers with close to 4000+ databases. Might be looking for 2012 .. AlwaysOn (clustering+mirroring) 😎
Its a huge environment and we already consolidated many server .. so the server count is less :hehe:
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 23, 2012 at 8:55 am
anthony.green (2/23/2012)
you would want to bring the DB's online so you will have to issue a command to each DB with the WITH RECOVERY statement and then resolve any issues with logins, linked servers, jobs etc.then you would want to make a DNS change on the Primary hosts DNS record to point to the Secondary hosts IP, to allow anything connecting to the primary to pass to the secondary.
failing back however will be tricky as you will need to setup logshipping again and redo all of the above, you would probably want to stay at the secondary site until you needed to fail over again.
if it is high availability you are after you could look at clusters, geo-clusters or mirroring etc
Thanks .. I understand the steps for bringing secondary online.
My only question is -- what is the best way to fail back.
We have looked into Mirroring and clustering, but at this moment they are not feasible.
mirroring has its overhead (due to bandwidth between cross region and mirroring that many databases ;-)) and clustering is not an option due to its complexity and cost. We have more than 1500+ sql servers with close to 4000+ databases. Might be looking for 2012 .. AlwaysOn (clustering+mirroring) 😎
Its a huge environment and we already consolidated many server .. so the server count is less :hehe:
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
February 23, 2012 at 9:10 am
there is no best way to fail back as they are both the same in essence
either setup logshipping again and then fail over again, then setup logshipping again
or
backup secondary, restore to primary, then setup logshipping again
you would probably want to do the latter due to having to rename nodes while you rebuild logshipping.
the other alternative is that when you do fail over, you rename the windows host to the primary name, and then reconfigure sql to use a new name, that way you then can rename the true primary node a 3rd name so when you fail over again you can rename the true secondary node its right name and then rename the true primary node its proper name.
gets confusing
something like this
operational before failover
Pri1 Sec1
Fail over
Pri1 becomes Thi1, Sec1 becomes Pri1
Fail back
Pri1 (which is actually Sec1) becomes Sec1 again, then Thi1 (which is actually Pri1) become Pri1 again
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply