September 10, 2010 at 11:02 am
I've successfully replicated about 30 tables across 3 different databases successfully and I've verified that the data is being successfully replicated but I have noticed that all the indexes didn't come across except the csPL_XXXXX (clustered) index.
Is there anything I need to do to have the indexes come across or do I have to created them manually on the server being replicated to?
If I do need to recreate the indexes on the server that has the replicated tables, is there any issue with the replicated data getting overly fragmented? Specifically, will i need to reorg/rebuild them frequently because the data keeps changing?
Any insight on this would be GREATLY appreciated!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 10, 2010 at 12:30 pm
If you initialize the data using snapshot, and if the snapshot agent created the tables in the subscribers, it should have created all the index for those tables. It is strange that in your case it has not happened.
There is no problem in creating indexes in subscriber. There will always be fragmentation if the data changes a lot just like the publisher.
You will have to do reorganize and rebuild just like you do in the publisher. That is what I have seen so far from all the work I have done in replication in our environment.
-Roy
September 10, 2010 at 1:40 pm
Another related question:
After replicating from the 2005 server to the 2008 server, I'd like to replicate to yet another 2008 server. When creating the new subscriber, will I face the same table lock issues from the main 2005 database or will it pull the database table information down from the UNC path I specified the first time around?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 10, 2010 at 1:46 pm
It will create the same table lock issues. It does a schema lock when creating the table scripts. Maybe you can look at initializing with back up. I have never done it with back up but that could reduce the locks on the Publisher when the snapshot agents run.
-Roy
September 10, 2010 at 2:48 pm
Unfortunately that's what I thought 🙁 Was hoping for something cool and hoping it could load the data from the UNC folder (that contains about 8GB of data from the first snapshot creation)
Thanks man! Have an awesome weekend!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 10, 2010 at 2:57 pm
I prefer to initialize from backups with transactional replication..
Enable the 'initialize from backup' option, run a full backup, restore with norecovery onto target server, restore any transaction logs taken since the backup.
There will be no need to generate a snapshot with this method, so there will be no locks on the database (schema or otherwise).
If you haven't done it before, mess around with it in a sandbox server first and check out BOL for the exact step by step procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply