SQL Monitoring and Challenges from an integration perspective

SQL Server is a relational database management system (RDBMS) developed by Microsoft for Windows and, more recently, for Linux. In any project, databases are paramount as it holds all the Business and Sensitive Data. In addition to that, most of the Microsoft Tools Like BizTalk, Dynamics AX have their Backend Configured with SQL. So, it becomes vital to Monitor Health of SQL not only from Business end but also for the entire system to function in a seamless way.

Monitoring Scenarios (WRT Integration):

Below are the few scenarios where Monitoring can help you to avoid loss of Data or hampering Business.

Scheduled Backups:

In any environment, it’s always important to Backup your Databases in a timely manner. During disaster its always important that your Databases can be restored.

So, we have to make sure that our jobs are running successfully and we configured our maintenance plans in the right way. If these tasks fail, we need to get notified and we can identify the root cause and fix it.

Scheduled Jobs:

In most of the scenarios, we would be having all the Business-Critical Tasks in the Jobs in SQL. So, it’s Important that these Jobs run successfully and if there any Errors or Warnings, then the respective teams are notified.

Size of the Databases:

When there is a lot of traffic in your Databases the size of the Databases tend to grow. Due to this the Scripts Executing on those Databases may get slow and sometimes Unresponsive.

For instance, related to BizTalk, we see a lot of times Tracking Databases gets occupied or your Message Box DB getting occupied. This situation leads to Throttling where BizTalk gets a lot slower and become unresponsive.

If we could monitor these abrupt increases in Transactions or Size of the Database we can take necessary actions to avoid them.

Execution Count and Times:

Based on the traffic we are receiving, we would be hitting a Few Stored Procedures or Functions frequently. In those scenarios if we have the Metric where we get how many times these Stored Procedure have been Called and the Execution Time of it.

This helps us to get deep Insights and to take actions whether we need to Scale up our resources or we need to do indexing etc.

Disk Spaces:

In most of the On-Premise Environments, we see Backups saved to a Physical Location. These Backups tend to occupy a lot of space. So, there are chances that you will be running out of storage.

Whenever we reach the threshold if we could have an alert informing that we will be running out of space we can take corrective actions accordingly.

Missing Indexes:

Indexes are used basically to quickly retrieve Data from the Database. This helps reduce the Timeout issues. If we could run the missing indexes report on our Databases on a periodical basis and get the columns which probably need them it will help the Administrator Teams to make their decisions easily.

We also need to monitor the Fragmentation Level of the indexes. If required, we need to rebuild them.

Security Audits:

Security Audits with respect to SQL will be to check which Users or Groups have access to SQL and what permissions are allotted to them. It’s important that we need to check them on a timely basis.

Some of the Users might only Require a Read only access for some Databases or Tables. So, we need to give appropriate permissions for the users or Groups.

For Instance, one of the User or Group gets DB Owner Access on one of the Databases which holds Sensitive Information. Because of the entitlement, the user gets full control and can perform any Operations and many which can be dangerous in real time.

So, it’s very important that we do our Auditing on our Database and Check whether the Access are allotted Correctly to Users or Groups. If we would get this Report in our Monitoring then it would save a lot of effort for the Teams to do them.

Monitoring Tools:

We can use PowerShell to Create these kinds of Monitoring. Although PowerShell scripts doesn’t cost us any, but It does take a lot of effort in writing them and also you need to have knowledge in PowerShell.

So, we need to have some Monitoring where its easy to install, configure and raise alerts in Realtime.

Tools like AIMS etc. helps to deal with these kinds of Monitoring and Real time performance analytics which will help us in predicting problems and take actions accordingly.

Some of the Features are like below

1. Easy to install the agents and configure them in no time.
2. Creating Dashboards for Important Events and downloading them as PDFs.
3. Configuring Custom Alerts to send the emails.
4. Monitoring Executions Times of Stored Procedure which will help you make real time decisions.
5. Monitoring Physical Backup Discs so they don’t run out of Space.

Conclusion:

I have discussed some of the common monitoring scenarios from an integration point of view. There are lots of other features like CPU utilization etc. which will help us to deal with these situations in the right way.

If people monitor thee scenarios manually there is a chance they might miss some checks and leads us to all sorts of issues. So, Monitoring Tools helps the Administrative Teams to do their job in a more effective way.