Strobaek Blog

I have a couple of times had to set up a SQL Server cluster, both at clients and in my own lab. At clients the underlying Windows cluster setup is often handled by their own infrastructure people, so this is seldom a problem – if we assume they know what they are doing, which I grant, is not always the case. However, as I don’t have an ops person standing around behind glass, to be called upon when I need her – it should of course be a her – I need to do it myself (reminds me of a saying I once heard: it is cheaper to do it yourself, but it is a lot more fun with someone else. Can’t remember what it was about …)

This blog post will describe the different steps required to create a small SQL Cluster with two nodes. The following is assumed:

• You have a domain controller. Mine is running Windows Server 2008, but you can use an earlier version if you wish to have a small footprint.
• You have installed Windows Server 2008 R2 on three servers. Two will be used for the cluster nodes and one as shared storage. All three servers have been joined to the domain and preferable been given a static IP-address.

Until Windows 8 I am running my lab using VirtualBox from Oracle. This allows me to run 64-bit virtual machines on my Windows 7 laptop, without having to boot into WS2008.

In summary we will construct the following:

• Two SQL2008R2 nodes running on WS2008R2
• One WS2008R2 server used as shared storage.

 

First thing to do is to create a Windows cluster. I use iSCSI and install the Microsoft iSCSI Software Target 3.3 on the storage box. There are other options. The important thing is, that you cannot create a SQL cluster, if the nodes are not able to see the same disk.

Run the install to unpack the files. This will open a web-page (index.htm). Select Install.

 

Accept all the default values and the install is completed swiftly.

Start the Microsoft iSCSI Software Target console.

Select the iSCSI Target node, right click and select Create iSCSI Target.

Enter a name for the iSCSI target and alternatively a description. Note, that the name cannot contain spaces.

In the iSCSI Initiators Identifiers dialog, press the Advanced button.

Enter the IP-address of the two nodes on which the SQL cluster will be installed.

Finish the target setup.

Right click the newly created iSCSI Target and select Create Virtual Disk for iSCSI Target.

Set location and size of virtual disk and finish the wizard. Upon completion you should see the newly created vdisk under the iSCSI target.

 

Next step is to connect the just created storage with the two cluster nodes.

On one of the nodes, open the Control Panel and select iSCSI Initiator.

If you see the following dialog

just select Yes.

Enter the IP-address of the Storage box and press Quick Connect.

You should now see the storage server under Discovered targets.

Go to the Volumes and Devices tab a press Auto Configure.

You should now see an entry under Volume List.

Click OK to close the iSCSI configuration.

Go to the second node and repeat the above steps.

Open the Server Manager and go to Storage and Disk Management. You should see the disk here.

Now go back to the first node, set the disk online and initialize it. This step will ensure that the disk is visible in the Cluster Manager when assigning disks.

We now have the foundation to setup a Windows Cluster. Next step is to enable clustering on both nodes. Start the Server Manager on each node, select Features and Add Feature.

Check the Failvoer Clustering click Next and complete the setup.

On one of the nodes go to Start -> All Programs -> Administrative Tools and select Failover Cluster Manager.

Select Create a Cluster in the middle of the page.

Add the two nodes to the list of servers that should be included in the cluster.

In the next step one can run a verification report. It will take some time to complete the report, but I recommend that it is done to avoid any needless frustration later on.

Give the cluster a name and an IP address.

Press Next and finish the setup.

To complete the setup the disk from before needs to be allocated or assigned to the cluster. Select Storage in the tree to the left and click Add a disk to the right.

This should display something similar to the following:

 

Press OK to continue.

So far so good. We now have the foundation on which we can install SQL Server.

SQL Sever 2008 R2 requires the .NET Framework 3.5. If you have not already done so, add it to your servers using the Server Manage and Add Feature.

On the node that “owns” the shared disk, run the installation. I know that it is not really correct to talk about ownership as it is the service that owns the resource and not the node itself, but I think you get the picture.

From the Installation Center, select Installation and then the second option New SQL Server failover cluster installation. The third option will be used to add node(s) to the cluster, once it has been set up.

Continue through the dialogs until the Setup Support files are installed. Once this has completed a “report” is run to identify any issues. There should be no Red Lights, and only Yellow of no importance or  that can be rectified later, e.g. the network binding order.

On the Feature Selection page, select the features that should be installed and press Next.

On the Instanced Configuration page, a SQL Server Network Name must be specified. This is the “virtual” name applications will use to connect to.

