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
- take a full backup and restore in off business hours
- 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?
Using Copy database command:
- While using this setting will my destination database will be in online? because both of my databases should be in online.
Old fashioned way: Taking full backups,differential backups and restore in the destinationdb.
while restoring the destination db will goes offline.