THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Config Points are H. E. Double Hockeysticks

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

Published Tuesday, September 22, 2009 10:23 AM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jonathan Kehayias said:

The SQL Dev Team Blog covered how to do #2 using SRV records earlier this year:

http://blogs.msdn.com/sqldev/archive/2009/07/09/dns-srv-record-support-for-sql-connectivity.aspx

For #3 you can use a script to load a registry file as a part of the startup script for the server.  This is how we did this to set aliasing across our entire AD environment for an application, and it is also how we automated ODBC datasource configuration updates as a part of a server migration.  Just make the change and export the registry hive.  For SQL Aliases it is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

For ODBC it is:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

September 22, 2009 3:36 PM
 

merrillaldrich said:

Great tips; I kind of figured we'd have to head that way. It looks like #2 essentially doesn't work, so #3 is the only practical solution. Anyone know of client libraries that just don't play with SQL aliases, where this technique can't be made to work?

September 22, 2009 6:44 PM
 

ALZDBA said:

We keep track of connections on the sqlinstance itself by using sqlserver events. (SQL2005 / SQL2008) ( cfr. http://www.simple-talk.com/sql/sql-server-2005/logon-triggers [--> only use triggers if you actually have to intervene !] )

For SQL2000 a sampling job runs every x minutes to collect sysprocesses data.

Off course, it's only as good as the content of the connection information supplied by the applications. (application name, workstation name, ...)

September 23, 2009 6:50 AM
 

ALZDBA said:

Regarding the SRV records in DNS this article leave mixed feelings ...

http://blogs.msdn.com/sqldev/archive/2009/07/09/dns-srv-record-support-for-sql-connectivity.aspx

I'll be testing this soon.

September 30, 2009 5:57 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement