Running XPE on multiple SQL Instances

There's a great post on the Embedded Team blog that stems from some work we've been doing in Windows Servicibility. My test lead, Randy, came up with this technique in a day - another day to clean it up and make it readable, and you have the post Lynda submitted. What is it?

Well, it's how you can run two XPE databases on the same server, like maybe an SP1 DB along with an SP2 DB, all on one machine. No more second servers to handle the SP2 upgrade.

Why did we come up with this? Simple - automation. I've been talking a bit in this blog, and to MVP's and other customers at events, that moving security work into WinSE meant we had to automate a lot of stuff. One of the first things we automated was the creation of update packages, and the WinSE Build lab is integrating that automation into their normal build processes.

The problem came when they realized they needed to XPE DB's to do the XPE update builds, one for SP1 and SP2. They were really agitated when I told them that, if there's an SP3, they'd need a third server for it as well. They needed a way to use only one server if possible.

SQL Server supports multiple instances - basically, other DB servers running as separate processes on the machine. But CMI was never written to handle multiple instances - it just connects to the default instance on a given machine. The server to connect to is just a single string - no extra setting for which instance to use. But SQL already provides a syntax for selecting a server and specific instance - it's /. If you set that as the DB to connect to in DB Manager, CMI happily connects to the specified instance.

A few things to note about the steps Lynda provides:

1. We developed these steps for the WinSE Build Lab, which has a SQL Server installation being used for other purposes. Therefore, we added two new instances, one for SP1 and one for SP2, so their default instance wouldn't be affected by XPE. In your setup, you may not need to be so diligent and be able to use just a single additional instance.
2. In our build environment, it's important we keep our repository files separated, so step 6 is necessary. You may not need to do this - repository separation is provided in the DB's themselves, and all the update files go into separate repositories, so this may not be necessary. It does duplicate a few Gb of data, so if you're tight on space on your server, you should be able to skip this without affecting your builds.
3. Step 5 has you copying databases from one instance to another - we did not test, and do not yet know, if you can install the DB directly to a given instance. If anyone tries this, let us know how it worked.

That's it - questions on this procedure should be sent on a comments to either this post or the original.