Post reply

DB Replication from Express Edition to Standard Edition

  • June 15, 2022 at 8:30 am

    #4049751

    Hi

    We have 40+ clients in different locations and they're using the SQL Express edition, and our head office location has a SQL Standard edition. We want to replicate clients' databases to our head office SQL instance to run some of our reports, reports are taken from our third-party tool. if someone can advise which is the exact compatible tool,  highly appreciates it.

     

    Thanks

     

    Harshana

  • June 15, 2022 at 12:28 pm

    #4049833

    SQL Replication is a built in tool. It works at the table level. Do you want the entire databases regularly, or just specific tables ?

    Can the client servers and your servers "talk" to each other ?

    • This reply was modified 2 years, 3 months ago by  homebrew01.

  • June 15, 2022 at 12:43 pm

    #4049878

    Hi

    Thank you so much for your reply.

    Actually, we want to replicate the entire database, once replicated full database, after that we need to transfer only the changes in the database daily.

     

    Can the client servers and your servers "talk" to each other ? Yes

     

    Thanks

     

  • June 15, 2022 at 1:29 pm

    #4049888

    Do you have reliable, reasonably high-bandwidth communication between the head office and all those satellite offices locations? Transactional replication could cause issues if not.

    Another option would be to restore backups from those satellite locations. Ideally, the locations are all copying the backups offsite (They are doing backups, right? What's the recovery model?) -- e.g., either a central server and or the cloud. You could copy backups to where there accessible from the head office instance & restore.

    What's the lag time on reports? Are they run on near real-time data, or a day or more lag?

    If you lag at least a day -- and they're doing at least daily diff backups -- you could probably restore diff each day.

  • June 15, 2022 at 1:36 pm

    #4049891

    Note: SQL Server Express is not supported as a replication publisher - only as a subscriber.

    So backup & restore or a homebuilt process for pulling data to the central instance or a 3rd party tool replication system would probably be necessary.

  • June 15, 2022 at 6:36 pm

    #4050050

    How big are the databases ?

  • June 29, 2022 at 5:51 pm

    #4056713

    Hi All

    Thank you so much for the given valuable support, we have planned to do a homebuilt process for pulling data to the central instance and build a small data synchronization tool.

    Thanks again

    Harshana

Viewing 7 posts - 1 through 6 (of 6 total)

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