Things have been quiet around here lately because I’ve been heads down on a dev project. We (that is to say, I) reached code complete today, so look for more posts shortly. But I wanted to quickly post a few things on deploying application that use SQLServer Express. It turns out that there are some significant differences between SSE and it’s older sibling with respect to Xcopy deployment.
First off, go read this article on Xcopy deployment and this article on User Instances. And while you’re out surfing, download SSEUtil – it’s a great little command line utility (i.e. no installer) that you can use to list, detach, attach, and create SSE databases.
Anyway, one of the MAJOR changes (and the one that gave my installer fits all day) is this idea of user or child instances of SSE. From the docs:
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 SQLExpress) 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 a big deal because essentially you have 2 instance of SSE running – the main one and the user specific one. And they’re different. I was totally confused by this because I didn’t realize it was happening. I created a new DB in .sqlexpress with VS but then it wouldn’t show up when I listed the databases with SSEUtil. The reason is the SSEUtil defaults to the child instance while creating a new DB with VS defaults to the main instance.
This is particularly relevant if you want to use the AttachDBFilename feature. I’m using SSE as a cache, so I wanted to put it in the <username>Application Data folder. But I couldn’t get the main instance of SSE to attach to a database in that folder tree. It drove me batty! I literally would move the DB from folder to folder – it works fine in My Docs, but not in App Data. However, it works fine from the child instance – I’m guessing that’s on purpose.
So my installer puts the DB file in the <username>App Data folder (as per Keith’s book) and changed my connection string to include the parameter “user instance=true”. Also, if you’re using AttachDBFilename, you need to include the initial catalog or database parameter, but without a value. In other words “Initial Catalog=”. Weird. But it worked like a charm.