Skip to content

Insight and analysis of technology and business strategy

Installing SQL Server 2008 R2 on Windows 2012 cluster

Alexandre Hamel date icon
Tags: , , , ,
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: Cluster Service Verification Rule Failed   Solution: Install the Failover Cluster Automation Server feature on all cluster nodes.
  1. Open Server Manager
  2. Click on Manage -> Add Roles and Features
  3. In the “Features” page of the wizard, select Failover Cluster automation Server under Remote Server Administration Tools -> Feature Administration Tools -> Failover Clustering Tools Install cluster feature
  4. Click Next and Install
  5. Repeat for every node in the cluster
  6. 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. Cluster Security Policy 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. Filestream hotfix rule Solution: This is also a bug. The solution is to slipstream service pack 1:
  1. Download all 3 SQL Server 2008 R2 SP1 packages from https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=26727.
    SP1 Download
  2. Copy the original SQL Server 2008 R2 installer files to a new folder. In this example, we will use C:\SQL2008R2_SP1. Filestream root folder
  3. Open a CMD window and run the below commands to extract the SP1 packages to C:\SQL2008R2_SP1\SP:
    SQLServer2008R2SP1-KB2528583-IA64-ENU.exe /x:C:\SQL2008R2_SP1\SP SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x:C:\SQL2008R2_SP1\SP SQLServer2008R2SP1-KB2528583-x86-ENU.exe /x:C:\SQL2008R2_SP1\SP
  4. Copy the Setup.exe from C:\SQL2008R2_SP1\SP to C:\SQL2008R2_SP1, replacing the original file. Filestream - Copy setup.exe
  5. 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
     
  6. See if you have a DefaultSetup.INI file under these three locations:
    C:\SQL2008R2_SP1\x86 C:\SQL2008R2_SP1\x64 C:\SQL2008R2_SP1\ia64
    If the file exists, edit each file and add the following line at the end: 
    PCUSOURCE=".\SP"
    If it doesn't exist, create it under the three locations with the following content: 
    ;SQLSERVER2008 R2 Configuration File
 [SQLSERVER2008]
 PCUSOURCE=".\SP"
  7. 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: [42000] [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  
Alexandre Hamel

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!
dba-cloud-services
Upcoming-Events-banner