If i have lets say 5 databases all around 2-5 GB and 30 tables on 5 different servers (SQL 2012 to 2017 and 2 Oracle tables) these all are to be moved to a central location on 1 server with SQL Server 2017. I also want to update certain tables but not all tables to keep the central location as current as possible with no more than a 30 minute delay between the different sources and central location. Initially i can take full backups of all 5 and restore to central location and from that point i am trying to figure out what is my best option for longevity after the initial build to keep central location current as possible. Just a note... when all said and done these will grow for approximately 3-5 years and most likely in that time the 100GB allotted to the drive will not be maxed out(but can grow it if the need be).
1. Transaction Replication on the particular tables to push to the central location when it takes place so that the update is almost immediately?
2. Use of triggers upon inserts or deletes
3. Always On..Could be used but i also know this is NOT the intended use for this feature but is also extremely easy to set up
Just looking for some ideas as to what might be my best bet to set this up and have it run with little to no intervention once setup properly.
All reply's and comments are welcome and appreciated. Thank you