Connecting To Data
CRM, DMS, Active Directory, internet / intranets / extranets, project management, time & billing, etc... All these systems provide valuable data for our custom development here at Whyte Hirschboeck Dudek. We regularly tap into these systems to retrieve information, so writing connection strings for each system was a regular occurrence, and a pain in the neck when one of our enterprise systems changed servers, or account information for security reasons - various custom apps would break and we would scramble to update the connection info in all the right places, to the new information.

The Good Kind of Lazy
I was feeling particularly lazy one day, and decided to fix the problem for good. I put all of our connection string data into a single table on a sql server, wrote a ConnectionStrings static class to Retrieve("systemName") them as needed, and a 2 minute cache to keep performance under control. Our apps now only need a reference to the ConnectionStrings class.  No more worrying about specific machine/account details, or XML file updates across multiple config files on the network.

Consequences need to be considered carefully, but are pretty easy to live with or get a handle on.  Server/db failure can be handled with a failover, or good disaster recovery plan.  There are a couple additional db calls, which again should have minimal impact on performance if you cache your connection strings when you retrieve them.  Finally, developers need to figure out the names to connect by.  We handled this with a quick datagrid web app that retrieves the ConnectionStrings table contents for reference.  Any arguments about security have better counter-arguments, especially when you consider that you can now change the way you retrieve connectionstrings easily (web service, xml file, hard-coded in the dll, encryption, etc), and therefore evolve your security for everything at once.  Furthermore, security accounts on these systems can be updated without fear of breaking custom apps.  The new information can be updated easily in the ConnectionStrings table.

The benefits, off the top of my head:

- Connect semantically to our systems (by name & context), Retrieve("DocumentManagementSystem") instead of the traditional method "...server=ObiWanKenobi04..."

- Connect in the code (why not?), instead of in an XML file somewhere

- Single point for {failure & recovery, security, monitoring & auditing}

- Integration across systems feels more natural, less fragile

- DBA can manage connectionstrings and account security, and just provide meaningful names to the dev team

- Log ALL connections retrieved, by application, user, & time

Print | posted on Wednesday, January 21, 2009 11:12 AM


No comments posted yet.
Email (never displayed)
Please add 5 and 1 and type the answer here: