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.