BizTalk High Availability Server Environment

BizTalk High Availability Server Environment – SQL Server 2008r2 Failover Cluster

This post will assume that you’ve followed all steps as mentioned in Part 1 through 4. Well let’s get started with installing and configuring our SQL Server 2008 R2 Cluster.

Installing SQL Server 2008 R2

One of the most crucial parts when installing SQL Server in combination with BizTalk, is to ensure that you’ve made the proper firewall configurations and at least configured the local Microsoft Distributed Transaction Coordinator.

Configuring the Firewall

In our lab environment I’ve simply turned off the Firewall for the following profiles

• Domain Profile
• Private Profile

In order to do so, startup tour first basic SQL Server instance, log on to the domain, open up the windows firewall, by going to Start and in the search box simple type: ‘Windows Firewall with Advanced Security’ followed by hitting ‘enter’

Within the MMC-Snap in, click on ‘Windows Firewall Properties’

A window will appear. Go to the first tab named ‘Domain Profile’ and set the firewall state to ‘Off’ and click on apply

Go to the second tab named ‘Private Profile’ and set the firewall state to ‘Off’ and click on apply and then ok

Close your Firewall MMC snap-in.

Configuring the (local) Microsoft Distributed Transaction Coordinator

go to start and type into the search box ‘Component Services’ and hit enter.

The Component Services MMC snap in will open; now extend the ‘Component Service’ node, do the same for the node ‘Computers’ and ‘My Computer’

Expand the ‘Distributed Transaction Coordinator’, right click on ‘local DTC’ and select ‘properties’

Within the Properties window go the the ‘Security Tab’

On this tab, check (enable) the following item:

• Network DTC Access
• Allow Inbound
• Allow Outbound
• No Authentication Required
• Enable XA Transactions
• Enable SNA LU 6.2 Transactions

Click on ‘Ok, a message box willsappear stating that the MSDTC service needs to be stopped and started. Click on Yes

Close the Component Services Snap in. And repeat the above mentioned steps on the following other servers

• Second SQL Server (SQL002)
• First BizTalk Server (BTS001)
• Second BizTalk Server (BTS002)

Creating your SQL Server Cluster

Before we start with installing SQL Server we will have to actually create our SQL Cluster. In order to do this logon to one of your servers which you want to be part of your SQL Cluster. In my particular case this is SQL001

Go to start and in the search box type ‘Failover Cluster Manager’ and then hit ‘enter’

In your Failover Cluster Manager, first click on ‘Validate a configuration’

On the ‘Before you begin’ screen, press ‘next’

Now Enter the server names (or browse) which you want to be part of your cluster. In my case that would be ‘SQL001 and SQL002’ and then select ‘Next’

on the ‘Testing Options’ screen, select the ‘Run all tests’ option and select ‘next’

Confirm the settings and then select ‘Next’

Once the validation process has finished you will notice a few warning relating to the storage. Ignore these warnings for now as we will take care of these one we’ve created our Cluster. Once you’ve examend the report (View Report) click on Finish

From within your ‘Failover Cluster Manager’ select the ‘Create a Cluster’ link

On the ‘Before you begin’ screen, press ‘next’

Now Enter the server names (or browse) which you want to be part of your cluster. In my case that would be ‘SQL001 and SQL002’ and then select ‘Next’

On the ‘Access Point for Administering the Cluster’ enter a Cluster name, and a designated IP Address and click ‘next’ once done.

Confirm your settings and then click ‘next’

On the ‘Summary’ screen, press ‘Finish’ (note the warnings, but no worries as we will address them in a bit)

Addressing the Storage issue on your SQL Cluster

In order to finish prepping our SQL Cluster, we need to address two issues which were mentioned in the previous step. The issue we need to address is:

• Assigning Storage

Verify you’re connected with the File Server

