April 14, 2021 at 1:21 am
Instead of SQL Server name I am thinking to use dns cname for connection string etc. Would this be possible to setup with named instances? If yes would there be any issues? My understanding is it can cause issues for connecting with SSIS and linked server?
April 14, 2021 at 6:18 pm
The cname will only resolve to an IP address - the port definition for the named instance won't be included. If you have named instances you can use the cname to reference the machine and the instance or port to reference the individual instances.
For example: mycname\inst1, mycname\inst2
The advantage of this approach is that you can create a new server - install inst1 and inst2 - and then change the cname to point to the new server. The downside to this approach is that both instances have to reside on the same server.
The other option is to create a cname for each instance, for example: cname1\inst1, cname2\inst2
This structure would allow you to move inst2 to a separate machine - then just update cname2 to the new machines IP address.
However, there are no cases where you can change the instance name without updating the connection strings to use the new instance name. If you use the port number in your connection strings instead of instance name - and use the same port on the new machine then you can change the instance name since your connection strings won't be reference the name, only the IP address and port.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2021 at 6:32 pm
There are SRV records, but I don't think the drivers look up these records.
As Jeffrey mentioned, you need to include the port, or have the instances listen on 1433. You can have a named instance listen on this port, but only one named instance.
April 14, 2021 at 8:46 pm
Thank you! How about cname1\inst and cname2\inst?
April 14, 2021 at 9:12 pm
This is like you can't have 2 same instances on one server right.
April 15, 2021 at 3:02 pm
I'm not completely sure what you mean, but DNS is a central system, somewhat like your local network. you can't have cname1 as the same machine name. You can have different hosts pointing to the same IP.
For example, I could have:
In this case, my web server resolves the host and sends back the appropriate code. Or, I could have two different ports running, and I'd have to use these in my web brower:
The first case, www, takes the default port (80), because the browser knows this. The second one uses 8080.
For SQL Server, when you connect via name (sqldb1), the driver connects to the default port. If you connected to sqldb1/inst1, the driver would look to the SQL Browser service, which returns the port. Then the driver connects on that. If you use sqldb1/inst1:51433, it uses 51433.
If you connect as sqldb.mydomain.com, which is a cname for (sqldb1), then the driver connects to that name and on 1433. You could override this, but you can't use sqldb/inst1.mydomain.com or sqldb.mydomain.com/inst1. Those aren't valid addresses, AFAIK. Therefore, you would need to have your instance on 1433 or include the port in the connection string or name.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply