SQL Server Connection String Planning

‘What should I put in the application connection string?’

 

It sounds like a simple request!

 

I’d suggest otherwise and some thought and planning is required before you answer.

 

At some stage in the future the database is likely going to move. The database move could be due to several factors i.e. performance issues, DR failover, new version of SQL Server, hardware upgrades etc.

 

As a DBA, you probably won’t have access to the application servers or remote clients where the connection name is entered and you’d like to keep it that way.

 

Let’s go through your options, remembering that you want to:

  • keep as far away as possible from having to ever update the application connection string.
  • minimize your future effort requirements when the database moves.

 

IP Address

image1

Pros:

  • A server name change doesn’t affect the client.

Cons:

  • Not friendly to remember.
  • DR failover/failback can be complicated and time consuming.
  • Each application will need to be updated if the database moves or the IP address of the server changes.

Log Shipping failover steps example:

  1. The Primary server needs to be off, as you can’t have duplicate IP addresses. It is likely that the Primary server will be off in the event of a DR scenario.
  2. The IP address needs to be changed on the Secondary server so that the applications can connect.

image3_2

Log Shipping failback steps example:

  1. Change the IP address on the original Primary server, as you can’t have duplicate IP addresses.
  2. Re-establish Log Shipping and failback the databases to the original Primary server.
  3. Remove Log Shipping.
  4. Change the IP address on the original Secondary server and shut it down, as you can’t have duplicate IP addresses.
  5. Change the IP address on the original Primary server, so that the applications can connect.
  6. Start the original Secondary server and re-establish log shipping.

That’s a lot of steps, with a lot of outage windows.

 

Server Name

image2

Pros:

  • You don’t have to update the connection string if the IP address changes.

Cons:

  • Each application will need to be updated if the database moves or the server name changes.

Log Shipping failover steps example:

  1. The Primary server needs to be off, as you can’t have duplicate server names. It is likely that the Primary server will be off in the event of a DR scenario.
  2. The server name needs to be changed on the Secondary server so that the applications can connect.
  3. The SQL Server internal name may also need to changed.

It’s worth noting that if any other applications reply on the server name i.e.  monitoring, backups etc, this method will also likely impact them as they connect based on the server name.

image4

Log Shipping failback steps example:

  1. Change the server name on the original Primary server, as you can’t have duplicate server names.
  2. Re-establish Log Shipping and failback the databases to the original Primary server.
  3. Remove Log Shipping.
  4. Change the server name of the original Secondary server and shut it down, as you can’t have duplicate server names.
  5. Change the server name of the original Primary server back to what it was, so the applications can connect.
  6. Update the SQL Server internal name on both servers, if required.
  7. Start the original Secondary server and re-establish log shipping.

That’s a lot of steps, with a lot of outage windows.

 

Server Name CNAME

image5

Pros:

  • You don’t have to rename servers when the database moves during a DR failover/failback scenario.
  • You can re-direct multiple applications with a single DNS change.

Cons:

  • Each application will need to be updated if the database moves to another server permanently or the main server name changes.

Log Shipping failover steps example:

  1. Update the DNS Alias (CNAME) Record to point to the Secondary server so that the applications can connect.

image6

Log Shipping failback steps example:

  1. Re-establish Log Shipping and failback the databases to the original Primary server.
  2. Update the DNS Alias (CNAME) Record to point to the original Primary server so that the applications can connect.
  3. Re-establish log shipping.

That’s a lot fewer steps, with minimal outage windows.

 

Application Name CNAME

image7

Pros:

  • You don’t have to ever update the application connection string. The goal is achieved.

Cons:

  • In the event of a failover/failback, multiple CNAME records will need to be updated.

Log Shipping failover steps example:

  1. Update the DNS Alias (CNAME) Record to point to the Secondary server so that the applications can connect.

image8

Log Shipping failback steps example:

  1. Re-establish Log Shipping and failback the databases to the original Primary server.
  2. Update the DNS Alias (CNAME) Record to point to the original Primary server so that the applications can connect.
  3. Re-establish log shipping.

CAUTION: If there are a lot of applications, you’ll have a lot of CNAME records to update.

 

Server Name CNAME plus Application Name CNAME

image9

Pros:

  • You don’t have to ever update the application connection string. The goal is achieved.
  • Ultimate flexibility.

Cons:

  • More CNAME record creations are required.

Log Shipping failover steps example:

  1. Update the DNS Alias (CNAME) Record 1 to point to the Secondary server so that the applications can connect.

image10

Log Shipping failback steps example:

  1. Re-establish Log Shipping and failback the databases to the original Primary server.
  2. Update the DNS Alias (CNAME) Record 1 to point to the original Primary server so that the applications can connect.
  3. Re-establish log shipping.

 

Database Mirroring

Database Mirroring allows for an initial partner name (Principal server) and a failover partner name (Mirror server) to be specified in the application connection string.

 

Based on the already covered connection strings, the most flexible option for a Database Mirroring environment is shown below. You don’t have to ever update the application connection string and your goal is achieved.

 

No DNS or application connection string changes would be required in the event of a DR failover/failback.

image11

 

Alwayson Availability Groups

Alwayson Availability Groups introduces a listener.

 

Based on the already covered connection strings, the most flexible option for Alwayson Availability Groups is shown below. You don’t have to ever update the application connection string and your goal is achieved.

 

No DNS or application connection string changes would be required in the event of a DR failover/failback.

image12

Summary

Planning your connection string can save you a lot of hassle and it’s not as simple as just giving the server name, which is done far too frequently.

 

The above is the start and next I’ll cover SPNs which you’ll likely find are a critical part of your connection string planning.

1 Comment

Leave a Reply