SQL server

Prerequisites

SQL Server 2012, 2014, 2016, 2017, 2019, or 2022
A user account with SQL permissions including Connect SQL and View Server State on the database
The Net.Tcp Port Sharing Service and Net.Tcp Listener Adapter services must be running, and set to enabled
TCP/IP is enabled on the port used for Turbonomic discovery

Creating a service user account

The user account that Turbonomic uses for its service login must include the following:

The account must exist in the Security folder within the SQL Server Object Explorer, with the following property:
- Enable SQL Server Authentication
The account's security properties must include:
- Permission to connect to the database through SQL
- Permission to view the server state

Adding a SQL server target

Click Settings > Target configuration.
On the Target configuration page, click Add Target.
On the Select target page, click SQLServer.
In the side panel, review the connection requirements and then click Connect Target.
On the Connection overview page, configure the following settings:
- Display name
  
  Specify a name that uniquely identifies this connection.
  
  This name is for display purposes only and does not need to match any name in SQLServer.
- Username
  
  Specify the username of the account Turbonomic uses to connect to the target.
  
  Username must not include the Active Directory domain.
- Password
  
  Specify the password of the account Turbonomic uses to connect to the target.
  
  Password must not include the Active Directory domain.
- Active Directory Domain
  
  Specify the Active Directory domain used by Turbonomic in conjunction with the username for authentication. Leave blank for local accounts.
- Discovery path
  
  Specify the hostname or IP address or scope when performing the discovery process.
  
  Turbonomic discovers SQL instances through the SQL Server's hostname/IP address or your selected scope.
  
  If you change your selection, the value associated with the deselected option is automatically removed.
  - Hostname or IP address
    
    Specify the hostname or IP address of your MSSQL environment configuration. Turbonomic scans the targeted hostname or IP address and tries to connect to the target using the specified port. Turbonomic adds any instances of the target it finds as entities from which metrics are retrieved.
  - Scope
    
    Specify the scope Turbonomic uses for application discovery.
    
    The scope is a group of applications that are stitched to the underlying VMs when the VMs are discovered as part of a separate Turbonomic target.
    
    If you set a scope, Turbonomic searches for virtual machines in the selected group.
    
    Turbonomic can monitor up to 500 virtual machines in a group. If you have more than 500 virtual machines in your environment, split them across smaller groups and then add those groups as individual targets.
- Browsing service port
  
  Specify the MS SQL Browsing Service port for the browsing service that listens for incoming connections to the SQL instances running on the SQL Server. This port overrides the SQLServer port. By default, the port is 1434.
  
  If the browsing service is reachable via the specified port, Turbonomic discovers the SQL instances used by the VM group that you defined as your scope, as well as the listening ports on those SQL instances.
  
  If the service is unreachable, or if you did not specify a MS SQL Browsing Service port, Turbonomic uses the TCP port that you specified in the SQLServer Port field to discover SQL instances.
- SQL Server port
  
  Specify the TCP port of the MS SQL Server. By default, the port is 1433.
  
  Turbonomic uses this port if the browsing service is unreachable, or if you did not specify a browsing service port.
- Authenticate all database servers
  
  If you select this option, Turbonomic attempts to authenticate all database servers in the selected scope. If Turbonomic is unable to authenticate a database server, the target is not added and no data is collected.

Standalone and clustered SQL servers

Turbonomic discovers both standalone and clustered SQL Servers, and represents them as Database Server entities in the supply chain.

When you set the scope to a SQL Server entity and view the Entity Information chart, the Server Configuration field indicates whether that entity is a standalone server or is part of a cluster.
When you search for Database Servers or create groups of Database Servers, use the Server Configuration filter to get a list of standalone or clustered servers.

For clustered environments:

Turbonomic represents each SQL Server instance in the cluster as a Database Server entity, and automatically creates a group for these instances.
To see all the auto-created groups, go to Settings > Groups and then search for group names starting with MSSQL:Cluster:. Click a group name to set the scope to that group. In the resulting supply chain, the Database Server entity shows the number of SQL Server instances in the cluster. This entity is stitched to the Virtual Machine entity, which represents the corresponding SQL Server nodes. Click Database Server to see a list of instances, and identify which instance is currently active and which ones are idle (redundant). Turbonomic only monitors resources for the active instance, and shows resource metrics when you set the scope to that instance.
If you have several clusters, you can use filters to identify all the active/idle instances in those clusters. In Search, select Database Servers, and then set the filter as follows:
- Active instances
  
  Server Configuration=Clustered and State=ACTIVE
- Idle (redundant) instances
  
  Server Configuration=Clustered and State=IDLE

Updating SQL server database passwords in bulk

You can update SQL server database passwords in bulk by uploading a CSV file through the Turbonomic user interface (UI). Use this feature to regularly rotate passwords for multiple SQL server targets without having to manually update each target.

