Part 5: BizTalk High Availability Server Environment – SQL Server 2008r2 Failover Cluster

In Part 4 we’ve prepped our SQL & BizTalk Servers so that they can be used as a basis for setting up our actual Failover Clusters.

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 Smile

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 of 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’

image

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

image

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

image

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

image

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.

image

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

image

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

image

Within the Properties window go the the ‘Security Tab’

image

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

 

image

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

image

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’

image

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

image

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

image

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’

image

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

image

Confirm the settings and then select ‘Next’

image

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

image

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

image

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

image

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’

image

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

I’ve used the following:

Cluster Name: CLUSTER_SQL
IP Address: 192.168.8.22

image

Confirm your settings and then click ‘next’

image

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

image

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 your 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)

image

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

image

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’

image

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

image

Right Click on Disk 1 and select ‘Initialize

image

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’

image

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

image

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

image

On the ‘Specify Volume Size’ click ‘next’

image

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

<img style="background-image: none; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; padding-top: 0px; border-width: 0px;" title="image" alt="image" src="http://blog.brauwers viagra 100 mg posologie.nl/wp-content/uploads/2011/05/image_thumb32.png” width=”244″ height=”188″ border=”0″ />

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’

image

Finish the wizard by clicking ‘Finish’

image

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

image

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.

image

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

image

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.

image

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

image

 

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’

image

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

image

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’

image

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’

image

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

image

Select ‘Advanced cluster preparation’

image

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

image

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’

image

Accept the license terms and press ‘next’

image

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

image

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’

image

warnings

In case you get a warning relating to ‘Microsoft .NET Application Security’, verify that your machine has access to the Internet. Fix the issue and rerun the validation process

In case you get a warning relating to ‘Network binding order’, check out the following links

http://theregime.wordpress.com/2008/03/04/how-to-setview-the-nic-bind-order-in-windows/.

http://support.microsoft.com/kb/955963

Fix the issue and rerun the validation process (you might need to reboot first and rerun the Cluster Installation Preparation.

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 Smile Once everything has been selected, click on ‘Next’

image

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’

image

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

image

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

image

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

image

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’

image

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

image

On the ‘Error Reporting’ screen, press next

image

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

image

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

image

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

image

 

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)

image

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

image

Ensure that the following items are enabled

  • Named Pipes
  • TCP/IP

 

Ensure that the following items are disabled

  • Shared Memory
  • VIA

 

image

 

Proceed with the Cluster Completion Installation

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

image

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

image

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

image

On the ‘Setup Support Files’ click on install

image

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

image

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

image

On the ‘Cluster Resource Group’ Screen, click Next

image

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

image

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)

image

 

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

image

I’ve used the following:
IP Address: 192.168.8.23

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

image

On the ‘Database Engine Configuration’ screen,

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

 

image

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

image

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

image

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

image

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

image

Check the summary screen and press ‘install’

image

Click ‘Close’

image

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.

image

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

image

Right Click on ‘IP Address’ and select properties

image

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)

image

 

Add additional Storage to the Cluster instance

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

image

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

image

Check the available disks and press ‘Ok’

image

The Storage has been added.

image

[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

image

Change the Resource Name, and press ‘ok’

image

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

image

Add a Clustered Distributed Transaction Coordinator

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

image

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

image

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

image

Right Click on this resource, and select ‘properties’

image

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

      • Name
  • IP Address
    • Storage   (I’ve used the SQL DTC Store)

 

image

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

image

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

image

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.

image

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

image

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

 

image

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’

image

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.

image

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

image

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

image

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’

image

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

image

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’

image

Click Next

image

Confirm the settings and click on ‘Next’

image

Click Finish

image

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 Smile and expand your ‘Cluster Node’, expand ‘Services and Applications’ and select the ‘SQL Server (BizTalk2010)’ node.

image

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

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

image

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

image

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

image

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’

image

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

image

Observe that changes to your resources

image

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!

image

 

Closing Note

Well it has been another long read but this sums up part 5. We now have our SQL Cluster and we are ready to start on our BizTalk cluster but more on that in part 6.

You’ve most likely noticed that we are currently not using all disk resources. Well no worries, these resources will be used in a future post. (Most likely part 7 as we will be playing around with the BizTalk Best Practices Analyzer)

Well I hope you enjoyed the posts so far, check back soon and feel free to leave any comments, remarks and/or suggestions with regards to Blog posts you would like to see in the future.

Cheers

René

7 comments

    1. Hi Filiberto,

      restoring SQL-Server is a subject matter on it’s own; luckily there are enough resources which can help you with your issue. I’ve listed 2 below:

      with regards to BizTalk check out:

      with regards to general SQL Server restore procedures:

    1. Hi Filiberto,

      restoring SQL-Server is a subject matter on it’s own; luckily there are enough resources which can help you with your issue. I’ve listed 2 below:

      with regards to BizTalk check out:

      with regards to general SQL Server restore procedures:

  1. On my second SQL Server node, when I click Auto Configure, the drive letters done show. Am I doing something wrong? Do I need to bring these drives Online prior to being able to see the drive letter in iSCSI Initiator?

Leave a Reply

Your email address will not be published. Required fields are marked *

WordPress spam blocked by CleanTalk.