| Author |
Topic |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
 Posted - 2009-06-03 : 02:39:55
|
I have a server which is running fine in our primary location and we want to have a secondary location for failsafe reasons.Are there other options than Active/Passive cluster solution?What we want is that if the secondary location is activated due to a failure in the primary location and application is working against the secondary location and when primary location is fixed and started again, the application will be run against the primary location. The application will automatically decide which site to work against.Now to the question, how to get the information stored in the secondary location back to primary location when primary location is started again?Is the only answer an Active/Passive cluster solution?What we are thinking about as an alternative is a merge replication.
E 12°55'05.63"N 56°04'39.26" |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-03 : 16:02:45
|
| For any type of solution other than Clustering or Mirroring, the failover is not automatic. You need to manually redirect to either your primary or secondary site. Clustering is not for failing over between sites.. Its for failover within the same site. You could look at Database Mirroring. There are different modes - synchronous and async modes. sync requires an additional witness server that sits between the primary and mirror and helps facilitate coordination.Peter, things need to be defined more clearly than "due to a failure in primary location"... DR solutions can be very expensive to install and maintain. The business needs to really put down what is RTO (time for recovery) and how much data can they afford to lose. There are different options- hardware level, software level - from Microsoft as well as third party. Here is a high level summary of different options: http://support.microsoft.com/kb/822400 Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-03 : 16:03:59
|
| Forgot to add, Merge replication is not a failover/DR solution. Its more for systems that have multiple points of data entry - something like POS (point of sale) systems like a hotel chain where data can be entered at different locations...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-04 : 00:25:53
|
The application is run on a desktop and when the application fails to connect to primary data site (for any reason), it will then try to connect to secondary data site.There are two data hosting companies they use. The databases are identical, they have decided to use this approach.If the application has used the secondary data site for a while and suddenly the primary data site is up and running again, they want to compare the two databases and make them identical again.It seems like database mirroring is the way to go here.Thank you for the tip.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-06-04 : 04:18:24
|
| i don't know if you can have 2 way database mirroring.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-04 : 10:24:41
|
quote:
Originally posted by spirit1
i don't know if you can have 2 way database mirroring.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
Mirroring is 2 way Mladen...If your primary fails, the mirror takes over automatically(assuming you have a witness server) and when the primary is back up the roles are switched.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-06-04 : 10:29:37
|
| yeah? that's great! didn't know it worked that way. so both server serve as source and target? like the currently used server is the source.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-04 : 10:40:26
|
quote:
Originally posted by spirit1
yeah? that's great! didn't know it worked that way. so both server serve as source and target? like the currently used server is the source.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
c'mon now.. stop teasing...  Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-06-04 : 10:47:01
|
| i'm not. i'm no hardcore dba. if you ask me to setup a cluster i'd have no idea how to do it.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-04 : 10:48:10
|
| ok.. here's a quick overview (10 mins read) on how Mirroring works: Link in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/97e32ac6-2c1a-4563-af6f-7cbb986fe49b.htmDinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-06-04 : 10:54:47
|
| thanx.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.5 out! |
|
|
|
|
How to keep servers up to date?