SQL Server with Mr. Denny

Dec 12 2012   2:00PM GMT

The Least Expensive SQL Server 2012 High Availability Solution

Denny Cherry Denny Cherry Profile: Denny Cherry

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

6  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    [...] The Least Expensive SQL Server 2012 High Availability Solution - Roll your own HA solution on a budget this week from Denny Cherry (Blog|Twitter). [...]
    0 pointsBadges:
    report
  • Denny Cherry
    [...] accessibility on a cheap: inexpensive options for SQL Server [...]
    0 pointsBadges:
    report
  • maggoe
    Hi Denny,

    How has been your mileage with this solution?  We are thinking of doing he same for TS Broker server high availability (it relies on SQL now) and wanted to know if you have some updates/caveats on this solution.

    Would you recommend this in production?  Is there a lot of difference between the clustered SQL Standard (besides AlwaysOn and mirroring and usual SQL Expresse limitations) and the "creative" solution you put in here?

    Thanks for answers!
    10 pointsBadges:
    report
  • Denny Cherry

    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.

    66,010 pointsBadges:
    report
  • anemicalgae
    Hi Denny,

    I am currently trying out your creative solution. Did you leave out the shared storage from the cluster? 
    10 pointsBadges:
    report
  • Denny Cherry
    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.
    66,010 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: