Page Comparison

What is This?

This is a small Cloudamize can collect two types of Microsoft (MS) SQL Server data: Basic data and Advanced data.

Basic data is collected from the Windows registry by the Cloudamize Agent and Agentless data collectors.

Advanced data is collected from MS SQL Server and can be used for detailed SQL Server performance analysis and licensing optimization.

The SQL Server Telemetry data collector is a 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 operationalizing SQL Servers in a manner that allows for the export of the information without providing 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 collect Advanced data. It can collect data from multiple SQL Servers and is especially useful when Cloudamize agents cannot collect this data. Collected data is saved to a file that can be uploaded to the Cloudamize console.

The SQL Server Telemetry data collector is available for the following platforms:

...

For other operating systems and build targets, please contact helpdesk@cloudamize.com.

Use Cases

Cloudamize captures SQL server data for licensing analysis via the Cloudamize agent or Cloudamize Agentless data collector. Some customers want to include SQL server information in their Cloudamize assessment but are unable to provide access credentials for their SQL servers. The provided utility allows you to capture this information without providing those credentials via the Cloudamize agent/agentless data collection method. This data can then be uploaded to Cloudamize and incorporated in the final results.

Other times, Cloudamize is able to collect SQL Server data from a customer’s environment but can only capture Basic SQL Server data and is unable to capture Advanced data. To capture Advanced data the customer must have PowerShell 5.1 (or higher) installed. If the customer wants to capture Advanced data but cannot upgrade PowerShell they can use this utility to provide the Advanced data to Cloudamize. You can learn more about the difference between Basic and Advanced data hereMS SQL Server Advanced data can be collected in a variety of ways data based on the customer’s environment and SQL Server configuration.

• The Cloudamize Windows Agent and the Cloudamize Agentless data collector will automatically collect advanced SQL Server data from SQL Server if the SYSTEM account has access to the SQL Server instances.

  • Requires PowerShell 5.1 or higher

  • Data is automatically sent to Cloudamize with no additional user action

• The Cloudamize Agentless data collector can also collect advanced SQL Server data from SQL Server if the User account specified in the Agentless UI has access to SQL Server instances.

  • Requires PowerShell 5.1 or higher

  • Data is automatically sent to Cloudamize with no additional user action

• The Cloudamize SQL Telemetry tool can collect advanced SQL Server data from SQL Servers if the Cloudamize Windows Agent and the Cloudamize Agentless data collector cannot collect this data because the specified account does not have access to SQL Server or PowerShell 5.1 or higher is not installed.

  • Does not require PowerShell

  • The user can provide different credentials for each instance of SQL Server on each machine

  • Data must be uploaded to the Cloudamize console by the user.

More information about what is included in the Basic data and Advanced data can be found at: https://support.cloudamize.com/kb/basic-and-advanced-ms-sql-collected-by-agent-based .

How to Use

...

1. Download the latest version of the

...

1. SQL Telemetry data collector using the link for your operating system above and save it to a directory on your system.

2. Create a servers.csv file with the SQL Server instances you want to collect data from (details below)

3. Run sqlserver-telemetry via the Terminal, Command Line, or Powershell (examples below)

4. Upload the telemetry.json file that is produced to the Cloudamize console

servers.csv (Input File)

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 in CSV format. The format for this file is on each line. Write out the Data Source Name (DSN) connection string for each connection strings for all of the SQL servers Server instances you would like to gather the information. You can provide 1 or 3000collect data from. This file can be created with Notepad, Excel, or any text editor. One SQL Server instance should be one per line, there is no limit to the number of SQL Servers.

The recommended connection string uses a URL format is:

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

But it will also accept as an example the following formatsThe following connection string URL formats are also supported

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

...

• 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"`

Run sqlserver-telemetry

Windows

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

sqlserver-telemetry.exe servers.csv

Linux

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

...

chmod a+x sqlserver-telemetry

...

telemetry.json (Output File)

This utility will run for ten minutes, and generate a JSON file called telemetry.json which 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:

...

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 customer's 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.

Upload SQL Telemetry Report

• 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 Settings Page > External Data option, you will see a tab called SQL Telemetry
    

    

  • Select SQL Telemetry and then click on the Upload SQL Telemetry Report option to upload the telemetry.json 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

  • SQL I/O Performance

  • SQL Enterprise Level Features Used

...

I/O Performance data can be viewed by clicking the button in the “Other SQL Info” column:

...

Reporting

The full report can be access accessed from the reports section of the new console:

...

The data is also available on the classic console, in the SQL Server and License Usage report (accessed via the Migration Planner):

...

Issues or Queries?

If you have any questions, concerns, or issues, please contact helpdesk@cloudamize.com.

...