Get List of SQL Server Instances with SMO
By : Kasim Wirama, MCDBA, MCITP
Sometimes there are requirements where you need to get list of SQL Server instances. For example you need to do database health check on each databases of each SQL Server instances. First you need to know SQL Server instance before you could get list of databases of the SQL Server instance. There are many ways to get list of SQL Server instance. You could get from running SQLCmd dos-prompt command here SQLCmd –Lc. If you need to get other attributes of a SQL Server instance such as SQL Server version, whether the instance is local instance or remote instance, and whether the instance is clustered instance then you could use SMO as I explain below.
SMO supplies one excellent class called SMOApplication. The class has 1 static method : EnumAvailableSqlServer. The method has one Boolean parameter named localOnly. Specify it to true, the method will return all SQL Server instances that run on your local machine only. Specify it to false, the method will return all SQL Server instances than run on your local and remote machine. The return result is ADO.NET datatable.
Let’s implement SMO into .NET project here. Fire up Visual Studio 2008. Add 4 SMO references (Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoExtended, Microsoft.SqlServer.Management.Sdk.Sfc). Switch on code behind of Form1 user interface and put 2 SMO-related namespace here :
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
On Form1 user interface, draw 1 button and 1 DataGridView. Double click on the button so that Visual Studio will automatically create “click” event handler for the button. Inside event handler, you specify the code below :
DataTable dt = SmoApplication.EnumAvailableSqlServers (true);
DataGridView1.DataSource = dt;
I would like to get list of local SQL Server so I pass true value into EnumAvailableSqlServers method. The result is shown on the screenshot below :
I find out that creating SMO application is easy and give additional security layer to your SQL Server.