Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2008 enterprise cluster and peer-to-peer replication Expand / Collapse
Author
Message
Posted Tuesday, July 12, 2011 3:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 25, 2011 7:24 AM
Points: 15, Visits: 64
Hi,

We have a customer that has 2 physical sites, we are putting 1 sql server in each site. each site has a different subnet.
we are planning to have a cluster were if server1 or the whole site1 went down, server2 will pick up and process the transactions.

mainly server1 at site1 will be the most active one, if server1 went down, server2 will pick up, other than that server1 will be taking all the requests.
when server1/site1 comes back up, server1 would be taking all the requests.

This my first time setting up a cluster, so i would appreciate some feed back.

As far as i understand it is i should install windows 2008 and configure the cluster then install sql on both machines and configure peer-to-peer replication since i have sql enterprise.

is that the right path?

thank you
Post #1140694
Posted Tuesday, July 12, 2011 3:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
I think you are confusing a number of things..

peer-to-peer replication is not typically used in cluster configurations because the database files are on shared storage and onyl one server may be in control of the shared storage at a time.

It sounds like you are talking about a multi-site cluster which has some challenges.. If that is it you might look at:
http://clusteringformeremortals.com/2009/09/15/step-by-step-configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-%e2%80%93-part-1/

Clustering is not a load balancing solution, only one node will be used for an instance of SQL, on failover the other node is used.

Perhaps you should lay out what your goals are for this setup.

CEWII
Post #1140708
Posted Wednesday, July 13, 2011 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 25, 2011 7:24 AM
Points: 15, Visits: 64
Thanks Elliott.

Site1 has 90% of the users so i what i was thinking is sql1 would take all the requests from site1 and site2.
If sql1 is down, all the requests will go to sql2.

Peer-to-Peer, as i understood it, can replicate changes both ways, so if sql1 was taking all the requests, all the changes will be replicated to sql2. if sql1 went down, sql2 will be taking all the requests and once sql1 comes back up, sql1 will start taking all the requests and all the changes that happened on sql2 will be replicated to sql1.

now, would that work with a windows cluster where i can specify that sql1 server is the primary and sql2 server is the backup?

thanks again.
Post #1140933
Posted Wednesday, July 13, 2011 8:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
Lebanese (7/13/2011)
Site1 has 90% of the users so i what i was thinking is sql1 would take all the requests from site1 and site2.
If sql1 is down, all the requests will go to sql2.

Peer-to-Peer, as i understood it, can replicate changes both ways, so if sql1 was taking all the requests, all the changes will be replicated to sql2. if sql1 went down, sql2 will be taking all the requests and once sql1 comes back up, sql1 will start taking all the requests and all the changes that happened on sql2 will be replicated to sql1.

now, would that work with a windows cluster where i can specify that sql1 server is the primary and sql2 server is the backup?
What you are specifying maybe be supportable by clustering but seems to be built on a misunderstanding of the way clustering works. Keep in mind that a cluster involves 2 (or more) nodes and SQL instance(s). However 1 or 10 instances they all work the same. An instance of SQL runs on a SINGLE node, ALWAYS, should that node become unavailable a failover occurs and that instance of SQL is started on another available node, still just one node. Same name, same ip, same databases, same SQL Server. Clients only see a brief disruption in connections.

You certainly could have a second instance on one of the other nodes that you are replicating between, but WHY?

If your desire is for load balancing then clustering is not really needed, if you are looking for high availability then P-2-P replication is not needed. So the real question is what are you trying to accomplish? And don't approach it from what solution to use, but what you need to accomplish and then we can work a solution.

CEWII
Post #1141027
Posted Wednesday, July 13, 2011 11:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 25, 2011 7:24 AM
Points: 15, Visits: 64
Elliott,

Thanks man for all your time.
I think i'm not explaining it right.
I'm trying to have an environment with 2 sql servers. traffic will go to sql1 all the time from both sites. If sql1 went down, sql2 will pick up automatically from site2. Also the databases between these 2 sql servers should be up-to-date so if one of the sql servers went down, the other sql server can function normally as if nothing happened.

based on the above requirements, i thought cluster will take care of the switching between the 2 sql servers if any of the servers goes down and peer-to-peer replication will take care of the replication of the databases.

am i little close? :)

thank you again.
Rami
Post #1141199
Posted Wednesday, July 13, 2011 1:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
You are most of the way there.

Let me restate your requirement and then then configs that can handle that.

You have databases that you need to have high availability on. You have two sites that can host the underlying SQL server(s). You need the data to be current on the alternate site in case of a failure of the primary.

That pretty much the requirement?

You can accomplish this with a multi-site cluster, following the instructions in the link I provided. Using clustering in that case does not use SQL replication at all, instead it is storage replication. The downside is this is the single most complex kind of clustering and is done by a VERY small fraction of people doing clustering.

Provided the links are fast enough between sites either of these two are possible.

For a limited number of databases you could use synchronous database mirroring with a witness, this would write the data to both the local and remote server at the same moment and failover is automatic. Failback is more challenging.

If you could tolerate minor levels of data-loss, say <3 minutes worth you could consider log-shipping. Failovers would be manual and there are challenges related to logins and the failover itself but the fail back is fairly straightforward.

The multi-site thing really makes this a challenge, the cross subnet failover won't be supported until SQL 2011 is released, which I would expect toward the end of the year.

Are the clients of these databases largely internal or external, is there a web server sitting in front or what?

CEWII
Post #1141272
Posted Wednesday, July 13, 2011 1:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
I also found this related to cross-subnet clusters:
http://msmvps.com/blogs/jtoner/archive/2008/08/08/multiple-subnets-with-windows-2008-clusters.aspx

CEWII
Post #1141303
Posted Wednesday, July 13, 2011 2:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 25, 2011 7:24 AM
Points: 15, Visits: 64
wow, Elliott!

really thankfull for your information.
just have a question, why you don't like peer-to-peer in this configuration?
i already have sql enterprise and it can be setup in a breeze.

thanks again.
Post #1141323
Posted Wednesday, July 13, 2011 2:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 25, 2011 7:24 AM
Points: 15, Visits: 64
Elliott,

you said
peer-to-peer replication is not typically used in cluster configurations because the database files are on shared storage and onyl one server may be in control of the shared storage at a time.

i found a youtube video of how to do it... it doesn't look like it is a shared storage
http://www.youtube.com/watch?v=mbANQW-73Mc

sorry if i'm asking dump questions but i just wanted to know.

thanks again Elliott,.

Post #1141328
Posted Wednesday, July 13, 2011 2:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 25, 2011 7:24 AM
Points: 15, Visits: 64
just found this link

http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/36935326-b52e-4d14-9114-2b1f52ad05fd

Post #1141356
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse