replication from SQL Express 2017 > Ent Ed 2016.

  • 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:

    • Upgrade SQL Express to STD.
    • Create a powershell job to do the ETL.

    Are there any other, better options I haven't thought about?

    Thank you.

     

  • 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.

  • This was removed by the editor as SPAM

  • 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.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • 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).

  • 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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply