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?
Thanks in advance!