Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/workload-manager/docs/set-up-agent-for-sql-server below:

Set up the agent for Microsoft SQL Server | Workload Manager

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.

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.

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:

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

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.

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''
    '
  

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

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.

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.

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:

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

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

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.

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

String

• DEBUG
• INFO
• WARNING
• ERROR

Boolean

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

Duration

Collection frequency for the common discovery service, in seconds.
The default value is 10800s.

Boolean

To enable agent health metrics logging, set the value as true. Default is false.

Boolean

To enable SQL Server metrics collections within the agent, set the value as true. Default is false.

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.

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.

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.

String

The SQL Server hostname.

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

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.

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.

Int

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

String

IP address or FQDN of the remote Windows VM

String

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

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.

Boolean

Specify true to indicate that the agent is doing local data collection. Default is true.

String

IP address or FQDN of the remote Linux VM.

String

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

Int

Specify the SSH port number for the remote Linux VM.

String

Specify the path to the SSH private key file.

String

Specify the name of your Compute Engine VM instance.

Note: Optional for local collection.

String

Specify the ID of your Compute Engine VM instance.

Note: Optional for local collection.

Duration

The timeout for metrics collection, in seconds. Default is `10s`.

Int

The maximum number of retries when a failed collection occurs. The default value is `3`.

Duration

Specify the frequency at which the agent should retry when a collection fails. The default value is `3600s`.

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:

{
"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"
}
}

{
"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
}
}

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.

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
   

To check the version of your agent, complete the following steps:

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

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

   yum info google-cloud-workload-agent

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

   zypper info google-cloud-workload-agent

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

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

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:

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

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

   sudo systemctl restart google-cloud-workload-agent

To ensure that you have the latest version of the agent, you need to check for updates periodically and update the agent.

Select your operating system, and then follow the steps:

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

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

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

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

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

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

   sudo apt list google-cloud-workload-agent

Select your operating system, and then follow the steps:

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

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

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

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

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

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

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

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.

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.

The following sections provide information about common issues related to using Agent for Compute Workloads, their causes and resolution.

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

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

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

• Learn more about workload evaluations.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4