.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

how to resolve conflict when using sql service broker to do bidirectional replication?

Posted By:      Posted Date: October 03, 2010    Points: 0   Category :Sql Server
 

greetings ,,

after spending days in search about the best practice to make bi-directional replication between 2 databases on 2 distributed sql server instances ,,i have found that sql server service broker is the most suitbale with my business req to use as i have a problem with the updatable subscrption replication due to leased line between networks in 1 MEGA and it will be large amount data that will be replicated and that will not work as the latency will increase ,,but i need some help to know how can i resolve these conflicts when i am using sql server broker service:

1-If you insert a record that has a key into a table on one of the servers and another record that has the same key already exists on the other servers that participate in the replication, the replication does not propagate the changes to the other servers.

2-When you update a column in a record that is updated at the same time on another server, the data may be different on the two servers.

3-When you update different columns in a record, simultaneous updates of different columns of a record may sometimes lead to conflicts.

4-When you delete a row that is being deleted at the same time on another server that is participating in the replication, the replication fails because the DELETE statement does not affect any rows on some of the subscribers.

 




View Complete Post


More Related Resource Links

Service Broker Conflict

  

Greetings,,

i want to make bi-diretctional replication between 2 databases each is one sql server instance,,after searching for the best way to do bi-directional replication i found that i can use service broker to exchange messages between 2 databases ,,but i have a question about conflict detecting and resolution and i need some help,,my question is how can i detect these type of conflicts that can be occured during exchanging messages ,,for examples,,

1-updating the same rows,,if each message containg the rows that are updated on each database .
2-updating rows on 1 database that are deleted on the other,,so when the message from data base 1 will contain an update statement for the rows that have been updated but when it arrives @ database 2 and executed the result will be 0 rows affected because those rows are deleted on database 2..
and there are other conflict,,i need some help or any ideas to know how can resolve those conflict ,,,
any suggestion will be highly apperciated

thnx all


2 way (bidirectional) MERGE replication?

  
Is this possible? I have SQL Server 3.5 CE on a windows mobile device which is going to do the majority of collection of information. I have SQL Server 2008 (needs to work with 2005 too) on a server and have no problem getting data from the server to the mobile device via replication but I need to know if it's possible and if so how, to get the merge to work the other way at the same time... or even via another means if it must do. Thanks for any help. Steve   It might also be helpful to add here that the server is configured for web synchronisation.

WCF Service "DataContract" conflict error

  
Hi,I am consuming two wcf services in my workflow. The services have the same datacontract with name "Consumer".My Workflow building successfully, But when i am trying to publish my workflow i am getting the "consumer" datacontract conflit error. The error is :DataContract for type 'Service1.Consumer' cannot be added to DataContractSet since type 'Service2.Consumer' with the same data contract name 'Consumer' in namespace 'http://MyWorkflow' is already present and the contracts are not equivalent.Suggest me how to solve this conflict.Thanks in advance.

SQL Server 2005 bidirectional replication problems

  

I am having a system with two servers running SQL Server 2005 on Windows Server 2003.
Between the two SQL Server instances I am having a bidirectional replication of all my tables to always have the databases in sync.

When the replication is set up on the servers it normally runs fine for some time and then it stops working and I get errors like the following:

  Command attempted:
  if @@trancount > 0 rollback tran
  (Transaction sequence number: 0x000001B200005784000D00000000, Command ID: 1)
 
  Error messages:
  Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. (Source: MSSQLServer, Error number: 2627)
  Get help: http://help/2627
  Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. (Source: MSSQLServer, Error number: 2627)
  Get help: http://help/2627

And another error:

  Error messages:
  Agent 'SERV-A2-SYST-SERV-A1-10' is retrying after an error. 77 retries attempted. See agent job history in the Jobs folder for more details.


I do not know much about how the bidirectional replication works as I just set the replication up by some script

Bi-directional Replication conflict handling and resoltion?

  

hi all

i want to know how to reslove conflicts when using Bi-directional transactional replication ,,conflicts like

1-If you insert a record that has a key into a table on one of the servers and another record that has the same key already exists on the other servers that participate in the replication, the replication does not propagate the changes to the other servers.

2-When you update a column in a record that is updated at the same time on another server, the data may be different on the two servers.

3-When you update different columns in a record, simultaneous updates of different columns of a record may sometimes lead to conflicts.

4-When you delete a row that is being deleted at the same time on another server that is participating in the replication, the replication fails because the DELETE statement does not affect any rows on some of the subscribers.

..i need help to know what to do to handle this conflicts and if there is any implementation or any ideas about this issue

 

thnx

 

3-


Service Broker Queue message activates stored Proc as SA but the stored proc is unable to access oth

  

I have a queue whose activation I defined as below

ALTER QUEUE SomeInitiatorQueue
WITH ACTIVATION
( STATUS = ON,
PROCEDURE_NAME = usp_SomeProc,
MAX_QUEUE_READERS = 30,
EXECUTE AS OWNER)

Now I am trying to access another database in usp_SomeProc as below

Select SomeColumn SomeOtherDb.dbo.SomeTable

Here I am getting this error "The server principal "sa" is not able to access the database "SomeOtherDb" under the current security context."

It never allows unless I grant all permissions on the database to all.

what is the solution?


how to get xml messages from service broker?

  

greetings 

 

i wana know how can i get your XML messages from service broker (in case of distributed brokers) and how to parse them to produce our data?

 

thnx


Verifying GRANT permissions on Service Broker CONTRACT and Queue

  

I am setting up SqlDependency and wish to verify that the permissions have been applied correctly.

One of the commands that should be run to grant permissions is to grant references on a contract for the user:

GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]  to [sql_dependency_subscriber]

How can I check whether the user e.g. 'databaseUser' has this REFERENCES granted on this contract? Which view/stored proc. to use?

The other command is similar (also required for SqlDependency) but grants permission "RECEIVE":

GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber]

How could I verify that the RECEIVE permission has been granted?

 

I have currently used this command:

SELECT
[permission_name],
[state_desc],
[name]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.database_principals AS grantee_principal
ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE prmssn.class = 0

With this command I can see some permissions (such as CREATE PROCEDURE, CREATE QUEUE, etc.) but the above permissions related to  the contract and queue do not seem to show in the results from sys.database_permissions.

Thankful for any advice!

 


Replication: Server restart vs service restart

  

Replication does not break when i restart the SS Agent, however, when there is a server restart, i get a message : The process was successfully stopped. and the Subscription Status is NOT RUNNING? Why does this happen? Does Tx replication not automatically sync after a server restart?


SQL 2008:Peer 2 Peer Replication: Update-Update Conflict on the same node

  

My environment is comprising of 2 SQL servers setup with P2P replication in SQL 2008, 2 Web servers connect to only one SQL server at a time and make changes to the published tables and 2 Application servers which connects to both the SQL servers all the time but they only read data.

We chose P2P since my app servers are in different locations which read data from both the SQL servers and we use it as HA solution.

Since we are changing data at only one SQL server, thought we should never get update-update conflicts but we are now on the same node.

A conflict of type 'Update-Update' was detected at peer 3 between peer 1 (incoming), transaction id 0x0000000000725ca2 and peer 1 (on disk), transaction id 0x00000000007259e9 (Source: MSSQLServer, Error number: 22815)

We are making changes only at Server A and Server B is my backup server.

I made a varchar field updated in 1 row on a table in server A then it gets replicated to Server B
then I made the same field updated for 160 rows on the same table in Server A then it gets replicated to Server B
then I reverted back the change made to the field on all 160 rows on the same table in Server A then my replication failing with Update-Update conflict.

I'm not sure whats causing this because I'm not making any change

Merge Replication and Conflict Management

  

Hi,

I am a newbee and now setting up Merge Replication on SQL Server 2005 as below.

Step1   Publisher > Node1 >publisher

In the morning,   Node1 will sync with Publisher, update data and sync back to Publisher.

Step2   Publisher > node2 > Publisher

Then, later Node2 will sync with Publisher, update data and sync back to Publisher.

 

My problem here is

In Step 2, if Node1 accidentally modify data and sync with Publisher after Node2 is synchronized , the data from Node2 would be overwritten once it sync back to publisher due to conflict as Publisher always has high priority.

So what I want is simply giving Node2   a high priority not to over write its data once sync with publisher back. But node 2 will get data if there is no conflict.

I know there are some conflict resolvers but I don't know how to use them.

thank you

zawhtway


Custom Conflict Resolver in Merge Replication

  

Hi,

I am a newbie and now setting up Merge Replication on SQL Server 2005 as below.

Step1   Publisher1 > Node1 > Publisher1

In the morning,   Node1 will sync with Publisher1, update data and sync back to Publisher1.

Publisher1 and Publisher2 are quite similar (some articles are the same)

Step2   Publisher2 > Node2 > Publisher2

Here I want to write a custom code for conflict resolver where Node2 will always be a winner whenever the conflict occurs. If there is no conflict, data will be sync.

I tried to use a Conflict resolver comes along in SQL Server 2005, but it also affect not only in the conflicting situation but also in normal merging without conflicts.

thank you

zawhtway


Optimizing tempdb for service broker based operations

  

SQL 2005 Standard.

I have an application based on Service Broker that is very very slow.

I know that service broker is based on tempdb and tried to optimize it moving on a separate disk and creating a different filegroup with new, big enough datafiles into it.
However it is still slow ..probably also due to the fact that sql server is a virtual machine with no dedicated disk.

Can you please recommend the best practices for dealing with high tempdb workload ?

Thanks !!


Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_

  

Error:Msg 468, Level 16, State 9, Procedure CreditRecal1_Update, Line 50
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.

 Update m Set m.CreditLeft = t.CreditLeft
 From mMemberProfile m Inner Join #Temp t On t.UserId = m.UserId
 Inner Join mMemberTree mt On mt.UserId = t.UserId
 Where mt.[Role] = 'MEM'

Why is it so?


Windows Service Applications Tutorials

  
You can easily create services by creating an application that is installed as a service. For example, suppose you want to monitor performance counter data and react to threshold values. You could write a Windows Service application that listens to the performance counter data, deploy the application, and begin collecting and analyzing data.

WCF Tip: Using Properties in Service Contracts

  
you'll notice all of the Service Contracts are implemented with interfaces containing only methods. For the longest time I thought that's all that was supported, until I thought about it a little more. In C# land, properties are simply wrappers around a get method and set method, so why wouldn't we be able to use properties in a service contract? Well the answer is we can.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend