July 26, 2005 at 3:49 pm
Am I missing something here?
I'm new to replication, and have read Hilary Cotter's excellent book (or most of it, at least), but I don't understand why I am required to create non-clustered indexes unless I enable DRI.
On a side-note (or really the main note, I guess), I have a huge production database (120GB growing 2.5GB/week) that I now need to replicate sections of for our various customers. The primary hog in the DB is the activity log for all of our customer's users (their customers).
I had originally planned on using transactional replication and making a smaller publication of just the users (which changes much less often) and a second publication for the activity. Unfortunately, I have a bunch of non-clustered indexes on the activity tables which I don't want on the replicated environment, as they double the size of the table and are probably useless for the analysis being performed on the replicated server.
I could turn on DRI to disable the non-clustered indexes, but then I'm worried about DRI issues between the two publications. Is there a way to syncronize the two publications? I'm afraid I will replicate an activity for a new user which hasn't been replicated yet.
On yet another note, I'm trying to avoid the massive snapshot requirement for the initial subscription to the activity tables. I don't know, but assume that if I make a backup of the database and distribute it to our customers, the replication just grows waiting for the subscriber to restore the snapshot and connect. This could easily take them days and I don't want to push 100s of megs over our link.
I was hoping I could row filter the activity table for all dates after some start date, to make it small. Then, at my leasure, provide a data set to be manually merged into the subscriber's database with the earlier data. Update subscriptions are turned off, and there should not be any data integrity issues. Is this a reasonable approach?
Any comments or suggestions would be greatly appreciated.
Thanks,
Marc
July 27, 2005 at 4:47 am
I'm glad you like the book!
I am not sure what you mean, by default the clustered and non clustered indexes are replication. PKs are replicated as unique indexes. If you include DRI the pk fk relationships are enforced on the subscriber.
If you are complaining about the fact that pk's are turned into unique indexes it is to maintain interoptability (sp) with hetergeneous data sources.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
July 27, 2005 at 7:43 am
Hi Hilary!
Yes, I loved the book. It seemed kinda tough to get, though. I had to order it directly from your publisher.
Anyhow, using the wizard at least, there was no way to lose the non-clustered indexes without replicating DRI. I want to know why that is.
I've read on other posts that you can manually change the scheme to 0x31 to stop DRI and non-clustered replication, but since the wizard enforces non-clustered indexes if DRI was not replicated, I am reluctant to use it.
Thanks for the response. Did you have any comments/suggestions on the other parts of my post?
Thanks,
Marc
July 28, 2005 at 4:55 am
in the specify articles wizard, click on the browse button to the right of the table name. In the snapshot tab you will find the ability to deselect the replication of non-clustered indexes.
To answer your second question, I have had to manually stitch snapshots together, using bcp and other mechanisms. Its a moving target and a real chore to set up. However this is AFAIK the only way to do it with a low impact on the publisher.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
July 28, 2005 at 7:25 am
I'm sorry. Either I'm not making myself clear, or I'm unusually dense.
I want to be able to disable DRI AND not replicate non-clustered indexes. I can't do that, at least not via the Wizard. If I disable DRI, the non-clustered checkbox becomes checked and greyed-out.
Is there a reason you must have at least one of them?
Marc
August 2, 2005 at 4:14 am
Sorry, I may have misread your post. If you can do this, you would do it by trying different schema_options values using sp_addarticle. I have struggled with this a bit and have been unable to find a combination that works.
I don't have enought time to keep struggling - you might want to give it a try.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
August 2, 2005 at 8:03 am
Hilary,
Thanks for the info. I'll look into it. I was really hoping to find out why the limitation was there.
Can you think of a reason why they enforced it?
Thanks,
Marc
August 3, 2005 at 4:57 am
I'm not exactly sure. One of the reasons could be that replication needs a good query plan to execute the procs. It will fail if its not there.
It is also possible to have a custom table creation script that replication will use to create the schema you want. Use sp_addarticle with creation_script and set the schema_options accordingly,
I have an example of how to do this in chap 6.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
August 3, 2005 at 7:46 am
Thanks. You've been a tremendous help! (Here and in your book).
Now, if all my big plans work, I'll be in good shape.
Marc
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply