Set up Agent for SQL Server

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:

To add a required role to your service account, follow these steps:

  1. In the Google Cloud console, go to the IAM page.

    Go to IAM

  2. Select your Google Cloud project.

  3. 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.
  4. Select the required role from the list of available roles:

    • Compute Engine > Compute Viewer
    • Secret Manager > Secret Manager Secret Accessor
  5. 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:

  1. Connect to the Windows VM instance using RDP.
  2. 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
    
  3. Review the configuration file located under %ProgramFiles%\Google\google-cloud-sql-server-agent and update the credential_configuration section using the details in Configuration properties.
  4. 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, named google-cloud-sql-server-agent.
  • Enables and starts the service, google-cloud-sql-server-agent.

To install the agent on a VM, follow these steps:

  1. Establish an SSH connection to your host VM.
  2. 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
  1. Review the configuration file located under \etc\google-cloud-sql-server-agent and update the credential_configuration section using the details in Configuration properties.
  2. 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

Boolean

To enable OS metrics collection, set the value as true. Default is true.
Don't set collection_configuration.collect_guest_os_metrics to false unless you are directed by Cloud Customer Care.

collection_configuration.guest_os_metrics_collection_interval_in_seconds

Int

Workload Manager OS metrics collection frequency in seconds. Default is 3600 seconds. You can update the collection frequency. However, we recommend keeping the default value.

collection_configuration.collect_sql_metrics

Boolean

To enable SQL Server metrics collection, specify true. Default is true.
Don't set collection_configuration.collect_sql_metrics to false unless you are directed by Cloud Customer Care.

collection_configuration.collect_sql_metrics_collection_interval_in_seconds

Int

Workload Manager SQL Server metrics collection frequency in seconds. Default is 3600 seconds. You can update the collection frequency. However, we recommend keeping the default value.

log_level

String

To set the logging level of the agent, add the log levels. The available log levels are as follows:
  • DEBUG
  • INFO
  • WARNING
  • ERROR
Default is INFO. Don't change the logging level unless you are directed by Cloud Customer Care.
log_to_cloud

Boolean

To redirect the agent's logs to Cloud Logging, specify true. The default is true.

disable_log_usage

Boolean

To disable agent health metrics logging specify true. The default is false.

remote_collection

Boolean

Specify true to indicate that the agent is doing remote data collection. The default is false.

credential_configuration[].sql_configurations[].user_name

String

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: domain-name\\user-name

credential_configuration[].sql_configurations[].host

String

The SQL Server hostname.

credential_configuration[].sql_configurations[].secret_name

String

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

Int

Specify the port on which your SQL Server instance accepts queries.

credential_configuration[].remote_win.server_name

String

IP address or FQDN of the remote VM

credential_configuration[].remote_win.guest_user_name

String

Specify the user account that is used to remotely connect to the VM.

credential_configuration[].remote_win.guest_secret_name

String

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

String

IP address or FQDN of the remote VM

credential_configuration[].remote_linux.guest_user_name

String

Specify the user account that is used to remotely connect to the VM.

credential_configuration[].remote_linux.guest_port_number

Int

Specify the SSH port number for the remote VM.

credential_configuration[].remote_linux.linux_ssh_private_key_path

String

Path to SSH private key file.

credential_configuration[].instance_name

String

Specify the name of your Compute Engine VM instance.

Note: Optional for local collection.

credential_configuration[].instance_id

String

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

  1. Connect to the Windows VM instance using RDP.
  2. 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

  1. Establish an SSH connection with your host VM instance.
  2. 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

  1. Use RDP to connect to the host machine.
  2. As an administrator, run the following command from PowerShell:
    googet installed google-cloud-sql-server-agent

RHEL

  1. Use SSH to connect to the host machine.
  2. Run the following command:
    yum info google-cloud-sql-server-agent

SUSE

  1. Use SSH to connect to the host machine.
  2. 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

  1. Use RDP to connect to the host machine.
  2. As an administrator, run the following command from PowerShell:
    Restart-Service -Name 'google-cloud-sql-server-agent' -Force

Linux

  1. Use SSH to connect to the host machine.
  2. 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

  1. Use RDP to connect to the host machine.
  2. As an administrator, run the following command from PowerShell:
    googet latest google-cloud-sql-server-agent

RHEL

  1. Use SSH to connect to the host machine.
  2. Run the following command:
    sudo yum check-update google-cloud-sql-server-agent

SLES

  1. Use SSH to connect to the host machine.
  2. 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

  1. Use RDP to connect to the host machine.
  2. As an administrator, run the following command from PowerShell:
    googet install google-cloud-sql-server-agent

RHEL

  1. Use SSH to connect to the host machine.
  2. Run the following command:
    sudo yum --nogpgcheck update google-cloud-sql-server-agent

SLES

  1. Use SSH to connect to the host machine.
  2. 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:

  1. In the Google Cloud console, go to the Logs Explorer page.

    Go to Logs explorer

  2. Go the Query pane.

  3. From the Resources drop-down menu, select Global, and then click Apply.

  4. In the query editor, enter google-cloud-sql-server-agent.

  5. 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:

  1. Establish an RDP or SSH connection with your host VM instance.

  2. 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

  3. For the property log_to_cloud, update the value to false.

  4. Save the configuration file.

  5. 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