Best way to copy tables without a primary key or unique constraint?

Question

Looking into synchronizing tables in a database across multiple servers using a DML method like MERGE, only to realize that some of the largest tables that take up the majority of the total size of the database do not have a primary key or unique constraint and therefore have duplicate data. As a result, doing a MERGE results in a full table scan, which explodes tempdb. For business reasons, I cannot alter the table structure or data itself.

Is there a way to copy these tables on a routine basis other than backing up and restoring the entire database?

Answer 1

Are you a high-tech or a low-tech person?

Personally I tend to go for low-tech solutions, feeling more in control and less moving parts.

If the table(s) without PK take out the majority of the db space, why not automate backup and restore? You do the backup anyhow, and you'll get those backup files rested by the restore process, as a bonus. I'd clearly define the downsides of backup/restore before eliminating this from the table.

Possibly add log backups (aka log shipping), but be aware that you must have 0 connections in the database when doing restore, limiting the usability of LS for your "reporting" needs.

Snapshot replication as suggested by J.D. (possibly with transactional replication), is of course another option. Now you have the replication agent jobs and config to manage. Might now be an issue, but just for you to keep in mind. And with replication comes limitations of what schema changes you can do.

If the destination is there solely for read-only queries, another option is to throw Always On Availability Groups on the database. You get the HA/DR aspect as a bonus, and you can do r/o queries on a replica database. The whole replica database is read-only.

Answer 2

AMtwo's point is without a primary key defined you have no reliable way to consolidate a record between a table on both servers. A primary key is the only way to guarantee row 137 is the same exact row 137 you're comparing it against between server 1 and server 2.

The closest thing you can attempt is use the HASHBYTES() function with a minimal collision hashing algorithm to hash the entire row, and then compare hashes. You can even goes as far as creating a computed column or an indexed view so you can index the hashed value and do quicker comparisons. The downside to this is with no primary key on the table means it's entirely possible two rows have exactly the same values across all columns within the same table and result in a duplicative join when comparing across servers resulting in excessive row comparisons.

The better solution from an accuracy standpoint would be to use a feature like Snapshot Replication on an appropriate schedule (e.g. hourly if that synchronization timeframe is acceptable but you can theoretically schedule it to run as soon as every second - though I wouldn't recommend this frequently). This essentially dumps the table on the target server and re-inserts all the rows from the source server, only for the tables you choose to replicate. You may want to even consider using a hybrid replication solution by implementing Transactional Replication for your tables that do have primary keys which offers near realtime synchronization.