I'm trying to config a notification service in SQL Server + ASP.NET Core + Angular project. I am struggling with configuring the SQL Server Service Broker.
Scenario: I have a table that gets rows inserted from a job, when a row is inserted, I need to send a notification from SQL Server > ASP.NET Core > Angular. I m currently struggling to configure the SQL Server Service Broker.
I want to know the steps how should I configure SQL Server services, queues, etc..
I think, you're on the wrong path here, or at least, the way you've explained it. If you read the Microsoft docs, it says
Use Service Broker components to implement native in-database asynchronous message processing
This is your application that must initiate contact with the DB. You're posting this MSSQL>ASP.NET core>Angular, while in reality it should be this Angular>ASP.NET core>MSSQL
Service Broker is not event-driven like RabbitMQ or SignalR. There is no direct 'push'. You mimic this like
WAITFOR (
RECEIVE TOP(10)
type,
body
FROM myQueue
), TIMEOUT 10000;
Another option is good-old SqlDependency
I guess only, that under "asp.net core" you mean web API. Then, it sounds like SignalR will be used between Angular and Web API, then you just pull the data from a queue. While Sql Broker in this case is nice that it provides in-DB queue. Because if you had to use a table for it, you would also needed to think of concurrency and deletions, etc..
^^ That is design portion of your application.
Now, if you are interested in configuration of SQL Server broker, this question should be closed because this information is broadly available on the internet and we don't need to repeat it here https://www.sqlservercentral.com/articles/step-by-step-how-to-setup-service-broker-in-single-database
