January 30, 2010 at 11:09 am
Hello, this sounds impossible to many people including me, but I am still wondering if there is any way to do this:
I have a server where vendor installed their product, whenever a user uses their service, a record will be generated, so basically you can think it like a log server.
Now what we are doing is all the logs need to be replicated to our own server for process. Currently I have a job running every hour, but I am thinking if there is a way I know there is new log generated on the vendor's server? so the new data can be replicated right away, not wait until the scheduled job is executed.
I understand creating a trigger on the vendor side might be the solution, however, asking the vendor to do something on their product involves lots of paper work so I really don't like to approach that way, can anyone provide an alternative solution (don't touch the vendor server, not necessary schedule fetching job every minute)?
Or if it's unavoidable to ask the vendor to do something on their server, what would be the simplest solution?
Thanks lots.
January 30, 2010 at 11:17 am
Another option is to use Service Broker. Maybe have a look to BOL.
Greets
Flo
January 31, 2010 at 7:37 am
Thank you, can you elaborate your thought?
January 31, 2010 at 9:41 am
Hi halifaxdal
Sorry, I forgot one thing. My answer implies some .NET code. I don't know if it is possible to use Service Broker in a pure SQL environment.
Would it be okay for you to use a .NET app for monitoring? If yes, I can show you a simple sample.
Greets
Flo
January 31, 2010 at 10:32 am
Hi Flo,
Thanks for your idea again.
Yes I have no problem creating any .NET application, as long as I don't need to make any change request on the proprietary server. I have full privilege on my own server.
January 31, 2010 at 10:54 am
Did you transaction replication? That will require the least amount of change.
Please read up Transaction Replication
You may want to check with the vendor to find out if this is considered a change as well.
January 31, 2010 at 11:40 am
Hi
Well, without any changes, it wont work. But you don't need to change any tables or other schema objects. Service Broker needs to be enabled on remote database by following statement:
ALTER DATABASE Sandbox SET ENABLE_BROKER;
After this, you have two different options.
First, you can create a QUEUE and a SERVICE which will be used from client. You can use the following statement to do this:
CREATE QUEUE ChangeMessages;
CREATE SERVICE ChangeNotifications
ON QUEUE ChangeMessages ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
Second, let .NET create the queue and service (what will be done automatically if you don't provide a queue name). In this case, the connecting user needs privileges for "CREATE QUEUE" and "CREATE SERVICE".
GRANT CREATE SERVICE TO UserName;
GRANT CREATE QUEUE TO UserName;
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO UserName;
GRANT CREATE PROCEDURE TO UserName;
GRANT CONTROL ON SCHEMA::[dbo] TO UserName;
GRANT IMPERSONATE ON USER::DBO TO UserName;
In both cases, the user needs subscribe privilege:
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO UserName;
Here's a little C# sample how to use:
string cnStr = Properties.Settings.Default.SandboxConnection;
// start notifications on specified queue
SqlDependency.Start(cnStr, "ChangeMessages");
using (SqlConnection cn = new SqlConnection(cnStr)) {
cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "SELECT SomeDateTime FROM dbo.TestData10K WHERE Id = 1";
// create dependency for specific service
SqlDependency dependency =
new SqlDependency(cmd, "service=ChangeNotifications;local database=Sandbox", 0);
// sample notification handler
dependency.OnChange += (o, e) =>
Console.WriteLine("Info: {0} | Source: {1} | Type: {2}", e.Info, e.Source, e.Type);
// execute command to show initial data and activate notifications
using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read()) {
// handle initial data
}
}
Console.WriteLine("Doze...");
}
// wait for notifications
Console.ReadKey();
When working without a pre-configured queue and service, use SqlDependency.Start without a queue name and SqlDependency.ctor without a service name.
Hope this helps.
Greets
Flo
January 31, 2010 at 12:00 pm
Forgotten...
I'm not sure if CLR needs to be enabled. Probably you have to execute the following statement:
EXECUTE sp_configure 'clr enabled', 1;
RECONFIGURE;
There are some requirements for monitored SQL statements.
.. there are some more restrictions I don't remember at the moment. Have a look into MSDN for SqlDependency for further descriptions.
Greets
Flo
January 31, 2010 at 5:02 pm
Thank you Flo,
Service Broker needs to be enabled on remote database
Do you mean this is something I need to have my vendor change their database setting? 'Cause this is something I try to avoid, unless it is unavoidable.
I'll test your approach out later on (I'm away for a week), but I'll post my result here whenever I get a chance to work it out.
Thank you again for your help.
September 23, 2010 at 4:35 pm
Hi Flo,
Hope you or someone else will see this reply.
I am sorry to get back after so long interval, the requirement is heating up so I start working on it now, I don't quite understand your .NET code in your reply, what is it doing? waiting for sql notification? does that mean if I add a new record into the table on the target server/DB your code will catch it? If so, how does the code know the change? I mean there got to be some value passed to it, right?
Here is my modified code in VB:
Imports System.Data.SqlClient
Module Module1
Sub Main()
Dim cnStr As String = My.Settings.NorthwindConnectionString
' start notifications on specified queue
SqlDependency.Start(cnStr, "ChangeMessages")
Using cn As New SqlConnection(cnStr)
cn.Open()
Dim cmd As SqlCommand = cn.CreateCommand()
cmd.CommandText = "SELECT CustomerID, CompanyName FROM dbo.Customers"
' create dependency for specific service
Dim dependency As New SqlDependency(cmd, "service=ChangeNotifications;local database=Northwind", 0)
' sample notification handler
AddHandler dependency.OnChange, AddressOf OnChangeEventHandler
' execute command to show initial data and activate notifications
Using reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' handle initial data
While reader.Read()
Console.WriteLine("CustomerID: {0} | CompanyName: {1}", reader(0), reader(1))
End While
End Using
Console.WriteLine("Doze...")
End Using
' wait for notifications
Console.ReadKey()
End Sub
Public Sub OnChangeEventHandler(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)
Console.WriteLine("Info: {0} | Source: {1} | Type: {2}", e.Info, e.Source, e.Type)
End Sub
End Module
I made this into a console application, does that matter? Should I expect to catch the new data in OnChangeEventHandler? I can't catch it there: what I tried is: I start the console application in debug mode and put a break point in OnChangeEventHandler, hoping to catch data change there; I insert a new row in remote database, but the application doesn't catch the new data change.
Thanks again for kind help.
Raymond
September 24, 2010 at 8:43 am
up
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply