6. Finding SQL Server Instances on your network

 

Chapter 6 from Learn dbatools in a Month of Lunches by Chrissy LeMaire, Rob Sewell, Jess Pomfret, Cláudio Silva

Have you ever started at a new job and, after asking for the list of SQL Servers you’ll be managing, were given an incomplete list of IPs and backup locations? That’s happened to us before, which is one of the reasons we often start each new job by scanning the network for undocumented SQL Server instances. It’s important to be aware of all SQL Server instances within a network so that they can all be managed, backed up and secured.

Finding rogue SQL Servers can be challenging because of unmanaged server sprawl, inconsistent network configurations, firewall settings, and more. Further, it can be challenging because the database engine may use different ports and SQL Server as a whole is comprised of many components such as Reporting Services and Integration Services.

When DBAs are given an incomplete view of their estate, there’s often a natural progression of tasks that looks like Figure 6.1. First, you have to find your undocumented servers, then you can go to the next step of inventorying your SQL Servers and centralizing your inventory for easier management and standardization.

Figure 6.1. General progression of inheriting a SQL estate
pyramid

6.1  Background

6.1.1  Find an instance

6.1.2  Find instances using a list of targets

6.1.3  Finding SQL Servers in an Active Directory Domain

6.1.4  Finding SQL Servers in your surrounding network

6.2  Working with detailed results

6.3  OS Support

6.4 Hands-on Lab