February 3, 2017 at 1:20 am
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.
Any suggestions would be appreciated!
Thanks
February 6, 2017 at 12:27 am
Transnational replication can be used to achieve this -- near real time sync., 2nd option is to keep both new and old tables in the same database under different schema and use triggers to do updates/deletes/inserts, this might have performance impact
February 23, 2017 at 11:34 pm
Just an update: I have decided to go with Change Data Capture. And scheduling a SSIS package every X minutes to write the changes to the new DB. It won't be real-time, but near real-time. The triggers may just be too bad performance wise
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply