0

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

Laiv
  • 14,990
sino
  • 105
  • 6

2 Answers2

2

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.

Eric Lavigne
  • 121
0

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.

Ewan
  • 83,178