July 12, 2011 at 3:19 pm
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
July 12, 2011 at 3:59 pm
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/[/url]
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
July 13, 2011 at 6:19 am
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.
July 13, 2011 at 8:33 am
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
July 13, 2011 at 11:40 am
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
July 13, 2011 at 1:17 pm
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
July 13, 2011 at 1:45 pm
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
July 13, 2011 at 2:01 pm
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.
July 13, 2011 at 2:10 pm
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,.
July 13, 2011 at 2:31 pm
July 13, 2011 at 2:46 pm
Lebanese (7/13/2011)
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.
Before I answer that, how will you do a failover if node 1 fails? How about a fail back?
Also keep in mind that at its heart P-2-P replication is transactional replication and all that that entails. Transaction replication should not be entered into lightly.
I don't want you to go down that path if its not needed and if its not the right fit.
If you have decided to go down that path then fine, I wouldn't, but thats me.
CEWII
July 13, 2011 at 2:55 pm
Lebanese (7/13/2011)
you saidpeer-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
Sorry, I can't get to YouTube from work.. but in a SQL cluster shared storage is required unless you are using a third-party tool like SteelEye DataKeeper to handle the underlying data replication between nodes. The tools also effectively presents a volume of space to the cluster service and says its shared storage. SAN replication can also accomplish this. But in the end you have to have something that the cluster thinks is shared. You can physically build a cluster without shared storage however you cannot install SQL on it. SQL will not allow it.
CEWII
July 27, 2011 at 11:58 pm
Peer-to-Peer replication is not really meant for what you describe. It is recommended that all of the writes occur on a single node (Site A) and the other nodes be used primarily for read operations. If you lost Site A there is no client redirection to automatically connect the clients in Site B to the server in Site B.
http://msdn.microsoft.com/en-us/library/ms151196.aspx
You are better off with Database Mirroring or a Multisite Cluster[/url].
July 28, 2011 at 12:42 am
As I understand your requirements, let me try to put the solution in simple way:
1) You need autofailover and syncronization between databases -- Opt for SQL Server failover cluster
Actually in this you have 2 physical servers and an SQL Server instance or instances are installed on both the servers. When user access the SQL server it access Virtual SQL Server..the underlying(actual) instance being accessed is the one which is active at that moment. When active goes down failover occurs and 2nd one becomes active..user will not notice this change. As the instance and databases sitting on it are on cluster....they are always syncronized (I mean since they access same storage...the same database is accessed.)
2) You can also go for Database mirroring with witness server..
Here also you get auto failover but then the mirrored database is normally not accessible (You need to use datanase snapshot on mirroed DB..if at all you need to access it).
Advantage: configuration is easier than cluster configuration. You can save some hardware (Storage etc)
Replication is all together different solution...which may not provide complete HA solution.
Hope this helps!
Cheers,
Shailendra
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply