Changing IP Settings on an SQL Cluster

This simple five minute job during an implementation  really threw me.

To paint a picture there is a three node SQL cluster with two instances (2 Active nodes, one Passive) these are isolated from clients behind a firewall.

To facilitate a hardened firewall policy to permit only TCP 1433 to the instance resource group IP addresses, as well as  ensuring only the instance resource group IP address listens on that port (opposed to the default ALL IP’s setting) some changes are required to the network settings in SQL configuration manager.

On a standalone SQL server, it’s simply a matter of changing the settings using the Configuration Manager GUI, restarting the SQL service and the change takes effect. However when in a cluster the changes revert back to the previous ones immediately after clicking ok.

After venturing into this issue a bit more I discovered what I was trying to do wasn’t really documented any ware, but some other articles pointed me in the general direction of the joys of quorum in clustering. In a nutshell I was making a change on one box but as the registry settings being changed are managed by the cluster service the the other two nodes in the cluster won quorum and overwrote the settings.

To change these settings the cluster the reservation checkpoint for the registry path needs to be removed, the changes made in the registry and then the cluster reservation checkpoint added again.

The first step is to get the checkpoint name of the instance you are going to modify, run the following command:

Cluster res /checkpoints

Once you have the instance name, take the SQL server offline in failover cluster manager and run the following command:

cluster res "SQL Server (INSTANCENAME)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.INSTANCENAME\MSSQLSERVER"

You should now edit the registry or use SQL configuration manager to make the changes you wish to make.
Personally I prefer to edit the registry as this enables you to delete the unused IP addresses and just leave the cluster IP in place, which is much tidyer.
The path to edit the registry settings is

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.INSTANCENAME\MSSQLServer\SuperSocketNetLib\Tcp\

Delete any of the IPx keys you don’t need but leave IPAll
To specify the port for the IP address to listen on, simply modify the TCPPort value, and remove the value from the TcpDynamicPorts
Once you are happy with the changes, run the following command to add the checkpoit back into clustering

cluster res "SQL Server (INSTANCENAME)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.INSTANCENAME\MSSQLSERVER"

Bring the SQL Server resource back online and check SQL configuration manager, the changes should have taken affect.

As a result of this change, your firewall rules will be more secure as the massive dynamic port range doesn’t need to be permitted and if need be both SQL instances can be failed over to the one server without ports comflicting. There is also the added bonus that the IP configuration in SQL configuration manager looks a whole lot tidyer.

Are SAN’s “old hat”? Bring on the DAS

Here goes my first “opinion” post opposed to one detailing a useful command or script.

I’m in the middle of a greenfield infrastructure redesign at the moment; a topic that has been playing on my mind is SAN vs. DAS. When I say SAN, I’m talking about a storage area network. That’s several trays of disk attached to a SAN head unit, which is then connected to a pair of fibre switches, or to a 10gig switch via Ethernet. Servers are then connected to the switches (via Fibre or Ethernet). When I say DAS, I’m talking about Direct Attached Storage. That’s several trays of disk attached to a DAS head unit, which is then connected to a number of servers via SAS cables, or indeed just a dumb tray or trays of disk connected directly to the server.

So, what are SAN’s traditionally used for? In a basic sense they present a large (or small) amount of scalable storage to a number of servers.  Why do these servers need this storage? Either because the server hosting the application needs more disk space or spindles than you can fit into the server chassis, or if you are utilising clustering and need shared storage between two servers.

Clustering is what I think changes things. It has always struck me that you build clusters with multiple nodes, NIC’s, power supplies etc to offer high availability and yet the data is still in one place. Therefore  the SAN  is effectively a single point of failure.

Traditional Cluster

Although a SAN itself will have no single point of failure (dual controllers, multiple paths etc.) the data is still on a single RAID volume so could potentially be a victim of bitrot or the RAID group having a hole punched in it. There is also the obvious risk that the physical file could become corrupt. Software vendors are obviously thinking the same.  Exchange 2003/2007 was made highly available in the traditional cluster sense (multiple nodes with the DB on shared storage).

In Exchange 2010 however you have the concept of DAG’s. With DAG’s the database itself is replicated to nodes rather than being shared.  This means a SAN is not required to provide a highly available exchange environment.  If you can find a server with enough capacity you can run two (or three, or four) exchange servers in a DAG and have mailbox databases failover between them. This is actually more resilient than a traditional exchange cluster because the databases are being replicated rather than shared, which means you have protection against a corrupted database, as well as hardware failure.

Exchange 2010 DAG.

The upcoming SQL 2012 “always on” feature works in a very similar way to DAG’s. The selected databases are replicated between cluster nodes.  This means you can now have two core business systems (Exchange and SQL) made highly available without needing any kind of shared storage.

Failover clustering in itself is also moving forward with “shared storage-less clusters”. You can create a cluster and use a file share as a witness, which means that’s another requirement for shared storage out of the window!

If you have services you would like to make highly available and they don’t require a common area to write to, you can easily make them highly available in failover clustering by using a file share witness. If a service does require a common area to read or write data to, then you could always create the directory locally on each server and use DFS replication to keep them in sync.

This brings me on nicely to DAS. With applications moving to a model where shared storage isn’t required, the only real reason you would need a SAN is to present more storage or spindles to a server.  Because there isn’t the need for multiple servers to all access a common bit of storage, DAS comes into play. You can buy a dumb tray of 12 disks that can have additional trays daisy chained off of it to provide around 120ish disks for about  £7k per tray (the Dell Powervault MD1200 for example) these can be dual connected to a single host. Or if you want to connect more hosts to the DAS solution, you can get an “intelligent” DAS head unit that can then have multiple “dumb” trays connected to it to provide 192ish disks. These can usually support four dual connected hosts and can be picked up for about £12k. (the Dell Powervault MD3200 for example)

There are still applications that require shared storage, such as Hyper-V or VMware for example. In this scenario the MD3200 (intelligent) with a few MD1200’s (Dumb) connected to it would be ideal. You can have four nodes in the cluster sharing the storage.

The initial reaction I get to this suggestion is that of shock, as it’s not very scalable like a SAN. I understand the argument, but on the flip side, do you really want 10 – 20 hosts sharing the backplane of your SAN (6 – 12gig) with the DAS solution those four hosts are sharing the dual 6gig backplane.  If you need more servers then you’ll probably need more storage, so buy another head unit instead of a dumb tray. This method leaves you with two clusters of four nodes each with their own 12gig backplane (2x 6gig) opposed to potentially eight nodes sharing the SAN’s backplane.

I’m a big fan of DAS over a SAN for several reasons:

·         The physical trays are cheaper than trays for a SAN

·         There is no requirement for fibre switches which are eye wateringly expensive, not only for the tin but also the port licencing

·         DAS is really simple as the cable goes from the head unit to the server. Simple is fast and also easy to support and fix when it goes wrong.

·         DAS removes a single point of failure. It’s affordable to build two SQL clusters attached to 2 DAS arrays. Unless you’re a fortune 500 company you wouldn’t be able to do this with a SAN.

I can also see the downsides of DAS vs.SAN

·         Physical limit of SAS cables mean your servers need to be near the DAS head unit.

·         The administrative overhead of many storage arrays vs one SAN.

·         DAS lacks some of the mirroring features that SAN’s do.

Based on the above though, I think the cost savings by going DAS in both financial terms and for simplicity, outweighs the disadvantages.

I’m open to constructive feedback on this; I still have an open mind on the subject. However at the moment I think SAN’s are a thing of the past in 90% of situations.

A quick way to set calendar permissions using Powershell

A Common request from users is to grant others access to their calendars.
You can either talk the user through this, or setup a new outlook profile to open their mailbox and set it yourself using the GUI – both are time consuming.
This simple powershell command allows you to set permissions with ease:

add-mailboxfolderpermission -identity USERNAME:\calendar -user "Username of person who needs access" -accessrights reviewer

The Identity switch needs to be the username of the mailbox which you are giving access TO, the user switch is the user you are giving access FROM.
The accessrights switch is the level of access you wish to grant the user, the link below lists some additional switches you can use:

http://technet.microsoft.com/en-us/library/dd298062.aspx

Using a PAC file to set proxy settings

