2

I'm currently working on a database that comes with a legacy project which uses EntityFramework (updates code based on existing database using Data Model Designer)

Currently I work on the master copy and our developers work locally using SQL Server merge-replications on their local PC.

Issue here is that we recently started doing some change work that modifies the database schema, so when we use schema comparison (visual studio SQL compare feature), there are huge number of replication sp & schema changes that basically if I do update it will corrupt the live database. So my current solution is remove the dev server replication (so that the schema goes back to what it should look like without replication changes), then do the schema compare & update, and then create a new merge replication again so our developers can continue working on the dev db.

I thought it was just one-off db schema change, but just realized it will be continuous changes at least for the next 3-6 months, so that basically make each release a big headache (if it can be called as a 'release' prep...)

My SQL & EntityFramework knowledge is limited, can anyone shed some light on this for me please?

Thanks in advance!

Lida Weng
  • 476
  • 6
  • 20
  • Additional comment: the database has over 100 tables and all tables are displayed in the edmx file in visual designer mode.... – Lida Weng May 28 '15 at 01:34
  • I think you may have better luck on Database Administration site under stack-exchange. Although your speaking about Entity Framework the DB administration is your main issue. – sleath May 28 '15 at 02:58

1 Answers1

0

Whats the observed need behind merge replication in the dev environment? I understand the need for devs to have a local copy they can mess with, run tests against etc, but I'm lost on why a full Publisher-Subscriber model is needed to synchronize DB state in a dev/test environment, and it seems to be causing you more problems than it may solve given the schema is going to be malleable for a few months.

If merge replication is not a hard requirement for the dev environment, I would suggest you replace it with an alternate method of distributing changes to the local copies. If the devs are working with a full copy of the DB anyway, I see no reason not to write a script that backs up the master copy on the dev server, then pulls that file down and restores it locally. Then, changes to that schema would be accomplished with change scripts, which can be run and tested locally before being applied to the master DB, then distributed on-demand with another run of the backup/restore script.

It's a slightly more manual process and an older way to work with DBs, but it seems far more palatable to me than breaking and re-establishing replication regularly. It'll require some collaboration to make sure devs aren't trying to make a backup at the same time or making conflicting changes to local copies that will blow up on the master copy; your devs ideally should be talking to each other anyway about this kind of thing, and you might make the script smart enough to look for a recent backup before generating another.

One more thought, don't know how feasible it is given your progress to date; it's not impossible to switch from DB-First to Code-First. The conversion is basically a hybrid process of Database First and Code First; the DB is reverse-engineered as a one-time operation to generate a model similar to DB First, but instead of EDMX files, the model is written out to source code files, and changes to those model files or to mapping conventions on the context can then be aggregated and applied to the schema as migrations in typical Code First style. Assuming you prepare the live DB for migrations as well (and have the live DB in the same state as the master Dev DB prior to the model generation), this even removes the requirement of a SQL compare and update; you just apply the migrations to the live DB, same as you would to any Dev instance. The only potential gotcha is that some migrations can be written destructively, so you have to make sure what you're about to apply isn't going to clear out all the fields in a renamed column.

KeithS
  • 70,210
  • 21
  • 112
  • 164