How to identify SQL Server version from program files for Auditing/Licensing Purposes
Hi Guys,
We are using Numara Track-IT Enterprise 8.5 as our helpdesk software. Track-IT has an audit tool that automatically scans every machine and lists all of the software. I'm in the process of setting up the licensing module that reconciles the audited software against licenses.
The problem I'm having is with SQL server and identifying all of the different versions from the files picked up by the audit.
Track-IT allows you to create a software title based on a set of files, so I can select different versions of 'SQLServr.exe' and call it 'SQL 2005 Enterprise edition'. I can then select other versions of the same file and call the software 'SQL 2005 Standard edition' etc.
The Track-IT audit software lists the File Name, File Version and Product Version. I have done some research on the internet and discovered an article at : http://support.microsoft.com/default.aspx/kb/321185 that lists different versions e.g. SQL Server
Release SQLServr.exe
SQL Server 2005 Service Pack 1 2005.90.2047
SQL Server 2005 Service Pack 2 2005.90.3042
SQL Server 2005 Service Pack 3 2005.90.4035
The trouble is that when I create a software title from these versions, its listing servers that have Enterprise, Standard and the desktop edition that comes with things like backup software and anti-virus etc.
Can anyone point me in the direction of a complete list of either the file versions above for sqlservr.exe or some collection of files, so that I can positively identify which files make up the different versions of SQL Server, including the free desktop edition (Not sure what the correct name is for it). That way I can create all the different software titles and allocate licenses against them. If I don't get this right, it looks like I have loads of unlicensed software on the servers.
Thanks for your help guys.
We are using Numara Track-IT Enterprise 8.5 as our helpdesk software. Track-IT has an audit tool that automatically scans every machine and lists all of the software. I'm in the process of setting up the licensing module that reconciles the audited software against licenses.
The problem I'm having is with SQL server and identifying all of the different versions from the files picked up by the audit.
Track-IT allows you to create a software title based on a set of files, so I can select different versions of 'SQLServr.exe' and call it 'SQL 2005 Enterprise edition'. I can then select other versions of the same file and call the software 'SQL 2005 Standard edition' etc.
The Track-IT audit software lists the File Name, File Version and Product Version. I have done some research on the internet and discovered an article at : http://support.microsoft.com/default.aspx/kb/321185 that lists different versions e.g. SQL Server
Release SQLServr.exe
SQL Server 2005 Service Pack 1 2005.90.2047
SQL Server 2005 Service Pack 2 2005.90.3042
SQL Server 2005 Service Pack 3 2005.90.4035
The trouble is that when I create a software title from these versions, its listing servers that have Enterprise, Standard and the desktop edition that comes with things like backup software and anti-virus etc.
Can anyone point me in the direction of a complete list of either the file versions above for sqlservr.exe or some collection of files, so that I can positively identify which files make up the different versions of SQL Server, including the free desktop edition (Not sure what the correct name is for it). That way I can create all the different software titles and allocate licenses against them. If I don't get this right, it looks like I have loads of unlicensed software on the servers.
Thanks for your help guys.
ASKER
Hi acperkins,
I know what version of SQL server I have on each server or at least I know the ones that require licenses e.g. not MSDE. The problem I'm having is finding a way to let my helpdesk software identify the version from the files it scans.
I was under the impression that you could tell the version from 'sqlservr.exe'. My helpdesk software picks this file up and shows all the different versions of the file. In theory I would just need to put all the SQL Server 2005 files together for example and create a software title called 'SQL Server 2005'. Whenever the helpdesk software finds those version of the file on one of my servers, it would allocate a license to it.
I have manually gone round a few of the servers, right-clicking the file and writing down its version number.
It was all going well until I discovered that my licensed SQL Server 2005 64 bit SQL server had the same 'sqlservr.exe' file version (2005.90.3042.00) as one of my other Servers, which has SQL 2005 MSDE installed for anti-spam software. This would cause my helpdesk software to allocate a license against a server that didn't require one and make it look as though I was using unlicensed software if we were audited by Microsoft.
Is there another way of identifying SQL server from file versions installed anywhere on the server?
I know what version of SQL server I have on each server or at least I know the ones that require licenses e.g. not MSDE. The problem I'm having is finding a way to let my helpdesk software identify the version from the files it scans.
I was under the impression that you could tell the version from 'sqlservr.exe'. My helpdesk software picks this file up and shows all the different versions of the file. In theory I would just need to put all the SQL Server 2005 files together for example and create a software title called 'SQL Server 2005'. Whenever the helpdesk software finds those version of the file on one of my servers, it would allocate a license to it.
I have manually gone round a few of the servers, right-clicking the file and writing down its version number.
It was all going well until I discovered that my licensed SQL Server 2005 64 bit SQL server had the same 'sqlservr.exe' file version (2005.90.3042.00) as one of my other Servers, which has SQL 2005 MSDE installed for anti-spam software. This would cause my helpdesk software to allocate a license against a server that didn't require one and make it look as though I was using unlicensed software if we were audited by Microsoft.
Is there another way of identifying SQL server from file versions installed anywhere on the server?
>>Is there another way of identifying SQL server from file versions installed anywhere on the server?<<
That is exactly what I am trying to get across to you. You need to be able to distinguish between SQL Server Editions (Enterprise, Standard, WorkGroup, Developer, MSDE, Express, Personal, etc.) as they all could potentially have the same version. One way to accomplish this is to use the SQL Server T-SQL command:
Select SERVERPROPERTY('Edition')
That is exactly what I am trying to get across to you. You need to be able to distinguish between SQL Server Editions (Enterprise, Standard, WorkGroup, Developer, MSDE, Express, Personal, etc.) as they all could potentially have the same version. One way to accomplish this is to use the SQL Server T-SQL command:
Select SERVERPROPERTY('Edition')
ASKER
Hi acperkins,
I apologise if I haven't made my self clear.
I understand that I can find the information out myself by running 'select SERVERPROPERTY ("Edition")', but that wont do me any good. I am trying to find a way for my helpdesk auditing tool to be able find the edition automatically. It only works by scanning computer/servers for any files and discovering their names and versions. It can't run SQL commands, it simply lists every file it has found on every computer it scans.
I then need to group the files that it discovers into groups and give them a collective name that describes the software e.g. 'SQL Server 2000', 'Office 2007', 'Faxmaker' etc. I then allocate how many licences I have for the software and my helpdesk application checks the difference between how many versions of that software it finds on my network and either shows I have too many or too few licences.
It all seems to be working well for me so far, apart from SQL server, because I can't find a file or group of files that distinguish one version from another.
I apologise if I haven't made my self clear.
I understand that I can find the information out myself by running 'select SERVERPROPERTY ("Edition")', but that wont do me any good. I am trying to find a way for my helpdesk auditing tool to be able find the edition automatically. It only works by scanning computer/servers for any files and discovering their names and versions. It can't run SQL commands, it simply lists every file it has found on every computer it scans.
I then need to group the files that it discovers into groups and give them a collective name that describes the software e.g. 'SQL Server 2000', 'Office 2007', 'Faxmaker' etc. I then allocate how many licences I have for the software and my helpdesk application checks the difference between how many versions of that software it finds on my network and either shows I have too many or too few licences.
It all seems to be working well for me so far, apart from SQL server, because I can't find a file or group of files that distinguish one version from another.
If the auditing tool you are using can only retrieve the version and cannot run any Windows command (let alone SQL command), then quite simply you are out of luck.
ASKER
Do you know of an auditing tool that can run SQL queries?
I am afraid I do not.
ASKER
Ok, thanks for the help. I'll leave the call open for a few days, just in case anyone else lokks at it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
thinking outside the square for a second, could you not use a web server to run the query and output the results to a text file then have the audit software record the details of the text file?
trimblenet,
I am not sure if you noticed, but this question is nearly 2 years old.
I am not sure if you noticed, but this question is nearly 2 years old.
Select SERVERPROPERTY('Edition')