replication problem

hi guys,
Just a question, I set up transaction replication between 2 servers.
it's working fine with data, table structures are also being replicated to
destination (I do reinitialize subscription). However, indexes and foreign
key are not being replicated. what is going on?
I checked the replication monitor, I dont' see any errors.
any hint?
thanks
kevin
By default the replication of fk's and pk's is not done. You can select to
include DRI in the articles property dialog. Right click on your
publication, select publication properties, click on the article tab, and
click on the browse button to the right of each table. In the snapshot tab
at the bottom you will find this option.
HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data is
flowing from your publisher to your subscriber and ideally your DML will be
occuring on your publisher not your subscriber. So your DRI will be
maintained on your publisher and there is no reason to include it on your
subscriber.
If you are doing DML on your subscriber as well and replication does work
with this, it is advisable to put the NFR attribute on your constraints on
your subscriber tables.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
> hi guys,
> Just a question, I set up transaction replication between 2
> servers. it's working fine with data, table structures are also being
> replicated to destination (I do reinitialize subscription). However,
> indexes and foreign key are not being replicated. what is going on?
> I checked the replication monitor, I dont' see any errors.
> any hint?
> thanks
> kevin
>
|||okay.. you're right.
I shouldn't worry about foreign key, but I do worry about indexes.
also, I added an article to subscription using sp_addarticle, but when I
force to run snapshot agent, snapshot agent said
no snapshot is available. why doesn't snapshot agent know I just added an
article?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
> By default the replication of fk's and pk's is not done. You can select to
> include DRI in the articles property dialog. Right click on your
> publication, select publication properties, click on the article tab, and
> click on the browse button to the right of each table. In the snapshot tab
> at the bottom you will find this option.
> HOWEVER!!!! Why do you want to include DRI on your subscriber. Your data
> is flowing from your publisher to your subscriber and ideally your DML
> will be occuring on your publisher not your subscriber. So your DRI will
> be maintained on your publisher and there is no reason to include it on
> your subscriber.
> If you are doing DML on your subscriber as well and replication does work
> with this, it is advisable to put the NFR attribute on your constraints on
> your subscriber tables.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23Zprj%23L1FHA.2964@.TK2MSFTNGP09.phx.gbl...
>
|||causer you have to issue a sp_refreshsubscriptions. You might have to
re-rerun the snapshot agent as well.
Keep in mind that on the subscriber the PK's are by default replaced by
unique indexes which are the functional equivalent of a pk, only it will
allow a single null - but the unique key is a clustered index.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
> okay.. you're right.
> I shouldn't worry about foreign key, but I do worry about indexes.
> also, I added an article to subscription using sp_addarticle, but when I
> force to run snapshot agent, snapshot agent said
> no snapshot is available. why doesn't snapshot agent know I just added an
> article?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Om$eJGM1FHA.3256@.TK2MSFTNGP09.phx.gbl...
>
|||sorry, Last question.
it looks like after I did sp_refreshsubscriptions, the destination table
(subscriber) was truncated first, now it's buck copying data to destination
table at subscriber.
But originally, I had 5200000 rows in destination table(subscriber), In
same table in Publisher, It has 8000000 rows. I thought it would just copy
over 2800000 rows over instead of doing everything all over again. is
there a system procedure that can do such ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
> causer you have to issue a sp_refreshsubscriptions. You might have to
> re-rerun the snapshot agent as well.
> Keep in mind that on the subscriber the PK's are by default replaced by
> unique indexes which are the functional equivalent of a pk, only it will
> allow a single null - but the unique key is a clustered index.
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23oVx3WM1FHA.916@.TK2MSFTNGP10.phx.gbl...
>
|||No there is no proc to do this. In SQL 2005 snapshots are restartable which
is probably what you are looking for.
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:u8IS%23IO1FHA.2132@.TK2MSFTNGP15.phx.gbl...
> sorry, Last question.
> it looks like after I did sp_refreshsubscriptions, the destination table
> (subscriber) was truncated first, now it's buck copying data to
> destination table at subscriber.
> But originally, I had 5200000 rows in destination table(subscriber), In
> same table in Publisher, It has 8000000 rows. I thought it would just
> copy over 2800000 rows over instead of doing everything all over again.
> is there a system procedure that can do such ?
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ubhUEiM1FHA.3756@.tk2msftngp13.phx.gbl...
>