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:

Caution: Using the Compute Engine default service account for the Workload Manager validation metrics collection and giving the account access to Secret Manager significantly increases the security risk. Instead, we recommend that you create a single-purpose service account and limit user's shell access to VMs.

• 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:

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.

Warning: Don't stop the Windows service manually or delete the scheduled task.

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 OS metrics collection, specify 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.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.
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

• Learn more about workload evaluations.