Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

We do rapid development of web applications and we're looking for ways to separate our development and production databases (we currently develop directly on production... it's bad news).

We use ASP.NET Webforms with LINQ2SQL and Dynamic Data for CRUD. How can we do database development locally and then deploy changes to production? I've seen Entity Framework Code-first migrations, but I don't know of any equivalent for LINQ2SQL. We don't want to switch to EF as our CMS is built around LINQ2SQL.

We would also need production data to be available locally (not up to the minute, but recent enough) so we can debug with real data if problems arise.

This is the only idea I've come up with so far but it's far from ideal:

  1. Initial development is done locally then deployed to production
  2. Subsequent maintenance is then done on a local replication of the production database. Then we use some kind of 'database diff' tool to determine the changes that were made, and migrate those changes to production.

Is this an acceptable way of doing things? Is there a better way we could use?

Thanks

share|improve this question

2 Answers

up vote 0 down vote accepted

Develop your data model and procedures in SSDT Database Projects. This keeps a perfect source controlled copy of what you want the database to look alike at any moment in time. Then let the tooling generate the publishing scripts for you.

Developers should always develop on their own local copy of a database. They can check out scripts form the database project and make changes which they publish locally They can get latest on the checked in project, merge their changes, deploy locally again, test it out, and then check in their changes. Only when everything is tested out, you then publish the changes to production.

You end up treating your database schema very much like code source files.

To get production data down to your development server I would take a .bacpac or .dacpac of the production DB and them import it into your local DB. This works well because you need the schema definition along with the data since it is likely that prod is an older version than what you would have in dev

share|improve this answer
Thanks, that's very helpful. @ryan1234 suggests in (2) copying down production data and restoring locally. How does the method you suggest handle keeping up-to-date data locally? – bombastic Mar 26 at 21:59
I think the easiest way is to take a .bacpac or .dacpac of the production DB and them import it into your local DB. This works well because you need the schema definition as well since it is likely that prod is an older version than what you would have in dev. – DancesWithBamboo Mar 27 at 16:01
up vote 0 down vote

Yeah I think you basically hit the nail on the head. Those are the two things I have done.

  1. You develop locally and check in your SQL scripts to source control. Then you run the scripts for a deployment. What I've seen work well is dropping/re-creating all stored procedures (seems scary, but if you trust those scripts it's very helpful), and then having one-off scripts per deployment for schema changes and data migration.

  2. Periodically you will copy down production data and restore it locally. Obviously this sync can only happen easily right after a deployment since that's when local and production will be the same. At my current job we actually duplex writes and send a copy to the lower environments and so I suppose that's an option. You could replicate data from production somewhere else and work off of that/write a tool to bring the data into local.

From what I've seen there are no easy answers.

share|improve this answer
Thanks, good food for thought! – bombastic Mar 26 at 22:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.