SqlTableDependency not working in Production Environment but works in Test Environment

Question

I have an application that heavily relies on SQlTableDependency, the application run well in our test environment; and we recently restored the database from test env to the production env and enabled service broker on the database the newly restored and we configure our web app to point to the production env , However we realised our web app stoped being notified by the database thru sqltabledependency.

here a sample code of our sqltabledependency handle event

public void SqlTableDependency_Changed(object sender, RecordChangedEventArgs<TableA> e)
{
   if(e.ChangeType == ChangeType.Insert || e.ChangeType == ChangeType.Update)
   {
     var record = e.Entity;
     Logger.GetInstance().Info($"Sqldependency event received with success!!");
     A a = new A()
     {
         ZoneId = record.ZoneId,
         FloorId = record.FloorId
     }
   }

}

In test environment i am able to see logs that sqltabledependency successful received the event, but with production database , there no event notification.

I would appreciate any help.

Start with general Service Broker troubleshooting: https://learn.microsoft.com/en-us/troubleshoot/system-center/scom/troubleshoot-sql-server-service-broker-issues — David Browne - Microsoft, Mar 11 '22 at 17:12
I tried to re-enable service broker on the restored database but failed! — Riley Christian, Mar 14 '22 at 12:56

score 0 · Accepted Answer · answered Mar 15 '22 at 12:55

After hours of googling i found a solution that fixed the problem by simply running the following command on the restored database

ALTER AUTHORIZATION ON DATABASE::[restored db name] TO [sa];

Thanks to Remus Rasanu who answered the similar question on this stackoverflow Sql Service Broker not working after database restore

SqlTableDependency not working in Production Environment but works in Test Environment

1 Answers1