PRB: SQL performance counters may be missing after MDAC installation on a cluster

On a clustered SQL Server, the SQL performance counters may be missing on the primary node of an active/passive or active/active cluster despite the existence of the SQL counters in the following registry keys:

For SQL perfmon counters to show up, the named pipe client-side network libraries, Dbnmpntw.dll and Sqlctr60.dll in the case of SQL Server 6.5 and Sqlctr70.dll in SQL Server 7.0, should be bound to Vernel32.dll, and binding takes place during the SQL cluster installation. On MDAC installation, a new copy of the library is copied to the %windir% folder. Since the %windir% comes first in the Windows NT path variable, the new Dbnmpntw.dll that is not bound to Vernel32.dll is used, and SQL perfmon counters fail to appear. This problem can occur on SQL Server 6.5 and SQL Server 7.0, and most likely after an installation of a new service pack when MDAC is installed first each time.

Make sure Dbnmpntw.dll is bound to Vernel32 and Sqlctr70.dll/Sqlctr60.dll are bound to Vernel32.dll and Vdvapi32.dll using "quick view." If not, follow these steps:

SQL Server 7.0:

1. Save a copy of Dbnmpntw.dll and Sqlctr70.dll from Winnt\System32 for the future.
2. Run Apf32cvt to bind the DLLs, this only can run successfully on the passive node:

   apf32cvt vernel32 vdvapi32 vtwdblib c:\winnt\system32\dbnmpntw.dll 
   apf32cvt vernel32 vdvapi32 vtwdblib c:\winnt\system32\sqlctr70.dll 
   					

   The Apf32cvt.exe file is available on your SQL Server 7.0 CD. For more information about how to obtain Apf32cvt.exe, see the following article in the Microsoft Knowledge Base:
   239473  (http://support.microsoft.com/kb/239473/ ) FIX: 70rebind.exe for Windows 2000 and MDAC upgrades on clustered SQL Server 7.0 servers
3. Reboot the passive node.
4. Move the SQL Group by failover and run the same command on the other node. Reboot the new passive node.
5. Unloaded and reloaded the counters by running the following commands on the active node:

   unlodctr mssqlserver$virtualservername
   lodctr <\\virtualservername> g:\mssql7\binn\sqlctr.ini
   					

6. Move the SQL Group by failover, and then run the same commands on the other node.
7. Run performance monitor. If it is open, close it, and then re-open it. You should then see the counters.

SQL Server 6.5:

1. Save a copy of Dbnmpntw.dll and Sqlctr60.dll from Mssql\Binn for the future.
2. Copy the Dbnmpntw.dll and Sqlctr60.dll to a new folder, say C:\Newfolder.
3. Run apfsql from SQL 6.5EE CD to bind the DLLs:

   apfsql c:\newfolder
   						

4. Copy the Dbnmpntw.dll and Sqlctr60.dll from C:\Newfolder to Winnt\System32.

If the perfmon counters do not appear when SQL Server is clustered, you could uncluster SQL Server to see if they appear when it is unclustered. But if the counters do not appear when they are unclustered, refer to the following article in the Microsoft Knowledge Base:

SQL Server Performance Counters on Clustered SQL Servers

SQL Server Performance Monitor counters for the Virtual Server are not present when SQL Server 7.0 is set up with a virtual SQL Server configuration and the passive node has control of the resources. The counters will not be available again to the primary node until the whole cluster is shutdown and restarted. Even then, availability is sporadic.

The SQL Server extension counters must be found when the system initially starts. Since the counters DLL file is located in the \\Mssql\Binn directory for SQL Server 6.5 by default, the counters are never found when the initial system startup takes place because the cluster drive in which SQL Server is installed is not accessible until the MSCS resources are all online.

SQL Server 7.0 already places these counters in the %Systemroot%\System32\ directory so that they are available. To make the Sqlctr60.dll file available, place a copy of the Sqlctr60.dll file in the %Systemroot%\System32 directory. The Sqlctr70.dll file is placed in this directory by default.

For more information about SQL Server performance counters, click the following article number to view the article in the Microsoft Knowledge Base: WARNING: If you use the following article, read it and compare it to other sections in this Microsoft Knowledge Base article that discuss rebuilding the SQL Server registry entries. In summary, performance counters on clustered SQL Servers are not always available. When performance counters are available, they are usually only on the primary node if no failover has occurred.