none
Server Inventory and SQL 2005

    Domanda

  • Hello,

    I am currently working on adapting the script found here:

    Server and SQL inventory

    The premise of the SQL inventory assumes that you know the server instances before hand.

    We are using SQL 2005, so I am able to use the "ManagedComputer" option, mentioned in the article.

    I have taken the snipet from here to look for SQL information on our ERP server: Enumerating SQL Server Instances

    When I run that set of code, I receive the following output:

    PS C:\Documents and Settings\administrator.JUICE> $m.ServerInstances

    ServerProtocols : {Np, Sm, Tcp, Via}
    Parent          : Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
    Urn             : ManagedComputer[@Name='SQLSERVER']/ServerInstance[@Name='MSSQLSERVER']
    Name            : MSSQLSERVER
    Properties      : {}
    UserData        : 
    State           : Existing

    If I just type $m at the prompt, I receive this:

    PS C:\Documents and Settings\administrator.JUICE> $m

    ConnectionSettings : Microsoft.SqlServer.Management.Smo.Wmi.WmiConnectionInfo
    Services           : {MSFTESQL, MSSQLSERVER, SQLBrowser, SQLSERVERAGENT}
    ClientProtocols    : {np, sm, tcp, via}
    ServerInstances    : {MSSQLSERVER}
    ServerAliases      : {}
    Urn                : ManagedComputer[@Name='SQLSERVER']
    Name               : SO-AFS
    Properties         : {}
    UserData           : 
    State              : Existing

    For my purposes, the SQL infomation is not critical; however, as the new admin, this would be very handy to have for the person who may replace me.

    I know I can modify the servers.txt file, once I have done the above, to add the server instances; however, this would require touching every server to test if there is SQL installed.  Again, in my environment, not painful, just tedious, but since we have the name, how do I go about extracting only the ServerInstance Name to use in the inventory script? How would I modify his function for the revision?

    As always, thank you for your help.

    lunedì 25 giugno 2012 20:36

Risposte

  • It looks like Allen's code requires you generate a new text file with:

    servername,ServerInstance

    You have the added require of ensuring the server is pingable and is a server OS. Here's a simple script to do that. It handles multiple instances:

    param($computername)
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
    
    
    function Get-ServerInstance
    {
        param($computername)
    
        $instances = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $computername
        $instances | foreach { $_.ServerInstances } | select @{name='ComputerName';expression={$computername}},
             @{name='ServerInstance';expression={if ($_.Name -eq 'MSSQLSERVER') { $computername } else { "{0}\{1}" -f $computername,$_.Name }}}
    }
    
    if ( (Test-Connection -ComputerName $computername -Count 2 -Quiet) -and (Get-WmiObject Win32_OperatingSystem -computername $computername).Caption -match 'Server' ) {
        Get-ServerInstance $computername
    }

    Save script as Get-SqlInstance.ps1 and use your list of computer names as input:

    Get-Content ./mycomputers.txt | foreach { ./Get-SqlInstance.ps1 $_ } | Export-Csv -NoTypeInformation ./sqlinstances.csv 

    Keep in mind only SQL Servers will have SqlWMI installed, so you'l get errors running against non-SQL Servers.

    venerdì 29 giugno 2012 14:55

