SQL Database Replication

Question

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.

• Tibor Karaszi: I suggest you add to your post the size of the database, as that can affect what tech is suitable.
• Patricia: Thanks for reply. DB size can be few GBs up to 1TB.
• Dan Guzman: @Patricia. it's best to add relevant information to your question rather than comments for visibility. I did that for you.

Answer 1

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.

• Patricia: Thanks Gaurav for reply.