Sql Server 2008 User Instances

Sql Server Express edition packs something really cool – User Instances.

Let me elaborate. I have following requirements -

1. A windows application
2. A single user database
3. Click-once deployment

I found out I can create a solution with Sql server User Instance and  local database really easily. This is one way to do this -

Add Database to Visual Studio

Create a windows forms application solution in Visual Studio.  Next create the database using SSMS. Detach the database and copy/add the mdf file to visual studio solution. Alternatively, you can add a local database in Visual Studio and edit the database model from VS.

Update Project

Update project properties by right clicking on project and selecting properties. Verify that in publish tab\Application files your mdf file is setup as data file and required. Also make sure, mdf file Build Action is set to Content and Copy.

Since we will use Sql Server Express 2008, you will need to install Visual Studio 2008 Express edition.  This is because Visual Studio 2008 Standard came up before Sql Server 2008 and does not have the necessary Sql Server 2008 bootstrapper. When you install VS Express, make sure you select Sql Server Express to install as well.

Add Sql Server 2008 Express edition to your list of pre-requisites under Publish tab.

You may also add a update url if you choose.

Setup Connection

Create a database connection string in your config file as following -

<add name="MyConnectionString"  connectionString="Server=.\SQLExpress;AttachDBFileName=|DataDirectory|\mydatabase.mdf;Database=mydatabase;Trusted_Connection=Yes;User Instance=True "
      providerName="System.Data.SqlClient"/>

During click-once deployment, Installer will copy the mdf file to the data folder in your profile directory (directory address varies among flavors of windows – in Windows 7 it is here: C:\Users\<username>\AppData\Local\Apps\2.0\Data). During update, the mdf file will be left alone. You will need code to update your database.

Publish

Publishing itself is straightforward. Choose your deployment option (Web/CDROM etc) and hit deploy. When user runs the installer, Sql Server Express will auto-download and install. The database file will attach itself and stay attached. No hassle on your part!

Few things to remember:

You won’t be able to view your database if you connect to the Express instance. this is because, when you specify User Instance = true in your connection string, Sql Server runs a special instance of Sql Server that allows non-admin users to create/attach database. For troubleshooting use following Sql command to find your instance –

SELECT owning_principal_name, instance_pipe_name, heart_beat FROM sys.dm_os_child_instances

You can use the value for instance_pipe_name as your server name to connect to this instance. this instance will shut down after 60 minutes of inactivity.

Also, if you want to have the option to allow user to be able to use either Standard Sql Server or Express, you can modify the connection string at runtime. Just remove the “User Instance”  key.

 

 

Virtual PC Annoyances

I use Virtual PC exclusively for development as well as testing. There has been many times I have run into issues that were not show stoppers but the fix were not obvious. In this Blog I will add the problems and solutions to do with VPC.

• Problem: No network connection when using Virtual PC.
• Resolution: Check following items

1. Host has network connection
2. In VPC settings, correct nic is selected
3. Restart the VPC
4. Remove nic, restart, add nic
5. Shutdown VPC. Delete Options.xml file from %appdata%\Microsoft\Virtual PC. Start VPC

Managing Application Settings with version changes

One can easily use ApplicationSettingsBase class to manage application settings on user or application level. When used with user scoped settings, configuration values will be lost with version upgrades. We can use following code at application launch to update configuration values from old configuration file.

String strVersion = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();

if (Properties.Settings.Default.CurrentVersion != strVersion) {
Properties.Settings.Default.Upgrade();
Properties.Settings.Default.CurrentVersion = strVersion;
}

Use self signed certificate for self hosted WCF service

Recently I was looking to see how I could configure a WCF service to use SSL. I came across this article (SSL with Self-hosted WCF Service) that states the steps nicely.

In case this article is no longer available; I decided to record the steps -

There are few steps involved in this.

1. One must be a Certificate Authority to issue certificates. For self signed certificates you can make yourself a CA by issueing following command –
   
   

   makecert -sv SignRoot.pvk -cy authority -r signroot.cer -a sha1 -n "CN=Dev Certification Authority" -ss my -sr localmachine
   

   Here you become a CA called "Dev Certification Authority" and set your local machine as CA server.
   

2. Create a certificate for the server that will host your service (such as WCF, web service etc). Issue following command for this –
   

   makecert -iv SignRoot.pvk -ic signroot.cer -cy end -pe -n
   CN="localhost" -eku 1.3.6.1.5.5.7.3.1 -ss my -sr
   localmachine -sky exchange -sp
   "Microsoft RSA SChannel Cryptographic Provider" -sy 12
   

3. You will use this certificate to setup SSL . Open the certificate you created in step 2. It is in Certificate MMC\Personals folder. Now go to details tab. You will see thumbprint all the way down. Copy it somewhere and remove all spaces.
   
   
4. Next issue the httpcfg command to connect it to your desired port(8012 in this case) –
   

   httpcfg set ssl -i 0.0.0.0:8012 -h copiedthumbprintfromstep2
   

5. There are additional httpcfg command that you can use to query and delete SSL ports as well -
   
• httpcfg delete ssl -i 0.0.0.0:8888
  
• httpcfg query ssl
  


6. On target machine install the root certificate by opening certificates MMC and do Import. Make sure you pick Physical store and pick Computer as the store. Otherwise, it will not appear in certificates.