what are the best ways to sync two databases in sql server or azure sql db?

Question

I have a question like syncing two different databases available on two different servers/instances.

lets say Source be the source server and sourcedb is the source database. destinationdb is the destination database available in the destination server(destination).

Sourcedb will be refreshed for every 15mins so data will keep on updating. *

Note: the source db is huge database, might be 3tb in size

Note: There are some 100 tables which should be in sync with Sourcedb and destinationdb

*

The process which i though

Process1:

1. take a full backup and restore in off business hours
2. take the transaction replication for those 100 tables b/w sourcedb and destinationdb

Limitations: Full backup restoring is fine but when comes to replication it made me cry. Sourcedb act as publisher and destinationdb act as subscriber and distributer. So whenever destinationdb restored with Sourcedb, the replication setting will be vanished. **

is there any other way to overcome this problem?

**

Process2:

Using Copy database command:

1. While using this setting will my destination database will be in online? because both of my databases should be in online.

Process3

Old fashioned way: Taking full backups,differential backups and restore in the destinationdb.

1. while restoring the destination db will goes offline.

Please advice.

Answer 1

You seem to want to do data replication in almost real-time. Why haven’t you considered SQL Server data replication or even better, an availability group? Our data warehouse is setup as a cluster-less availability group and the TB sized DBs are kept in sync.

Answer 2

Setup an Availability Group. If a synchronous backup causes too much overhead, look at using asynchronous.