How can I sync data from a source database table without primary key to the destination database table with primary key

Question

Hi all,

If the source MS SQL SERVER 2017 database table does not have primary key but destination MS SQL SERVER 2019 database table has primary key, what is the best way or method to make both tables data in sync?

Please take into consideration that there are columns which are using data type varbinary(max) to be sync over. The data size is huge.

Thanks.

From Cindy

Answer 1

Hi @Cindy See ,

Thanks for your kindly feedback.

I feel sorry that I didn't notice the disadvantages of using materialized views with replication.

Especially when you replicate tables that have a very high number of transactions. Some applications may hold a lock where you don't expect it.

You are using data type Var binary(max) to be sync over, I think this method may not be suitable for you. SSIS package which can be used if amount of data is large, and you want to schedule it for running and do data manipulation before loading the data.

We can also try to use the Always On availability group, linked server, backup & restore, log shipping, etc.

Previously, I was bound by the primary key and have been considering the issue, if you just want to achieve data synchronization, there many methods you can try.

The following are my answer to your issue.

Transactional Replication for SQL Server Tables without Primary Keys demonstrates how to replicate the view of the source database table (without primary keys) to the remote subscriber database.

Yes, this article first shows us it will have a warning that a table cannot be published because it doesn't have a primary key column, then it tells us we can Use Materialized Views to Replicate Tables without Primary Keys.

You can see the detailed steps here and the writer creates the indexed views for the replication on the table without primary keys.

I can create index on the replicated view at the subscriber database

I don't get the exact information from the article.

Please feel free to share your issue here if you have any confused.

Note: The versions of your two servers are different. As far as I know, SQL Server is not backward compatible. If you want, you can upgrade your server to avoid unnecessary issue (this is just my suggestion).

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 2

Hi @Cindy See ,

Thanks for your information.

I found the best way for Azure SQL Data Sync. But from this official document, we can get the information:

Each table in a sync group must have a primary key. SQL Data Sync can't sync a table that doesn't have a primary key.

However, I found this article, you can Transactional Replication for SQL Server Tables without Primary Keys.

Please feel free to share your issue with us if you have any confused.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications