1

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?

cxfx
  • 135
  • 1
  • 6

1 Answers1

2

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • Thanks - yes, I'm replicating the entire 100 Gb. I've been reading on BOL about 'Initializing a Transactional Subscription Without a Snapshot' by using a backup instead of a snapshot, so maybe creating a backup would have less impact on prod than creating a snapshot? – cxfx Feb 03 '11 at 23:44
  • The full backup will have the same IO impact as all the pages have to be read. However the upside to the backup is that the backup doesn't cause the buffer pool to flush. If an initialize via backup is an option for you then I would take it. The basic process is create the publication, take the backup, setup the subscriber. Books OnLine has this pretty well documented. – mrdenny Feb 04 '11 at 19:44