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.

Improve this question
3
  • 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. 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. Commented Jun 5, 2022 at 12:14

1 Answer 1

Reset to default
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.
Improve this answer
1
  • Thanks Gaurav for reply. 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.