I was looking at setting up multiple SQL Server 2005 Standard edition
servers using Peer-to-Peer Replication with the goal being able to
reindex one server at a time so as not to have any downtime (via locked
tables). Would this work?
Also, I know log-shipping to a standby server only requires 1 CPU
license which is attractive as CPU licenses are pricey. Still, if I
use P2P only for this reindex purpose (including three nodes for
voting) with only one server being used by customers/users, is a 1 CPU
license good enough?
Thanks!
MichaelI can't seem to find an answer anywhere for these questions:
Can I run ALTER INDEX REBUILD seperately between a live SQL 2005 server
and the hot-standby server? This way, if the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Is it allowed to have a single CPU license for two SQL 2005 server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
The goal is to have no downtime for executing ALTER INDEX REBUILD.
Thank you,
Michael
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
p2p replication error too many arguments specified
Currently, we have peer-to-peer replication on two servers (server1 and
server2).
I added a few columns using sp_repladdcolumn stored procedure. Rows inserted
at Server1
are replicated to Server2 without problems, and rows inserted Server2 can
not be replicated to Server1
because of the error below. Any help is appreciated. thanks, D
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x0000008600000EE7000400000000, Command ID: 1)
Error messages:
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Paul,
First time at this. Not sure how to quiesce the system. Does
this mean change db to single user?
I've used sp_repladdcolumn to add two columns to the publisher's article.
Both tables at subscriber and publisher do reflect the new columns.
However, stored procedures at the publisher do not receive the changes
while stored procedures at subscriber do.
When a new record is inserted into the table at publisher, the
new record does get distributed. When a new record is inserted
at the subscriber, the record got an error when being distributed.
Thanks, D
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OwyqlhJwHHA.4612@.TK2MSFTNGP04.phx.gbl...
> 1.. Quiesce the system.
> 2.. Execute the data definition language (DDL) statements to modify the
> schema of published tables. For more information about supported schema
> changes, see Making Schema Changes on Publication Databases.
> 3.. Before resuming activity on published tables, quiesce the system
> again. This ensures that schema changes have been received by all nodes
> before any new data changes are replicated
> The general advice is to do things as above - is that how you made the
> schema change?
> I'm also interested in what has actually happened - do the tables have an
> equal number of columns now? Can you also compare the text of the 2
> sp_MSins_dboCustomers procs (pub and subs) and see if they are different.
> If so, can you tell us which one is correct.
> Cheers,
> Paul Ibison
>
server2).
I added a few columns using sp_repladdcolumn stored procedure. Rows inserted
at Server1
are replicated to Server2 without problems, and rows inserted Server2 can
not be replicated to Server1
because of the error below. Any help is appreciated. thanks, D
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x0000008600000EE7000400000000, Command ID: 1)
Error messages:
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Procedure or function sp_MSins_dboCustomers0823818373 has too many arguments
specified. (Source: MSSQLServer, Error number: 8144)
Get help: http://help/8144
Paul,
First time at this. Not sure how to quiesce the system. Does
this mean change db to single user?
I've used sp_repladdcolumn to add two columns to the publisher's article.
Both tables at subscriber and publisher do reflect the new columns.
However, stored procedures at the publisher do not receive the changes
while stored procedures at subscriber do.
When a new record is inserted into the table at publisher, the
new record does get distributed. When a new record is inserted
at the subscriber, the record got an error when being distributed.
Thanks, D
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OwyqlhJwHHA.4612@.TK2MSFTNGP04.phx.gbl...
> 1.. Quiesce the system.
> 2.. Execute the data definition language (DDL) statements to modify the
> schema of published tables. For more information about supported schema
> changes, see Making Schema Changes on Publication Databases.
> 3.. Before resuming activity on published tables, quiesce the system
> again. This ensures that schema changes have been received by all nodes
> before any new data changes are replicated
> The general advice is to do things as above - is that how you made the
> schema change?
> I'm also interested in what has actually happened - do the tables have an
> equal number of columns now? Can you also compare the text of the 2
> sp_MSins_dboCustomers procs (pub and subs) and see if they are different.
> If so, can you tell us which one is correct.
> Cheers,
> Paul Ibison
>
P2P Replication Conflicts
Can someone who has had direct experience with this tell me exactly what happens when a conflict (updating same record on two nodes at the same time) occurs in a P2P replication topology? Does the Dist. Agent throw an error? More importantly does the replication set continue to replicate the articles after any error occurs?
Thanks,
Derek
the distribution agent(s) fail. They won't start again until you manually fix the error. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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> the distribution agent(s) fail. They won't start again until you manually
> fix the error. >
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. >
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions. >
> 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 > > >
>
> news:e01a768f-ced3-43c1-854f-949ddbd00a29@.discussions.microsoft.com...
> Can someone who has had direct experience with this tell me exactly what
> happens when a conflict (updating same record on two nodes at the same
> time) occurs in a P2P replication topology? Does the Dist. Agent throw an
> error? More importantly does the replication set continue to replicate the
> articles after any error occurs?
> Thanks,
> Derek
>
P2P Replication - Issue in Manual Identity Management
I am using P2P replication between two server. I face a big issue in manual
identity management. I did as follows
Step 1: Built MyDB1 with data in server1.
Step 2:Set up Server1 MyDB1 as Publisher (Followed msdn white paper
p2ptranrepl.doc)
Step 3:Took backup of MyDB1 in Server1 and restored in server2 as MyDB1
(again as per steps in msdn white paper)
Step 4:Identity range for tablen in server1 is say 1-1000 (have put the
necessary check constraint on identity column. Also made Identity column NOT
FOR replication. Following Hillary Cotter's writeup in
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/)
I reseeded the identity value in Server2 MyDB1 to have range as 1001 to
2000. Added necessary check constraints
Step 5: As per the msdn whitepaper setup using "Configure peer to peer
topology' wizard.
Step 6: Now say the identity range of server 1 is consumed and I want to
reseed the value to say 2001-3000. Server2 has max as only 1010 and hence
left as is.
I reseed server1 table1 and also change the check constraints accordiingly
from 2001-3000.
**********Now the issue is this change in constraint gets automatically
propogated to the subscriber. But i do not want the subscribers to move to
the new range. They should be in 1001-2000 only. I have verifed that 'Copy
Check Constraints' property is set to FALSE. But still the constraints are
getting propogated to subscribers****************************
This becomes a blocking issue for my manual identity management.
Kindly help to resolve the same.
Thank you,
Lalitha
remove the check constraints. They aren't necessary. I think you should be
using larger ranges as well.
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
"Lalitha" <Lalitha@.discussions.microsoft.com> wrote in message
news:AF09E8A4-90F8-4D4E-9429-94E2FAC3726F@.microsoft.com...
>I am using P2P replication between two server. I face a big issue in manual
> identity management. I did as follows
> Step 1: Built MyDB1 with data in server1.
> Step 2:Set up Server1 MyDB1 as Publisher (Followed msdn white paper
> p2ptranrepl.doc)
> Step 3:Took backup of MyDB1 in Server1 and restored in server2 as MyDB1
> (again as per steps in msdn white paper)
> Step 4:Identity range for tablen in server1 is say 1-1000 (have put the
> necessary check constraint on identity column. Also made Identity column
> NOT
> FOR replication. Following Hillary Cotter's writeup in
> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/)
> I reseeded the identity value in Server2 MyDB1 to have range as 1001 to
> 2000. Added necessary check constraints
> Step 5: As per the msdn whitepaper setup using "Configure peer to peer
> topology' wizard.
> Step 6: Now say the identity range of server 1 is consumed and I want to
> reseed the value to say 2001-3000. Server2 has max as only 1010 and
> hence
> left as is.
> I reseed server1 table1 and also change the check constraints accordiingly
> from 2001-3000.
> **********Now the issue is this change in constraint gets automatically
> propogated to the subscriber. But i do not want the subscribers to move to
> the new range. They should be in 1001-2000 only. I have verifed that 'Copy
> Check Constraints' property is set to FALSE. But still the constraints are
> getting propogated to subscribers****************************
> This becomes a blocking issue for my manual identity management.
> Kindly help to resolve the same.
> Thank you,
> Lalitha
>
|||Hi,
Thanks for the response.
Though I use large ranges, having the check constraints is for making the
solution fool proof. This validation will ensure that no data gets inserted
other than my configured range.
When 'Copy Check Constraints' is set to FALSE, why is P2P behaviour different?
1) Is this a bug in P2P which will be fixed in future service packs?
2) Is this a limitation of P2P?
If this is the limitation of P2P then I intend to go for merge.
Please clarify.
Thanks for your inputs...
Lalitha
"Hilary Cotter" wrote:
> remove the check constraints. They aren't necessary. I think you should be
> using larger ranges as well.
> --
> 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
>
> "Lalitha" <Lalitha@.discussions.microsoft.com> wrote in message
> news:AF09E8A4-90F8-4D4E-9429-94E2FAC3726F@.microsoft.com...
>
>
identity management. I did as follows
Step 1: Built MyDB1 with data in server1.
Step 2:Set up Server1 MyDB1 as Publisher (Followed msdn white paper
p2ptranrepl.doc)
Step 3:Took backup of MyDB1 in Server1 and restored in server2 as MyDB1
(again as per steps in msdn white paper)
Step 4:Identity range for tablen in server1 is say 1-1000 (have put the
necessary check constraint on identity column. Also made Identity column NOT
FOR replication. Following Hillary Cotter's writeup in
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/)
I reseeded the identity value in Server2 MyDB1 to have range as 1001 to
2000. Added necessary check constraints
Step 5: As per the msdn whitepaper setup using "Configure peer to peer
topology' wizard.
Step 6: Now say the identity range of server 1 is consumed and I want to
reseed the value to say 2001-3000. Server2 has max as only 1010 and hence
left as is.
I reseed server1 table1 and also change the check constraints accordiingly
from 2001-3000.
**********Now the issue is this change in constraint gets automatically
propogated to the subscriber. But i do not want the subscribers to move to
the new range. They should be in 1001-2000 only. I have verifed that 'Copy
Check Constraints' property is set to FALSE. But still the constraints are
getting propogated to subscribers****************************
This becomes a blocking issue for my manual identity management.
Kindly help to resolve the same.
Thank you,
Lalitha
remove the check constraints. They aren't necessary. I think you should be
using larger ranges as well.
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
"Lalitha" <Lalitha@.discussions.microsoft.com> wrote in message
news:AF09E8A4-90F8-4D4E-9429-94E2FAC3726F@.microsoft.com...
>I am using P2P replication between two server. I face a big issue in manual
> identity management. I did as follows
> Step 1: Built MyDB1 with data in server1.
> Step 2:Set up Server1 MyDB1 as Publisher (Followed msdn white paper
> p2ptranrepl.doc)
> Step 3:Took backup of MyDB1 in Server1 and restored in server2 as MyDB1
> (again as per steps in msdn white paper)
> Step 4:Identity range for tablen in server1 is say 1-1000 (have put the
> necessary check constraint on identity column. Also made Identity column
> NOT
> FOR replication. Following Hillary Cotter's writeup in
> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/)
> I reseeded the identity value in Server2 MyDB1 to have range as 1001 to
> 2000. Added necessary check constraints
> Step 5: As per the msdn whitepaper setup using "Configure peer to peer
> topology' wizard.
> Step 6: Now say the identity range of server 1 is consumed and I want to
> reseed the value to say 2001-3000. Server2 has max as only 1010 and
> hence
> left as is.
> I reseed server1 table1 and also change the check constraints accordiingly
> from 2001-3000.
> **********Now the issue is this change in constraint gets automatically
> propogated to the subscriber. But i do not want the subscribers to move to
> the new range. They should be in 1001-2000 only. I have verifed that 'Copy
> Check Constraints' property is set to FALSE. But still the constraints are
> getting propogated to subscribers****************************
> This becomes a blocking issue for my manual identity management.
> Kindly help to resolve the same.
> Thank you,
> Lalitha
>
|||Hi,
Thanks for the response.
Though I use large ranges, having the check constraints is for making the
solution fool proof. This validation will ensure that no data gets inserted
other than my configured range.
When 'Copy Check Constraints' is set to FALSE, why is P2P behaviour different?
1) Is this a bug in P2P which will be fixed in future service packs?
2) Is this a limitation of P2P?
If this is the limitation of P2P then I intend to go for merge.
Please clarify.
Thanks for your inputs...
Lalitha
"Hilary Cotter" wrote:
> remove the check constraints. They aren't necessary. I think you should be
> using larger ranges as well.
> --
> 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
>
> "Lalitha" <Lalitha@.discussions.microsoft.com> wrote in message
> news:AF09E8A4-90F8-4D4E-9429-94E2FAC3726F@.microsoft.com...
>
>
P2P replication
Hi,
I understand that P2P replication does not do automatic conflict detection.
I do not have conflict scenarios in my application except for the following
single requirement:
For a test plan i will add cases and will give numbering like case 1, 2, 3,
4 etc.
Any new case added to a plan will have the number as max(number) for the
plan + 1.
There are 4 servers with P2P replication setup. Consider the below scenario
1) Assume that max number for the plan at second t1 was 4.
Server1 adds new case. Data gets committed in DB server1 as 5. User gets
success message.
In the same instant server2 adds new case, data gets committed in DB server2
as 5. User gets success message.
Due to replication lag, it will take couple of section for data in db
server1 to reach dbserver2.
When data synchronization happens there are two 5's and this will cause
conflict.
How will this kind of conflict during replication be handled in
1) Merge Repln
2) P2P repln
I want to go with P2P here as there is only one less probable conflict
scenario to get the better performance that P2P offers. Any suggestion how
this will behave or should be handled in P2P.
Thanks,
Lalitha
Lalitha,
in merge the selected conflict resolver will run and you'll get a message
like this: "The row was inserted at 'computer1.Sub1' but could not be
inserted at 'computer2.Northwind'. Violation of PRIMARY KEY constraint
'PK_PublicationName'. Cannot insert duplicate key in object 'tyourtable'."
In P2P you'll have to modify the stored procedures to avoid an error which
will stop the distribution agent from running, or use the SkipErrors
parameter to avoid it.
However, if the PK is PlanID and a geographic identifier, you'll have the
data partitioned and no resulting PK errors.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I understand that P2P replication does not do automatic conflict detection.
I do not have conflict scenarios in my application except for the following
single requirement:
For a test plan i will add cases and will give numbering like case 1, 2, 3,
4 etc.
Any new case added to a plan will have the number as max(number) for the
plan + 1.
There are 4 servers with P2P replication setup. Consider the below scenario
1) Assume that max number for the plan at second t1 was 4.
Server1 adds new case. Data gets committed in DB server1 as 5. User gets
success message.
In the same instant server2 adds new case, data gets committed in DB server2
as 5. User gets success message.
Due to replication lag, it will take couple of section for data in db
server1 to reach dbserver2.
When data synchronization happens there are two 5's and this will cause
conflict.
How will this kind of conflict during replication be handled in
1) Merge Repln
2) P2P repln
I want to go with P2P here as there is only one less probable conflict
scenario to get the better performance that P2P offers. Any suggestion how
this will behave or should be handled in P2P.
Thanks,
Lalitha
Lalitha,
in merge the selected conflict resolver will run and you'll get a message
like this: "The row was inserted at 'computer1.Sub1' but could not be
inserted at 'computer2.Northwind'. Violation of PRIMARY KEY constraint
'PK_PublicationName'. Cannot insert duplicate key in object 'tyourtable'."
In P2P you'll have to modify the stored procedures to avoid an error which
will stop the distribution agent from running, or use the SkipErrors
parameter to avoid it.
However, if the PK is PlanID and a geographic identifier, you'll have the
data partitioned and no resulting PK errors.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
P2P reindexing
I was looking at setting up multiple SQL Server 2005 Standard edition
servers using Peer-to-Peer Replication with the goal being able to
reindex one server at a time so as not to have any downtime (via locked
tables). Would this work?
Also, I know log-shipping to a standby server only requires 1 CPU
license which is attractive as CPU licenses are pricey. Still, if I
use P2P only for this reindex purpose (including three nodes for
voting) with only one server being used by customers/users, is a 1 CPU
license good enough?
Thanks!
MichaelI can't seem to find an answer anywhere for these questions:
Can I run ALTER INDEX REBUILD seperately between a live SQL 2005 server
and the hot-standby server? This way, if the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Is it allowed to have a single CPU license for two SQL 2005 server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
The goal is to have no downtime for executing ALTER INDEX REBUILD.
Thank you,
Michael
servers using Peer-to-Peer Replication with the goal being able to
reindex one server at a time so as not to have any downtime (via locked
tables). Would this work?
Also, I know log-shipping to a standby server only requires 1 CPU
license which is attractive as CPU licenses are pricey. Still, if I
use P2P only for this reindex purpose (including three nodes for
voting) with only one server being used by customers/users, is a 1 CPU
license good enough?
Thanks!
MichaelI can't seem to find an answer anywhere for these questions:
Can I run ALTER INDEX REBUILD seperately between a live SQL 2005 server
and the hot-standby server? This way, if the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Is it allowed to have a single CPU license for two SQL 2005 server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
The goal is to have no downtime for executing ALTER INDEX REBUILD.
Thank you,
Michael
Friday, March 9, 2012
P2P reindexing
I was looking at setting up multiple SQL Server 2005 Standard edition
servers using Peer-to-Peer Replication with the goal being able to
reindex one server at a time so as not to have any downtime (via locked
tables). Would this work?
Also, I know log-shipping to a standby server only requires 1 CPU
license which is attractive as CPU licenses are pricey. Still, if I
use P2P only for this reindex purpose (including three nodes for
voting) with only one server being used by customers/users, is a 1 CPU
license good enough?
Thanks!
MichaelI can't seem to find an answer anywhere for these questions:
Can I run ALTER INDEX REBUILD seperately between a live SQL 2005 server
and the hot-standby server? This way, if the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Is it allowed to have a single CPU license for two SQL 2005 server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
The goal is to have no downtime for executing ALTER INDEX REBUILD.
Thank you,
Michael
servers using Peer-to-Peer Replication with the goal being able to
reindex one server at a time so as not to have any downtime (via locked
tables). Would this work?
Also, I know log-shipping to a standby server only requires 1 CPU
license which is attractive as CPU licenses are pricey. Still, if I
use P2P only for this reindex purpose (including three nodes for
voting) with only one server being used by customers/users, is a 1 CPU
license good enough?
Thanks!
MichaelI can't seem to find an answer anywhere for these questions:
Can I run ALTER INDEX REBUILD seperately between a live SQL 2005 server
and the hot-standby server? This way, if the live table is locked
during this operation, the hot-standby server would take over the CRUD
statements.
Is it allowed to have a single CPU license for two SQL 2005 server if
one is a hot-standby-only server and the standby-only server is only
used for failover purposes. Note that when tables are locked during
ALTER INDEX REBUILD, I assume the failover would kick in. Is that
possible?
The goal is to have no downtime for executing ALTER INDEX REBUILD.
Thank you,
Michael
p
I am having some issues with my replication server. The sqlservr.exe is running 75-100%. Sometimes it pins up above 95 for more than two minutes. It's eating a ton of memory as well. The only thing the server is used for is to replicate our production server. The distribution database runs on it and the log reader, but thats it!
Dell Poweredge 2950
2003 x64
Xeon 5160 3.00
8.0 gbs of ram
Currently replication is running OK with the processor that high. But it only has about 900 mb of memory available. Does anyone have any ideas why sqlserver.exe needs that much horsepower? I just can't image it is right.
Thanks,ok ok i figured this out. i am kind of a newbie. It was an in house app running huge select statements that was eating up all the CPU. I ran a trace and it was pretty obvious. The replication db also serves as the reporting db. Not a replication issue at all, the cpu is running very low now.
thanks
Saturday, February 25, 2012
overwrite a record on the subscriber
Hello. I have a table which I replicate to server B. Does it pose any
problems if I update the table Server B, then replication overlays the rows
a minute later? Someone told me that replicated tables keep track of the
last date/time the row was updated, but I thought that was only the
published table. Is this true?
Thank you,
Steve
You are free to update tables on the subscriber if the are part of a
transactional or snapshot publication.
With merge replication the change will make their way back to the publisher.
If you insert records with transactional replication on the subscriber you
may get a primary key violation. If you are ok with this, you can change
your profile of your distribution agent to continue on data consistency
errors. To do this expand Replication monitor in EM, expand the Replication
Agents folder, expand distribution agents folder, right click on your
distribution agent, and select agent profiles. Then select the continue on
data consistency errors profile. Stop and start your distribution agent.
Keep in mind you have lost database consistency between your publisher and
subscriber.
For most replication solutions this is NOT a good thing, but your particular
solution might benefit from it.
"SteveS" <ssinger@.trendmls.com> wrote in message
news:%23uznj2cFEHA.3080@.tk2msftngp13.phx.gbl...
> Hello. I have a table which I replicate to server B. Does it pose any
> problems if I update the table Server B, then replication overlays the
rows
> a minute later? Someone told me that replicated tables keep track of the
> last date/time the row was updated, but I thought that was only the
> published table. Is this true?
> Thank you,
> Steve
>
problems if I update the table Server B, then replication overlays the rows
a minute later? Someone told me that replicated tables keep track of the
last date/time the row was updated, but I thought that was only the
published table. Is this true?
Thank you,
Steve
You are free to update tables on the subscriber if the are part of a
transactional or snapshot publication.
With merge replication the change will make their way back to the publisher.
If you insert records with transactional replication on the subscriber you
may get a primary key violation. If you are ok with this, you can change
your profile of your distribution agent to continue on data consistency
errors. To do this expand Replication monitor in EM, expand the Replication
Agents folder, expand distribution agents folder, right click on your
distribution agent, and select agent profiles. Then select the continue on
data consistency errors profile. Stop and start your distribution agent.
Keep in mind you have lost database consistency between your publisher and
subscriber.
For most replication solutions this is NOT a good thing, but your particular
solution might benefit from it.
"SteveS" <ssinger@.trendmls.com> wrote in message
news:%23uznj2cFEHA.3080@.tk2msftngp13.phx.gbl...
> Hello. I have a table which I replicate to server B. Does it pose any
> problems if I update the table Server B, then replication overlays the
rows
> a minute later? Someone told me that replicated tables keep track of the
> last date/time the row was updated, but I thought that was only the
> published table. Is this true?
> Thank you,
> Steve
>
Monday, February 20, 2012
Overlapping partitions and join filters
I am using Sql 2005 and merge replication with push subscriptions. I have several dynamic join filters on some of my tables.
The join filters all use a central table that maps say a server location name (something that is returned from HOTNAME() in my case) to an for a store branch ID. This is a retail system database.
When I add a new new subscription I update this table with the new server location name and it's corresponding branch ID. My filtered tables all have a foreign key in them that is the branch ID. I can then effectively join from the server location name to a Branch ID.
What I have noticed is that if I update one row in the map table, sql server will re-generate all partitioned rows for all subscribers, even for rows that haven't been updated.
The net result is that when I add a subscription, my existing subscriptions all get about 52,000 row updates.
Am I seeing this because I said my partitions will overlap when I created the table articles?
Thanks for any help
Graham
Graham, you always ask the toughest questions 
Are you "updating" or "inserting" the central table? If you're updating, are you moving rows from one partition to another?
|||Hi Greg,
I am updating the central table. The data is not moving partitions either. I see it even when I update the talb eon a row that doesn't have a partition yet, i.e. there are no subscriptions that would cause a match in the filter.
Graham
Subscribe to:
Posts (Atom)