0

I have requirement with below for SQL database replication.

  1. I have two separate SQL database servers on same network, one will be used as primary and another one as secondary.
  2. Primary is a central source of data, and I have ETL which load data from source systems to central database on daily basis (once in a day). This daily data will need to be replicated over to secondary server and application will be connected to secondary server to use for some analysis purpose. Additional processing and reporting logic will be done at secondary server. This DB on secondary server need to be in read/write mode, however this updates does not need to replicated back to primary as it will be additional new table data.
  3. Different security settings need to apply to both servers.

What type of database replication is best suited for such scenario? Database size can be from a few GBs up to 1TB.

Thanks.

4
  • I suggest you add to your post the size of the database, as that can affect what tech is suitable. Commented Jun 5, 2022 at 9:16
  • Thanks for reply. DB size can be few GBs up to 1TB.
    – Patricia
    Commented Jun 5, 2022 at 9:31
  • @Patricia. it's best to add relevant information to your question rather than comments for visibility. I did that for you.
    – Dan Guzman
    Commented Jun 5, 2022 at 12:14
  • noted. thanks @TiborKaraszi
    – Patricia
    Commented Jun 9, 2022 at 12:12

1 Answer 1

0

Assuming you would be modifying data on Primary server only once a day, there are two methods should work for you

  1. Daily refresh (Restore Full backup from Primary after ETL process is done to Secondary server)
  2. If all the tables are not needed on Secondary server, you can configure snapshot replication and schedule it to run once a day, of course after ETL is done on Primary.
1
  • Thanks Gaurav for reply.
    – Patricia
    Commented Jun 9, 2022 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.