When a server is set up with SQL Clustering, generation of
SQLDIAG output may require additional steps. This article describes how to
connect to and execute SQLDIAG.exe against clustered SQL Server 7.0
installations and clustered SQL Server 2005 instances or clustered SQL Server 2000 instances locally and remotely.
To retrieve SQLDIAG and WINMSD information from a clustered SQL
Server 7.0 server, follow these steps:
Connect to the virtual server using Query
Analyzer.
Run the following command to obtain the SQL Server
information:
xp_cmdshell 'shared drive\mssql7\binn\sqldiag -E -O local drive\SQLdiag.txt'
Run the following from a command prompt to get the system
information:
winmsd \\<nodename> /a /f
Executing the code creates a file named Nodename.txt in the directory
from which WINMSD was executed. For more information about WINMSD parameters,
run the following from a command prompt:
winmsd /?
On a computer that is running Microsoft Windows 2000, the correct
syntax for WINMSD is:
winmsd /computer mycomputer /report myreport.txt
SQL Server 2000 Enterprise Edition
This articles presents three options for for running the SQLDIAG
utility on a clustered SQL Server 2000 instance.
Option 1
To run the SQLDIAG utility on a clustered SQL Server 2000
instance, follow these steps:
Connect to the virtual SQL Server by using Query
Analyzer.
Run the following command to obtain the SQL Server
information:
xp_cmdshell 'full path for the Binn folder\sqldiag.exe -Iinstancename -Ooutputfile –E -C'
Note Omit the -I option to connect to the default clustered instance
of SQL Server.
For more information about the SQLDIAG utility and its
arguments, see the "sqldiag Utility" topic in SQL Server Books Online.
The advantage to running the SQLDIAG utility by using the xp_cmdshell extended stored procedure is that you do not have to be logged on
locally to the active node of the cluster. For example, assume that you have a
named virtual SQL Server 2000 server: VSQL2\INST2 (where the SQL Network Name
is VSQL2 and the SQL Server instance name is INST2). The full path to the BINN
folder is C:\Program Files\Microsoft SQL Server\MSSQL$INST2\Binn. To run the
SQLDIAG utility, open Query Analyzer. Connect to VSQL2\INST2, and then run the
following command:
If you are logged on locally to the active
node of the cluster, you can follow these steps to run the SQLDIAG utility from
the DOS command window:
Open a DOS command window on the node on which the
clustered SQL Server 2000 instance is running.
Move to the BINN folder. By default, the BINN folder for a
clustered SQL Server 2000 default instance is C:\Program Files\Microsoft SQL
Server\MSSQL\BINN. The default BINN folder for a named instance is C:\Program
Files\Microsoft SQL Server\MSSQL$NAMEDINSTANCE\BINN.
Type set _CLUSTER_NETWORK_NAME_=SQL NETWORK
NAME, and then press ENTER.
Note The SQL NETWORK NAME is the SQL Server virtual name for a
named instance. This is only the first part of the name. For example, if the instance name is
VSQL2\INST2, the SQL NETWORK NAME is VSQL2.
Type sqldiag -Iinstancename -Ooutputfile -E
-C, and then press ENTER. If
your Windows account is not mapped to the sysadmin role in SQL Server, you can
replace -E with –Uuser_name -Ppassword and pass in an account that is mapped to the
sysadmin role.
Note Omit the -I option to connect to the default clustered SQL Server 2000
instance. For more information about the SQLDIAG utility and its
arguments, see the "sqldiag Utility" topic in SQL Server Books Online.
EXAMPLE
For this example, assume that your SQL Network Name is
VSQL2, and that the SQL Server instance name is INST2. To run the SQLDIAG
utility on your clustered SQL Server 2000 named instance, follow these steps:
Open a DOS window.
Type the following commands on separate lines:
CD C:\Program Files\Microsoft SQL
Server\MSSQL$INST2\Binn
set
_CLUSTER_NETWORK_NAME_=VSQL2
sqldiag –I INST2 –Oc:\sqldiag_INST2.txt -E
-C
The next two options require you to add resources to the Disk
Group that owns the instance of SQL Server. Microsoft recommends that you take
the resource that owns the instance off line, and that you delete it when the
SQLDIAG output has been collected. If the resource is not removed, then when a
failover occurs, or when the group is moved, the resource will be brought
online and a SQLDIAG.txt file will be created. If you do not delete the
resource, service pack installations may fail.
To run SQLDIAG and connect to a specific virtual
server, you must add the program to the disk group that owns that instance of
SQL Server. Additionally, because SQLDIAG runs in a command window, the
resource is started on the node that owns it. If no output is generated,
SQLDIAG might be connecting through a local pipe. If the cluster has failed
over to another node before running SQLDIAG, the local pipe connection fails
and you do not get any SQL Server information.
Option 2
To run SQLDIAG on a clustered SQL Server 2000 instance,
follow these steps:
Open Cluster Administrator.
Create a New Resource. To create a New Resource, follow
these steps:
Right-click the Disk Group that has the SQL Server
instance, point to New, and then click Resource.
Type a name in the Name box.
You can type a description in the Description box. However, the description is optional.
In the Resource Type box, click to select Generic Application.
In the Group box, click to select the Disk Group that owns the
SQL instance.
Clear the Run this resource in a separate Resource Monitor check box.
Click Next.
The Possible Owners dialog box appears. By default, both nodes are available to bring
the new resource on line. If this is not the case, it is a possible indicator
of a problem node. When satisfied with possible owners, click Next.
In the Dependencies dialog box, select SQL Network Name (Desired Instance) from the Available Resources list. Click Add to add the selected resource as a dependency, and then click Next.
In the Generic Application Parameters dialog box, type Cmd.exe in the Command line box.
Type the following in the Current Directory text box:
Current Directory =
%WINDIR%\System32
Note You must enter the actual path of the windows
directory.
Click to select the Allow application to interact with desktop check box.
Check the Use Network Name for computer
name check box.
Click Next.
In the Registry Replication dialog box, click Finish.
After you save the resource, you can edit the properties
for the resource. On the Advanced tab for the resource, click Do not restart.
Bring the generic application online. To bring the resource
online, right-click and then click Bring Online. A command shell (command prompt) will open in the background. If
the New Generic Application starts online without a command window, make sure
that you are on the node that owns the SQLDIAG resource, and that the Allow application to interact with desktop option is selected in the Properties of the generic application.
Locate SQLDIAG.exe. To do so, in the command shell window,
change to the %SQL_DIR%\BINN folder. By default, the path is Program
Files\Microsoft SQL Server\MSSQL$%INSTANCE_NAME%\BINN.
Run SQLDIAG.exe. To run SQLDIAG.exe, in the command shell
window, type the following, and then press ENTER:
Note Omit the -I option to connect to the default clustered SQL Server 2000
instance.
Option 3
Terminal services does not currently support the remote
execution of a Microsoft Windows NT command shell that interacts with the
desktop. Therefore, you must use additional measures to generate the SQLDIAG
output from a remote location. One solution is to create a command file to run
SQLDIAG by using the options you want, and to then host it as a generic
application on the cluster. Use these steps to create and host a command file
to run SQLDIAG from a remote source:
Create a directory and a new text file that is named
SQLDIAG.cmd on the clustered drive that hosts the instance of SQL Server you
want. For example, S:\sqldiag.
Include the following contents, and edit as appropriate
(any paths or options) that are required for your
environment:
Sqldiag.exe /I%instancename% /E /O%output path
and filename%
In cluster administrator, create the generic application by
using steps 1through 2j under "Option 2." For step 2j, use the following
parameters:
Command line = cmd.exe /k
s:\sqldiag\sqldiag.cmd
Current directory = C:\Program Files\Microsoft SQL
Server\MSSQL$VINST01\Binn
After you save the resource, edit the
properties for the resource. To do so, on the Advanced tab for the resource, click to select Do not restart.
Bringing the resource online runs the SQLDIAG command as
specified in the SQLDIAG.cmd text file. The /k option keeps the virtual command shell open so that it can taken
off line again gracefully.
SQL Server 2005 Enterprise Edition
For information about how to connect to and run SQLDIAG.exe against clustered SQL Server 2005 instances locally and remotely, search for "SQLdiag Utility" in SQL Server 2005 Books Online.
Back to the top
