At first I was a little confused about the concept of Active/Active and Active/Passive and its relationship with SQL failover clustering. In a multi failover cluster instances, these terms can be seen as ambiguous when used to describe the configuration of the SQL Server instances and the nodes they are running on. This term can give the misleading impression that some load balancing is occurring across nodes or across SQL Server instances 
.
Active/Passive Cluster: In an Active/Passive scenario, all SQL instances are owned by only one node irrespective of the number of nodes present in a SQL cluster. In the event of a failover, ownership would be transferred to the next preferred node.
Active/Active Cluster: In an Active/Active scenario, all SQL instances are owned by individual participating cluster nodes respectively. In a two instance setup, instance1 will be owned by node1 while instance2 would be owned by node2. Note you can’t share a single network instance and its disks with two named instances, with the current version of SQL.
Requirement:
- A second Network Name/Named Instance with IP address (SYS02SQLINS01/INST02, 172.16.2.8)
- Port number (1435)
- SQL service account different from one used with first instance
- Additional disks for Database and Log files (Refer to my previous post in Part1 on how to add virtual disks)
Note: Enure that the new virtual disks for Data/Log had been formatted using 64K blocksize and added as Available Storage using Failover Cluster Manager.
Setup Guide
1. Node1
- Insert the SQL installation disk -> Double-click Setup.exe -> Wait for the SQL application to load
- On SQL Server Installation Center page -> click Installation -> click New SQL Server failover cluster installation
- On Setup Support Rules page -> click Show details -> Ensure the status column shows Passed for all rules -> click OK
- On Setup Support Files page -> click Install and wait for the setup rules to identify any problem
- Click Next
Note: You can ignore the Network Binding Order warning, this appears to be a bug
- On Product Key page -> Enter the product key or select Evaluation -> click Next
- Accept the license terms -> click Next
- On Feature Selection -> Select Database Engine Services; Analysis Services and Reporting Services (Note the shared services are greyed out because they are not instance aware and can only be installed once in a multi instance setup) -> click Next
- On Instance Configuration Page -> Enter the second Network Name/Named Instance -> click Next
- On Disk Space Requirement -> click Next
- On Cluster Resource Group -> Accept default -> click Next
- On Cluster Disk Selection -> Select the two new virtual disks -> click Next
- On Cluster Network Configuration page -> Deselect DHCP -> Enter IP address assigned for this instance -> click Next
- On Cluster Security Policy page -> Select ‘Use domain group’ -> Enter the SQL group account used in first instance -> click Next
- On Server Configuration page -> Click Use the same account for all SQL Server services -> Enter the SQL service account created and password -> click OK
- Click Collation tab -> Select your collation depending on your region -> click Next
- On Database Engine Configuration page -> Select either Windows authentication mode or Mixed mode -> Add the relevant accounts
- Select Data Directories Tab -> Enter Data/Log/Temp/Backup location depending on your choice
- Select FILESTREAM Tab if you want to enable it -> click Next
- On Analysis Services Configuration page -> Add required accounts
- Click Data Directories -> Make the needed directory changes -> click Next
- On Reporting Services Configuration page -> Select Install, but do not configure the report server -> click Next
- On Error Reporting page -> Tick the box if you want to send error reports to Microsoft -> click Next
- On Cluster Installation Rules page -> Ensure that there are no failed rules -> click Next
- On Ready to Install page -> Review your settings -> click Install
- Click Close after installation is completed.
2. Node2
- Insert the SQL installation disk -> Double-click Setup.exe -> Wait for the SQL application to load
- On SQL Server Installation Center page -> click Installation -> click Add node to a SQL server failover cluster
- On Setup Support Rules page -> click Show details -> Ensure the status column shows Passed for all rules -> click OK
- On Setup Support Files page -> click Install
- On Setup Support Rules page -> click Show details -> Ensure there are no failed rules -> click OK
- On Product Key page -> Enter the product key or select Evaluation -> click Next
- Accept the license terms -> click Next
- On Cluster Node Configuration -> Leave default (Note the new Instance only has one SQL node) -> click Next
- On Service Account page -> Enter password for SQL service account -> click Next
- On Error Reporting page -> Tick the box if you want to send error reports to Microsoft -> click Next
- On Add Node Rules page -> Ensure that there are no failed rules -> click Next
- On Ready to Add Node page -> Review your settings -> click Install
- Click Close after installation is completed
Post Configuration:
Remember in previous post when we installed the first SQL instance, we latter upgraded the instance to SQL SP1. The newly installed instance will not be automatically upgraded to SP1. To verify this:
- Open Microsoft SQL Server Management Studio -> On connect to server screen -> Enter the second instance server name
- Click New Query -> Type this command ‘select @@version‘ -> click Execute -> Take note of the version “10.50.1600.1″. Click here to view the different SQL version number and the corresponding service pack. So the version number for SP1 is 10.50.2500. To upgrade the new instance to SP1, follow the steps outlined in previous post (Click here). Note accept default settings this time and follow the on-screen instruction.
- After the upgrade, this would change the version number to 10.50.2500
Good morning … I am super confused about adding a second instance, to an existing sql cluster. Here is where the confusing exists, why does the new instance need a new set of disk for the data and logs? We have a stand alone slq server, and there we have many instances, and we are using the data d: and l: drives for the secondary instances. Can you please clear my confusion?
Thank you so much in advance.
In clustered setup, you need separate disks if you have more than one SQL network name, e.g. I have SYS01SQLINS01 and SYS02SQLINS01, both can’t share same disks but you can have multiple named instances sharing same disks. Hope this clears your question.
I have a 5 node A/A/A/A/P SQL 2008 R2 cluster on Windows 2008 R2 cluster. As of today, Node 1 has a named SQL Instance running on it and is set to Failover to Node2 and they both are the preferred owners/failover partners. Next, Node 3 has a named SQL instance installed on it and is set to failover to Node 5 and they both are preferred owners/failover partners in cluster. Node 4 doesn’t have anything running on it but it is in the windows cluster.
The task I was given is to now first change the failover partner(preferred owners) of Node 1 to Node 5(this is Node 2 at present) and then install a new named SQL cluster instance on the Node 2 and set it to failover to Node 5. Next thing I need to do is, install another named SQL instance on the Node 4 and set it to failover to Node 5. The ultimate goal here is all the first 4 nodes will have once single named instance running on each of them and they will all be set to failover to ONLY Node 5 in case any one goes down. Yeah Node 5 has all the resources like memory, CPU to handle the load of 4 nodes at a time.
In order to accomplish this here is my plan…
1. On Node 5, I run the SQL setup and do “Add node to the SQL failover cluster”. This is in preparation to make the failover partner for instance on Node 1 to Node 5. While doing this step in “Cluster Node Configuration” I will select the name of instance on Node 1 in “SQL Server Instance name” option.
2. Now I change the Failover partner instance on Node 1 to Node 5 and make Node 1 and Node 5 preferred owners.
3. Now I run the set up on Node 2 and select the “New SQL Server failover cluster Installation” and install new instance on Node 2.
4. On Node 5, I run the SQL setup again and do “Add node to the SQL failover cluster”. This is in preparation to make the failover partner/preferred owners for instance on Node 2 to Node 5. While doing this step in “Cluster Node Configuration” I will select the name of instance on Node 2 in “SQL Server Instance name” option.
5. Now I run the set up on Node 4 and select the “New SQL Server failover cluster Installation” and install new instance on Node 4.
6. On Node 5, I run the SQL setup again and do “Add node to the SQL failover cluster”. This is in preparation to make the failover partner for instance on Node 4 to Node 5. While doing this step in “Cluster Node Configuration” I will select the name of instance on Node 4 in “SQL Server Instance name” option.
7. Finally I will have to remove the Node 2 which was being used by Instance 1 on Node 1 earlier as a failover partner. I will perform this by following the Maintenance tab of install media and select the option to remove the node.
Please let me know if these steps are correct or if I am missing anything here. Any help will be very appreciated and will make my install smooth as I already have 2 production instances up and running in this cluster and I would like to make sure I don’t mess those during the install.
Interesting question
I’ll get back to you on this.
@amohammed
I have reviewed your steps and I must say your steps are good. I do not see you running into any issues if you follow the steps correctly. The only thing which I am sure you already know is:
1. All the new instances must have a unique network name and assigned IP address
2. Each new instances would require its own drives both for Database and Log files
3. Ensure all instances have same sp/patch level installed on all nodes so you don’t run into issues during failover
Thank you sir, appreciate your time on this.
Another quick question
When changing the failover partner of Instance 1 on Node 1 to Node 5(at present it is set to failover to node 2), I initially planned to add the node for instance 1 on Node 5 and then do remove node from node 2 for instance 1. But all my instances on cluster are going to be standard edition(allows only 2 nodes in cluster) so I am left with choice of doing this other way by removing the instance from Node 2 first and do Add node on Node 5. Just trying to play safe here as instance 1 is already in production.
Do you believe is there any other way to accomplish this without removing the Instance from Node 2 first?
Yes, SQL standard supports only two nodes per instances and enterprise up to 16 as at the last time I checked. In your situation, your only best option is to remove node2 from the cluster first but ensure you disable the preferred owner option first and ensure node1 is the current owner of instance1.
Hope this helps
Yep, thank you for the quick response.
I am testing Multi-Instance SQL Server 2012 Failover Clustering in Windows Server 2012 in Active/Passive or Active/Active scenarios. My second named instance got another Unique IP address, why not i can connect to my second instance with that second IP address other then calling SecondClusterNetworkName\SQLInstance2. Is there any way to connect my second or third instance by calling the provided IP address not with IP\Instance Name. It will be highly appropriated in this regards…….
@SohelNax
Sorry for responding late. Been busy with my day to day job
.
I am a bit confused as to what your actual question is here. Extracts from your comment:
why not i can connect to my second instance with that second IP address other then calling SecondClusterNetworkName\SQLInstance2
Is there any way to connect my second or third instance by calling the provided IP address not with IP\Instance Name
Your first statement says connect to second instance as IP\Instance Name but second statement says not with IP\Instance Name
Please clarify your question.