question

venkatreddy avatar image
venkatreddy asked

snapshot Replication,Indexes and performence..

Dear all, We have a replication scenario in the production environment.Publisher as the production database and suscriber as the reporting database to generate management reports and distributor to place the snapshot,distributor agent.For few of the reasons we are often intializing the snapshot and placing it in snapshot folder at distributor which will be subscribed by subscriber.I have few doubts in this regard..

1) Maintaining distributor system will impacts on the replication performence? Can i create the snapshot folder at the pulisher system to place the snapshot?

2) Each time when we re-intialize the snapshot will it pulls all the records in the publisher?

3) Can we re-intialize the snapshot begining from a paricular record in the production database?

4) Should we rebuild or reorganize the indexes in the subscriber database or it will be updated with publisher as it is from publisher?

5) In both publisher and subscriber will it maintains the same fillfactor for the indexes?

Thanks in advance.

replicationperformanceindexingdatabase-snapshot
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Steinar avatar image
Steinar answered

1 If you have a large amount of data that you are replicating from a heaily loaded production system, consider placing the distribution role at a separate server from the publisher

2 Yes

3 No, not directly. But you could look into filtering the replicated records.

4 The indexes that you replicate (default is to only replicate the clustered index) are rebuilt every time you reinit

5 yes

On a sidenote, I understand it as you are running snapshot replication? If you have large volumes of data, you might consider transactional replication for performance reasonos

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.