Tag Archive: vmware

Over the past few evenings, I’ve been playing with SQLIO, to get an idea of how SSD compares to a couple of servers (one quite old, one a bit newer) that I have access too.

SQLIO can be used to do performance testing of an IO subsystem, prior to deploying SQL Server onto it. It doesn’t actually do anything specifically with SQL, it’s just IO.

If you haven’t looked at SQLIO, I would highly recommend looking at these websites:



The SQLIO Analyser, created by David Klee, is amazing. It allows you to run the SQLIO package (a preconfigured one is available on the site) and submit the results. It then generates an Excel file that contains various metrics. It’s nice!

Running on my Laptop…

Having run the pre-built package on my laptop, I got the following metrics out of it. As you can see, it’s an SSD  (Crucial M4 SSD), and pretty nippy.



Interesting metrics here, and one of the key benefits of an SSD, is that regardless of what you are doing, the average latency is so low. For these tests, I was getting:

Avg. Metrics Sequential Read Random Read Sequential Write Random Write
Latency (ms) 19.28ms 18.38ms 23.21ms 51.51ms
Avg IOPs 3777 3493 2930 1340
MB/s 236.07 218.3 183 83.7

Running on an older server

So, running this on an older server, connected to a much older (6-8+ years old) SAN gave me these results. You can see that the metrics are all much lower, and there is a much wider spread of for all the metrics, and that is down to the spinning disks.



As you can see from the metrics below, there is a significant drop in the performance of the server, a lot more variance across the load types.

Avg. Metrics Sequential Read Random Read Sequential Write Random Write
Latency (ms) 24.81ms 66.79 373 260
Avg IOPs 1928 710 186 210
MB/s 120 44.3 11.6 13.14

Slightly newer Server

So, next I had the SQLIO package running on a slightly newer server (with a higher spec I/O system, I was told), which gave the following results.



As expected, this did give generally better results, it is interesting that Sequential read had better throughput on the older server.

Avg. Metrics Sequential Read Random Read Sequential Write Random Write
Latency (ms) 35.13 44.17 41.81 77.44
Avg IOPs 1474 1021 1314 794
MB/s 92.7 63.8 82.8 49.6

Cracking open VMware

Since I use VMware Workstation for compartmentalising projects on my laptop, I thought I’d run this against a VM. The VM was running on the SSD (at the top of the post), so I could see how much of an impact the VMware drivers had on the process. This gave some interesting results, which you can see below. Obviously there is something screwy going on here, it’s not likely that the VM can perform that much faster than the drive it’s sitting on. Would be nice if it could though…



Avg. Metrics Sequential Read Random Read Sequential Write Random Write
Latency (ms) 7.8 7.5 7.63 7.71
Avg IOPs 12435 13119 15481 14965
MB/s 777 819 967 935

While the whole process was running, Task manager on the host machine was sitting at around 0-2% for disk utilisation, but the CPU was sitting at 50-60%. So, it was hardly touching the disk.



Just to summarise this, in case you didn’t already know, SSD’s are really quick. For the testing I was doing, the SSD was giving me approx. double the performance from some pretty expensive hardware (or at least it was 5-10 years ago…)

Also, take your test results with a grain of salt.


SQL Server ODBC on Linux

Disclaimer : I’m not a Linux Expert, and I’m sure that doing everything as root is bad, just like doing everything as a Domain Admin account is bad.

Having seen that the CTP version of the Microsoft SQL Server ODBC Driver for Linux has been released, I thought that it would be an interesting thing to play with. Particularly since it might be something I’ll interact with using C++.

Getting Ready

Officially, it’s supported on Red Hat Enterprise Linux, but I’ve not got that, and you have to pay for it (not much, but still). Having downloaded Fedora 16,installed it in a VM (VMWare Workstation), and fired it up, I needed to install a number of prerequisites.

Using the Add/Remove Software option in Applications –>System Tools, I installed these Packages:

  • Development Libraries
  • Development Tools

I also needed to install wget. Type it into Filter box, tick the box against the result and click Apply.

Then download the driver from here: http://www.microsoft.com/download/en/details.aspx?id=28160

Note, that you’ll also need the unixODBC Driver manager, and the current version is 2.3.1. I couldn’t get that working, but 2.3.0 does work, and is available to download here (unixODBC-2.3.0).

Installing it

To get everything to work, I downloaded the files into the Downloads directory, and follow the instructions on the MS Downloads page (copied below, and with an item (3) added by me to make life easier).

To install the driver manager:

  1. Make sure that you have root permissions.
  2. Navigate to the directory where you downloaded sqlncli-11.0.1720.0.tar.gz and extract it:
    cd ~/Downloads/
    tar xvf sqlncli-11.0.1720.0.tar.gz.
  3. (added by me) Copy the unixODBC-2.3.0.tar.gz file into the  sqlncli-11.0.1720.0 folder with
    cp unixODBC-2.3.0.tar.gz sqlncli-11.0.1720.0/
  4. Change to the sqlncli-11.0.1720.0 directory, where you can run build_dm.sh to install the unixODBC Driver Manager:
    cd ./sqlncli-11.0.1720.0
    ./build_dm.sh –help
  5. You can install the driver manager by executing the following command:
    Note: you can also download the driver manager manually at http://www.unixodbc.org/ and use the downloaded archive locally:
    ./build_dm.sh –download-url=file://unixODBC-2.3.0.tar.gz
  6. Type “YES” to proceed with unpacking the files. This part of the process can take up to five minutes to complete.
  7. After the script stops running, follow the instructions on the screen to install the unixODBC Driver Manager.

Next up, we need to install the driver, again, follow the instructions from the MS Download page (copied here):

To install the driver:

  1. Make sure that you have root permissions.
  2. Navigate to the directory where you downloaded sqlncli-11.0.1720.0.tar.gz and extract it:
    cd ~/Downloads/
    tar xvf sqlncli-11.0.1720.0.tar.gz.
  3. Change to the sqlncli-11.0.1720.0 directory, where you can run install.sh to install the driver:
    cd ./sqlncli-11.0.1720.0
    ./install.sh –help
  4. (Optional) You may want to make a backup of odbcinst.ini. The driver installation will update odbcinst.ini. odbcinst.ini contains the list of drivers that are registered with the unixODBC Driver Manager. Execute the following command to discover the location of odbcinst.ini on your computer:
    odbc_config –odbcinstini.
  5. Before you install the driver, you may run a verify step to check if your computer has the required software to support the Microsoft SQL Server ODBC Driver for Linux:
    ./install.sh verify
  6. When you are ready to install the Microsoft SQL Server ODBC Driver for Linux CTP, run the install script:
    ./install.sh install
  7. After reviewing the license agreement, type “YES” to continue with the installation.
  8. Verify that Microsoft SQL Server ODBC Driver for Linux CTP was registered successfully:
    odbcinst -q -d -n “SQL Server Native Client 11.0”

Resolving library issues

That then completed the installation. However, I did get a couple of issues when running sqlcmd. These issues were down to different versions of a couple of Linux SSL libraries being installed, rather than the expected version. Having had a root (pun not intended) around, the issues were resolved by adding a couple of symbolic links (kind of like shortcuts, kind of…), by doing this:

ln –s /lib64/libcrypto.so.1.0.0.e /lib64/libcrypto.so.6
ln –s /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6

Time to Play!

As if by magic, I can now query a SQL Server database, from Linux!


This was surprisingly straightforward I thought. My next thoughts will be to see if I can communicate with it from code (C++ since it’s Linux ).

Update – 26/1/2012

It’s been requested that I post the odbc.ini and odbcinst.ini files I used. These are shown below, and are unchanged by me.


<empty file>


[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux

As part of this ongoing series in creating a Virtual Cluster, this post will cover the creation of the Windows Cluster, using the Shared Storage created in Part 1.

MyClusterAs can be seen in the proposed architecture diagram to the left, we have a Shared Storage device, and next we’ll be setting up the Windows Servers, then adding the two SQL Server boxes together to create a cluster.

Part 3 of this series will be to create a SQL Server Cluster.

Step 1 – Creating the Windows Servers

I’m going to assume that you can install Windows Server 2008 R2.

We’ll be needing three instances of Windows Server 2008 R2. One running as a Domain controller, and two member servers.

So, go ahead and create those three VM’s, set one up as a domain controller, and add the two member servers to the domain.

Note that each of the Network cards in each server should have static IP Addresses.

For the specification of the servers, I’ve gone with this for the domain controller, since it doesn’t require a huge amount of resources.


The two member server’s need an additional network card for the heartbeat connection. To achieve this, both these server’s have an additional network adapter, configured on a private network.


Once the two member servers are up and running, go to Server Manager, right click on Features and Tick the Failover Clustering option. Click through the Wizard to install it. Do this on both Member servers.

Step 2 – Adding the iSCSI Targets on Member Server 1

Next, we need to add the iSCSI Target created previously, to the Windows Server. Do this by going to Start -> Administrative Tools and iSCSI Initiator. In here, go to the Discovery Tab, and click Add Portal


Then, you’ll be able to click on the Targets tab, and you should see the drives hosted on the FreeNAS VM.


Highlight each disk (starting with the Quorum disk) and click the Log on Button. Tick the ‘Automatically restore this connection when the computer starts’ tickbox, and click Ok. Repeat this step for each disk.


Next, you’ll be able to go to Disk Management, and you’ll see the disks you’ve just created. For each disk, you need to Bring them online, Initialise them, Create a Simple Volume on them using NTFS, and then take them offline again.


Step 3 – Setting up Member Server 2

Next, we need to add the iSCSI drives to the 2nd Member Server. So go to Start -> Administrative Tools and launch iSCSI Initiator. If you are prompted to Start the Service, say yes. As before, go to the Discovery tab, click Add Portal, enter the IP Address of the FreeNAS VM, and click OK.

Go to the Targets tab, and Log on to each of the drives, and ensure you tick the Automatically log on tickbox.

Next, go to Disk Management in Server Manager, and you should see the drives, Offline, and with Blue lines, indicating the drives are configured.

Step 4 – Validating the Cluster

One of the really helpful features of Windows Server 2008 R2 is the ability to validate your cluster configuration, prior to creating it. It runs a whole long list of tests, and tells you how it got on. Tests include the Disk Subsystem (can both members connect to the drives), Networking (is there a Heartbeat card in both, that communicates, IP Address checks, etc) and checking all the services are configured appropriately.

To validate your Cluster, go to Start -> Administrative Tools and Failover Cluster Management. Then click Validate a Configuration…


The Wizard will start, and page 2 will prompt you for your server names. Enter these, or browse for them


Click Next, Click Next to choose to Run all the tests, and click Next to run them. It takes a little while to run them all. All being well, you’ll get a list of Green, followed by a screen similar to this:


“If the light is green, the trap is clean” – Link

Note: If your validation fails due to an IP Address conflict, and you can’t see a network card that is causing it (since all your servers have Static IP Addresses), there may be an issue with the ‘Teredo Tunneling Pseudo interface’. This can be resolved by going to Device Manager, go to View and click Show Hidden Devices. Right Click on the Teredo Tunneling Pseudo-Interface and choose Disable. Then revalidate your Cluster configuration.


Step 5 – Creating the Cluster

Once we’ve successfully Validated the cluster, we can create the cluster. This can be done by Clicking ‘Create a Cluster…’.


You’ll be presented with a Wizard. Click Next, and enter the names of the two member servers and click next. Enter the name of the Cluster, and if you have DHCP, the IP address will be configured. If you aren’t using DHCP, it’ll need to be specified. Click next, and the information will be confirmed. Click Next and your Cluster will be created.


If everything is successful, you’ll get a confirmation screen, and confirmation of the Quorum type configured.


You’ll then be able to see the details in Failover Cluster Management, showing the various disks, and the drives will appear in Windows Explorer, showing the Clustered Disks.


So that’s Part 2 completed, we now have a Shared Storage device, and a Windows Cluster running off it, using 4 VM’s. Next we need to install SQL Server on the Cluster to get it doing something.

As part of my ongoing work towards the MCM Certification in SQL Server, something I wanted to look into was the creation of a cluster to play with. It turns out this isn’t a straightforward as you might think (!).

DISCLAIMER – This isn’t a plan for a live production environment. It is a plan for a set of Virtual Machines, that you can use to play with.

DISCLAIMER 2 – This is most certainly not a guide on how to use FreeNAS. This method works, but I make no claims that this is the best way to do it, as I’m certainly not a FreeNAS Expert.

MyClusterTo get the process around creating a cluster together, I’m going to write a few blog posts:

Part 1 – Creating the storage (This post)
Part 2 – Creating the Windows Cluster (Pending)
Part 3 – Creating the SQL Server Cluster (Pending)

There may be additional parts…

I’m anticipating creating a network as shown in the diagram to the right.

It’ll be a Windows Domain, so there’ll be a Domain controller (VMLAN-DC), two SQL Server’s which’ll eventually be running in a Cluster.

One thing that a Cluster needs is a shared storage area. To achieve this, I’m going to use FreeNAS, which is an Open Source Storage Platform. It’ll allow me to create ISCSI-3 drives and since Requirements for Windows clustering specify Serial attached SCSI, Fibre Channel or ISCSI for storage, that’ll work.

Step 1 – Download FreeNAS

I’m going to use FreeNAS 8 since that is the current (at this date) version. It’s a free download, so go here, and set it downloading (approx. 130mb).

Step 2 – Start your Virtualisation

Next up, start your virtualisation engine of choice. I’m going to use VMWare Workstation 8, as it’s always served me well in the past, and it gives me flexibility that I need. I imagine this process should work with other Virtualisation platforms. Create yourself a VM, and I’m giving it the following specification:


2 Gb RAM, which I may reduce after testing, and just the one core.

Hard Disk 1 will be used by FreeNAS.

Hard Disk 2 will be the Quorum disk (used for Clustering).

Hard Disks 3-6 will be available for shared storage (Data, Log, Tempdb, Backup).

Virtually insert the ISO Image, and start the VM.


Choose Option 1 to Install to a Hard Drive


Choose da0 to install to the appropriate Disk.


Click Yes, to continue with the installation.


Upon completion, Press OK, Choose 4 to Shutdown the machine, and when it’s done, ‘eject’ the ISO Image.

Then restart the VM. When it has completed starting up, you’ll be presented with a screen similar to this:


The important bit here is the web address at the bottom. Open that up in a browser.


Step 3 – Configuring FreeNAS

The first thing to do is assign a password, as this’ll get rid of the annoying red light in the top right corner. Click the Account Icon, choose the Change Password option and enter a new password.

The next thing we need to do is to add the storage devices into FreeNAS. Do this by clicking on the Storage icon at the top, then click on Create Volume


Then enter the volume name, pick the Member disks and choose ZFS as the filesystem, then click Add Volume. I’m going to create them as follows:

quorum – da1
disk1 – da2
disk2 – da3
disk3 – da4
backup – da5

This should give you the following results:


Step 4 – Configuring iSCSI

Next click on the Services icon, and click the spanner next to the iSCSI service.


In here, you’ll have a number of tabs. First, we’ll need to create a Portal:


So, click the Portals tab, and click Add Portal. The IP Address of the server should appear in the box, if you have, replace it with the IP Address of the server, so it should be <ipaddress>:3260. Then click ok.


Next click, Authorized Initiator, to add an Initiator. Leave the defaults here (ALL and ALL) and click Ok.


Next go to the Targets tab, and click Add Target. You need to add a target for each disk, so for each, you’ll need to add a name, choose Disk as Type, Choose 1 for Portal Group and 1 for Initiator Group, and click OK. Repeat this for each disk. This should result in this screen:


Next we need to add extents to the NAS, so click on the Extents tab, and click Add Extent. Again, we need to create one for each disk, so enter a name, choose the path, and add /<diskname> to the end, and specify the extent size, which’ll be 10240MB for the 10gb disks. Repeat this for each disk.


So, you’ll have these extents:


Next we need to associate the Targets we created earlier, with the Extents. Click on the Associated Targets tab, click Add Extent to Target, and for each disk, pick the Target and Extent to match.

Next, click the System Icon, click Reboot, and let the system reboot. When it comes back up, log in to the Web UI again, click the Services icon, and click the On/Off Toggle next to iSCSI and it should start.


Step 5 – Testing iSCSI

The easiest way to test that the iSCSI connection works is to run the iSCSI Initiator, which’ll be on a server, or your Windows 7 Desktop. Go to Start -> Administrative tools and click iSCSI Initiator. It’ll ask to start the service. Say yes.

Then, in the Target box at the top, enter the IP Address of your FreeNAS VM, and click Quick Connect. If all is working, you’ll be presented with the list of disks from the FreeNAS VM.


Congratulations, you’ve created a FreeNAS Environment for your Cluster!

Next up, creating the Windows Cluster