The bulk update process uses a CSV file that you generate in the Turbonomic UI, download and edit locally, then upload to Turbonomic. Complete the following steps.

Click Settings > Target configuration.
On the Target configuration page, filter the list of targets to include only your SQL server targets.
1. Click the Filter icon to create a search filter.
2. Click Add criteria, and select the Type filter.
3. Set the value to be SQLServer.
4. Click Save and apply to create the filter and filter the list of targets.
Create the CSV file.
1. Click the dropdown menu on the Add Target button and select Download file for bulk update.
2. In the Select target type window, select SQLServer and click Next.
3. In the Select scope window, select the SQL server targets that you want to update and click Next.
  
  By default, all SQL server targets are selected.
4. Click Download file (CSV).
Open the CSV file that you downloaded and update the password column with the new password. Save the changes.
Upload the updated CSV file to Turbonomic.
1. On the Target configuration page, click the dropdown menu on the Add Target button and select Upload file for bulk update.
2. Drag the updated file into the Upload file window or select it from your file browser.
  
  If the file is invalid, an error notification appears, along with a window that details the errors that were found. Fix any errors and repeat steps 5a and 5b. When the file passes the validation check, the Submit button is available.
3. Click Submit.
  
  When the upload completes, a notification appears that confirms the passwords were updated.

Monitored resources

Turbonomic monitors the following resources:

Database server

Note:
For clustered environments, Turbonomic only monitors resources for the currently active SQL Server instance, and shows resource metrics when you set the scope to that instance.
- Connection
  
  Connection is the measurement of database connections utilized by applications.
  
  A database connection is a physical communication pathway that holds database sessions, which are logical entities in the database instance memory that represent the state of a current user login to a database. Connections should be managed properly.
- Database memory (DBMem)
  
  Database memory (or DBMem) is the measurement of memory that is utilized by a Database Server.
  
  Actions to resize database memory are driven by data on the Database Server, which is more accurate than data on the hosting VM.
- DB cache hit rate
  
  DB cache hit rate is the measurement of Database Server accesses that result in cache hits, measured as a percentage of hits versus total attempts. A high cache hit rate indicates efficiency.
- Response time
  
  Response time is the elapsed time between a request and the response to that request. Response time is typically measured in seconds (s) or milliseconds (ms).
- Storage access (IOPS)
  
  Storage access, also known as IOPS, is the per-second measurement of read and write access operations on a storage entity.
- Storage amount
  
  Storage amount is the measurement of storage capacity that is in use.
- Transaction log
  
  Transaction log is the measurement of storage capacity utilized by a Database Server for transaction logging.
- Transaction
  
  Transaction is a value that represents the per-second utilization of the transactions that are allocated to a given entity.
- Virtual storage
  
  Virtual storage is the measurement of virtual storage capacity that is in use.
Virtual machine
- Virtual memory (vMem)
  
  Virtual memory (vMem) is the measurement of memory that is in use.
- Virtual CPU (vCPU)
  
  Virtual CPU is the measurement of CPU that is in use.
- Virtual storage
  
  Virtual storage is the measurement of virtual storage capacity that is in use.
- Storage access (IOPS)
  
  Storage access, also known as IOPS, is the per-second measurement of read and write access operations on a storage entity.

Actions

Turbonomic supports the following actions:

Database server

Resize
- Connections
  
  Turbonomic uses connection data to generate memory resize actions for on-prem Database Servers.
- Database memory (DBMem)
  
  Actions to resize database memory are driven by data on the Database Server, which is more accurate than data on the hosting VM. Turbonomic uses database memory and cache hit rate data to decide whether resize actions are necessary.
  
  A high cache hit rate value indicates efficiency. The optimal value is 100% for on-prem (self-hosted) Database Servers, and 90% for cloud Database Servers. When the cache hit rate reaches the optimal value, no action generates even if database memory utilization is high. If utilization is low, a resize down action generates.
  
  When the cache hit rate is less than the optimal value but database memory utilization remains low, no action generates. If utilization is high, a resize up action generates.
- Transaction log
  
  Resize actions based on the transaction log resource depend on support for virtual storage in the underlying hypervisor technology.
  
  Currently, Turbonomic does not support resize actions for Oracle and Database Servers on the Hyper-V platform (due to the lack of API support for virtual storage).
Virtual machine
- Provision additional resources (VMem, VCPU)
- Move Virtual Machine
- Move Virtual Machine Storage
- Reconfigure Storage
- Reconfigure Virtual Machine
- Suspend VM
- Provision VM

Note:

Without separate targets to discover Guest OS Processes or Application Servers, Turbonomic does not generate actions on applications. Instead, it generates resize actions on the host VMs. For on-prem environments, if host utilization is high enough on the host running the application VM, Turbonomic can also recommend provisioning a new host.

To retrieve the IP address and DNS name of the node inside the SQL Server cluster, Turbonomic might enable advanced tools and xp_cmdshell options and run commands against nslookup.