Monitoring GCP Cloud SQL using Dynatrace
Objective
In one of my older posts, I discussed how we can use Dynatrace to monitor kubernetes. Many clients running SQL Server on GCP Cloud SQL have a very common requirement about monitoring their business critical application databases reliably. Dynatrace is a commonly favored observability tool for cloud native and on-premises workloads. Let's see how we can leverage Dynatrace to monitor Cloud SQL instances for SQL Server.
There are two main ways to do that
- Using Google Cloud SQL extension
- Using Microsoft SQL Server extension (not limited to Cloud SQL SQL Server Instances )
I am just discussing SQL Server in this post, but you can try using the PostgreSQL or MySql extensions if you are using the Cloud SQL PostgresSQL or Cloud SQL MySql.
Using Cloud SQL extension
Dynatrace GCP integration leverages data collected from the Google Operation API to constantly monitor health and performance of Google Cloud Platform Services. While combining all relevant data into dashboards, it also enables alerting and event tracking.
Prerequisites
- You need to have a Dynatrace GCP metric and log integration enabled.
Configuration
- This uses a GKE environment to deploy a function which fetches the logs and metrics from a Pub/Sub subscription in your GCP project and pushes it to your Dynatrace environment.
- During setup, a new Pub/Sub subscription will be created. GKE will run two containers: a metric forwarder and a log forwarder. After installation, you’ll get metrics, logs, dashboards, and alerts for your configured services in Dynatrace.
- Log Router is configured to send the logs to a Pub/Sub topic.
- If you already have a GKE cluster, you may leverage that as well.
Dynatrace GCP metric and log integration by default provides observability metrics for many resources in the related GCP project.
There is no extra steps to enable Cloud SQL extension. Here is how the Google cloud SQL dashboard looks like. You can see there are 3 instances. 2 primary Instances and one replica Instance. It has useful information/metrics about the Instances, databases, and the infrastructure hosting these instances.
Major benefit I observe is the automatic discovery of the new Cloud SQL instances.
This is OK but not good enough because if you compare the metrics gathered by Cloud SQL extension versus the metrics gathered by MS SQL Server Extension, you would see Cloud SQL extension doesn’t include many useful metrics.
Besides metrics, you can ingest the Cloud SQL logs to Dynatrace using GCP log sink to the Pub/Sub topic we setup earlier. Then create alerts based on logs. Parsing logs is not very efficient or foolproof compared to metrics ingestion.
So Next, We will look into how to setup and configure Microsoft SQL Server extension.
We are just discussing SQL Server in this post, but you can try using the PostgreSQL or MySql extensions if you are using the Cloud SQL PostgresSQL or Cloud SQL MySql to use the missing metrics in Cloud SQL extension.
Using the MS SQL Server Extension
We can monitor a Microsoft SQL Server instance remotely using the ActiveGate extension as it gives much more data insights about the Cloud SQL Instances and the databases as well. This extension works for SQL Server Instances whether it is hosted on-premises or or on Public Cloud Provider. It supports both PaaS and IaaS installations for SQL Server. This covers many more metrics which the DBAs are interested in and are not not part of the Cloud SQL Extension.
Prerequisites
- Microsoft SQL Server with mainstream or extended support by Microsoft. Check the Microsoft documentation for end-of-life details.
- Database user permissions , I am using default admin account for Cloud SQL SQL Server for configuration.
- ActiveGate version should be newer than 1.242
Install ActiveGate on the VM
- You need to have ActiveGate installed on a VM.
- Navigate to “deploy Dynatrace”
- click on Install ActiveGate
- Choose “linux”
- Click on “Create token”
- Run the populated commands to download artifacts
- Run the installer script as root user
- You don't need OneAgent for this setup but its better to install oneagent because it enables you to monitor the VM where your activegate is running.
- VM should be able to connect to the target Cloud SQL Instance.
- Lets assume the VM for ActiveGate is in GCP Project B and Cloud SQL is GCP Project A. If Cloud SQL and VM are in diff VPCs, then VPC peering should exist or both resources should be in same shared VPC.
- Firewall rules should allow the connectivity from ActiveGate VM to Cloud SQL.
Why can’t we use the same ActiveGate that we use to monitor kubernetes?
Because the main modules used for MS SQL Server extensions are OTLP Ingest and HTTP Metric Ingest and OTLP is not supported in containerized ActiveGate deployments. https://www.dynatrace.com/support/help/setup-and-configuration/dynatrace-activegate/capabilities#functional_tbl
Extension activation
To install the extension
- In the Dynatrace menu, go to Dynatrace > Infrastructure > Extensions.
- Find and select Microsoft SQL Server.
- Select Add to environment.
- Configure monitoring as described below.
Monitoring configuration
The following steps describe how to configure a monitoring configuration endpoint.
- Select Add monitoring configuration.
- Choose ActiveGates that will run this monitoring configuration.
- Select Add Sql Server endpoint.
Endpoints are automatically distributed between ActiveGates within a certain chosen group. This allows for High Availability (HA) and load balancing between several ActiveGate instances.
- Specify connection parameters and credentials.
- Dynatrace offers you to use credential vault.
You can also enable SSL to make the data source verify the server certificate and use SSL encryption instead of native encryption.
Specify endpoint name and Select Activate.
If your organization uses SQL Auth Proxy for Database connections, you will need to install Auth Proxy on the VM where the ActiveGate is installed and then use the host equals “localhost” and port will be the proxy port for that instance
After being activated, the newly created endpoint acquires Pending status, followed by Error status, which indicates that the tenant has not yet found an ActiveGate to run the endpoint on.
- Check that the status of the configuration is OK.
Status Ok indicates that the configuration has passed a Fastcheck stage, verifying that it has a connection to the database and collects metrics.
- Navigate to dashboards and find “SQL Server”, default dashboard will show the Instances added to extension configuration.
You will see much more useful information in addition to what Cloud SQL Extension offers.
Here are few more Snapshots of the SQL Server Instance metrics
You can review the logins and user connections as well. You can also see the blocking sessions and processes.
You can navigate to the databases associated with the SQL Server Instance and review the metrics specifically related to that database.
Some drawbacks Of SQL server extension
- Need to configure, maintain and administer a VM hosting Dynatrace ActiveGate which adds to overhead.
- There is no auto discovery, each time a new Instance is added you need to configure it in the extension
- Same will be the case when a replica is promoted.
- Manual removal of the Instances which don’t need to monitored anymore
- No Custom queries are supported.
- Need to update password after every password rotation hence manual maintenance required.
What if you want to create an alert using a custom query against the SQL Server database?
You can use Custom Query extension (not covered in this post) for that purpose.
In nutshell, none of the above is a perfect solution but a hybrid solution using all these extensions can be used to monitor the Cloud SQL instances and databases comprehensively. In my Opinion, GCP’s out of box monitoring is more superior as compared to Dynatrace’s Cloud SQL Extension as it covers more useful metrics.
Please read my other articles as well and share your feedback. If you like the content shared please like, comment, and subscribe for new articles.
