Connect to SQL Express 'User Instances'

Introduction

In the free SQL Server 2005 Express Edition, you get a new connection string parameter 'User instance=true'.

"data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|people.mdf;User Instance=true"

When this parameter is specified, SQL Express will create another instance of the sqlservr.exe process on the client. This process is running in the security context of the currently logged on user. The connection object passed back to the client is connected to this new user child instance, running as a client process in the user's security context.

More details about User Instances? Read this excellent article on msdn: Working with User Instances

SQL Profiler

For monitoring purposes (what's going on under the hood, troubleshooting and more), the SQL Server Profiler acts as a big time safer. The Profiler is able to read and display all the traffic between the client (e.g. a custom-application, SQL Management Studio etc.) and the SQL Server. Just attach the Profiler to a running SQL Server instance.

But how to attach to a 'user instance', where you don't have a well known instance name?

First, query the master database from SQL Express as follows:



There you get the named pipes per instance. You get the heart-beat as well: alive or dead (if the Lifetime of a User Instance Connection has timed out; default is 60 minutes).

Second, connect with the pipe (just alive instances can be connected):



Third, get the traffic, sniff, profile...



You get the Profiler together with Workgroup-, Standard- and Enterprise edition.

SQL Server Management Studio / SQL Server Management Express

This solution will work for SQL Server Management Studio as well:

The Management Studio does display all attached Databases(in my case AspNetDB.MDF and People.MDF). - There is the free Version SQL Express Studio Management Server.

You must Sign In to use this message board.

FAQ  Noise Tolerance Very HighHighMediumLowVery Low  Search Messages    Layout Expand All MessagesThread ViewNo Javascript (slow)No Javascript Preview    Per page 102550

(Refresh)

-- There are no messages in this forum --