There are many ways to configure proxy settings, via a GPO, via a build, or an application.

Proxy settings can cause issues for mobile users if they use their device away from the corporate LAN as the proxy server will not be reachable, this will render the internet browser unusable (unless of course Direct Access has been implemented)

There are many solutions to this problem, some common ones are:
1. Teach users to enable and disable proxy settings, This is not the most elegant solution, is likely to cause a fair amount of support calls, and also means proxy settings cannot be enforced.
2. Run a 3rd party app that users can click on and select proxy on or proxy off. Im not a fan of these types of applications that sit there and use up resources for no real reason.

3. Run a login script that sets the proxy setting if you are connected to the corporate LAN, and doesn’t if you are not. This is a long winded way of doing it, and is not 100% effective.

In my opinion, the most effective and efficient way of configuring proxy settings is to use a proxy auto config file (PAC)
A PAC file contains a JavaScript function "FindProxyForURL(url, host)". This function returns a string with one or more access method specifications. These specifications cause the user agent to use a particular proxy server or to connect directly
.
You configure your browser (works in all popular browsers) to use a script to configure proxy settings, this setting remains in place permantly. If the PAC file is placed on a web server accessible only within the corporate LAN, if the user is away from the LAN, the config file is not found, so therefore a proxy is not used.

When the user is within the LAN, the file is found, and proxy settings configured.
Some say that a login script can achieve this too, however the login script requires you to login to take effect.

Take a scenario where a user is in the office, closes the lid on his or her laptop, gets on the train then opens the lid, and connects via 3G.
If proxy settings were configured with a login script, the office proxy settings would still be present unless the user logged off and on again.
With a PAC method in place, the browser looks for the settings each time a page is requested, therefore it would fail to find the config file and connect directly.
Below is an example PAC file which can be modified to suit your needs. This could be further extended to look at the current IP of the client, and return a different proxy depending on where the client is. Eg if the client is within an IP range which is associated with the Paris office, the Paris proxy would be returned, or if the client is on a New York IP range, the New York proxy would be returned.

function FindProxyForURL(url, host)
 {
        
        // Direct connections to Hosts
         if (isPlainHostName(host) ||
         (host == "127.0.0.1") ||
         (host == "www.a-whole-domain.com") ||
         (shExpMatch(host, "*.a-entire-domain.com")) ||
         (shExpMatch(host, "10.20.30.*"))) {
           return "DIRECT"
         } else {
           return "PROXY proxy-server.domain.com:8080"
         }
 }

Within this file, access to the IP range 10.20.30.0 - 10.20.30.255 would be accessed directly (bypassing the proxy) aswell as the domain www.a-whole-domain.com. anything under the domain a-entire.domain.com would also bypass the proxy. everything else will be directed at the proxy server "proxy-server.domain.com" on port 8080.
Add additional sites to the proxy bypass list by copying an existing line and pasting it below.


Although a WPAD file could also offer similar functionality, in my experience a PAC file is much more flexible and will enable changes to take effect instantly.

Labelling those NIC’s

Tracing cables in a busy rack is a nightmare. Many people who share my switchport labelling OCD will always label up the port on the switch in a format such as “Link to MyServer01” and this is good. However it seems it’s not such common practise to label up where on the server the connection goes to, and then at the server end, where the connection goes to.


Most server’s today have PCI slots labelled in numerical form, and anyone with common sense will count the NIC’s from left to right. With the huge uptake of virtualisation, servers are now packed with NIC’s, Therefore my plea starts today, let’s start labelling server NIC ports in the same way we label switch ports.


The image below is a screenshot from one of the servers i look after. There are two quad port NIC’s installed in PCI slots one and two. Therefore I have adopted the naming convention “Slot X – NIC X – Link to Switchname giX-X” then on the switch side its reversed, the port is labelled as “link to MyServer01 NIC x\x . Implementing this method of labelling does take a bit of extra time and effort to keep up to date, but it will make yours and others life’s supporting the network so much easier. You can now tell exactly where a link goes from either the server or the switch end. I also add to the server NIC label what the NIC is used for eg “Slot X – NIC X – Link to switchname gix-x – Hyper-V Host Management” it’s a long label but your find it very useful!