There are times when you need to store information for configuration or tracking SQL operations. This can be true regardless of the SQL engine you use. I have found that a simple, Key Value pair, table definition allows me to flexibly store and retrieve data inside a database, allowing it to be accessed by SQL queries without having to make external calls to registry, LDAP, ADO, or other external data stores. This data can be used for virtually anything, acting as a log, or configuration repository.
I like to have a database with a Key Value pair table available on any instance of a database engine. Even having a single database can be useful, rather than having to have a table on every database. If you use a lot of stored procedures, a central database can be used. If you need to qualify instances of variables for different systems, a simple dot notation can be used for the keys to segregate values from different systems.
For example, storing a configuration value for maximum fragmentation allowed in a table before defragmentation is exercised might look like this:
Key Value
DefragThreshold 10
When you need to have a different value you might use a suffix or prefix for the database:
Key Value
DefragThreshold.MyDb1 10
DefragThreshold.MyDb2 25
Using your own design you can store entries in a single database for any number of systems, segregating each as needed. Perhaps you might have a single configuration database storing values for different environments such as Development, Test, Production, Training, etc.
There’s nothing really magic about this technique. It’s not a grand idea that solves world hunger. But, sometimes it is the simple ideas that can make our life a little better, or at least easier, when we need a centralized location for configuration like data.
Cheers,
Ben