I have always recommended that DBAs be as proactive as possible, catching potential problems, and actual problems, as soon as possible before they can negatively affect a SQL Server’s performance or availability. In fact, I am working on a new book, tentatively titled, How to Perform a SQL Server Health Check, which will provide a large number of check-off lists DBAs can use to verify that their SQL Server instances are correctly configured using generally accepted SQL Server best practices.
So at the PASS Summit this past November, when Microsoft Codename Atlanta was announced, I was intrigued, as this new tool seemed to overlap what I was trying to accomplish in my book. Now that I have reviewed Atlanta, I don’t have anything to worry about. What Atlanta offers, and what my book will offer, are substantially different.
So what is Microsoft Codename Atlanta? Essentially, Atlanta is a cloud-based, SQL Server configuration-monitoring tool that proactively checks your SQL Server 2008 and SQL Server 2008 R2 instances for a limited number of potential configuration problems, and provides you with alerts about these problems, along with helpful information on what the alerts mean and how to correct them. The tool can also be used by Microsoft CSS to view your SQL Server’s configuration, making it easier for them to help resolve problems should you have to contact them for help with a difficult problem.
In this article, I’ll take a brief look at the beta version of Atlanta, showing you how it works, how to install it, how to use it, and show you some of the alerts it produces. Since this product is in beta, its features could change by the time you read this article, so the best way to learn about this tool is to download it and try it out yourself.
Why is it called Microsoft Codename Atlanta? Because the tool is currently in beta and because Microsoft likes to give new products codenames while they are under development. When the product is finally released, it will be given an official name. For the purposes of this article, I will refer to it simply as Atlanta.
Atlanta is supposed to launch sometime in the first half of 2011, and its licensing cost has not yet been made public.
Atlanta High-Level Overview
In brief, here’s how Atlanta works. First, you install an agent (Systems Center Operations Agent 2007 R2) on every SQL Server box. For example, if you have 10 SQL Server instances on 10 different boxes, then you will have to install the agent 10 different times. In addition, at least one server needs to be designated as a Gateway. A Gateway (another service) is used to collect data from each of the agents, and once every 24 hours, the collected data is sent to the Atlanta cloud, where data is analyzed and aggregated. Once the data is analyzed for potential configuration issues, then you can view the alerts for each of your monitored instances by accessing the Atlanta web portal via a web browser. At this point, you can view the alerts, learn about them, and then decide what action, if any, you want to take based on the alert. Each of these will be described later in this article.
Prerequisites for Installing Atlanta
Before you can install and use Atlanta, you have to ensure your monitored SQL Servers have the following software:
- Windows Server 2008 or later (32- or 64-bit)
- SQL Server 2008 or later (32- or 64-bit)
- A web browser that supports Silverlight 4.0 (which also must be installed). This can be installed on a monitored SQL Server, or on any computer with access to the Internet.
See this webpage for detailed Atlanta requirements.
Installing Atlanta
Before you can do anything with Atlanta, you must go to the Atlanta website and create an account using a Windows Live ID. If you don’t already have a Windows Live ID, then you will have to create one before creating an Atlanta account.
Once you have an Atlanta account and log in, you will see a screen like the one below.
As you can see above, installing Atlanta is a multi-step process. First, you must download what is called the Registration Certificate. It is required when you install a Gateway. Second, you must download the Atlanta Gateway and Agent Setup program, which allows you to install the Gateway, the Agent, or both. Third, you must select a box where the Gateway is to be installed. And last, you must install the agent on each SQL Server box you want monitored.
As I have been testing Atlanta, I have been running both the Gateway and the Agent on a single SQL Server box to keep things simple. Once Atlanta is released, I would suggest that you run the Gateway on a server not running a production SQL Server instance so that its overhead will not negatively affect SQL Server’s performance.
After downloading the two files, I started the setup program, which opens to a command prompt, as shown below.
As you can see, the setup program allows you to install the Agent, the Gateway, or both on a server. For my testing, I chose to install both the Gateway and Agent on the same test box.
After I selected “Both” from the above screen, the following wizard appeared.
This is just a splash screen, so I clicked on “Next.”
The first step is to install the Gateway. To do this, you must point at the Registration Certificate file you downloaded previously, which I have done above. Then I clicked “Next”.
The next screen to appear (see above) is a confirmation screen, and I clicked on “Install”.
After a brief time, the above screen appears, telling you that the installation is completed. I clicked on “Finish”.
After clicking on “Finish”, the command prompt which originally appeared now requires that you “Press any to key to continue”. When I did this, the command prompt disappeared and my simple, one server test box was configured to run Atlanta.
Atlanta offers some rudimentary configuration options by directly editing the registry. How to do this is explained in the documentation, but you would probably be wise not to make any changes, unless of course, you love to tinker and break things.
Using Atlanta
Of course, now I was excited. I wanted to go to the “Alerts” screen to see what Atlanta had discovered about my test box. Unfortunately, I was disappointed. This is because it take about 24 hours after installing Atlanta before it sends the data to the cloud, processes it, and returns you any alerts.
So the next day I logged back onto the Atlanta Website Portal and I got my first taste of the kinds of alerts that Atlanta can produce.
Above, you can see that Atlanta provided 12 different alerts. The first 6 alerts tell me that I haven’t performed a DBCC CHECKDB for 6 of the databases on this SQL Server instance. The next 5 alerts tell me that I haven’t performed a database backup on 5 of the databases on this SQL Server instance. And the last alert tells me that I need to add a missing hotfix and to also add a trace flag in order to active the hotfix.
How many alerts are included in Atlanta? I don’t know and I couldn’t find out. Hopefully, Microsoft will eventually publish this data for us.
While each of these are important pieces of information, I was a little underwhelmed at the alerts that were provided. I assume that the lack of additional alerts is because Atlanta is still in beta and that all of the alerts that will be in shipping version of the product are not all currently implemented. As a comparison, I ran the Microsoft SQL Server 2008 R2 Best Practices Analyzer (BPA) against this same server, and it found quite a few more problems with my test server than did Atlanta, as you can see below.
As you can see, the BPA, in its current form, can provide much more useful results than the existing version of Atlanta. I am assuming that when Atlanta is finally released, that its features will match, if not exceed, what the BPA offers.
Now let’s go back to the Atlanta alerts screen and take a closer look.
Above, on the alerts screen, I have highlighted the first alert. Notice that at the bottom of the screen is a brief description of the alert. It is a little hard to read in the above screen shot, so I have displayed in below in a more readable form.
As you can see, this is a straight-forward description of the alert. In addition, I am told that it has been 37 days since the DBCC CHECKDB has been run on this database. Also notice that the information you see above is from the Detail tab, and that there are two additional tabs to view. So let’s take a look at them.
The Solution tab lists a Knowledge Base (KB) URL you can click, in addition to providing a link to contact Microsoft support via email, online, or by phone. If you click on the KB link, you are directed to this webpage.
Although you can only read part of this page, notice at the bottom of the page is a reference to the BPA. In other words, the KB article you are being referred to is the same KB article that you would be referred to if you were using the BPA for checking out your instance’s configuration. The explanation on the page is very good, and I would assume that these pages will be eventually modified to reflect Atlanta content.
If you click on the link to contact Microsoft support via email, online, or by phone, you have first login using your Live ID, and the following screen appears.
This screen allows you to select the topic of your question, and once you select a general support topic, you are then asked for a more detailed sub-topic (not shown here). Once you have selected the topic and sub-topic, the following screen appears.
As you might expect, this is where you may have to lay out some money, or use up some of your prepaid support calls. So you only want to use this option if you are willing to pay for help.
Now that we have looked at the Details and the Solution tabs, let’s look at the Context tab, which is shown below.
This is obviously an XML file that describes the Alert. The documentation does not currently describe the purpose of this tab.
Now that we have taken a brief look at what one Alert looks like, there is one more part of the Alert screen that I want to mention. Notice that the Alert below has a Status of Active, and an Occurrence of 2.
This means the Alert has not been dealt with (Status is Active) and that the Alert has fired 2 times (Occurrence = 2). Once an Alert starts, and if the cause of the Alert is not fixed, the Alert’s Occurrence will increment until the problem is fixed. If an Alert has been corrected, or if you choose to ignore the Alert, you can choose to select the Alert and the click on Close. This will remove the Alert from the screen, until, of course, should the Alert reoccur. The Activate option allows you to reactive a previously closed Alert, although this option doesn’t appear be working with the beta version I was using for this article.
Exploring More of the Atlanta User Interface
As you may have noticed, that there a series of tabs to the right of the screen, as show below. Up until this time, we have only looked at the Alerts tab. So let’s check out the other tabs and see what they do.
The second tab is called the Configuration tab and displays current configuration settings that have been collected by Atlanta about your SQL Server instance.
When you open up any of the lists, you will see all the data that Atlanta collects about your SQL Server instance.
The third tab is called the Configuration Change History tab, which shows you not only the Update Value (or current value) of the various configuration settings collected by Atlanta, but the previous value as well. Because this is a new installation, the Previous Value column doesn’t have any useful information.
On the other hand, once an instance is up and running for some time, and configurations changes tracked by Atlanta are made, then Atlanta will keep a history of the changes, providing you an audit trail of sorts. Of course, this is not a complete audit trail of all the changes on your instance, plus it doesn’t show who made the change. But at least does let you know if some key configuration settings have changed over time.
The fourth tab is the Servers tab.
This screen shows all of the Gateways and any instances of SQL Server that are running the Agent. This screen also allows you to add additional servers for monitoring by Atlanta, and it shows you the last time that configuration information was uploaded from the Agent to the Gateway, or from the Gateway to Atlanta for analysis.
The fifth and final tab is the Account tab, where you manage your Atlanta account.
The Account screen is self-explanatory, and is where you can delete the account once you are done experimenting with it.
As you can see, Atlanta is very straight-forward and easy to use. It’s design is simple and to the point.
Atlanta Pros & Cons
As I was testing Atlanta, one question that I kept asking myself was whether or not it is a product that I would want to use in a production environment, once it was released in its final form. Well, based on the beta, it is still too early for me to decide. Here are some pros and cons about the tool that I think you will want to consider if you decide to evaluate Atlanta.
Pros
- Easy to install.
- Simple to use.
- The explanations of the Alerts seemed to be useful and relatively complete.
- Provides a single location to monitor your SQL Server instances.
- It’s web-based, so you can access it anywhere.
- It is integrated with Microsoft Support, which should make it easier for you to work with Microsoft Support when a major problem arises. This is because Microsoft Support can use the information collected by Atlanta to help troubleshoot your problem.
Cons
- A service has to be installed on each instance to be monitored. This not only means that it must be physically installed and updated, it takes up valuable SQL Server resources, especially memory.
- The data is collects is not real-time, so you can’t depend on it for catching problems are are happening now.
- Only a limited set of configuration information is monitored, and the number of alerts is not great. This will probably improve in the final version, but it still has a limited ability to collect important SQL Server information that should be monitored, such as performance data, or telling you if a job failed.
- Only supports Windows 2008 and SQL Server 2008 and higher, which limits its usefulness in most SQL Server shops that run many different versions of SQL Server.
- I seemed to get more alerts (false positives) than I would have expected to see. I would like to see the ability turn off a alert, or configure an alert for my specific needs, which can’t be done now.
- Although not a lot of data about your SQL Server instances is sent over the Internet, some security-conscious shops may not feel comfortable sending unsecured data about their SQL Server over the Internet.
- If Internet connectivity is lost, then Atlanta is unavailable.
Summary
One big unknown now is how much Atlanta will cost. If the cost is very low, then it might be a worthwhile product. On the other hand, if it is expensive, then I don’t see much of a future for the product, especially given its limited functionality. If I were Microsoft, I would give this service away for free, just as they give the SQL Server 2008 R2 Best Practices Analyzer away for free. This way, they will have a tool available that Microsoft Support can use to better help DBAs when difficult problems arise.
Additional Resources
Microsoft Codename Atlanta Official Website
Atlanta Microsoft System Center Website
