0

I have a data driven website (SQL Express auto-attached mdf file) that was built as a local site in Visual Web Developer 2010 Express, then deployed to a production server. The site is stored locally and checked into SVN for version control. Application updates are made to the local copy (using the built in development web server) then copied to the production server using the Copy Website function.

The problem I'm having is that any changes made to the database schema can be tested locally, however, there does not seem to be any way to copy these changes to the production database without overwriting the entire file including the production data.

I suppose I could connect to the production database directly and make the necessary changes by hand, however, this leaves a lot of room for error, especially in cases where a significant number of changes have been made. I do not see any way in VWD to script the changes either.

So my question is: What is the best way to handle these database schema updates in VWD 2010 Express?

Searching Stack Overflow and Google has been unfruitful, so any help is greatly appreciated!

Cheers,
JE

Jens Ehrich
  • 573
  • 1
  • 6
  • 19

1 Answers1

1

I believe you are referring to what are known as SQL Express User Instances. From this excellent article on SQL Express User Instances, a tool called SSEUtil can be used to execute commands against the local user instance. Note that I said local.

Unfortunately this isn't a direct answer to your question of how to best handle schema updates in VWD 2010. The reason for this is because there isn't any way to do this. User Instances are local (connections use named pipes) and you will need to do the updates within the production environment. Using SSEUtil is better than installing VWD 2010 on the server or writing your own code to do the updates manually.

Really once a database is released into production, update scripts are the only way things get updated. Obviously it's rare that you can do a backup of your dev database and restore to production. There are professional grade products that can simplify this process but really update scripts are where you start from.

One suggestion: I'd move away from User Instanced SQL Express to at least a regular SQL Express instance if you can.

JasonCoder
  • 1,126
  • 2
  • 12
  • 24
  • Thanks JasonCoder! I was afraid that would be the answer. I wasn't aware of SSEUtil, and I will give it a try when I have some time. I already saw the product you mentioned, but it's not in the budget this time (for anyone else interested, I did find another similar product called xSQL which has a free "lite" version). – Jens Ehrich Mar 24 '11 at 00:23
  • Continued... Yes, I am using User Instances. I will probably take your advice and move everything to a standard SQL Express instance. Is there a best practices guide to creating/using update scripts with VWD (user or standard instances) that you can recommend? – Jens Ehrich Mar 24 '11 at 00:35
  • So regardless of how you create the update scripts, make sure you test the updates against a UAT/Test version of your production database. That way when you actually deploy schema updates you're not testing the updates on your production database. Good luck! – JasonCoder Mar 24 '11 at 15:31