Turbonomic supports Microsoft SQL Server 2008 R2, 2012, and 2014, from version 5.2 onwards, and 2016 SP1 in 6.0.

This document applies to versions 5.4 and newer. For previous version there is a GC article you can use:

https://greencircle.vmturbo.com/docs/DOC-1285

Target definition

To add or edit an existing SQL Server target, go to the Admin/Target Configuration panel, and fill out the SQL Server information as follows:

**To add multiple SQL instances while using the same credentials, see steps below**

Note:

Port number 1433 is the out of the box default SQL Server Port. If your instance is using another port (ex. some installs are dynamically assign a port number) change this value to the port actually used.

There are two types of authentication modes:

• SQL Server authentication. SQL Server stores both the username and a hash of the password in the master database, by using internal authentication methods to verify login attempts.
• Windows authentication often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server.

Fill the “Full Domain Name” only if using a Windows authenticated (LADP) user.

“Username” must be a present login within the Security folder in the SQL Server Object Explorer (one will need to be created if you do not have currently.)

**Create a group of SQL VMs and apply credentials for easier setup**

Create SQL VM Group

• Under "Policy -> Group Management" click the green "+" to add a new group of virtual machines
• Name the group
• In the first combo box, select "Virtual Machines"
• In the adjacent combo box, you can select either:
  • Group entities by criteria (Dynamically groups VMs by specified criteria, better if you plan on adding more)
  • Manually select entities to group (Better for multiple different names, or just looking for some, not all VMs)
• In the "By" combo box, select "Name"
• In the next combo box, select "equals"
• Lastly, through regular expressions use ".*sql.*" to find all VMs with the name sql in it.
  • .* = everything, using it before and after the string indicates that we do not care what is before or after the string containing sql
• All that is left is to add the targets in the "Admin" tab as you see above, click "Scope" and expand "My Groups", lastly, select your group.

Screenshots

The user must have read permission for the sys tables (db_datareader role in all databases defined on a server instance).

In order to verify that user setup is correct, have a DBA do the following two steps:

1. execute the query bellow in a tool like SQL Server Management Studio using the credentials for the SQL Server target:

select TOP 1 1 from sys.dm_os_performance_counters, sys.dm_exec_query_stats, sys.configurations, sys.master_files, sys.databases;

The query should be successful.

   2. execute the following statement (using the same tool as before) twice: once under sa privileges, and once using the credentials for the SQL Server target:

DBCC SQLPERF(LOGSPACE);

The results should be identical.

Tips:

Enable network services

If you are unable to connect with the above configuration, check the following:

The following services should be started and not set to disabled:

Check communication

On the VM running opsManager, run the following curl command from the prompt:

vmturbo$ curl 10.10.172.33:1433

curl: (52) Empty reply from server

(use the target’s IP and port)

The result should be like above: “Empty reply from server”, if the communication is successful.

If not, you will most likely get “Connection refused”:

vmturbo$ curl 10.10.172.33:1435

curl: (7) Failed to connect to 10.10.172.33 port 1435: Connection refused

This may happen because of invalid IP, invalid port, firewall issues among other possibilities.

Enable Remote connection

In SQL Management Studio, right-click on the server name and select Properties. Click on Connections, and in the right pane, check Allow remote connections to this server.

User creation

If creating a new SQL Login, make sure to select the SQL Server authentication and uncheck “Enforce password policy”:

Otherwise the first time the user connects it will have to change the password (and this is not possible if the first connection is from opsManager.)

This user should also have permissions to connect to the database through SQL:

As well as read the server state:

Verifying the port

Make sure TCP/IP is enabled in SQL Server Configuration Manager, and that the port is what you specified in the Turbonomic admin panel configuration: