0

I have two applications, each with their own SQL DB's. I have no control over the applications themselves, but I do have access to the databases.

What I'm looking to do is, when there is a change to the data for certain tables in App1, I need to either insert/update this data in the App2 database. This needs to be done in a 'live' scenario and not by polling periodically.

Currently I have done this using triggers, but I would like to consider some alternatives. I don't know if it is possible to create some sort of C# application which constantly runs in the background as a windows service or the like that monitors for changes. The minimum SQL Server to support is 2005.

Any help or suggestions are greatly appreciated.

Thanks

Adam
  • 33
  • 3
  • 3
    Have you considered [SQL Server Replication](http://msdn.microsoft.com/en-us/library/ms151198.aspx)? – Simon Wilson Jun 10 '13 at 12:45
  • 2
    IMO you're already doing it the right way. You could of course optionally build a proxy that those applications have to go through so you could monitor the statements but that's a little insane really. – Mike Perrenoud Jun 10 '13 at 12:46
  • 1
    Why were your triggers not suitable? – Deblaton Jean-Philippe Jun 10 '13 at 12:47
  • Thanks Simon. Sorry I should have pointed out that the DB's aren't the same. Don't they need to be for SQL Server Replication? Also I need to do programmatic checks, for example when retrieving ID's for 'Codes' tables (Titles, Departments etc). I'll update my original post. – Adam Jun 10 '13 at 12:49
  • 1
    @Adam not at all. There's a lot of room for manipulation/massaging of data in replication jobs. – Mansfield Jun 10 '13 at 12:50
  • @Mansfield, oh right great, I didn't realise that was possible. This probably requires more investigation on my part. – Adam Jun 10 '13 at 12:54
  • Thanks for all your comments, this site is amazing for response rate/time. I'm used to waiting days+! – Adam Jun 10 '13 at 13:04
  • 1
    @Adam - As Mansfield mentioned replication can be done across varying DB's. I just completed a replication of AS400 data into a SQL Server system. – Simon Wilson Jun 10 '13 at 13:06
  • @SimonWilson - That's good to know, I'm going to look into replication a bit further. Thanks a lot for your response. – Adam Jun 10 '13 at 13:09

1 Answers1

1

You could use SQLDependency for this if you really want to go the .NET route, but I can't imagine it being more efficient/better than simply using triggers as you've done already.

Mansfield
  • 14,445
  • 18
  • 76
  • 112
  • Thanks, I had looked into this and it looked like a viable solution. The issue I'm having at the moment, which may be down to my triggers, is that App1 updates it's database in a way that causes multiple triggers to be fired multiple times from one simple update in the front end. Which in turn causes a delay on the front end. (Also I trust my C# programming and error handling slightly more :)) – Adam Jun 10 '13 at 13:02