By SwissCoder


2019-02-11 08:26:02 8 Comments

To be very flexible and move database to different servers more easily, we came up with the idea of using an alias per database. So by simply changing a DNS entry, we can route all queries to a different database server without changing the connection string or re-deployment of the respective client application.

To clarify:

Per database we would have an alias. So if the database is moved to a different server, only its DNS alias has to be changed.

For example:

  • db1-alias.mydomain.com points to db1 on some random server.
  • db2-alias.mydomain.com points do db2 on some other random server.

So lets say db1 was moved from server1 to server2, there is no need to change the connection string. Only the DNS alias entry has to be changed, forwarding to server2 instead of server1.

Are there any known drawbacks to this?

2 comments

@Viorel Ciucu 2019-02-12 23:48:55

Be aware that when you use DNS CNAME with a SQL database you need to be aware of the TTL setting. This setting tells a system how long the DNS entry should be cached for before a trip back to the DNS server is required. You will probably need to adjust the TTL in advance so that when you do make the switch, you don't have client systems that have the old entry cached. You can run 'ipconfig /flushdns' command to flush the cache, but it must be run on every computer individually.

@Tony Hinkle 2019-02-11 13:52:24

One minor drawback would be that you would need to add a Subject Alternative Name (SAN) to the certificate that is bound to SQL Server if you are using encrypted connections. This could be a significant pain if you have a "shared" server that hosts a number of smaller applications and you want to set up an alias for each application.

If you are using a separate alias for each application, you would need to generate and install a new certificate each time you add a new application to the server. The same might apply if you want to make sure there are no unused/invalid SANs in the certificate, in that you would need a new certificate each time you removed an application.

@SwissCoder 2019-02-11 16:55:40

Thanks a lot for pointing this out! Let's assume there would be only a handful of DBs, but multiple applications accessing each DB (not ideal design, I know). Would it require a SAN per DB or per Application?

@Tony Hinkle 2019-02-11 17:41:46

@SwissCoder It's a SAN per CNAME alias. If you have multiple applications using the same database and they will always use the same database, then I would have one CNAME, and accordingly one SAN, for connections to that database.

Related Questions

Sponsored Content

1 Answered Questions

1 Answered Questions

[SOLVED] What are valid connection strings for SSMS login box?

1 Answered Questions

1 Answered Questions

[SOLVED] SQL Server 2008R2 Alias not working

1 Answered Questions

1 Answered Questions

[SOLVED] Changing autogrow settings with database mirroring

2 Answered Questions

[SOLVED] Remove nulls from TSQL connection string

Sponsored Content