4

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?

Community
  • 1
  • 1
citrus
  • 81
  • 2
  • 7

4 Answers4

4

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
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.

Will A
  • 24,780
  • 5
  • 50
  • 61
  • 1
    A user reading data can also be affected - it's far less headache to make sure no one is using the system. – OMG Ponies May 14 '11 at 21:57
  • True. How feasible would it be to run all schema + data updates in a single transaction? I'm assuming, of course, that 'the correct' isolation level is being used and the code isn't littered with (NOLOCK)s. :) – Will A May 14 '11 at 21:59
  • In a single transaction? Any error, including one on the very last item of the entire operation, would trigger a `ROLLBACK` -- could be very frustrating. – OMG Ponies May 14 '11 at 22:01
  • Really? Wouldn't it (potentially) stop the batch at that point and leave the transaction half-baked, unless try/catch was being used and the transaction was rolled-back in the catch block? – Will A May 14 '11 at 22:03
  • You can't resume the process, and you have an error to deal with. – OMG Ponies May 14 '11 at 22:35
  • So long as you know where it fell over, wouldn't you be able to fix it up and continue from there? Within a transaction, should any single statement fail, isn't the effect of that statement still rolled back, independent of the parent transaction? – Will A May 14 '11 at 22:37
  • You're assuming that information is readily available, which is not likely the case. I don't know of any means to resume a transaction in the event of an error if handling is not already in place. What you're arguing is now much more complex than making separate scripts for little to no value. – OMG Ponies May 14 '11 at 22:43
  • Totally agree that this is just comlicating the matter, but still interesting. I'll shut up now and have a bit more of a think... Thanks OMG. – Will A May 14 '11 at 22:45
1

Mostly we create release scripts whereby the changes are scripted out and repeatable. So you will see things like

IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = 'myTableName' 
           AND  COLUMN_NAME = 'myColumnName')
BEGIN
     ALTER TABLE myTableName
     ADD myColumnName varchar(50)
END

You can tell which objects have changed with stuff like:

SELECT
[name],
create_date,
modify_date
FROM sys.tables
ORDER BY
modify_date DESC

Some people use RedGate software to compare the 2 different database, but there is a cost to that.

JBrooks
  • 9,901
  • 2
  • 28
  • 32
0

It depends on that kinds of changes you want to make to your live database.

In your question you only talk about adding new tables and stored procedures.
As long as you only add new stuff (tables, sprocs, or even new columns to existing tables) you don't have to do anything special, because SQL Server can do this while the database is in use and the changes don't affect your users because their version of your app doesn't even know about your newly added stuff.

On the other hand, it gets way more complicated if you change or even delete existing stuff.
There is a great chance that this will be a breaking change for your app, as it will probably stop working when tables look different than it expects or if it tries to access tables/sprocs that don't exist anymore.
(Even if you only add new stuff like I said in the beginning - you will probably want to update your app anyway, so it can actually USE the new stuff in the database)

So you probably will need to make your database changes AND deploy a new version of your app as well, both at the same time.

I'm no ASP.NET expert, but as far as I know it's not possible to update an ASP.NET app without all active users getting kicked out.
So in this case you would have to do what OMG Ponies already said: choose a time when the least possible number of users is affected, and/or inform your users early enough about the scheduled outage.

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182