Category: Certification

On Friday, I was looking to get the SNMP integration working on SQL Server. However, it turns out that to get this functionality, you need to have the SNMP server running when SQL Server is installed. Sadly this wasn’t the case. To fix this, I needed to uninstall SQL, check the SNMP server was running (as I’d now installed it) and reinstall SQL.

Sounds straightforward doesn’t it.. It wasn’t.

Uninstalling was the most straightforward part. When I tried to reinstall, I was told that the version of Visual Studio 2008 needed to be Service Packed (SP1), so I did that. Then when i tried to reinstall again, I started getting lots of other error messages which appeared to have assorted issues.

The resolutions to these issues were around corrupt registry settings (I cleaned those out), corrupt setup files (I changed the install location, which seemed a little random, as a fix) and corrupt ISO images (downloaded a fresh install, and I love my MSDN account, and the Fibre Internet connection!). None of these worked.

The last item I saw, at 10:30pm, on a Saturday night was to create a slipstream build of SQL Server 2008 R2, with SP1 in it, since this would resolve the issues in the installer files. I found a really useful article on creating this (, and having followed that through, SQL successfully installed.

While this was a complete pain to fix, and did have me wondering if I’d need to rebuild the laptop, since I knew the ISO was fine (as I’d installed a VM from it during this whole process), it did help me as it’s been quite a while since I’ve had to deal with installer issues. Also, I’ve certainly never created a slipstream build of SQL Server, which I can now do.

It’s all good MCM Prep. 🙂

Update 16/12/2011 :  I’ve Passed the exam! If you are taking it, the very best of luck!

I’m writing this now, as I have the exam on Monday, and after that point any comments I make on the exam will be influenced by taking it, and therefore restricted by the NDA.

So, here are a list of the Resources I’ve used to prepare for this exam:

Microsoft Exam Preparation Page – This has alot of links to Whitepapers, and books. I’ve read almost all of the whitepapers (all of them by Monday), and 60% of the books.

Online Training videos / Associated Resources – These are a phenomenal resource. Specific, highly detailed training by Paul Randal, Kimberley Tripp, Brent Ozar and Bob Beauchemin. These are useful, even if you don’t want to do the Master Certification.

SQL Skills Training IE1 – I attended the UK course held by SQL Skills in the UK. Ideally, I’d have attended all 4, but due to Training budgets and Time, I couldn’t. This was a great course, and I highly recommend their courses. Being taught SQL Server by such highly skilled Trainers (Paul Randal and Kimberley Tripp) is an amazing experience.

Community Training Notes – Hosted by Neil Hambly (who was also on the IE1 training), this is a great resource also, and has sections of all of the areas an MCM should know. Thanks Neil.

Community events – There are many, SQLBits, SQLPass (24Hours of PASS, and the Nordic Rally event, in particular), SQL Maidenhead, SQL in the Evening, they are all really helpful, and thank you.

SQLBits also have a huge number of videos of the conference sessions available.

Of course, on top of this is the past 12 years of experience I’ve had with SQL Server (though the most has been over the past  7-8 years). For that, I’d like to thank my previous employer JI Software and most of all to my current employer, TAH Limited. I’ve been fortunate enough to work on some really challenging Data warehousing projects over the past few years, including a huge one for Vodafone (read more here).

If you are looking to do the certification, the very best of luck to you!

I passed this exam on Friday. This was a hard one. Having taken it just over 2 months ago, and failing it, I changed the way I prepared for exams.

Previously, I’d been reading the material on the MS Learning Plan for the exam, and any associated books. For the 70-451 exam I did 6 weeks or so back, I did alot of reading of blog articles, MSDN and Books Online. For this exam however, I didn’t find the Learning plan overly helpful, and there are a huge amount of blog articles on Database Administration (some of which is contradictory).

So, having failed the exam, which isn’t a bad thing, as it proves that the exams aren’t so straightforward that everyone can pass first time, I made notes on the areas I was weakest. Then looked for relevant articles in those areas. The difficult part was finding blog articles by people who knew what they were talking about. A couple of the most useful were John Sansom (particularly the Something for the Weekend: SQL Server Links posts), and the articles from Jonathan Kehayias from SQLSkills (particularly those on Extended Events and Clustering). Thanks to you both!!

I did also find a useful book, and even better, it was one I already had. SQL Server 2008 Administrator’s Pocket Consultant (2nd Edition) by William Stanek was a great help, and while I’d read bits and pieces before, going through it from cover to cover was really helpful.

Having completed this exam, I’ve now completed the prerequisites for the SQL Master Certification, and I’ll be looking to take the Knowledge exam before Christmas.

First though, I’m going to have a break from studying for SQL Server exams. Just for a couple of days. Something different for a change, PowerShell or C++. We’ll see… Just for a couple of days though…

This is part 3 of a set of posts around creating a Virtual cluster on SQL Server, which I’m creating to raise my awareness of clustering.

Part 1 of this series created the Shared Storage used by the cluster, using FreeNAS.MyCluster

Part 2 of this series created a Windows Cluster using this storage.

This section will cover creating a SQL Server cluster using this environment.

The architecture diagram to the right shows the cluster we are creating. This post will cover the clustering of SQL1 and SQL2.

Step 1 – Starting the Process

The first step is to get the SQL Server media available to the server. I’ve taken a copy of the DVD and placed it on the C Drive on the first node of the cluster.

Run Setup.exe, which’ll install some pre-requisites (you should do this on both nodes, to make for an easier life). When the server’s have restarted, run setup.exe on the active node (SQL1 in my case).

Within SQL Server Installation Center, click the Installation option on the Left, and choose New SQL Server Failover cluster Installation.


The Wizard will start, and as normal, will take you through installing the setup support files. Once this has completed, the Setup support rules will run, and any issues will be flagged. This includes a set of checks around Clustering.


The one that has a warning here, checking to see if MSDTC is clustered, effectively means that there may be issues with linked server queries, and SSIS. It’s not required for the Cluster to build however, so lets press on. Clicking Next gives us the Feature Selection screen.


I’m only going to install the Database engine, Integration Services and Management tools. Click Next.

In the Instance Configuration screen, enter the name for the SQL Cluster.(SQLCluster in my case).


Click next to confirm the disk usage requirements, Confirm the name of the Cluster Resource group (selecting the default).


Click next, and we are prompted to select the disks for the SQL Cluster. I’m selecting all the unused disks.


Click Next, and confirm the Network settings for the Cluster.


Click next and confirm the Cluster Security Policy (the default being to use the Service SIDs).


Click Next to continue,  for the Services, provide a Domain account, and click next.

Next you are prompted for Database engine configuration as normal, click Next, Click Next on the Error Reporting screen. The next screen is the Cluster Installation rules. These should all be passed, so click Next.


Click next and next to start the Installation. The installation will take a little while to run.

Upon completion, you’ll get this:


If you now look in the Failover Cluster Manager, you’ll see the SQL Cluster in Services and Applications.


Next, you can fire up SQL Server Management Studio and connect to the Cluster.

If you go to Server Properties, you can see that the Server is Clustered.


The Default System databases (Master, MSDB, Model and Tempdb) are all now stored in one of the cluster drives, and the default drive for databases will also be that drive.

Step 2 – Adding the Second Node

Next we need to add the second node to the Cluster, so switch to your other Member server, and run setup.exe, from the Installation section choose ‘Add node to a SQL Server failover cluster’.

Run through the installation screens, and note that it should pickup the SQL Server Cluster name when you get to the Cluster Node configuration screen.


Click Next, enter the passwords for the Domain accounts on the on the Service accounts page, and click next on the Error Reporting page.

The Add Node Rules page should complete successfully, so click Next.


Then Click Install to continue.

When this has been successful, you’ll see:


Step 3 – Test it out

I’ve taken a backup of the AdventureWorks DW 2008 R2 database from my laptop and copied the backup to SQL1. Note that to restore a database, it must be on one of the Cluster drives, so having copied the Backup file to the shared drives, I can now restore it.


Having restored this database, I can now run queries against it, and see what activity is taking place, through the performance graphs on FreeNAS. You can see from the graphs below that are spikes when the Cluster nodes were connecting, and the then later when the database was copied over and restored.




The next post will be around optimising and getting some performance metrics off this cluster.

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

One more SQL Server 2008 Certification under my belt. I passed the ‘PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008’ exam on Friday, which gives me the MS ITP (IT Professional) Database Developer 2008 certification.


To get through this certification, I used a number of sources, however, one of the most useful was a blog series by Eric Wisdahl ( where he covered each of the sections listed in the exam topics.

Next up will be the 70-450 DBA certification. Once I’ve completed this exam, I’ll have completed the prerequisites for the MCM SQL Server 2008 certification, which is then another two exams!

Interestingly, to me at least, this was my 18th Microsoft certification, having taking my first back in 1999 (Implementing and Supporting NT Server 4.0)!.

Since then I’ve done:

5 x Windows NT 4
1 x Windows 2000 Professional
2 x Microsoft CRM v1.2
3 x Microsoft CRM v3
2 x SharePoint 2007 / WSS 3
1 x SQL Server 2005
4 x SQL Server 2008

And, I’m planning to do another 3 in the next 6 months.

Certified or Certifiable, you decide!

So that’s another one completed, Smile which is the third of the SQL 2008 TS level exams (there should be an Achievement for that – ‘SQL Nutter – SQL TS Hat Trick Unlocked!’.

I took the exam this afternoon, and it went better than I expected, though not as well as some colleagues thought! However, the revision material I used was predominantly experience based, though I did also use the MS Press Training book (available here, at Amazon) to focus in on the exam requirements.

I’ve been running through the MCM training material that SQL Skills and Microsoft have provided (, since that is my end goal!

Next up will be the 70-450 exam sometime in July, which is the first PRO level exam I’ll have done! I would do it sooner, but I’m attending the SQLSkills Immersion course in London in June, and from what I’ve heard that’ll be a little intense. Cool!

And how did I celebrate passing the exam ? With a great friend, Colonel Sanders.


Well, that’s a mouthful for a title.

I took this exam this afternoon and passed that with 826.

To prepare for it I used the Microsoft Press book (on here at Amazon UK), and also used the Transcender exam.

I found the Transcender exam to be really helpful, and was substantially easier to use than the MeasureUp exam that was supplied with the MS Press exam.

As an aside, I would highly recommend Mass Systems (in Wokingham) for taking certifications as they have decent LCD screens, rather than CRT’s (as at some other providers)..

So I took the SQL Server 2008 Database Developer exam today, and passed (Hooray!!), with 715. It was a close one. I was expecting to do better on that, as I’ve got project experience and went through the Microsoft Press training book on this (here on Amazon UK).

I’ve got the 70-448 SQL Server 2008 BI Development and Maintenance exam in a few weeks time, so I think I’ll get the Transcender exam for that as well.