Database synchronisation in SQL Server

Question

I have a question with regards to synchronizing 2 DB on a daily basis, but first let me share some context.

Technical environment:

Windows Server 2012R2 Standard edition
SQL Server 2012

Functional environment: the main database is a production database. It is populated every morning using informatica with a full export from a partner data base (FYI no way to modify the latest).

When my production is populated then I create a dump and populate another database for data sciences/BI/... to play with (first the BI DB is purged).

When BI database is populated, there is no synchronization required between production & BI during the day. Both database live completely unconnected during the day.

In the current situation, the BI team is able to connect at its database by mid day (due to the volumes ~ 500 G every day) - this is not acceptable

So now we have some context, how could I make sure the BI database is ready for use as soon as the production database is populated.

Any idea is most welcome so feel free

How long does it take to take a compressed full backup and restore it on the other server? — David Browne - Microsoft
– David Browne - Microsoft, Commented Mar 8, 2020 at 16:19
What is the storage solution and does it support disk snapshots? — David Browne - Microsoft
– David Browne - Microsoft, Commented Mar 8, 2020 at 19:22

David Browne - Microsoft · Accepted Answer · 2020-03-08 19:29:12Z

1

Your options AFAIK

1) Dual-Load both databases from the ETL tool.

2) Use a disk snapshot or a VM snapshot.

3) Put the production database in Full recovery mode and use Log Shipping

answered Mar 8, 2020 at 19:29

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

lucTiber Over a year ago

Thanks David very much appreciate: I discarded the log shipping as the BI dB must not be kept in sync after being created (just so you have the full picture)

lucTiber Over a year ago

Additional question: any idea how difficult is to clone an etl process

David Browne - Microsoft Over a year ago

It's typically simple to clone an ETL process, but it can create additional load on the data source and ETL system. And with log shipping you could recreate the BI DB every day, and at the end of the load recover the database and make it writable.

Collectives™ on Stack Overflow

Database synchronisation in SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related