I have transactional replication setup between 2 sql 2000 sp3a servers
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
Ron
Any ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>
2012年3月29日星期四
BcpBatchSize problem in replication
I have transactional replication setup between 2 sql 2000 sp3a servers
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
RonAny ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
RonAny ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>
BcpBatchSize problem in replication
I have transactional replication setup between 2 sql 2000 sp3a servers
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
RonAny ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
RonAny ideas on this? We are still experiencing the problem.
Thanks,
Ron
Ron <rgellenbecker@.doralsys.com> wrote in
news:Xns95DC57BC7F324doral39312565@.207.46.248.16:
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The
> servers are running windows 2003. Everything is enterprise edition
> and the sql servers are using about 14 gig memory.
> We restore the database to the second server and then enable
> publication to that server turning of the generation of SPs but
> enabling the use of column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just
> fine. (before we started using column names).
> Up until this past weekend we did not need the column name option in
> the replication distribution and everything ran fine. Some table
> structures changed but not that big of changes but we do not have
> about 60 ident columns that we are dealing with.
> Starting this Monday we needed to use the column name option because
> we are replicating to some large tables that have identity columns on
> them. We have enabled the not for replication property on the
> columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it
> a couple of times and each time the distribution job ran fine for a
> while and then would just hang. Once it would hang there was no
> getting past that point.
> I have traced the replication process and it just stops sending data.
> I have also enabled the output of the distribution job to a file and
> it shows more data being sent than I see in the sql trace. The trace
> is not missing data because it always stops at the same spot (I tried
> it several times) and the output file always stops also but shows alot
> more data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the
> way down to 1 or 2 but the trace still shows many more commands per
> batch than 1 or 2. I have enabled the tsql batch start and end to
> trace the batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables
> with large rows I am thinking that the 10,000 commands per batch is
> too much now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
>
BCPBatchSize and Replication hanging
I have transactional replication setup between 2 sql 2000 sp3a servers
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
Ron
fiddling with the bcpbatchsize is not always a good idea. What this does is
determines how many rows are processed before they are committed. Setting
this to a high value means your tlog on your subscriber will grow and the
bcp process (performed by odbcbcp) will chug along longer before sending
messages back to the SQL Server agent. This will cause the "waiting for
response from the backend".
using a small value for bcpbatchsize will take longer, but is more chatty
and you won't get this message.
Think of it like the way your girlfriend blows your check. Give her a large
amount of money and you won't hear from her for weeks. On the other hand
give her a small amount of money, and she will be back in a few hours. Give
her to little money and she will never come back. So you have to balance the
amount of money you give her with how frequently you want to see her. Same
with bcpbatchsize - but I digress.
If your subscriber is a multiproc machine think about using UseInprocLoader
switch.
But, I'm a little confused - are you regenerating your snapshot? Are the
tables whose column names you have changed large?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ron" <rgellenbecker@.wonderboxtech.com> wrote in message
news:Xns95E29DD9C85B5WBT39312565@.207.46.248.16...
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The servers
> are running windows 2003. Everything is enterprise edition and the sql
> servers are using about 14 gig memory.
> We restore the database to the second server and then enable publication
> to that server turning of the generation of SPs but enabling the use of
> column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just fine.
> (before we started using column names).
> Up until this past weekend we did not need the column name option in the
> replication distribution and everything ran fine. Some table structures
> changed but not that big of changes but we do not have about 60 ident
> columns that we are dealing with.
> Starting this Monday we needed to use the column name option because we
> are replicating to some large tables that have identity columns on them.
> We have enabled the not for replication property on the columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it a
> couple of times and each time the distribution job ran fine for a while
> and then would just hang. Once it would hang there was no getting past
> that point.
> I have traced the replication process and it just stops sending data. I
> have also enabled the output of the distribution job to a file and it
> shows more data being sent than I see in the sql trace. The trace is
> not missing data because it always stops at the same spot (I tried it
> several times) and the output file always stops also but shows alot more
> data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the way
> down to 1 or 2 but the trace still shows many more commands per batch
> than 1 or 2. I have enabled the tsql batch start and end to trace the
> batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables with
> large rows I am thinking that the 10,000 commands per batch is too much
> now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
|||Thanks for the response.
I was changing the bcpbatchsize because of the problem I am having were
the replication just stopped. I let it sit for over 24 hrs and saw no
activity, no error message, it just said it "waiting for response from
the backend co". It never timed out. I was using the default profile
at that time. The destination server was not showing much activity and
the trace showed no command flowing between the servers.
The other problem I then saw using the trace was that even when I set
the bcpbatchsize very small and I was able to keep replication running
(after doing complete resetup) I would see more commands sent per batch
than I had specified. I set it to two and during the trace saw more
than two commands between eatch batch start and end in the trace. My
understanding of the bcpbatchsize is as you defined it. I just did not
see it actually doing that in the trace. That is why I am wondering
just what it actually does?
Even with the bcpbatchsize at 2 the replication eventually froze again
on the same message, "waiting for a response from the backend co". The
problem was that it was still sending the big batches. I wanted to make
them smaller as a test and was having no luck.
We don't use the snapshot because it takes way too long for our 70 gig
databases. We can backup and then restore the database in about an
hour.
Some of the tables are quite large (lots of columns and some the columns
get pretty big) and some of the column names get a little wordy.
We have gone back to removing the idents (which takes about an hour
right now it took about 1/2 hr 6 months ago on slower hardware) and are
no longer setting the "use column names" in the replication and it has
been running fine for over a week. I don't want to keep removing the
idents because the time it takes is getting longer quickly and the
window to setup replication is shrinking. I can get about 6 hours now
before users start to complain. Our next upgrade is coming up soon and
I will be redoing the replication setup again because several tables
will have structure changes.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:#1pAG5k$EHA.3424@.TK2MSFTNGP11.phx.gbl:
> fiddling with the bcpbatchsize is not always a good idea. What this
> does is determines how many rows are processed before they are
> committed. Setting this to a high value means your tlog on your
> subscriber will grow and the bcp process (performed by odbcbcp) will
> chug along longer before sending messages back to the SQL Server
> agent. This will cause the "waiting for response from the backend".
> using a small value for bcpbatchsize will take longer, but is more
> chatty and you won't get this message.
> Think of it like the way your girlfriend blows your check. Give her a
> large amount of money and you won't hear from her for weeks. On the
> other hand give her a small amount of money, and she will be back in a
> few hours. Give her to little money and she will never come back. So
> you have to balance the amount of money you give her with how
> frequently you want to see her. Same with bcpbatchsize - but I
> digress.
> If your subscriber is a multiproc machine think about using
> UseInprocLoader switch.
> But, I'm a little confused - are you regenerating your snapshot? Are
> the tables whose column names you have changed large?
>
|||There is a condition where the buffers are exhausted/depleted this might
account for what you are seeing. Normally you get this when the agent
starts.
There is another condition, which again you might be running into. When you
push a tonne of data over the wire it takes some time for the data to be
committed. This time is a function of how much data and bcpbatchsize and
your log size. I have on occasion waited overnight for a snapshot to be
applied.
I would advise you to look into another strategy for deploying these large
snapshots. For instance you might want to break your publication into
pieces related to DRI. Put the large tables into separate publications, i.e.
one publication for each large table and tables related to it by DRI.
Then do a nosync subscription with these tables and use another method for
deploying the snapshot. Like bcp, or copying the snapshot to a cd,
tapedrive, network share and then applying it to the subscriber from this
location.
I prefer to use bcp as I have a window into what is being applied and I can
control it in a highly granular fashion. It will require more work, but the
problem is with large snapshot you have to start off at square one with each
hiccup.
SQL 2005 will restart the snapshot application where it fails so it should
fix this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ron" <rgellenbecker@.wonderboxtech.com> wrote in message
news:Xns95E37DDC6CCD5WBT39312565@.207.46.248.16...
> Thanks for the response.
> I was changing the bcpbatchsize because of the problem I am having were
> the replication just stopped. I let it sit for over 24 hrs and saw no
> activity, no error message, it just said it "waiting for response from
> the backend co". It never timed out. I was using the default profile
> at that time. The destination server was not showing much activity and
> the trace showed no command flowing between the servers.
> The other problem I then saw using the trace was that even when I set
> the bcpbatchsize very small and I was able to keep replication running
> (after doing complete resetup) I would see more commands sent per batch
> than I had specified. I set it to two and during the trace saw more
> than two commands between eatch batch start and end in the trace. My
> understanding of the bcpbatchsize is as you defined it. I just did not
> see it actually doing that in the trace. That is why I am wondering
> just what it actually does?
> Even with the bcpbatchsize at 2 the replication eventually froze again
> on the same message, "waiting for a response from the backend co". The
> problem was that it was still sending the big batches. I wanted to make
> them smaller as a test and was having no luck.
> We don't use the snapshot because it takes way too long for our 70 gig
> databases. We can backup and then restore the database in about an
> hour.
> Some of the tables are quite large (lots of columns and some the columns
> get pretty big) and some of the column names get a little wordy.
> We have gone back to removing the idents (which takes about an hour
> right now it took about 1/2 hr 6 months ago on slower hardware) and are
> no longer setting the "use column names" in the replication and it has
> been running fine for over a week. I don't want to keep removing the
> idents because the time it takes is getting longer quickly and the
> window to setup replication is shrinking. I can get about 6 hours now
> before users start to complain. Our next upgrade is coming up soon and
> I will be redoing the replication setup again because several tables
> will have structure changes.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
> news:#1pAG5k$EHA.3424@.TK2MSFTNGP11.phx.gbl:
>
|||Thanks for the info.
My problem is not with deploying the snapshots, we have figured out how
to do the replication setup without using snapshots.
The problem is that we want to start using column names in the
replication.
I have done more testing and tracing and have narrowed down my problem.
We are using 1 way transactional replication. When the system updates
records, replication is issuing deletes and then inserts. The deletes
are the part that are taking a long time (over an hour). The inserts
takes seconds. It is the same number of rows deleted and inserted.
If I delete the rows outside of the update statement on the publisher
and then just insert the data on the publisher the process takes seconds
for the deletes and inserts to replicate. It is when I do update
statments to the publisher that the distribution takes a very long time.
The log reader gets the data in seconds in either case. The odd part is
the during the trace I can see that the distribution is just doing
deletes and then inserts. I have even taken and recreated the deletes
that replication appears to be using and they run very quickly against
the publisher and through the distribution process.
The table has the same structure on both the publisher and subscriber.
There are no idents in this table and all foreign keys have been removed
on the subscriber. The delete statement is using the clustered primary
to select the records and the deletes are being done one row at a time.
I have done the deletes one row at time outside of the update statement
and it took about 2 min to execute and about 2 min to replicate.
Why do the delete statments created by replication for the updates at
the publisher take so much longer to execute when using column names in
replication? Updating the same records without column names takes
seconds.
Thanks,
Ron
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:usZrKu#$EHA.1564@.TK2MSFTNGP09.phx.gbl:
> There is a condition where the buffers are exhausted/depleted this
> might account for what you are seeing. Normally you get this when the
> agent starts.
> There is another condition, which again you might be running into.
> When you push a tonne of data over the wire it takes some time for the
> data to be committed. This time is a function of how much data and
> bcpbatchsize and your log size. I have on occasion waited overnight
> for a snapshot to be applied.
> I would advise you to look into another strategy for deploying these
> large snapshots. For instance you might want to break your
> publication into pieces related to DRI. Put the large tables into
> separate publications, i.e. one publication for each large table and
> tables related to it by DRI.
> Then do a nosync subscription with these tables and use another method
> for deploying the snapshot. Like bcp, or copying the snapshot to a cd,
> tapedrive, network share and then applying it to the subscriber from
> this location.
> I prefer to use bcp as I have a window into what is being applied and
> I can control it in a highly granular fashion. It will require more
> work, but the problem is with large snapshot you have to start off at
> square one with each hiccup.
> SQL 2005 will restart the snapshot application where it fails so it
> should fix this.
>
sql
running on 2 4 way Dell servers with 16 gig of memory each. The servers
are running windows 2003. Everything is enterprise edition and the sql
servers are using about 14 gig memory.
We restore the database to the second server and then enable publication
to that server turning of the generation of SPs but enabling the use of
column name for the distribution.
The Distribution runs every 10 minutes and normally keeps up just fine.
(before we started using column names).
Up until this past weekend we did not need the column name option in the
replication distribution and everything ran fine. Some table structures
changed but not that big of changes but we do not have about 60 ident
columns that we are dealing with.
Starting this Monday we needed to use the column name option because we
are replicating to some large tables that have identity columns on them.
We have enabled the not for replication property on the columns.
What I am seeing is that replication now just hangs at times. The
server just goes idle and distribution job just keeps running and says
"it is waiting for response from the backend co". We have rebuilt it a
couple of times and each time the distribution job ran fine for a while
and then would just hang. Once it would hang there was no getting past
that point.
I have traced the replication process and it just stops sending data. I
have also enabled the output of the distribution job to a file and it
shows more data being sent than I see in the sql trace. The trace is
not missing data because it always stops at the same spot (I tried it
several times) and the output file always stops also but shows alot more
data being sent out.
I tried changing the bcpbatchsize for the distribution agent all the way
down to 1 or 2 but the trace still shows many more commands per batch
than 1 or 2. I have enabled the tsql batch start and end to trace the
batches allow with the statement start and end.
I am wondering what the bcpbatchsize really affects if it does not
change the number of commands per batch? Since we have some tables with
large rows I am thinking that the 10,000 commands per batch is too much
now that we are trying to include column names.
How do I really change the batch size?
Anybody else seen this problem when including column names?
Thanks,
Ron
fiddling with the bcpbatchsize is not always a good idea. What this does is
determines how many rows are processed before they are committed. Setting
this to a high value means your tlog on your subscriber will grow and the
bcp process (performed by odbcbcp) will chug along longer before sending
messages back to the SQL Server agent. This will cause the "waiting for
response from the backend".
using a small value for bcpbatchsize will take longer, but is more chatty
and you won't get this message.
Think of it like the way your girlfriend blows your check. Give her a large
amount of money and you won't hear from her for weeks. On the other hand
give her a small amount of money, and she will be back in a few hours. Give
her to little money and she will never come back. So you have to balance the
amount of money you give her with how frequently you want to see her. Same
with bcpbatchsize - but I digress.
If your subscriber is a multiproc machine think about using UseInprocLoader
switch.
But, I'm a little confused - are you regenerating your snapshot? Are the
tables whose column names you have changed large?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ron" <rgellenbecker@.wonderboxtech.com> wrote in message
news:Xns95E29DD9C85B5WBT39312565@.207.46.248.16...
> I have transactional replication setup between 2 sql 2000 sp3a servers
> running on 2 4 way Dell servers with 16 gig of memory each. The servers
> are running windows 2003. Everything is enterprise edition and the sql
> servers are using about 14 gig memory.
> We restore the database to the second server and then enable publication
> to that server turning of the generation of SPs but enabling the use of
> column name for the distribution.
> The Distribution runs every 10 minutes and normally keeps up just fine.
> (before we started using column names).
> Up until this past weekend we did not need the column name option in the
> replication distribution and everything ran fine. Some table structures
> changed but not that big of changes but we do not have about 60 ident
> columns that we are dealing with.
> Starting this Monday we needed to use the column name option because we
> are replicating to some large tables that have identity columns on them.
> We have enabled the not for replication property on the columns.
> What I am seeing is that replication now just hangs at times. The
> server just goes idle and distribution job just keeps running and says
> "it is waiting for response from the backend co". We have rebuilt it a
> couple of times and each time the distribution job ran fine for a while
> and then would just hang. Once it would hang there was no getting past
> that point.
> I have traced the replication process and it just stops sending data. I
> have also enabled the output of the distribution job to a file and it
> shows more data being sent than I see in the sql trace. The trace is
> not missing data because it always stops at the same spot (I tried it
> several times) and the output file always stops also but shows alot more
> data being sent out.
> I tried changing the bcpbatchsize for the distribution agent all the way
> down to 1 or 2 but the trace still shows many more commands per batch
> than 1 or 2. I have enabled the tsql batch start and end to trace the
> batches allow with the statement start and end.
> I am wondering what the bcpbatchsize really affects if it does not
> change the number of commands per batch? Since we have some tables with
> large rows I am thinking that the 10,000 commands per batch is too much
> now that we are trying to include column names.
> How do I really change the batch size?
> Anybody else seen this problem when including column names?
> Thanks,
> Ron
|||Thanks for the response.
I was changing the bcpbatchsize because of the problem I am having were
the replication just stopped. I let it sit for over 24 hrs and saw no
activity, no error message, it just said it "waiting for response from
the backend co". It never timed out. I was using the default profile
at that time. The destination server was not showing much activity and
the trace showed no command flowing between the servers.
The other problem I then saw using the trace was that even when I set
the bcpbatchsize very small and I was able to keep replication running
(after doing complete resetup) I would see more commands sent per batch
than I had specified. I set it to two and during the trace saw more
than two commands between eatch batch start and end in the trace. My
understanding of the bcpbatchsize is as you defined it. I just did not
see it actually doing that in the trace. That is why I am wondering
just what it actually does?
Even with the bcpbatchsize at 2 the replication eventually froze again
on the same message, "waiting for a response from the backend co". The
problem was that it was still sending the big batches. I wanted to make
them smaller as a test and was having no luck.
We don't use the snapshot because it takes way too long for our 70 gig
databases. We can backup and then restore the database in about an
hour.
Some of the tables are quite large (lots of columns and some the columns
get pretty big) and some of the column names get a little wordy.
We have gone back to removing the idents (which takes about an hour
right now it took about 1/2 hr 6 months ago on slower hardware) and are
no longer setting the "use column names" in the replication and it has
been running fine for over a week. I don't want to keep removing the
idents because the time it takes is getting longer quickly and the
window to setup replication is shrinking. I can get about 6 hours now
before users start to complain. Our next upgrade is coming up soon and
I will be redoing the replication setup again because several tables
will have structure changes.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:#1pAG5k$EHA.3424@.TK2MSFTNGP11.phx.gbl:
> fiddling with the bcpbatchsize is not always a good idea. What this
> does is determines how many rows are processed before they are
> committed. Setting this to a high value means your tlog on your
> subscriber will grow and the bcp process (performed by odbcbcp) will
> chug along longer before sending messages back to the SQL Server
> agent. This will cause the "waiting for response from the backend".
> using a small value for bcpbatchsize will take longer, but is more
> chatty and you won't get this message.
> Think of it like the way your girlfriend blows your check. Give her a
> large amount of money and you won't hear from her for weeks. On the
> other hand give her a small amount of money, and she will be back in a
> few hours. Give her to little money and she will never come back. So
> you have to balance the amount of money you give her with how
> frequently you want to see her. Same with bcpbatchsize - but I
> digress.
> If your subscriber is a multiproc machine think about using
> UseInprocLoader switch.
> But, I'm a little confused - are you regenerating your snapshot? Are
> the tables whose column names you have changed large?
>
|||There is a condition where the buffers are exhausted/depleted this might
account for what you are seeing. Normally you get this when the agent
starts.
There is another condition, which again you might be running into. When you
push a tonne of data over the wire it takes some time for the data to be
committed. This time is a function of how much data and bcpbatchsize and
your log size. I have on occasion waited overnight for a snapshot to be
applied.
I would advise you to look into another strategy for deploying these large
snapshots. For instance you might want to break your publication into
pieces related to DRI. Put the large tables into separate publications, i.e.
one publication for each large table and tables related to it by DRI.
Then do a nosync subscription with these tables and use another method for
deploying the snapshot. Like bcp, or copying the snapshot to a cd,
tapedrive, network share and then applying it to the subscriber from this
location.
I prefer to use bcp as I have a window into what is being applied and I can
control it in a highly granular fashion. It will require more work, but the
problem is with large snapshot you have to start off at square one with each
hiccup.
SQL 2005 will restart the snapshot application where it fails so it should
fix this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ron" <rgellenbecker@.wonderboxtech.com> wrote in message
news:Xns95E37DDC6CCD5WBT39312565@.207.46.248.16...
> Thanks for the response.
> I was changing the bcpbatchsize because of the problem I am having were
> the replication just stopped. I let it sit for over 24 hrs and saw no
> activity, no error message, it just said it "waiting for response from
> the backend co". It never timed out. I was using the default profile
> at that time. The destination server was not showing much activity and
> the trace showed no command flowing between the servers.
> The other problem I then saw using the trace was that even when I set
> the bcpbatchsize very small and I was able to keep replication running
> (after doing complete resetup) I would see more commands sent per batch
> than I had specified. I set it to two and during the trace saw more
> than two commands between eatch batch start and end in the trace. My
> understanding of the bcpbatchsize is as you defined it. I just did not
> see it actually doing that in the trace. That is why I am wondering
> just what it actually does?
> Even with the bcpbatchsize at 2 the replication eventually froze again
> on the same message, "waiting for a response from the backend co". The
> problem was that it was still sending the big batches. I wanted to make
> them smaller as a test and was having no luck.
> We don't use the snapshot because it takes way too long for our 70 gig
> databases. We can backup and then restore the database in about an
> hour.
> Some of the tables are quite large (lots of columns and some the columns
> get pretty big) and some of the column names get a little wordy.
> We have gone back to removing the idents (which takes about an hour
> right now it took about 1/2 hr 6 months ago on slower hardware) and are
> no longer setting the "use column names" in the replication and it has
> been running fine for over a week. I don't want to keep removing the
> idents because the time it takes is getting longer quickly and the
> window to setup replication is shrinking. I can get about 6 hours now
> before users start to complain. Our next upgrade is coming up soon and
> I will be redoing the replication setup again because several tables
> will have structure changes.
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
> news:#1pAG5k$EHA.3424@.TK2MSFTNGP11.phx.gbl:
>
|||Thanks for the info.
My problem is not with deploying the snapshots, we have figured out how
to do the replication setup without using snapshots.
The problem is that we want to start using column names in the
replication.
I have done more testing and tracing and have narrowed down my problem.
We are using 1 way transactional replication. When the system updates
records, replication is issuing deletes and then inserts. The deletes
are the part that are taking a long time (over an hour). The inserts
takes seconds. It is the same number of rows deleted and inserted.
If I delete the rows outside of the update statement on the publisher
and then just insert the data on the publisher the process takes seconds
for the deletes and inserts to replicate. It is when I do update
statments to the publisher that the distribution takes a very long time.
The log reader gets the data in seconds in either case. The odd part is
the during the trace I can see that the distribution is just doing
deletes and then inserts. I have even taken and recreated the deletes
that replication appears to be using and they run very quickly against
the publisher and through the distribution process.
The table has the same structure on both the publisher and subscriber.
There are no idents in this table and all foreign keys have been removed
on the subscriber. The delete statement is using the clustered primary
to select the records and the deletes are being done one row at a time.
I have done the deletes one row at time outside of the update statement
and it took about 2 min to execute and about 2 min to replicate.
Why do the delete statments created by replication for the updates at
the publisher take so much longer to execute when using column names in
replication? Updating the same records without column names takes
seconds.
Thanks,
Ron
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
news:usZrKu#$EHA.1564@.TK2MSFTNGP09.phx.gbl:
> There is a condition where the buffers are exhausted/depleted this
> might account for what you are seeing. Normally you get this when the
> agent starts.
> There is another condition, which again you might be running into.
> When you push a tonne of data over the wire it takes some time for the
> data to be committed. This time is a function of how much data and
> bcpbatchsize and your log size. I have on occasion waited overnight
> for a snapshot to be applied.
> I would advise you to look into another strategy for deploying these
> large snapshots. For instance you might want to break your
> publication into pieces related to DRI. Put the large tables into
> separate publications, i.e. one publication for each large table and
> tables related to it by DRI.
> Then do a nosync subscription with these tables and use another method
> for deploying the snapshot. Like bcp, or copying the snapshot to a cd,
> tapedrive, network share and then applying it to the subscriber from
> this location.
> I prefer to use bcp as I have a window into what is being applied and
> I can control it in a highly granular fashion. It will require more
> work, but the problem is with large snapshot you have to start off at
> square one with each hiccup.
> SQL 2005 will restart the snapshot application where it fails so it
> should fix this.
>
sql
2012年3月6日星期二
BCP error during DB Sync on Filtered Columns
Hi,
I'm have a wonderful time configuring replication on a set of tables and
need some help in diagnosing an error that is popping up.
Currently, I am using the transactional model with queued updating. I have
a set of tables that contain text columns, so during the publication build I
excluded these columns.
I then took a backup of the database and restore it at the subscriber.
When the DB's try to sync, I am getting the following error message on the
tables that have filtered columns:
'The process could not bulk copy into table 'tablename'
Unexpected EOF encountered in BCP data-file.
SQL is creating the file behind the scenes when the snapshot occurs, so I am
not directly creating a format file for it to use.
Any thoughts on how to fix this error?
the problem is because you have different schemas on your publisher and
subscriber.
You should recreate and redeploy your snapshot to fix this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"misterkit(at)yahoo.com" <misterkit(at)yahoo.com@.discussions.microsoft.com>
wrote in message news:847D3E50-44D7-4B37-9BE4-DDF54D379844@.microsoft.com...
> Hi,
> I'm have a wonderful time configuring replication on a set of tables and
> need some help in diagnosing an error that is popping up.
> Currently, I am using the transactional model with queued updating. I
have
> a set of tables that contain text columns, so during the publication build
I
> excluded these columns.
> I then took a backup of the database and restore it at the subscriber.
> When the DB's try to sync, I am getting the following error message on the
> tables that have filtered columns:
> 'The process could not bulk copy into table 'tablename'
> Unexpected EOF encountered in BCP data-file.
> SQL is creating the file behind the scenes when the snapshot occurs, so I
am
> not directly creating a format file for it to use.
> Any thoughts on how to fix this error?
I'm have a wonderful time configuring replication on a set of tables and
need some help in diagnosing an error that is popping up.
Currently, I am using the transactional model with queued updating. I have
a set of tables that contain text columns, so during the publication build I
excluded these columns.
I then took a backup of the database and restore it at the subscriber.
When the DB's try to sync, I am getting the following error message on the
tables that have filtered columns:
'The process could not bulk copy into table 'tablename'
Unexpected EOF encountered in BCP data-file.
SQL is creating the file behind the scenes when the snapshot occurs, so I am
not directly creating a format file for it to use.
Any thoughts on how to fix this error?
the problem is because you have different schemas on your publisher and
subscriber.
You should recreate and redeploy your snapshot to fix this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"misterkit(at)yahoo.com" <misterkit(at)yahoo.com@.discussions.microsoft.com>
wrote in message news:847D3E50-44D7-4B37-9BE4-DDF54D379844@.microsoft.com...
> Hi,
> I'm have a wonderful time configuring replication on a set of tables and
> need some help in diagnosing an error that is popping up.
> Currently, I am using the transactional model with queued updating. I
have
> a set of tables that contain text columns, so during the publication build
I
> excluded these columns.
> I then took a backup of the database and restore it at the subscriber.
> When the DB's try to sync, I am getting the following error message on the
> tables that have filtered columns:
> 'The process could not bulk copy into table 'tablename'
> Unexpected EOF encountered in BCP data-file.
> SQL is creating the file behind the scenes when the snapshot occurs, so I
am
> not directly creating a format file for it to use.
> Any thoughts on how to fix this error?
2012年2月16日星期四
Batches, generations and replication-order
I have some questions about how the merge-agents deal with batches and its
effect on the order of merge replication.
As described in
http://support.microsoft.com/default...B;EN-US;307356 the processing
order of the merge agent can result in foreign key conflicts when Parent and
child changes are split across generation batches.
In my db-system, when inserting large volumes of data, these foreign key
conflicts occur when replicating. So I have increased the
-UploadGenerationsPerBatch and -DownloadGenerationsPerBatch parameters to
their maximum of 2000. But the foreign key conflicts keep happening.
I don't understand this, because when I check the MsMerge contents table,
there are not even 2000 generations. This table contains 122 generations and
10500 datachanges.
My questions are:
- Why do I keep the FK conflicts, while the number of generations is below
2000 ?
- Is there also a maximum to the number of changes in a batch?
- What do the parameters MaxDownloadChanges, MaxUploadChanges,
UploadReadChangesPerBatch, DownloadReadChangesPerBatch,
UploadWriteChangesPerBatch, DownloadWriteChangesPerBatch mean with regard to
the parameters UploadGenerationsPerBatch, DownloadGenerationsPerBatch ?
These params seems to conflict eachother.
Unfortunately setting the foreign keys on NOT FOR REPLICATION is not a good
option for my db-system.
thanks in advance,
Marco Broenink
To that would mean the the microsoft article of
http://support.microsoft.com/default...B;EN-US;307356 is not
completely correct, because it says : 'You can increase the
-UploadGenerationsPerBatch and the -DownloadGenerationsPerBatch parameters
discussed previously to avoid splitting parent and child changes across
generation batches.'
Setting the NFR attribute of Foreign keys have as side-effect that
replication can result in a db-state in which violating Foreign Keys exist.
For example when Site A adds child X to parent Y while concurrently Site B
deletes parent Y. After replication, child X contains a reference to a
non-existing parent Y. How can I avoid such a situation on database level ?
Thanks for your help,
Marco Broenink
"Paul Ibison" wrote:
> Marco,
> it is my experience that the order of application of
> merge records can't be controlled, regardless of the size
> of the -UploadGenerationsPerBatch and -
> DownloadGenerationsPerBatch parameters, so the NFR
> attribute is still needed. Incidentally, in SQL Server
> 2005 it is entirely controllable.
> As for the conflicting parameters, I would expect that
> the generations parameters are primary, with the changes
> parameters secondary in importance. I'll check this when
> I get a moment.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Marco,
this phrase: 'to avoid splitting parent and child changes
across generation batches.' doesn't mention the ordering
of applied changes at individual row level, ie the child
record could still be processed before the parent, even
if they are in the same batch.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
The concerned article tells also about the order in which changes are
replicated.
My understanding of the article is:
- The merge agent processes the changes in two groups.
- All articles that are involved in joinfilters or Foreignkey (DRI)
relations are put in the second group. This group contains all parent child
related changes.
- INSERTs of a paticular group are processed in ascending nickname order.
- The nickname of a parent is smaller then the nickname of a child (I've
checked this in my database).
- So this all would imply that an insert of a parent is replicated before
the insert of a child.
Please tell me at what point my understanding is wrong.
Thanks, Marco
"Paul Ibison" wrote:
> Marco,
> this phrase: 'to avoid splitting parent and child changes
> across generation batches.' doesn't mention the ordering
> of applied changes at individual row level, ie the child
> record could still be processed before the parent, even
> if they are in the same batch.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Marco,
I'll look into this in more detail and will post back
asap.
Regards,
Paul
>--Original Message--
>Paul,
>The concerned article tells also about the order in
which changes are
>replicated.
>My understanding of the article is:
>- The merge agent processes the changes in two groups.
>- All articles that are involved in joinfilters or
Foreignkey (DRI)
>relations are put in the second group. This group
contains all parent child
>related changes.
>- INSERTs of a paticular group are processed in
ascending nickname order.
>- The nickname of a parent is smaller then the nickname
of a child (I've
>checked this in my database).
>- So this all would imply that an insert of a parent is
replicated before[vbcol=seagreen]
>the insert of a child.
>Please tell me at what point my understanding is wrong.
>Thanks, Marco
>"Paul Ibison" wrote:
changes[vbcol=seagreen]
ordering[vbcol=seagreen]
child[vbcol=seagreen]
even
>.
>
|||Marco,
as promised:
http://www.replicationanswers.com/Me...derArticle.htm
Rgds,
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:14eb01c52b0c$73c6af90$a601280a@.phx.gbl...[vbcol=seagreen]
> Marco,
> I'll look into this in more detail and will post back
> asap.
> Regards,
> Paul
> which changes are
> Foreignkey (DRI)
> contains all parent child
> ascending nickname order.
> of a child (I've
> replicated before
> changes
> ordering
> child
> even
|||Paul,
thanks for the link. Unfortunately I get a Page Not Found when clicking it.
Is the link temporarily disabled?
greetings, Marco
"Paul Ibison" wrote:
> Marco,
> as promised:
> http://www.replicationanswers.com/Me...derArticle.htm
> Rgds,
> Paul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:14eb01c52b0c$73c6af90$a601280a@.phx.gbl...
>
>
|||I've read the article on
http://www.replicationanswers.com/Me...derArticle.asp
Thanks!
Marco
"Marco Broenink" wrote:
[vbcol=seagreen]
> Paul,
> thanks for the link. Unfortunately I get a Page Not Found when clicking it.
> Is the link temporarily disabled?
> greetings, Marco
> "Paul Ibison" wrote:
effect on the order of merge replication.
As described in
http://support.microsoft.com/default...B;EN-US;307356 the processing
order of the merge agent can result in foreign key conflicts when Parent and
child changes are split across generation batches.
In my db-system, when inserting large volumes of data, these foreign key
conflicts occur when replicating. So I have increased the
-UploadGenerationsPerBatch and -DownloadGenerationsPerBatch parameters to
their maximum of 2000. But the foreign key conflicts keep happening.
I don't understand this, because when I check the MsMerge contents table,
there are not even 2000 generations. This table contains 122 generations and
10500 datachanges.
My questions are:
- Why do I keep the FK conflicts, while the number of generations is below
2000 ?
- Is there also a maximum to the number of changes in a batch?
- What do the parameters MaxDownloadChanges, MaxUploadChanges,
UploadReadChangesPerBatch, DownloadReadChangesPerBatch,
UploadWriteChangesPerBatch, DownloadWriteChangesPerBatch mean with regard to
the parameters UploadGenerationsPerBatch, DownloadGenerationsPerBatch ?
These params seems to conflict eachother.
Unfortunately setting the foreign keys on NOT FOR REPLICATION is not a good
option for my db-system.
thanks in advance,
Marco Broenink
To that would mean the the microsoft article of
http://support.microsoft.com/default...B;EN-US;307356 is not
completely correct, because it says : 'You can increase the
-UploadGenerationsPerBatch and the -DownloadGenerationsPerBatch parameters
discussed previously to avoid splitting parent and child changes across
generation batches.'
Setting the NFR attribute of Foreign keys have as side-effect that
replication can result in a db-state in which violating Foreign Keys exist.
For example when Site A adds child X to parent Y while concurrently Site B
deletes parent Y. After replication, child X contains a reference to a
non-existing parent Y. How can I avoid such a situation on database level ?
Thanks for your help,
Marco Broenink
"Paul Ibison" wrote:
> Marco,
> it is my experience that the order of application of
> merge records can't be controlled, regardless of the size
> of the -UploadGenerationsPerBatch and -
> DownloadGenerationsPerBatch parameters, so the NFR
> attribute is still needed. Incidentally, in SQL Server
> 2005 it is entirely controllable.
> As for the conflicting parameters, I would expect that
> the generations parameters are primary, with the changes
> parameters secondary in importance. I'll check this when
> I get a moment.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Marco,
this phrase: 'to avoid splitting parent and child changes
across generation batches.' doesn't mention the ordering
of applied changes at individual row level, ie the child
record could still be processed before the parent, even
if they are in the same batch.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
The concerned article tells also about the order in which changes are
replicated.
My understanding of the article is:
- The merge agent processes the changes in two groups.
- All articles that are involved in joinfilters or Foreignkey (DRI)
relations are put in the second group. This group contains all parent child
related changes.
- INSERTs of a paticular group are processed in ascending nickname order.
- The nickname of a parent is smaller then the nickname of a child (I've
checked this in my database).
- So this all would imply that an insert of a parent is replicated before
the insert of a child.
Please tell me at what point my understanding is wrong.
Thanks, Marco
"Paul Ibison" wrote:
> Marco,
> this phrase: 'to avoid splitting parent and child changes
> across generation batches.' doesn't mention the ordering
> of applied changes at individual row level, ie the child
> record could still be processed before the parent, even
> if they are in the same batch.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Marco,
I'll look into this in more detail and will post back
asap.
Regards,
Paul
>--Original Message--
>Paul,
>The concerned article tells also about the order in
which changes are
>replicated.
>My understanding of the article is:
>- The merge agent processes the changes in two groups.
>- All articles that are involved in joinfilters or
Foreignkey (DRI)
>relations are put in the second group. This group
contains all parent child
>related changes.
>- INSERTs of a paticular group are processed in
ascending nickname order.
>- The nickname of a parent is smaller then the nickname
of a child (I've
>checked this in my database).
>- So this all would imply that an insert of a parent is
replicated before[vbcol=seagreen]
>the insert of a child.
>Please tell me at what point my understanding is wrong.
>Thanks, Marco
>"Paul Ibison" wrote:
changes[vbcol=seagreen]
ordering[vbcol=seagreen]
child[vbcol=seagreen]
even
>.
>
|||Marco,
as promised:
http://www.replicationanswers.com/Me...derArticle.htm
Rgds,
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:14eb01c52b0c$73c6af90$a601280a@.phx.gbl...[vbcol=seagreen]
> Marco,
> I'll look into this in more detail and will post back
> asap.
> Regards,
> Paul
> which changes are
> Foreignkey (DRI)
> contains all parent child
> ascending nickname order.
> of a child (I've
> replicated before
> changes
> ordering
> child
> even
|||Paul,
thanks for the link. Unfortunately I get a Page Not Found when clicking it.
Is the link temporarily disabled?
greetings, Marco
"Paul Ibison" wrote:
> Marco,
> as promised:
> http://www.replicationanswers.com/Me...derArticle.htm
> Rgds,
> Paul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:14eb01c52b0c$73c6af90$a601280a@.phx.gbl...
>
>
|||I've read the article on
http://www.replicationanswers.com/Me...derArticle.asp
Thanks!
Marco
"Marco Broenink" wrote:
[vbcol=seagreen]
> Paul,
> thanks for the link. Unfortunately I get a Page Not Found when clicking it.
> Is the link temporarily disabled?
> greetings, Marco
> "Paul Ibison" wrote:
2012年2月9日星期四
Basic Replication trouble.
I'm trying to run a simple update script on this database which is
created from a replication agent. Several individual databases are
replicated into one consolidated. I've not worked with replication
before and I was hoping someone could tell me what this error means.
created from a replication agent. Several individual databases are
replicated into one consolidated. I've not worked with replication
before and I was hoping someone could tell me what this error means.
UPDATE
CUSTOMER
SET
[TIMESTAMP]='20060920090453'
[IVBTYPE]='M '
WHERE
[ROWID]='9f83bc89-76f8-4140-a0cc-58fa34962638'
yields:
Server: Msg 208, Level 16, State 1, Procedure
upd_0119B5A9AA624A55AF1B73F2E32A7A0C, Line 14
Invalid object name 'dbo.sysmergearticles'.
Do I have to do something to the database before trying to update it?P.S. I notice I'm missing a comma in there after the first set, missed
it while I was copying it over and didn't notice until I posted.
basic replication question
I'm interested in creating a replicated SQL server database on my laptop to develop without having to be connected to the network. I'd like to make changes and then synchronize when connected, both data and design changes. With SQL it looks like replication only applies to data and not to schema, stored procedures, dts packages, etc.
Do I understand this correctly.Do I understand this correctly.
Nope.
See
Schema changes under replication in bol.
A stored proc can be an published article.
DTS packages are stored in msdb..sysdtspackages if you keep them in sql server and you can copy them by copying the contents of this table.
It's usually easier just to copy all the stored procs too.
Have you thuoght about restoring a backup rater than replication which has quite a large learning curve and administrative overhead.
Do I understand this correctly.Do I understand this correctly.
Nope.
See
Schema changes under replication in bol.
A stored proc can be an published article.
DTS packages are stored in msdb..sysdtspackages if you keep them in sql server and you can copy them by copying the contents of this table.
It's usually easier just to copy all the stored procs too.
Have you thuoght about restoring a backup rater than replication which has quite a large learning curve and administrative overhead.
Basic Replication Question
I'm really new to this concept, so i would appreciate a little guidance.
I have a database that is stored on a remote server (shared hosting
environment). Of course the hosting company says they do daily backups, but
I don't want to trust that for myself and our clients.
Is there a way for me to set up something locally in enterprise manager that
replicates those databases to my local copy? I would like for it to sync
whenever there are changes, but it could be a batch process that runs hourly
or something.
thanks,
You could use transactional replication or log-shipping for this. Have a
look at this article for some pros and cons:
http://www.replicationanswers.com/Standby.asp
Probably the main issue will be making sure that there is connectivity and
that it is secure.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have a database that is stored on a remote server (shared hosting
environment). Of course the hosting company says they do daily backups, but
I don't want to trust that for myself and our clients.
Is there a way for me to set up something locally in enterprise manager that
replicates those databases to my local copy? I would like for it to sync
whenever there are changes, but it could be a batch process that runs hourly
or something.
thanks,
You could use transactional replication or log-shipping for this. Have a
look at this article for some pros and cons:
http://www.replicationanswers.com/Standby.asp
Probably the main issue will be making sure that there is connectivity and
that it is secure.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Basic replication problem
I must have a fundamental lack of understanding here as i can't believe
setting replication up can be so hard!
I'm setting up transactional replication, SQL 2000. I want to replicate a
whole database but for the moment i'm trying to get it working with one
table (or any combination of tables!). I've chosen push subscription.
The snapshot agent runs ok, generating a snapshot of all the required
articles. When i start the distribution agent to push out the subscription i
always get an error. I think its because of the settings in table article
properties (what to do 'If a table named xxx exists at the Subscriber').
But no matter what i do there is always a problem. If i say keep the
existing data unchanged then i get a duplicate primary key error (why does
it try to re-insert the rows again?). If i get rid of the existing data in
whatever way it doesnt work because of referential integrity rules. I tried
doing all tables at once (all the tables that have constraints between one
another) but that has no effect.
All i want to do is update the table(s) with any changes... can't be that
hard surely?
Paul
Paul Ibison wrote:
> Paul,
> leaving the table as it is can be used if you are
> replicating to a different tablename. As far as I can tell
> for your requirements, you should choose to drop the
> existing table (if you really want to leave the table
> there as it has the same data as the publisher, then try a
> nosync initialization.)
> HTH,
> Paul Ibison
Thanks Paul, I seem to have got it working now anyway... and after messing
about with this for 2 days an hour after i posted this message.. typical!
I changed the article snapshot setting to "delete data in the existing table
that matches the row filter statement" and that has done the trick. I
expected to have to type in SQL to select all the rows in the table but i
didn't have to. So what filter statement its using i have no idea! But it
works anyway!!
setting replication up can be so hard!
I'm setting up transactional replication, SQL 2000. I want to replicate a
whole database but for the moment i'm trying to get it working with one
table (or any combination of tables!). I've chosen push subscription.
The snapshot agent runs ok, generating a snapshot of all the required
articles. When i start the distribution agent to push out the subscription i
always get an error. I think its because of the settings in table article
properties (what to do 'If a table named xxx exists at the Subscriber').
But no matter what i do there is always a problem. If i say keep the
existing data unchanged then i get a duplicate primary key error (why does
it try to re-insert the rows again?). If i get rid of the existing data in
whatever way it doesnt work because of referential integrity rules. I tried
doing all tables at once (all the tables that have constraints between one
another) but that has no effect.
All i want to do is update the table(s) with any changes... can't be that
hard surely?
Paul
Paul Ibison wrote:
> Paul,
> leaving the table as it is can be used if you are
> replicating to a different tablename. As far as I can tell
> for your requirements, you should choose to drop the
> existing table (if you really want to leave the table
> there as it has the same data as the publisher, then try a
> nosync initialization.)
> HTH,
> Paul Ibison
Thanks Paul, I seem to have got it working now anyway... and after messing
about with this for 2 days an hour after i posted this message.. typical!
I changed the article snapshot setting to "delete data in the existing table
that matches the row filter statement" and that has done the trick. I
expected to have to type in SQL to select all the rows in the table but i
didn't have to. So what filter statement its using i have no idea! But it
works anyway!!
Basic questions ...
We are considering using replication for some of our needs and I need a few
basic questions answered so I can let my boss know what some of the gotcha's
are.
Using transactional replication, distributor and publisher on the same
serveer, read only subscribers:
1) What happens to the subscriber when you restore a database and/or
transaction logs on a publisher?
2) Can synchronization be forced outside of the normal scheduling? I.E. if
we sync nightly but the VP of marketing gets a bright idea and says I need
the lasted data, can we force synchronization ad hoc?
3) What happens with DDL on published objects?
4) What is the meaning of life?
5) Are there any rules of thumb about additonal load on the publisher's
database server? There is obviously some overhead for running the agents and
distibution database, but if you are replicating say 10% of the
transactions, is this analogous to adding yet an additonal 10% load on the
publisher's server?
6) We will be doing this over a VPN between separate domains. We are
expecting nightly synchronization to be required, but I was wondering if
more regular syncs would be more reliable over less than perfect internet
connections (DSL and cable are likely. Dial-up in worse cse scenarios). Any
thoughts?
Thanks,
Bob Castleman
DBA Poseur
Bob,
I will leave questions 1, 2, 3, 5 & 6 to the pros (HC & PI), but as far
as #4, I received an email a while back that said...
What is the meaning of life?
The meaning of life is to give and receive love and work at something
you feel passionate about. Or not.
I hope this helps.
Larry....
|||1) when you restore a publication db you basically break replication on the
publisher. Nothing happens to the subscriber
2) Sure. You can synchronize at any time or any time interval.
3) Making DDL changes on SQL 2000 and SQL 7 server is not allowed. You can
make some schema changes (DDL) using sp_repladdcolumn and sp_repldropcolumn.
In SQL 2005 most schema changes will be replicated.
4) life is nasty, solitary, brutish and short, however it is a hauntingly
beautiful universe. Time is all we really own. Value it carefully. Spend it
with someone special. Replicate often
5) Its typically between 5 and 10%, but it can vary wildly depending on what
you are replicating, hardware, replication type, throughput etc.
6) By replicating as frequently as possible you will lessen the load on your
publisher/distributor. The longer between sync's the greater the performance
degradation.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bob Castleman" <nomail@.here> wrote in message
news:%23oB45H3aFHA.1600@.tk2msftngp13.phx.gbl...
> We are considering using replication for some of our needs and I need a
few
> basic questions answered so I can let my boss know what some of the
gotcha's
> are.
> Using transactional replication, distributor and publisher on the same
> serveer, read only subscribers:
> 1) What happens to the subscriber when you restore a database and/or
> transaction logs on a publisher?
> 2) Can synchronization be forced outside of the normal scheduling? I.E. if
> we sync nightly but the VP of marketing gets a bright idea and says I need
> the lasted data, can we force synchronization ad hoc?
> 3) What happens with DDL on published objects?
> 4) What is the meaning of life?
> 5) Are there any rules of thumb about additonal load on the publisher's
> database server? There is obviously some overhead for running the agents
and
> distibution database, but if you are replicating say 10% of the
> transactions, is this analogous to adding yet an additonal 10% load on the
> publisher's server?
> 6) We will be doing this over a VPN between separate domains. We are
> expecting nightly synchronization to be required, but I was wondering if
> more regular syncs would be more reliable over less than perfect internet
> connections (DSL and cable are likely. Dial-up in worse cse scenarios).
Any
> thoughts?
>
> Thanks,
>
> Bob Castleman
> DBA Poseur
>
|||Thanks, guys.
My own answer to #4
The meaning of life is that we indeed are alive.
"Bob Castleman" <nomail@.here> wrote in message
news:%23oB45H3aFHA.1600@.tk2msftngp13.phx.gbl...
> We are considering using replication for some of our needs and I need a
> few basic questions answered so I can let my boss know what some of the
> gotcha's are.
> Using transactional replication, distributor and publisher on the same
> serveer, read only subscribers:
> 1) What happens to the subscriber when you restore a database and/or
> transaction logs on a publisher?
> 2) Can synchronization be forced outside of the normal scheduling? I.E. if
> we sync nightly but the VP of marketing gets a bright idea and says I need
> the lasted data, can we force synchronization ad hoc?
> 3) What happens with DDL on published objects?
> 4) What is the meaning of life?
> 5) Are there any rules of thumb about additonal load on the publisher's
> database server? There is obviously some overhead for running the agents
> and distibution database, but if you are replicating say 10% of the
> transactions, is this analogous to adding yet an additonal 10% load on the
> publisher's server?
> 6) We will be doing this over a VPN between separate domains. We are
> expecting nightly synchronization to be required, but I was wondering if
> more regular syncs would be more reliable over less than perfect internet
> connections (DSL and cable are likely. Dial-up in worse cse scenarios).
> Any thoughts?
>
> Thanks,
>
> Bob Castleman
> DBA Poseur
>
basic questions answered so I can let my boss know what some of the gotcha's
are.
Using transactional replication, distributor and publisher on the same
serveer, read only subscribers:
1) What happens to the subscriber when you restore a database and/or
transaction logs on a publisher?
2) Can synchronization be forced outside of the normal scheduling? I.E. if
we sync nightly but the VP of marketing gets a bright idea and says I need
the lasted data, can we force synchronization ad hoc?
3) What happens with DDL on published objects?
4) What is the meaning of life?
5) Are there any rules of thumb about additonal load on the publisher's
database server? There is obviously some overhead for running the agents and
distibution database, but if you are replicating say 10% of the
transactions, is this analogous to adding yet an additonal 10% load on the
publisher's server?
6) We will be doing this over a VPN between separate domains. We are
expecting nightly synchronization to be required, but I was wondering if
more regular syncs would be more reliable over less than perfect internet
connections (DSL and cable are likely. Dial-up in worse cse scenarios). Any
thoughts?
Thanks,
Bob Castleman
DBA Poseur
Bob,
I will leave questions 1, 2, 3, 5 & 6 to the pros (HC & PI), but as far
as #4, I received an email a while back that said...
What is the meaning of life?
The meaning of life is to give and receive love and work at something
you feel passionate about. Or not.
I hope this helps.
Larry....
|||1) when you restore a publication db you basically break replication on the
publisher. Nothing happens to the subscriber
2) Sure. You can synchronize at any time or any time interval.
3) Making DDL changes on SQL 2000 and SQL 7 server is not allowed. You can
make some schema changes (DDL) using sp_repladdcolumn and sp_repldropcolumn.
In SQL 2005 most schema changes will be replicated.
4) life is nasty, solitary, brutish and short, however it is a hauntingly
beautiful universe. Time is all we really own. Value it carefully. Spend it
with someone special. Replicate often
5) Its typically between 5 and 10%, but it can vary wildly depending on what
you are replicating, hardware, replication type, throughput etc.
6) By replicating as frequently as possible you will lessen the load on your
publisher/distributor. The longer between sync's the greater the performance
degradation.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bob Castleman" <nomail@.here> wrote in message
news:%23oB45H3aFHA.1600@.tk2msftngp13.phx.gbl...
> We are considering using replication for some of our needs and I need a
few
> basic questions answered so I can let my boss know what some of the
gotcha's
> are.
> Using transactional replication, distributor and publisher on the same
> serveer, read only subscribers:
> 1) What happens to the subscriber when you restore a database and/or
> transaction logs on a publisher?
> 2) Can synchronization be forced outside of the normal scheduling? I.E. if
> we sync nightly but the VP of marketing gets a bright idea and says I need
> the lasted data, can we force synchronization ad hoc?
> 3) What happens with DDL on published objects?
> 4) What is the meaning of life?
> 5) Are there any rules of thumb about additonal load on the publisher's
> database server? There is obviously some overhead for running the agents
and
> distibution database, but if you are replicating say 10% of the
> transactions, is this analogous to adding yet an additonal 10% load on the
> publisher's server?
> 6) We will be doing this over a VPN between separate domains. We are
> expecting nightly synchronization to be required, but I was wondering if
> more regular syncs would be more reliable over less than perfect internet
> connections (DSL and cable are likely. Dial-up in worse cse scenarios).
Any
> thoughts?
>
> Thanks,
>
> Bob Castleman
> DBA Poseur
>
|||Thanks, guys.
My own answer to #4
The meaning of life is that we indeed are alive.
"Bob Castleman" <nomail@.here> wrote in message
news:%23oB45H3aFHA.1600@.tk2msftngp13.phx.gbl...
> We are considering using replication for some of our needs and I need a
> few basic questions answered so I can let my boss know what some of the
> gotcha's are.
> Using transactional replication, distributor and publisher on the same
> serveer, read only subscribers:
> 1) What happens to the subscriber when you restore a database and/or
> transaction logs on a publisher?
> 2) Can synchronization be forced outside of the normal scheduling? I.E. if
> we sync nightly but the VP of marketing gets a bright idea and says I need
> the lasted data, can we force synchronization ad hoc?
> 3) What happens with DDL on published objects?
> 4) What is the meaning of life?
> 5) Are there any rules of thumb about additonal load on the publisher's
> database server? There is obviously some overhead for running the agents
> and distibution database, but if you are replicating say 10% of the
> transactions, is this analogous to adding yet an additonal 10% load on the
> publisher's server?
> 6) We will be doing this over a VPN between separate domains. We are
> expecting nightly synchronization to be required, but I was wondering if
> more regular syncs would be more reliable over less than perfect internet
> connections (DSL and cable are likely. Dial-up in worse cse scenarios).
> Any thoughts?
>
> Thanks,
>
> Bob Castleman
> DBA Poseur
>
订阅：
博文 (Atom)