Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nicholasdoan
Starting Member
3 Posts |
 Posted - 2010-08-25 : 13:12:08
|
| I have service broker up and running accross 2 instances. If data is entered/updated on table A in instance A, it will send a message to the target queue and a stored procedure is used to read in the message and perform the update to table B on instance B.Once service broker is up and running, does anyone know if there would be scenario where a message would be lost or unprocessed? I'm trying to determine if there is a downside to using service broker as a data replication method. Any insight would be appreciated. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 13:32:08
|
| You should be using Transactional Replication for this. Why re-invent the wheel, especially when it's a darn good wheelAs for downside, Service Broker is not guaranteed to be transactionally consistent. |
 |
|
|
nicholasdoan
Starting Member
3 Posts |
Posted - 2010-08-25 : 13:48:01
|
| Thanks for your response. Transaction replication would not work in our case as we are not mapping column for column. We are using the updated data to update only certain record. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 13:52:33
|
| thats what filters are for |
|
|
|
nicholasdoan
Starting Member
3 Posts |
Posted - 2010-08-25 : 14:11:15
|
| Sorry, perhaps I didn't explain correctly or completely of why we can't use replication. Perhaps this example might help.tableA.Col1, tableA.Col2 ----> tableB.Col3-----------------------------"asdf", "123" ----> "asdf123"if col2 is updated, col3 would be updated accordingly"asdf", "333" ----> "asdf333" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-25 : 14:36:12
|
quote:
Originally posted by russell
As for downside, Service Broker is not guaranteed to be transactionally consistent.
Do you have a reference for this?I'd say SSB is transactionally consistent, if you want it to be.See http://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspx#sqlsvcbr_topic7
N 56°04'39.26"E 12°55'05.63" |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-25 : 16:06:56
|
| you're right Peter. thanksAnd as for your mapping nicholasdoan, what you're doing is likely to be a better solution. I do not like mapping objects with different names and messing around with the columns in replication. it becomes a maintenance nightmare. |
|
|
|
|
|
|
|
|
Service Broker downside