Server Inventory and SQL 2005

Tutte le risposte

• lunedì 25 giugno 2012 21:47

   

   
  

  1

  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

  • Risposta
  • Citazione

   
• martedì 26 giugno 2012 00:12

   

   
  

  1

  I'll use the following to get the instance name:

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

  • Risposta
  • Citazione

   
• mercoledì 27 giugno 2012 15:44

   

   
  

  0

  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.]

  • Risposta
  • Citazione

   
• mercoledì 27 giugno 2012 17:40

   

   
  

  0

  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

  • Risposta
  • Citazione

   
• mercoledì 27 giugno 2012 22:08

   

   
  

  0
  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).
  • Risposta
  • Citazione

   
• giovedì 28 giugno 2012 04:58

   

   
  

  0

  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

  • Risposta
  • Citazione

   
• giovedì 28 giugno 2012 13:42

   

   
  

  0

  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

  • Risposta
  • Citazione

   
• giovedì 28 giugno 2012 14:58

   

   
  

  0

  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).

  • Risposta
  • Citazione

   
• venerdì 29 giugno 2012 14:55

   

   
  

  1

  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.

  • Contrassegnato come risposta Yan Li_Microsoft Contingent Staff, Moderator martedì 3 luglio 2012 04:28
  •  
  • Risposta
  • Citazione

   
• venerdì 29 giugno 2012 19:39

   

   
  

  0

  cmille19,

  Thank you for the reply.

  
  

  • Risposta
  • Citazione