When working with SQL Server, there are often situations where you need to retrieve the fully qualified domain name (FQDN) of the server. This information can be useful for various tasks, such as configuring database mirroring or setting up network connections. In this article, we will explore a simple and efficient way to obtain the FQDN using PowerShell.

Traditionally, one might resort to opening a connection to the SQL Server and running a T-SQL query to retrieve the FQDN. However, this approach can be cumbersome and unnecessary. Instead, we can leverage the power of PowerShell to query the Domain Name System (DNS) directly.

The first step is to import the system.net.dns namespace, which provides the necessary functions for querying DNS. Once imported, we can use the GetHostEntry function to retrieve the FQDN. The syntax is as follows:

[system.net.dns]::GetHostEntry("Machine Name").HostName

By replacing “Machine Name” with the appropriate server name, we can retrieve the FQDN of the SQL Server. For example, if we want to obtain the FQDN of the local machine, we can use:

$FQDN = [system.net.dns]::GetHostEntry($Machine).HostName

In the context of a database mirroring script, we may need to call the GetHostEntry function multiple times, depending on whether we are configuring a witness. In such cases, we can assign variables to the function for each server, passing in the appropriate server names. These variables can then be used to create the TCP connection strings for assigning the mirroring partners and witness.

To simplify the process, we can create a reusable scriptlet that accepts a machine name as a parameter. If no machine name is provided, it will default to the name of the local machine. Here is an example:

Param ($Machine)
if (!$Machine) {
    $Machine = read-host "Enter machine name"
}
if (!$Machine) {
    $Machine = get-content env:Computername
}
$FQDN = [system.net.dns]::GetHostEntry($Machine).HostName
return $FQDN

With this scriptlet, obtaining the FQDN becomes a straightforward process. Simply execute the script, providing the desired machine name if necessary, and the FQDN will be returned.

By utilizing PowerShell’s ability to query DNS, we can easily retrieve the fully qualified domain name of a SQL Server without the need for complex T-SQL queries or opening unnecessary connections. This approach not only simplifies the process but also improves efficiency and reduces the risk of errors.

Next time you find yourself needing the FQDN of a SQL Server, give PowerShell a try. You’ll be amazed at how simple and effective it can be!