Despite being an older version, some applications may require you to install a SQL Server 2008 R2 clustered instance on a Windows 2012 environment. You will quickly find out that there are a number of compatibility issues that can make this task tricky. See below for a summary of the issues you can encounter and how to work around them in order to successfully install SQL Server.
Cluster service verification rule failed
The first issue you may run into is during the Setup Support Rules step of the SQL installation wizard. The Cluster Service Verification Rule fails, saying that the SQL Server failover cluster service is not online or cannot be accessed:
Solution: Install the Failover Cluster Automation Server feature on all cluster nodes.
- Open Server Manager
- Click on Manage -> Add Roles and Features
- In the “Features” page of the wizard, select Failover Cluster automation Server under Remote Server Administration Tools -> Feature Administration Tools -> Failover Clustering Tools
- Click Next and Install
- Repeat for every node in the cluster
- Rerun the SQL Installation Wizard
Missing option in cluster security policy
The next issue is on the Cluster Security Policy page of the SQL Installation Wizard. When running the wizard on Windows 2008, you will have an option to either “Use Service SIDs” or “Use Domain Group”. The recommended option for Windows 2008+ is the service SIDs. However, when running on Windows 2012, it does not give you this option and just asks you to enter a domain group for the Database Engine and SQL Server Agent.
Solution: This is a bug. The simple fix is to leave it blank and just click Next. When the Domain Group button is left empty, it will use Service SIDs by default.
Windows Server 2003 FILESTREAM Hotfix check rule failed
You reach the end of the setup wizard and get to the Cluster Installation Rules page, but the “Windows Server 2003 FILESTREAM Hotfix Check” rule fails even if you do not wish to install Filestream. The error says: “Windows Server 2003 hotfix KB937444 is not installed. This hotfix is required for FILESTREAM to work on a Windows Server 2003-based cluster”; however, the mentioned hotfix is not valid for Windows 2012.
Solution: This is also a bug. The solution is to slipstream service pack 1:
- Download all 3 SQL Server 2008 R2 SP1 packages from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=26727.
- Copy the original SQL Server 2008 R2 installer files to a new folder. In this example, we will use C:\SQL2008R2_SP1.
- Open a CMD window and run the below commands to extract the SP1 packages to C:\SQL2008R2_SP1\SP:
- Copy the Setup.exe from C:\SQL2008R2_SP1\SP to C:\SQL2008R2_SP1, replacing the original file.
- For each architecture type (IA64, x64 and x86), copy all files (excluding the folders and Microsoft.SQL.Chainer.PackageData.dll) from C:\SQL2008R2_SP1\SP\ to C:\SQL2008R2_SP1\. You can use the below robocopy commands:
robocopy C:\SQL2008R2_SP1\SP\x86 C:\SQL2008R2_SP1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll
robocopy C:\SQL2008R2_SP1\SP\x64 C:\SQL2008R2_SP1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll
robocopy C:\SQL2008R2_SP1\SP\ia64 C:\SQL2008R2_SP1\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll
- See if you have a DefaultSetup.INI file under these three locations:
If the file exists, edit each file and add the following line at the end:
If it doesn’t exist, create it under the three locations with the following content:
;SQLSERVER2008 R2 Configuration File [SQLSERVER2008] PCUSOURCE=".\SP"
- Run setup.exe from C:\SQL2008R2_SP1. The wizard should open as usual. To confirm that you are slipstreaming, in the installation rules you should notice “Update Setup Media Language Rule”. Proceed with the install as normal.
SQL Instance does not failover properly
This next issue only occurs if you have another SQL Server 2012 or later failover cluster instance (or availability groups) on the same server as the SQL 2008 R2 cluster instance. You finally managed to install the SQL 2008 R2 instance and added the other cluster nodes. You test failing over the instance to one of the passive nodes, but you may notice issues such as the SQL instance does not failover to the second node or SQL Agent resource does not come online and the SQL Server resource eventually goes in a failed state. In addition, the following messages can be found in the cluster logs:
Res SQL Server Agent : WaitingToComeOnline -> OfflineDueToProvider( StateUnknown ) 000026d4.00002244::2018/03/15-15:55:39.419 INFO [RCM] TransitionToState(SQL Server Agent ) WaitingToComeOnline-->OfflineDueToProvider 0000273c.00002cf8::2018/03/15-15:55:39.419 ERR [RHS] RhsCall::DeadlockMonitor: Call ONLINERESOURCE timed out by 16 milliseconds for resource 'SQL Server'. 0000273c.00002cf8::2018/03/15-15:55:39.419 ERR [RHS] Resource SQL Server handling deadlock. Cleaning current operation. 0000273c.0000439c::2018/03/15-15:55:25.934 INFO [RES] SQL Server : [sqsrvres] Run 'EXEC sp_server_diagnostics 20' returns following information 0000273c.0000439c::2018/03/15-15:55:25.934 ERR [RES] SQL Server : [sqsrvres] ODBC Error:  [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'sp_server_diagnostics'. (2812) 0000273c.0000439c::2018/03/15-15:55:25.934 ERR [RES] SQL Server : [sqsrvres] Failed to run diagnostics command. See previous log for error message 0000273c.0000439c::2018/03/15-15:55:25.934 INFO [RES] SQL Server : [sqsrvres] Disconnect from SQL Server
Solution: Either stop and restart the cluster service on each node in the cluster or restart the nodes. After the restart, failover should behave normally. Follow this link for more details on the cause of the issue: https://support.microsoft.com/en-ca/help/2938136/could-not-find-stored-procedure-sp-server-diagnostics-error-and-the-in