Sql-server – How to use single domain name to connect to multiple database servers

sql serversql-server-2017

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!

Best Answer

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;
Related Question