DNS entry for each database

Question

DNS entry for each database

TangoVictor

SSChampion

Points: 10975
More actions
March 22, 2017 at 1:24 pm

#322011

We've been tossing around the idea of using DNS CNAME entries for each database.
For instance a cname entry such as db_MyDatabase, which points to the server ACME.SQLSERVER01. Access to the databases would use a connection like Datasource="db_MyDatabase"; initial catalog="MyDatabase".
When MyDatabase moves to ACME.SQLSERVER03 we update the cname to point to the new server and we don't need to find every connection (ETL, App, Reports, etc...)
Just looking for some input... good idea, bad idea, issues, major problems, etc...
thx
November 23, 2024 at 10:20 am

This was removed by the editor as SPAM
December 3, 2024 at 8:39 am

This was removed by the editor as SPAM
December 9, 2024 at 8:58 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Bill Talada SSChampion Points: 11954 More actions · Answer 1

Do you mean DSN instead of DNS? I've been burned in the past by redirection. It is probably ok if everyone is referencing the same redirector.

TangoVictor SSChampion Points: 10975 More actions · Answer 2

Nope, I mean DNS (Domain Name Servers).

When i connect to ACME.SQLSERVER01 it will hit DNS to get the IP and then route to the correct server. I would like to have a DNS record for each database, well, not actually for a database since DNS doesnt know about SQL databases, i just want a DNS entry that i can use when i want to connect to a particular database. This name would always be used in every system when connecting to that database. You could have hundreds of DNS records that all point to the same IP address. Technically in DNS we would point to an A record from a CNAME entry but its the same affect.

So, instead of connecting to ACME.SQLSERVER01, i connect to ACME.DB_Northwind or ACME.DB_Pubs or ACME.DB_AdventureWorks or ACME.DB_MickeyMouse and the DNS system will look for that record to see what IP those go to and send the request to the SQL Server that lives at that IP address. Basically who cares what SQL Server it is, we just care that port 1433 is sitting there listening. If we move the database to a different server the DNS entry will be updated and the same connection name will now go to the new server.

In fact, this DNS setup would work for DSN connections 🙂

Sue_H SSC Guru Points: 90860 More actions · Answer 3

I've seen arguments both ways and don't necessarily have any hard stance one way or another. A few things I would wonder about though:

If you move the database and update the DNS entry, how long will it take for all clients to pick it up due to DNS caching? There would be ways to resolve that (no pun intended) but it seems that it could still be a challenge in some scenarios.
Wouldn't you have to manually create a lot of SPNs if you need Kerberos to work?
I think there are some things that need the real server name - replication being the first one I can think of.

Sue

TangoVictor SSChampion Points: 10975 More actions · Answer 4

Hi Sue,
Yeah the DNS cache lag was something i thought about and really not sure the impact it would have, but a very valid point to figure out. I've also heard there can be increased latency due to an extra round trip request with DNS. However, i cant imagine that would be noticeable.

As for Kerberos, well, we aren't using it, but that is likely to change. I found this:

For example, an SPN always includes the name of the host computer on which the service instance is running, so a service instance might register an SPN for each name or alias of its host.

Service Principal Names

I'd like to think this would not be treated like an alias it still points to the same hostname (DNS A record) and somehow that ties to the kerberos realm I belive.

Again, something to consider before moving forward.

Thanks for the input!

russ960 SSCommitted Points: 1852 More actions · Answer 5

TangoVictor:

Did you ever get this implemented? How did you handle SPN records or did you just go one without it? I hit a block in the domain is not trusted:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

Russ