| Author |
Topic |
|
Peace2007
Posting Yak Master
239 Posts |
 Posted - 2008-08-05 : 06:12:20
|
| Hi,we have around 20 applications, which each use a separate DB server and different DB schema. Each DB server locates in one sub company. Now we need to integrate these applications. Since each sub company locates in different city, and the integrated application needs a part of data of each application, I am looking for a way to gather the required information from these DB servers via a view or anything else.The number of DB servers may get increased continuously.I'd like to know if service broker is a good idea for that |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-06 : 01:21:16
|
| Thank you very much for your helpful article. So far as I know,this scenario can be done by linked server and transactional replication as well.But I don't wanna take the risk of replication for such sensitive situation and linked server seems slow. Could you tell me which one you'd prefer? I'm not familiar with SB enough; does it work fine on WAN and is it secure enough? |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-06 : 05:06:09
|
| i'm not very familiar with replication so i can't really comment on its security freatures.i'm not sure how and if linked servers encrypt data on the wire accross networks and domains. but since it can use Kerberos i don't think this is a problem.i do know that SB handles all this well and you can have a very secure communication accross domains and networks via dialod and transport security._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-06 : 05:42:55
|
| Thanks again I'm going to try SB on a small domain;will be surely back to you in case of problem :) |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-11 : 04:08:53
|
| Dear Spirit1, I studied your article carefully and tried to put up SB on multiple servers using your scripts. All steps done well except point 6! I copied the certificate to client and replaced the IPs with my master and audited servers' as well as broker Guid but when I run the script (CAA2_Child_Server_Audited_Database_6.sql) I'm given following error:Msg 102, Level 15, State 1, Line 9Incorrect syntax near '<'.Msg 102, Level 15, State 1, Procedure usp_SendAuditData, Line 46Incorrect syntax near '<'.Msg 319, Level 15, State 1, Procedure usp_SendAuditData, Line 48Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.Cannot add rows to sysdepends for the current object because it depends on the missing object 'dbo.usp_SendAuditData'. The object will still be created.the errors refer to these lines of script: 1. BROKER_INSTANCE = <'33205754-9FA1-4CE9-81BA-632220F2BB7E'>, 2. TO SERVICE '//Audit/DataWriter', -- this is a MasterAuditDatabase Service Broker Id -- (change it to yours and remove < and >) <'33205754-9FA1-4CE9-81BA-632220F2BB7E'>3. TO SERVICE '//Audit/DataWriter', -- this is a MasterAuditDatabase Service Broker Id -- (change it to yours and remove < and >) <'33205754-9FA1-4CE9-81BA-632220F2BB7E'> ON CONTRACT [//Audit/Contract] WITH ENCRYPTION = OFF;Could you please tell my how to resolve it? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-11 : 04:38:52
|
look at this comment: (change it to yours and remove < and >)you haven't removed the < and >  _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-11 : 04:47:14
|
quote:
Originally posted by spirit1
look at this comment: (change it to yours and remove < and >)you haven't removed the < and > _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
yes sorry  it works now |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-11 : 04:58:00
|
| do tell me how it works in the end_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-11 : 05:21:27
|
| it works great spirit I couldn't experience SB so easily without your helpful articles.However,I have two questions:1. Is there a way to save the last status (update or insert) for each record only once? I mean,if the first row of the audited table is inserted once, then updated currently two rows are added to the master audit table while I need only to have the last changed data. 2. Can we audit a view through service broker?Many Thanks |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-11 : 05:34:28
|
| 1. hmm... so you want to have the delta saved? you'll have to handle this on the central server in it's activation sproc. query the last saved row for that table, create the delta and insert only the delta.2. you can add a trigger to the view with no problem_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-11 : 07:17:01
|
quote:
Originally posted by spirit1
1. hmm... so you want to have the delta saved? you'll have to handle this on the central server in it's activation sproc. query the last saved row for that table, create the delta and insert only the delta.
you mean manually? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-11 : 07:21:16
|
| yes i mean manually _______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-12 : 03:29:32
|
| I'm trying SB on view so am creating triggers on a view for each of insert, update, and delete but as you know INSTEAD OF trigger cannot be associated with an order so I'm getting error on creating insert view on last statement: "EXEC sp_settriggerorder @triggername=N'[dbo].[insteadTrgViewAudit]', @order=N'Last', @stmttype=N'INSERT'"Honestly,I'm not sure what that actually does and if it's required in view triggers as well. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-12 : 04:14:35
|
| oh the trigger order is not really needed.i've added it because if for some tables you already have a trigger on them they might change the data in the table.so i set the auditing trigger to fire last thus getting all the changes.look at the sp_settriggerorder to see what it does._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-12 : 07:01:44
|
| Dear Spirit, Many thanks to you for your immediate replies. I'm going to apply SB on our company which serves more than 20 servers with different schemes so I'm afraid I'll be referring to you so much. By the way, in your article,you've written that conversation can be ended through scheduled jobs. Is it started automatically to receive messages? or, better saying, I don't know why it's required to end the conversation! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-12 : 07:43:56
|
One more Q: I'm a bit confused about views. I've created Insert and update triggers on my view and SB works fine but when inserting to or updating the view  I was expecting the view triggers be fired when something has changed in tables but it's fired when something is inserted/updated in view. but while views are reading from tables what does insert/update in view mean? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-12 : 08:02:15
|
| conversations can go on indefinitly and for auditing this is a good idea.if there's a need for you to end the conversation then you can. usually a conversation has a lifetime. in your case it has a very long lifetime emmm view triggers are fired when something is changed in the view. i'm not quite sure what your problem here is.table triggers are fired when something changes in the table.so if you have a trigger on the tables that are in the view you don't need a trigger on the view. but i'm guessing here because i'm not familiar with your business requirement._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-13 : 01:20:29
|
| Generally I wonder why trigger on view is required, since views always read data from tables; right? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-08-13 : 04:35:19
|
| auditing trigger on the view isn't required if you ask me._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2008-08-20 : 04:47:29
|
| one more Q I was going to make another table to be audited in another database but in the same DB server, which is being audited; so I modified and ran Child_Server_Audited_Database_6.sql. But when I try to add a row to the table, it gives me this error: invalid object name tcp://audited_server_IP/TestDb1/Audit/DataSenderwhile me table name is city!Could you help me resolve this? |
|
|
|
Next Page
|
IS Service Broker a Good Solution