I haven't been able to post for a bit; my group had a large project come up involving consolidation and relocation of services from one data center to another. It's interesting, and challenging, and has been a reminder of what a pain point database connection information can be. Every application seems to have a random smattering of text or XML config files, ini files, registry entries, DSNs, JDBC URI's, you name it. We've tried a few methods to abstract the server connection information in our environment, but there are some difficulties, especially with pointing to consolidated named instances on a cluster. So, for both of you who read my blog, a quick survey! I know of a few methods to provide indirection for database connections, and I wonder what you use:
- DNS can work to point to a default SQL Server instance by another, "virtual" name. Standard DNS techniques don't work so well for named instances, though, as they just resolve names to IP addresses. For simple apps on default instances, we've made some DNS aliases for the SQL Servers. Can't really point those to a named instance, though.
- I've heard of a technique to use SRV records in DNS to point to a specific port for a service. I've not personally been able to do that (not a DNS expert by any means). I would love to know if that really works and how to set it up properly, to point a simple DNS name like "SOMEAPPDB" to a specific named instance on a cluster like "SQLSRV01\MYINSTANCE" or SQLSRV01 port 12345.
- Local SQL Server aliases could be configured per app server, which seems a reasonable compromise if only to force consistency across all the different techniques that applications use to set the information. That is, an application server could have an alias for SQL Server set locally, then have all it's ideosyncratic settings point to that alias, so that at least all the app servers have a consistent method for redirecting to a specific SQL Server instance. One would still have to visit them all to make a change, but at least it would involve only one technique. I haven't implemented this, but I'm giving it serious thought. I'll need to find out if it works for all client libraries (e.g. JDBC).
Over and above this, I feel like I just have to keep some record of how every single app connects, which is not very reliable (or fun, frankly.) What do you do?
Update 6 Jan 2009 - Linchi Shea is also looking at this issue: http://sqlblog.com/blogs/linchi_shea/archive/2009/12/28/bad-database-practices-allowing-apps-to-connect-to-the-hostname.aspx