Accept the default values for the Cluster Resource Group and the Cluster Disk Selection.

On the Cluster Network Configuration specify an IP-address.

Continue through the setup until the Database Engine Configuration.

Supply the information for the Account Provisioning. On the Data Directories tab it can be seen that the shared or clustered drive has been selected. If you had multiple disks in your cluster and had selected them in the Cluster Disk Selected step, it would here be possible to place the data one one disk and the logs on another. For this test-lab all is installed on the same disk.

Continue with the configuration and end by pressing the Install button.

At this point it would be a good idea to get a cup of coffee as the installation might take some time.

Eventually something like this should be displayed

We have now configured and installed the first node in our failover SQL Cluster and we now just have to add the second node and we are done.

Start the install from the second node, select the Installation menu item, and as previous noted, select the third menu option Add node to a SQL Server failover cluster.

As before the installation process will begin by installing a number of setup files.

On the Cluster Node Configuration screen select the cluster to join.

Continue through the rest of the setup and press Install at the end.

If all goes well, the completion screen will be displayed.

That’s it! We now have a two node SQL failover cluster.

I recently had to install a SQL Server 2008 R2 cluster at a client. My previous experience was, that once the underlying Windows cluster and the shared storage, e.g. in form of a SAN have been set up, the installation of SQL server is relatively straight forward.

Well, not this time around.

Before you begin the installation of the SQL cluster, you can run some verifying tests of the Windows cluster. These were all green. During the SQL installation process all the Setup Support Rules are checked, and these were also all green.

The actual installation completed, but at the very end I got the following error:

Looking in the event log, the following error could be seen:

Cluster network name resource ‘SQL Network Name (BJSQCON)’ failed to create its associated computer object in domain ‘xxxx.com’ for the following reason: Unable to create computer account.

The text for the associated error code is: Access is denied.

“Access is denied”. This sounds like an AD problem, but the installing user should have all the required access rights (this at least according to the infrastructure team).

After some investigation I opened the (advanced) properties in AD for the computer object of the SQL cluster (sorry for the black; client confidentiality)

What was missing was the computer object for the Windows cluster, here shown after being added.

So after having pre-staged the AD, I removed the SQL node from the cluster and tried the installation again and this time all went fine.

Interested parties can read more about Failover Cluster setup and pre-staging here http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_precreating

I was recently asked to help a client migrate some data from IBM DB2 to SQL Server.

It was about 900 tables and a couple of terabytes of data. Before I came onsite, the client had extracted the data from DB2, a process which alone took a couple of days.

Was I was presented with was the following: one file for each table containing INSERT INTO statements for each row to be inserted into SQL. The largest file was about 2 GB, the smallest one a few K. Due to time constraints it was not possible to re-extract the data into a format there e.g. BULK INSERT or SSMI could be used.

The client had tried to import one of the larger ones – around 1.3 GB with about 3.2 million lines – using the command utility OSQL. After 14 hours we stopped the process.

My first thought was to insert explicit transactions into the files getting a structure of something like this:

BEGIN TRANSACTION
INSERT INTO …
…
INSERT INTO …
COMMIT TRANSACTION

I wrote a small utility which would transform the original files into the above structure given the overall size of the file and maximum allowed batch size, e.g. lines in each transaction.

This did not really help at all. The aforementioned file still took ages.

As we were running out of time, a request was made to upgrade the SQL server from 2 to 4 cores and to 16 GB Ram. This of course did not solve our import problem, but at least we got some more juice to play with.

I next made two changes, which turned out to solve the problem:

1. Used SQLCMD instead of OSQL
2. Split input files up into smaller chunks

SQLCMD was introduces with SQL Server 2005. It is using OLE DB and not ODBC for connectivity and it is 64 bit. Both of these will of course speed up performance.

To split up the files into smaller chunks I found a small utility called TextFileSplitter. The utility is free to use and comes with both a UI and command line interface.

Writing a small CMD-files automated the process of splitting up the largest files into chunks of 100K lines each.

SET INPUT=C:\Input
SET OUTPUT=C:\Output
SET LINES=100000

FOR %%F IN (%INPUT%\*.*) DO TextFileSplitterConsole -i=%%F -o=%OUTPUT% -splitstrategy:ls:%LINES%

 

Once we had the smaller files I started three other “CMD-file processes” to execute all the INSERT INTO statements using SQLCMD.

All in all it took a couple of hours for the splitting and inserting of data.

End result: happy client.