So during the week I had a situation where I had to take an existing web application, that used SQL Server Express, and had to make it work with SQL Server 2005 Standard Edition. Both versions of SQL Server form part of the SQL Server SKU line of products, but their implementation in certain scenarios differ. The providers that are used to connect through your applications are the same though.
I used Visual Studio 2005 to develop the initial application, and in many ways Visual Studio is awesome but in other ways its not, and specifically with regards to SQL Server databases its not that great. Visual Studio allows you to simply drag and drop databases into your web application project, which is great for productivity, but it may cause you some headaches later on. If you have SQL Server Express running on a local machine you will note that a database created in Visual Studio will not show up in SQL Server Management Studio Express. Visual Studio creates the database for you, but it does not attach it to the SQL Server instance that you are running in SQL Server Management Studio Express. SQL Server Express uses a special mode of operation called user instancing, which means it uses accounts such as NETWORK SERVICE and ASPNET to give it rights to connect to the SQL Server Express Instance. SQL Server Express Instancing also adds a 45-75MB memory overhead.
