Amazon EC2 Amazon RDS AWS Web Management Console infrastructure assessment inventory data ISO downloads MAP toolkit Microsoft Active Directory Microsoft Azure MS Assessment and Planning Toolkit (MAP) NMap NMAP utility Phil Ekins PowerShell SQL Server SQL Server Express SQL Server footprints SQL Server licensing SQL Server Sprawl SQL Sprawl Tech TechNet CDs UDP scan

SQL Sprawl and Its Impact on Your Licensing

SQL Sprawl and Its Impact on Your Licensing

SQL Sprawl and Its Impression on Your Licensing

posted January 11, 2019, 12:55 PM by

Phil Ekins (@SQLPhilUK)Principal Architect

At Home of Brick we frequently present license based mostly consulting throughout the SQL Server and Oracle database stacks. We’ve quite a few blogs on the Oracle stack, however at this time we shall be speaking about SQL Server sprawl and its potential impression on licensing.

The time period “SQL Sprawl” has been floating across the SQL group for a lot of years, however for the needs of this weblog we will probably be defining it as “the SQL Server footprint inside a corporation having expanded past the IT division’s potential to handle, and/or the stock (or lack thereof) of SQL Server is not a assured supply of knowledge”.  That is sometimes termed as a License evaluation.

With the prevalence of TechNet CDs (again within the day) and entry to them, or extra just lately, ISO downloads (by way of MSDN or elsewhere), customers can deploy an Enterprise set up to a PC beneath their desk.  With this ease of set up comes the danger for an organization to be audited, and publicity could be unknowingly, very excessive.

The primary part in figuring out your SQL sprawl is to ask your group some questions:

  • What merchandise are we presently licensed to run?
  • What merchandise are put in?
  • Do these two numbers match?
  • Who’s putting in SQL Server?
    – Have they got a license?
  • Are we ready for an Audit?


Half one in every of this weblog collection will talk about administration methods to stop future sprawl in addition to methods to get a deal with on the present sprawl. Half two will talk about the evaluation of the newly found infrastructure and situations to think about throughout your evaluate. Half three will stroll by way of the logical subsequent steps following an infrastructure evaluation.


SQL Sprawl Half 1 – Getting a Deal with on Your SQL Sprawl
When assessing your setting there are a selection of methods that may assist find wayward installs. Step one for me is all the time the work smarter not more durable strategy.  Bigger organizations sometimes produce other present methods that may be leveraged in your evaluation.  They could embrace:

Buy Order System

  • A PO system might offer you entry to a searchable set of knowledge the place you’ll be able to search for SQL Server product variations and the proprietor who requested it.
  • Even smaller organizations might have a bodily drawer/credenza and so on. with a stack of bodily POs that may be sifted by way of throughout a search.


Software program Asset Administration System

  • If an asset administration system exists, then reporting will most certainly be obtainable to offer listings of SQL Server. Moreover, a database will most certainly even be behind the scenes and your DBA staff will little question have entry (or know who to ask for it) and the talent set wanted to question the info.
  • First hand state of affairs – we created a nightly job to verify for brand spanking new appearances of SQL Server installs and generated an exception report to trace down whether or not the occasion is licensed appropriately and whether or not it will be a greater match as a consolidation candidate.


Subsequent we will leverage tooling and scripting to audit our surroundings. The varied instruments / scripts we’ll can use embrace:

  • MS Evaluation and Planning Toolkit (MAP)
  • Lively Listing (AD)
  • PowerShell
  • exe
  • NMap


MAP Toolkit

This can be a free Microsoft device that may be downloaded right here.  The entire performance of this software is out of scope of this weblog, however under we’ll stroll via its use to find SQL Server installs.

After putting in the MAP device (notice: a SQL Server Categorical occasion can be put in behind the scenes) and launching MAP, we first need to create a database.

Then Choose ‘Database’ on the left aspect of the display and click on on ‘Acquire stock knowledge’.

Subsequent, choose ‘SQL Server’.

Choose ‘Use Lively Listing Area Providers (AD DS)’ and ‘Scan an IP tackle vary’.

For AD credentials, we have to specify the area and a legitimate account.

Subsequent,  for AD choices, we have now the choice to concentrate on a subset of OUs. However for this train, we’ll goal all OUs.

