SQL triggers, SP ans service broker objects

[cbealperto08]

Hi, I read that christian is not maintaining the project anymore and this in the most recent activity. My question is about the
start method if the timeouts are not set does is mean that the SL server will not auto delete the objects created when not properly disposed? My database is now flooded with objects and almost everyday we encounter locks from the trigger saying
tr_dbo_PRODUCTION_ATE_1d9ce1de-fb8d-4494-85dc-a251e0cfa641_Sender. Hope you can guide me for the best practice.

Thanks!

[tomasfabian]

Hi @cbealperto08, thx for reaching me out.
As far as I remember there is a default timeout in case that it is not set.

Christian unfortunately left his project and there are some open issues in his repository. I tried to fix some of them in my SqlTableDependency.Extensions package. Did you try it out? It also contains a reconnection mechanism, so you can reuse your existing triggers etc.

Install-Package SqlTableDependency.Extensions -Version 3.0.0

See also Wiki- How-to-convert-from-SqlTableDependency-to-SqlTableDependencyProvider

I'm not using this approach anymore, I started to work on a more loosely coupled approach via Kafka sink connectors. See my newest project https://github.com/tomasfabian/ksqlDB.RestApi.Client-DotNet#cdc---push-notifications-from-sql-server-tables-with-kafka. You can actually execute continuous push queries against your table change records thx to the stream-table duality. We can discuss this too. I would like to help the .NET community as much as I can.

@lekrus does my package solve most of the issues with not auto deleted objects in SqlTableDependency? Could you please join the discussion please, too? I remember you from issues #11, #13.

Thank you, Tomas.

[lekrus]

Hello Tomas,

Yes, in fact we are using your fixed version since and it's working just fine.
Still, if you could implement that suggested fix from #13 that would be just great, as even though we cannot reproduce that with any local tests, on real production servers that exception happens from time to time and that small fix from #13 solved the issue completely.

Thanks!

[cbealperto08]

Hi @cbealperto08, thx for reaching me out.
As far as I remember there is a default timeout in case that it is not set.

Christian unfortunately left his project and there are some open issues in his repository. I tried to fix some of them in my SqlTableDependency.Extensions package. Did you try it out? It also contains a reconnection mechanism, so you can reuse your existing triggers etc.

Install-Package SqlTableDependency.Extensions -Version 3.0.0

See also Wiki- How-to-convert-from-SqlTableDependency-to-SqlTableDependencyProvider

I'm not using this approach anymore, I started to work on a more loosely coupled approach via Kafka sink connectors. See my newest project https://github.com/tomasfabian/Kafka.DotNet.ksqlDB#cdc---push-notifications-from-sql-server-tables-with-kafka. You can actually execute continuous push queries against your table change records thx to the stream-table duality. We can discuss this too. I would like to help the .NET community as much as I can.

@lekrus does my package solve most of the issues with not auto deleted objects in SqlTableDependency? Could you please join the discussion please, too? I remember you from issues #11, #13.

Thank you, Tomas.

Hi Thomas,
About Kafka I will give this a try. On the other hand my application is receiving notifiction almost every second and the SQL server is sending this to 30 to 80 client PC. Is kafka is suitable for this type of application?

Thanks!

[tomasfabian]

Hi Thomas,
About Kafka I will give this a try. On the other hand my application is receiving notifiction almost every second and the SQL server is sending this to 30 to 80 client PC. Is kafka is suitable for this type of application?

Thanks!

Hi @cbealperto08,
Kafka is horizontally scalable, but of course it depends on your requirements. You can create topics for each db table and partition them etc.

[tomasfabian]

I forgot to mention that you can also horizontally scale Kafka Connect (or ksqldb).
This will buy you options (so discuss it with your project manager, it will cost your company some effort/money), because it will introduce additional complexity into your system.

Regards Tomas Fabian

[pailyn]

Hey @tomasfabian .
I was looking at the trigger and procedure code generated between your sql dependency extension code and christiandelbianco's sql table dependency code.

In his code there are two places where the sql objects can be dropped, in one of the stored procedures associated to one of the sql service queues and the table trigger. In your version its only in the stored procedure.

From what i can tell by default the sql conversation endpoint has a dialog timer set for about 180 seconds (from the base sqldependency table) which if it triggers it runs the portion of the stored procedure that drops all the sql objects. The sql table dependecy component updates this value while the connection is active. In christiandelbianco's trigger it also checks if the conversation is active. if it is it sends the appropriate dml changes to the appropriate service queues. if not, it drops the sql objects associated to the table dependency.

The problem with christiandelbianco's trigger code is that the cursor can be an issue as part of a trigger. it can lock up resources especially if there are multiple table dependency triggers for the same table. if one of them locks up when dropping it's conversation handles it can prevent any of the other triggers from running properly.

This is what I believe @tomasfabian's said in his comment at IsNemoEqualTrue/monitor-table-change-with-sqltabledependency#188

My suggestion is replace the cursor with a while loop that gets the top associated conversation handle. while the query returns a non-zero rowcount it drops the conversation handle, then queries again for any other associated conversation handles. I made the comment in sqltableDepenency project at IsNemoEqualTrue/monitor-table-change-with-sqltabledependency#188.

[tomasfabian]

Hey @tomasfabian . I was looking at the trigger and procedure code generated between your sql dependency extension code and christiandelbianco's sql table dependency code.

In his code there are two places where the sql objects can be dropped, in one of the stored procedures associated to one of the sql service queues and the table trigger. In your version its only in the stored procedure.

From what i can tell by default the sql conversation endpoint has a dialog timer set for about 180 seconds (from the base sqldependency table) which if it triggers it runs the portion of the stored procedure that drops all the sql objects. The sql table dependecy component updates this value while the connection is active. In christiandelbianco's trigger it also checks if the conversation is active. if it is it sends the appropriate dml changes to the appropriate service queues. if not, it drops the sql objects associated to the table dependency.

The problem with christiandelbianco's trigger code is that the cursor can be an issue as part of a trigger. it can lock up resources especially if there are multiple table dependency triggers for the same table. if one of them locks up when dropping it's conversation handles it can prevent any of the other triggers from running properly.

This is what I believe @tomasfabian's said in his comment at christiandelbianco/monitor-table-change-with-sqltabledependency#188

My suggestion is replace the cursor with a while loop that gets the top associated conversation handle. while the query returns a non-zero rowcount it drops the conversation handle, then queries again for any other associated conversation handles. I made the comment in sqltableDepenency project at christiandelbianco/monitor-table-change-with-sqltabledependency#188.

Hi @pailyn,
I remember that I helped Christian to solve this issue #161. It solved a data race which caused that the db objects weren't closed. I thought that it fixed the issue, but Christian also added the drop logic inside the trigger which causes dead locks.
That's why I removed that piece of code (dropAllScript) from the trigger itself. Isn't the issue fixed in SqlTableDependencyWithReconnection? Did you try it without your changes?

In case that someone is observing the same issue with SqlTableDependencyWithReconnection, we can of course try to find a better solution and also try yours. It is strange (or good) that no one is complaining :)

Hi @schneidenbach, @nadjibnet, @dongfo, @nadjibnet, @RajathVenkatesh, @tzepterbvv, @mbowles201, @tmonte is anyone of you using SqlTableDependency.Extensions? Are you observing not dropped database objects? I'm mentioning you because you posted some issue in the past. If you are not interested, please ignore this message and I apologize.

Regards Tomas

[pailyn]

To be honest, I had not tried reproducing the issue with the unmodified SqlTableDependencyWithReconnection, and the PR might not be even required. I'll do some more testing before determining if i should abandon this course of action.

If Christain solved the issue, it wasn't apparent in the code base, since i encountered the locking issue in version 8.5.8 of the sqltabledependency nuget package.

In my case I, this locking issue was preventing any of the dml actions to the table from being commited, and i had to manully clear the triggers and sql objects to get my services using the table to run again.

In my opinion (in conjunction with your original comment on IsNemoEqualTrue/monitor-table-change-with-sqltabledependency#188.), the issue is the the cursor that is used by the trigger to drop conversation handles. I think the dml actions were rolled back as a failure of the trigger from being run, or because the trigger hit a dead lock scenario.

However this might not be the case with your SqlTableDependencyWithReconnection since the drop sql object code, (and the cursor itself) is not part of the trigger but only part of the queue activation procedure that runs only when there is a timeout occurs for the conversation, or when the SqlTableDependencyWithReconnection sends executes the same code when calling DropDatabaseObjects().

[tomasfabian]

@pailyn thx for your great insights! It looks like that Christian solved the issue based on my suggestion, but he unfortunatelly introduced another bug, when he put the drop logic inside the trigger, too.

As we discussed before we can't be sure whether my version is correct, since we are not able to reproduce this issue. Please try out the current version of SqlTableDependencyWithReconnection, too. Hopefully someone who is using this extenion extensively will respond us.

In case that everything is OK, we can still create a simplified base class, without the reconnection logic.

Thank you very much.