SQL Server migration using replication

Question

SQL Server migration using replication

brianbilow

Valued Member

Points: 64
More actions
December 3, 2024 at 7:40 pm

#4492818

I've set up replication in my SQL 2019 environment in attempt to migrate SQL databases individually from one datacenter to another. in my testing of one database several issues were found and any advice would be appreciated.
1. There several missing indexes
2. very large tables cannot be filtered on for transaction replication(post snapshot migration)
3. it takes 1-2 minutes for the transaction to publish (even if there were no database changes) but the subscriber takes the publication near instantly.
4. every publication generates a large data file that's the same size regardless of any data changes.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736718 More actions · Answer 1

Hard to know what you're doing here, as we can't see config. In general, I wouldn't use replication here.

I'd backup and restore. Once you have a full starting to restore, start to create diffs on the source. Once the full is restored, move a diff and restore that. Start to add log backups on the primary, at shorter intervals and restore those on the secondary.

At some point you should have log backups down to a short period of time. Then quiesce the system and move the final log over, restore, run recovery.

brianbilow Valued Member Points: 64 More actions · Answer 2

Thank you for your feedback.

I ended up deciding to use Basic AGs as that will ensure the database is replicated properly with nearly instantaneous fail-over and no impact on the other databases on our system. Once we migrate to the new datacenter I'll switch it to an always on cluster to match our existing architecture.

Steve Jones - SSC Editor SSC Guru Points: 736718 More actions · Answer 3

AGs are amazing. Wasn't sure you could, so didn't suggest, but that's essentially log shipping things.

Glad you have a solution

micherve Newbie Points: 9 More actions · Answer 4

Make sure you review the schema of the databases and add any missing indexes to improve the performance of the replication process. For very large tables, you may need to consider using a different approach, such as a full backup and restore, instead of relying solely on transaction replication.