Tag Archives: Extended Properties

Extended Properties as a Run Book?

Perhaps Run Book isn’t the right terminology, but it’s close. So, in my environment we are a manufacturer of large equipment. We do have a centralized IT organization, but it seems to be more focused on keeping the lights on and trying to catch up on what the business purchases off the shelf or even what some groups have developed on their own.

One thing I’ve struggled with in the past two years, since this company has no defined processes (no ITIL or anything similar) and little to no documentation, has been in capturing what databases are where, who is the “Business Owner”, what the application is, and such. I’ve also started implementing DNS aliases for connection strings so that we can be more portable when moving applications, especially COTS systems where we might not have the understanding to change a set of connection strings. As you may know, I have a number of Powershell scripts that capture the specifics of the servers, instances, database properties, file locations, etc. One thing that these cannot grab are the applications and business representatives. Sure, with my two years of knowledge, I can look at a database server, look at an instance name, look at the name of the database and make a good guess at what it does, where it runs, who I should contact if there are problems or changes are planned… but when onboarding someone new, it would take an incredible amount of time for that new person to build up the same intimacy with the environment.

Previously, the approach was to take the Excel document output from the Database Properties script and add a column with “Owner” which sometimes had a name I could place with it.

Today I received a request for a new blank database via email from a quasi-developer in another location. This person is not in IT but has developed a number of supporting type applications for the local users at his manufacturing site. I thought to myself, I have all the details in this email from the requestor, maybe we should just embed this email to the excel document. But I’m lazy, and this is a very loose requirement, I could be busy, I could be in a procrastination mood and put off the follow up documentation for… well,,, ever. Next, I thought, we have a web based in-house developed tool for requesting services from IT. So I thought, hey, I could make it a requirement that a ticket be created for any new Database needed, and then just copy the ticket number into the spreadsheet. Blah, same thing, when I’m off creating a DB in SSMS, I don’t really want to have to open a spreadsheet on our SharePoint site to update that… still too manual and loose.

So, I thought, why not make it part of the “Create database” process to copy the details from the request ticket into Extended Properties inside the database. What I came up with was setting up four Key, Value pairs to start with. A Description which contains some details of the what the database is used for. An AppOwner key with the requestor name or whomever is identified as the owner. A Ticket key with the number from the request. The final required one is the ConnectionString which contains the DNS alias name created for the application to use. I kind of like it, it’s tidy and neat, and it stays with the database if I restore to a new server. The only maintenance might be to change the AppOwner if they change roles or leave the company.

I’m going to modify my above referenced powershell script to include grabbing these keys and values from every database when I run it. I’ve also been noodling a type of “Self Service portal” where power users like the requestor above can hit a web page form that would then kick off an approval work flow and I could then choose the server to deploy to and the database create and all the above documentation would be created on the fly. That would be really neat, the only flaw would be organizational changes.

In short, I’m going to make it a requirement than any new database request be filled out with our services request system, and require a description of the application just for paperwork reasons… it’s much easier to run a web based report than search a DBA’s PST file for auditing purposes. I’m going to have the DBA team start using the Extended Properties for the database to hold that. I’m going to “Extend” my powershell script to grab those properties for our documentation purposes. I may or may not work on a Provisioning Portal… I might be getting too far ahead of the rest of the IT organization here.