SQL SERVER 2014 Standard - High Availability Listener solution

Question

The company has SQL Server 2014 Standard licenses and we're standing the servers up in a VMware environment.

My initial plan is to create 2 instances on each DB server and run Instance1 on SQL1 as primary, Instance2 on SQL2 as primary with failover vice versa.

Questions:

• Without the listener available only through Enterprise addition, has anyone used a load balancer to repoint the DB connection when you failover for maintenance?

• Is there a better method for failing over with Standard Edition?

The only other solution that comes to mind is DNS CNames/ALIAS and repointing to a different IP address, but that seems inherently flawed.

Answer 1

With SQL Server 2014 Standard Edition you can create a two-node failover infrastructure. For that, you need to have in each node at least Windows Server 2008 R2 SP1 Standard, because you need to first generate a failover cluster with Windows Server.

If you want a multi-instance failover cluster, then you need to generate two SQL Server 2014 installations, since the product works in an Active-Passive mode. I mean, you will need to install the first instance, SQL1 and then you will proceed with the second instance, SQL2. You will need to configure each instance so SQL1 runs in node 1 and SQL2 runs in node 2.

Also, it's very important and recommended, that you have at least a SAN or some device that can provide you with the shared storage schema, since previous to the SQL Server failover cluster installation you need to validate the Windows Server failover cluster support to the SQL Server one.

Answer 2

Why don't you use Database Mirroring instead and configure your applications to connect using a fail-over partner in the connection string (read more about that here Connect Clients to a Database Mirroring Session (SQL Server)). If you require automatic DBM fail-over then you will also need a Witness server (and run in-sync) in order to use High-Safety Mode with Automatic Fail-over.

If you don't want to use failover partner's in your connection strings, rather than using DNS alias redirection, you can also consider using SQL Client Aliases. If you want them to be "Enterprise deployed" you can use group policy to push those registry entries across your Application servers. Remember these solutions are manual (as opposed to the FP being automatic).

Please be aware that Database Mirroring was deprecated in SQL Server 2012 so you should be mindful that if will be removed in a future version:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

Therefore once you go to SQL Server 2016 SE you can consider upgrading your DBM to a Basic AG. When you are ready to move to Basic Availability Groups please review Overview of AlwaysOn Basic Availability Groups (SQL Server) to understand the limitations between that and Enterprise edition AGs.