1

Just wondering the best way to handle the following....

I want to have a VS2010 database project to keep the schema of my database in the dev, integration test and production environments in sync.

As part of the test and production environments I have a lot of reference data that needs to be loaded into the database.

For dev and test I can just recreate the database and use Post Deployment scripts to load the data. However, I cant really do this for the production environment as obviously it will have live data on it.

So what is the best solution to do this? I dont think I can use Post Deployment scripts to load the datbase, because in the case of an insert statement I would need to wrap each one inside an IF NOT EXISTS... clause and there are 1000's of rows.

Maybe its best to use the VS2010 + MSBuild tools to keep the schema up to date and then have a seperate solution for managing the data?

Or is there a solution to this that uses purely the tools in VS2010 + MSBuild?

Remotec
  • 10,304
  • 25
  • 105
  • 147

4 Answers4

2

The best solution for live production enviroment - not to use automatic updates at all!

Use very well tested hand made update scripts in touch with your backend and frontend applications update

And there is always a good idea to have a fresh backup

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • +1 - you want it to work well, do it yourself. Do not use automatic updates. – A-K Dec 19 '11 at 22:37
  • I think this is the way forward. The current VS2010 tools don't appear to be flexible enough to handle static/reference data. – Remotec Dec 20 '11 at 11:02
0

Use a populated database to generate merge statements that can be applied in Post-Deployment. It might be a good idea to take out the DELETE clause though.

gregn
  • 1,260
  • 1
  • 14
  • 25
0

How about truncating and rebuilding the reference data table each time? If there are constraints you can remove them and add them back at the end of the post-deployment script. Would that work for you?

Or is there a reason why you can't remove production reference data?

David Atkinson
  • 5,759
  • 2
  • 28
  • 35
0

For reference data you can have a script that handles an insert, update or delete depending if the data is already in the table or not.

Check out this link for more details (this also includes a generator to help you generate your scripts).

joerage
  • 4,863
  • 4
  • 38
  • 48