This is the first part of the series “Installing SQL Server 2008/2008R2 Failover Cluster”.
Let me ask you something first! Have you ever been in the situation where you want to explore about your cluster, but your hands were tied because of the Live Production Server? Raise your Hands Up if “YES”. I can see lot of hands raised. Okay!Come back to your normal position before somebody sees you raising your hand, sitting in front of your computer ;-p.
Did you ever think that ” hey what happens if i do this on my cluster, what is that option, what is this option etc…” but again you couldn’t because of the real environment. I Agree we’ve POC(Proof Of Concepts) Servers for DBA’s team, but how much freedom do you really have to destroy the entire cluster?? Do you really think that you Boss will be Okay if you destroy the entire cluster in your LAB?? Most of the LAB environments I’ve seen will be a simple Stand Alone Server(s) where we can install and play with new SQL Server(s) and Service Packs and so on….But really they don’t fulfill our desire to explore and play with cluster and SAN!!
Don’t worry…in this Series I’m going to explain step by step installation right from creating your own Domain on a Domain Controller, Build your own SAN(Yeyyyyyyy! Exciting huh???), Build your Windows Servers and at last Installing SQL Server 2008 Failover Cluster 🙂
Before going any further, please read What a cluster can do?. So…by this time i assume you understood what a cluster can do from 20,000 miles distance!
Let me explain little bit further:
- Failover cluster is a combination of multiple Servers(Physical Nodes) all connected to a shared storage(SAN-Storage Area Network).
- If your Primary Node fails for some reason,the hot spare node(Active-Passive config) takes over the ownership of your application and all your operations continue to work seamlessly
- Failover Clustering provides high availability for your entire SQL Server Instance(Failover occurs as a Single unit) unlike Log shipping or Database Mirroring where they offer only Database level Failover.
- Your SQL Server Failover Cluster appears as a single computer to the clients connecting to you SQL Server.They don’t even know at what node SQL Server is running on.
- SQL Server offers Single Instance Clusters and Multi-Instance Clusters.
- Single Instance:Let us say, you’ve only one SQL Server Instance running at any given time on your cluster. It’ll be running either on your 1st node or 2nd node.(Active-Passive).
- Multi Instance: Let’s say you’ve 2 nodes running 2 Instances or even 4 Instances of SQL Server, Or let’s say you’ve 3 nodes where you’ve 2 Instances of SQL Server(Active-Active-Passive), the third node serving as a standby node ready to take ownership in an event of any failure of Node1 or Node2.
Things to Know:
- Failover Clustering is an Enterprise edition feature. So, You can create a FOC only on Windows Server Enterprise or above(Windows Server Data Center Edition).
- SQL Server 2008 Enterprise on Windows Server Enterprise/Data center supports upto 16 Physical Nodes and 50 Instances of SQL Servers 🙂
- You can do SQL Server Cluster installation with SQL Standard edition too(But remember Windows needs to be minimum on enterprise edition)…but it only support upto 2 physical Nodes and can be scaled upto 16 Instances of SQL Server!!(which is never a good solution to go upto 16 Instances on Just 2 Nodes)
- SSIS/SSRS are not cluster aware by default.(You can imagine SSRS as a Web Service where you really don’t need to be as a fail over cluster service. Typically it goes with scaled out deployment on NLB Clustering).
- There are few tricks where we can cluster SSIS, but it is not recommended by Microsoft to cluster your SSIS installation.
- Minimum 2 windows servers(enterprise edition) besides your domain controller.
- All the participating Hardware and drives should be certified by microsoft.
- minimum two NIC are needed per server. Netbios should be disabled on the network cards associated with HeartBeat(Private network).
- Shared Storage(SAN) is needed. Again PS, this should be microsoft certified.
- Ensure that disks have same drive letter mappings on all the physical nodes participating in clustering.
- Windows Server Failover Clustering Feature and Application role should be added on Each node manually participating in SQL clustering.
MSSQLTREK 
Private Network Requirement in Windows 2008 R2
I have a question
I am running a lab enviornment on my laptop two node SQL 2008 R2 running on windows 2008 R2 clustered Vms
Based on my understanding
a) Public Network or all Communication network
I see checkbox is enabled for cluster communication on this network also.
b) Private or HeartBeat or Cluster communication only network.
It appears to me that currently public network is used for cluster communication?
Is this correct? Does heartbeat network is used only for redundancy and cluster communication is primarily done on public network?
Hi Chirag!
To keep it very simple, Private – For Pure Internal Cluster Communications and Public – Clients connect through this Network(Basically, this is part of your cluster!). I wouldn’t go for option “do not allow this network for cluster communications” for Public Network.
BTW, If you want to dig deep, there is drastic deviation in Win Server 2008 Failover clustering networking from Windows Server 2003. I personally recommend reading http://blogs.technet.com/b/askcore/archive/2010/02/12/windows-server-2008-failover-clusters-networking-part-1.aspx for a very detailed information.
Hello Sreekanth, Excellent, Marvellous blog I ever seen. Thanks for sharing knowledge with all of DBA’s.
Thanks & Regards
Deepak Kumar Sql Server DBA
Thanks Deepak! Am Glad this clustering series helped you…
Cheers!
Sree, I am just reading your article, but failed to get the second part (part 2) for it. please help me out to get that 2nd part.
Thanks & Regards
Deepak
Here you go….. sqlbuzz.wordpress.com/2011/08/02/installing-sql-server-20082008r2-failover-cluster-part2/
T-Mobile. America’s First Nationwide 4G Network
Sree, One more thing to ask, in multi cluster (active, active). If we install service pack on Node1, Will it automatically deploy on Node2, Node3 so on……..? Like installing service Pack 2 on Node 1 and then we manually failover it then will it automatically install on Node2 ? Sorry, If I am confusing you.
Deepak
Nope! Thats used to be the case on SQL Server 2005 where sql server installations and patches are cluster aware. Starting SQL 2008, this process is no longer cluster aware…In other words, you have to manually apply patches on all the nodes. BTW, the last part in this series clearly explains on patching process of clustered sql servers.
Powered by Android.
Excellent posting, looking forward to reading next posting
Excellent Article Sreekanth..Helped me in understanding the Cluster..As a beginner, am setting up my own cluster environment and doing practising. Any idea about how we can setup a 2- Node cluster mirroring? Example- A 2- node Cluster in Uk and 2- Node Cluster in Netherlands. How we can setup mirroring between these two cluster environments? Please let me know the requirements for this as like Domain should be the same? etc. Or instead of this mirroring between two clusters, do you think replication is a better choice? Please clarify me in both the ways.Thanks
Thanks Nagavara! Glad it helped you…. Coming to your Question(s), There’s no such thing called Cluster Mirroring 🙂 If I Understood Correctly, Basically you are looking for setting you DB Mirroring between 2 Failover Clusters, Is this correct? If that’s the case, It’s pretty much the same how it works with your StandAlone SQL Instances. Coming to UK -Netherlands : I prefer Replication over Mirroring in this case. For Mirroring, there are lot of other things which comes into play like your Network Speed between your Principal and Mirrored Instance, Mode of Mirroring, Amount of Transaction Log records Generation on your Principal.
Epitome: You can setup Mirroring between two clusters with pretty much same procedure what you can follow for setting between two standalone Instances. Hope this Hleps!
Thanks Sreekanth for your Quick reply.
So you said “replication over Mirroring”, what kind of replication you go for the above – UK to Netherlands 2-Node cluster Scenerio? And regarding the mirroring between 2-node clusters do you have any article or documentation so that i can follow it? Only thing i was confused here is, for example Node-A and Node-B are in cluster sitting in UK & Node-C and Node-D are in cluster sitting in Netherlands. When mirroring in this case, should i do between Node-A and Node-c & Node-B and Node-D ? if i do this, how the cluster drives in UK will be available to cluster drives in Netherlands because both will be using different quorums and both are of different cluster environments.
Well, What kind of Replication really depends on the Users Requirement. Not all the Replication topologies serve the same purpose. You’ve to discuss with your App teams and even business sometimes to make this decision.(Troubleshooting Replication sometimes is really pain, Your Mileage May Vary) Looks like you are confused with Clustering topology, Are you saying that Node1,Node2 are in UK and Node3,Node4 are in Netherlands and all the Nodes are forming the same Cluster. In this Case this becomes Geo-Cluster with 4 Nodes, Which is too complex to understand initially. In General, all the Nodes in a Basic Failover Cluster will be sitting in the same DataCenter and Public NIC’s on the same Subnet. Don’t get confused with Clustering and Mirroring. I Strongly recommend you to Understand the concepts seperately, Once you get thorough understanding of Clustering and Mirroring…Try to combine both together.
Thanks. I will better go a bit deep into clustering knowledge before i understand the mirroring between two clusters.
HI Sreekanth, can you please clarify with this,
I have a 2-node cluster which is a single instance active/passive at present. This single instance installation files were done on (C:) Drive where as other drives were given for (D:)Data, (E:)Log and (F:)Backups and other two drives for quorum and MSTDC
Now, Client requested us to setup a new named instance on this 2-node cluster. For this we requested for New IP address and Shared disks for Data, Log and Backups. My doubt here is, can i use the same (C:) Drive for the New named instance installation files?. Thanks.
If I understood Correctly, you are referring to Multi-Instance Cluster…So 1st Question which you’ve to ask urself is, what is the Preferred Node for 2nd Instance? In Other Words, have you decided to run both the Instances from Node1 primarily and use Node2 as secondary for both the Instances or use Node1 as Preferred Node for INS1 and Node2 as Prefered Node for INS2?
We are making Node 1 as the preferred choice(Active) for new instance, keeping Node 2 as passive. so in this case can we use the same C: drive for installing the New Instance?
If you are referring to location where you Install SQL Binaries, Yes. You can! Basically It will create a seperate folder for each Instance….
Hi Sreekanth, Thanks for your help in regards to clustering.Want to request you one more thing. I had NodeA and NodeB. There was a standalone instance called ‘SQL01’. My client asked to put this standalone into clustering.What will be your way of working.
My doubts are;
[1] If my client requests- clustering instance should be the same name of standalone, can i install the failover cluster installation with the same standalone ‘SQL01’ name on the same server in parallel ? once the failover installation is done than i can detach and attach the databases making less down time. (OR)
[2] Should i totally remove the standalone instance on the server and than install the failover with the same name ‘SQL01’ asking client for more down time?
[3] Can the standalone instance name ‘SQL01’ be used on the same server for the clustering instance as well? Thanks.
Hi Naga, Let me tell you this…There is no simple Switch to convert a Standalone SQL Instance into Clustered Instance. Why? Because, SQL Server should be running on a Windows Cluster in the first place in order to run as a Clustered SQL Instance; and in most of the cases(99.999%) Standalone SQL Instances are Installed on Standalone Windows Servers. Coming to you Question…Did you said, you’ve a Standalone SQL Instance running on one of the Nodes in your Windows Cluster(Which also has other Clustered SQL Instance Running) and now you need to Introduce this Standalone Instance into your Failover Cluster??…Is this correct?
Yes Sreekanth. It’s a windows clustered server. I have 3 instances on Node A where 2 instances are in cluster. One is standalone on only Node A which is a normal SQL Server Instance. Now i want to put this standalone into Failover cluster so that all the 3 instances will be in fail over clustering.
So, i was thinking to start a fresh failover cluster installation on server. But now whether i can give the same name as standalone here?? or First i should completely remove the standalone and than install the failover so that it accepts the original standalone instance name?
I’ve never been in that Situation….Look at this article. http://blogs.msdn.com/b/blakhani/archive/2010/01/27/how-to-move-sql-server-from-standalone-to-cluster-environment.aspx
Hope this helps… Personally, I would go with creating a new Instance with as a new Named Instance and making changes in Applications as needed, pointing to new Instance. How big are the Databases residing on this Standalone Instance? Anyways…either approach has Downtime….involved!
So it mean, detach all the databases first and uninstall the standalone first and than install the failover with the same name which it previously had for standalone. Only thing is, for this we need more down time.
If i can able to install the failover instance name same as standalone instance name than i would have required less downtime. I shall work on it and update you.
Thanks Sreekanth.
I think it will fail if we create with the same Instance name(not 100% sure though). Pls. let me know how did it go.
HI Sreekanth, i have tried doing the cluster installation with the same name of standalone instance name and as predicted failed !! so I just thought of dropping you a message to let you know. As i was stuck with too much work couldn’t able to come back to you soon. Thanks.
Hi Sreekanth, i have a question, i’m installing a SQL failover cluster with 2 node and 2 instances ( will be active\active ). Are SSRS, SSIS, SSIS mandatory services or just optional ? I’m only setting up the resources and MSDTC instance.
Thanks a lot
They are optional…
Remember, basically you are Clsutering your “Database Engine” peice of SQL Server.
Thanks Sreekanth, may i ask you some clarification ? what are the difference in the privilege to sql engine user and agent , considering windows auth ? i tipically use the same user as a domain admin, dedicated user. Thanks
Are you referring to Service accounts for DB Engine and SQL Agent? SQL Agent is a seperate service(But dependent on SQL Engine) which can be run under a different service account(Recommended practice by microsoft for securty reasons). If you are using a seperate account for Agent, you have to make sure that, Agent Service account has all the required permissions to the folders/Shares to finish its job without any issues. Basically, you have to manage an additional account besides SQL Engine Service account.
Note: Using Domain Admin as a SQL Server Service account is a very Bad choice. All you need is just a user account on your domain. Why do you need a Domain admin for running SQL Server?
Ok, so i can use a normal user domain account for the sql cluster, that’s make sense. should this account be a local administrator also ? thanks a lot for clarification Sreekanth
Nope! SQL Service account being a member of Local Admin is not mandatory. It is not a requirement to have SQL Server Service accounts in your Local Admin Group. All you have to do is, granting only the permissions which are needed. Please go through below link for more clarification….
http://blogs.technet.com/b/canitpro/archive/2012/02/08/the-sql-guy-post-15-best-practices-for-using-sql-server-service-accounts.aspx
Ok, perfect Sreekanth, thanks. One more question to clarify my ideas. When i create an instance of the SQL and then i use an application server thatacreate the dB poiting the instance of SQL, only one DB per instance is possible ? I mean for example, the instance name is SQLDB1 and the application server create 2 DB. Can one be SQLDB1\DB1 and SQLDB1\DB2 or i need 2 instance of SQL for each Db created by the application server?
Am not sure I understood your question correctly…But, You can have multiple databases on a single SQL Instance.
ok, that’s the answer i need, thanks Sreekanth, much more clear to me now.
Sree, Could you please brief us about multi instance clustering in sql server 2008 r2 and please let us know about multi subnet clustering in sql server 2012 (Denali).
Deepak – As I already mentioned above somewhere in the comments, If you create 2nd Instance on your cluster, that becomes Multi Instance Clustering(People used to call it as Active-Active). Yes, it’s really that simple. Don’t get confused…!
Multi Subnet Clustering: In this topology, basically you will have one Node of your cluster in One Subnet and another node on a different subnet(Most of the cases, it will be on different Data Center). Hence it is AKA “Geo Clustering”. Starting SQL server 2012, it will support “OR” dependency of IP Address resources. This avoids making use of Streached VLAN. For More info…go through below:
http://technet.microsoft.com/en-us/library/ff878716.aspx
Below video should give you basic back ground on Multi Subnet clustering to you…
Hope this helps….!