February 25, 2010 at 6:01 pm
Hi
We have one database which publishes to another (SQL 2005 - transactional replication). The subscriber is used by a number of applications where the publisher is only ever used to store and import data from an external oracle system via SSIS. There is a lot of data published so for quick access, we create indexes on key tables on the subscriber. However these are understandably wiped every time we reinitialize the subscription from a new snapshot. Is it better practice to put the indexes on the publisher and replicate these over or better for them to be continually recreated on the subscriber (presuming disk space is not an issue).
Cheers
Steve
March 1, 2010 at 11:26 am
My understanding on this is .... create a index where you require it most. for me. it could be possible that publisher is require different index then subsriber depending upon the need.
my one cent.... my opinion.
Thanx.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
March 1, 2010 at 12:01 pm
I assume you are talking about Snapshot replication. If that is the case you will need to recreate all indexes. Maybe you can have a batch file that does it right after the Snapshot is done.
If you were using transactional replication, you would not have to snapshot all the time. Only once during initialization.
-Roy
March 1, 2010 at 4:17 pm
Thanks Vinay and Roy
This is (one way) transactional replication. We do not know why the subscribers continually get replication errors which can only be solved through reinitialization. We have the same problem across multiple sites and servers. The errors usually received include rows in the subscriber differing or being removed compared to those in the publisher which fails to apply changes for those rows. (No user has write permission to the subscriber).
Thanks
March 2, 2010 at 2:36 am
Eugoogley (2/25/2010)
HiWe have one database which publishes to another (SQL 2005 - transactional replication). The subscriber is used by a number of applications where the publisher is only ever used to store and import data from an external oracle system via SSIS. There is a lot of data published so for quick access, we create indexes on key tables on the subscriber. However these are understandably wiped every time we reinitialize the subscription from a new snapshot. Is it better practice to put the indexes on the publisher and replicate these over or better for them to be continually recreated on the subscriber (presuming disk space is not an issue).
Cheers
Steve
Change the article properties "Action if name is in use" from "Drop existing object and create new one" to "Truncate all data in the existing object"
March 2, 2010 at 6:26 am
Eugoogley (3/1/2010)
Thanks Vinay and RoyThis is (one way) transactional replication. We do not know why the subscribers continually get replication errors which can only be solved through reinitialization. We have the same problem across multiple sites and servers. The errors usually received include rows in the subscriber differing or being removed compared to those in the publisher which fails to apply changes for those rows. (No user has write permission to the subscriber).
Thanks
Replication can break due to many reasons. But the main two reasons are these two I am providing below.
1. Someone updating/removing data from subscriber either by accident or by design.
2. Network problems
To solve the first one, make sure no one has update rights on the replicated DB. The sys admin account password should be kept a secret and give the USER that is supposed to access for reports and other stuff read only rights.
Network problems : Make sure you have a very good relation with your Network admins and get it sorted out.
-Roy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply