Workload Manager for Microsoft SQL Server uses Google Cloud's Agent for SQL Server to detect and collect metadata for evaluating your SQL Server configurations. The Agent for SQL Server is required on all VMs that run SQL Server.
This document shows how to install, configure, and verify Google Cloud's Agent for SQL Server.
Prerequisites for installing the agent
Before you install Agent for SQL Server, you need to ensure that the following prerequisites are met along with creating a SQL Server workload evaluation.
Grant IAM roles to the service account
Google Cloud's Agent for SQL Server uses the Identity and Access Management (IAM) service account attached to the VM for authentication with Google Cloud and for permission to access Google Cloud resources. For the Workload Manager validation metrics collection, use a new service account that includes the following IAM roles:
- Compute Viewer (
roles/compute.viewer
) - Secret Manager Secret Accessor (
roles/secretmanager.secretAccessor
) - Workload Manager Admin (
roles/workloadmanager.admin
) - Logs Writer (
roles/logging.logWriter
), if you use Cloud Logging feature
To add a required role to your service account, follow these steps:
In the Google Cloud console, go to the IAM page.
Select your Google Cloud project.
Identify the service account to which you want to add a role.
- If the service account isn't already on the principals list, then it doesn't have any roles assigned to it. Click Add and enter the email address of the service account.
- If the service account is already on the principals list, then it has existing roles. Click the Edit button for the service account that you want to edit.
Select the required role from the list of available roles:
- Compute Engine > Compute Viewer
- Secret Manager > Secret Manager Secret Accessor
Click Add or Save to apply the roles to the service account.
Enable access to Google Cloud APIs
Compute Engine recommends configuring your VM instances to allow all access scopes to all Cloud APIs and using only the IAM permissions of the instance service account to control access to Google Cloud resources. For more information, see Create a VM that uses a user-managed service account.
If you do limit access to the Cloud APIs, then Agent for SQL Server requires the following minimum Cloud API access scopes on the host VM instance:
https://www.googleapis.com/auth/cloud-platform
For more information, see Scopes best practice.
If you are running SQL Server applications on a VM instance that doesn't have an external IP address, then you need to enable Private Google Access on the VM's subnet so that Agent for SQL Server can access Google APIs and services. To learn how to enable Private Google Access on a subnet, see Configuring Private Google Access.
Required permissions on SQL Server
Use the following script to assign required permissions to the user account configured in the agent.
USE [master] GO
GRANT VIEW SERVER STATE TO [user_name] GRANT VIEW ANY DEFINITION TO [user_name]
-- Adds db_datareader role to the user EXEC sp_MSForEachDB ' USE ? IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''user_name'') BEGIN CREATE USER [user_name] FOR LOGIN [user_name] END EXEC sp_addrolemember ''db_datareader'', ''user_name'' '
Install the agent
Windows
On Windows, you install Agent for SQL Server by using the GooGet package
management command, googet
. The package management command completes the
following tasks:
- Downloads the latest version of Agent for SQL Server.
- Creates a Windows service named
google-cloud-sql-server-agent
and a scheduled task that runs every minute to check if the service is still running and, if necessary, restart it.
To install the agent on a VM, follow these steps:
- Connect to the Windows VM instance using RDP.
- As an administrator, run the following commands from PowerShell:
googet addrepo google-cloud-sql-server-agent-windows https://packages.cloud.google.com/yuck/repos/google-cloud-sql-server-agent-windows googet install google-cloud-sql-server-agent
- Review the configuration file located under
%ProgramFiles%\Google\google-cloud-sql-server-agent
and update thecredential_configuration
section using the details in Configuration properties. - Restart the agent for this change to take effect.
Linux
On Linux, you install Agent for SQL Server by using the standard operating system package management commands:
- On RHEL, use the
yum
command - On SLES, use the
zypper
command
The package management command completes the following tasks:
- Downloads the latest version of Agent for SQL Server.
- Creates the agent as a Linux
systemd
service, namedgoogle-cloud-sql-server-agent
. - Enables and starts the service,
google-cloud-sql-server-agent
.
To install the agent on a VM, follow these steps:
- Establish an SSH connection to your host VM.
- In your terminal, install the agent by running the command that is specific to your operating system:
RHEL
sudo tee /etc/yum.repos.d/google-cloud-sql-server-agent.repo << EOM [google-cloud-sql-server-agent] name=Google Cloud Agent for SQL Server baseurl=https://packages.cloud.google.com/yum/repos/google-cloud-sql-server-agent-el$(cat /etc/redhat-release | cut -d . -f 1 | tr -d -c 0-9) enabled=1 gpgcheck=0 repo_gpgcheck=1 gpgkey=https://packages.cloud.google.com/yum/doc/yum-key.gpg https://packages.cloud.google.com/yum/doc/rpm-package-key.gpg EOM sudo yum install google-cloud-sql-server-agent
SLES 15
sudo zypper addrepo --refresh https://packages.cloud.google.com/yum/repos/google-cloud-sql-server-agent-sles15 google-cloud-sql-server-agent sudo zypper install google-cloud-sql-server-agent
SLES 12
sudo zypper addrepo --refresh https://packages.cloud.google.com/yum/repos/google-cloud-sql-server-agent-sles12 google-cloud-sql-server-agent sudo zypper install google-cloud-sql-server-agent
- Review the configuration file located under
\etc\google-cloud-sql-server-agent
and update thecredential_configuration
section using the details in Configuration properties. - Restart the agent for this change to take effect.
Configuration properties
The following table explains the properties for the agent's configuration file.
Properties | |
---|---|
collection_configuration.collect_guest_os_metrics |
To enable OS metrics collection, set the value as |
collection_configuration.guest_os_metrics_collection_interval_in_seconds |
Workload Manager OS metrics collection frequency in seconds.
Default is |
collection_configuration.collect_sql_metrics |
To enable SQL Server metrics collection, specify |
collection_configuration.collect_sql_metrics_collection_interval_in_seconds |
Workload Manager SQL Server metrics collection frequency in seconds.
Default is |
log_level |
INFO . Don't change the logging level
unless you are directed by Cloud Customer Care.
|
log_to_cloud |
To redirect the agent's logs to Cloud Logging,
specify |
disable_log_usage |
To disable agent health metrics logging specify |
remote_collection |
Specify |
credential_configuration[].sql_configurations[].user_name |
Specify the user account that is used to query the SQL Server instance. To configure account permissions, review the permissions required in the permission script and grant them according to your internal policies. Note: If you are using Windows Authentication, make sure to specify the username in
the following format:
|
credential_configuration[].sql_configurations[].host |
The SQL Server hostname. |
credential_configuration[].sql_configurations[].secret_name |
To securely provide the password for the database user account that the agent uses to query SQL Server, specify the name of the secret in Secret Manager that contains the security credentials for the database user account. Note: The secret and the host VM instance must exist in the same Google Cloud project. |
credential_configuration[].sql_configurations[].port_number |
Specify the port on which your SQL Server instance accepts queries. |
credential_configuration[].remote_win.server_name |
IP address or FQDN of the remote VM |
credential_configuration[].remote_win.guest_user_name |
Specify the user account that is used to remotely connect to the VM. |
credential_configuration[].remote_win.guest_secret_name |
To securely provide the password for the user account that the agent uses to remotely connect to the VM, specify the name of the secret in Secret Manager that contains the security credentials for the user account. Note: The secret and the host VM instance must exist in the same Google Cloud project. |
credential_configuration[].remote_linux.server_name |
IP address or FQDN of the remote VM |
credential_configuration[].remote_linux.guest_user_name |
Specify the user account that is used to remotely connect to the VM. |
credential_configuration[].remote_linux.guest_port_number |
Specify the SSH port number for the remote VM. |
credential_configuration[].remote_linux.linux_ssh_private_key_path |
Path to SSH private key file. |
credential_configuration[].instance_name |
Specify the name of your Compute Engine VM instance. Note: Optional for local collection. |
credential_configuration[].instance_id |
Specify the ID of your Compute Engine VM instance. Note: Optional for local collection. |
The following example shows a configuration file for Agent for SQL Server:
Local Collection
{ "collection_configuration": { "collect_guest_os_metrics":true, "guest_os_metrics_collection_interval_in_seconds":3600, "collect_sql_metrics":true, "sql_metrics_collection_interval_in_seconds":3600 }, "credential_configuration": [ { "sql_configurations": [ { "host":".", "user_name":"db_user_name", "secret_name":"db_pwd_secret_name", "port_number":1433 } ], } ], "log_level":"INFO", "log_to_cloud":true, "collection_timeout_seconds":10, "max_retries":5, "retry_interval_in_seconds":3600 }
Remote Collection
{ "collection_configuration": { "collect_guest_os_metrics":true, "guest_os_metrics_collection_interval_in_seconds":3600, "collect_sql_metrics":true, "sql_metrics_collection_interval_in_seconds":3600 }, "credential_configuration": [ { "sql_configurations": [ { "host":"sql_server_instance", "user_name":"db_user_name", "secret_name":"db_pwd_secret_name", "port_number":1433 } ], "remote_win":{ "server_name":"sql_server_instance", "guest_user_name":"user_name", "guest_secret_name":"pwd_secret_name" }, "instance_name":"db01", "instance_id":"9999999999999999999" } ], "remote_collection":true, "log_level":"INFO", "log_to_cloud":true, "collection_timeout_seconds":10, "max_retries":5, "retry_interval_in_seconds":3600 }
Verify agent installation
Windows
- Connect to the Windows VM instance using RDP.
Run the following command from PowerShell as an administrator:
$(Get-Service -Name 'google-cloud-sql-server-agent' -ErrorAction Ignore).Status
If the agent is running, then the status shows
Running
.
Linux
- Establish an SSH connection with your host VM instance.
Run the following command:
systemctl status google-cloud-sql-server-agent
If the agent is functioning properly, then the output contains
active (running)
. For example:google-cloud-sql-server-agent.service - Google Cloud Agent for SQL Server Loaded: loaded (/usr/lib/systemd/system/google-cloud-sql-server-agent.service; enabled; vendor preset: disabled) Active: active (running) since Sun 2023-12-31 18:59:12 UTC; 10s ago Main PID: 14412 (google_cloud_sq) Tasks: 7 Memory: 12.9M (max: 1.0G limit: 1.0G available: 1011.0M) CGroup: /system.slice/google-cloud-sql-server-agent.service └─ 14412 /usr/bin/google_cloud_sql_server_agent --action=run
Check the version of the agent
To check the version of your agent, complete the following steps:
Windows
- Use RDP to connect to the host machine.
- As an administrator, run the following command from PowerShell:
googet installed google-cloud-sql-server-agent
RHEL
- Use SSH to connect to the host machine.
- Run the following command:
yum info google-cloud-sql-server-agent
SUSE
- Use SSH to connect to the host machine.
- Run the following command:
zypper info google-cloud-sql-server-agent
Restart the agent
If Agent for SQL Server stops working or you update its configuration, then restart the agent.
Select your operating system, and then follow the steps:
Windows
- Use RDP to connect to the host machine.
- As an administrator, run the following command from PowerShell:
Restart-Service -Name 'google-cloud-sql-server-agent' -Force
Linux
- Use SSH to connect to the host machine.
- Run the following command:
sudo systemctl restart google-cloud-sql-server-agent
Update the agent
To ensure that you have the latest version of the agent, you need to check for updates periodically and update the agent.
Check for updates
Select your operating system, and then follow the steps:
Windows
- Use RDP to connect to the host machine.
- As an administrator, run the following command from PowerShell:
googet latest google-cloud-sql-server-agent
RHEL
- Use SSH to connect to the host machine.
- Run the following command:
sudo yum check-update google-cloud-sql-server-agent
SLES
- Use SSH to connect to the host machine.
- Run the following command:
sudo zypper list-updates -r google-cloud-sql-server-agent
Install an update
Select your operating system, and then follow the steps:
Windows
- Use RDP to connect to the host machine.
- As an administrator, run the following command from PowerShell:
googet install google-cloud-sql-server-agent
RHEL
- Use SSH to connect to the host machine.
- Run the following command:
sudo yum --nogpgcheck update google-cloud-sql-server-agent
SLES
- Use SSH to connect to the host machine.
- Run the following command:
sudo zypper --no-gpg-checks update google-cloud-sql-server-agent
View the agent's logs in Cloud Logging
By default, the logs for Agent for SQL Server are redirected from your VM instances to Cloud Logging.
To view the agent's logs in Logging, follow these steps:
In the Google Cloud console, go to the Logs Explorer page.
Go the Query pane.
From the Resources drop-down menu, select Global, and then click Apply.
In the query editor, enter
google-cloud-sql-server-agent
.Click Run query.
You should see the logs generated by the agent instances running on all your VM instances. To filter the logs from a specific machine, use the filters available in the interface.
Disable the agent's logs in Cloud Logging
To disable the default redirection of agent's logs to Cloud Logging, follow these steps:
Establish an RDP or SSH connection with your host VM instance.
Open the agent's configuration file:
Windows
%ProgramFiles%\Google\google-cloud-sql-server-agent\configuration.json
Linux
/etc/google-cloud-sql-server-agent/configuration.json
For the property
log_to_cloud
, update the value tofalse
.Save the configuration file.
Restart the agent for this change to take effect.
Troubleshooting
The following sections provide information about common issues related to using Agent for SQL Server, their causes and resolution.
Insufficient authentication scopes
Issue: If you limit the access scopes on your host VM instance, then Agent for SQL Server logs might show insufficient IAM permissions error.
googleapi: Error 403: Request had insufficient authentication scopes. Details: [ { "@type": "type.googleapis.com/google.rpc.ErrorInfo", "domain": "googleapis.com", "metadata": { "method": "google.cloud.workloadmanager.datawarehouse.v1.DataCollectService.WriteInsight", "service": "workloadmanager.googleapis.com" }, "reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT" } ]More details: Reason: insufficientPermissions, Message: Insufficient Permission
Cause: Agent for SQL Server requires minimum Cloud API access scopes on the host VM instance.
Resolution: To resolve this issue, enable the required access scopes
Failed to load configuration file
Issue: If the configuration file has invalid values, you will see the following error.
"Failed to load configuration","pid":3524,"error":"proto: (line 19:42): unknown field "{field_name}"
Resolution: To resolve this issue, update the configuration file using the details in Configuration properties
Failed to initialize data collection
Issue: After installing the agent if the configuration file is not updated, you will see the following error:
"Failed to initialize guest collection","pid":2112,"error":"invalid value for "user_name" "secret_name"
Resolution: To resolve this issue, initialize the credential configuration using Configuration properties
What's next
- Learn more about workload evaluations.