March 3, 2021 at 10:30 am
Hey All,
Requirement is replicate data from 2 tables from SQL Express 2017 > Ent Ed 2016. Replication to be as "hot" as possible. 5000 rows per hour. Smallish tables. As I can't publish from my SQL Express ..
Options as I see:
Are there any other, better options I haven't thought about?
Thank you.
March 3, 2021 at 2:38 pm
Service Broker (not sure it works in express but I expect so) would be another option and would be near real time, but may also be overkill. Linked server and scheduled job (similar to powershell approach), SSIS (again similar to powershell approach).
Depending on what is putting the data into those rows, you may be able to update that side of things too and push to both systems at the same time. like if it is a C# application you have the source code to, when it does a write to Express, do a write to Enterprise as well. OR update the stored procedures on the express side to write to express and enterprise. The only "gotcha" with that is if you are using features ONLY available in 2017 and newer, then you will get errors when writing to the 2016 version.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 13, 2021 at 4:35 am
This was removed by the editor as SPAM
March 14, 2021 at 4:56 pm
Thanks Brian. Appreciate the response. Some good ideas I'll have a deeper look into. I especially like the pushing to both systems - sorting it out at source but if not I'll check out SSIS / linkedServer also. I get such muddled requests from this particular client but they pay me so I'll make it work.
March 15, 2021 at 5:37 am
This was removed by the editor as SPAM
March 15, 2021 at 5:39 am
This was removed by the editor as SPAM
March 15, 2021 at 6:15 pm
This was removed by the editor as SPAM
March 15, 2021 at 10:18 pm
Happy to help.
There are advantages and disadvantages to all of the approaches. linked servers can help reduce data duplication. SSIS can allow you to transform the data as you go. And having the application handle pushing data to both sources helps ensure data consistency.
But they all have disadvantages too. Having the application push the data to both systems, what do you do if one of the 2 data pushes fails? Do you retry the push? Do you undo the first data push? Something else?
Linked server, if the server becomes unreachable, the data becomes unreachable too.
SSIS needs to be scheduled, so you have no "realtime" data there.
Which solution you use depends on which solution fits your needs and which risks you can work with (or around).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 15, 2021 at 11:18 pm
do not use linked servers to push data to a SQL Server - it does so on a row by row basis.
always pull - or use a process outside SQL server (SSIS/C# with bulk insert into destination)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply