Tag Archives: SQL Express

Tools Tips and Tricks #5: SQLDIAG and RANU

Posted on by

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.

image

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!

Advertisement

Tools Tips and Tricks #2: SQL Express RANU instances

Posted on by

Recently I had worked on an issue with Run As User Instances a.k.a. RANU. The user instance, also known as a child or client instance, is an instance of SQL Server that is generated by the parent instance (the primary instance running as a service, such as SQL Express edition) on behalf of a user. The user instance runs as a user process under the security context of that user. The user instance is isolated from the parent instance and any other user instances running on the machine. The user instance feature is also referred to as “Run As Normal User” (RANU). This is not a feature of SQL Server that is common knowledge to most people in the SQL realm. Note the following mention in BOL for the topic “User Instances for Non-Administrators”:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Why did I decide to talk about a SQL Server feature in a series that I am running on my blog related to tools. Well, this post is about connecting to a RANU instance and the tools available to do this. But this is a lesser known beast, I had to set some context first. Smile

User instances are created when the “User Instance option” is set in the SQL Client connection string. Following is a section of a Visual Basic configuration file that shows the User Instance option:

<connectionStrings>
<add name=”TestVB1.Settings.Database1ConnectionString” connectionString=”Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database1.mdf; Integrated Security=True; User Instance=True” providerName=”System.Data.SqlClient” />
</connectionStrings>

How can I find out if there are RANU/User Instances running on the machine?

On the SQLEXPRESS instance, the configuration option “user instances enabled” in should be enabled. Then execute the query shown in the screenshot below against the SQLEXPRESS instance:

image

The heart_beat column value will tell you is the instance is online or not. For an online instance, the heart_beat will have a value set to alive. RANU instances allow only local Named Pipe connections. So there are three ways how you can connect to a RANU instance:

1. Using Management Studio

image

a. Launch a Management Studio on the machine where the SQL Express instance is running.
b. In the Connect to Server dialog box, you will have to specify the instance_pipe_name value obtained from the above output as the Server Name.

2. Using SQLCMD

Using the sqlcmd utility, you can also connect using the pipe name in the following manner:

sqlcmd -Snp:\\.\pipe\BA78E627-AD14-4F\tsql\query -E

3. Using SQL Server Express Utility

This is a utility available on the Microsoft Downloads site but is not supported by CSS. One of the tasks that can be performed by this utility is to connect to User Instances. The SSEUtil.exe –childlist command will list all the RANU instances on the machine. The output is the same as the one shown above which was retrieved using sys.dm_os_child_instances. Refer screenshot below.

image

The ReadMe.htm available with the installation of the utility has information on what can be done with this utility other than connecting to a user instance.

You can then connect to the instance using the following command:

SSEUtil.exe -c –child <domain>\<user name>

How do I shutdown a RANU/User Instance?

A RANU instance has a defined timeout value of 60 minutes; after 60 minutes without activity, RANU shuts itself down. The RANU timeout is configurable at the parent instances through the ‘User Instance Timeout’ setting. If you want to shutdown a RANU instance before the timeout is reached, then you can use one of the methods listed above to connect to the User Instance and issue the following command:

SHUTDOWN WITH NOWAIT

References:
Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances
Connecting to SQL Express User Instances in Management Studio

Now it’s time to shutdown this post… Adieu till the next post.