As we all know by now AlwaysOn Availability Groups are an enterprise edition feature and SQL Server Clustering is a standard edition feature. Butt what happens when you have a small business that is running its apps on SQL Server Express. Can’t SQL Express have any sort of high availability?
Officially the answer is no, however with a little bit of creative configuration you sure can.
The Overall Environment
To setup SQL Server Express in a Windows Cluster I’m building this on a two node Windows Server 2012 cluster using a file share hosted on my domain controller to host the actual databases. To ensure that the domain controller is rebooted as little as possible the domain controller is installed in core mode. The cluster nodes are Windows Server 2012 standard edition (which now supports clustering) as is the domain controller.
Installation
As SQL Server 2012 express edition doesn’t support Windows Clustering out of the box the installation will be a little different from doing a normal clustered install under standard or enterprise edition. To install I did a normal SQL Express install on node1. The only change from a normal install that I made was that I configured the SQL Server instance to start under a domain account. When I got to the data directories part I configured the data folder to a network share on the domain controller.
Once the installation on node1 was completed I stopped the SQL Server services. Then I renamed the folder that I installed the SQL Server database files into. The reason for this is that I need to configure the second instance to put the database files into the same location. I can then install SQL Server 2012 express edition onto the second node.
The installation on node2 is done exactly like it was done on node1.
Once the installation is done on both nodes configure the SQL Server service to have a startup type as “Manual” instead of disabled or automatic. Leave the SQL Agent service as disabled as even though SQL Express installs the SQL Agent the SQL Agent isn’t supported on SQL Express.
Configuring Clustering
Once the installation on Node2 is done the cluster can be configured. To do this bring up the Failover Cluster Administrator on one of the nodes and connect to the cluster. If the cluster hasn’t been configured yet run through the normal Clustering Configuration wizard.
We’ll now configure a new cluster role on the cluster. To do this right click on “Role” then select “Configure Role” from the context menu as shown below.
When the wizard opens click next to get to the list of services. Then select the Generic Service item from the list as shown below.
On the next screen you’ll be asked what service you wish to cluster. From this list select the SQL Server service as shown below.
On the next screen you’ll be asked to name the resource group. Give the group a name which is unique on the domain and click next. The next screen will ask you to select the needed storage. Simply click next on this screen as we aren’t using any local shared storage. The next screen asks you if any registry settings need to be replicated between the machines. We don’t need to replicate anything as SQL Server doesn’t make much use of the registry for the actual SQL Server service so we can simply click next on this screen as well. The next screen is simply a screen to review the changes which will be made. You can simply click next on this screen after reviewing the information on the screen. When the summary screen displays click finish.
Post Clustering SQL Config Changes
The first change that you’ll need to make is to enable the TCP network protocol on both nodes. By default SQL Express has the TCP network protocol disabled which need to be corrected before uses will be able to connect to the SQL Server service.
The next change that you’ll need to make is to change the local server name in the master database from the name of the last node which was installed to the cluster name using a script similar to the one shown below. In the case of this script the nodes are named node1 and node2 and the cluster name is clustersql. Once this script has been run the SQL Server instance should be restarted or failed over to the other node.
exec sp_dropserver ‘nodeb’
GO
exec sp_addserver ‘clustersql’, local
GO
At this point the cluster is up and running and applications can have their databases configured on the SQL Server Instance.
Denny
11 Responses
Hi Denny,
Maggoe,
I haven’t deployed this into production for a customer. This idea came up when we were kicking around HA solution ideas for a customer and we were trying to figure out if it would even work. Turns out that it does.
If SQL Express will fit your needs, then I can’t see a reason not to do something like this. Now some things it keep in mind are that SQL wouldn’t be cluster aware like it would be with Standard or Enterprise edition so you’ll loose some of the normal health checks which would happen with Standard or Enterprise edition as the cluster will just be looking for service failure. But if you can live with that this solution should be able to work for you.
Hi Denny,
In this case no, I did use shared storage. This could be done using network storage as well easily enough using SQL Server 2012 Express or newer.
I wasn’t able to install sql server on the second node because it gave error “system database file already exists in \servershareMSSQL11.instance” even after changing the shared folder name
Try moving the folder with the system databases out to another location. As long as the folder is renamed and isn’t where SQL is expecting it, you shouldn’t have a problem.
Seems a bit pointless to have a failover cluster if not also able to replicate the database to the failover subnet.
MushroomHunter,
HI Denny,
You’ll want to get redundant storage. The easiest way to do that without a SAN is to use SIOS Data Keeper to replicate local storage on the two machines which will be running Microsoft SQL Server.
That said, SQL Express isn’t really an enterprise ready version. It only supports one processor, one gig of RAM, and a 10 or 20 Gig database depending on version.