SQL Server Auto Detection

Sam Kirchoff

For years the Live Optics server/virtualization assessment, Optical Prime, has been a standard in the IT community for helping to gather and share server configurations and performance data.

For Microsoft Windows OSes this data is gathered through the Windows Management Instrumentation (WMI) interface. WMI is a standard management interface and is accessible in almost every environment as many systems tools would also use this interface.

Multiple instances of Microsoft SQL Server can be installed on a single machine and inside those instances, there can be one or many Databases.

This can make it difficult to gather data such as total capacities of the actual database files, performance metrics or backup status as a regular Windows Admin. DBAs often maintain the security of the SQL Database and, in larger companies, the Windows Admins and the DBAs are most likely not the same person.

Live Optics supports two methods of collecting data from a SQL instance; through Dynamic Management Views (DMV) and WMI.

The DMV method will require dual authentication depending on how the security is established, but once authenticated to the SQL Instance, all the detailed information of SQL Server can be brought to life.

Currently, this method will only create a project focused on the configuration of the SQL Instance.

As typical in Live Optics, the resulting data from the DMV model will be saved into a .SIOKIT file. These files are the proprietary Live Optics extension and are fully encrypted to protect their contents.

Alternatively, Microsoft SQL also publishes configuration and performance data through WMI. This is effectively Perfmon data for SQL, but this means that these metrics can be gathered without dual authentication and in the normal course of discovery for any supported Windows operating system.

This is a much more efficient process since Live Optics can document these details without additional user interactivity and therefore this is considered an “auto-discovery” service.

The auto-discovery service through Optical Prime will only invoke the discovery of SQL Instances when the settings are selected to allow the data to be “streamed” or sent immediately to the Live Optics Portal. This is done to ensure that the instances are correctly tied to the corresponding server project.

Even while streaming, .SIOKITs are created as a failsafe. In the circumstance where networking connectivity to the portal is dropped mid-collection, but the collection itself completed successfully, there will be one .SIOKIT for the server(s) collection itself. However, every SQL Instance encountered will also have its own .SIOKIT that is identified by [SQL-]+[Servername or IP address-]+[instance name].siokit syntax.

Live Optics currently only allows the upload of one .SIOKIT at one time by using the “Upload SIOKIT” feature of the Collector. Simply start the collector, select Upload SIOKIT and follow the prompts to return the files for processing.

Streamed Optical Prime projects that auto-discovery SQL Instances will have a “SQL Server” tab that is automatically generated. This tab will show the links to all the associated SQL instances discovered in that collection as well as providing a “pop-out” for each Instance’s dedicated project page.

SQLLinks.JPG

On rare occasions, a SQL Instance project may have been deleted or might become un-linkable for various reasons. These pop-out links will become grayed out and no longer active.

Projects that need to be created due to manual upload of an .SIOKIT will not be auto-linked to its corresponding Optical Prime project. These projects can be easily viewed by clicking on “View Projects” from the Dashboard, filtering by SQL Server to narrow the assessment types shown to just Microsoft SQL Server.

SQLProject.JPG

Was this article helpful?
0 out of 0 found this helpful
Please sign in to leave a comment.