April 28, 2011 at 10:34 am
I understand that ListAvailableSQLServers is deprecated, but I cannot find what to replace it with.
I see that SQL-DMO is replaced with SMO, but I do not see an equivalent method. code snippet below.
Assistance greatly appreciated.
SQLDMO.NameList oNames;
SQLDMO.Application oSQLApp = new SQLDMO.Application();
oNames = oSQLApp.ListAvailableSQLServers();
April 28, 2011 at 10:47 am
judy -good question. You can derive this information, but will have to use Powershell. Aaron Nelson, aka, SQLVariant posted a blog to do just this -
http://sqlvariant.com/wordpress/index.php/2010/09/finding-sql-servers-with-powershell/
Hope this helps!
Robert Pearl
April 28, 2011 at 12:11 pm
If you want to find all the instances on the AD domain, hidden or not you can use the code which Aaron and I put together for my book "Securing SQL Server". It'll do a dump from AD for all the machines in the domain then go through the list looking for machines which have the SQL Server services installed. As long as the machine is turned on and connected to the network this script will find the services. This does need to be run by a domain admin.
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("computer")
$objSearcher.PropertiesToLoad.Add("name")
$Computers = $objSearcher.FindAll()
foreach ($machine_name in $Computers | sort computername )
{
$sql_servers = get-wmiobject -class win32_service -computer $machine_name
$sql_servers | where { $_.name -like 'MSSQL$' -or $_.name -eq 'MSSQLSERVER'} | select name
}
April 28, 2011 at 1:06 pm
Thanks Robert. Here's the code that I posted in the second blog I did on that. I find it slightly more useful although SQL Browser has some funny impact on it every once in a while:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers() | Export-Csv C:\Temp\LookWhatElseIFound.csv
@SQLvariantI have a PowerShell script[/url] for you.
April 28, 2011 at 2:34 pm
I realize this isn't showing you how to do it yourself programatically, but I've always liked using the Microsoft Assessment and Planning (MAP) toolkit. It shows me all instances, organizes them into a very detailed spreadsheet (SQL Version, service pack level, number of CPUs, edition, Windows edition/version, drives with space info, etc.)
I blogged about the tool a couple years ago when I first discovered it (pardon the excitement in the post 😉 ) Downfalls are - it has to be installed, Office 2007 needed and you need to have higher permissions (Preferably domain admin, especially if you don't know what boxes SQL is installed on) but you can always have a sysadmin run it and then you can use that Spreadsheet to list your SQL instances and even import it into a database used for database administration.
http://www.straightpathsql.com/archives/2009/02/what-sql-instances-are-installed-on-my-network/
__________________________________________________
Mike Walsh
SQL Server DBA
Blog - www.straightpathsql.com/blog |Twitter
April 28, 2011 at 2:49 pm
and yet another way..this doesn't return the "hidden" servers denny identified; just the items that are advertised via SQL browser, i think.
this is how to do it in .NET using an SMO object:
--Imports Microsoft.SqlServer.Management
--Imports Microsoft.SqlServer.Management.Smo
Dim myDt As DataTable = Smo.SmoApplication.EnumAvailableSqlServers(False)
Dim a() As DataRow = myDt.Select("", "[Server] asc")
--just so you can see the columns collection
For Each dr As DataRow In a
Debug.Print(dr!Name)
Debug.Print(dr!Server)
Debug.Print(dr!Instance)
Debug.Print(dr!IsClustered)
Debug.Print(dr!Version)
Debug.Print(dr!IsLocal)
Next
Lowell
April 28, 2011 at 2:55 pm
Anything which uses SMO will only return running instances, which are not set to hidden.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply