AZURE MONITOR FOR SAP SOLUTIONS
Microsoft recently the launch of “Azure Monitor for SAP solutions” (AMS) version 2, in Public Preview. AMS is Azure native monitoring solution for customers who run SAP workloads on Azure. With AMS, customers can collect and view technical monitoring telemetry of their SAP landscapes within the Azure Portal and efficiently correlate telemetry between various layers of SAP like SAP NetWeaver, different Databases such as SAP HANA, Microsoft SQL server and IBM Db2 and the underlying Infrastructure including High-availability Pacemaker clusters for SUSE & RHEL.
AMS is available through Azure Portal in the following geographies: US, Europe, Australia and Asia.
AMS does not have a license fee.
Microsoft SQL Server Monitoring
When customers run complex, mission critical SAP workloads, issues are bound to happen from time to time. To keep the impact of issues as small as possible, AMS can identify these issues and alert based on threshold breaches automatically. This early detection of failures can prevent system degradation and reliability decreases for the SAP systems, especially during critical periods like Finance period closes and others.
SAP Systems and their underlying databases are very important for many customers. Latest release of AMS comes with a feature that Customers can monitor their Microsoft SQL Server databases.
With Azure Monitor for SAP Solutions (AMS), customers can add a provider type “Microsoft SQL Server”, this provider type enables “SAP on Azure” customers to monitor their Microsoft SQL Server SAP system database. The solution also allows for easy creation of custom visualizations and custom alerting, this new provider type ships with default visualizations that can either be used out of the box or extended to meet your requirements.
Pre-Requisite steps to onboard to AMS Microsoft SQL Server Provider
Port
The Windows port the SQL Server is using for connections (default is 1433) should be opened in the local firewall of the SQL Server machine and the Azure network security group (NSG) for the Azure network the SQL Server and the "Azure Monitor for SAP Solutions" are placed in.
SQL Server
The SQL Server must be configured with mixed authentication mode, means it must accept the login from Windows Logins as well as SQL Server logins. You can find this option in the SQL Server Management Studio -> Server Properties -> Security -> Authentication -> SQL Server and Windows authentication mode. A SQL Server restart is required after changing this option.
SQL Server Login and User
A SQL Server Login and User should be created, using the following script. Please ensure to replace <Database to monitor> with your SAP database name (e.g. PRD) and <password> with the real password of the login. The example login and user AMS can be used or replaced with any other SQL login and username of your choice:
USE [<Database to monitor>]
DROP USER [AMS]
GO
USE [master]
DROP USER [AMS]
DROP LOGIN [AMS]
GO
CREATE LOGIN [AMS] WITH
PASSWORD=N'<password>',
DEFAULT_DATABASE=[<Database to monitor>],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
CREATE USER AMS FOR LOGIN AMS
ALTER ROLE [db_datareader] ADD MEMBER [AMS]
ALTER ROLE [db_denydatawriter] ADD MEMBER [AMS]
GRANT CONNECT TO AMS
GRANT VIEW SERVER STATE TO AMS
GRANT VIEW ANY DEFINITION TO AMS
GRANT EXEC ON xp_readerrorlog TO AMS
GO
USE [<Database to monitor>]
CREATE USER [AMS] FOR LOGIN [AMS]
ALTER ROLE [db_datareader] ADD MEMBER [AMS]
ALTER ROLE [db_denydatawriter] ADD MEMBER [AMS]
GO
Microsoft SQL Server telemetry data is collected by configuring a Microsoft SQL Server provider within AMS. As part of configuring the provider, customers need to provide the following information:
- The IP address or hostname of the Microsoft SQL Server database host
- The Windows port number of the Microsoft SQL Server database system (default 1433)
- The Microsoft SQL Server username and password
- The SAP System ID (e.g. PRD)
How Microsoft SQL Server Telemetry data is captured
The provider collects data from every 60 seconds up to every hour from the SQL Server database system. Therefor the AMS Azure functions connect to the Microsoft SQL Server system using a Microsoft ODBC driver to read the telemetry data directly from the SQL Server system tables and Management Views.
In public preview, you can expect to see the following data with Microsoft SQL Server provider:
- Overall System and Resource Usage
- Buffer pool Usage
- Top 20 expensive requests
- Backup Information
- Error Conditions
- SQL Server AlwaysOn Monitoring information
- and many more ..
Further, you can expect following alert templates with this release:
- High blocking time - Fired when a blocking situation last longer than the defined threshold.
- High CPU usage percent - Fired when a MS SQL Server instance CPU utilization percentage exceeds the specified threshold.
- High IO read time - Fired when the IO read time for a MS SQL Server instance reaches the defined threshold.
- High IO write time - Fired when the IO write time for a MS SQL Server instance reaches the defined threshold.
- Low page life expectancy - Fired when the page life expectancy for a MS SQL Server instance drops below the threshold.
Asks & Feedback:
AMS links: