What is This?


This is a small command line interface (CLI) utility to gather very specific SQL Server telemetry information that drives the optimization of SQL Server licensing. The focus of this application is to gather data about running and operational SQL Servers in a manner that allows for export of the information without providing the access credentials to the report requesting organization. This can be compiled and run on Windows, Mac, and Linux for all of the most common chipsets, but the default packages are compiled for the following platforms:

For other operating systems and build targets, please contact support@cloudreach.com

How to Use

In order to use this, you will need to download from the links provided above, this will provide you with the latest version of the application. Since this is a CLI based application, it is required that you have some knowledge of how to use your Terminal or Command Line (Powershell for Windows is perfectly acceptable as well).Regardless of Operating System (Input)
In the directory where you downloaded the application, create a new file called servers.csv - which can be done with vi, emacs, Notepad, Excel, or any other format that can write to CSV format. The format for this file is on each line write out the Data Source Name (DSN) connection string for each of the SQL servers you would like to gather the information. You can provide 1 or 3000, there is no limit. The recommended connection string uses a URL format:

sqlserver://username:password@host:port/instance
CODE

But it will also accept as an example the following formats

  • sqlserver://username:password@host/instance?param1=value&param2=value

  • sqlserver://username:password@host:port?param1=value&param2=value

  • sqlserver://sa@localhost/SQLExpress?database=master&connection+timeout=30 // SQLExpress instance.

  • sqlserver://sa:mypass@localhost?database=master&connection+timeout=30 // username=sa, password=mypass.

  • sqlserver://sa:mypass@localhost:1234?database=master&connection+timeout=30 // port 1234 on localhost.

  • sqlserver://sa:my%7Bpass@somehost?connection+timeout=30 // password is "my{pass"

The connection string can also be provided in ADO and ODBC format, examples as follows:

  • server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName

  • server=localhost;user id=sa;database=master;app name=MyAppName

  • odbc:server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName

  • odbc:server=localhost;user id=sa;database=master;app name=MyAppName

  • odbc:server=localhost;user id=sa;password={foo;bar} // Value marked with {}, password is "foo;bar"

  • odbc:server=localhost;user id=sa;password={foo{bar} // Value marked with {}, password is "foo{bar"

  • odbc:server=localhost;user id=sa;password={foobar } // Value marked with {}, password is "foobar "

  • odbc:server=localhost;user id=sa;password=foo{bar // Literal {, password is "foo{bar"

  • odbc:server=localhost;user id=sa;password=foo}bar // Literal }, password is "foo}bar"

  • odbc:server=localhost;user id=sa;password={foo{bar} // Literal {, password is "foo{bar"

  • odbc:server=localhost;user id=sa;password={foo}}bar} // Escaped } with }}, password is "foo}bar"`

Windows

In your Windows command prompt of preference (Command Prompt or Powershell), change directory to where you downloaded the file and saved the aforementioned servers.csv file.
Run the following command

sqlserver-telemetry.exe servers.csv
CODE

Linux

In your Linux terminal, change directory to where you downloaded the file and saved the aforementioned servers.csv file.
Run the following command

./sqlserver-telemetry servers.csv
CODE

If you have issues with the command, you may need to run the following first (and only once)

chmod a+x sqlserver-telemetry
CODE

Regardless of Operating System (Output)

This utility will generate a JSON file called telemetry.json that is a machine and human readable list of all of the output for the telemetry commands run against each of the listed DSN connection strings in the servers.csv file. For each connection string it will capture the following information as accessible from within SQL Server AND the permissions allowed to the connection string account:

  • Host

  • SQLServerVersion

  • SQLServerEdition

  • OperatingSystem

  • AvailabilityGroups

  • Roles

  • ClusterInfo

  • Mirroring

  • LogShipping

  • Memory

  • HostCores

  • VisibleCores

  • Errors

In order to make this more resilient, any errors identified during the execution of this utility will be silently captured and stored in the Errors field. This allows for maximal telemetry gathering without having to repeatedly trial and error within a customers site. It is important to note that the output will NOT have any sensitive credential information and can therefore be exported with minimal to no risk associated.Issue

File (Output) Upload

  • Follow these steps in the Cloudamize Console to upload the SQL Telemetry output file:

    • When the above process completes, there will be a telemetry.json file in the Downloads folder

    • Log into the Cloudamize Console

    • Navigate to the Settings page

    • On the Inventory Settings > Pre-Cloud pane you will see a tab called SQL Telemetry

    • Select SQL Telemetry and then click on the Upload link to upload the servers.csv file

  • We will fill in the following data collected from the above process where the data was not collected by the agent or agentless data collector

    • On-Prem Server Name (REQ'D)

    • On-Prem SQL Server Edition(REQ'D)

    • On-Prem SQL Server Version(RCM'D)

    • On-Prem Operating System(REQ'D)

    • SQL Cluster Name

    • Passive Instance Using Log Ship, DB Mirroring(Non-Cluster)

    • SQL Availability Group

    • SQL Availability Group Role

    • SQL Max Server Mem in MB

    • SQL DB Mirroring Present

Reporting

If you have any questions, concerns, or issues, please contact support@cloudreach.com. This application is built using the Go Programming Language in order to allow for safe and reliable software that is cross platform.