Puttyq.com

In my previous article “Building a SQL 2012 Always-On Cluster – Part 1” we examined the deployment of a SQL Server 2012 by deploying the first cluster node. The idea was to explore both the process of SQL Server 2012 clustering for a failover cluster, but also to see how different the installation process might be from Windows Server 2008 R2. In the initial deployment the process did not seem much different at all, although there are many other differences in the product features themselves.

In this article we will explore the process of adding a second node to the cluster that was already deployed.

As with the deployment of the first node, please ensure that all your rre-requisites are met in relattion to the node setup, software versions and hardware configuration.

On your second node, execute the SQL Server installation and process the Setup Support Rules and validation. (As mentioned before, please ensure that you have validated the Windows Fail-over Cluster).

Step 1: Complete the SQL Server validation and remediate any errors.

Step 2: From the listed provided, select the SQL Server cluster you wish to add a node to.

Step 3: On the selected cluster, select the SQL Server virutal IP address that should be used for this node.

The rest of the installation will validate the node against the required SQL Server features selected and complete the installation. (Simple right).

Related articles:

• SQL Server AlwaysOn Design Principles
• Microsoft SQL Server 2012: AlwaysOn Availability Groups Feature
• High Availability | SQL Server AlwaysOn | SQL Server 2012

Over the past few weeks I have been involved in a major SQL cluster roll-out and as such got my hands dirty with getting SQL 2008 R2 clusters built. With the release of SQL Server 2012 I decided I might just was well contrast the two builds for the fun of it. As it turns out the builds are very similar in the way the deployment is executed. I still thought it would be of value to go through the process step-by-step below. 

Before you begin:

Prior to starting the SQL Server 2012 setup there are various requirements than needs to be in place. For the purpose of this article I am assuming that most of these have been covered including:

• Server hardware (incl. HBA’s) patching and firmware updates
• Operating system and patching
• Network patching for both external and heartbeat networks
• .Net Framework and Failover Clustering features added
• Cluster validation is done (and passed of course)

As with SQL Server 2008 the MSDTC service is still a required component prior to starting the SQL Server installation itself. If you fail to install MSDTC on both nodes you will be presented with the error message as shown below.

Let’s start the SQL installation.

Step 1: Start the SQL Server 2012 installation and select “Installation” – “New SQL Server failover cluster”

Step 2: Verify that all requirements are met. In my example I still have two errors (one relating to the server not having internet access and the second due to the way my virtual machine network is confirmed. In production please ensure that your binding order is set for the external network to have the highest priority.

Step 3: Accept the SQL Server 2012 terms and conditions. You can choose to send feature data to Microsoft to improve the product in future.

Step 4: To install SQL components choose the “SQL Server Feature Installation” option. If you wish to play or deploy PowerPivot the new data presentation tool choose the “SQL Server PowerPivot for SharePoint” option. If you have not seen PowerPivot I recommend you try it out since it is an amazing way to represent large tabular datasets in a very easy to navigate (and pretty) format.

Step 5: On the feature selection screen choose your components to install. Also decide where to place your shared Microsoft SQL Server installation files. Note that these file should be on the operating system or node specific drive. The shared files for each of the various instances of SQL that will be installed will be placed with the respective drive resources later in the installation.

Step 6: The feature rule validation will validate your feature selection against the node configuration.

Step 7: Next you need to select you instance configuration. In my example above I am only installing a single instance of SQL Server, but in production your requirements might be different. I would suggest that you create a named instance of SQL Server that allows you to create additional multiple instances (named) on the same cluster in the future. (Just to be clear, you will be able to create another instance at any time, but then you will have default and named instances on the same cluster).

Step 8: Review your disk space requirements for your shared configuration.

Step 9: Provide the name for the cluster group that will be created by the installation within “Fail-over Cluster Manager”.

Step 10: Select the disk resources that will be used within this cluster group to deploy the SQL features. In the case where you are deploying mulitple instances of SQL, try to ensure that you create descriptive names for the disk resources when you create and assign then in Windows Storage configuration. This will make it easier to find during this step. In my example, I am only deploying a single instances (with DB features as the primary goal), thus I only created 3 disk resources.

Step 11: Configure you network resource for the cluster group. Ensure that you remove the DHCP tick box and specify an VIP on the correct subnet for you SQL Server instance name.

Step 12: Provide the required domain service accounts for the various SQL Server features. Something new I did notice is that when doing a single server local installation of SQL Server 2012, there is a set of new built-in accounts that can be used to deploy SQL Server (e.g. NTService\MSSQLServer, etc.). These are not available due to the requirement that a shared domain account has access to both the nodes while in a cluster configuration.

Step 13: Ensure your instance has the correct Collation type. Note that products like System Center 2012 require different collation types for components such as OpsMgr.

Step 14: Ensure that you authentication to the new cluster is enabled with the required auth mode as well as by specifying an administrator account(s) to be added to the SQL Server during installation.

Step 15: In the “Database Engine Configuration” -> “Data Directories” tab you need to specify the various data directories that will be used for the specific instance of SQL Server. You need to specify the “Data Root” which will contain the shared files for the instance. Additionally you have the option to specify where to place all new (and installation created) databases such as MDF, LDF, TempDB and Backup files.

Step 16: (Optional based on components). If you are installing Analysis Services you need to decide what data model the server will be using by selecting the “Server Mode”. Options include “Multidimensional and Data Mining Mode” or Tabular Mode”. If you want to play with the new PowerPivot features in SQL Server you need to select “Tabular Mode”. Additionally you need to specify the account(s) that will be granted administrative permissions to the Analysis Services features as part of the installation.

Step 17: As with the database features, the Analysis Features can be installed on their own set of disk resources. (In my example I have not created separate LUN’s for this so I will be using the existing resources. In production separate resources should be created, presented and assigned in this step).

Following this you will be presented with the final few cluster validation steps and configuration review. The cluster should start the installation process and complete the installation of the first node.

In Building a SQL 2012 Always-On Cluster – Part 2 of the article I provide an illustration of the process of adding a second node to the cluster.

Related articles:

• SQL Server AlwaysOn Design Principles
• Microsoft SQL Server 2012: AlwaysOn Availability Groups Feature
• High Availability | SQL Server AlwaysOn | SQL Server 2012

When using any SQL Server 2008 or later version there is a possibility of getting the following error:

Element not found. (Exception from HRESULT: 0×80070490) – SQL Server 2008 R2

This appears right after feature selection (with the installer failing – or the installer hangs). There are no specific logs that provide adequate information as to the cause. After a bit of pain I cluster validator re-test revealed that there was a ‘stale’ disk in the available storage (inside Cluster Manager). Searching the web of related instances the error was confirmed.

The error is caused if there is offline and removed disks inside the cluster (that are no longer presented to the server nodes). Removing the disks and re-running the installer fixed the problem.

(In my case the error was caused to the removal of a LUN that was no longer required – without deleting the disk from available storage)

This morning when applying SQL Server 2008 R2 SP1 i came across the following error during the installation “Check File In Use” page:

The ‘WmiPrvSE.exe’ process is not controlled by the update wizard. You have to manually stop this process to avoid a computer restart.

There where two theads being identified by the SQL SP1 installer, but in Task Manager – Processes there where 4 running process instances. Killing these processes manually just made them spawn again.

The “WmiPrvSE.exe” process is the provider created to launch multiple instances of WMI and if stopped each new request to the service will launch as new thread. The only way to get around this issue without restarting the server after installation is to stop the WMI service itself during the installation process.

To do this:

1. Navigate to Start - Admistrative Tools – Services (or Start - Run - services.msc)
2. Locate the Windows Management Instrumentation (Winmgmt) service – right click and select Stop.

Running a “Recheck Files” in the SQL Server SP1 installer should detect no running instances of WmiPrvSE.exe. Once you have deployed the service pack, simply restart the service.

One of the primary requirements for a proper Microsoft SQL Server environment is MSDTC (Microsoft Distributed Transaction Coordinator). When installing a SQL Server clustered environment it is required to add this clustered resource before installing you SQL Server instances.

To procedure to do this:

1. To start the Failover Cluster Management program, click Start, Programs, Administrative Tools, and then click Failover Cluster Management.
2. In the left hand pane, right-click Failover Cluster Management, and then click Manage a Cluster.
3. In the Select a cluster to manage dialog box, enter the cluster to be managed, and then click OK.
4. To start the High Availability Wizard, in the left pane click to expand the cluster, right-click Services and Applications, and then click Configure a Service or Application.
5. If the Before You Begin page of the High Availability Wizard is displayed, click Next.
6. On the Select Service or Application page, click Distributed Transaction Coordinator, and then click Next.
7. On the Client Access Point page, enter a value for Name, enter an available IP address under Address, and then click Next.
8. On the Select Storage page, click to select a disk resource and then click Next.
9. On the Confirmation page, click Next.
10. On the Summary page, click Finish.

Source: How to Cluster MSDTC (MSDN)

When installing SQL Server 2008 R on a Windows Server 2008 R2 cluster I recently received the following message when trying to add the MSDTC storage volume. (Step 8).

I found quite a few references to people with similar issues, thus I thought I would post this simple fix. The solution is listed in the text below the message. To resolve this blocker, you simply need to assign an actual drive letter to the disk you with the use for MSDTC storage. In my case we were using extensive Windows mount points due to the number of LUN’s presented to the cluster, but in this case it seems an actual drive letter is required.

A drive letter is assigned by:

1. To start the Failover Cluster Management program, click Start, Programs, Administrative Tools, and then click Failover Cluster Management.
2. In the left hand pane, right-click Failover Cluster Management, and then click Manage a Cluster.
3. In the Select a cluster to manage dialog box, enter the cluster to be managed, and then click OK.
4. To assign a drive letter to a volume, select Storage. Right-click the volume you wish to assign a drive letter to and select Change Drive Letter.

Now the volume will appear in the MSDTC High-Availability resource Wizard.

Related articles:

I have been building large SQL clusters as part of a large Microsoft infrastructure deployment the last week adn came across the following error. I thought I would share the simple solution. When trying to add a cluster disk resource as a mount point you get an error stating “The parameter is incorrect”. Note that I have already added all the disks to the failover cluster manager as resources (and the disks where online).

It turns out to assign these disk the simple solution is just to place the disk resources into maintenance mode before trying to assign the mount points. To do this in failover cluster manager – select “Storage“. Find the disk you want to assign. Right click the disk and selec “More Actions” – > “Turn on maintnenace for this disk“.