First verify that we’ve set-up our link with our Fileserver. Do this by clicking on Start and in the search box type ‘iSCSI Initiator’ and hit ‘enter’ (note: perform these steps on your main SQL node (in my case SQL001)

Ensure that you are connected to your ‘Target’, by clicking on the ‘Targets’ tab and checking the statuses

Repeat the above mentioned steps for your other SQL node (in my case SQL002)

Assigning Storage to your Servers

Go back to the main SQL Server Node, open the ‘Server Manager’, expand the ‘Storage’ node and select ‘Disk Management’

At this point you should notice several disks which are not initialized.

Right Click on Disk 1 and select ‘Initialize

The ‘Initialize Disk’ screen will appear, and enables you to initialize the other disks as well. Make sure to check all disks, and use the MBR partition option. Once done, click ‘Ok’

Now right click in the area next to Disk 1 and select ‘Simple Volume’

The ‘New Simple Volume Wizard’ will pop up; click ‘next’

On the ‘Specify Volume Size’ click ‘next’

On the ‘Assign a drive letter or path’ screen; assign a drive letter and click ‘next’

On the ‘Format Partition’ screen; leave the Default Values intact with exception of the ‘Volume Label’ for this enter a name (fe; Disk1) and press ‘next’

Finish the wizard by clicking ‘Finish’

Repeat the ‘new Simple Volume’ steps for all other Disks which are ‘Unallocated’

Once done; your disk management screen should look similar like to this

Open your iSCSI initiator once again, do this by clicking on Start and in the search box type ‘iSCSI Initiator’ and hit ‘enter’ Once in the iSCSI Initiator properties screen pops up, go to the ‘Volume and Devices Tab’ and click on the ‘auto configure’ button.

Your Volume List should now be populated with the disks you assigned earlier. Once done click ‘OK’

At this point, go to your second SQL Server node (in my case SQL002). Open your iSCSI initiator, do this by clicking on Start and in the search box type ‘iSCSI Initiator’ and hit ‘enter’ Once in the iSCSI Initiator properties screen pops up, go to the ‘Targets’ tab and verify that your connected. If not; hit ‘Refresh’ and then Connect to the target.

Now go to the ‘Volume and Devices Tab’ and verify that the Volume List is populated.

Add the assigned storage as a disk resource in your Cluster

Go back to the main SQL Server Node (in my case SQL001)  open up the ‘Cluster Manager’, expend the CLUSTER_SQL node and right click on Storage and select ‘Add a disk’

A list of available disks will appear, ensure that they are all selected and press ‘ok’

Installing SQL Server on your Cluster

The steps mentioned below, need to be executed on all your Servers which will be part of your SQL Server Cluster

Make sure you’ve mounted the SQL Server 2008r2 ISO file, this image can be downloaded form MSDN if you have a subscription.

Once you’ve mounted SQL Server 2008R2

open up windows explorer and browse to the mounted Drive (in my case drive D) and double click ‘Setup’

You will be prompted with a message indicating that the .NET Framework is required and an updated version of the windows installer. Click on ‘Ok’

After a while the ‘SQL Server Installation Center’ will pop up. Click on the ‘Advanced Link’.

Select ‘Advanced cluster preparation’

The window ‘Setup Support Rules’ will appear. Wait till it finished, ensure that there are no warnings and then click on ‘Ok’

After a wile a window will pop up in which you will be asked for the product key. In case no product key is filled out, enter your product key and then press ‘next’

Accept the license terms and press ‘next’

Install the setup support files, by clicking on ‘Install’

The support files will no be installed, and once done check the warnings, if everything went well you should only see one warning; being the Windows Firewall warning. This warning can be ignored and click ‘Next’

Note: if after the binding order changes you still receive the same error; just skip and proceed with the installation as this error is most likely at this point showing up due to the fact that the ‘Failover Feature’ installs a virtual NIC .

You will be presented with the Feature Selection screen, for sake of simplicity we will check all options and thus do a Feature Complete installation Once everything has been selected, click on ‘Next’

The next screen will be the ‘Instance Configuration’ screen, ensure to check the option ‘Named Instance’ and give it the following name ‘BizTalk2010’. Once done press ‘next’

On the ‘Disk Space Requirements’ screen, press ‘next’

On the ‘Cluster Security Policy’ screen, select ‘Use service Sids’ and press ‘next’

On the Server Configuration screen; select the ‘Service Accounts’ tab and set the required Accounts and Passwords to the corresponding service

SQL Service Agent: LABsrvc-sql-agent
SQL Server Database Engine: LABsrvc-sql-engine
SQL Server Analysis Services: LABsrvc-sql-analysis
SQL Server Reporting Services: LABsrvc-sql-reporting

On the Server Configuration screen; select the ‘File Stream’ tab and ensure that the option ‘Enable FILESTREAM’ for Transact-SQL access is disabled as we will not use this feature. Press ‘Next’

On the ‘Reporting Services Configuration’ screen, select ‘Install, but do not configure the report server’ and click ‘Next’

On the ‘Error Reporting’ screen, press next

Ensure that no warning appear on the ‘prepare failover cluster rules’ screen and press next

Verify the features and select ‘Install’ (Please note: This can take a while)

Once the installation is complete, press the ‘close’ button and repeat the above mentioned steps for your other sql server.

SQL Server 2008r2 Cluster completion

Ensure to logon to your SQL Primary Node server, in my case that is the SQL001 server.

Before we start with the ‘Cluster Completion’ installation we will verify the following:

• SQL Server Configuration

Verify SQL Server Configuration

Open up the SQL Server Configuration Manager( Start –> All Programs –> Microsoft SQL Server 2008 R2 –> Configuration Tools)

Open the SQL Server Network Configuration en select ‘Protocols for BIZTALK2010’

Ensure that the following items are enabled

• Named Pipes
• TCP/IP

Ensure that the following items are disabled

• Shared Memory
• VIA

Proceed with the Cluster Completion Installation

Open up the SQL Server Installation Center ( Start –> All Programs –> Microsoft SQL Server 2008 R2 –> Configuration Tools)

Click on the Advanced link, and select the option ‘Advanced Cluster Completion’

On the ‘Setup Support Rules’ screen, click on ‘Ok’

On the ‘Setup Support Files’ click on install

On the ‘Setup Support Rules’ check for any warnings and click on ‘Next’.

You might see one warning, this warning relates to the Cluster Validation. You can ignore this warning as it mentions a storage issue, but we’ve tackled this issue earlier

On the ‘Cluster Node Configuration’. Select the correct SQL Server instance name and assign a SQL Server Network Name and press Next.

On the ‘Cluster Resource Group’ Screen, click Next

On the ‘Cluster Disk Selection’ select the storage intended for your database, and select ‘next’.

In my case I assigned 2Gb for the SQL Data; in order to backtrack which Disk Resource to use; check the sizes of the disks in the Failover Cluster Manager(in my case this would be Disk 2)

On the ‘Cluster Network Configuration’ Screen, ensure to uncheck ‘DHCP’ and assign a static IP address. In case you have multiple Networks, make sure to only fill out the details for the internal network (in my case I disabled Cluster Network 2)

I’ve used the following:
IP Address: 192.168.8.23

On the ‘Server Configuration’ screen, click ‘next’.

On the ‘Database Engine Configuration’ screen,

• select Mixed Mode and enter a password.
• Click on ‘Add Current User’

Click on the ‘Data Directories’ tab, verify the settings and press ‘next’

On the ‘Analysis Service Configuration’ Screen, click on the ‘Add Current User’

Click on the ‘Data Directories’ tab, verify the settings and press ‘next’

Click Next on the ‘Complete Failover Cluster Rules’ Screen.

Check the summary screen and press ‘install’

Click ‘Close’

Finalizing your SQL Server 2008r2 Cluster

Congratulations we’ve now have a SQL Cluster, however we need to verify a few things and manually add and change some resources. But all of this is explained below.

Verify the IP settings

In case you have 2 NICS available to the server, verify you assigned the correct NIC. If not you can skip the following steps.

Open the ‘Failover Cluster Manager’ and select the ‘SQL Server (BizTalk2010)’ node.

In case you have 2 nicks available to the server, verify you assigned the correct NIC, do this by expanding the Name node

Right Click on ‘IP Address’ and select properties

Verify that the Network settings are correct.

In my case I know I need to have 192.168.8.0/24 as 192.168.8.x is used for my internal network and 192.168.1.x is used for my external network (internet access)

Add additional Storage to the Cluster instance

Open the ‘Failover Cluster Manager’ and select the ‘SQL Server (BizTalk2010)’ node.

Right Click on ‘SQL Server (BizTalk2010’) node, select ‘Add storage’

Check the available disks and press ‘Ok’

The Storage has been added.

[Optional] Rename the Disk Drives

For readability I’ve renamed the Disk Drives, in order to rename a disk; richt-click on it and select properties

Change the Resource Name, and press ‘ok’

Repeat these steps for all disks. Eventually you could have a result similar to this.

Add a Clustered Distributed Transaction Coordinator

Open the ‘Failover Cluster Manager’ and select the ‘SQL Server (BizTalk2010)’ node.

Right Click on ‘SQL Server (BizTalk2010’) node, select ‘Add a resource’-> ‘More Resources’ –> ‘2 – Add Distributed Transaction Coordinator’

You will notice that a ‘MSDTC-SQL Server (BIZTALK2010) resource has been added.

Right Click on this resource, and select ‘properties’

Go to the ‘Dependencies’ tab, and add the following dependencies:

o    Name

• IP Address

o    Storage   (I’ve used the SQL DTC Store)

Once done click ‘OK’ and bring the MSDTC resource online, by right-clicking on it and selecting ‘Bring this resource online’

Go to Start and in the search box type ‘Component Services’ and hit ‘enter’

The ‘Component Services’ screen will appear, now expand ‘Component Services’ –> ‘Computers’ –> ‘My Computer’ –> ‘Distributed Transaction Coordinator’ –> ‘Clustered DTCs’ right click on ‘SQL 2008’ and select ‘properties.

On the ‘SQL 2008’ properties screen, select the ‘Security’ Tab

Enable the following options:

• Network DTC Access
• Allow Remote Clients
• Allow Remote Administration
• Allow Inbound
• Allow Outbound
• No Authentication Required
• Enable XA Transactions
• Enable SNA LU 6.2 Transactions

Once done click ‘Ok’. A message will appear asking to stop/start to DTC service. Click ‘Yes’. Once done Close the Component Services screen and return to your ‘Cluster Manager’

Obtaining Quorum on your Cluster

On the ‘Cluster Manager’ screen, select your main Cluster_SQL node and notice the warning with regards to the Quorum Configuration.

In order to fix this; right click on ‘Cluster_SQL’ and select ‘More Actions’ –> ‘Configure Cluster Quorum Settings’

On the ‘Before You Begin’ screen, click ‘next’

On the ‘Select Quorum Configuration’ screen, select ‘Node and File Share Majority’ (You could use Node and Disk Majority, but then you would have to create additional storage on your FileServer and configure your iSCSI target accordingly). Click ‘next’

On the ‘Configure File Share Witness’  browse to an available Shared Folder Path.

If you’ve not created a share at this point. Go to your FileServer, Create a folder and Share this Folder (http://technet.microsoft.com/en-us/library/cc770880.aspx#BKMK_interface)

On the ‘Browse for Shared Folders’ screen, enter your FileServer name; in my case ‘EUROPOORT’ and click on the ‘Show Shared Folders’ button. Select the share you would like to use; in my case the share is called ‘Majority_SQL’ and press ‘Ok’

Click Next

Confirm the settings and click on ‘Next’

Click Finish

Verifying your Cluster and doing a manual Failover.

At this point you’ve setup your SQL Server Cluster. Congratulations! No you might be wondering at this point of you need to perform the same actions on your second SQL node (in my case SQL002), well actually this has already auto magically been done for you.

So in order to verify this, go to your second SQL Server Node and open the Failover Cluster Manager and expand your ‘Cluster Node’, expand ‘Services and Applications’ and select the ‘SQL Server (BizTalk2010)’ node.

Notice that the Current Owner is: SQL001 and that all resources are online

If you look closely you see, that we haven’t assigned a Preferred

In order to assign a preferred owner, right click on SQL Server (BizTalk2010) and select Properties

On the ‘Properties’ screen, set the Preferred owners to ‘SQL001’ and click ‘Ok’

Well now we are up running! However let’s go and test if a failover works. In order to test this, right click on SQL Server (BizTalk2010) and select ‘Move this service or application to another node’ –> ‘1-Move to node SQL002’

A confirmation message wills popup. Select ‘Move SQL Server (BIZTALK2010) to SQL002.

Observe that changes to your resources

Once it is done, you will see that all resources are back online, and that the current owner is SQL002

Voila! Now you’re done!