Skip to main content

SQL Server Instructions to run a collection for Workloads

Team Live Optics
  • Updated

Overview

Live Optics now supports data analysis to help you understand the performance and workload characteristics of your SQL Servers.

Using the Live Optics collector, configuration data and performance metrics are captured, analyzed and compiled into a Project and viewed from the online viewer.

This document outlines the process on how to run a SQL Server Collection for Workloads from the Live Optics Collector.

As a reminder, please make sure to download the latest version of the Live Optics Collector.

 

 

Introduction

The collection process is broken down into two (2) phases which are to be executed separately and manually – the Optical Prime Performance step and the SQL Configuration step. During the Optical Prime Performance collection process Windows Host based and SQL performance data are collected.  During the SQL Configuration collection process, SQL instance configuration data (including SQL file path locations) is collected.

 

SQL_2.png

It is important to note that, for a complete collection, both phases must be executed and completed separately.

 

Quick-Start Directions

  • Download the Live Optics Collector (Windows .NET version).
  • Launch Live Optics Collector
  • Select Workloads from the left menu
  • Select SQL Server
  • Select your preferred Collection Method
    • SQL Server Config & Performance using Optical Prime (Recommended) 
    • Config Data Only 

SQL Server Config & Performance using Optical Prime (Recommended)

Config Data Only

While the Optical Prime Collection process is running, execute the SQL Collector .exe again to start a new thread to gather configuration data.

This will direct you to a dedicated SQL Server collection workflow. This method will use a SQL Server authentication or Windows authentication method to collect configuration data, directly from one SQL Server instance at a time. Whichever authentication method you choose, verify you have “read” access to the instance of SQL.

  • Select the authentication type; SQL Server Authorization or Windows Authorization
  • Enter the username and password (if applicable)
  • Click NEXT to select the Upload Option, either upload automatically (recommended) or upload later
  • Click Next
  • If upload automatically was selected, enter the project name to be created, click Next
  • If upload later was selected, enter the file name and directory to save the .SIOKIT file
  • When you have finished naming the project or entering the file name, click NEXT. You will be presented with a progress bar indicating how long it will take to run the collection.
  • After the files are processed and if upload automatically was selected, the data will be sent to the Live Optics portal with NO action needed from the user
  • An option to create a SIOKIT for backup is provided, this can be skipped or the file can be created at this time
  • If upload later was selected, the SIOKIT will need to be manually uploaded to the Live Optics portal.
  • Click Quit to exit the application

 

Detailed Instructions

Download and run the Live Optics Windows Collector Software

  • There are two methods to download the collector software:
    • From the email you received from your systems consultant inviting you to Live Optics by clicking the Download Collector link.
    • From the Live Optics portal, select Collectors from the left menu, then Download Collector. For more information on how to download a Live Optics collector, please see knowledge base article Download the Live Optics Collector Either option will bring you to the Download Collectors page. 

Start Profile Selection Process and collection method

  • Select Workloads from the Category and select SQL Server from the list of asset types.
  • There are two collection methods for you to select, both of which are detailed below;
    • SQL Server Config & Performance using Optical Prime (Recommended) 
    • Config Data Only 

 

mceclip0.png

 

SQL Server Config & Performance using Optical Prime (Recommended) 

Choosing SQL Server Config & Performance using Optical Prime (Recommended) will redirect you to Optical Prime. This collection method will use WMI to collect config and performance from multiple SQL Severs.

  • The only available Optical Prime upload option is to establish a secure connection with Live Optics web service. This is to ensure that the generated Optical Prime project is associated to SQL Server project(s) created, corresponding to the available instances, therefore manual SIOKIT upload and Inventory mode aren’t supported.

mceclip1.png

  • The remaining workflow follows a typical Optical Prime collection.
  • To view project results:
    • Log in to the Live Optics web portal https://www.liveoptics.com
    • On the Dashboard, you’ll find an Optical Prime project that corresponds to this collection
    • Upon opening the Optical Prime Project, proceed to SQL Server tab, which lists all your SQL Server Instances

SQL_Server_TB.png

    • Click on the individual instances to be redirected to the corresponding SQL Server Project and view per Instance configuration and performance data.

Config Data Only

If you choose to collect Config Only Data, this will direct you to a dedicated SQL Server collection workflow. This method will use a SQL Server authentication or Windows authentication to collect config data only, from one SQL Server instance.

On the authentication screen, identify the SQL Server instance

  • Enter the SQL Server Name, username, and password or choose Windows Authentication. In order to successfully run a SQL Server config collection, the user must have VIEW SERVER STATE permission on the SQL Instance.
  • Once entered, click Next

mceclip2.png

  • Start the Configuration Collection
    • The collector will connect to the SQL Server and confirm that the user has the correct permissions to collect from this specific database.

mceclip3.png

NOTE: In order to successfully run a SQL Server config collection, the user must have VIEW SERVER STATE permission

  • Select the Method to Upload to the Live Optics Portal for Processing
    • Selecting the default, “Upload my collection data to the Live Optics web portal automatically” this is the recommended method. Once the collection is complete, the data is automatically upload to the Live Optics portal. Internet connection is required for this option.
    • There is an option to upload later. This will save the configuration and performance data to an encrypted file. You will be prompted to give your file a name and select a location to save the file.
    • Once selected, click Next.
    • Enter the name of the project that will be created in the Live Optics portal
    • Click Next
  • Upload the data to the Live Optics portal
    • Once the collection has ended the files are processed and automatically uploaded to the web portal
    • During the upload, a status bar indicates the progress. As can be quite large, the upload may take some time to complete depending on the speed of the Internet connection
    • The collector will indicate when the upload has completed
  • Create a backup file of the data
    • A choice will be provided to back up the project via a SIOKIT. There is a choice to skip this option or create the backup file.
    • Identify the file name and save location of this file
  • Process has completed successfully
    • Once the upload has completed. Click Quit to close the collector
    • An email from Live Optics will be sent when the project is ready for viewing on the web portal

  • Uploading the SIOKIT (applies only if the “upload later” was selected)

  • View Project Results
    • Log in to the Live Optics web portal https://www.liveoptics.com
    • Recent SQL Server projects are listed on the dashboard
    • Select the project