Many of you are working with User Instances, sometimes without even realizing it. Briefly, a User Instances is special process of SQL Express that can be started by an application at runtime and allows SQL Express to emulate some of the characteristics of embedded databases. User Instances were added to SQL Express in order to better server developers creating ClickOnce applications in Visual Studio 2005.
As long as you work completely within Visual Studio, you'll likely never even notice that anything special is going on. Visual Studio gives you the data tools you need to handle most tasks required for creating a database that sits behind an application. In some cases, you might want to move to a richer tool set, such as SQL Server Management Studio or SQL Server Management Studio Express, which is when you're going to hit a road bump. What road bump you ask?
There are a myriad of solutions to each of those problems individually, but the real solution to all of them is to learn how to connect Management Studio to your User Instance so that you can work with the database using the same instance of SQL Server that your Visual Studio application is using. As always, there are a couple things you have to be aware of when working with User Instances.
The rest of this post describes how to start a User Instance, how to determine the State and Pipe Name for a User Instances and how to connect to a running User Instances using Management Studio.
Starting a User Instance
A User Instances is automatically created and started by SQL Express when an application makes a connection to the parent instances using a special connection string. Specifically, the connection string must contain the keyword "User Instance=TRUE". Read the white paper if you're interested in the gory details, for this post all you need to know is that SQL Express knows what to do with that connection string keyword. When you add a database to your Visual Studio project, a connection string is automatically generated that contains the right information to start the User Instance when the application is run. Since it is most common that you will want to work with your database while you are working with your application, the easiest way to start your User Instances is to start the application using Start Debugging or the F5 key. If you don't want to bother opening Visual Studio and loading your application, you can download a tool called the SQL Express Utility that is capable of starting your User Instances. SSEUtil is a tool written by the Visual Studio team to help troubleshoot User Instance issues, you can read more about it in the read me file that is installed with the utility.
Determine the State and Pipe Name of a User Instance
You can get information about the User Instances on a machine that are associated with a specific instance of SQL Express by using the dynamic management view sys.dm_os_child_instances. I say "associated with a specific instance" because User Instances are unique to both the user than starts them and the instance of SQL Express where they are started. For example, if I have two instances of SQL Express on my computer named SQLEXPRESS and MIKESOTHERINSTANCE, I could actually have two separate User Instances running for me, one for each parent instance. Back to the DMV. There are a number of columns available from this View, but I'll focus on three of them and let you explore the rest yourself. The three columns of interest are owning_prinicipal_name, instance_pipe_name and heart_beat, as in:
SELECT owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances
Here is the purpose of each column:
owning_principal_name: The name of the Windows User account that owns the User Instance in the form MacineName\UserName or DomainName\UserName.
instance_pipe_name: The Named Pipe reference to the User Instance, which will be important in the next section.
heart_beat: Denotes the State of the User Instance as Alive if it's running or Dead if it is not running.
Connecting to a User Instance in Management Studio
Now to the point, go ahead and start Management Studio and connect to your parent instance of SQL Express, it's probably named SQLEXPRESS. Open a new query windows and run the query from the section above. If you've never created a User Instance before you won't see any results returned, but if you have, you'll see something like this:
As you can see, I have two User Instances on my computer, neither of which is running at the moment. I can see that the first User Instances belongs to me, while the second User Instance belongs to OtherUser, which is a local user on my computer that I use for testing. The Pipe Name is interesting because that is how I'm going to connect to the User Instance once I start it. For this example, I'd just use SSEUtil by opening a Command Prompt to the directory where I've saved the tool and running SSEUtil -L. If you've read the read me file, you know this command line will start the current users User Instances and list all the databases currently attached. The important thing is that my User Instances is now started, and if I ran the DMV again, it would now look like this:
Now that my User Instance is alive, I can connect to it in Management Studio just like any other SQL Server.
You will see a new Server show up in your Object Explorer that represents the User Instance. You can work with this User Instance just like any other SQL Server. Since this is the same User Instance that is being used by your Visual Studio applications, you won't have any conflicts between having your embedded database open in the application and opened in Management Studio.
Rather than running the DMV every time you want to connect to your User Instance, it's a real time saver to add your User Instance to the Registered Servers list once you discover it's Pipe Name. When SQL Express creates the User Instance the first time a random Pipe Name will be created, but once it's created, that same Pipe Name will be used every time the same user starts the User Instance. Once you've added your User Instance to the Registered Servers list, you can connect to it by just double clicking it, as long as it is already running. Have fun!