Alleviating concerns regarding hosting SQL Server Express solutions.

  • Article

I was concerned when I was pointed towards this in the SQL Server Express docs in an earlier CTP release:

" When a process attaches to a SQL Server Express Edition database (.mdf file), the process must have administrative permissions."

I thought "Oh no" we are back to the “Please Mr Hoster can you attach this database for me and provide me with the connection string so that I can update my config file”… or even worse hosted access databases. Furthermore what about running as a least-privilege user account (LUA) on the development box... vent vent.

Well I was pleasantly surprised when I discovered ALL these issues have already been addressed!

  1. To address the Xcopy deployment issue for hosted websites... Bradley Millington has been working on a Web Hosting Upsize Tool for SQL Express that is targeting the November release timeframe. This is significant as SQL Express is essentially a development tool and we have SQL Workgroup Edition 2005 (2 CPU, 3GB RAM) which hosters are loving! SQL Workgroup Edition 2005 can be licensed in NZ for <$100 per month per processor... regardless of the number of cores.
  2. To address the LUA development issue... SQL Server Express now supports the new User Instance Feature (Read the "Xcopy Deployment for Non-administrators on a SQL Server Express Instance" of the Books Online for the September CTP). User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases... it is also worth noting that User Instance does not support SQL Server Authentication... only Windows Authentication is supported... not a problem on dev machines.

  *brilliant*

On another note:

If a hoster wanted to offer user instances, and preserve the xcopy functionality with SQL Server Express 2005 in a hosted environment, they must have a specific app pool and Windows ID for each user, and have the app developers run the app under their User ID rather than the ASP .NET User account.

This could be a good alternative for a dedicated single box single user hosted solution... bearing in mind SQL Server Express supports ~50 instances, 1gb ram and a single cpu. 

This posting is provided "AS IS" with no warranties, and confers no rights.