SQL Detection by Edition/Version

  • This is rather general, not really limited to 2012. My company has requested I find a way to automatically detect which edition/version is installed. Are there file paths, trace files or registry entries that once found on a host id, would tell me the edition/version installed. Looking for a listing of signatures or tags that might assist me in this detection task. I have the ability to customize a signature with all the parameters/variables required, I am just not sure what the parameters might be. Thank you.:

  • gbill (8/5/2013)


    This is rather general, not really limited to 2012. My company has requested I find a way to automatically detect which edition/version is installed. Are there file paths, trace files or registry entries that once found on a host id, would tell me the edition/version installed. Looking for a listing of signatures or tags that might assist me in this detection task. I have the ability to customize a signature with all the parameters/variables required, I am just not sure what the parameters might be. Thank you.:

    I am not 100% certain of what you are asking here. Are you trying to find some t-sql that will tell all versions of sql server installed on a given server? Or are you just looking for a way to find the versions installed on a machine from outside of t-sql?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Again based on what you are look for this may or may not help you.

    Use PowerShell to Quickly Find Installed Software

    http://blogs.technet.com/b/heyscriptingguy/archive/2011/11/13/use-powershell-to-quickly-find-installed-software.aspx

    It's a nice write up that shows you how to use powershell and either WMI calls or search of the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall

  • Thanks for answering my post.

    I am not overly skilled in the SQL area, I am learning though. For example, once the SQL product is installed, are there identifiers (i.e., files or registry entries) on the host that would tell me I am running MS SQL Server Datacenter, Enterprise, Standard, Workgroup, Web, Developer, Express, Azure, Compact, Evaluation, Fasttrack, Parallel Data Warehouse, Small Business, etc. Further, are their identifiers that could tell me version 10.0, 10.5, 11.0, etc. A person could say, go on line and look at the add/remove section, but this is not feasible due to the number of host id's I must support with a given account. In my examples, a Customer may say, I have 5K servers, tell me everywhere SQL Standard is installed. My automated tool set allows me to search for files/byte size, registry entries (package name, package version) and file paths. Is there any source that says, if you find this registry entry or this file path or these 3 or 4 specific files (or combinations of all of them), you must have Enterprise 11.0 installed. A different set of variables would tell me you have Standard 10.5 installed.

  • gbill (8/5/2013)


    Thanks for answering my post.

    I am not overly skilled in the SQL area, I am learning though. For example, once the SQL product is installed, are there identifiers (i.e., files or registry entries) on the host that would tell me I am running MS SQL Server Datacenter, Enterprise, Standard, Workgroup, Web, Developer, Express, Azure, Compact, Evaluation, Fasttrack, Parallel Data Warehouse, Small Business, etc. Further, are their identifiers that could tell me version 10.0, 10.5, 11.0, etc. A person could say, go on line and look at the add/remove section, but this is not feasible due to the number of host id's I must support with a given account. In my examples, a Customer may say, I have 5K servers, tell me everywhere SQL Standard is installed. My automated tool set allows me to search for files/byte size, registry entries (package name, package version) and file paths. Is there any source that says, if you find this registry entry or this file path or these 3 or 4 specific files (or combinations of all of them), you must have Enterprise 11.0 installed. A different set of variables would tell me you have Standard 10.5 installed.

    Is this being done for inventory or for the application that is running against the database? Most of this information could be returned via SQL Server Management Objects (SMO) (http://technet.microsoft.com/en-us/library/ms162169.aspx) .

    For example there server class (http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx ) would have the following properties available for you:

    EditionGets the Edition property value.

    OSVersionGets the OSVersion property value.

    ProductGets the product of the server.

    ProductLevelGets the ProductLevel property value.

    VersionGets the Version property value.

    VersionMajorGets the major version of the server.

    VersionMinorGets the VersionMinor property value.

    VersionStringGets the version string of the server.

    You can get a full list of properties here ( http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server_properties.aspx )

  • Thanks again for the answers. It is for license management. Our Customer pay us to keep them compliant. In other words if we detect 500 instances of say standard, the Customer would need to have 500 licenses. Big name companies conduct audits of our Clients to make sure they are compliant. So, if a Customer has 3 or 4K servers, they would need to know how many copies they have of each product, and then check against their license pool. It is not good enough to say a Customer has 100 copies of MS SQL, the Customer would want to know Edition and version/release. We do this for many product, or at least as many as we are able. Some times detection can just be done by identifying a simple file and byte size or registry entry - SQL is a little more problematic. I could probably isolate a file that tells you SQL is running, but I don't think I could pin point a specific file that tells me Enterprise is running.

  • I can understand that since you are looking for all sorts of products, that you don't want to engage in anything SQL Server-specific like SMO. However, the PowerShell option that was suggested may be palatable.

    I don't really know what you can do through WMI, but that is also an option to investigate, as I imagine that WMI can be used for inspecting other products as well.

    If you want to read the registry directly, the place to go is HLKM/Software/Microsoft/Microsoft SQL Server. The node Instance Names will give you all instances on the machine. (Note plural! There can be several instances on the same server, and they can be of different editions). You are interested in the values in the Data column. On my machine, I see the names MSSQL.1 and MSSQL11.MSSQLSERVER. Then you can look in HKLM/Software/Microsoft/Microsoft SQL Server/MSSQL.1/Setup.

    This schema works for SQL 2005 and up; I don't know about SQL 2000 on the top of my head. (And it does not work for SQL7 and earlier.)

    Another story is that even if there is a license - is the licence correct for the number of cores/socket in the machine? And in earlier versions, licenses can be Per Seat or whatever the other is called.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply