Tag: Windows

SQL November 11, 2015

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 January 22, 2013

How To Log Off of Server 2012

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

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 September 10, 2012

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:

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 July 5, 2012

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 November 23, 2011

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 November 1, 2011

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 July 7, 2011

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 June 30, 2011

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 June 28, 2011

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.

SQL, Windows June 16, 2011

MSDTC Failing In A Cluster

I’m merrily working away on installing SQL 2008R2 on a bunch of new clusters. The SQL installs have gone fine and I’m getting set to install MSDTC, one for each SQL instance (read the awesome post by Cindy Gross on this).

The install of MSDTC went smoothly and it seemed very happy. Then I failed over the cluster…

MSDTC failed.

It wouldn’t restart.

I failed back to the original node and it wouldn’t start there either.

What’s the error?

I dumped the last few minutes of the cluster log by opening a dos box command prompt and running…

cluster log /gen /span:5

This dumps the last five minutes of cluster logs into C:WindowsClustercluster.log

I scrolled through to get to the MSDTC error piece:

INFO [RES] Physical Disk: Failed to get vol MP root for path ?, status 123
ERR [RHS] Error 123 from ResourceControl for resource <instance>_MSDTC.
WARN [RCM] ResourceControl(STORAGE_IS_PATH_VALID) to <instance>_MSDTC returned 123.

I checked the disk resource for MSDTC and it was online. Looking at the filesystem on that disk and there was an MSDTC directory, so I knew there were no access problems. It didn’t make any sense.

So what’s going on?

The key error here is the failure to get MP root for path ?

Apparently MSDTC is not supported does not work with Mount Points, which is what I had set the dependency to. There were no warnings on this when setting MSDTC up and I’d not seen or heard of any documentation that speaks to this.

I was finally pointed to a Connect item opened by someone who’d had the same issue https://connect.microsoft.com/SQLServer/feedback/details/576545/msdtc-fails-to-restart-in-sql-server-2008-r2-clustered-group

_{Side note: I love it when Connect items such as this are closed as by design. Why is this by design? Can someone explain to me why MSDTC shouldn’t be supported on Mount Points?}

I deleted the MSDTC resource and added it again, this time using a regular drive as a dependency and everything worked perfectly. I was able to failover and have SQL perform distributed transactions.