How to make live changes to SQL server express

Question

I've been developing an asp.net web app using VS studio. I'm using SQL Server Express. During development I have been testing my web app on my server.

Every time that I need to update my database I would simply delete my old database (located on my server) and upload my new DB. Since I'm only testing, and my app has no users, it hasn't been a problem.

Problem:

Once my site goes live I don't know how to make changes to my DB. Obviously I wont be able to simply delete it as it will contain user data. So how do people typically update a live DB. That is, lets say my site is live and now I need to add more tables and stored procedures to my DB. How would i do this on a live site?

Answer 1

To make changes to a production database, you'd:

1. Try to schedule an outage when the least number of users will be affected, posting information so users are aware prior to
2. Use data definition language (DDL) scripts to make the changes to the database tables, and potentially data manipulation language (DML) scripts to massage existing data into what the changes need.

The scripts necessary for step 2 should have been tested in Development and Test/QA environments to ensure as few issues as possible are experienced in the Production system. Backups that allow you to restore the database to previous versions of the application are required for both the Development and Test/QA environments.

Answer 2

You either need to:

• Update your database at a time when no-one will be using the site. OR
• Ensure that your updates do not affect the operation of the site.

The second option involves giving any new non-NULLable columns sensible defaults, ensuring that all INSERT statements use column lists (e.g. INSERT INTO dbo.MyTable (col1, col2, col3) VALUES (...)) and ensuring that new stored procedure parameters have defaults. This is not an exhaustive list, but a good start.