How to discover SQL Server instances, SSRS, SSAS and SSIS services across the network

Applies to

ApexSQL Manage

Summary

This article covers basic information about the process of discovering SQL Server instances, and its services across the network.

Description

The first step of this process, upon starting the SQL manage instance tool, is to head over to the Inventory tab and click on the Scan button. This action will initiate the Scan window:

The Scan window

In the Scan window, all the scan process options can be set.

The SQL Server checkbox under the Include area is ticked by default. Additionally, SQL manage instance tool offers an option to search across the network for SQL Server instance services: SSAS, SSIS, and SSRS

Including SQL Servers, SSAS, SSIS, SSRS services into scan process

There are two different search options within the SQL Server instance tool: domain and IP scan.

Domain scan

A DNS or Domain Name System Server is a system that converts computer names into IP addresses. Namely, it is a distributed hierarchical composition of Internet Servers containing information about domain names. It can be said that a DNS Server is a database that contains all the names and IP addresses of its individual computers.

To further explain, when the IP address is provided, the signal is sent to the domain controller that further includes all different machines, i.e., all IP addresses which are part of that particular domain will also be scanned.

For example, to search for SQL Server instances, SSAS and SSIS services on a remote machine that is a part of a specific domain do the following:

  • check the SQL Server, SSAS and SSIS checkboxes in the Include area of the Scan window,
  • select a name of credential from Display name drop-down list
  • provide an IP address of domain controller in the DNS Server field
  • provide the user and its password
  • click the OK button to initiate the scan process

Search by domain via SQL manage instance tool

Quick tip icon

Quick tip:

If the machine where the SQL manage instance tool is installed on, is part of a domain then all domain settings in the Scan window can be left blank thus having no impact on search results

After scanning is complete, under the Register window, the scan results will be displayed in four columns:

  • Server – the name of the discovered SQL Server instance (service)
  • Type – SQL Server or service (SSAS, SSIS, SSRS)
  • Version number – the version number of the listed SQL Server
  • Find method – the scan methods that are used in the scan process

To connect to any of the listed SQL Server instances and its services via SQL manage instance tool, check them in the grid, check the Ask for credentials for failed logins option since the credentials are required for the remote communication, and click the OK button:

Scan results shown in the Register window

Provide valid credentials in the Edit connection window, and click the OK button:

Connecting to remote SQL Server using SQL Server authentication

This action will open an additional window where a separate folder for the chosen SQL Server instance can be assigned:

Select the folder to assign to SQL Server

After this, the chosen SQL Server instances, SSAS and SSIS services will be displayed in the main grid of the Inventory tab with all necessary information spread across the following columns:

  • Server – the name of the SQL Server or its service
  • Type – SQL Server or service (SSAS, SSIS, SSRS)
  • Version – the version of the SQL Server
  • Edition – the edition of the connected SQL Server (Enterprise, Standard, Express or Developer)
  • IP – the IP address the SQL Server, or service, was discovered on
  • Port – the port SQL Server, service, was identified on
  • Build number – the information about the build number of the SQL Server instance
  • Latest update – the information and a link to the official webpage for the selected SQL Server instance
  • Status – the status of the selected SQL Server instance (Online or Offline)

SQL Server list shown in the grid

IP scan

When scanning the network by using the IP range option, there are three scan methods to choose from in the SQL manage instance tool, and each of those gives different scan results:

To search for SQL Server instances, SSAS, SSIS and SSRS services across the network using IP range scan and WMI, SCM and SSRS scan methods, do the following:

  • select the Other radio button and check the IP option
  • in the From field enter the starting IP address of the range
  • in the To field provide the ending IP address of the range
  • choose all three scan methods

Search  for SQL Servers, SSAS, SSIS and SSRS services via SQL manage instance tool

The following are the results of the last scan.

Quick tip icon

Quick tip:

It is mandatory to have valid credentials to make a connection between the SQL manage instance tool and any of the remote machines that host discovered SQL Server instances

Results of IP scan shown in the Register window

Quick tip icon

Quick tip:

If valid credentials are provided, and some SQL Servers still aren’t accessible, there is a possibility they are not configured to allow the remote connections

To add SQL Server instances from the last performed scan, under the Inventory tab, click on the Add button and from the drop-down list choose the Add from last scan option:

Add from last scan option in SQL manage instance tool

Additionally, there is a neat option to scan the local machine by using its IP address and, in result, allowing fast connection to local SQL Servers and its further managing. For this example, do the following:

  • select the Other radio button and check the IP option
  • in both, From and To fields, enter the IP address of the local machine
  • choose the SSRP scan method and click the OK button

IP search for SQL Servers with SQL manage instance tool

Quick tip icon

Quick tip:

There is also an option to search SQL Server instances across the network by ports

Port range scan via SQL manage instance tool

After the scan process is finished, connect to all discovered SQL Server instances by using the Windows authentication, in other words, the Log in automatically using Windows authentication option must be checked:

Results of local scan shown in the Register window

In the next step, choose the available or create a custom folder to allocate SQL Server instances:

Create custom folder to assign SQL Server to

This way, under the Home tab, all connected SQL Server instances in the Server explorer will be organized by the assigned folders:

Server explorer under the Home tab

Main grid

After the connection between the SQL manage instance tool and the discovered SQL Server instances, SSAS, SSIS, and SSRS services, the main grid of the Inventory tab is populated with all its details.

To export SQL Server instances and its services into a list, check them in the main grid and click the Export to list option. After the Export window is opened provide the name for the list and click the Save button:

Export SQL Servers to a list within SQL manage instance tool

This way, the list can be later easily imported from the same folder location by using the Import from list option under the Inventory tab. After the Import window is opened, select the saved list and click the Open button:

Import SQL Servers from a list in the Import wondow

Scan results from the main grid can be further easily exported to DOCX, PDF, and XLSX file formats using the Export option under the Inventory tab:

SQL Servers exported to DOCX, PDF and XLSX file formats in the SQL manage instance tool

All the data displayed in the main grid can be grouped by its columns for a better visual experience. There are two different approaches when sorting scan results:

  • right-click on the header of the preferred column and from the context menu choose the Group by this column option or
  • drag and drop the desired column into a group area:

    Two ways of sorting grid data by its columns

The following image is the depiction of all the grid data that is grouped by the Type and IP columns, respectively:

SQL Servers grouped by Type and IP grid columns in the SQL manage instance tool