Which replication method? 1

[peterlyttle]

I was hoping for some advice on the following. I have 2 sites X and Y that have a 100mbit link between them. I would like have a DB that is accessible and updateable from both sites DBX and DBY so that local users on each site access the local DB eg X accesses DBX and Y DBY. In the event that DBX fails the X sites clients would then be pointed to DBY and vice versa.

Notes -
The data that is being updated may be updated on each site simultaneously.
There is no shared storage between sites.
Site X and Y are on separate subnets.

Can anyone recommend the best method; MS where saying Merge Replication would be the best method. What are your views?

Cheers,
Peter

 

[mrdenny]

Yep, Merge Replication would be your best choice. Now with replication there isn't any auto failover. So if the database on ServerX isn't available the clients (or the application) would need to know how to change the config so that they can point to the other site.

Now with people writing to the same records in both sites you will have people overwriting each others changes.

You might want to look at database mirroring. All users connect to the same database, but if that database isn't available you can fail over to the other database (can be done automatically).

With a 100mbit connection your users shouldn't really notice any performance issues over the WAN link unless the WAN is running at capacity, or the sites are 10,000+ miles apart.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[peterlyttle]

I had been thinking of using DNS Netmask Ordering where if I have 2 A records eg

SQLSERVER 192.168.0.1 (site X range)
SQLSERVER 192.166.1.1 (site Y range)

Any clients on the X range will hit the 192.168.0.1 and Y will hit the 192.168.1.1 unless it is unavailable in which case Round Robin will kick in.

I thought there was a way that a SQL Admin could be notified of a conflict happening and then decided which server 'wins' - in theory this sounds good but has anyone put it into practise?

Cheers

 

[mrdenny]

DNS won't know if the machine is down. you'd have to remove the downed machine manually.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[peterlyttle]

Yea but I could remove the A record internally to speed things up?

Time to look into merge replication then!

Cheers

 

[mrdenny]

You could, but everyone in the site will have the DNS info cached on their machines. You'll also need to have everyone clear their DNS cache to ensure that the get the new IP.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog