SQL Server Reference Guide

In the previous article in this series, I explained what an application profile is, and how you can perform one for your environment. I showed you a few of the elements which 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.

In this article, I’ll complete that description. In other tutorials I’ll show you how I leverage this data with the SQL Server Central Management System (SQLCMS) environment I created earlier, and show you the sample schema I created for my own application profiles.

Elements of an Application Profile (Continued)

In the last tutorial, I left off with the following elements already defined:

• Application Meta Data
• Identifier
• Name
• Version
• General Purpose
• Acquisition Date
• License Type
• License Renewal
• Business Value
• Risk Assignment

Next, I’ll explain the rest of the elements you might want to track in your application profile.

RPO and RTO

I won’t cover the complete technical discussion of RPO and RTO here, but they are vital to your HA and DR strategy, but a quick overview is in order.

A Recovery Point Objective (RPO) at its most basic is how much data your organization will tolerate losing. If you can lose one transaction, then your system should not allow for any further loss than this amount of data.

Of course, the organization will tell you they don’t want to lose any data, but as you and I know, that is an expensive objective. When you explain how many people, licenses and hardware elements are required to make that work, they will probably back off a bit and be more realistic in their evaluation. This number is also sometimes expressed in hours, so you want to record that for each application. As a practical example, this is normally the interval at which you back up your transaction logs. If you back up every hour, then in a data loss situation you theoretically will only lose an hour’s worth of data, and that many transactions.

The Recovery Time Objective (RTO) is the amount of time before the system is available again. That means if the system goes down, and you have to restore the database and logs, how long will that take?

Once again, the organization will tell you that they want the system back up in seconds, and once again, you have to explain what kind of resources that takes. To do that, you need to have realistic, practical information about what is really achievable, which means you need to “practice” a disaster recovery, and be honest about the results.

I was the IT manager at one location where the team assured me of a two-hour RTO. I nodded, walked out of the meeting into the test environment, and trashed the hard drive. I then walked back in to the meeting and said, “OK. Test is down. Go.” They stared at me in disbelief, and exclaimed “But Roy is off today! He’s the only one with the password to database X!” I said, “Clock’s ticking.” Two days, and a lot of hurt feelings later, the system was back on line. The team then re-evaluated their procedures on their own and got that RTO down to two hours, although it meant scrapping their current procedures and coming up with new ones. It turned out to be a great learning exercise, although I did have a “near miss” or two in the parking lot later!

As you can see, RPO and RTO are essential elements to know for HA and DR exercises, something bundled into a “Business Continuity Plan”.

Service or Operating Level Agreements

A Service Level Agreement or SLA is a contract between you and the organization on how well the system will perform, stay available and so on. This is sometimes termed an “Operating Level Agreement”. It’s important to have these on hand, and develop them in conjunction with the business. It’s your job to carefully explain what costs and resources are involved in any level of system maintenance and recovery. The organization simply does not know how the software continues to operate, which is yet another reason for performing this application profile.

You need to record this element so that you have the right precedence when a system is performing poorly or you need to budget for high-availability and disaster recovery. This is normally an outcome from the life-boat exercise I mentioned earlier.

Data Processing and Flow

This element might take the form of a diagram. It shows the inputs, processing, transfer and display of information from the time a user presses the “Save” button until they see a confirmation on the screen.

I stay pretty high-level here. You don’t necessarily have to include the source code for each and every application, just where the data basically starts and ends, and the path it travels back and forth. Think of a simple Query Plan and you’ll have the idea. In fact, you can store it like a Query Plan as an XML element in your tracking system so that you can extract pictures or data from it.

This information is vital for troubleshooting, performance tuning, high-availability and disaster recovery. If you just protect or tune one element in the system, such as the database, you aren’t guaranteeing anything other than that one element being safe or fast. It’s a system, so you have to know all the parts.

Data Retention and Disposition

This element is another one of those that might be broken out into multiple parts. It’s also another element that I see a severe lack of consideration in. There are many organizations that I work with as a DBA that have given a great deal of thought and effort to creating, installing and configuring a system, and even for maintaining it. But at some point the data becomes so large or interrelated that it reaches a crisis point. Only then do they consider creating an information lifecycle.

So as you are profiling the application, there are two big questions to ask. The first is of the business or organization. You should question them about how long each large element of data should be retained. They may be surprised by this question, believing that you plan to keep all the data, all the time. Once again, you need to educate them about the costs and resources associated with this process.

The second question is of the application vendor or application development team. Make sure there is some sort of mechanism that will allow you to archive the data periodically, and detail that information here.

You may also legal responsibilities around keeping data, and how it should be disposed of. This is such an important question that I’ll leave it for another element.

Why is this element included? Well, for performance tuning, system sizing and consolidation this information is vital, and for any kind of HA or DR situation you’ll need it as well. Once again, from a legal standpoint you may actually be required to maintain it anyway.

Data Sizing

This is an important element as well for all the reasons mentioned above, and one of the most difficult to obtain. If the system is new, you may not be able to predict the size of the initial data set or have any idea about how quickly it will grow. There are strategies, however, that will help you find this information out.

You’ll know, of course, the current size of the database by simply measuring it. If it’s a brand-new system, then the data will probably be quite small. You should still record that, so that you have a starting point. You might also import data from other locations into the system, so you’ll have a good starting size number that way as well.

You can query the vendor (if this is an established system) to gauge what they have seen in the field for the growth of the system. They might be hesitant to tell you, since no situation is exactly like yours, but you should press them so that you have at least some idea of how the system grows.

Once you’ve recorded a starting value and date, and then a project value of growth, you should take periodic measurements to record the actual growth. How often you sample this data will depend on how active your system is. I normally monitor each day for a month, and then back off of that daily measurement to weekly or monthly.

If you are using the SQLCMS system I mentioned, you’ll be able to pull this data from there. If not, check out my other tutorials on data files to find queries that will detail this information for you.

Legal Constraints/ Security Requirements

This might be the most important element in an application profile, and in fact, you might already have a system in place for this. If your company is subject to any government rules about electronic data, then you need to track how you’re complying with those laws.

Even if you don’t have a legal requirement to do so, you should track the security requirements of the data you store. Sure, the data probably has security on it already, but that doesn’t mean it’s the right security. For that you need to perform a complete security audit, and this is a good time and place to store that data.

Performance Profile

You might be able to derive the performance of the application from all of the elements above. In fact, that might be the driver for the application profile to begin with. But if you already know the bottlenecks of the system, or you learn about them through this exercise, you should record that data as an element of the application profile.

First, what do I mean by the “Performance Profile” of the application? It’s a combination of the primary resource stress points and how they are inflected. For instance, you’ve probably seen those “heat maps” of an aircraft’s wing or perhaps on a car or boat design. Those red areas on the wing or other surface show the designer where the friction from the air or water is, and allow them to “dial up” those numbers until the surface fails.

You can do that with your applications as well. Once you’ve identified the key components of the application, you can record how they exercise the CPU, Memory, Hard Drive or Network Interfaces, and which one of those gets “hotter” the fastest. Then using those numbers you can predict the load at which that element will fail. This is invaluable for system sizing and consolidation efforts.

Finalizing Your Analysis

Does every application your system uses need this level of review? Do you really have to record this much information? No, and No.

I try to at least record the Application Meta Data, Data Sizing and Data Retention for all of the applications I know about. As I’m asked to tune or troubleshoot the application performance on my databases, I add more elements to them. In fact, performance tuning and troubleshooting an application adds to the value of the data, and the data adds values to those efforts. It’s a symbiotic relationship.

We all have lots of work to do already. But if you take some time to perform an Application Profile in your organization, even if you do it in stages, you’re helping yourself. It’s the money in the bank — assuming that it’s one of those banks that stays in business!

InformIT Articles and Sample Chapters

RPO and RTO are discussed further in Disaster Recovery Test Case Study Part One: Planning.

Books and eBooks

There are already systems out there that will record and track much of this data for you, automatically. Microsoft’s System Center is detailed in this great book, System Center Operations Manager 2007 Unleashed (also available as a downloadable eBook and in Safari Books Online).

Online Resources

You can get a free “Continuity of Operations” planning kit from the U.S. government here.