Get Real Time Alerts For Data Issues? Do you use Azure SQL Database and want to get real-time alerts on your mobile when there are data problems without any tools? Want the Alerts to available on Mobile, Email and Desktop so you’re always in the know?
Azure SQL Database can call external REST (API) endpoints, making it easy to integrate with data sources, which could be a good or a bad thing.
Why the bad.
Stop using SQL Server to consume JSON
- SQL is priced and licensed per CPU core – the more cores and compute power, the more expensive it gets. JSON parsing has never been the most CPU-friendly operation in SQL Server. The only way to scale up JSON processing in SQL Server is by adding more computer power = spending more money.
- Most people will save the JSON payload (text) to a table (“just in case”) rather than discard it after the required data has been extracted. This means you will spend a lot on storing text data in an expensive relational database.
- T-SQL is sequential, making it time-consuming to process multiple endpoints.
The Solution!
The best way to pull data from endpoints is via Azure Function or Logic app (or an equivalent in non Azure world), save the result to blob storage (“just in case”, as we like to do), parse what you need and save the structured output in the relational database.
What’s the best use case, then?
Think outside the box and use SQL to produce JSON.
Forget about using pulling data and think about pushing. The ability to call endpoints right from T-SQL stored procedure can be leveraged to build business alerting and monitoring where you can get alerts on the back of your actual data processing (data validation, count mismatch, errors and so on). Imagine if a critical stored procedured failes and you get a push notification on your mobile and an email in the support inbox straight away.
This is usualy difficult to achieve with traditional monitoring systems that have to query your database every so oftten which puts an extra load. Integrating API endpoint in the THROW part of the TRY CATCH could be a life-saver.
Or, have SQL push real time data to Power BI endpoint…. The possibilities are endless, I could go on…