SQL Server replication. data already available in subscriber database.Subscriber is Azure SQL DB

Question

SQL Server replication. data already available in subscriber database.Subscriber is Azure SQL DB

BoobyB

SSCertifiable

Points: 5470
More actions
November 6, 2017 at 10:24 am

#333520

Hi All,
I am setting up sql server replication where the publisher and distributor are SQL server databases and the subscriber is a Azure DB. I have successfully implemented this setup, however, the database at the subscriber has data already in it. Because of the size of the DB, we are not looking to initialize and populate the whole data in the destination DB. we just want to do the synchronization of the delta . Is there any way to accomplish this in replication?
Many Thanks,
B

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

qur7 - Monday, November 6, 2017 10:24 AM
Hi All,
I am setting up sql server replication where the publisher and distributor are SQL server databases and the subscriber is a Azure DB. I have successfully implemented this setup, however, the database at the subscriber has data already in it. Because of the size of the DB, we are not looking to initialize and populate the whole data in the destination DB. we just want to do the synchronization of the delta . Is there any way to accomplish this in replication?
Many Thanks,
B

You can sync with a backup or manually by ensuring the exact data and schema of the publication are on the subscriber but that doesn't sound like this is the case. I'm trying to understand what you are trying to accomplish with wanting to do "synchronization of the delta". Replication replicates an exact copy of whatever is in the publication.
I haven't tried this with Azure but you can do comparisons and filtered synchronizations with SSDT and that may be more of what you are looking for.
How to: Compare and Synchronize the Data of Two Databases

Sue

BoobyB SSCertifiable Points: 5470 More actions · Answer 2

For clarity, let m explained in simple terms.... In replication, if the subscriber has data already ( from back and restore or bacpac option). Can I setup replication and sync the data without deleting all the data in subscription and fill the data again through replication?

I have not been able to figure this out yet.

Kindly help.

BoobyB SSCertifiable Points: 5470 More actions · Answer 3

Sue_H - Monday, November 6, 2017 1:18 PM
qur7 - Monday, November 6, 2017 10:24 AM
Hi All,
I am setting up sql server replication where the publisher and distributor are SQL server databases and the subscriber is a Azure DB. I have successfully implemented this setup, however, the database at the subscriber has data already in it. Because of the size of the DB, we are not looking to initialize and populate the whole data in the destination DB. we just want to do the synchronization of the delta . Is there any way to accomplish this in replication?
Many Thanks,
B
You can sync with a backup or manually by ensuring the exact data and schema of the publication are on the subscriber but that doesn't sound like this is the case. I'm trying to understand what you are trying to accomplish with wanting to do "synchronization of the delta". Replication replicates an exact copy of whatever is in the publication.
I haven't tried this with Azure but you can do comparisons and filtered synchronizations with SSDT and that may be more of what you are looking for.
How to: Compare and Synchronize the Data of Two Databases
Sue

Thanks for the reply, let me explain a bit more.

I have a DB in Server A ( on-premises) and a Db in Azure SQL database. The databases are all the same minus few new SP. I have a set of 10 tables that need to be migrated and to have up-to-date data available in Azure sql database.

Because of the small maintenance window if I do the full sync from publisher to the subscriber ( subscriber table(s) being empty); this will take a long time because of the size of the tables. As mentioned, we have already move data up till last month to Azure and am trying to find out if in replication is it possible to move just the new data without the whole data copy?
if it's not possible so be it but for my own understanding, I am looking for the answer fo this question.

Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 4

qur7 - Monday, November 6, 2017 1:19 PM
For clarity, let m explained in simple terms.... In replication, if the subscriber has data already ( from back and restore or bacpac option). Can I setup replication and sync the data without deleting all the data in subscription and fill the data again through replication?
I have not been able to figure this out yet.
Kindly help.

If the schema and the data on the subscriber are exactly the same as what is in the publication. And it needs to be exact.
As long as that is in place, follow the directions in this document - push, pull or merge are all a bit different:
How to: Initialize a Subscription Manually (Replication Transact-SQL Programming)

Sue