1

We have multiple Windows server 2016 machines hosting single SQL server instance on each server. Every server hosts ~100 databases. We have a master table in which has a record for each database & its connection string. Connection string contains IP of the server where database is hosted & some other typical information. Since databases are distributed across multiple servers we have to query on master table first to get correct connection string & then connect to the desired database to perform any operation.

As we have multiple DB server(and new ones might get added in near future), it would be great if we can put all our DB servers under single domain name so that all DBs can be accessed through one domain name. This will give us flexibility of changing IPs if needed without updating connections strings. Also adding new servers will become easy without changing any application code.

To be able to achieve this, Can someone please put me in right direction on how to do following?

  • How to add a domain name to existing SQL server instance?
  • How to add a proxy which will accept connection requests from application code & forward it to correct database server using some logic?

Desired architecture

Thanks in advance!

2

Assuming your intent is for the application code to connect and use the proxy as if it were a SQL instance (i.e. connect using a SQL API and executes queries), don't go there.

Consider creating a DNS alias entry (CNAME record) for each database (not instance) and specify that name in the connection string along with the database name. That will provide abstraction over instance hosting the database so database can be moved with no connection string changes.

Example with 2 SQL instances hosting 3 databases:

db1.yourdomain.local -->instance1.yourdomain.local
db2.yourdomain.local -->instance1.yourdomain.local
db3.yourdomain.local -->instance2.yourdomain.local

Example connection string:

Data Source=db1.yourdomain.local;Initial Catalog=db1;Integrated Security=SSPI;
  • Just remember to create a CNAME and not an A record in DNS. CNAME will always be expanded as FQDN and then Kerberos will work without you having to create multiple SPNs – Spörri Apr 11 at 16:15
  • 1
    @Spörri, thanks for mentioning that. Added it to my answer. – Dan Guzman Apr 11 at 17:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.