locked
SQL Server Edition Issue --Please Help RRS feed

  • Question

  •  

    Hello Friends,

    I am trying to get a list of SQL edition (Enterprise, standard etc.) for multiple servers under same domain. I can get individual sql server edition info through serverproperty but not sure how to get this info for multiple servers without running multiple queries against the databases--Please help


    • Edited by VATiger Friday, February 10, 2012 7:28 PM
    • Moved by Naomi N Friday, February 10, 2012 8:07 PM Better in this forum (From:Transact-SQL)
    Friday, February 10, 2012 7:28 PM

Answers

  • or you can use the browser service..
     
    If you use console you probably need to load the type, but ISE will load it
    for you...
     
    ([System.Data.Sql.SqlDataSourceEnumerator]::Instance).GetDataSources()
     

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Bigteddy Monday, February 13, 2012 2:50 PM
    • Marked as answer by Yan Li_ Monday, February 20, 2012 1:17 AM
    Monday, February 13, 2012 1:51 PM
  • Sup VA,

    The following is a quick and dirty way of getting that information.

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $servers = @('fisintdbadb2','fisintdbadb1')
    foreach ($server in $servers) {
     $instance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server
     $instance | Select Name, Edition
    }

    Change the instance names assigned to "$servers" to whatever machines you're looking up.

    Quick summary of how the script works: (1) load the SMO assembly (what SSMS uses to manage SS objects); (2) Identify server instances to query; (3) For each instance, connect and get it's name and edition.

    Good luck bro.

    ~CA

    A-


    Adam

    • Proposed as answer by Naomi N Friday, February 10, 2012 9:06 PM
    • Marked as answer by Yan Li_ Monday, February 20, 2012 1:17 AM
    Friday, February 10, 2012 9:00 PM
  • you can use following script to find SQL edition for multiple servers

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $servers = Get-Content D:\Temp\ServerList.txt
    foreach ($server in $servers) {
     $instance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server
     $instance | Select Name, Edition
    }

    ServerList.txt file consist list of servers

    Server1

    Server2

    • Proposed as answer by Naomi N Sunday, February 12, 2012 12:41 AM
    • Marked as answer by Yan Li_ Monday, February 20, 2012 1:17 AM
    Friday, February 10, 2012 9:47 PM
  • I don't know of any way to "discover" instances of SQL Server. Hence the need for a list of servers. You would need to query AD for all computers (not just servers, since SQL Server can be installed on computers with a workstation OS), then connect to each to check if SQL Server is installed. Perhaps best would be to use code similar to what Bhavik suggests, but ForEach on all computers and trap the error if there is no instance. However, you might need to also account for computers being unavailable.


    Richard Mueller - MVP Directory Services

    • Marked as answer by Yan Li_ Monday, February 20, 2012 1:17 AM
    Saturday, February 11, 2012 4:19 PM

All replies

  • The best solution here will be in using PowerShell script. I suggest to ask this question in PowerShell forum, if you'd like, I can move your thread there.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, February 10, 2012 7:31 PM
  • Other than powershell, you can also use Cenrtal Management Server in Management Studio.

    But PS is easiler.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Friday, February 10, 2012 7:40 PM
  •  

    Thank you so much Naomi ! Would you able to tell me how to move this thread to Powershell Forum.. I tried to find this info but unfortunately I could not get it.

    Also, Thank you Balmumand .. Can you explain a little bit about this method

    Friday, February 10, 2012 7:58 PM
  • http://msdn.microsoft.com/en-us/library/bb895144.aspx (Administering Multiple Servers Using Central Management Servers)

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Friday, February 10, 2012 8:02 PM
  • This forum is here http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/threads/ 

    I will try to move your thread there, but so far I could not find this forum when I use Move functionality. There are too many forums...


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by VATiger Friday, February 10, 2012 8:34 PM
    • Unmarked as answer by VATiger Friday, February 10, 2012 8:39 PM
    Friday, February 10, 2012 8:06 PM
  •  

    Naomi thank you so much once again for all your help ! I closed this thread by mistake but started  again

    Thank you balmukand but it looks like Central Management Server  is a new functionality in SQL2008 but here i am still using sql 2005.


    • Edited by VATiger Friday, February 10, 2012 8:42 PM
    Friday, February 10, 2012 8:37 PM
  • Sup VA,

    The following is a quick and dirty way of getting that information.

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $servers = @('fisintdbadb2','fisintdbadb1')
    foreach ($server in $servers) {
     $instance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server
     $instance | Select Name, Edition
    }

    Change the instance names assigned to "$servers" to whatever machines you're looking up.

    Quick summary of how the script works: (1) load the SMO assembly (what SSMS uses to manage SS objects); (2) Identify server instances to query; (3) For each instance, connect and get it's name and edition.

    Good luck bro.

    ~CA

    A-


    Adam

    • Proposed as answer by Naomi N Friday, February 10, 2012 9:06 PM
    • Marked as answer by Yan Li_ Monday, February 20, 2012 1:17 AM
    Friday, February 10, 2012 9:00 PM
  • Can you enhance this script to discover installed servers? I just tried it with 1 server and it worked great.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, February 10, 2012 9:07 PM
  • you can use following script to find SQL edition for multiple servers

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $servers = Get-Content D:\Temp\ServerList.txt
    foreach ($server in $servers) {
     $instance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server
     $instance | Select Name, Edition
    }

    ServerList.txt file consist list of servers

    Server1

    Server2

    • Proposed as answer by Naomi N Sunday, February 12, 2012 12:41 AM
    • Marked as answer by Yan Li_ Monday, February 20, 2012 1:17 AM
    Friday, February 10, 2012 9:47 PM
  • I don't know of any way to "discover" instances of SQL Server. Hence the need for a list of servers. You would need to query AD for all computers (not just servers, since SQL Server can be installed on computers with a workstation OS), then connect to each to check if SQL Server is installed. Perhaps best would be to use code similar to what Bhavik suggests, but ForEach on all computers and trap the error if there is no instance. However, you might need to also account for computers being unavailable.


    Richard Mueller - MVP Directory Services

    • Marked as answer by Yan Li_ Monday, February 20, 2012 1:17 AM
    Saturday, February 11, 2012 4:19 PM
  • or you can use the browser service..
     
    If you use console you probably need to load the type, but ISE will load it
    for you...
     
    ([System.Data.Sql.SqlDataSourceEnumerator]::Instance).GetDataSources()
     

    Justin Rich
    http://jrich523.wordpress.com
    PowerShell V3 Guide (Technet)
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Bigteddy Monday, February 13, 2012 2:50 PM
    • Marked as answer by Yan Li_ Monday, February 20, 2012 1:17 AM
    Monday, February 13, 2012 1:51 PM
  • Justin, that's a great script. It found 3 SQL instances in my domain, all on different computers.


    Richard Mueller - MVP Directory Services

    Thursday, February 16, 2012 8:00 PM