To provide High Availability for a SQL Server 2008 R2 instance, you can install it in a failover cluster. The High Availability will be provided at the instance level. In this blog post, we will look at how to install a SQL Server 2008 R2 Cluster. I’ll be using the SQL Server 2008 R2 Enterprise edition, but you can actually install Standard Edition in a failover cluster as well. Just keep in mind that, Standard edition can only have 2 nodes in the failover cluster, while Enterprise edition is limited by the maximum nodes for the Operating System. In Windows Server 2008 R2, that would be 16 nodes.
1.1 A failover cluster must already be present, before you can start installing SQL Server 2008 R2. If you need to know how to install a failover cluster, just follow this guide.
1.2 You should install the Database files, TempDB files and Log files on separate disks. You can force TempDB onto a local (non shared) disk on each node, but its not a supported solution. You will also need to have a separate shared disk for the MSDTC Service, which will be in its own cluster group. Therefore I have added four shared disks to the failover cluster.
1.3 Install the MSDTC service on the failover Cluster. SQL Server uses the MSDTC service for distributed queries and two-phase commit transactions, as well as for some replication functionality.
1.3.1 Start Failover Cluster Manager. Right-Click Services and applications, and choose Configure a Service or Application. Click Next on the first page of the wizard.
1.3.2 Choose Distributed Transaction Coordinator and then click Next.
1.3.3 Give the Service a name and an IP address, then click Next again.
1.3.4 Select the DTC disk as the storage volume you want to assign it. Then click Next -> Next -> Finish.
1.4 Install the .Net Framework 3.51 feature on all nodes of the Failover Cluster.
2. Install SQL Server 2008 R2 on the first node of the Failover Cluster
2.1 Start setup.exe
2.2 Click on Installation, and then choose New SQL Server failover cluster installation
2.3 Setup Support Rules are run, click on OK.
2.4 Enter the product key, then click Next.
2.5 Accept the license terms, click Next.
2.6 Install the Setup Support Files
2.7 Setup Support Rules are ran next, and they might reveal warnings or errors. Review and correct those, if possible.
Microsoft Cluster Service MSCS cluster verification Warning – I skipped some tests, that’s why I’m getting this warning. 🙂
Microsoft .Net Application Security Warning – The machine I am installing SQL Server on, does not have a connection to the internet. Therefore I am getting this warning.
Network binding order – Now this is an interesting warning. Microsoft mentions Ghosted Adapters being the cause in KB955963, but the warning is actually due to the Microsoft Failover Cluster Virtual Adapter, which is always at the top of the binding order, and Microsoft recommends that you should keep it on the top of the binding order, unless you want to experience problems with the Failover Cluster. So basically, you can not get rid of this warning, yet Microsoft does not acknowledge that, and you will receive the same warning in SQL Server 2012.
Windows Firewall Warning – You can see a list of Firewall ports used by the SQL server in this article on Technet.
Just make sure to at least open port 1433, otherwise you will not be able to make remote connections to the SQL Server.
2.8 Next up is the Feature Selection Screen. Choose Database Engine Services, Management Tools- Basic and Management Tools – Complete. You can also install SQL Server Books Online if you like, those are the help files for SQL Server 2008 R2. On a failover cluster, you will have to install SQL Server Replication and Full-Text Search, when you install the Database Engine Services.
When you are done choosing which features you want to install, click Next. Yes, the Shared feature directory will be on a local disk of the node.
2.9 On the Instance Configuration screen, give the SQL Cluster a name. This will be the name of the Clustered SQL server, which the applications will connect to. Keep the default instance name, or name it if you like. Click Next, when done.
2.10 Click Next on the Disk Space Requirements screen.
2.11 Choose the default setting here, and a new cluster resource group named SQL Server (MSSQLSERVER) will be created in the Failover Cluster.
2.12 Choose the three disks that were added to the Failover Cluster as Available Storage at point 1.1
2.13 Assign the clustered SQL Server Instance an appropriate IP Address.
2.14 Just keep the recommended option, and click Next
2.15 You can choose to have different service accounts for the different SQL services, or you can choose to use the same account for all SQL Server services. I’m choosing the latter. In the Collation tab, select the appropriate collation to be used by SQL Server. Finally click Next.
2.16 Specify which authentication mode you would like for the Database Engine, then specify SQL server administrators. As you can see I am choosing Windows authentication mode, and I am choosing to specify a group. Which means that I can just add members to that Active Directory group, whenever I need to give someone administrative access to the Database Engine on this SQL Server installation.
2.17 Click on the Data Directories Tab, and choose where to install the different files. As you can see, I’m installing Database files on G:\, TempDB files on H:\, and all log files on I:\. These three volumes reside on the three disks that were added on point 2.12. Click Next on the Database Engine Configuration screen.
2.18 On the Error Reporting screen, make your choice, then click Next.
2.19 Cluster Installation Rules are ran, click Next.
2.20 Click Install.
2.21 Click Close when the installation completes.
3. Install SQL Server 2008 R2 on the second node of the Failover Cluster
3.1 Start setup.exe on the second node of the Failover Cluster
3.2 Click on Installation, and then choose Add node to a SQL Server failover cluster
3.3 Setup Support Rules are ran, confirm that each one is passed, and then click OK.
3.4 Enter the Product key, click Next
3.5 Accept the license terms, then click Next again
3.6 Install the Setup Support files
3.7 Setup Support Rules are ran next, and they might reveal warnings or errors. Review and correct those, if possible. Keep in mind that you can not correct the Network Binding Order warning.
3.8 The correct settings should be automatically selected for you on the Cluster Node Configuration screen. Just validate they are correct, and Click Next
3.9 Type in the password for the designated Service Account (which we specified on point 2.15), then click Next
3.10 Make your selection on the Error Reporting screen, Click Next
3.11 Add Node Rules are run, click next.
3.12 Click Install on the Ready to Add Node screen.
3.13 When the installation completes, click Close.
4. Validate the SQL Server 2008 R2 Failover Cluster
4.1 Start Failover Cluster Manager
4.2 Expand the Services and Applications node, and check the SQL Server (MSSQLSERVER) cluster resource group
4.3 If you right-click SQL Server (MSSQLSERVER), you will have the option to move it to the second node. Feel free to move it back and forth between the two nodes, to verify the failover process.
4.4 On either node, start SQL Server Management Studio, and connect to the clustered SQL Server. Notice the name of the SQL server
5. Installing Patches on the nodes of the Failover Cluster
5.1 Install the patch or Service Pack to the passive node first, then move the cluster resources to this node so it will become active. Then install patch or Service Pack on the node which has now become passive.
5.2 So go ahead and install SP3, its pretty much a next -> next -> finish session.
MSDN: Installing a SQL Server 2008 R2 Failover Cluster