During the course of the Tools Tips and Tricks series, I had blogged about how to connect to a RANU instance and how to find out the SQL Express user instances active on the machine. Today, I shall discuss how to use SQLDIAG to collect data from a SQL Express user instance.

Since a SQL Express User Instance involves the dynamic spawning of a new instance and connections are allowed locally only using named pipes, normal instance discovery doesn’t work. If you need to collect diagnostic data using SQLDIAG for such an instance, you need to take the following steps:

1. Start the user instance by instantiating a connection to the user instance from the application that uses it.
2. Use any method mentioned in my previous post to get the instance GUID and the named pipe string.
3. Construct a named pipe alias using SQL Server Configuration Manager (see screenshot below). Use SQL Native Client Configuration 32-bit or 64-bit as appropriate for your version of SQL Express.

The Alias Name is in the form of <server name>\<user instance GUID>. The pipe name is what you obtained for the user instance that you are interested in monitoring. Leaver the Server name as blank.
4. Once the alias is created, test if you can connect to it using SQLCMD –S:.\BA78E627-AD14-4 –E locally from the machine that has SQL Express installed on it.
5. Now in the SQLDIAG.XML configuration file that you are using put the server name and instance name as follows:

<Machine name=".">
….
<Instance name="BA78E627-AD14-4" windowsauth="true" ssver="10.5" user="">

After this you can run SQLDIAG from the machine which has SQL Express installed on it as user instances don’t accept remote connections.

Check back tomorrow for another new trick for tools that we use!