SQL Server Reference Guide

I have several sections in this Guide, from Administration to Programming, and points in between. The activity I describe in this overview has so many applications, I wasn’t sure where to put it — it’s that important. I decided on the Professional Development section, since this part of the Guide is devoted to helping you advance your career. Application Profiling is not strictly a database administrator or developer activity — it’s an architectural discipline that will help set you apart from the average IT worker. And as I’ve mentioned before, it’s a perfect role for the data professional, since we are the ultimate custodians of the organization’s information.

Interestingly, many people don’t either know how to do a proper application profile or don’t know how important it really is to the other activities they do. It is completely integral to performance tuning, disaster recovery, hardware planning and more. Many shops in fact do perform parts of an application profile when they implement one of those initiatives, but since they only focus on one aspect, they end up having to repeat the work for the next initiative.

In this two-part overview, I’ll explain what an application profile is, and how you can perform one for your environment. You’ll learn which elements are important for all profiles, and which you might care about for a particular purpose. At the end of this exercise, you should be able to create your own application profile, which will not look like any other application profile does at another shop — and that’s completely acceptable. Your situation will dictate the elements you care about and the format and presentation you want to use to best serve your organization.

Definition

An application profile is a description of the computer programs running in your organization. It includes information needed to ensure that you are licensed and legal, you know the performance requirements and patterns in the application, and that you have planned for high availability and disaster recovery. This means you’ll have information that involves how important the application is to the organization, how it is secured and other elements. You decide how deep you need to go with the information, and each application might have different levels of detail depending on the need.

Keeping it Current

It’s important that this information is up-to-date. I’ve seen organizations embark on a profiling effort because a certain driver (compliance with a set of accounting laws here in the U.S. normally drives this) and then “go stale” because no one kept up with it. If you make the information too detailed and if you don’t set up a methodology to make sure all responsible parties know when and how to update it, you’re not getting the full benefit of the data.

The point is, make sure you make it easy to create, update and report on the data. If you don’t, it won’t be kept up to date. It’s just that simple.

Storage and Presentation

There are a lot of ways to store this data. In fact, as you’ll discover, you probably have much of this data stored already, although perhaps in various locations.

I recommend a database (I’m sure you saw that coming) and I’ve even detailed out a methodology for storing IT data in a central location using SQL Server 2008 — called the Central Management System, or SQL CMS. You can read about that project on my Codeplex project site. The interesting thing about using that system is that you will already have performance and storage data there, so linking the application data to that allows for rich reporting and analysis.

The SQL CMS also permits integration into systems you already have, such as Microsoft’s System Center or other IT compliance software. And by linking this information and adding only the elements you need, updating and access is made much easier.

If your needs are simpler, you can just use a spreadsheet. It provides analysis and reporting, although with more “hard wiring,” and it takes more effort to keep it up to date. And as a wise friend once taught me, the harder something is to do, the less likely you are to do it. It’s that simple.

In any case, make sure that you think the process through, and protect the data as you would any application. Ensure that only the proper access is granted, and make sure you back it up and maintain it effectively.

Reasons to Implement an Application Profiling Effort

I’ve mentioned one of the reasons you want to profile the applications in your organization — compliance with accounting or other laws. You also need this information for a performance tuning exercise. I’ve seen several shops start to tune a database without a comprehensive examination of the data path from the application to the database. When you create an application profile, you already have that information, and performance tuning as well as troubleshooting are much simpler.

Another reason to detail the applications in your organization is consolidation. There are lots of ways to consolidate your IT infrastructure, and one that many people don’t consider is application de-duplication. You may find, as you perform this analysis, that you have two or more applications that do the same kind of work. If you simply collapsed those into one application, you save licensing, maintenance, data storage and more. The best kind of consolidation or performance tuning is to remove the work altogether.

Security is another reason to profile your applications. Knowing what data is stored where, and who has access to it, is crucial to a good security strategy.

And of course the largest driver for application profiling is high-availability and disaster recovery. Both of those are sometimes gathered under a “Business Continuity” plan that your organization maintains in case of a catastrophe.

Any of these reasons are enough to make the effort worthwhile. All of them together make it essential.

Elements of an Application Profile

You don’t have to go to a very detailed level for every application. While most Office Productivity (Like Microsoft Office) applications are required for the business, they are often used locally, and data is stored outside the database. So while you want to store the name, license count, and installed base in a system, it might not be as important for performance tuning, for instance.

However — even Office Productivity applications might store their data in a database. Every time the users set up Microsoft SharePoint document libraries, for instance, they are storing some data in a database. That means it’s up to you to track it, secure it, make it available, back it up and so on. So make sure you include at least the names and locations these applications are installed so that you are sure being thorough in your examination.

As far as the level of detail required, that will be up to you. I normally include more detail for those applications my organization deems “mission critical.” That means I’ve taken all the application names and purposes to a high-level business strategy meeting and asked the executives to do a “life boat” exercise. I ask them “If I could only keep one application running, which one of these would that be?” After they argue it out, I then ask “What if I could keep another one alive, which one would that be?”, and so on until I have a complete ranking, which the business helps me develop. The top applications have a lot of detail, and the rest have less.

Make sure you include enough detail to make the process worthwhile, and not so much that you won’t maintain it. And by “you” I mean the entire IT organization. If you design the inputs and outputs in a meaningful way you’ll let the person farthest down the chain update the data so it is current, but still accurate.

Application Meta Data

The first element you need to include is of course the application itself. The primary sub-elements are its name and some sort of identifier (which you might already have in System Center or other tracking software), the version, and its general purpose.

More detailed elements might include licensing, the sales location or in the case of open-source software the repository where you got it. Something that is interesting to include is the contact information for the person you dealt with when you bought or obtained the software. You might think that the vendor maintains this information for you, but having that info handy can save you tons of time when you need it.

I also include the date I got the software, and since I’m using a database for this information I normalize the tables where I store it so that I have a history of versions, upgrades and so on. Again, many packages such as System Center already provide this data, you just have to link it to the rest of the elements, which is why that identifier is so important, even if you “home grow” your own system.

Business Value / Risk Assignment

This element, which you might actually break out into two elements, is the result of the ranking exercise I mentioned earlier. I’ve done the first part of the work, which is gathering the names and purposes and licenses of all the applications in my organization, and presented it to the executives. I’ve explained that there are a limited amount of resources available to keep software running, such as servers, storage and people, and they have determined the impact of each application not working. That gives me a business value of “high,” “medium,” and “low, or perhaps “critical,” “important,” “routine,” or some other designation. You can also use numbers to represent those values in case the descriptors change.

The Risk Assessment part could be combined, or perhaps you want to break that out to say that a particular application is more “fragile” than another. Perhaps it takes more resources to operate, is likely to change to another vendor, or some other vector. In that case, assign a risk element to the application data as well.

This element (or both, if you’ve split them) is important to know when you’re doing a consolidation exercise. You don’t want to virtualize, for instance, two highly-critical applications onto the same hardware. That presents far too high a risk to the organization.

This information also helps you when you are evaluating your High-Availability (HA) and Disaster Recovery (DR) strategies. In this case, you need to evaluate the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) goals for this application — which make up the next two elements you need to track.

And that’s where we’ll stop for Part One — I’ll pick up in Part Two with what RPO and RTO actually mean, and the rest of the elements in an Application Profile.

InformIT Articles and Sample Chapters

Business Continuity isn’t only the purview of the data professional. Rich Schiesser covers the Business Continuity plan in this series on Conducting an Effective Table Top Exercise (TTE).

Books and eBooks

Bruce Michelson has a long relationship with the hardware part of Business Continuity, and his book, Closed Loop Lifecycle Planning®: A Complete Guide to Managing Your PC Fleet, will show you his thoughts on the process.

Online Resources

This entire process fits nicely within the Microsoft Operations Framework, or MOF, which I use a great deal. You can learn a lot at their primary site.