Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

How to Modify the Global DNS Configuration on Servers Using PowerShell


By:   |   Last Updated: 2018-04-20   |   Comments   |   Related Tips: More > PowerShell

Problem

You are administering servers with SQL Server instances. Suddenly your phone starts to ring and at the other end of the phone is the software architect claiming that his applications cannot connect to the SQL Server instances you are administering. After digging a little bit he tells you that if he uses the IP address of the servers he is able to connect. It's obvious that the problem has something to do with DNS settings. So, you fix the DNS issue on one of your servers and the software architect can connect to the database servers using the server name, but now you should apply this configuration to all your other servers. In this tip I will show you how it can be done using PowerShell.

Solution

If I have to bet on an issue every IT professional has faced, it will be a DNS related one; probably not only at work but even in your family environment. It is very common that people confuse a DNS failure and report that there is no network/internet connection. I remember one day, a major ISP provider in my country had their DNS servers down for a while and it was on the news. But what the news didn’t tell was that you can setup your network interface to use the IP 8.8.8.8, which is the Google DNS server as the DNS server.

Beyond this example, it is clear that DNS resolution plays a key role not only for the general public, but for enterprises too. Furthermore, in corporate environments managing name resolution can be pretty complicated because of the presence of different domains, firewall restrictions and DMZ’s.

Changing DNS Settings

We know that in order to change the advanced DNS settings we must open the network adapter properties screen, and then select the internet protocol version according to our environment which usually is IPv4 (1) and press the Properties (2) button as you can see on the next screen capture. When the properties window opens you should hit the “Advanced…” (3) button.

Network Properties - Description: Follow the number sequence to open advanced DNS settings.

Finally, when the Advanced TCP/IP settings window opens you can hit the DNS tab and you will see a window like the one following. I enclosed in a red box the items that are global to all TCP/IP connections, so if you have more than one network adapter then changing those setting in one connection will be enough to impact the whole system.

advanced settings

Just for information purposes I added a table below with a brief description of the options in the previous screen.

Item Description
DNS server addresses, in order of use It's an ordered list of the name servers your machine will contact for DNS resolution.
Append primary and connection specific DNS suffixes When checked, on name resolution requests that are not FQDN the DNS suffix of the machine will be appended to the host name
Append parent suffixes of the primary DNS suffix This does the same as the above, but using parent suffixes instead.
Append these DNS suffixes (in order) This is a list on which you can add additional suffixes to query for name resolution.
DNS suffix for this connection Retrieves global DNS client settings like the suffix search list.
Register this connection's addresses in DNS When checked the computer will attempt to dynamically create DNS records by registering itself in the zone associated with the connection. This will either be the Active Directory domain the computer is member of; or the DNS suffix listed in the above setting if you have the next setting checked.
Use this connection's DNS suffix in DNS registration Specifies whether DNS dynamic update is used to register the IP addresses and the connection-specific domain name of this connection. The connection-specific DNS name of this connection is the concatenation of the computer name (which is the first label of the full computer name) and the DNS suffix of this connection. The full computer name is specified on the Computer Name tab (available in System in Control Panel). If the “Register this connection's addresses in DNS” check box is selected, this registration is in addition to the DNS registration of the full computer name. The local setting is used only if the associated Group Policy is disabled or unspecified.

Now that we know how to modify advanced DNS configuration on one server, what if we must do the same on all the servers hosting SQL Server instances? I have a way of thinking that if I have to do the exact same task more than twice, then that task can be automated.

Using PowerShell to Change DNS Settings

PowerShell began its days as a task automation and configuration management framework that included a command-line shell and its own scripting language. It was introduced in 2006 for Windows XP as an attempt from Microsoft to get rid of the old MS-DOS inherited shell CMD.EXE. As time passed, PowerShell has evolved by adding more features.

Nowadays PowerShell is an open source multiplatform tool which is available for Linux, MacOS and of course Windows. In other words, the scripts of this tip can be used on those platforms.

In order to change the global DNS settings, we are going to use the cmdlet functions of DnsClient object which you can list by using the following command on PowerShell:

get-command *-DnsClient*			

The execution of the previous line will return results like on the next screen capture.

DnsClient - Description: Here we have a list of functions from the object DnsClient.

On the next table I add a brief description of each of those functions.

Source: https://docs.microsoft.com/en-us/powershell/module/dnsclient/?view=winserver2012-ps and https://docs.microsoft.com/en-us/powershell/module/dnsclient/?view=win10-ps

Function Description
Add-DnsClientNrptRule Adds a rule to the Name Resolution Policy Table (NRPT).
Clear-DnsClientCache Clears the DNS client cache. Just like executing ipconfig /flushdns.
Get-DnsClient Gets details of the network interfaces configured on a specified computer.
Get-DnsClientCache Retrieves the contents of the DNS client cache. Just like executing ipconfig /displaydns
Get-DnsClientGlobalSetting Retrieves global DNS client settings like the suffix search list.
Get-DnsClientNrptGlobal Gets the NRPT global settings.
Get-DnsClientNrptPolicy Gets the NRPT configured on a computer.
Get-DnsClientNrptRule Retrieves the DNS client NRPT rules.
Get-DnsClientServerAddress Gets DNS server IP addresses from the TCP/IP properties on an interface.
Register-DnsClient Registers all of the IP addresses on the computer onto the configured DNS server.
Remove-DnsClientNrptRule Removes the specified DNS client NRPT rule.
Set-DnsClient Sets the interface-specific DNS client configurations on the computer.
Set-DnsClientGlobalSetting Sets the DNS client global, non-interface specific, settings.
Set-DnsClientNrptGlobal Modifies the global Name Resolution Policy Table (NRPT) settings.
Set-DnsClientNrptRule Modifies a DNS client Name Resolution Policy Table (NRPT) rule for the specified namespace.
Set-DnsClientServerAddress Sets DNS server addresses associated with the TCP/IP properties on an interface.

Now for example, if we aim to add a new suffix to the search list the following code will do the trick.

$dnsCGSetting = Get-DnsClientGlobalSetting
$dnsCGSetting.SuffixSearchList += "otherdomain"
 
Set-DnsClientGlobalSetting -SuffixSearchList $dnsCGSetting.SuffixSearchList
Clear-DnsClientCache			

The first line saves the resulting array of the Get-DnsClientGlobalSetting cmdlet into object dnsCGSetting. On the next line we are concatenating the new suffix on the string “otherdomain” into the SuffixSearchList property of the dnsCGSetting object resulting from the first line. The following two lines set the suffix search list with the contents of the SuffixSearchList property of dnsCGSetting object and then clean the DNS cache.

The previous script works for one server. I mean, if you need to change this settings on many servers by using the previous script you will need to log on each server to run the script. Fortunately, PowerShell offers us tools to automate what would be a tedious task and make it easier.

If we aim for automation we must look at Invoke-Command cmdlet which allows us to run a given script against another server or list of servers.

The easiest way for executing commands on another server is by using Invoke-Command is to save the PowerShell script you want to execute into a file, in this case let’s create a file with the contents of the previous script and save it as script.ps1. As a side note, when I say easiest way, I mean not just coding difficulty but also code readability.

After creating the script file script.ps1 the next step is to open a PowerShell terminal and run the following command.

Invoke-Command -FilePath c:\scripts\script.ps1 -ComputerName ([System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources().ServerName | select -uniq) 
			

The previous command loads the script specified in –FilePath parameter and executes it into the computers detailed in –ComputerName parameter.

In this case the computers detailed in –ComputerName parameter is those who hold a SQLServer instance. We can get the detail of servers running SQL Server by executing the PS command [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources().ServerName. The “| select –uniq” part of the command is a workaround when you have multiple instances running on a server, because you don’t want to add the same DNS suffix multiple times.

But getting the list of servers that way may not work as well. In order to use [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() you must have enabled the SQL Server Browser on each machine running a SQL Server instance.

Next Steps


Last Updated: 2018-04-20


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools