March 10, 2009 at 11:54 am
I work for a Software Asset Management consulting firm that deals with many companies using Microsoft SQL Server. Since MS did not do a great job of identifying the SQL Versions and especially Editions using normal discovery tools I am looking for a way to validate SQL deployments and usage. There are many challenges with this process:
1) True SQL server installations (many discovery tools report SQL server tools as full SQL installations
2) Edition (Standard, Enterprise)
3) Processor or Client Access License based
4) Number of instances
5) Number of users (CALs). This is very important.
If there is anyone who knows how to get this information accurately I would really appreciate knowing. We have have tried different scripts but have not come up with a good solution as yet.
March 10, 2009 at 12:05 pm
If you run the code below in each instance you will get all the information for number two and four, the others you may need to talk to Microsoft. Number one is also easy to find out go into configuration manager and try to enable features it will error out if the feature is not installed. How to access all the instances on the network enable the browser service and register all.
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
http://support.microsoft.com/kb/321185
Kind regards,
Gift Peddie
March 10, 2009 at 12:37 pm
Thank you very much for your help. I am hoping to find scripts that will allow me to get the rest of the information. I would like to combine them if possible to get a total picture.
March 10, 2009 at 4:34 pm
i think the windows registry would be the best place to look
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 11, 2009 at 6:26 am
If you run "osql -L" from the command line (in the [sql]\tools\binn directory) this will return a list of SQL Servers that were discovered. Using this: "osql -L > c:\servers.txt" will pipe this information out to a file. You could create an Integration Services package, .NET application, or even a PowerShell script around this to pick up each row and execute the sql against the server to grab the detailed information. This article might lead you down the PowerShell path: http://www.simple-talk.com/sql/database-administration/let-powershell-do-an-inventory-of-your-servers/
Cheers,
Brian
March 11, 2009 at 7:03 am
Thank you. I actually did look but not all the information is in there.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply