Set up the agent for Microsoft SQL Server

Workload Manager for Microsoft SQL Server uses Google Cloud's Agent for Compute Workloads to detect and collect metadata for evaluating your SQL Server configurations.

This document shows how to install, configure, and verify Agent for Compute Workloads on Compute Engine instances that run SQL Server.

Prerequisites for installing the agent

Before you install Agent for Compute Workloads, 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 Compute Workloads 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 Compute Workloads 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 Compute Workloads 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

Note: If the legacy Agent for SQL Server is already installed on the VM, during the installation process, Agent for Compute Workloads automatically migrates the agent configuration file to the new location and deletes Agent for SQL Server from the VM.

To install the latest version of the Agent for Compute Workloads, complete the following steps:

Windows

On Windows, you install Agent for Compute Workloads by using the GooGet package management command, googet. The package management command completes the following tasks:

• Downloads the latest version of Agent for Compute Workloads.
• Creates a Windows service named google-cloud-workload-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-workload-agent  https://packages.cloud.google.com/yuck/repos/google-cloud-workload-agent-windows-x86_64
   googet install google-cloud-workload-agent
   

3. Review the configuration file located under %ProgramFiles%\Google\google-cloud-workload-agent\conf and update the configuration file using the details in Configuration properties.
4. Restart the agent for this change to take effect.

Linux

On Linux, you install Agent for Compute Workloads by using the standard operating system package management commands:

• On RHEL, use the yum command
• On SLES, use the zypper command
• On Debian, use the apt command

The package management command completes the following tasks:

• Downloads the latest version of Agent for Compute Workloads.
• Creates the agent as a Linux systemd service, named google-cloud-workload-agent.
• Enables and starts the service, google-cloud-workload-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-workload-agent.repo << EOM
[google-cloud-workload-agent]
name=Google Cloud Agent for Compute Workloads
baseurl=https://packages.cloud.google.com/yum/repos/google-cloud-workload-agent-\$basearch
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-workload-agent

SLES

sudo zypper addrepo --refresh https://packages.cloud.google.com/yum/repos/google-cloud-workload-agent-\$basearch  google-cloud-workload-agent
sudo zypper install google-cloud-workload-agent

Debian

echo 'deb https://packages.cloud.google.com/apt google-cloud-workload-agent-x86-64-apt main' | sudo tee -a /etc/apt/sources.list.d/google-cloud-workload-agent.list \
sudo apt-get update \
sudo apt-get install google-cloud-workload-agent

1. Review the configuration file located under \etc\google-cloud-workload-agent and update the configuration file using the details in Configuration properties.
2. Restart the agent for this change to take effect.

Configuration properties

The following table describes the properties for the agent's configuration file.

Properties
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. Default is true.
common_discovery.collection_frequency	Duration Collection frequency for the common discovery service, in seconds. The default value is 10800s.
agent_properties.log_usage_metrics	Boolean To enable agent health metrics logging, set the value as true. Default is false.
sqlserver_configuration.enabled	Boolean To enable SQL Server metrics collections within the agent, set the value as true. Default is false. Note: For other SQL Server agent-specific configuration parameters to take effect, set sqlserver_configuration.enabled to true.
sqlserver_configuration.collection_configuration.collect_guest_os_metrics	Boolean To enable OS metrics collection, set the value as true. Default is true. Don't set sqlserver_configuration.collection_configuration.collect_guest_os_metrics to false unless you are directed by Cloud Customer Care.
sqlserver_configuration.collection_configuration.collect_sql_metrics	Boolean To enable SQL Server metrics collection, specify true. Default is true. Don't set sqlserver_configuration.collection_configuration.collect_sql_metrics to false unless you are directed by Cloud Customer Care.
sqlserver_configuration.collection_configuration.collection_frequency	Duration Agent for Compute Workloads metrics collection frequency, in seconds. Default is 3600s. You can update the collection frequency. However, we recommend keeping the default value.
sqlserver_configuration.credential_configurations[].connection_parameters[].host	String The SQL Server hostname.
sqlserver_configuration.credential_configurations[].connection_parameters[].username	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
sqlserver_configuration.credential_configurations[].connection_parameters[].secret.project_id	String The ID of the project where the secret is stored. Set it to an empty string ("") if the secret and the host VM instance exist in the same Google Cloud project.
sqlserver_configuration.credential_configurations[].connection_parameters[].secret.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.
sqlserver_configuration.credential_configurations[].connection_parameters[].port	Int Specify the port on which your SQL Server instance accepts queries.
sqlserver_configuration.credential_configurations[].remote_win.connection_parameters.host	String IP address or FQDN of the remote Windows VM
sqlserver_configuration.credential_configurations[].remote_win.connection_parameters.username	String Specify the user account that is used to remotely connect to the Windows VM.
sqlserver_configuration.credential_configurations[].remote_win.connection_parameters.secret.secret_name	String To securely provide the password for the Windows 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 database user account. Note: The secret and the host VM instance must exist in the same Google Cloud project.
sqlserver_configuration.credential_configurations[].local_collection	Boolean Specify true to indicate that the agent is doing local data collection. Default is true.
sqlserver_configuration.credential_configurations[].remote_linux.connection_parameters.host	String IP address or FQDN of the remote Linux VM.
sqlserver_configuration.credential_configurations[].remote_linux.connection_parameters.username	String Specify the user account that is used to remotely connect to the Linux VM.
sqlserver_configuration.credential_configurations[].remote_linux.connection_parameter.port	Int Specify the SSH port number for the remote Linux VM.
sqlserver_configuration.credential_configurations[].remote_linux.linux_ssh_private_key_path	String Specify the path to the SSH private key file.
sqlserver_configuration.credential_configurations[].vm_properties.instance_name	String Specify the name of your Compute Engine VM instance. Note: Optional for local collection.
sqlserver_configuration.credential_configurations[].vm_properties.instance_id	String Specify the ID of your Compute Engine VM instance. Note: Optional for local collection.
sqlserver_configuration.collection_timeout	Duration The timeout for metrics collection, in seconds. Default is `10s`.
sqlserver_configuration.max_retries	Int The maximum number of retries when a failed collection occurs. The default value is `3`.
sqlserver_configuration.retry_frequency	Duration Specify the frequency at which the agent should retry when a collection fails. The default value is `3600s`.
sqlserver_configuration.remote_collection	Boolean Specify true to indicate that the agent is doing remote data collection. The default is false.

The following example shows a configuration file for Agent for Compute Workloads:

Local Collection

{
"log_level": "INFO",
"common_discovery": {
    "collection_frequency": "10800s"
},
"sqlserver_configuration": {
    "enabled": true,
    "collection_configuration": {
        "collect_guest_os_metrics": true,
        "collect_sql_metrics": true,
        "collection_frequency": "60s"
    },
    "credential_configurations": [
        {
            "connection_parameters": [
                {
                    "host": ".",
                    "username": "db_user_name",
                    "secret": {
                        "project_id": "",
                        "secret_name": "idb_pwd_secret_name"
                    },
                    "port": 1433
                }
            ],
            "local_collection": true
        }
    ],
    "collection_timeout": "60s",
    "max_retries": 5,
    "retry_frequency": "3600s"
}
}

Remote Collection

{
"log_level": "INFO",
"common_discovery": {
    "collection_frequency": "10800s"
},
"sqlserver_configuration": {
    "enabled": true,
    "collection_configuration": {
        "collect_guest_os_metrics": true,
        "collect_sql_metrics": true,
        "collection_frequency": "60s"
    },
    "credential_configurations": [
        {
            "connection_parameters": [
                {
                    "host": "sql_server_instance",
                    "username": "db_user_name",
                    "secret": {
                        "project_id": "",
                        "secret_name": "db_pwd_secret_name"
                    },
                    "port": 1433
                }
            ],
            "remote_win": {
                "connection_parameters": {
                    "host": "sql_server_instance",
                    "username": "user_name",
                    "secret": {
                        "project_id": "",
                        "secret_name": "pwd_secret_name"
                    }
                }
            },
            "vm_properties": {
                "instance_name": "db01",
                "instance_id": "9999999999999999999"
            }
        },
        {
            "connection_parameters": [
                {
                    "host": "sql_server_instance",
                    "username": "db_user_name",
                    "secret": {
                        "project_id": "",
                        "secret_name": "db_pwd_secret_name"
                    },
                    "port": 1433
                }
            ],
            "remote_linux": {
                "connection_parameters": {
                    "host": "sql_server_instance",
                    "username": "user_name",
                    "secret": {
                        "project_id": "",
                        "secret_name": "pwd_secret_name"
                    },
                    "port": 22
                },
                "linux_ssh_private_key_path": "path of the private key"
            },
            "vm_properties": {
                "instance_name": "db02",
                "instance_id": "9999999999999999999"
            }
        }
    ],
    "collection_timeout": "10s",
    "max_retries": 3,
    "retry_frequency": "3600s",
    "remote_collection": true
}
}

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-workload-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-workload-agent

   If the agent is functioning properly, then the output contains active (running). For example:

   google-cloud-workload-agent.service - Google Cloud Agent for Compute Workloads
   Loaded: loaded (/usr/lib/systemd/system/google-cloud-workload-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-workload-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-workload-agent

RHEL

1. Use SSH to connect to the host machine.
2. Run the following command:

   yum info google-cloud-workload-agent

SUSE

1. Use SSH to connect to the host machine.
2. Run the following command:

   zypper info google-cloud-workload-agent

Debian

1. Use SSH to connect to the host machine.
2. Run the following command:

   dpkg -s google-cloud-workload-agent | grep version

Restart the agent

If Agent for Compute Workloads 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-workload-agent' -Force

Linux

1. Use SSH to connect to the host machine.
2. Run the following command:

   sudo systemctl restart google-cloud-workload-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-workload-agent

RHEL

1. Use SSH to connect to the host machine.
2. Run the following command:

   sudo yum check-update google-cloud-workload-agent

SLES

1. Use SSH to connect to the host machine.
2. Run the following command:

   sudo zypper list-updates -r google-cloud-workload-agent

Debian

1. Use SSH to connect to the host machine.
2. Run the following command:

   sudo apt list google-cloud-workload-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-workload-agent

RHEL

1. Use SSH to connect to the host machine.
2. Run the following command:

   sudo yum --nogpgcheck update google-cloud-workload-agent

SLES

1. Use SSH to connect to the host machine.
2. Run the following command:

   sudo zypper --no-gpg-checks update google-cloud-workload-agent

Debian

1. Use SSH to connect to the host machine.
2. Run the following command:

   sudo apt-get install google-cloud-workload-agent

View the agent's logs in Cloud Logging

By default, the logs for Agent for Compute Workloads 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-workload-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-workload-agent\conf\configuration.json

   Linux

   /etc/google-cloud-workload-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 Compute Workloads, their causes and resolution.

Insufficient authentication scopes

Issue: If you limit the access scopes on your host VM instance, then Agent for Compute Workloads 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 Compute Workloads 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 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 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.