Gathering Information About SQL Server Using Terminal Servers
February 26, 2001
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.
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.
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.
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.
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