November 6, 2016 at 6:15 pm
I've got more and more applications popping up in my enterprise environment that would benefit from being notified when information they care about is updated.
However, more often than not this information is stored in a proprietary database - an ERP system, or a CRM system like dynamics, whose database schema cannot be modified.
This limitation rules out every Microsoft technology of which I am aware. Change tracking requires both schema and application changes. Query notifications requires the creation of a queue and service. CDC creates a bunch of objects in the CDC'd database.
Out of these three options, we have previously managed to negotiate with a vendor to allow us to enable CDC (by arguing that the CDC objects are technically system objects and not part of the vendor schema as such). However, CDC is an enterprise-only feature, and it has some non-trivial overhead for the DBA when it comes to restoring systems, synching up systems prior to performing upgrades, and so on.
So, learned denizens of sqlservercentral, what are your opinions on good ways to implement change detection against a schema that you do not control and cannot modify? The obvious fallback is polling, but that's just as obviously a costly and poorly-scaling solution.
November 6, 2016 at 10:16 pm
Which SQL Server version(s) and edition(s) are you using?
😎
November 6, 2016 at 11:08 pm
2012, standard on some instances, enterprise on others. A solution that works for standard edition would of course be preferable.
I have yet to look at 2016 features, if there's some nice new solution there - well, we're due for an upgrade....
November 6, 2016 at 11:15 pm
Have you looked into using Extended Events for i.e. triggering a poll etc.?
😎
November 6, 2016 at 11:32 pm
I've used extended events sparingly for admin purposes, but never as a normal developer resource. I wasn't even aware there was a "data change" type of extended event - unless you mean trickery involving index usage or something?
November 7, 2016 at 12:31 am
Don Halloran (11/6/2016)
I've used extended events sparingly for admin purposes, but never as a normal developer resource. I wasn't even aware there was a "data change" type of extended event - unless you mean trickery involving index usage or something?
I was thinking of the table_update_code_path object from the sqlserver package, "Occurs when update statement is executed."
😎
November 7, 2016 at 4:15 pm
Hm, neither my SQL instance nor google seems to have heard of table_update_code_path.
November 7, 2016 at 4:36 pm
Documentation is slippery... but here is the event
select p.name, p.description, o.name, o.description
from sys.dm_xe_objects o
join sys.dm_xe_packages p
on p.guid = o.package_guid
where p.name='sqlserver'
and o.name like 'table_update_code_path'
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 7, 2016 at 4:58 pm
mister.magoo (11/7/2016)
Documentation is slippery... but here is the event
select p.name, p.description, o.name, o.descriptionfrom sys.dm_xe_objects o
join sys.dm_xe_packages p
on p.guid = o.package_guid
where p.name='sqlserver'
and o.name like 'table_update_code_path'
I have to say though - this event is a Debug event, and so should not form part of a production system - as it could stop working (it doesn't work on my test system)...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 7, 2016 at 6:52 pm
Ah, that makes sense.
Yeah, I think I would prefer to avoid debug events... especially since updates alone are not suffcient and, assuming there are similar insert and delete events, I suppose those would also be debug events.
It's a novel idea, I haven't previously encountered any articles/blogs/etc suggesting EE as a way of doing data change detection. It does skirt around the "proprietary schema" problem, so that's a plus. And yet, I would have thought this proprietary schema problem would be a very common issue. I'm quite surprised that this kind of situation hasn't factored into the architecture and design over at microsoft.
Having said that, dacpacs have a similar issue. I sometimes feel like all of these cool technologies are only designed for use by software development companies working in nice, elegant, self-contained ecosystems 🙁
November 7, 2016 at 7:27 pm
What about Audit Events?
November 7, 2016 at 9:47 pm
The audit events that would arguably useful for data change would have to be database level audits and thus would require alteration to the nominally proprietary database, although I can't imagine there being a lot of resistance to this, but that's similar to the CDC/query notification solution where one can negotiate that it really should be permitted. I expect it might be easier to argue the case for audit.
On the downside you lose any chance at context with audit. That is to say, all we can ask for is whether a DML operation occurred, but not what kind of operation (in terms of the changed data). That's not as good as query notifications, CDC or change tracking with columns updated, but is better than nothing for tables that don't get updated very often.
For tables that do get updated often, though, it seems like one might as well just do polling. The overhead of being told to poll (which is all audit can really do here) would seem to be unnecessary in those cases.
November 7, 2016 at 11:43 pm
mister.magoo (11/7/2016)
mister.magoo (11/7/2016)
Documentation is slippery... but here is the event
select p.name, p.description, o.name, o.descriptionfrom sys.dm_xe_objects o
join sys.dm_xe_packages p
on p.guid = o.package_guid
where p.name='sqlserver'
and o.name like 'table_update_code_path'
I have to say though - this event is a Debug event, and so should not form part of a production system - as it could stop working (it doesn't work on my test system)...
Thanks MM, good to know. I haven't implemented this event but it was on my list as a possible option for a very similar problem.
😎
November 8, 2016 at 12:09 am
Another option I've been using for similar purposes is the dynamic management view sys.dm_db_index_usage_stats, monitoring the changes in user_updates using last_user_update and index_id < 2 to limit the output. The problem with this approach is that if there aren't any time stamp columns indicating which row has been changed/added then it is pretty much a full scan of the table.
😎
November 8, 2016 at 8:20 am
Don Halloran (11/7/2016)
The audit events that would arguably useful for data change would have to be database level audits and thus would require alteration to the nominally proprietary database, although I can't imagine there being a lot of resistance to this, but that's similar to the CDC/query notification solution where one can negotiate that it really should be permitted. I expect it might be easier to argue the case for audit.On the downside you lose any chance at context with audit. That is to say, all we can ask for is whether a DML operation occurred, but not what kind of operation (in terms of the changed data). That's not as good as query notifications, CDC or change tracking with columns updated, but is better than nothing for tables that don't get updated very often.
For tables that do get updated often, though, it seems like one might as well just do polling. The overhead of being told to poll (which is all audit can really do here) would seem to be unnecessary in those cases.
I may be wrong, but you might be able to use the Audit to trigger the polling of the data.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply