I'm planning to set up transactional replication for a 100Gb SQL Server 2008 database. I have the distributor and publisher on the same server, and am using push subscription.
Should there be a performance impact on my publisher server when it creates the initial snapshot, and synchronises it with a subscriber? From what I've tried so far on a staging server, it seems to slow right down.
Is there a better way to create the initial snapshot without impacting my production publisher server?
Best Answer
It is best practice to move the distributor off of the publisher to another SQL Server instance to avoid these problems.
Creating the snapshot will put load on the system as it needs to BCP the data out to files. Are you trying to replicate the entire 100 Gig database, or just part of it? With 100 Gig database there will be a hell of a lot of IO being generated, and you'll flush the buffer pool while the snapshot runs, which will definitely impact production.
Your best bet is to create the snapshot during low load work time, and let the users know that you are doing maintenance which will impact performance.