SQL Server Integration Services 2008(SSIS) and MySQL

38 Comments

In my previous article I had created linked server, to access and query a database. However, it is just the basic step to try and bring two different RDBMS systems linked together.

In this article I will discuss about creating a SSIS package to transfer data from MS SQL Server 2008 to MySQL 5.5

Prerequisite:

___________________________________________________

SQL Server Business Intelligence Development Studio

MySQL Connector Net 5.2.7

You can download this package from http://www.mysql.com/downloads/connector/net/

Connector/Net is a fully-managed ADO.NET driver for MySQL.

MySQL Connector/ODBC 5.1 (Which is already installed, in our previous article)

http://www.mysql.com/downloads/connector/odbc/

Brief about SSIS

___________________________________________________

SSIS provides a graphical front end to design control flow data processing logic.  Once designed, these ‘packages’ are compiled into ‘.dtsx’ packages which can then be distributed and run on any machine with the SSIS tools installed.

Packages contain two main logic flows, a ‘Control Flow’ which defines a sequence of logical operations which are processed in sequence. Each step is completed before the next starts e.g.

1.  Empty out work tables in a database

2.  Populate the work tables with data

3.  Perform calculations and update the values in the work table

4.  Update OLAP cubes with the data from the work tables

5.  Run reports against the OLAP cubes.

This level of control also allows processing loops to be defined e.g.

For each file in a specified folder, read the contents of the file and write it into a specified table.

The second main logic flow is the ‘Data Flow’.  This allows for the processing of data at the record level. Data is read from a ‘Data Source’ and passes down a series of ‘Data Transformations’ to a ‘Data Destination’. These transformations can be as simple as changing the data type of fields e.g. varchar(4000) to varchar(2000) or decimal(18,2) to decimal(8,2), or can be more complex like data merges, joins, pivot tables, multicasts etc. Each transformation is represented by an icon in the designer and the icons are linked together to define the logic path.

Creating SSIS package to transfer data from MS SQL Server 2008 to MySQL

___________________________________________________

Follow the below steps to create a SSIS package for data transfer.

Go to START>MS SQL SERVER 2008>SQL SERVER BUSINESS INTELLIGENCE DEVELOPMENT STUDIO and Click on File>New>Project

Under Business Intelligence Projects, select Integration Services Project and rename the Project Name.

This will open a Package Designer Screen, where you can start designing the data flow for your package.

Configure Connection Managers for you package

___________________________________________________

Right Click in the area where it shows the Connection Manager tab and select New ADO.Net Connection.

You will see the Configuration Manager Editor window

Click New

Now you need to define connection manager configuration for source (i.e. MS SQL Server 2008) in our case.

In the Connection Manager Editor, by default the Provider is set to .Net Providers\SqlClient Data Provider

Use your test server name or IP for Server Name and under connect to database, select the database to use. I’m using ssistest.

Similarly, you need to create connection manager configuration for Destination (i.e. MySQL)

Right Click, under Connection Manager’s tab and Select ADO.NET Connection as mentioned above and click New on Connection Manager Editor.

For Provider click on the drop down arrow to select .Net Providers\odbc data Provider.

In the previous article we had created System DSN name MySQL. We will use the same here for Use user or system data source name.

Enter your login information and Test Connection. It should succeed, and then click Ok.

Now, on Configure ADO.NET Connection Manager screen, you can see both the source and destination are configured.

It’s now time to add control flows to the package, ensure you are on Control Flow tab and Drag Data Flow Task from the Control Flow Items under ToolBox.

Now click on the Data Flow tab above and Drag ADO NET Source and ADO NET Destination as shown below. You can rename the Source and Destination Names from Properties.

Right click on ADO NET SOURCE i.e. MS SQL Server 2008 and click EDIT. Make the below changes as mentioned on the screen.

As mentioned before I have ssistest database which has few sample tables, that I have exported from AdventureWorks database. I’m selecting one of the tables named HumanResources.Department for this example.

In the above screen you can see the test server selected with database and table to transfer the data from.

Similarly right click on MySQL (Destination) and click Edit. On this screen, under ADO.NET Connection Manager select MySQL.root from the drop downlist.

Since we do not have any destination table to map with the source, we need to click on New. It will show you the below message, click OK

Now remove the quotes and Click OK to create a table on destination as shown.

It will come back to the editor,

Click on the Mappings tab and check if all the columns are mapped and click OK.

This will complete the data flow design of our package.

Saving the SSIS Package

___________________________________________________

Once the package is created, save the copy of it to SSIS Package store or MSDB. Follow the below steps,

Click on File> Save Copy of Package.dtsx As from the menu it will open a window,

Select SSIS Package Store as the Package Location and Select the name of your test server.

Enter /File System/Export2MySQL as the package path and Click OK.

Select File > Save Copy of Package.dtsx as again from the menu and Select SQL Server as the Package Location. Select the name of your test server and fill in your authentication information.

Enter Export2MySQL as the package path and Click OK.

Once the copies are saved you can see them under Integration Services Stored Packages as shown below.

Changes on MySQL

___________________________________________________

Before running the Package, make this change on the MySQL Server

mysql> SET GLOBAL sql_mode= ‘ANSI’

Executing the Package

___________________________________________________

You can execute the package either from SSMS and BI Development Studio. I’m doing it from the BI, on the right hand side your BI screen you will find solution explorer, which has the Package.dtsx listed. Right click and select Execute Package.

The moment you click execute the debugging process start and the Output is show below on the BI screen.  If the Data Flow Task is changed to Green, it means the package has succeeded, and if it is Red it means somewhere in the flow there was an error.

On the Data Flow Tab, you can see the number of rows transferred in the execution.

There is a progress tab on the screen, where you can check the steps taken in the flow, it also list errors and warnings if any.

Query the MySQL Server to see if the data transfer was successful as show below.

This was about transferring data from MS SQL Server 2008 to MySQL, you can do vice verse by changing the source and destination while designing the package on Data Flow tab.

Hope this article is useful.

Link MySQL to MS SQL Server2008

28 Comments

I came up with Idea of this article, when I tried to create replication setup between MS SQL 2008 and MySQL. I was unable to do it since MS SQL provides only two non SQL Subscriptions/Publishers options.

However I created the linked server through which I can access and query the MySQL databases and tables.

A linked server (a virtual server) may be considered a more flexible way of achieving remote access, with the added benefits of remote table access and distributed queries. Microsoft manages the link mechanism via OLE DB technology. Specifically, an OLE DB data source points to the specific database that can be accessed using OLEDB.

Let’s try and create MySQL linked Server on MS SQL Server 2008 and query a database.

Step 1: Create ODBC DSN for MySQL

To create ODBC DSN you need to download the MySQL Connector/ODBC Drivers 5.1 from http://www.mysql.com/downloads/connector/odbc/ .

Once you download and install the ODBC drivers, it’s time to create the DSN. Initially check if the drivers are listed under your data sources from CONTROL PANEL>Administrative Tools>Data Sources(ODBC)

After you see the drivers listed. Follow the Images to setup MySQL ODBC DSN.

On System DSN tab click Add button,

After you click OK and Close the window, MySQL ODBC will be added to System DSN as shown below.

Steps 2: Create Linked Server through SSMS

Under Object Browser expand Server Objects and right click Linked Servers as shown below

Click New Linked Sever, It brings up a window; fill in the information as shown to create linked server under General tab.

In the above screen I have entered the following details to create a linked server for MySQL.

Provider: Microsoft OLE DB Provider for ODBC Drivers

Product name:  MySQL

Data Source: MySQL (This the system dsn created earlier)

Provider String:  DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;DATABASE=repltest; USER=user;PASSWORD=password;OPTION=3;

(This string is providing all the information to connect to MySQL using the ODBC)

Location: Null

Catalog: repltest (Database name to access and query)

Now on the Security tab, select Be made using this security context option and enter the credentials to connect to MySQL server.

Also finally under Server Options tab, change RPC and RPC Out properties to True, by default they are set to False.

Click Ok, after making all the mentioned changes. This will create the linked server and it will be listed under SSMS Object Browser. Right Click on the MYSQL linked server and click Test Connection.

It should show you the above once succeeded. You can also browse the MYSQL linked server to check if the catalogs are displayed by expanding it.

Step 3: Create Linked Server using T-SQL

While the linked server can be created using the built-in wizard of the Management Studio, it can also be created using TSQL statements as in the following listing (run both statements, the first one creates the linked server and the second the logins).

Exec master.dbo.sp_addlinkedserver
@server=N’localhost’,
@srvprodcut=N’MySQL’,
@provider=N’MSDASQL’,
@datasrc=N’MySQL’

Exec master.dbo.sp_addlinkedserverlogin
@server=N’localhost’,
@locallogin=NULL,
@rmtuser=N’user’,
@rmtpassword=N’<your password>’
@rmtsrvname=N’localhost’

Step 4: Accessing and Querying MySQL through SSMS

Open a new query tab, and run a select query [ select * from openquery(MYSQL,‘select * from reptab’)]

Since we also have enabled the RPC, we can test the same using the following query [Execute (‘select * from reptab‘)at MYSQL]

If it returns the same results, the RPC is configured fine.

Follow all the above steps to configure working Linked Server to MySQL.


Walkthrough: Cluster Setup SQL/Win2008

18 Comments

Problem
After Installing SQL Server 2008 Installation Process you must have noticed how different SQL Server 2008 installation is from its previous versions. Now, we will be installing SQL Server 2008 on a Windows Server 2008 Cluster. Windows Server 2008 has a lot of differences from its previous versions and one of them is the clustering feature.

Solution
There have been a lot of changes regarding clustering between Windows Server 2003 and Windows Server 2008. It took quite a lot of effort for us to build a cluster in Windows Server 2003 – from making sure that the server hardware for all nodes are cluster-compatible to creating resource groups. Microsoft has redefined clustering with Windows Server 2008, making it simpler and easier to implement. Now that both SQL Server 2008 and Windows Server 2008 are out in the market for quite some time, it would be a must to prepare ourselves to be able to setup and deploy a clustered environment running both. Installing SQL Server on a stand-alone server or member server in the domain is pretty straight-forward. Dealing with clustering is a totally different story. The goal of this series of tips is to be able to help DBAs who may be charged with installing SQL Server on a Windows Server 2008 cluster.

Prepare the cluster nodes

I will be working on a 2-node cluster throughout the series and you can extend it by adding nodes later on. You can do these steps on a physical hardware or a virtual environment. I opted to do this on a virtual environment running VMWare. To start with, download and install a copy of the evaluation version of Windows Server 2008 Enterprise Edition. This is pretty straight-forward and does not even require any product key or activation. Evaluation period runs for 60 days and can be extended up to 240 days so you have more than enough time to play around with it. Just make sure that you select at least the Enterprise Edition during the installation process and have at least 12GB of disk space for your local disks. This is to make sure you have enough space for both Windows Server 2008 and the binaries for SQL Server 2008. A key thing to note here is that you should already have a domain on which to join these servers and that both have at least 2 network cards – one for the public network and the other for the heartbeat. Although you can run a cluster with a single network card, it isn’t recommend at all. I’ll lay out the details of the network configuration as we go along. After the installation, my recommendation is to immediately install .NET Framework 3.5 with Service Pack 1 and Windows Installer 4.5 (the one for Windows Server 2008 x86 is named Windows6.0-KB942288-v2-x86.msu). These two are prerequisites for SQL Server 2008 and would speed up the installation process later on.

Carve out your shared disks

We had a lot of challenges in Windows Server 2003 when it comes to shared disks that we will use for our clusters. For one, the 2TB limit which has a lot to do with the master boot record (MBR) has been overcome by having the GUID Partition Table (GPT) support in Windows Server 2008. This allows you to have 16 Exabytes for a partition. Another has been the use of directly attached SCSI storage. This is no longer supported for Failover Clustering in Windows Server 2008. The only supported ones will be Serially Attached Storage (SAS), Fiber Channel and iSCSI. For this example, we will be using an iSCSI storage with the help of an iSCSI Software Initiator to connect to a software-based target. I am using StarWind’s iSCSI SAN to emulate a disk image that my cluster will use as shared disks. In preparation for running SQL Server 2008 on this cluster, I recommend creating at least 4 disks – one for the quorum disk, one for MSDTC, one for the SQL Server system databases and one for the user databases. Your quorum and MSDTC disks can be as small as 1GB, although Microsoft TechNet specifies a 512MB minimum for the quorum disk. If you decide to use iSCSI as your shared storage in a production environment, a dedicated network should be used so as to isolate it from all other network traffic. This also means having a dedicated network card on your cluster nodes to access the iSCSI storage.

Present your shared disks to the cluster nodes

Windows Server 2008 comes with iSCSI Initiator software that enables connection of a Windows host to an external iSCSI storage array using network adapters. This differs from previous versions of Microsoft Windows where you need to download and install this software prior to connecting to an iSCSI storage. You can launch the tool from Administrative Tools and select iSCSI Initiator.

To connect to the iSCSI target:

In the iSCSI Initiator Properties page, click on the Discovery tab.

Under the Target Portals section, click on the Add Portal button.

In the Add Target Portal dialog, enter the DNS name or IP address of your iSCSI Target and click OK. If you are hosting the target on another Windows host as an image file, make sure that you have your Windows Firewall configured to enable inbound traffic to port 3260. Otherwise, this should be okay.

Back in the iSCSI Initiator Properties page, click on the Targets tab. You should see a list of the iSCSI Targets that we have defined earlier

Select one of the targets and click on the Log on button.

In the Log On to Target dialog, select the Automatically restore this connection when the computer starts checkbox. Click OK.

Once you are done, you should see the status of the target change to Connected. Repeat this process for all the target disks we initially created on both of the servers that will become nodes of your cluster.

Once the targets have been defined using the iSCSI Initiator tool, you can now bring the disks online, initialize them, and create new volumes using the Server Manager console. I won’t go into much detail on this process as it is similar to how we used to do it in Windows Server 2003, except for the new management console. After the disks have been initialized and volumes created, you can try logging in to the other server and verify that you can see the disks there as well. You can rescan the disks if they haven’t yet appeared.

Adding Windows Server 2008 Application Server Role

Since we will be installing SQL Server 2008 later on, we will have to add the Application Server role on both of the nodes. A server role is a program that allows Windows Server 2008 to perform a specific function for multiple clients within a network. To add the Application Server role,

Open the Server Manager console and select Roles.

Click the Add Roles link.  This will run the Add Roles Wizard

In the Select Server Roles dialog box, select the Application Server checkbox. This will prompt you to add features required for Application Server role. Click Next.

In the Application Server dialog box, click Next.

In the Select Role Services dialog box, select Incoming Remote Transactions and Outgoing Remote Transactions checkboxes. These options will be used by MSDTC. Click Next

In the Confirm Installation Selections dialog box, click Install. This will go thru the process of installing the Application Server role

In the Installation Results dialog box, click Close. This completes the installation of the Application Server role on the first node. You will have to repeat this process for the other server

We have now gone thru the process of creating the cluster at this point. In the next tip in this series, we will go thru the process of installing the Failover Cluster feature, validating the nodes that will become a part of the cluster and creating the cluster itself. And that is just on the Windows side. Once we manage to create a working Windows Server 2008 cluster, that’s the only time we can proceed to install SQL Server 2008.

To continue this series on Installing SQL Server 2008 on a Windows Server 2008 Cluster, we will look at building our Windows Server 2008 cluster in preparation for SQL Server 2008. We have completed the installation of the Application Server role in both of the servers that we will be using as part of our cluster. This tip will walk you through the installation of the Failover Cluster Feature, validating the servers that will be a part of the cluster, and creating the cluster.

Adding the Failover Cluster Feature

Windows Server 2008 calls them features which are simply software programs that can support or augment the functionality of server roles. Since we’ve already installed the Application Server role in our server, let’s define a feature from this perspective: failover clustering simply augments the role as an application server by making it highly available. It is disabled by default, unlike in Windows Server 2003 so we need to add it on both of the servers that will form a part of our cluster.

To add the Failover Clustering feature:

Open the Server Manager console and select Features.

Click the Add Features link. This will run the Add Features Wizard

In the Select Features dialog box, select the Failover Clustering checkbox and click Next.

In the Confirm Installation Selections dialog box, click Install to confirm the selection and proceed to do the installation of the Failover Clustering feature.

In the Installation Results dialog box, click Close. This completes the installation of the Failover Clustering feature on the first node.

That’s how simple and easy it is to add the Failover Clustering feature in Windows Server 2008. You will have to do this on both nodes to complete the process. Once you have managed to install the Failover Cluster Feature on both nodes, we can proceed to validate our servers if they are ready for clustering.

Running the Windows Server 2008 Validate Cluster Configuration

Unlike in previous versions of Windows where Microsoft had some sort of a hardware compatibility list (HCL) from which we had to find and select components tested to be clustering-supported, this wizard is like the “seal” that tells you whether or not the hardware you are using is supported. In fact, Microsoft has partnered with hardware vendors to create the Failover Cluster Configuration Program to make the acquisition of hardware for Windows Server 2008 Failover Clustering very easy and simple. Basically, your hardware will be supported for clustering if it meets these two requirements: the server has a “Certified for Windows Server 2008” logo and it passes this wizard.

One word of caution: do not skip any error message that this wizard generates in the final report. Doing so would simply mean that your configuration going forward will be unsupported. You only need to run this wizard on either of the nodes.

To run the Validate Cluster Configuration Wizard:

Open the Failover Cluster Management console

Under the Management section, click the Validate a Configuration link. This will run the Validate a Configuration Wizard

In the Select Servers or a Cluster dialog box, enter the hostnames of the nodes that you want to add as members of your cluster and click Next.

In the Testing Options dialog box, click Next to run all the necessary tests to validate whether or not the nodes are OK for clustering. If this is the first time to run the wizard, you must run all the tests for validation. For succeding runs, especially when adding hardware like disk subsystems ot network cards on your cluster nodes, you can selectively choose which tests to run as long as you have initially validated your hardware by running all tests.

In the Confirmation dialog box, click Next. This will run all the necessary validation tests.

In the Summary dialog box, verify that all the report returns successful.

If you have reached this part of the process, the wizard will tell you whether or not you can proceed to the next step of creating your cluster. As I’ve mentioned earlier, do not attempt to go any further if this report returned any error messages. I have seen some installations where the shared disk is displaying an error in the validation report prompting me to reconfigure the shared disk. This could mean removing the disk subsystem from both nodes, creating new disks and presenting them on the nodes as mentioned. It would be best to work with your storage engineers or your system administrators when in doubt as different vendors may have different implementations of their disk subsystems.

I’ve also seen issues pertaining to IPv6. This is a fairly common issue which can easily be resolved. The error message in the cluster validation report looks something similar to the one displayed below

Verifying  that there are no duplicate IP addresses between any pair of nodes.

Found  duplicate IP address fe80::100:7f:fffe%13 on node node1.domain.local adapter

Local Area Connection* X and node node2.domain.local adapter Local Area  Connection* X.

This blog post outlines the step in resolving this issue. In a few cases, however, I needed to disable the Teredo Tunneling Pseudo-Interface adapter from Device Manager before I got a successful summary report generated by the Validate Cluster Configuration wizard. The bottom line is simply to make sure that the report returns a successful validation before creating the cluster.

Creating the Windows Server 2008 Cluster

You’ve finally reached this step in the entire process. This means you are now ready to create your Windows Server 2008 cluster. It’s as easy as running the Create Cluster Wizard on either of the nodes. Make sure that you have your virtual hostname and IP address ready before proceeding

To run the Create a Cluster Wizard:

Open the Failover Cluster Management console

Under the Management section, click the Create a Cluster link. This will run the Create Cluster Wizard

In the Select Servers dialog box, enter the hostnames of the nodes that you want to add as members of your cluster and click Next.

In the Access Point for Administering the Cluster dialog box, enter the virtual hostname and IP address that you will use to administer the cluster. Click Next

In the Confirmation dialog box, click Next. This will configure Failover Clustering on both nodes of the cluster, add DNS and Active Directory entries for the cluster hostname.

In the Summary dialog box, verify that all the report returns successful.

Congratulations! You now have a working Windows Server 2008 cluster. Notice how easy it was to do all of these with fewer steps and less configuration compared to its predecessors. You can now validate whether your cluster is working or not. A simple test would be to do a continuous PING on the virtual hostname or IP address that you have assigned to your cluster. Reboot one of the nodes and see how your PING test responds. At this point, you are now ready to install SQL Server 2008.

OPTIONAL: Configuring your cluster quorum

This section is sometimes necessary especially when Windows Server 2008 decides to take a different disk subsystem as a quorum other than the one you’ve originally intended it to. Notice that in the Create a Cluster wizard, there was no option to select the disk subsystem that we can use as a quorum disk (now called the “witness” disk). By default, the Create a Cluster wizard will use the first available disk as the witness disk. I have seen cases where the originally planned witness disk is sized 1GB while the other shared disks are sized 200 GB. The wizard then selects one of the 200GB-sized disks as a witness disk, requiring you to move the witness disk to the original allocation. To validate, check the Storage node under the Failover Cluster Management console

To configure the quorum in a failover cluster:

Open the Failover Cluster Management console

Select the name of the cluster you have just created. Right-click on the cluster, select More Actions, and click Configure Cluster Quorum Settings… This will open up the Configure Cluster Quorum Wizard

In the Select Quorum Configuration dialog box, select the Node and Disk Majority (recommended for your current number of nodes) option. The options presented will depend on how you want your cluster configured. The current selection is for a 2-node cluster configuration

In the Configure Storage Witness dialog box, validate and select the disk that you want your cluster to use as the quorum/witness disk. Click Next

In the Confirmation dialog box, verify that the disk configuration for the quorum/witness disk is correct. Click Next

In the Summary dialog box, verify that all the configurations are successful.

For more details on Configuring the Quorum in a Failover Cluster in Windows Server 2008, check out this Microsoft TechNet article.

To continue this series on Installing SQL Server 2008 on a Windows Server 2008 Cluster, we will look at installing SQL Server 2008 in a failover cluster. Firstly, we have completed the installation of the Application Server role in both of the servers that we will be using as part of our cluster. Secondly walked you through the installation of the Failover Cluster Feature, validating the servers that will be a part of the cluster, and creating the cluster. In this tip, we will proceed to install SQL Server 2008 in a clustered Windows Server 2008 environment.

Installing and Configuring MSDTC

The Microsoft Distributed Transaction Coordinator (MSDTC) is a transaction manager that permits client applications to include several different data sources in one transaction and which then coordinates committing the distributed transaction across all the servers that are enlisted in the transaction. A lot of people ask why we need to install MSDTC prior to installing SQL Server. If you are using distributed transactions or running SQL Server on a cluster, this is definitely a must. SQL Server uses the MSDTC service for distributed queries and two-phase commit transactions, as well as for some replication functionality.

Configuring MS DTC in Windows Server 2003 clusters as defined in this Microsoft KB article is not pretty straight-forward. Windows Server 2008 made it simpler by providing a more straightforward process with fewer steps and less configuration.

To install and configure MSDTC:

Open the Failover Cluster Management console on any of the cluster node.

Under the cluster name, right-click on Server and Applications and select Configure a Service or Application. This will run the High Availability Wizard

In the Service or Application dialog box, select Distributed Transaction Coordinator (DTC) and click Next.

In the Client Access Point dialog box, enter the name and IP address of the clustered MSDTC. This should be a different IP addresses and host name from the one that the Windows Server 2008 cluster is already using. Click Next.

In the Select Storage dialog box, select the disk subsystem that will be used by MSDTC. These disk subsystems have to be defined as available storage in your cluster. In the example below, I have used the disk volume F:\ and left the disk volume E:\ for SQL Server later in the installation process. Click Next

In the Confirmation dialog box, validate the configuration you have selected for MSDTC and click Next

In the Summary dialog box, click Close. This completes the installation of MSDTC on the cluster.

You can validate your installation of MSDTC by expanding the Services and Applications node and check the cluster name of MSDTC.  Make sure that all of the dependency resources are online

Installing SQL Server 2008 on a Windows Server 2008 cluster

You’ve gone this far, don’t stop now. Only after we have managed to prepare everything can we proceed to install SQL Server 2008 on this cluster. Since we’ve already installed .NET Framework 3.5 with Service Pack 1 and Windows Installer 4.5, we no longer have to worry about them as they both are prerequisites whether you are doing a single server or a cluster installation. There are two options to install SQL Server 2008 on a cluster. The first one is by using the Integrated failover cluster install with Add Node option and the second one is the Advanced/Enterprise installation option. The process outlined below will take into account the first option.

To install SQL Server 2008:

Run setup.exe from the installation media to launch SQL Server Installation Center. Click on the Installation link on the left-hand side

Click the New SQL Server failover cluster installation link. This will run the SQL Server 2008 Setup wizard

In the Setup Support Rules dialog box, validate that the checks return successful results and click Next.

In the Product Key dialog box, enter the product key that came with your installation media and click Next.

In the License Terms dialog box, click the I accept the license terms check box and click Next. You probably haven’t read one of these, but if you feel inclined go for it.

In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. If the checks returned a few warnings, make sure you fix them before proceeding with the installation. An example of this is the Network binding order. The public network cards should be first on both nodes. Also, you can disable NETBIOS and DNS registration on the network cards to avoid network overhead. Be sure to check your binding order as well.  For more details on the network binding order warning, see Microsoft KB 955963.

For the Windows Firewall, make sure that you open the appropriate port number on which SQL Server will communicate. You can do this after the installation. Alternatively, you can disable Windows Firewall during the installation and enable it later with the proper configuration. Click Next to proceed.

In the Feature Selection dialog box, select only the components that you want installed. For the Shared feature directory, you can keep the default path if you have sufficient disk space on your C:\ drive or anywhere that is a local disk as this will be used by the SQL Server installation process later on. The directory for the clustered database engine will be different. Click Next.

In the Instance Configuration dialog box, enter the SQL Server Network Name. This is the name that will be available on the network for the clients. This will vary depending on your selection of whether it is a default or named instance. In this example, default instance is selected.

A couple of things need highlighting in this section. By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for your instance of SQL Server and is helpful when you want to run multiple instances in a cluster. This is the case for default instances and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a non-default instance ID, you should select the Instance ID box and specify a value.

The section on Detected SQL Server instances and features on this computer would make sense if there are other SQL Server instances running on your server.

In the Disk Space Requirements dialog box, check that you have enough space on your local disks to install the SQL Server 2008 binaries and click Next.

In the Cluster Resource Group dialog box, check the resources available on your Windows Server 2008 cluster. This will tell you that a new Resource Group will be created on your cluster for SQL Server. To specify the SQL Server cluster resource group name, you can either use the drop-down box to specify an existing group to use or type the name of a new group to create it. Click Next.

In the Cluster Disk Selection dialog box, select the available disk groups that are on the cluster for SQL Server 2008 to use. In this example, two clustered disk groups – APPS and APPS2 – have been selected to be used by SQL Server 2008. I will be using one disk resource for the system databases while the other one for the user databases. Click Next.

In the Cluster Network Configuration dialog box, enter the IP address and subnet mask that your SQL Server 2008 cluster will use. Deselect the checkbox under the DHCP column as you will be using static IP addresses. If you have not disabled your IPv6 adapters and protocols, it would be better to uncheck the row for IPv6

In the Cluster Security Policy dialog box, accept the default value of Use service SIDs (recommended). In Windows Server 2003, we specify domain groups for all SQL Server services but in Windows Server 2008, this is the recommended option. For more information on using service SIDs for SQL Server 2008, check out this MSDN article

In the Server Configuration dialog box, enter the credentials that you will use for your SQL Server service accounts in the Service Accounts tab. In the Collation tab, select the appropriate collation to be used by SQL Server. Note that the startup type is set to manual for all cluster-aware services and cannot be changed during the installation process. Click Next.

In the Database Engine Configuration dialog box, select the appropriate Authentication Mode. If you want to add the currently logged on user to be a part of the SQL Server administrators group, click the Add Current User button.

On the Data Directories tab, enter the path where your system and user database files will be created. This will default to the first shared disk in the cluster so in case you want to change it to the other shared disks to be used by SQL Server 2008, modify accordingly. If you intend to use the new FILESTREAM feature, click the FILESTREAM tab and set the appropriate configurations. Click Next

In the Error and Usage Reporting dialog box, click Next.

In the Cluster Installation Rules dialog box, verify that all checks are successful and click Next.

In the Ready to Install dialog box, verify that all configurations are correct. Click Next.

In the Complete dialog box, click Close. This concludes the installation of a SQL Server 2008 Failover Cluster

At the completion of a successful installation and configuration of the node, you now have a fully functional failover cluster instance. To validate, open the Failover Cluster Management console, and click on SQL Server (MSSQLSERVER) under Services and Applications. Make sure that all dependencies are online

Although we do have a fully functioning SQL Server 2008 failover cluster, it does not have high-availability at this point in time because there is only one node in the failover cluster. We still have to add the second node to the SQL Server 2008 cluster. In the last part of this series, we will add the second node in the failover cluster and install the latest cumulative update

To continue this series on Installing SQL Server 2008 on a Windows Server 2008 Cluster, we will look at adding a node in a SQL Server 2008 failover cluster. In this third step, we will proceed to add a node in a SQL Server 2008 failover cluster and apply the latest cumulative updates.

Adding a node on a SQL Server 2008 Failover Cluster

Now that you have a working failover cluster, we will make it highly available by adding nodes. The number of nodes you can add in a failover cluster depends on the editions of SQL Server that you will use. A Standard Edition of SQL Server 2008 can support up to two (2) nodes in a failover cluster while the Enterprise Edition supports up to sixteen (16) nodes, which is practically the limit for the Enterprise Edition for Windows Server 2008. As most of the steps in this process are similar to the one when you were installing the failover cluster, I’ve skipped most of the screenshots.

To add a node on a SQL Server 2008 failover cluster:

Run setup.exe from the installation media to launch SQL Server Installation Center

Click on the Installation link on the left-hand side. Click the Add node to a SQL Server failover cluster link. This will run the SQL Server 2008 Setup wizard.

There are a couple of glitches when you get to this point. One of them is a popup error with an error message “failed to retrieve data for this request” while in this step. I’ve seen a Microsoft Connect item on this but refers to CTP6 so I was thinking it has already been resolved. After a few searches and questions asked, SQL Server MVP Geoff Hiten advised that prior to adding another node in the cluster, any cumulative update should be pre-applied to the node before the main installation as the cluster install of the RTM version has some bugs. This creates a patched install script for the RTM installer to use. The fix started with cumulative update 1 so, technically, you can apply any cumulative update. Sounds weird, but it works. You still have to apply the patch after the installation.

In the Setup Support Rules dialog box, validate that the checks return successful results and click OK.

In the Product Key dialog box, enter the product key that came with your installation media and click Next.

Again, a few glitches on this step. This might seem unusual as you are only being asked about the Product Key. There is also a Microsoft Connect item for this which basically asks you to run the setup.exe in command prompt. There is a popup error with an error message “The current SKU is invalid” while in this step. This usually happens when you use a media with a supplied product key, like the one that comes with an MSDN subscription. What worked for me was to copy the installation media on a local disk, locate the file DefaultSetup.ini file from the installation files and delete it or move it to different location. If you opt to delete the file, make sure you note down the product key written on this file as you will need to manually key this in during the installation process. This forum post will give you quite a few options to solve this issue

In the License Terms dialog box, click the I accept the license terms check box and click Next.

In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. Again, make sure to fix any errors returned by this check before proceeding with the installation.

In the Cluster Node Configuration dialog box, validate that the information for the existing SQL Server 2008 cluster is correct.

In the Service Accounts dialog box, verify that the information is the same as what you have used to configure the first node.

In the Error and Usage Reporting dialog box, click Next

In the Add Node Rules dialog box, verify that all checks are successful and click Next

In the Ready to Add Node dialog box, verify that all configurations are correct and click Install

In the Complete dialog box, click Close. This concludes adding a node to a SQL Server 2008 Failover Cluster

You can validate your cluster installation by expanding the Services and Applications node and check the cluster name of your SQL Server instance.  You can now see an option to move the service to another node, in this case, the node you’ve just added in your failover cluster

Applying patches on a SQL Server 2008 cluster

Part of the tasks of a DBA is to apply patches on the database engine and a SQL Server 2008 failover cluster is no exception. In fact, it is not as straight-forward as applying patches and service packs on a stand-alone server. It is important to note that when applying patches or service packs to a SQL Server failover cluster, you should apply them first on the passive node. After completing the installation on the passive node, failover the SQL Server 2008 cluster resource to this node making it the active node. Once the SQL Server service and all other dependencies are up, you can, then, apply the patches on the new passive node. The latest available patch for SQL Server 2008 is cumulative update 4 and is available for request from Microsoft.  For more information, check out this Microsoft KB article. You will have to request for the patch from Microsoft as it is not available from the Microsoft Download Center. The screenshots below show cumulative update 3 (version 10.0.1600.22) but the process is basically the same. Also, note that even though you may have already applied the cumulative update due to the bug mentioned above for adding a node in a failover cluster, you still have to apply the patch on both nodes

To apply patches on a SQL Server 2008 failover cluster node:

Run SQLServer2008-KB960484-x86.exe (this would depend on the cumulative update that you want to apply) from the hotfix package you have requested from Microsoft

In the Welcome dialog box, validate that the checks return successful results.

In the License Terms dialog box, click the I accept the license terms check box and click Next

In the Select Features dialog box, validate the SQL Server 2008 components by clicking on the check box.  The Upgrade Status field will tell you whether or not the patch has already been applied. Click Next

In the Ready to Update dialog box, verify that all configurations are correct and click Patch

In the Update Progress dialog box, validate that the installation was successful.

In the Complete dialog box, click Close. This concludes patching the passive node of a SQL Server 2008 Failover Cluster


After successfully installing the patch on the passive node, move the SQL Server 2008 cluster resource to this node so it will become the new active node. Make sure that all the SQL Server 2008 cluster dependencies are online prior to applying the patch on the other node.  Repeat the process outlined above to the new passive node. A more comprehensive approach for applying a SQL Server 2008 patch to a failover cluster instance is defined in this Microsoft KB article

Congratulations! You now have a working two-node SQL Server 2008 failover cluster running on Windows Server 2008.

Clustering Windows Server 2003 and SQL Server 2005

10 Comments

How to Cluster Windows Server 2003

Before you can install SQL Server 2005 clustering, you must first install Windows Server 2003 clustering services. Once it is successfully installed and tested, then you can install SQL Server 2005 clustering. In this article, we take a step-by-step approach to installing and configuring Windows 2003 clustering. In a later article, we will learn how to install SQL Server 2005 clustering.

Before Installing Windows 2003 Clustering

Before you install Windows 2003 clustering, we need to perform a series of important preparation steps. This is especially important if you didn’t build the cluster nodes, as you want to ensure everything is working correctly before you begin the actual cluster installation. Once they are complete, then you can install Windows 2003 clustering. Here are the steps you must take:

  • Double check to ensure that all the nodes are working properly and are configured identically (hardware, software, drivers, etc.).
  • Check to see that each node can see the data and Quorum drives on the shared array or SAN. Remember, only one node can be on at a time until Windows 2003 clustering is installed.
  • Verify that none of the nodes has been configured as a Domain Controller.
  • Check to verify that all drives are NTFS and are not compressed.
  • Ensure that the public and private networks are properly installed and configured.
  • Ping each node in the public and private networks to ensure that you have good network connections. Also ping the Domain Controller and DNS server to verify that they are available.
  • Verify that you have disabled NetBIOS for all private network cards.
  • Verify that there are no network shares on any of the shared drives.
  • If you intend to use SQL Server encryption, install the server certificate with the fully qualified DNS name of the virtual server on all nodes in the cluster.
  • Check all of the error logs to ensure there are no nasty surprises. If there are, resolve them before proceeding with the cluster installation.
  • Add the SQL Server and Clustering service accounts to the Local Administrators group of all the nodes in the cluster.
  • Check to verify that no antivirus software has been installed on the nodes. Antivirus software can reduce the availability of clusters and must not be installed on them. If you want to check for possible viruses on a cluster, you can always install the software on a non-node and then run scans on the cluster nodes remotely.
  • Check to verify that the Windows Cryptographic Service Provider is enabled on each of the nodes.
  • Check to verify that the Windows Task Scheduler service is running on each of the nodes.
  • If you intend to run SQL Server 2005 Reporting Services, you must then install IIS 6.0 and ASP .NET 2.0 on each node of the cluster.

These are a lot of things you must check, but each of these is important. If skipped, any one of these steps could prevent your cluster from installing or working properly.

How to Install Windows Server 2003 Clustering

Now that all of your physical nodes and shared array or SAN is ready, you are now ready to install Windows 2003 clustering. In this section, we take a look at the process, from beginning to end.

To begin, you must start the Microsoft Windows 2003 Clustering Wizard from one of the nodes. While it doesn’t make any difference to the software which physical node is used to begin the installation, I generally select one of the physical nodes to be my primary (active) node, and start working there. This way, I won’t potentially get confused when installing the software.

If you are using a SCSI shared array, and for many SAN shared arrays, you will want to make sure that the second physical node of your cluster is turned off when you install cluster services on the first physical node. This is because Windows 2003 doesn’t know how to deal with a shared disk until cluster services is installed. Once you have installed cluster services on the first physical node, you can turn on the second physical node, boot it, and then proceed with installing cluster services on the second node.

Installing the First Cluster Node

To begin your installation of SQL Server 2003 Clustering, open Cluster Administrator. If this is the first cluster, then you will be presented with the following window.

image001

From the Action drop-down box, select Create New Cluster and click OK. This brings up the New Server Cluster Wizard, as show below.

image002

Click Next to begin the wizard.

image003

The next steps seem easy because of the nature of the wizard, but if you choose the wrong options, they can have negative consequences down the line. Because of this, it is important that you carefully think through each of your responses. Ideally, you will already have made these choices during your planning stage.

The first choice you must make is the domain the cluster will be in. If you have a single domain, this is an easy choice. If you have more than one domain, select the domain that all of your cluster nodes reside in.

The second choice is the name you will assign the virtual cluster. This is the name of the virtual cluster, not the name of the virtual SQL Server. About the only time you will use this name is when you connect to the cluster with Cluster Administrator. SQL Server 2005 clients will not connect to the cluster using this virtual name.

Once you enter the information, click Next to proceed.

image004

Now, we have to tell the wizard the physical name of the node we want to install clustering on. Assuming that you are running the Cluster Wizard on the primary node of your cluster, then the computer name you see in the above screen will be the name of the physical node you are installing on. If you are installing from one node, but want to install clustering on a different node, you can, but it just gets confusing if you do. It is much easier to install on the same node.

Notice the Advanced button in the screen shot above. If you click on it, you will see the following.

image005

Advanced Configuration Options allow you to choose from between a Typical and an Advanced configuration. In almost all cases, the Typical configuration will work fine, and that is the option we use during this example. The Advanced configuration option is only needed for complex SAN configurations, and is beyond the scope of this article.

So click Cancel to return to the wizard, enter the correct physical node, if need be, and click Next.

image006

This next step is very important. What the Cluster Wizard does is to verify that everything is in place before it begins the actual installation of the cluster service on the node. As you can see above, the wizard goes through many steps, and if you did all of your preparation correctly, when the testing is done, you will see a green bar under Tasks completed, and you will be ready to proceed. But if you have not done all the preliminary steps properly, you may see yellow or red icons next to one or more of the many tested steps, and a green or red bar under Tasks completed.

Ideally, you will want to see results similar to the figure above, with a green bar and no yellow icons next to the test steps. In some cases, you may see yellow warning icons next to one or more of the test steps, but still see a green bar at the bottom. While the green bar does indicate that you can proceed, it does not mean the cluster will be completed successfully or will be configured like you want it to be completed. If you see any yellow warning icons, you can drill down into them and see exactly what the warning is. Read each warning very carefully. If the warning is something unimportant to you, it can be ignored. But in most cases, the yellow warnings need to be addressed. This may mean you will have to abort the cluster service installation at this time to fix the problem. Then you can try to install it again.

If you get any red warning icons next to any of the test steps, then you will also get a red bar at the bottom, which means that you have a major problem that needs to be corrected before you can proceed. Drill down to see the message and act accordingly. Most likely, you will have to abort the installation, fix the issue, and then try installation again.

Assuming that the installation is green and you are ready to proceed, click Next.

image007

The next step is to enter the IP address of our virtual cluster. This is the IP address for the cluster, not the virtual SQL Server. The IP address must be on the same subnet as all of the nodes in the cluster. Click Next.

image008

Next you enter the name of the domain account you want to use as the cluster service account. You will also enter the account’s password and the name of the domain where the account was created. This account should have already been created in your domain and added to all of the cluster nodes in the Local Administrators Group. Click Next.

image009

The next Cluster Wizard step is the Proposed Cluster Configuration. But before you click Next, be sure to click on the Quorum button and check which drive the Cluster Wizard has selected for the Quorum. In this case Drive Q has been chosen, which is correct. Most of the time, the Cluster Wizard will select the correct drive for the Quorum, but not always. This is why it is important to check to see if the correct drive was chosen. Because I named my Quorum drive “Q,” it is very easy for me to determine that the correct drive was chosen by the Cluster Administrator. That is why I earlier suggested that you name the Quorum drive “Q.”

Assuming everything is OK, click OK to accept the Quorum drive, and then click Next. At this time, the Cluster Wizard will reanalyze the cluster, again looking for any potential problems. If none is found, click Next, and then click Finish to complete the installation of SQL Server 2003 clustering on the first node.

Installing the Second Node of Your Cluster

Once you have installed the first node of your cluster, it is time to install the second node. Like the first node, the second node is installed from Cluster Administrator. Because the cluster already exists, we are just adding the second node to the currently existing cluster. You can install the second node from either the first node or the second node. Personally, I do it from the second node so that I don’t get confused.

To install the second node, turn it on (it should have been off while you installed the first node) and bring up Cluster Administrator. You will get the same window as you saw when you installed the first node. From here, select Add Nodes to Cluster. This brings up the Add Nodes Wizard, which is very similar to the previous New Server Cluster Wizard we just ran, except it has fewer options.

As the wizard proceeds, you will enter the name of the physical node to add to the current cluster, after which a series of tests will be automatically run to verify that the node is ready to be clustered. As before, if you run into any problems—yellow or red warnings—you should correct them first before continuing. Once all problems have been corrected, you are then asked to enter the password for the cluster service account (to prove that you have permission to add a node to the cluster) and the node is added to the cluster.

Verifying the Nodes With Cluster Administrator

Once you have successfully installed the two nodes of your cluster, it is a good idea to view the nodes from Cluster Administrator. When you bring up Cluster Administrator for the first time after creating a cluster, you may have to tell it to Open a Connection to Cluster, and type in the name of the virtual cluster you just created. Once you have done this, the next time you open Cluster Administrator it will automatically open this cluster for you by default.

After opening up Cluster Administrator, what you see will be very similar to the figure below.

image010

Notice that two resource groups have been created for you: Cluster Group and Group 0. The Cluster Group includes three cluster resources: the Cluster IP Address, the Cluster Name, and the Quorum drive. These were all automatically created for you by the Cluster Wizard. We will talk more about Group 0 a little later.

When you look next to each cluster resource, the State for each resource should be Online. If not, then your cluster may have a problem that needs to be fixed. As a quick troubleshooting technique, if any of the resources are not Online, right-click on the resource and choose Bring Online. In some cases, this will bring the resource online and you will not experience any more problems. But if this does not work, then you need to begin troubleshooting your cluster.

Also, next to each resource is listed the Owner of the resource. All the resources in a resource group will always have the same owner. Essentially, the owner is the physical node where the cluster resources are currently running. In the example above, the physical node they are running on is SQL2005A, which is the first node in my two-node cluster. If a failover occurs, then all of the resources in the resource group will change to the other node in your cluster.

How to Configure Windows Server 2003 for Clustering

Before you install SQL Server clustering, there is one small step you need to perform, and that is to prepare a resource group for the SQL Server resources that will be created when SQL Server is installed.

Most likely, when you created the cluster, as above, you will see a resource group named Group 0. This resource group was created when the cluster was created, and it most likely includes the shared resource for your SQL Server databases to use. See below.

image011

In my example, Disk F, the shared array for SQL Server, is in Group 0. If you like, you can leave the resource group with this name, but it is not very informative. I suggest that you rename Group 0 to SQL Server Group. You can do this by right-clicking on Group 0 and selecting Rename.

In some cases, the Cluster Wizard may put the SQL Server shared disk array in the Cluster Group resource group and not create a Group 0. If this is the case, then you will need to create a new resource group and then move the SQL Server shared disk array from the Cluster Group to the newly created SQL Server resource group.

Here’s how you create a new resource group using Cluster Administrator:

  • Start Cluster Administrator.
  • From the File menu, select New, then select Group. This starts the New Group Wizard.
  • For the Name of the group, enter “SQL Server Group.” Optionally, you can also enter a description of this group. Click Next.
  • Now, you must select which nodes of your cluster will be running SQL Server. This of course will be all of your nodes. The nodes are listed on the left side of the wizard. CTRL-click each of the nodes on the left and then select Add. This will move the selected nodes from the left side of the wizard to the right side. Click Finish.

The new SQL Server Group resource group has now been created.

Now that the group has been created, it must be brought online. Here’s how.

  • From Cluster Administrator, right-click on the SQL Server resource group (it will have a red dot next to it) and select Bring Online.
  • The red dot next to the resource group name goes away, and the SQL Server Group resource group is now online and ready for use.

Now, your next step is to move any disk resources from the Cluster Group (except the Quorum drive) to the SQL Server Group. This is a simple matter of dragging and dropping the disk resources from the Cluster Group to the SQL Server Group. Once you have done this, you are ready for the next step.

Test, Test, and Test Again

Once you have installed Windows 2003 clustering on your nodes, you need to thoroughly test the installation before beginning the SQL Server 2005 cluster install. If you don’t, and problems arise later with Windows 2003 clustering, you may have to remove SQL Server 2005 clustering to fix it, so you might as well identify any potential problems and resolve them now.

Below are a series of tests you can perform to verify that your Windows 2003 cluster is working properly. After you perform each test, verify if you get the expected results (a successful failover). Also be sure to check the Windows event log files for any possible problems. If you find a problem during one test, resolve it before proceeding to the next test. Once you have performed all of these tests successfully, then you are ready to continue with the cluster installation.

Preparing for the Tests

Before you begin testing, identify a workstation that has Cluster Administrator on it, and use this copy of Cluster Administrator for interacting with your cluster during testing. You will get a better test using a remote copy of Cluster Administrator than trying to use a copy running on one of the cluster nodes.

Move Groups Between Nodes

The easiest test to perform is to use Cluster Administrator to manually move the Cluster Group and SQL Server resource groups from the active node to a passive node, and then back again. To do this, right-click on the Cluster Group and then select Move Group.

Once the group has been successfully moved from the active node to a passive node, then use the same procedure above to move the group back to the original node. The moves should be fairly quick and uneventful. Use Cluster Administrator to watch the failover and failback, and check the Event Logs for possible problems. After moving the groups, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

Manually Initiate a Failover in Cluster Administrator

This test is also performed from Cluster Administrator. Select any of the resources found in the Cluster Group resource group (not the cluster group itself), right-click on it, and select Initiate Failure. Because the cluster service always tries to recover up to three times from a failure, if it can, you will have to select this option four times before a test failover is initiated. Watch the failover from Cluster Administrator. After the failover, then failback using the same procedure as described above, again watching the activity from Cluster Administrator. Check the Event Logs for possible problems. After this test, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

Manually Failover Nodes by Turning Them Off

This time, we will only use Cluster Administrator to watch the failover activity, not to initiate it. First, turn off the active node by turning it off hard. Once this happens, watch the failover in Cluster Administrator. Once the failover occurs, turn the former active node on and wait until it fully boots. Then turn off the now current active node by turning it off hard. And again, watch the failover in Cluster Administrator. After the failover occurs, bring the off node back on. Check the Event Logs for possible problems. After this test, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

Manually Failover Nodes by Breaking the Public Network Connections

In this test, we will see what happens if network connectivity fails. First, both nodes being tested should be on. Second, unplug the public network connection from the active node. This will cause a failover to a passive node, which you can watch in Cluster Administrator. Third, plug the public network connection back into the server. Fourth, unplug the public network connection from the now active node. This will cause a failover to the current passive node, which you can watch in Cluster Administrator. Once the testing is complete, plug the network connection back into the server. Check the Event Logs for possible problems. After this test, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

Manually Failover Nodes by Breaking the Shared Array Connection

This test is always exciting as it is the test that is most apt to identify potential problems. First, from the active node, remove the shared array connection. This will cause a failover that you can watch in Cluster Administrator. Now reconnect the broken connection. Second, from the now active node, remove the shared array connection. Watch the failover in Cluster Administrator. When done, reconnect the broken connection. Check the Event Logs for possible problems. After this test, all of the resources in each group should be in the online state. If not, you have a problem that needs to be identified and corrected.

As I mentioned before, if any particular test produces unexpected problems, such as failover not working or errors are found in the Event Logs, identify and resolve them now before proceeding with the next test. Once you have resolved any problems, be sure to repeat the test that originally indicated the problem in order to verify that it has been fixed.

Now that you have completed the Windows 2003 cluster installation and have tested it, you are ready to install and configure the Microsoft Distributed Transaction Coordinator.

Configuring the Microsoft Distributed Transaction Coordinator

While not required, it is recommended that you install the Microsoft Distributed Transaction Coordinator (MS DTC) on each of the cluster nodes before installing SQL Server 2005 clustering. This is because SQL Server 2005 requires this service in order to perform some functions, including running distributed queries, two-phase commit transactions, and some aspects of replication. MS DTC must be installed after installing Windows 2003 clustering, but before installing SQL Server 2005 clustering.

Installing MS DTC Using Cluster Administrator

While MS DTC can be set up for clustering from the command line, it is much easier to use Cluster Administrator, as described below. This is because this procedure automatically configures MS DTC on all of the cluster nodes at the same time. Take your time to ensure that you do it right the first time.

  • Start Cluster Administrator.
  • Right-click on the Cluster Group resource group, select New, then Resource. This starts the new Resource Wizard.
  • In the first screen of the Resource Wizard, enter the name of the resource you are creating, which would be “MSDTC Resource.” If you like, you can also enter an optional description of this resource. Under Resource Type, select Distributed Transaction Coordinator. Under Group, Cluster Group should already be displayed. Click Next.
  • In the Possible Owners dialog box, you will see that all of the nodes of the cluster are listed under Possible Owners. This is correct and should not be changed. Click Next.
  • In the Dependencies dialog box, press and hold the CTRL key on the Quorum disk resource and the Cluster Name, then click Add. Then click finish.

At this time, the MSDTC Resource is created.

Now that the resource has been created, it must be brought online. Here’s how.

  • From Cluster Administrator, right-click on the MSDTC Resource (it will have a red dot next to it) and select Bring Online.

The red dot next to the resource name goes away, and the MSDTC Resource is now online and ready for use. If the new resource won’t come online, delete it and try again.

Ready to Install SQL Server 2005

Finally, you are ready to install SQL Server 2005 clustering.

Believe it or not, the procedure to install a SQL Server 2005 instance onto a cluster is one of the easiest parts of getting your SQL Server 2005 cluster up and running. The SQL Server 2005 setup program is used for the install and does the hard work for you. All you have to do is make a few (but critically important) decisions, and then sit back and watch the installation take place. In fact, the setup program even goes to the trouble to verify that your nodes are all properly configured, and if not, will suggest how to fix most problems before the installation begins.

When the installation process does begin, the setup program recognizes all the nodes, and once you give it the go ahead to install on each one, it does, all automatically. SQL Server 2005 binaries are installed on the local drive of each node, and the system databases are stored on the shared array you designate.

In the next section are the step-by-steps instructions for installing a SQL Server 2005 instance in a cluster. The assumption for this example is that you will be installing this instance in a 2-node active/passive cluster. Even if you will be installing a 2-node active/active or a multi-node cluster, the steps in this section are virtually the same. The only real difference is that you will have to run SQL Server 2005 setup for every instance you want to install on the cluster, and you will have to specify a different logical drive on the shared array.

Clustering SQL Server

To begin installing your SQL Server 2005 cluster, you will need the installation CD or DVD. You can either install it directly from the media, or copy the install files from the media to the current active node of the cluster, and run the setup program from there.

To begin the installation, run Setup.exe. After an introductory screen, you will get the first install dialog box as shown in the figure below.

image012

The Installing Prerequisites dialog box lists the prerequisites that need to be installed before installation of SQL Server 2005 can begin. The number of components may vary from the above figure, depending on what you have already installed on your nodes. What is interesting to note here is that these prerequisite components will only be installed immediately on the active node. They will be installed on the passive node later during the installation process. This is done automatically and you don’t have to worry about it.

Click Install to install these components. When completed, you will get a dialog box telling you that they were installed successfully, and then you can the click Next to proceed. On occasion, I have seen these components fail to install correctly. If this happens, you will have to troubleshoot the installation. Generally speaking, try rebooting both nodes of the cluster and try installing them again. This often fixes whatever caused the first setup try to fail.

Once the prerequisite components have been successfully installed, the SQL Server Installation Wizard launches, as you can see in the figure below.

image013

Click Next to proceed.

image014

The next step is for the SQL Server Installation Wizard to perform a System Configuration Check. This is very similar to the check that was performed with clustering services when you installed Windows Server 2003 Clustering. Ideally, you want all checks to be successful, with a green icon. If you get any yellow warning or red error icons, then you need to find out what the problem is, and correct it before proceeding. In some cases, yellow warning icons can be ignored, but red error icons cannot. If you have any yellow or red icons, you may have to abort the setup process, fix the problem, then restart the setup process. Assuming all is well, click Next to proceed.

The next dialog box is Registration, where you enter your company name and license key, if applicable.

Next, you must select the SQL Server 2005 components to install. See below.

image015

I want to point out the options to “Create a SQL Server failover cluster” and to “Create an Analysis Server failover cluster” (currently grayed out). Since we are creating a SQL Server 2005 cluster, you must select the “Create a SQL Server failover cluster.” If you are going to install Analysis Services (not covered in this example) then you must select “Create an Analysis Server failover cluster.” Once you have selected all the components you need to include, click Next.

image016

As with any install of SQL Server 2005, the next step is to select the name of the instance to be installed. You can choose between a default instance and a named instance. Click Next to proceed.

image017

Now, here is a very important step. This is when you enter the name of the virtual SQL Server 2005 instance you are currently installing. This is the name that clients will use to connect to this instance. Ideally, you have already selected a name to use that makes the most sense to your organization. Click Next to proceed. If you ever need to change this virtual name, you will have to uninstall and then reinstall SQL Server 2005 clustering.

image018

This is also a very important step. This is where you enter the virtual IP address for this instance of SQL Server 2005. Like the cluster virtual name, it is used by clients to connect to this instance of SQL Server 2005. The IP address must belong to the same subnet as the IP addresses used by all of the nodes.

In addition, in this dialog box you must select the network to be used for the public network—the network used by the clients to connect to this instance of SQL Server 2005. All of the available networks will be listed in the drop-down box next to Network to use. If you have named the public and private networks Public and Private, respectively, it will be very easy for you to select the correct network, as I have above.

Once you have entered the IP address and selected the public network, click on Add, so that the information you just selected is in the Selected networks and IP addresses box. Then click Next.

image019

In this dialog box, select the SQL Server Group as the group where you want to create the SQL Server resources. In addition, be sure that the Data files will be created on the correct logical drive of the shared array using the folder name you choose. Click Next to proceed.

image020

Now, you specify which nodes you want to install this instance of SQL Server on. Because our example is for only two nodes, the default setting works for us. Notice that under Required node is SQL2005A, which is the name of the physical node where we are running the setup program. And under Selected nodes is SQL2005B, the second physical node in our 2-node cluster. Click Next to proceed.

image021

In this dialog box, we must select an account (with password) that has administrative rights on all of the nodes where we want to install this instance of SQL Server 2005. This can be any domain account that is a local administrator of all the nodes. Click Next to proceed.

image022

The Service Account dialog box is identical to the one you see when you install SQL Server 2005 on a non-cluster, and it is configured the same. Click Next to proceed.

image023

In this dialog box, you must select pre-existing global domain groups that are used to contain the startup account for each clustered service. You can choose to add all three services to the same global domain group, or to create separate global domain groups, one for each service, as has been done above. Once you have selected appropriate domain groups, click Next to proceed.

The next four dialog boxes of the Installation Wizard, not shown here, are the same as for any other installation of SQL Server 2005. After you have completed these steps, the installation of this instance of SQL Server 2005 begins, and you see the following dialog box.

image024

The installation process will take some time as it is installing the binaries on both nodes of the cluster, and installing the system data files on the shared array. The Setup Progress step shows the status of the first node’s install. If you want to see the status of the second node’s install, you can change the drop-down box next to Node to the second node and watch its progress.

As the installation proceeds, you will want to see all green icons next to each installation step. If any step should fail, then the entire installation process will need to be rolled back, any problems fixed, and SQL Server 2005 installed fresh. In most cases, cancelling a bad installation will uninstall what has already been installed, but not always.

Sometimes, if the installation breaks, it just dies and a rollback of what has been done so far will not occur. If this is the case you can either choose to reinstall on top of the existing bad install (which often does not work), manually uninstall the failed installation (check Microsoft’s Web site for assistance in this area), or rebuild your cluster from scratch (starting with the operating system).

It the install was a success, you will see a final dialog box, where you can click Finish. SQL Server 2005 clustering had now been successfully installed on the two cluster nodes.

Clustering Analysis Services

SQL Server 2005 Analysis Services can be clustered just like SQL Server 2005, and in fact, is installed using the same setup program used to install SQL Server 2005. Below are some points to keep in mind if you should decide to cluster SQL Server 2005 Analysis Services.

  • SQL Server 2005 Analysis Services can be installed by itself, or with SQL Server 2005. Because some of the features of Analysis Services require components of the SQL Server 2005 database engine, it is generally a good idea to install both of them in your cluster.
  • SQL Server 2005 Analysis Services is installed using the same setup program as SQL Server 2005. When running the setup program, you select, or not select, Analysis Services to be installed in the “Components to Install” screen.
  • Because SQL Server 2005 Analysis Services needs to store program files, data files, and shared files, you will have to specify the location of your shared array, where they will reside. These files must reside on a shared array if you want Analysis Services to run after a failover. To specify the location of the shared array, you must select the “Advanced” button from the “Components to Install” screen in the setup wizard.

Other than the above, installing SQL Server 2005 Analysis Services in a cluster is virtually identical to installing SQL Server 2005 in a cluster.

Installing the Service Pack and Hot Fixes

Once you have installed SQL Server 2005 clustering, your next step is to install the latest SQL Server 2005 service pack and hot fixes, which can be downloaded from Microsoft’s Web site. Installing a service pack or hot fix is fairly straightforward because they are cluster-aware. Once the service pack or hot fix setup program is started, it detects that you have a cluster and will upgrade all nodes simultaneously. Once setup is complete, you may need to reboot your servers and failover the nodes. Generally, once I have run the service pack, I like to reboot the active node first. Once it has rebooted, then I reboot the passive node. This way, failover and failback is automatic.

Checking the SQL Server 2005 Installation From Cluster Administrator

Once an instance of SQL Server 2005 clustering is installed, you can view its cluster resources by going to Cluster Administrator and opening up the SQL Server Group resource, as shown below.

image025

This figure shows the cluster resources for the SQL Server 2005 cluster we just built. We see all of the names of the resources, their state, and which node the resources are running on. As I have already mentioned, Cluster Administrator is a great tool for seeing if all the resources are up and running and which node is the current active node.

Here is a brief rundown on each of the SQL Server 2005 cluster resources:

  • Disk F: This is the shared disk array where the SQL Server data files and logs are stored.
  • SQL Network Name (sqlcluster): This is the virtual SQL Server name used by clients to connect to this clustered instance of SQL Server 2005. The name “sqlcluster” is the name I have assigned this cluster instance, and will not be the same as your cluster, unless you name yours the same as mine.
  • SQL IP Address (sqlcluster): This is the virtual SQL Server IP address used by clients to connect to this clustered instance of SQL Server 2005. Again, the name “sqlcluster” is the name of the virtual server, and is the one I have used for this cluster. Your name will most likely be different.
  • SQL Server: This is the SQL Server service.
  • SQL Server Agent: This is the SQL Server Agent service.
  • SQL Server FullText: This is the SQL Server FullText service. Even though you may not use this service, it is automatically installed as a cluster resource.

Clustering Analysis Services

SQL Server 2005 Analysis Services can be clustered just like SQL Server 2005, and in fact, is installed using the same setup program used to install SQL Server 2005. Below are some points to keep in mind if you should decide to cluster SQL Server 2005 Analysis Services.

  • SQL Server 2005 Analysis Services can be installed by itself, or with SQL Server 2005. Because some of the features of Analysis Services require components of the SQL Server 2005 database engine, it is generally a good idea to install both of them in your cluster.
  • SQL Server 2005 Analysis Services is installed using the same setup program as SQL Server 2005. When running the setup program, you select, or not select, Analysis Services to be installed in the “Components to Install” screen.
  • Because SQL Server 2005 Analysis Services needs to store program files, data files, and shared files, you will have to specify the location of your shared array, where they will reside. These files must reside on a shared array if you want Analysis Services to run after a failover. To specify the location of the shared array, you must select the “Advanced” button from the “Components to Install” screen in the setup wizard.

Other than the above, installing SQL Server 2005 Analysis Services in a cluster is virtually identical to installing SQL Server 2005 in a cluster.

Installing the Service Pack and Hot Fixes

Once you have installed SQL Server 2005 clustering, your next step is to install the latest SQL Server 2005 service pack and hot fixes, which can be downloaded from Microsoft’s Web site. Installing a service pack or hot fix is fairly straightforward because they are cluster-aware. Once the service pack or hot fix setup program is started, it detects that you have a cluster and will upgrade all nodes simultaneously. Once setup is complete, you may need to reboot your servers and failover the nodes. Generally, once I have run the service pack, I like to reboot the active node first. Once it has rebooted, then I reboot the passive node. This way, failover and failback is automatic.

Checking the SQL Server 2005 Installation From Cluster Administrator

Once an instance of SQL Server 2005 clustering is installed, you can view its cluster resources by going to Cluster Administrator and opening up the SQL Server Group resource, as shown below.

Note: I’m not the author of the above reference manual, I found it online, hope its helpful to you guys.

-Death

Follow

Get every new post delivered to your Inbox.

Join 57 other followers