February 7, 2018 at 12:58 pm
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).
Options:
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
DHeath
February 8, 2018 at 12:06 am
DHeath - Wednesday, February 7, 2018 12:58 PMIf 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).Options:
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 upJust 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
Will third party tools be allowed ? 😉🙂
February 8, 2018 at 6:55 am
No third party tools are to be used in this solution. Jut trying to make it happen with the capabilities of SQL Server.
Thanks for the comment
DHeath
February 12, 2018 at 10:25 am
I think you can consider Replication/CDC/CT. BTW, if SQL 2012 is not enterprise edition, forget about CDC.
GASQL.com - Focus on Database and Cloud
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply