Discovering SQL Instances to help determine compliance

There are many Discovery tools on the market, from Snow to Centennial (FrontRange Solutions). However, If you are responding to a vendor audit, might be worth at exploring the following options.

Discovering Instances of SQL Server can be achieved using the Microsoft Assessment Planning Solution Accelerator (MAPSA) Tool.

Install the MAPSA tool from: ttp://www.microsoft.com/downloads/details.aspx?FamilyID=67240b76-3148-4e49-943d-4d9ea7f77730&DisplayLang=en

Having discovered which machines are running SQL Server it is then necessary to discover which version and edition is being used, and if it’s clustered. 

Install the SQL Server Health and History Tool (SQLH2) from: ttp://www.microsoft.com/downloads/details.aspx?FamilyID=EEDD10D6-75F7-4763-86DE-D2347B8B5F89&displaylang=en

  1. Discovering Instances of SQL Server can be achieved using the Microsoft Assessment Planning Solution Accelerator (MAPSA) Tool.  A function of this tool is to capture software inventories of machines on the network.  The data is stored in a SQLExpress instance that is downloaded and installed as part of the MAPSA tool installation process.  Full details for using the tool are included in the following this Walkthrough [Below]
  2. Having discovered which machines are running SQL Server it is then necessary to discover which version and edition  is being used, and if it’s clustered. [Determining Licensing Modes – Below]
  3. To gather this information we can use the SQL Server Health and History tool (SQLH2) available here
  4. SQLH2 can be configured to collect SQL instance information from a list of servers held in a single column text file.  This file can easily be created using the data in the spreadsheet referred to in paragraph above
    1.  De-duplicate the machine names in the first column using the ‘Remove Duplicates’ option on the Data menu.
    2. Create a blank txt file using notepad and then simply copy and paste the list of unique machine names into the blank file.
  5. During SQLH2 setup you will create a SQLH2Repository database – this can be created in the SQLExpress instance installed by the MAPSA tool.
  6. You can either configure the tool through a wizard immediately after running setup (check the ‘launch configuration utility after setup finishes’ option during installation) or through the configuration utility (SQLH2Cfg.exe).
  7. Please be aware  there is a feature that requires the utility be closed and re-started prior to re-editing the configuration.
  8. Having configured SQLH2 you need to run the application itself from the command line – being in the UK this will most likely require the date format switch to avoid date conversion errors eg C:\SQLH2\sqlh2 /DDMY (you must use the capitals).
  9. To extract the additional instance details gathered by the SQLH2 you can either use the same instructions below xtracting data into a spreadsheet, only this time you will need the following query:SELECT mgr_server.name AS [Server Name], mgr_instance.name AS [Instance Name], hs_instance.version, hs_instance.edition, hs_instance2.IsClustered, hs_instance.plevel, mgr_instance.IsDefault, hs_instance.run_id, hs_instance.IsRunning
    FROM mgr_server
    INNER JOIN mgr_instance
    ON mgr_server.srv_id = mgr_instance.srv_id
    INNER JOIN
    hs_instance ON
    mgr_instance.srv_id = hs_instance.srv_id
    AND mgr_instance.inst_id = hs_instance.inst_id
    LEFT OUTER JOIN hs_instance2 ON
    hs_instance.srv_id = hs_instance2.srv_id
    AND hs_instance.inst_id = hs_instance2.inst_id

SQL Server Discovery with Microsoft Assessment Planning Solution Accelerator (MAPSA) ToolCreate a new database

After installing the tool, create a new database using the “Select a database” link in the upper right-hand corner of the main page.  Remember the name of the database you create, you’ll need this name later in order to run queries against the database to find the SQL Server instances.

Run an inventory

To find the machines in the environment with SQL Server installed, you’ll need to have MAPSA run an inventory of your environment.  Here are some things to keep in mind when running an inventory:

  • The queries we’re providing today to find SQL Servers will find ALL installed instances of SQL Server, regardless of whether it’s a version of SQL Express or a FPP version. 
  • If you like, you can limit where MAPSA will inventory machines to exclude machines that are likely to have SQL Express but not a FPP version of SQL Server.  This will cut down on the amount of noise you have to filter through later.

To start an inventory:

  • Choose “Identify servers that are capable of running Windows Server 2008”   (don’t worry if you want to inventory clients as well, they won’t be filtered out by this choice)
  • On the “Select Reports and Proposals” page of the wizard, accept the default and click “Next” (you can add additional reports if you want to)
  • On the “Choose Computer Discovery Methods” page, you’ll now have the ability to start filtering out machines you don’t want to inventory.  Here are some ideas for filtering:
    • If you have all your servers in a particular domain or in a particular OU and only want to inventory those servers and nothing else, then choose the “Use Active Directory Domain Services” checkbox only, and specify which Domain or OU within a domain is to be inventoried.
  • If you have a specific list of computer names that you’d like to inventory (that you perhaps got from System Center Configuration Manager or another tool), you can import that list using the “Import Names from a file” option and only those machines will be inventoried if you disable all other checkboxes on this page.
  • If you have a specific IP address range or ranges that you’d like to inventory, then choose the “Scan IP address range” option, and only the IP addresses you specify will be inventoried if you disable all other checkboxes on this page.
  • Make your selection on the “Choose Computer Discovery Methods” page and click “Next”.  Depending on which options you choose here, you’ll be shown additional pages to configure the inventory methods.   When prompted for WMI credentials, make sure to provide credentials that have the equivalent of “Administrator” privileges on the machines being inventoried (a domain admin credential, for example).
  • After configuring all the discovery methods and entering WMI credentials, the inventory will run and a set of reports will be generated.  These reports will be interesting, but won’t contain a list of all installed SQL servers.
  • There are several tools available for querying SQL Server, including Microsoft Access, Microsoft Excel, osql.exe, and the SQL Server Management Studio.  You can use any of these you feel comfortable with, but we’ll only be describing how to use Excel 2007 to run the queries to get the data.  
  • Create a new workbook
  • On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.
  • On the “Choose Data Source” dialog, click <New Data Source> and click OK
  • Enter a name for the datasource, such as the name of the database you created for inventory
  • Select “SQL Native Client” for the driver
  • Click Connect, and enter machineName\MAPS in the “Server Name” field.  For example, if you computer is named “MyLaptop”, enter MyLaptop\MAPS, click the “Use Trusted Connection” checkbox then click the “Options” button to expand the options
  • Under Database, choose the name of the database you created for inventory
  • Click OK to dismiss the “SQL Server Login” page
  • Click OK to dismiss the “Create new datasource” dialog
  • Make sure the datasource name you just created is selected in the “Choose Data Source” dialog, and click OK
  • Click the ‘+’ next to “all_colums” to expand the list, then click the top item in the list under “all_columns” and press the ‘>’ button to move the item to the right-hand list.
  • Click “Next”
  • Click “Next” to skip the “Filter Data” page
  • Click “Next to skip the “Sort Order” page
  • On the “Finish” page, click “View or Edit data in Microsoft Query” and press “Finish”
  • Click the “SQL” button
  • Erase the query displayed in the “SQL” dialog.
  • Paste in the query for getting all SQL Servers from earlier in these instructions
  • Click OK, then click OK again to dismiss the “SQL Query can’t be represented graphically” warning.
    The MAPSA application uses a dedicated database instance called “MAPS”.  To connect to this database instance on your local machine, you’ll use the server name localmachinename\MAPS   For example, if your machine is named “mylaptop”, use mylaptop\MAPS as the database server name.The database name will be whichever name you specified when creating the inventory in the steps above.SELECT COALESCE(d.[ad_dns_host_name],

    CASE

    WHEN (RTRIM(LTRIM(LOWER(d.computer_system_name))) != RTRIM(LTRIM(LOWER(d.dns_host_name))))

    THEN d.computer_system_name + ‘(‘ + d.dns_host_name + ‘)’

    END,

    d.dns_host_name

    ) AS ‘Computer Name’,

    d.domain_name,

    s.caption,

    s.description,

    s.display_name,

    s.name,

    s.path_name,

    s.start_mode,

    s.start_name,

    s.started,

    s.status,

    CASE WHEN PATINDEX(‘MSSQL$%’, s.name) > 0 THEN

    SUBSTRING(s.name, 7, 999)

    ELSE

    ‘(Default)’

    END AS ‘Instance Name’

    FROM Services s INNER JOIN devices d on d.device_number = s.device_number

    WHERE s.Path_Name like ‘%SqlServr.exe%’

    To get the list of machines with Analysis Services installed (2000, 2005, 2008):

    — returns for 2000, 2005

    SELECT COALESCE(d.[ad_dns_host_name],

    CASE

    WHEN (RTRIM(LTRIM(LOWER(d.computer_system_name))) != RTRIM(LTRIM(LOWER(d.dns_host_name))))

    THEN d.computer_system_name + ‘(‘ + d.dns_host_name + ‘)’

    END,

    d.dns_host_name

    ) AS ‘Computer Name’,

    d.domain_name,

    s.caption,

    s.description,

    s.display_name,

    s.name,

    s.path_name,

    s.start_mode,

    s.start_name,

    s.started,

    s.status

    FROM Services s INNER JOIN devices d on s.device_number = s.device_number

    WHERE s.Path_Name Like ‘%msmdsrv.exe%’

    To get the list of machines with Integration Services installed (2005, 2008)

    SELECT COALESCE(d.[ad_dns_host_name],

    CASE

    WHEN (RTRIM(LTRIM(LOWER(d.computer_system_name))) != RTRIM(LTRIM(LOWER(d.dns_host_name))))

    THEN d.computer_system_name + ‘(‘ + d.dns_host_name + ‘)’

    END,

    d.dns_host_name

    ) AS ‘Computer Name’,

    d.domain_name,

    s.caption,

    s.description,

    s.display_name,

    s.name,

    s.path_name,

    s.start_mode,

    s.start_name,

    s.started,

    s.status

    FROM Services s INNER JOIN devices d on s.device_number = d.device_number

    WHERE s.Path_Name Like ‘%MSDTSSrvr.exe%’

To run a query from Excel 2007

    At this point, MSQuery will be invoked in Wizard mode.   You don’t actually want to use the wizard, but unfortunately, you have to step through it to get to the point where you can enter your own SQL Query.   To get through the wizard:The main UI for Microsoft Query will now be displayed.  Click the “SQL” button at the top of the page to enter the query you want to run.  TO get a list of all SQL Servers: At this point, you can close MSQuery (File->Return Data to Microsoft Excel).  Excel will prompt you for a range on a worksheet to place the data in.Once the data for the first query has been placed in Excel, you can repeat the same steps to execute the queries for Analysis Services and Integration Services if you like.  Be sure to place the results of these queries on different tabs in the workbook so you don’t overwrite the data from other queries.Note that Excel automatically turns on the Auto Filtering mode, which will allow you to use the drop-down menus at the top of each column to apply filters to the data.  See the sample workbook in the folder for an idea of what this looks like.

Tracking SQL Server Modes

Tracking License Information in SQL 2005

SQL 2005 does not track (Server CAL Model or Per Processor) registry entries. SQL 2005 still reads the registry for this information, but the SQL 2005 setup doesn’t put licensing information in the registry during setup as it did SQL Server 2000.
Supported Resolution

Since SQL 2005 still queries the registry for licensing information, add the following key and values and ServerProperty(‘LicenseType’) will return license information.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\MSSQLLicenseInfo\MSSQL9.00

If you want to configure SQL Server for Per Processor then add these Registry Values under that Key adjusting for the number of processors you have a license for:

Name                         Type                           Value
Mode                         REG_DWORD           2           ß LICENSE_MODE_PERPROC   
ConcurrentLimit        REG_DWORD           4           ß Number of Processors

If you want to configure SQL Server for Per Seat licensing then add these Registry values under the Key adjusting for the number of seat license you have purchased.

Name                         Type                           Value
Mode                         REG_DWORD           0            ß LICENSE_MODE_PERSEAT   
ConcurrentLimit        REG_DWORD           100         ß No. of client licenses registered for SQL Server in Per Seat mode.

Test in SQL Management Studio

You need to stop and restart SQL Server 2005 before the information will be available to ServerProperty() as the registry is read on start-up of SQL Server.

With the above settings you would see the following when you restart SQL Server 2005.

SELECT  ServerProperty(‘LicenseType’) as LicenseType, ServerProperty(‘NumLicenses’) as ProcessorCount

[Ref: Emma Healey]

Advertisements

2 thoughts on “Discovering SQL Instances to help determine compliance

  1. Pingback: The roundup: here’s what you may have missed « Microsoft License Review

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s