For the IP scan vary, we’ll enter our lab vary however we might enter a number of subnets (as wanted).

We’ll specify an account that may have entry to any computer systems discovered. You could need to work with the administration group nevertheless, for an appropriately elevated account right here.

For the Credentials Order we’ll settle for the preconfigured values, but when a number of accounts have been configured you’ll be able to set precedence by know-how as wanted.

Lastly, under is a abstract of the examine we’re about to run.

After clicking ‘End’ the gathering will run. The execution time depends upon the size of ranges offered.

After the gathering is full, the MAP interface will show the outcomes.

As the info is collected in a SQL Server Categorical occasion, we will additionally immediately choose this knowledge from the database we created towards the (localdb)maptoolkit occasion.

SELECT * FROM [Map-demo].[SqlServer_Reporting].[SqlInventoryView]


Lively Listing

Right here we will leverage PowerShell to look AD for pc names as wildcard searches.  *SQL* might be the only strategy, but when naming conventions are used, we will effective tune our standards to focus on SQL. Conversely, we will additionally search for non-compliant names suggesting a non IT sanctioned machine.

Get-ADComputer -Filter ‘Identify -like “*SQL*”‘ -Properties IPv4Address, whenCreated, lastlogon `
| SELECT Identify, DNSHostName, IPv4Address, whenCreated, @N=’LastLogon’; E=[DateTime]::FromFileTime($_.LastLogon)  `
#| Export-CSV C:ScriptsOutputSearch-AD-End result.csv –NoTypeInformation


This script is utilizing the Get-ADComputer technique to look identify for *SQL*, returning the identify, dnshostname, IP Tackle, datetime entry was created, and final datetime a consumer logged into the pc with the resultset exported right into a CSV file.

PowerShell Script

Right here we’ll leverage a PowerShell name to seek for SQL Server and output to a CSV.

The restrictions of this strategy might embrace firewall guidelines or the browser service not operating; the outcomes additionally is probably not constant between runs.

$ServerList = [System.Data.Sql.SqlDataSourceEnumerator]::Occasion.GetDataSources() | SELECT
ServerName, InstanceName, Model

#$ServerList | Out-File C:SQLSaturdaySQL_SprawlScriptsOutputSearch-Corp-End result.txt



SQLCmd.exe additionally has performance that permits us to seek for SQL Servers, with the caveat of comparable limitations as talked about within the PowerShell Script part above.





NMap Utility

The community port scanning/mapping software is an open supply utility that may be downloaded right here. It’s a very highly effective utility that can be utilized to seek for SQL Server listening on ports.

Most community monitoring instruments will alert on the exercise NMap is producing, so there’ll all the time be a big caveat related to it. Subsequently you need to all the time examine with, and get permission from, your networking workforce previous to operating NMap. They could need to run it in your behalf or modify an present course of.

Actual World Instance

Throughout supply of the SQL Sprawl materials, the safety officer within the room signifies that they scan their entire community nightly.

On this first demo, we’ll create a really tightly outlined scope.

Targeted Demo – NMap
The NMap command:

nmap -p T:1433 -sV -oG C:Search-Nmap-Targeted-1433-End result.txt


Specify the port to verify -p T:1433 (this can be a TCP verify towards 1433), probe open ports –sV , the IP vary and output to a file –oG xxx.

We will additionally do a UDP scan towards 1434 utilizing -p U:1434 –sU

The entire script under additionally cleans up the output.

nmap -p T:1433 -sV -oG C:SQLSaturdaySQL_SprawlScriptsOutputSearch-Nmap-Targeted-1433-End result.txt

$Rows = Get-Content material C:SQLSaturdaySQL_SprawlScriptsOutputSearch-Nmap-Targeted-1433-End result.txt
ForEach ($Row IN $Rows)
IF ($Row -Notlike ‘#*’)
IF($Row -like ‘*Ports:*’)
$StartPoint = $Row.IndexOf(“Ports:”) + 6
$PortArray = $Row.Substring($StartPoint).Cut up(“,”)
ForEach ($Port IN $PortArray)
$PortDetails=$Port.Cut up(“/”)
IF (($PortDetails[4] -Like ‘*ms-sql-s*’) -or ($PortDetails[5] -Like ‘*SQL Server*’))
Write-Host $Row.Substring(zero, $StartPoint) $Port


Observe: We’ve got, for the primary time, recognized a SQL on Linux occasion.  All prior collections didn’t catch this occasion.

Sledgehammer Demo

For this demo, because the title suggests, we will probably be operating a way more intrusive scan.

The NMap command:

nmap -p- -sV –version-intensity eight -oG C:SledgeHammer-Outcome.txt


The distinction right here is that the Port definition is –p- which suggests ALL ports. Moreover, the variety of probes (which vary from a light-weight scan (zero) to all probes (9)) is being set to eight –version-intensity eight and the IP vary has been set to a single IP to scale back the demo execution time.

The identical cleanup script is used.

Make observe of the upper degree of element within the output, but in addition the 90 second execution for a single IP.


Azure Stock

For the Azure Net Portal the ‘All Assets’ view provides you an inventory of all of your Azure subscription objects with filtering out there. Nevertheless, there’s not a simple approach to obtain the info proper from the display.

Utilizing PowerShell, we will additionally question our Azure subscriptions to gather SQL Server assets. Right here we shall be utilizing a Tag DBMS : SqlServer to assist filter digital machines and the useful resource sort for choosing SQL Server databases.

Azure is segmented by subscription, so we have to interrogate every subscription individually.


import-module Azure


$AzureSubscriptionID = ‘aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee’,’ffffffff-gggg-hhhh-iiii-jjjjjjjjjjjj’

$AzureSubscriptionID | %   The place-Object ($_.ResourceType -eq “Microsoft.Sql/servers”) | SELECT Identify, ResourceType, SubscriptionId
Discover-AzureRmResource -Tag @ DBMS=”SqlServer” | SELECT Identify, ResourceType, SubscriptionId



AWS Stock

With AWS, the Net Administration Console is of course filtered by Area, however through the use of instruments akin to AWS Config you possibly can create a dashboard that pulls your whole regional objects right into a single view.

Utilizing PowerShell, we will question our AWS account to gather EC2 and RDS providers associated to SQL Server.  Right here we’ll make the most of the Tag DBMS : SqlServer to determine the EC2 situations.  RDS may be recognized by DB Engine.

AWS is segmented by area, so we have to interrogate every area individually to seek out all assets.


Import-Module ‘C:Program Information (x86)AWS ToolsPowerShellAWSPowerShell’


Initialize-AWSDefaultConfiguration -AccessKey $accessKeyID -SecretKey $secretAccessKey
Initialize-AWSDefaultConfiguration -Area “us-east-1” -AccessKey $accessKeyID -SecretKey

$AWSRegions = Get-AWSRegion | Choose -EXP Area

$AWSRegions | % The place-Object $_.Engine -like “sqlserver*” | Choose DBInstanceIdentifier, DBInstanceClass, Engine, AvailabilityZone
$EC2 = (Get-EC2Instance -InstanceId (Get-EC2Tag -Filter @ Identify=”tag:DBMS”;Values=”SqlServer” | The place-Object $_.ResourceType -eq “occasion” | Choose -Exp ResourceId)).Situations | Choose InstanceId, InstanceType, SubnetId


IF (($RDS -ne $null) -or ($EC2 -ne $null))
Write-Host “Area : $_”
Write-Host “RDS Situations Recognized”
$RDS | Format-Desk
Write-Host “EC2 Situations Recognized”
$EC2 | Format-Desk



In Conclusion

Whereas the varied methods mentioned on this weblog have some overlap, they provide us the power to gather our SQL Server footprints, each on premises and within the cloud.  The MAP device is a really highly effective utility with performance far past what we confirmed right here.  Nevertheless it’s a guide course of to run (there are some third celebration utilities on the market to help in automation), and as we noticed, it didn’t discover all of our SQL installs by itself.  The opposite scripting examples have been extra configurable when it comes to automation, however the output assorted between runs and a few have been probably unacceptably intrusive to your company community.

The perfect strategy is a mixture of the above, in a scheduled and automatic course of.

Within the subsequent a part of this weblog will talk about the perfect technique for utilizing most of the methods mentioned above to create an automatic assortment course of. We will even cowl how greatest to make use of the listing of SQL Servers you generated when it comes to license evaluation, consolidation, and migration issues.