Tag: Windows

AlwaysOn, Availability Groups, Uncategorized

The Curious Case of the Vanishing AG IP

Before you read any further, be sure to go and read Kendra Little’s fantastic post on How to Survive Opening A Microsoft Support Ticket for SQL Server or Azure SQL. There are details I won’t go into in this post, but I just wanted to note that the issues Kendra raises are not ones that only she experiences.

The Basics

Availability Groups (AGs) are a great way to handle business continuity. When they work, they work great. When they don’t work…well the documentation and tooling are rather lacking to help you get through.

One of the things with AGs is that you can have servers on different subnets. This is useful if you want to have AGs span across multiple data centers or want to perform cage migrations, or, in the case of Azure VMs, you want to have an AG that can automatically failover and isn’t at the mercy of a timeout of an Azure load balancer.

Failing over an AG to a different subnet works well, but it does require configuring your Windows Server Failover Cluster (WSFC) resource so that all of the IPs associate with the AG listener are registered (known as RegisterAllProvidersIp which is now the default). If they aren’t and you failover to a different subnet, you’re then at the mercy of your DNS TTL for the amount of time it will take for clients to connect to SQL on that new subnet (or you connect to each machine and flush its DNS cache).

With RegisterAllProvidersIp enabled, every IP address associated with the AG listener is presented and the client connection string includes MultiSubnetFailover=True (with supported client libraries) then the client will test all the presented addresses and then connect to the one that responds.

Adding a new IP to a listener for an AG is done using an ALTER AVAILABILITY GROUP MODIFY LISTENER command. This will normally update DNS appropriately but there are manual steps you can take to ensure it done. Why an extra step? Because if the IP is not registered and you failover to the new subnet, you end up with the TTL problem I mentioned earlier. A way around this is to create a new A record in DNS that points to the new IP. That way, when you failover, that IP is already in DNS and you don’t run into issues. You can create the A record either through the DNS console or through PowerShell.

The Issue

A requirement came in for a new server in a new subnet in an existing AG. A simple process and one that’s been done dozens of times in the past with a well worked SOP. In this instance, the new server was added to the WSFC, logins all added, databases restored, the instance added to the AG, and the listener modified to include it.

After getting the DNS team to manually add the new A record, a nslookup confirmed the IP appeared in the DNS record.

Great stuff! Ready to move into service then.

Except, prior to adding traffic to the new instance, the nslookup ran again and somehow the new IP had vanished from the A record. The DNS team stated they hadn’t removed it. Logs showed that none of the DBAs had executed anything to remove the IP, and yet it had vanished.

The DNS team added it back once more. Everyone validated it.

The next day it was gone once more.

The DNS admin did some looking and it showed that the AG listener computer account deleted it. Odd. So, I went digging through the SQL Server logs. Nothing there. Then I dumped the cluster logs and went digging through those.

There were some entries in the logs indicating that the WSFC was checking the IPs were valid, but only showed the IPs that already existed, and not the new one added. Looking back, this process ran every 24 hours.

Unfamiliar with this, it was time to open a ticket.

The Investigation

After explaining the issue multiple times and collecting many set of logs and and answering the same question a large number of times, we received a couple of “things to try” that included turning off RegisterAllProvidersIp (which would have caused an outage in a failover to a box on a different subnet) and to remove permissions from DNS for the AG listener (which would mean we couldn’t add new IPs using TSQL or PowerShell).

After several false starts over weeks, recreating the A records over and over again (I truly feel bad for the DNS admin who just kept his PowerShell script to hand and just hit enter once a day), we got to someone who moved past beyond reading some random web pages and gave us the first piece of useful information.

The Fix

The short version, is that after adding an IP to an AG listener, you have to restart the AG network name for it to actually pick up the change. You can do this by either failing over the AG to any other replica or offline/online the network name resource using the Cluster Manager GUI or PowerShell.

When a new IP is added to the AG listener, it’s added to the static config, but that config is not read in until the computer name is restarted. In this case, we hadn’t performed a failover or restarted that resource and so the WSFC used the cached record to validate the IPs with DNS. When it noticed that DNS had an extra IP that wasn’t in the cached configuration, it removed it.

After adding the IP once more, and performing an after-hours restart of the computer network name, the new A record remained through the next DNS check in the WSFC. After leaving it a couple more days to be sure that it wasn’t going to vanish again, the new server was added fully into service.

I asked for a link to documentation on this facet of AGs and WSFCs. Apparently there is none. So, this is just a warning note for those of you maybe adding IPs in extra subnets – restart your resources to ensure your change is picked up.

SQL

Enabling IFI on Setup in SQL Server 2016

SQL Server 2016 has added a couple of nice new options to the setup experience. First they added the ability to have multiple tempdb files on install, a nice time saver for later. And now, with CTP 3.0 they have added the ability to enable IFI on install.

What is IFI, and how do I get it?

IFI stands for instant file initialization, and if you are not aware, enabling this allows SQL Server to grow data files almost instantaneously. Without this enabled the data file space has to be claimed and then filled with zeroes, something that is a long and arduous tasks, especially on slower spinning media type storage.

This is only relevant to data files, for security and integrity reasons the log files still need to be zeroed out.

Enabling IFI is actually quite a simple task, you just need to add the SQL service account to the Perform Volume Maintenance Tasks section of the Local Security Policy and then restart the SQL Server service.

What does SQL 2016 do differently?

Prior to SQL Server 2016 (CTP 3.0) you would need to manually add the SQL account to the Perform Volume Maintenance Tasks (PVMT) section of the Local Security Policy (secpol). Now you can have the installer take care of this for you. That really helps with not forgetting to do it later, which can cause some serious performance problems down the road.

Continue reading “Enabling IFI on Setup in SQL Server 2016” →

Windows

How To Log Off of Server 2012

I’m finding little things that are petty annoyances when working with Windows Server 2012.

NoLogoffOption

The most recent one of these is the lack of a logoff option when connected to a server via a remote desktop session. When using the charm you can get the option to Disconnect, Shut down or Restart the server, but not log off.

It turns out that the solution is pretty simple, but not intuitive.

Just open up PowerShell and type logoff.

PowerShell, Windows

Disabling Windows Updates With PowerShell

Allan Hirt wrote a post back in February entitled “Stop Automatically Updating Production Servers” in which he gave some excellent reasons on why you should ensure that your servers are only updated manually (after all who wants an accidental production down scenario, am I right?).

This hasn’t hit me as a problem as all of my servers are disconnected from the Internet and there’s no WSUS server configured for them to reach, we just push patches down to the machines from a central location. I was perfectly happy with this scenario, and still am, however there is a PITA reason why I wanted to turn off the automatic checks for Windows Updates; constant dumping of events into the System and Application Event Logs:

image

 

image

 

I was really sick of seeing these errors and wanted a quick way to turn off Windows Update without having to go through the multi-click interface nonsense.

Once more PowerShell to the rescue.

Disable-WindowsUpdates.ps1 does just what it says on the box. It disables Windows Updates on the machine it’s run on (provided that’s allowed by Group Policy). You have to execute it as an administrator. When it done it will remove those pesky alerts from the logs and you can feel free that Windows won’t decide to reboot your servers in the middle of the day to apply that IE update you’ve always wanted.

PowerShell, SQL, Windows

Configuring DTC Security In A Cluster With PowerShell

The other day Allan Hirt (blog|twitter) write a fantastic post around “How to properly configure DTC for Clustered Instances of SQL Server with Windows Server 2008 R2”. He included a PowerShell script to handle all the funky setup stuff. The only downside was that after that you had to manually go through and configure Network Access and security for the clustered DTC using the GUI.

“There has to be a better way” I thought. I recalled how security back in 2003 could be set in the registry so I went digging. Eventually I was able to find the requisite keys under the Distribute Transaction Coordinator resource. From there it was just a case of tying things back so that those keys could be easily found from the SQL Resource Name and then updating them so that they matched what Allan demonstrated on his page.

I’ve tested it on a couple of my clusters and it’s worked exactly as designed, you just need to change the ServiceName variable so that it matches your clustered service name and then decided what authentication method you want to use (it defaults to the most secure level: Mutual Authentication Required).

Get the code below or download ConfigureMSDTC.ps1

<#

.SYNOPSIS

   Configures MSDTC on a cluster

.DESCRIPTION

   This configures a basic networked config for MSDTC for clustered SQL Server instances.

   Uncomment the transaction manager security setting and enter the correct ServiceName that can be found in FC manager

.PARAMETER <paramName>

   NONE

.EXAMPLE

   NONE

#>

 

$ServiceName = "SQL Server (SOX2)"

 

#--What MSDTC Transaction Manager Security setting is requested? (uncomment one)

$TranManSec = "Mutual" #Mutual Authentication Required

#$TranManSec = "Incoming" #Incoming Called Authentication Required

#$TranManSec = "None" #No Authentication Required

 

 

 

 

#Grab a list of cluster groups

$GroupPath = "HKLM:ClusterGroups"

$GroupList = dir $GroupPath

 

#Iterate through the groups to find the one matching the service name

foreach ($Group in $GroupList)

{

    $GroupChildPath = $Group.PSPath

    if ((Get-ItemProperty -path $GroupChildPath -name Name).Name -eq $ServiceName)

    {

        #we got a match! Now grab a list of the groups in this service

        $ReferencedResources = (Get-ItemProperty -path $GroupChildPath -name Contains).Contains

        foreach ($Resource in $ReferencedResources)

        {

            #Query each of the resources for their type and work with MSDTC

            $ResourcePath = "HKLM:ClusterResources$Resource"

            if ((Get-ItemProperty -path $ResourcePath).Type -eq "Distributed Transaction Coordinator")

            {

                #We found MSDTC resource for that service group, let's configure it

                $SecurityPath = "$ResourcePathMSDTCPRIVATEMSDTCSecurity"

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccess" -value 1

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessClients" -value 0

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessTransactions" -value 0

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessInbound" -value 1

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessOutbound" -value 1

                Set-ItemProperty -path $SecurityPath -name "LuTransactions" -value 1

 

                #Now configure the authentication method for MSDTC (defaulting to Mutual Auth as it's most secure)

                $SecurityPath = "$ResourcePathMSDTCPRIVATEMSDTC"

                if ($TranManSec -eq "None")

                {

                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 1

                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 0

                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 0

                }

 

                elseif ($TranManSec -eq "Incoming")

                {

                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 0

                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 0

                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 1

                }

 

                else 

                {

                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 0

                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 1

                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 0

                } 

 

            }

        }

    }

}

 

PowerShell, Windows

Adding Users To Local Groups With PowerShell

I ran into a situation last week where I needed to add a user to the Administrators group on a whole bunch of remote servers. Having had serious problems with cubital tunnel I try to avoid mouse clicks as much as possible, and anyway this is one of those situations that PowerShell is great for.

After a little trial and error I put together a script. The script doesn’t accept any pipelining, rather it will prompt you for the values (old school).

When asked enter the name of the local group (you don’t always want to add people to Administrators after all). Then the AD user or group name (include the domain for example PRODSomeAccount). Finally a comma separated list of servers to add the account to. Yes, I could have done this with a text file or something like that, but this is to be used as a quick thing and it’s easier to just type a list of servers in rather than create a file every single time you run it.

Once those three things have been entered the script will go out, check to see if the AD user/group already exists in that server group. If it exists you’ll get a warning that it’s already there, otherwise it will add the user/group to the local server group. There’s also a quick check to see if the server is available, if not you’ll get an error for that particular machine, it will continue to work on the others.

Of course the account you execute this script as will have to have the relevant permissions on the remote servers to allow you to add the user/group.

 

<#

.SYNOPSIS

   Adds a domain user or group to a local Windows group on a local or remote server

.DESCRIPTION

   This script can be used in the event you need to add a domain user or group to a local Windows server group.

   It's real strength comes in being able to enter a comma delimited list of servers so that you can add the same domain user/group to multple machines quickly and easily.

   The user executing the process will require the rights on the remote machines to be able to add the accounts.

   Contains basic error handling to check if the server is reachable, if the group exists and if the user is already a member of the group.

.PARAMETER <paramName>

   No parameters, script file will ask for input

#>

 

$usrName = (Read-Host "Enter the domain account or group that you wish to add to the local server group (eg PRODMyAccount)")

$grpName = (Read-Host "Enter the server group name that the user should be added to (eg Administrators)")

$srvList = (read-host "Enter a comma delimited list of servers (eg SrvA, SrvB)").split(",")

Write-Host ""

$Exists = 0 #Initialize the Exists variable which is used to see whether or not users should be added to groups

 

foreach ($srvName in $srvList)

    {

    $srvName = $srvName.Trim().ToUpper() #Gets rid of spaces

    

try

{

if([ADSI]::Exists("WinNT://$srvName/$grpName,group")) #Check to see if the group exists

    { 

        #Set the comparison string for the account to add

        $usrName = $usrName  -replace "", "/"

        $chkOutput = "WinNT://$usrName"

        #Write-Output "Checking for $chkOutput"

    

        $group = [ADSI]("WinNT://$srvName/$grpName,group") 

        $group.Members() | 

        % { 

            $AdPath = $_.GetType().InvokeMember("Adspath", 'GetProperty', $null, $_, $null) 

            #Write-Output $AdPath

            if ($AdPath -ilike $chkOutput) 

                {

                Write-Warning "User $usrName already a member of the $grpName group on $srvName"

                $Exists = 1 #This way we won't try to add an account twice

                }

        }        

        

        if ($Exists -eq 0)

        { 

            Write-Output "Account $usrName does not exist in the local $grpName group on $srvName. Adding now..." 

            $group.add("WinNT://$usrName,user") #Add the account to the local server group

            $Exists = 0 #Reset the Exists variable ready for the next server

        }

    }

else

{

    Write-Warning "The $grpName group does not exist on server $srvName."

    }

 

}

 

catch { Write-Error "Server $srvName was unreachable." } 

}

PowerShell, Windows

Using PowerShell To Restrict MSDTC Ports

Ever tried to create a linked server that uses MSDTC only to find yourself blocked by your company firewall? Ever tried to run a WMI query against a server just to find yourself blocked? Ever had the firewall team tell you that they aren’t going to open up ports 49152-65535 so that you can use RPC?

Let’s be fair, your network team shouldn’t have to open up all those ports because RPC responds somewhere within a large dynamic range.

How to configure RPC dynamic port allocation to work with firewalls will tell you how to edit your registry to restrict that port range and make your network admin a little happier.

Working with the registry is not fun at the best of times, and when you are setting up a bunch of machines it takes time. Sure, you could create a .reg file and run that on each machine, but this is 2011 and we have PowerShell now.

 

The following script checks and if necessary adds the required registry keys to restrict that port range. In the example below windows is being limited to ports 5000-5200.

 

 

<#

.SYNOPSIS

   Restricts the RPC ports to be used on Windows from 5000-5200

.DESCRIPTION

   Execute to add registry entries on the local machine to restrict the RPC ports from 5000-5200. Requires a reboot once executed.

.PARAMETER <paramName>

   NONE

.EXAMPLE

   NONE

#>

 

if (Test-Path 'HKLM:SOFTWAREMicrosoftRpcInternet' -ErrorAction SilentlyContinue) { "Registry Key Exists" } 

else { md 'HKLM:SOFTWAREMicrosoftRpcInternet' }

 

if (Get-ItemProperty -Name "Ports" -Path 'HKLM:SOFTWAREMicrosoftRpcInternet' -ErrorAction SilentlyContinue ) { "Ports value exists" }

else { New-ItemProperty 'HKLM:SOFTWAREMicrosoftRpcInternet' -Name 'Ports' -Value '5000-5200' -PropertyType 'MultiString' }

 

if (Get-ItemProperty -Name "UseInternetPorts" -Path 'HKLM:SOFTWAREMicrosoftRpcInternet' -ErrorAction SilentlyContinue ) { "UseInternetPorts value exists" }

else { New-ItemProperty 'HKLM:SOFTWAREMicrosoftRpcInternet' -Name 'UseInternetPorts' -Value 'Y' -PropertyType 'String' }

 

if (Get-ItemProperty -Name "PortsInternetAvailable" -Path 'HKLM:SOFTWAREMicrosoftRpcInternet' -ErrorAction SilentlyContinue ) { "PortsInternetAvailable value exists" }

else { New-ItemProperty 'HKLM:SOFTWAREMicrosoftRpcInternet' -Name 'PortsInternetAvailable' -Value 'Y' -PropertyType 'String' }

PowerShell, Windows

Grabbing The Newest File From Subdirectories Using PowerShell

Every once in a while I come up with a need for something a little out of the ordinary, in this instance I was moving backups from one machine to another. Robocopy is the obvious tool of choice to do this. Trouble was that the backups were not all within a single directory, rather they were in multiple subdirectories. Robocopy will of course handle this with the /S switch. What Robocopy can’t handle is the fact that I want only the most recent file from each one of those subdirectories, not all of them (in this case I just wanted to move the most recent differential backup from one location to another).

I figured I could sit down and query msdb for this information and dump that out. I mean it works, it’s functional and does exactly what I would need it to do. Where’s the fun in that though, really? Add to that it would only handle SQL backups, what if I had a need to do the same thing later on  for other types of files? The TSQL query wouldn’t work for me then.

 

PowerShell to the rescue

Seriously, I think that I’m going to get a cape for PowerShell that it can wear around the place as it’s that damn super (although I am not sure that I want to see it’s underwear outside its pants).

In this example I’m going to be working from C:Temp on my local machine.

Within C:Temp there are two folders and a subfolder:

Each of these folders contains a couple of files:

 

To grab the most recent file from a particular folder is a quick one liner:

dir c:tempsubfolder2 | sort-object {$_.LastWriteTime} -Descending | select-object -First 1

 

That is the basis for the rest of the script. Essentially everything else just handles recursing through the subdirectories to grab this information:

cls

$Path = 'C:Temp' #Root path to look for files

$DestinationPath = 'RemoteD$' #Remote destination for file copy

 

#Grab a recursive list of all subfolders

$SubFolders = dir $Path -Recurse | Where-Object {$_.PSIsContainer} | ForEach-Object -Process {$_.FullName}

 

#Iterate through the list of subfolders and grab the first file in each

ForEach ($Folder in $SubFolders)

    {

    $FullFileName = dir $Folder | Where-Object {!$_.PSIsContainer} | Sort-Object {$_.LastWriteTime} -Descending | Select-Object -First 1 

    

    #For every file grab it's location and output the robocopy command ready for use

    ForEach ($File in $FullFileName)

        {

        $FilePath = $File.DirectoryName

        $FileName = $File.Name

        Write-Output "robocopy $FilePath $DestinationPath $FileName /R:6 /W:30 /Z"

        }

    }

Running this gives the results:

 

Quick and easy, does just what it says on the box. Feel free to download CopyNewestFileFromSubDir.ps1 and give it a try. Let me know what enhancements you put around it (here’s a quick one, to limit the type of files evaluated change !$_.PSIsContainer to $_.Extension –eq “.bak” )

PowerShell, Windows

Using PowerShell To Set Resource Owners In A Cluster

Following up on my post about Using PowerShell To Add Owners For Cluster Disks here’s another quick post on how PowerShell can help you with your clusters.

In my new cluster build out I needed to quickly set it so that one of the SQL instances could only potentially live on two of the 5 nodes. This could have been quickly done using the GUI however it’s just as fast to do so using PowerShell.

Load up the Windows PowerShell Modules and you’re ready to go.

 

In this example we have a two node cluster made up of SERVER1 and SERVER2 and a single installed SQL instance called INST1.

In PowerShell the following would provide us a list of possible owners of INST1:

Get-ClusterOwnerNode -Group "SQL Server (INST1)"

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1, server2}         

 

Now to change this so that only SERVER1 can potentially own the SQL instance is a very quick task:

Set-ClusterOwnerNode -Group "SQL Server (INST1)" -Owners SERVER1

 

Sadly you don’t get any feedback that the change has been made, but if you run Get-ClusterOwnerNode again:

Get-ClusterOwnerNode -Group "SQL Server (INST1)"

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1}       

 

Adding SERVER2 back is as quick as running Set-ClusterOwnerNode again and providing a comma delimited list of servers:

Set-ClusterOwnerNode -Group "SQL Server (INST1)" -Owners SERVER1,SERVER2

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1, server2}     

 

You have to love the simplicity of working with clusters in PowerShell.

PowerShell, Windows

Using PowerShell To Add Owners For Cluster Disks

Here’s a quick post about how great PowerShell can be for your clusters.

I’m in the middle of configuring a few machines in clusters and recently added a new node to a cluster that was already built (but not configured). I needed to have that final node be a possible owner for all 33 presented disks on that cluster.  To do that through the cluster GUI would require a heck of a lot of mouse clicks….PowerShell to the rescue!

The Windows PowerShell Modules (under Administrative Tools) allow you to do all kinds of wonderful things with clusters.

Allowing a single disk to be owned by the extra server is as simple as:

CLUSTER RESOURCE "Cluster Disk 1" /addowner:DBSERVER1

 

 

If I want to do that for 33 disks a quick PowerShell one-liner takes care of it:

$i=1;do {cluster resource `"Cluster Disk $i`" /addowner:DBSERVER1;$i = $i +1} while ($i -lt 33)

 

 

Here’s a version that’s a little more readable:

$DiskNo = 1

DO

    { CLUSTER RESOURCE `"Cluster Disk $DiskNo`" /addowner:DBSERVER1

        $DiskNo = $DiskNo+1

    }

    

WHILE ($DiskNo -lt 33)

 

Quick and easy. Saved my clicky finger lots of work.

 

More PowerShell and cluster fun coming up in the next few weeks.