We are currently running SQL 2014 with a 3-node AlwaysOn cluster. The main transactional database is going through an overall and
all systems connecting to it, is being re-designed. Once the new DB and systems are ready, it will go live at 1 of our clients as
a pilot phase.
But during this time, all changes made the current (old) DB must sync to the new DB AND visa versa. And this should happen real-time.
The structures of the old and new DB will be different - name changes, column changes, table changes, etc.
I was wondering what would be the best way to do this?
The current DB is about 850 GB and is fairly busy, with 1000's of users connected at any time and a big volume of
transactions (reads, write, proc executions...) running all the time.
So whatever means of synch I use, should not have a negative effect on the user experience.
I would guess that the code for the synchronisation could turn out to be as complex as the rebuild of the system itself
I'll be interested in other opinions. We've done this when we needed to keep old-and-new-tables active (its never been a good idea!). Usually we just rename the table to MyTable_V2 and create a VIEW using the original table name to provide backward compatibility, but on occasions when we haven't been able to do that we've gone with two tables. That experience puts me off being able to enter data in two places and have them kept in SYNC.
We have a trigger on each table, which updates the other, doing "whatever is necessary" for the columns that are not included in the update [of the old table]. The most annoying problem we have is when IDENTITY values get out of step; we probably ought to solve that using SET IDENTITY_INSERT but at the time I thought both tables would allocate IDs "simultaneously" ... of course sooner or later there was a rollback on one table and then all hell broke lose ...
So my thought is to have triggers on all tables simulating the inserts/updates/deletes on the other DB and doing whatever column/data translations/manipulations are required.
Maybe you could get away with dropping the table on the old DB and replacing it with a VIEW which queries the New Table. You can put an INSTEAD OF trigger on a view, so if an INSERT to the VIEW fails (e.g. SQL considers that the view is too "complex" to allow direct update), or needs column/data manipulation, you could take care of it there).
For all the "not yet changed" tables you could replace the tables with SYNONYMS that point to the New Database. Maybe that would work ...
In typing this I'm starting to think that having only one (new) database, and "pointing" the old database at the new, for backwards compatibility period, might be an answer.
