Using SQLdiag as a Service
The third way you can collect data using SQLdiag is with a Windows service. You can use the /R parameter to register the utility as a service, and /U to unregister the tool as a service. You can use the /A switch to register SQLdiag as a service with a unique name. Figure 2
shows the properties of the SQLdiag utility registered as a service.
The following command registers SQLdiag as a service with the name DIAG1:
SQLDIAG /R /A DIAG1
As you can see, the SQLdiag service registration appends DIAG$ to the service name when the /A parameter is used to provide the application name. If you choose to only use the /R switch, then the service is named SQLDIAG as shown in Figure 3.
Note that service registration is done using the Log On As account as Local System.
The majority of environments in which you will be using SQLdiag as a
service will likely not have SQL Server sysadmin privileges granted to
the Local System account.
Therefore, after registering the utility as a service, you will need to
change the service account to an account that has Administrator rights
on the Windows machine and sysadmin privileges on the SQL Server
instance(s) to which the service will connect.
WARNING If
you run the service using a user account that is not part of the
Windows Administrator group or the sysadmin role of SQL Server without
the /G switch, you will get the following message in the Windows Application Event Log:
Warning: User SYSTEM is not a member of the sysadmin role on <SQL Server instance name>. Diagnostics for <SQL Server instance name> will not be collected
The next decision you need to make is which
parameters you want to specify for the service. While registering the
service, you can provide parameters as appropriate. In the following
example, SQLdiag is being registered as a service:
'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqldiag.exe' /R /I
C:\SQLDIAG_Data\SQLDIAG_Custom.XML /O 'C:\SQLDIAG_Data'
A successful service registration
reports the message “SQLDIAG Service installed. Exiting.” Note that in
the above command, we needed to specify the path of the configuration
file and the output folder, as well as to ensure that the right set of
configuration options are used and the diagnostic data collected is
captured in the correct folder.
WARNING The
aforementioned success message is reported if you have multiple
versions of SQLdiag available on the machine even if the SQL Server
2012 SQLdiag was not used for the service registration. This happens
because your environment path variable points to the path of an older
version of SQLdiag from another SQL Server 2005, 2008, or 2008 R2
installation, before the SQL Server 2012 path was added to the
environment variable. Once you start the SQLdiag service, the following
error will be reported in the SQLdiag log as well as in the Windows
Application Event Log:
SQLDIAG . Function result: 87. Message: The parameter is incorrect.
SQLDIAG Invalid SQL Server version specified. SQL Server version
11 is not supported by this version of the collector
The above message signifies that an older version
of SQLdiag was used to connect to a SQL Server 2012 instance. When you
start the SQLdiag service, it uses the parameters specified during
service registration to collect the required data based on information
present in the configuration file. You can view events reported by the
SQLdiag service by looking at the Windows Application Event Log, shown
in Figure 4.
Configuring and Running SQLdiag on a Failover Cluster
When configuring a data collection
package for a SQL Server failover cluster instance with Diag Manager,
you need to remember a few salient points:
- Specify the SQL virtual server name as Machine Name.
- Specify the SQL Server instance name in the Instance Name text box. For a default failover cluster instance, enter MSSQLSERVER.
- Once you have the package configured, it is a best practice to run
the SQLdiag data collection utility from the node that is the current
owner of the SQL Server resource.
When running SQLdiag in the default
configuration on a multi-instance failover cluster, the command window
will show numerous errors in red. This is because the default
configuration file, SQLDIAG.XML,
has not been changed to collect data from a specific failover cluster
instance. SQLdiag automatically detects the cluster and gathers logs
and configuration information for every virtual server and instance.
Errors are displayed in the command window because SQLdiag attempts to
connect to each instance at every virtual server, resulting in several
failures.
In this case, either configure the SQLDiag.XML
file with the required target’s virtual server name or ignore these
errors. If any errors are displayed, they can be safely ignored.
Likewise, the log file (##SQLDIAG.LOG)
is usually easier to read and interpret to identify errors that can be
safely ignored. A few of the ignorable messages actually have the text
“you can usually safely ignore this” enclosed in parentheses.
