October 12, 2009 at 12:44 pm
Hello all,
I've been trying for days now to get my new SQL Server x64 Enterprise cluster online in an active / active configuration.
In the end, this is going to an a 5 node cluster with one acting as passive.
My question / issue stems from not being able to install a new instance of SQL without it being a named instance. I get the first node online then the second will only install as a named instance..
Help?
October 12, 2009 at 3:05 pm
You may only install one default instance of SQL Server on a server.
For clustering to work, you must install an instance on every node (server) in the cluster (the installer will do that for you).
The base idea behind clustering is that an instance of SQL Server may run on any of the nodes. Even though your intention is to run the cluster in active/active config - two instances of SQL Server, each running on a different server - if one server fails, the active instance on the failed server will move to the other server. That leaves both instances running on a single server. Both of those instances cannot be the default instance. Otherwise, how would you identify to which instance you wish to connect?
When you performed the first clustered install of the default instance, you claimed the server's default instance for that instance across every server in the cluster to which you enabled that instance to run.
All subsequently-installed instances must be named instances as a result. Quite often, when folks install instances on clusters, they only use named instances.
-Eddie
Eddie Wuerch
MCM: SQL
February 16, 2010 at 2:33 am
Hi all,
I found a great product that is running as a GRIDSCALE and it's activating like a active/active cluster, take a look:
P.B.
If you will buy it, just send me an botle of wine 🙂
Best regards,
Victor S.
DBA
February 17, 2010 at 2:55 am
SQL Server clustering is designed to solve the problem of high availability, not the problem of load balancing.
You can install SQL Server on a multi-node cluster, but only one node of the SQL Server instance name can be active at one time. If you have a multi-node cluster this has the effect of increasing the number of locations where SQL Server can be started if a given server fails, but it does not allow you to run multiple active nodes at one time.
Gridscale adds a little bit extra. This gives you a single logical name your applications can use to connect to SQL Server, and routes the connection request to the appropriate SQL Server instance. Each SQL Server instance in Gridscale has exclusive use of the databases attached to it. This means that if you have a given database that is used very heavily it MUST be located on a server that has enough power to run all your queries. Gridscale does not give you a shared data cluster where multiple nodes can both update the same database.
There is a way to get a shared data cluster with SQL Server, where multiple nodes are able to update the same database, but I have no idea what performance this would have. If you have what Microsoft call a geographically dispersed cluster you use SAN replication to keep the various LUNs synchronised. SQL Server uses the VSS writer component to let all active nodes know what pages have been changed, so that the other nodes can refresh their copy of these pages from disk. It is marketed as a way to distribute a cluster across multiple data centres, but there is no reason why everything could not be located in a single SAN. My main reservation with this setup is lack of community knowledge on the performance of the VSS writer component, because if this cannot keep up with the rate of change everything would comer to a halt or get corrupted.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 17, 2010 at 8:50 am
richard-730095 (10/12/2009)
Hello all,I've been trying for days now to get my new SQL Server x64 Enterprise cluster online in an active / active configuration.
In the end, this is going to an a 5 node cluster with one acting as passive.
My question / issue stems from not being able to install a new instance of SQL without it being a named instance. I get the first node online then the second will only install as a named instance..
Help?
Eddie explained very well how active\active clustering works. If that is the case why don't you go for Active\Passive clustering?
EnjoY!
October 17, 2011 at 10:38 am
Hi Gurus,
I'm having trouble understanding SQL Server active/active cluster model. I was asked by our management about the difference between active/passive active/active to run SharePoint 2010.
I know what is active/passive means. I’m trying to make sense out of active/active. So in active/active, you have 2 instances running on each node. Say
In a two node Windows 2008 cluster server,
Instance share2010 running on node1 as active and share2010 running on node2 as passive
Instance share2010_2 running on node2 as active and share2010_2 running on node1 as passive
Can someone please explain how application(SharePoint 2010) failover would work ?
And how data between 2 instances are sync ?
November 28, 2011 at 4:11 am
SQLDBA-536161 (10/17/2011)
Hi Gurus,I'm having trouble understanding SQL Server active/active cluster model. I was asked by our management about the difference between active/passive active/active to run SharePoint 2010.
I know what is active/passive means. I’m trying to make sense out of active/active. So in active/active, you have 2 instances running on each node. Say
In a two node Windows 2008 cluster server,
Instance share2010 running on node1 as active and share2010 running on node2 as passive
Instance share2010_2 running on node2 as active and share2010_2 running on node1 as passive
Can someone please explain how application(SharePoint 2010) failover would work ?
And how data between 2 instances are sync ?
What is your goal here? Two different sharepoint sites, eg. sharepoint1.domain.com and sharepoint2.domain.com or are you just having sharepoint.domain.com?
If you are having just sharepoint.domain.com then you want active passive. If you are having sharepoint1.domain.com and sharepoint2.domain.com then you can either have active passive and have the databases on the same instance aslong as they names are different, e.g WSS_Content_SP1, WSS_Content_SP2, or you could go active active with two SQL instances, and the db's could be called the same, e.g WSS_Content.
The data would not sync between the two instances and the failover would be just like a normal fail over, where the resource group moves over to the second node and starts up, and its the same instance, same databases, then Sharepoint will continue to work, no change nessesary.
September 30, 2012 at 11:24 pm
You are right Eddie and I agree with you
But let's all think about the following scenario (Note: I didn't test yet)
There is another solution called "Network Load Balancing" that comes with Windows servers 2000 Advanced server and next later versions of windows so I believe that the way we can make Active - Active solutions is to merge both techniques together.
what I want to say is in case we have enough resources and servers we can do the following:
Windows Clusters Configuration:
Cluster A (SQL Node 1, SQL Node 2) ----> Shared disk Drives (ABCD) , Cluster A IP Address: 192.168.0.110
Cluster B (SQL Node 3, SQL Node 4) ----> Shared disk Drives (ABCD) , Cluster B IP Address: 192.168.0.120
Cluster C (SQL Node 5, SQL Node 6) ----> Shared disk Drives (ABCD) , Cluster C IP Address: 192.168.0.130
Network Balancing must be configured for the following clusters:
Cluster A , Cluster B, Cluster C
The Public IP Address of Network Load Balance Service is: 192.168.0.140
Now,
1- The Users will connect to the servers using NBL of IP: 192.168.0.140
2- The load on each server could be balanced because of NBL Balance.
3- NBL will handle redirecting user's requests to the Clusters.
4- All Clusters are using same Shared Disks that means all are using same databases location.
5- We'll have only one problem or doubt which is does SQL Server installer overwrite the configuration of other clusters?
This is a question.
Does anyone has faced same scenario? or has any correction ?
I am just thinking and would like to share my thoughts
October 1, 2012 at 5:28 am
Your Active/Active scenario will not work. There is an inherent restriction with SQL Server that means a given SQL service must have exclusive access to the database file.
If somehow you get past this and get multiple SQL instances to share the same disk in read/write mode you will corrupt the databases. When SQL instance A reads a page from disk to its buffer, it assumes it has 100% control over the page. If SQL instance B replaces the page on disk this is not known to SQL instance A. When SQL instance A writes the page to disk you will destroy the updates made by SQL instance B.
If you really need multiple database instances able to update the same database file, then you need to look at DB2. In DB2 there is instance to instance communication to let all instances know which instance is updating a given database page, so that updates from multiple instances do not get lost.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 1, 2012 at 9:11 pm
Hi and thanks for your quick response,
So, is there anyway to implement Active / Active scenario to ensure the load on each server is as low as possible?
what does microsoft say regarding this?
October 2, 2012 at 2:32 am
As far as a cluster is concerned, you are limited to Active/Passive for a given database.
If you want to have multiple instances accessing the same database, then the two simplest things are:
a) Mirroring. This works OK in SQL 2005 and above, but is greatly improved in SQL 2012. Mirroring will allow you to have one instance doing all the updates while the other instances read committed data.
b) Peer to Peer replication. This is what we use. It does support multiple instances updating the same set of data and sharing the updates, but you need your application to be designed to support this. We need P2P as we populate scale-out servers at multiple sites, which cannot be done with mirroring, but direct all our updates to a single instance because our applications cannot cope with multi-site update.
c) Merge replication. The typical use case for this is where you have subscriber databases that may not be connected to your network full time. It allows more flexibility than P2P, but is more complex to maintain.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 18, 2012 at 10:40 am
What is the difference between Active/Active and Acitve/Passive clustring in SQL 2008??
October 19, 2012 at 3:44 am
All clustering in SQL Server is inherently Active/Passive. A given database can only be online to a single node at any one time.
People use the term Active/Active to indicate they have installed multiple SQL Server clusters, each one with its own set of databases. In this scenario, SQL Cluster 1 can have its active databases on windows cluster node A, and SQL Cluster 2 can have its active databases on windows cluster node B. This means both windows cluster nodes are active, but they are active with completely separate databases.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply