I spend a lot of time trying to figure out what MSSQL provide to update MSSQL schema without loosing the sync, but I find it hard to find a solution. Does any of you faced the some problem ? I found two solutions and both are not an option :

  1. move to NoSQL DB

  2. halt calls to DB until update finish.

The most simple scenarios is you have either 1 DB or 2 DBs in sync

  • 2
    "without loosing the sync" means what? You have a replicated database? What kind of replication? – joshp Jan 8 '18 at 3:11
  • Please describe the problem using an example.. What type of situation creates your databases to be out of sync? Is it only temporarily out of sync (latency problems?) or is it a conceptual issue? – Neil Jan 8 '18 at 8:59
  • "without loosing the sync" mean while you switch the app to DB 2 while you are updating the schema of DB 1 , the data stored at that time will be lost from DB 1 , and you need to somehow resync , but the problem now the two dbs will have different schema and you will have to do it case by case , but I wounder if there is a global approach for that – sino Jan 8 '18 at 15:58
  • @Neil : I ll come up with diagram to make it more easy – sino Jan 8 '18 at 15:59
  • The way problems like this are solved is that the problem is avoided entirely. Afterall you write to the database to ensure information is persisted, not to use as a cache. Simply ensure that before sync occurs, you write this partial state to one database so that should something go wrong, you can recover. – Neil Jan 9 '18 at 7:42

Despite the restriction, I will recommend this. :-)

"halt calls to DB until update finish"

The schema change will likely finish in seconds (test this on a test copy of the database!), so users will not even notice the pause. The pausing part can be implemented on a proxy so that there's no need to make application changes. This allows your application code to be changed at the same time so that it never needs to support both schema versions at once.

Let's assume that all URLs in your application can be classified as either fast (less than 10 seconds) or slow (10-60 seconds).

1) Proxy starts pausing all slow requests and waiting 10-60 seconds for already in progress slow requests to finish.

2) Proxy starts pausing all requests and waiting 0-10 seconds for already in progress fast requests to finish.

3) Run the schema migration and switch to new application code that uses the new version of the schema. With no load on the database, the schema migration should be very fast.

4) Proxy lets all paused requests through.

Users who were waiting for slow requests are not surprised that their typically 60 second requests took 130 seconds instead. Users who were waiting for fast requests might notice that their 10 second request took 30 seconds instead. Anyone who notices the slight lag will probably forget a minute later.

| improve this answer | |

You can update the DB Schema without switching databases, turning off replication or losing data if you structure your queries correctly and use transactions.

Instead of a simple drop table, create table you must plan out the change, moving the data to a temporary table and transferring it back all within the same transaction.

There are several tools which will generate the migration statements for you. Visual Studio Enterprise, Entity Framework Migrations and various third party software.

| improve this answer | |
  • So in that case the code has to be compatible with both version of DB (old and new ) to keep running during the schema migration , is that correct ? Also I assume transaction should not lock the tables to let current activity go from app able to write on DB , otherwise there will be downtime to customers – sino Jan 10 '18 at 20:09
  • Yes. Although you can use a service layer and/or sprocs to allow this. The normal process is, add a v2 service that works with old and new. point code at v2 service, upgrade db – Ewan Jan 10 '18 at 20:14
  • is that what you mean by "losing sync" – Ewan Jan 10 '18 at 20:16
  • You need to test the migration and time it. But even with a large DB it can be done in less than a second – Ewan Jan 10 '18 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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