Introduction
One of the current trends in data centers, especially startup businesses, is
the use of a colocation facility for their production servers. A number of my
colleagues as well as my current company, contract for services that host our
production servers in a remote location. This allows the IT people at the company
to concentrate on the applications and not worry about the basic requirements of computers.
There are two types of hosting companies. Both of these provide power, HVAC, and network
services in a data center environment for a monthly fee. In the colocation
centers, you rent a rack with these services provided and you provide the servers and are
responsible for installing and configuring the operating systems as well as all of your
higher level services (web, database, mail, etc). Many hosting companies also offer
“managed” services which include the installation and configuration of the operating system,
database, etc. and you are responsible for the applications that run on these services.
The Problem
This sounds like a great idea. Especially the managed platform, which my company has used.
I basically am the sa of a remote server and am only responsible for the operations inside the
database. So I setup tables, keys, stored procedures, etc. Backups, patches, basic maintenance, etc. are all managed
for me. I can also add SQLAgent tasks if I want.
The only problem that I have with this situation has to do with monitoring and performance
tuning. This is a remote server (actually in another state) and I access the server through
the terminal services provided by Windows 2000. In general this works well for me, but there is one
problem.
Perfmon counters cannot be accessed from a remote session.
They must be viewed from the console.
This quote is from this URL
in the Technet section of Microsoft.com. It is also something that I have confirmed. It also
exists with SQL Server 2000 in a terminal session. My company is moving to a colocation
scenario from the managed solution, but I will access the SQL Server from Terminal Services
the majority of the time.
With both SQL 7 and SQL 2000 on Windows 2000, when I connect in terminal server and run the
performance monitor, I cannot even see the SQL Server counters. Even if I run the Performance
application from the Microsoft SQL Server menu group, I get no information back. This
had caused me problems in trying to tune the server as well as perform some capacity planning. For
a long time I had to rely on Profiler to do my performance monitoring.
The Solution
After lots of digging and experimenting, I discovered a solution for my problem. I will
describe what I did and then explain what I think is happening.
The Procedure
On a test server that is on my local network, I ran the performance monitor.
I then create a new log item in the Counter Logs section as show below.
Before I add counters to the list, I select the “Use local computer counters:” item
instead of the default “Select counters from computer:”
I then add the counters I want and close the “Add counters” dialog.
Right click the new counter log item that was added and select “Save Settings As”
and enter a filename. This will save an HTM file to your file system.
I then copy this file to the remote server using Cut-and-Paste or ftp.
Start performance monitor and Counter Logs. Right click this Counter Logs item
and select “New Log Settings From…”. Choose the .htm file that was copied to this
server. The two images below show the addition of this log file.
Start and stop this as necessary to collect the information needed. You will probably
get an error that the counter failed to start, but it will still start. The error that
I got is shown below, but after a few minutes, I find the log was started.
This basically allows you create a template that you can use to log performance data, which
is usually the best way to monitor the server. You can then copy the log file back to your
local machine and load the data into your local Performance Monitor, or Excel, or whatever
your analysis tool of choice is.
From what I have learned, this appears to be a permissions issue with terminal server and
the third party counters installed on the server. I know that SQL Server is a Microsoft product, but
it is not native to NT/2000 and falls in the same class as other 3rd party products. I know that
I have this same problem with the Allaire Cold Fusion Application server and viewing the counters. By selecting
the “Local computer counters” I am not tying my selections to a particular server.
Conclusion
This technique is also handy if you have servers in your office that you want to administer from
your desktop without going to the console. Or if you want to create one consistent performance
monitoring file and distribute it to all servers. If you do not change to the local computer counters,
and distribute the file, you will end of collecting data about the single server from all servers.
I hope that most of you do spend some time performance monitoring and I plan on writing some more
articles on how I monitor performance. This is a big deal for my company as we are moving to a new platform
and trying to ensure good performance for our application. As I can distill my notes into something that
makes sense and would be useful for others, I will be adding some more articles.
As always, I welcome feedback on this article and any others. Also, please remember to vote at the bottom
of this page.
Steve Jones
February 2001
