Develop for SQL Server Standard using SQL Server Express?

Question

I'm working on a web service project, and I'm coding at home, where I have SQL Express 2008 installed, but the app needs to interface with an SQL Server Standard. I've never done the transition before, and I haven't been able to find any resources on the subject - plenty of stuff about upgrading, but nothing about how to deploy.

For instance, Visual C# Express, I don't seem to be able to connect to a database without a database file - is that how Standard works as well? Do I just deploy the file with the application?

Answer 1

You can use the Express version also as a stand-alone installation. See here for example. In fact, there is also a free edition of Management Studio. You can manage your database the same way as any other edition. If you install SQL server express in this way, you can move to another version of SQL server without a hitch!

Answer 2

Unless you are doing something very very unusual or something hacky, the deployment will be very easy. Anything that I know of that you can do in the Express version works exactly the same in the full version.

All different ways of connecting to the database are available in both the Express version and full version. You don't need any database file to make a connection, unless the framework that you use requires it. You always connect to the database server through the network, never through the file system.

When you deploy the application you just change the connection string so that it points to the live server. If the login is set up the same way on that server, it works without any other changes.

Answer 3

I think you'll have to manually create the connection string as the IDE won't automatically generate ones connecting to SQL Server Standard Edition. But you can easily record two in the app - one for testing that points to the EXPRESS instance, and one for the live that points to the real one. As long as you're connecting to the same objects and interacting with them in the same way, it should be fine.

You can manually amend your connection string AFTER the IDE has generated its own to something like :

Data Source=ServerName;Initial Catalog=AppDatabase;Integrated Security=True;Persist Security Info=True;Connect Timeout=30

replacing ServerName and AppDatabase as required, and with possible authentication changes. You'll have to watch for the IDE recreating the original connection string, though, as I can't see a way to modify the connection string used in the Database Explorer and if you use the IDE to drag datasources into your app it'll keep using the original connection string.

SQL Server Developer Editions are pretty cheap, though - easily less than £50.