Simplest SQL Failover / Failback Design?

Thanks for your patience in fielding yet another replication design question.

I want to be able to fail over and back my 24x7 SQL DB to another VM in case the first instance fails or more likely when I have to patch SQL. I have Veeam backups. I failover and back with dual Exchange 2010 DB servers for those of you who know what a DAG is but I'm not up on SQL.

I have twin VMWare hosts and ISCSI SAN storage. I can create more VM's and add SQL licenses if necessary.

I'm on SQL 2012 and am open to going to 2016.

I have about a dozen client PCs with the SQL server name and DB name manually entered into the app. So I have to keep the same server name and DB name or touch them all at failover/failback.

Can someone guide me towards what would be the simplest (not necessarily the cheapest) solution?

If I've left out any relevant details please let me know.

Thanks!

Tim

You can use Synchronous Database Mirroring with a witness. You won't need to change the connection strings after a failover if the database driver supports the Failover Partner parameter.

Another option is SQL Server Failover Clustering. Requires clustering knowledge for both the Windows admin and the DBA. Not easy to implement without experience, typically need to hire a consulting company.

Finally, Availability Groups are an option. We see a lot of improperly setup AGs where they'll have significant downtime when they patch the secondary replica. It's because their quorum isn't setup properly. If there's a DR site, need to understand votes too.

I really think mirroring is the simplest for most people.

2 Likes

Thank you for your suggestions. Regarding your recommendation I'm not clear about what strings the database driver supports (I presume you are talking about ODBC). I have some limited experience configuring ODBC but in this case the client seems to have 2 ODBC drivers installed but no DSN's set up. There is a config app where I specify the server name and DB instance and everything else seems to be taken care of.

I will research further to see if I can get past this hurdle then start learning about SDM.

Thanks again!

I'm referring to the connection string. ODBC does support the Failover Partner option, which you can get to in the ODBC GUI.

If you instead have a regular connection string, scroll down to the Database Mirroring section for the connection string changes: http://www.connectionstrings.com/sql-server/

1 Like

Sorry I'm stuck on the connection string part, do I have this right?

In my config app it wants:

Data Source Name:
Server Name:
Database Name:
UserLoginID
User Password:

With this info the app constructs a connection string and via 2008 Native Client the connection string is passed to the SQL server.

Is that correct?

And on failover with Synchronous Database Mirroring all the string data is the same so the client believes it's still talking to the original server?

If the app is constructing the connection string, then the app needs to be modified to have the ability to include the Failover Partner parameter in the connection string.

Yes on failover the app keeps working as it'll now use the Failover Partner server name. There will be a brief downtime when the failover is happening, usually about 30 seconds.

1 Like

I confirmed with the vendor that I can't modify how they implement the connection string so that kills the simple approach. They suggest Failover Clustering. Do you know in FC can I accomplish my SQL service pack/security patching without taking the DB offline? Thx.

I was watching a couple good videos on Youtube and I was thinking they would be suitable for this post. When I visited their website I saw...wait a minute...Tara Kizer works there? :relaxed:

With an FCI, you can install service packs and hotfixes without taking the service down, but only on the passive node. You'd have to do a failover to do the other node. So there's about 1 minute of downtime in order to patch both nodes.

Yes I do work for Brent Ozar Unlimited. As of January.