Tutte le risposte

  • I collected SQL instance names by quering the SQL service name.  The service name takes the format of "MSSQL${instance_name}".

    $SERVERS = get-content servers.txt

    $INSTS=@()

    foreach ($SERVER in $SERVERS) {$SQLSVCS = Get-WmiObject Win32_Service -computername $SERVER | where {$_.name -like "MSSQL$*" -and $_.state -eq "running"}
    foreach ($SQLSVC in $SQLSVCS) {$INSTS += "$($SERVER)\"+$SQLSVC.name.replace("MSSQL$","")}
    }

    $INSTS

    lunedì 25 giugno 2012 21:47
  • I'll use the following to get the instance name:

    $m.Services| where {$_.Type -eq 'SqlServer'} | foreach { $_.PathName -replace '[^-].+\s{1}-s',"" }

    martedì 26 giugno 2012 00:12
  • Are both of you finding the instance and then modifying the text file, thereby suggesting that I run a SQL enumeration script to modify my servers.txt and then run my server inventory script.

    But, why modify the text file? Why can't we parse the instance name and then pass that to the SQL inventory function?

    [I know, more moving parts, but I feel that this ought to be a tweak in the script.  Here is where my thought process was going: 1) Just read in servers from text file, and pass to loop to begin processing, 2) I have SQL 2005, so I can use ManagedComputer (according to Mr. White) to obtain instance name (appears other options presented by wallst360 and cmille19 will also lead to satisfactory results),  3) Pass found SQL instance(s) to SQL inventory function.  Is this logic flawed? I admit I am more of a networking guy than a scripter, so sometimes the method that appears to be KISS for me would have many of you scripting pros hanging from the ceiling.]

    mercoledì 27 giugno 2012 15:44
  • The Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer class doesn't find computers with SQL Server installed rather given a computer name it will among other things list the SQL Server instances installed on that machine. So if you intent is as follows, then yes you could do that:

    1. List of servers

    2. Enumerate and return all SQL Server instances

    3. Write output to another text file

    mercoledì 27 giugno 2012 17:40
  • As cmille19 mentioned, the SQL SMO needs a full instance name, it cannot do anything with just a server name.  If all you have to start with is a list of server names, you will have to use that to enumerate the instance names, and then feed that into your SQL data collection.  You can either do that in one step (use server names to create an array variable of instance names and feed that to your data collection) or two (use a server name text file to generate an instance text file, and then feed that to your script).
    mercoledì 27 giugno 2012 22:08
  • As cmille19 mentioned, the SQL SMO needs a full instance name, it cannot do anything with just a server name.

    SMO supports a static method to enum all SQL Server instances found in network by using UDP broadcast:
    [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($false);

    See http://gallery.technet.microsoft.com/scriptcenter/Create-Inventory-of-SQL-19d61963 for an example.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    giovedì 28 giugno 2012 04:58
  • Your mileage may vary with that method unless you have a very simple network which allows broadcast traffic. In my environment with multiple subnets and firewalls between network segments--this method would be very inaccurate.

    If his goal is to find SQL Servers on the network which it isn't entirely clear then I would recommend one of two methods:

    1. If you use System Center and have 100% System Center cover (every server build has System Center installed). You can query the System Center database to find servers with SQL Server. I've got a blog post about this:

    http://sev17.com/2008/11/inventory-sql-server-databases-with-powershell/

    2. Quest makes a tool for scanning your network for SQL Servers. Note: You'll want to let your network/security people know you plan on doing this:

    http://www.quest.com/landing/?ID=1305

    giovedì 28 giugno 2012 13:42
  • The purpose of the inventory is two fold:

    1) Find what I am managing, and what it is
    2) Server inventory so that when we start having a discussion around moving to a hosted cloud architecture, we know what our needs are.

    I have been with this company since January, and full-time since May.  As previously stated, my skill set is predominantly network centric; however, I have had some exposure to basic server administration.

    The current network design is relatively flat, with one remote site, that is irrelevant for this discussion (I will perform inventory on that location at a later date).

    In the script that I found, in the post by Mr. White, he already knew the SQL server instance, and was running SQL 2000, so he had to modify his text file to accommodate.  I do not know the server instance names, I actually only know of one server that is running SQL, our ERP server, and it is SQL 2005.  Mr. White states in his post that if you are using SQL 2005 or newer, you can use ManagedComputer to find your instances to pass to the SQL inventory function (alternative methods for finding the instance has been noted above).  I understand that the server and instance name have to be passed to the function, and we have the server name from the script, hence why I have only picked on the instance name in earlier posts.  As the script is currently written, it expects to get the instance name from the text file.  Yes, I can run an enumeration script that will modify the text file (as it appears is being done in the methods above), and I was wondering if it would be possible to just pass that new "knowledge" to the function, without having to rewrite, and reread, the text file.

    With all of that said, here are my goals from this script:
    1) Inventory Servers
    2) Is SQL installed:
    a. Yes -- what instance(s), get SQL inventory

    From this, the script logic would have been modified slightly:
    1) Load server list
    2) Test server reachability
    a. Server reachable:
    1. Get inventory
    b. Otherwise, server unreachable
    3) Is SQL installed on the server
    a. Get SQL inventory

    Where am I at now:
    > I can get the server inventory and find a SQL instance on a server

    Script modifications to be made (at present):
    > Modify initial foreach so that it only processes servers
    > Add SQL instance finding capability and assign to variable to pass to SQL inventory function

    Questions/Confussion:
    1) How to account for the possibility of more than one instance on a server?
    2) How to account for servers that may not have SQL installed (as I imagine without a test, there will be errors when this section is processed with a server that does not have SQL installed)?

    As always, thank you for helping out a newb (these past two weeks have been my introduction to powershell).

    giovedì 28 giugno 2012 14:58
  • It looks like Allen's code requires you generate a new text file with:

    servername,ServerInstance

    You have the added require of ensuring the server is pingable and is a server OS. Here's a simple script to do that. It handles multiple instances:

    param($computername)
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
    
    
    function Get-ServerInstance
    {
        param($computername)
    
        $instances = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $computername
        $instances | foreach { $_.ServerInstances } | select @{name='ComputerName';expression={$computername}},
             @{name='ServerInstance';expression={if ($_.Name -eq 'MSSQLSERVER') { $computername } else { "{0}\{1}" -f $computername,$_.Name }}}
    }
    
    if ( (Test-Connection -ComputerName $computername -Count 2 -Quiet) -and (Get-WmiObject Win32_OperatingSystem -computername $computername).Caption -match 'Server' ) {
        Get-ServerInstance $computername
    }

    Save script as Get-SqlInstance.ps1 and use your list of computer names as input:

    Get-Content ./mycomputers.txt | foreach { ./Get-SqlInstance.ps1 $_ } | Export-Csv -NoTypeInformation ./sqlinstances.csv 

    Keep in mind only SQL Servers will have SqlWMI installed, so you'l get errors running against non-SQL Servers.

    venerdì 29 giugno 2012 14:55
  • cmille19,

    Thank you for the reply.


    venerdì 29 giugno 2012 19:39