Administering Azure from PowerShell Core

Introduction

Not long ago Microsoft proclaimed to us that they love Linux. And while many remain skeptical of that assertion (and not without reason), the tech giant is continually pouring more time and resources into the open source world. One of the really exciting products of this (to me at least) is PowerShell Core. Not only can I now program with my favorite scripting language from Mac or Linux, I can interact with services like Azure. In fact, Microsoft’s own Azure Cloud Shell runs PowerShell Core, so it’s no side mission for them. Let’s take a look at using it to connect to Azure from a non-Windows environment.

Install PowerShell Core

To start out, we’ll need to install PowerShell Core.

# Download the Microsoft repository GPG keys
wget -q https://packages.microsoft.com/config/ubuntu/18.04/packages-microsoft-prod.deb

# Register the Microsoft repository GPG keys
sudo dpkg -i packages-microsoft-prod.deb

# Update the list of products
sudo apt-get update

# Enable the "universe" repositories
sudo add-apt-repository universe

# Install PowerShell
sudo apt-get install -y powershell

# Start PowerShell
pwsh

Now we have PowerShell on Linux!

Next, we will need to install and import the ‘AZ’ module for working with Azure.

Install-Module -Name Az

Import-Module Az

Alright, now we’re ready to rock. Let’s get connected up to Azure using Connect-AzAccount. That will give us a prompt like the one below, supplying a code to be used for connection.

Supply your Azure credentials, and your machine should now be connected.

Now, we can interact with our Azure environment!

Microsoft has done a fantastic job with making PowerShell Core and the Azure cmdlets available on non-Windows systems. It’s a trippy experience to be working with them from Mac or Linux, but I’m excited for the possibilities ahead!

Quickly Copy AD Group Memberships To A New User

I recently moved into a new role and needed to to be added to a number of AD groups. An often-used method for this is to model a new employee’s permissions after someone already in the same role. For one or two groups it is relatively simple to just do this manually, but as you can imagine it quickly gets tedious when several are involved. For this reason, I whipped up the following PowerShell snippet to ease the pain.

This works by:

  1. Pulling the list of AD groups that user1 is a member of.
  2. Selecting only the memberof property and expanding it to make sure the list isn’t truncated.
  3. Looping through each of the groups to:
    • Extract only the group name from the Distinguished Name (which is the format it comes in from the previous command).
    • Use the extracted group name with Add-ADGroupMember to add our new user to that group.

 

get-aduser user1 -Properties memberof | select -ExpandProperty memberof | ForEach-Object -Process {$groupName = ($_ -split ',*..=')[1]; Add-ADGroupMember -Identity $groupName -Members user2}

List the Manager Emails for AD Group Members with PowerShell

Below is a quick script I’ve been working on for a colleague. Its simple purpose is to query an AD group then display each member’s username and the email address of their manager. Hopefully this will help others accomplish this common task with ease.

(Update 04/11/2018)

I modified the script to include the employee’s full name and also to sort the output by that name. Additionally, the way I was handling the variable members and naming bothered me. I knew it could be better. So I did some more research and cleaned that up utilizing the -ExpandProperty option for select.

#################################################################################
#Script to pull the members of an AD group and display their manager's email.
#Author: Landon Fowler 
#Date: 04/11/2018
#################################################################################

#Pull in the desired group name as a parameter 
Param 
( [Parameter(Mandatory=$True,Position=1)] 
[string]$ADGroup ) 

#Import the necessary PowerShell module 
Import-Module -DisableNameChecking -Name ActiveDirectory -Cmdlet Get-ADUser

#Create an empty object for display 
$display = @() 

#Get a list of the AD group members 
$groupMembers = Get-ADGroupMember $ADGroup 

#Loop through each user and add their username and manager's email to the display
foreach ($user in $groupMembers) { $manager = Get-ADUser $user -Properties
Manager | select -ExpandProperty Manager $objDisplay = New-Object System.Object 

#Test whether the Manager field has a value, and if it does proceed 
if ($manager) 
   { $managerEml = Get-ADUser -Identity $manager -Properties emailaddress |
   select -ExpandProperty emailaddress $objDisplay | 
   Add-Member -type NoteProperty -name EmployeeName -value $user.Name $objDisplay
   | Add-Member -type NoteProperty -name Username -value $user.SamAccountName
   $objDisplay | Add-Member -type NoteProperty -name ManagerEmail -value
   $managerEml $display += $objDisplay 
   } 

#If the manager field was empty or null, inform that no manager was present 
else 
   { $objDisplay | Add-Member -type NoteProperty -name EmployeeName -value
   $user.Name $objDisplay | Add-Member -type NoteProperty -name Username -value
   $user.SamAccountName $objDisplay | Add-Member -type NoteProperty -name
   ManagerEmail -value "No Manager" $display += $objDisplay 
   } } 

#Display the completed display object 
$display | sort EmployeeName

Getting Started with PowerShell Desired State Configuration (DSC)

As I’ve mentioned in other posts, Desired State Configuration (DSC) is a powerful technology with a lot of potential. However, due to how new it is and how rapidly it’s evolving, it can be difficult to get started and figure out how to accomplish your specific goals. My intention here is not to give an exhaustive look at the ins and outs of DSC (I’m not qualified to do that), but rather to give you the tools to get started and be successful with it .

Step 1: Get a Baseline

After years of cobbling together information, then having to go back later and relearn how to do it the right way, I’ve learned the value of getting the framework right from the start. If you have a strong framework in mind for how something is built and how it’s intended to be used, then building useful things on top of it is much easier. I had the same experience when learning DSC. There is a lot of information on the internet about it, but much of it is “old” and out of date. Also, everyone has their own opinions for how it should be used. Eventually I came across two videos on Microsoft Virtual Academy that put everything into perspective. They are taught by “Jeffrey Snover, Microsoft Distinguished Engineer and inventor of PowerShell,  along with Windows PowerShell MVP Jason Helmick”. This is a fantastic starting point for diving into DSC and I can’t recommend them enough.

Getting Started with PowerShell Desired State Configuration (DSC) – Microsoft Virtual Academy

Advanced PowerShell Desired State Configuration (DSC) and Custom Resources – Microsoft Virtual Academy

Step 2: Working With New Resources

Now that you know all about DSC and how it can be used, it’s time to put that knowledge to work. You eagerly download the module you want to use (let’s say xSQLServer, for example) and are ready to have machines install SQL for you. The first inclination is probably to google it to see how it’s used, which will lead you to github or PowerShell Gallery. Those are great for getting information about the package and its change tracking, but not much use for actually implementing the module. So here you are with a brand new toy and no manual.

Examples

The first thing to do with a new module is always check for an examples folder. Your module was probably installed in C:\Program Files\WindowsPowerShell\Modules.

Opening the examples folder within that module will reveal a list of scripts made by the creating team for various scenarios they see the module being used in. Your mileage may vary depending on the module you’re using and who made it, but generally those produced by Microsoft have useful information. This is where I obtained the example file that my last post, “Installing SQL Server Using Desired State Configuration“, was based on. Again, how much explanation is included within each script is completely up to the discretion of the creator. That’s why it’s important to first watch the videos linked above. Then, whether there is proper documentation or not you can make sense of it yourself.

(Upon further inspection, the examples are sometimes also available on the github site.)

Interrogating Resources

Even when you have an example file that closely matches your needs it’s likely that you will still want to customize it. Many times, the module you are working with will have a resource you need but not an example of it listed in the file. Or you simply want to know what all you can do with the module. As usual, it’s PowerShell to the rescue.

Using PowerShell we can easily find which resources are available to us in a module using Get-DscResource.

After finding a resource that interests us, we can dig further down into its specifics. In order to get more than cursory information, it s necessary to expand the properties field.

From this information we can tell which fields are available to us, what their data types are, and which ones are required.

It should also be noted that you can discover these from right within ISE as well. This is the improvised method that I used before discovering the PowerShell cmdlets above. If you type anything within the resource block that it doesn’t recognize, intellisense will automatically suggest the proper fields to use by hovering over the angry red line.

And, if all else fails, in the end resources are just PowerShell scripts. You can go to their folder and open them like any other file (ie C:\Program Files\WindowsPowerShell\Modules\xSQLServer\DSCResources\MSFT_xSQLServerLogin).

Step 3: Be Brave

Armed with all of this knowledge there is but one thing to do, be brave. Start putting some configurations together, make mistakes, then use the lessons learned to make better configurations. This is an exciting technology in which things are rapidly moving and changing. In fact, within days of my last post (and while writing this one) I discovered that xSQLServer had been retired in favor of SqlServerDsc, and I’d had no idea.

So get at it, make your own creations, keep your eyes open daily for changes, and let me know if you have any questions. I look forward to learning with you.

Installing SQL Server Using Desired State Configuration

(Update: I’ve since discovered that SqlServerDsc has replaced xSQLServer.)

One of my growing passions is using PowerShell Desired State Configuration (DSC) to automate all the things. I started out with simple configurations for testing but wanted to dive into more complex\useful situations for my day-to-day DBA life. To be honest, I was intimidated by the idea of doing a SQL installation. Configuring simple parameters or creating a directory are easy enough to wrap my head around, but something as complex as a DBMS installation gave me pause. I’m here to tell you that my worries were unfounded, and that you should have none as well.

The blessing and curse of DSC is that it’s so new. It is without doubt a very powerful tool, but as of yet there isn’t a lot of documentation around the individual resources. Or worse yet, the pace of improvement moves so quickly that information from two years ago is now out of date. I plan on doing a separate post for how to approach DSC given these realities. With this post, however, I wanted to fill one of those documentation gaps. Specifically, how to install and configure an instance of SQL server. I based my file off of an example one provided by Microsoft in the “Examples” folder of the xSQLServer module named “SQLPush_SingleServer.ps1”. Pro tip: always look for example folders in the modules you want to work with. It should be noted that you can address much more complicated scenarios, such as setting up clusters or Availability Groups, but for simplicity this configuration will be creating a single instance on one node.

If you have experience with DSC or simply don’t want to listen to me drone on about the details, the full configuration is at the bottom. For those interested in the play by play, or just bored and looking for something to do, I’ll address each piece individually.

The script starts out with compulsory documentation on the script and what it does. Kidding aside, get into the habit of doing small sections like this. Your coworkers (and you years from now when you’ve forgotten what you did) will thank you.

############################################################################################################################
# SQL Server Single Instance Push
# Author: Landon Fowler
# Date 01/2018
#
# Based off of Microsoft example file
#
# Prerequisites: Unzip the SQL ISO to your file share
#                If required, copy the sxs folder to your file share for .NET Framework
############################################################################################################################

Next, we hard-code a couple of items specific to your individual run of the script. List the computer(s) that you want to deploy to as well as a local path for the configuration file that DSC will create.

#Computers to target
$computers = 'YourComputer'
#Local path on your computer to which the .mof file will be exported
$OutputPath = 'C:\Install\DSCLocal'

$cim = New-CimSession -ComputerName $computers

Following that, we will set how the Local Configuration Manager on the target nodes is to behave. We’re specifying that the configuration is being pushed to it, that it should automatically check every so often for compliance to this configuration and auto-correct anything that’s not aligned, that modules on the node can be overwritten, and that it can reboot if needed.

#Set LCM configuration for target nodes
[DSCLocalConfigurationManager()]
Configuration LCM_Push
{
    Param(
        [string[]]$ComputerName
    )
    Node $ComputerName
    {
    Settings
        {
            AllowModuleOverwrite = $True
            ConfigurationMode = 'ApplyAndAutoCorrect'
            RefreshMode = 'Push'
            RebootNodeIfNeeded = $True
        }
    }
}

#Apply the above LCM settings to each target node
foreach ($computer in $computers)
{
    $GUID = (New-Guid).Guid
    LCM_Push -ComputerName $Computer -OutputPath $OutputPath
    Set-DSCLocalConfigurationManager -Path $OutputPath  -CimSession $computer
}

Following that is the actual configuration details, where all the fun is defined. Mine is named “SQLSA”, but it really doesn’t matter what you name it. This is like defining a function; so as long as you call it by that same name later, little else is relevant. You’ll see at the top of this section there are three “Import-DscResource” lines. This tells the configuration which DSC modules will be needed to perform the actions we’re requesting.

#Detail the configuration that will be pushed
Configuration SQLSA
{
    Import-DscResource -Module xPSDesiredStateConfiguration
    Import-DscResource -Module xSQLServer
    Import-DscResource -Module xNetworking

    Node $AllNodes.NodeName
    {
        # Set LCM to reboot if needed
        LocalConfigurationManager
        {
            AllowModuleOverwrite = $true
            RebootNodeIfNeeded = $true
        }

The WindowsFeature item is one of the most handy in DSC. This allows us, as you might guess, to install Windows Features (in this case the .NET Framework).

        # Not necessary when using SQL 2016 on Windows Server 2016.
        WindowsFeature "NET"
        {
            Ensure = "Present"
            Name = "NET-Framework-Core"
            Source = $Node.NETPath
        }

Next I’ve created a firewall rule to make sure our instance’s port will be open (this is defined later under xSQLServerNetwork). It’s worth noting that there is a resource built into xSQLServer that allows you to configure firewall rules for SQL. However, I did not like the behavior of it and found that xFirewall from the module xNetworking provided a lot more flexibility.

        #Create firewall rule for our instance
        xFirewall Firewall
        {
            Name                  = 'SQL_Instance'
            DisplayName           = 'SQL Instance Firewall Rule'
            Ensure                = 'Present'
            Enabled               = 'True'
            Profile               = ('Domain', 'Private', 'Public')
            Direction             = 'Inbound'
            RemotePort            = 'Any'
            LocalPort             = $Node.SqlPort
            Protocol              = 'TCP'
        }

Up next is the actual meat of installing SQL Server. The if($Node.Features) block is something I picked up from the example file. I’d say it’s redundant to check for whether you’re installing SQL when you came here to install SQL, but hey, it works well so I left it.

One way I’ve altered this section from the original is to parameterize everything. If you look further down there is a $ConfigurationData section. Having all of our customizable fields there allows us to easily change them for each deployment (dev, test, prod) without having to search through the code. You and your team will know exactly where to go and what to change for each situation.

I’ve also included some examples of basic SQL Server tasks like creating a database, disabling the sa account, disabling a feature like xp_cmdshell, and configuring the network port (referenced earlier). The naming on these items looks odd but makes sense. By adding in the node name we can ensure that they are unique should we deploy to more than one target node. And adding a friendly name to the configuration item, like “sa”, makes it easy to tell DSC which item depends on which. Speaking of which, note that each of the configurations depends on the base installation. That way DSC will not run those if there is nothing to actually configure.

        #Begin actual SQL setup
        if($Node.Features)
        {
           xSqlServerSetup ($Node.NodeName)
           {
               DependsOn = '[WindowsFeature]NET'
               SourcePath = $Node.SourcePath
               SourceCredential = $Node.InstallerServiceAccount
               InstanceName = $Node.InstanceName
               Features = $Node.Features
               SQLSysAdminAccounts = $Node.AdminAccount
               InstallSharedDir = $Node.BinaryPath
               InstanceDir = $Node.InstancePath
               InstallSQLDataDir = $Node.SysDBPath
               SQLUserDBDir = $Node.UserDBPath
               SQLUserDBLogDir = $Node.UserDBLogPath
               SQLTempDBDir = $Node.TempDBPath
               SQLTempDBLogDir = $Node.TempDBLogPath
               SQLBackupDir = $Node.BackupPath
           }

           #Create a database
           xSQLServerDatabase ($Node.NodeName+"Diagnostics")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                Name = "Diagnostics"
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                Ensure = "Present"
           }

           #Disable the builtin sa account
           xSQLServerLogin($Node.Nodename+"sa")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                Name = 'sa'
                Disabled = $true
           }

           
           #Disable an option such as xp_cmdshell
           xSQLServerConfiguration($Node.Nodename+"xp_cmdshell")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                OptionName = 'xp_cmdshell'
                OptionValue = '0'
           }

           #Make sure TCP support is enabled and configured
           xSQLServerNetwork($Node.Nodename+"Network")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                InstanceName = $Node.InstanceName
                ProtocolName = 'tcp'
	        TCPPort = $Node.SqlPort
	        IsEnabled = $true
	        RestartService = $true
           }
        }
    }

After the configuration definition we have the $ConfigurationData mentioned earlier. It’s a great idea to get in the habit of using sections like this. It will make your transition between various environments much easier.

#### Section to customize each DSC deployment ####
$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName = "*"
            PSDscAllowPlainTextPassword = $true
            PSDscAllowDomainUser =$true
            NETPath = "\\FileShare\Install\sxs"
            SourcePath = "\\FileShare\Install\SQL"
            InstallerServiceAccount = Get-Credential  -Message "Credentials to Install SQL Server"
            AdminAccount = "YourAccount"
            BinaryPath = "C:\Program Files\Microsoft SQL Server"
            InstancePath = "C:\Program Files\Microsoft SQL Server"
            SysDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            UserDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            UserDBLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            TempDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            TempDBLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            BackupPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            SQLService = 'NT Service\MSSQLSERVER'
            SQLAgentService = 'NT Service\SQLSERVERAGENT'
            SqlPort = '1433'

        }
    )
}

The next section details what we’d like the instance name to be as well as what features should be installed. It’s very picky about the feature names, and they don’t line up exactly with a standard command line install. So be careful with what you place here. It won’t install anything incorrectly, just simply cause the configuration not to run and you to lose your mind.

Also in this section, we’re copying over the modules that each node will need in order to perform this configuration. This isn’t necessary when using DSC in pull mode, but that’s a story for a different post.

#Loop through each computer target, setting instance name, features to be installed, and also copying over necessary modules.
ForEach ($computer in $computers) {
   $ConfigurationData.AllNodes += @{
            NodeName        = $computer
            InstanceName    = "MSSQLSERVER"
            Features        = "SQLENGINE"

    }
   $Destination = "\\"+$computer+"\\c$\Program Files\WindowsPowerShell\Modules"
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xSQLServer' -Destination $Destination -Recurse -Force
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xNetworking' -Destination $Destination -Recurse -Force
}

I know you thought it’d never come, but at last it’s time to actually do something with all of this. We call our “SQLSA” configuration, passing in the $ConfigurationData and specifying to place the resulting .mof file in $OutputPath. After that, configuration is started on each node using Start-DscConfiguration and calling the .mof that was just created. Lastly, the node is tested to make sure it’s not out of compliance.

#Prepares the configuration's .mof file to be pushed.
SQLSA -ConfigurationData $ConfigurationData -OutputPath $OutputPath

#Push the configuration to each computer and start the configuration.
foreach($Computer in $Computers)
{

    Start-DscConfiguration -ComputerName $Computer -Path $OutputPath -Verbose -Wait -Force
}

#Make sure each node is now incompliance
foreach($Computer in $Computers)
{
    Test-DscConfiguration -ComputerName $Computer
}

If all goes well, your output will lack red and eventually will end in a message stating that the configuration tests as “True”.

And that’s all there is to it! Not so scary after all. I deployed my first DSC SQL Server while making tea and wondered why I’d been doing it any other way…

############################################################################################################################
# SQL Server Single Instance Push
# Author: Landon Fowler
# Date 01/2018
#
# Based off of Microsoft example file
#
# Prerequisites: Unzip the SQL ISO to your file share
#                If required, copy the sxs folder to your file share for .NET Framework
############################################################################################################################

#Computers to target
$computers = 'YourComputer'
#Local path on your computer to which the .mof file will be exported
$OutputPath = 'C:\Install\DSCLocal'

$cim = New-CimSession -ComputerName $computers

#Set LCM configuration for target nodes
[DSCLocalConfigurationManager()]
Configuration LCM_Push
{
    Param(
        [string[]]$ComputerName
    )
    Node $ComputerName
    {
    Settings
        {
            AllowModuleOverwrite = $True
            ConfigurationMode = 'ApplyAndAutoCorrect'
            RefreshMode = 'Push'
            RebootNodeIfNeeded = $True
        }
    }
}

#Apply the above LCM settings to each target node
foreach ($computer in $computers)
{
    $GUID = (New-Guid).Guid
    LCM_Push -ComputerName $Computer -OutputPath $OutputPath
    Set-DSCLocalConfigurationManager -Path $OutputPath  -CimSession $computer
}

#Detail the configuration that will be pushed
Configuration SQLSA
{
    Import-DscResource -Module xPSDesiredStateConfiguration
    Import-DscResource -Module xSQLServer
    Import-DscResource -Module xNetworking

    Node $AllNodes.NodeName
    {
        # Set LCM to reboot if needed
        LocalConfigurationManager
        {
            AllowModuleOverwrite = $true
            RebootNodeIfNeeded = $true
        }
        
        # Not necessary when using SQL 2016 on Windows Server 2016.
        WindowsFeature "NET"
        {
            Ensure = "Present"
            Name = "NET-Framework-Core"
            Source = $Node.NETPath
        }

        #Create firewall rule for our instance
        xFirewall Firewall
        {
            Name                  = 'SQL_Instance'
            DisplayName           = 'SQL Instance Firewall Rule'
            Ensure                = 'Present'
            Enabled               = 'True'
            Profile               = ('Domain', 'Private', 'Public')
            Direction             = 'Inbound'
            RemotePort            = 'Any'
            LocalPort             = $Node.SqlPort
            Protocol              = 'TCP'
        }
        
        #Begin actual SQL setup
        if($Node.Features)
        {
           xSqlServerSetup ($Node.NodeName)
           {
               DependsOn = '[WindowsFeature]NET'
               SourcePath = $Node.SourcePath
               SourceCredential = $Node.InstallerServiceAccount
               InstanceName = $Node.InstanceName
               Features = $Node.Features
               SQLSysAdminAccounts = $Node.AdminAccount
               InstallSharedDir = $Node.BinaryPath
               InstanceDir = $Node.InstancePath
               InstallSQLDataDir = $Node.SysDBPath
               SQLUserDBDir = $Node.UserDBPath
               SQLUserDBLogDir = $Node.UserDBLogPath
               SQLTempDBDir = $Node.TempDBPath
               SQLTempDBLogDir = $Node.TempDBLogPath
               SQLBackupDir = $Node.BackupPath
           }

           #Create a database
           xSQLServerDatabase ($Node.NodeName+"Diagnostics")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                Name = "Diagnostics"
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                Ensure = "Present"
           }

           #Disable the builtin sa account
           xSQLServerLogin($Node.Nodename+"sa")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                Name = 'sa'
                Disabled = $true
           }

           
           #Disable an option such as xp_cmdshell
           xSQLServerConfiguration($Node.Nodename+"xp_cmdshell")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SQLServer = $Node.NodeName
                SQLInstanceName = $Node.InstanceName
                OptionName = 'xp_cmdshell'
                OptionValue = '0'
           }

           #Make sure TCP support is enabled and configured
           xSQLServerNetwork($Node.Nodename+"Network")
           {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                InstanceName = $Node.InstanceName
                ProtocolName = 'tcp'
	        TCPPort = $Node.SqlPort
	        IsEnabled = $true
	        RestartService = $true
           }
        }
    }
}

#### Section to customize each DSC deployment ####
$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName = "*"
            PSDscAllowPlainTextPassword = $true
            PSDscAllowDomainUser =$true
            NETPath = "\\FileShare\Install\sxs"
            SourcePath = "\\FileShare\Install\SQL"
            InstallerServiceAccount = Get-Credential  -Message "Credentials to Install SQL Server"
            AdminAccount = "YourAccount"
            BinaryPath = "C:\Program Files\Microsoft SQL Server"
            InstancePath = "C:\Program Files\Microsoft SQL Server"
            SysDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            UserDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            UserDBLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            TempDBPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            TempDBLogPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            BackupPath = "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data"
            SQLService = 'NT Service\MSSQLSERVER'
            SQLAgentService = 'NT Service\SQLSERVERAGENT'
            SqlPort = '1433'

        }
    )
}

#Loop through each computer target, setting instance name, features to be installed, and also copying over necessary modules.
ForEach ($computer in $computers) {
   $ConfigurationData.AllNodes += @{
            NodeName        = $computer
            InstanceName    = "MSSQLSERVER"
            Features        = "SQLENGINE"

    }
   $Destination = "\\"+$computer+"\\c$\Program Files\WindowsPowerShell\Modules"
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xSQLServer' -Destination $Destination -Recurse -Force
   Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xNetworking' -Destination $Destination -Recurse -Force
}

#Prepares the configuration's .mof file to be pushed.
SQLSA -ConfigurationData $ConfigurationData -OutputPath $OutputPath

#Push the configuration to each computer and start the configuration.
foreach($Computer in $Computers)
{

    Start-DscConfiguration -ComputerName $Computer -Path $OutputPath -Verbose -Wait -Force
}

#Make sure each node is now incompliance
foreach($Computer in $Computers)
{
    Test-DscConfiguration -ComputerName $Computer
}

My First PowerShell Module – PSOraenv

Update (04/01/2019): We’ve hit over 100 downloads for PSOraenv! A big thanks to the PowerShell community. Please keep using it and let me know how I can make it better.

*******

Most of my experience with Oracle has been on Linux, but recently I began working with it on Windows as well. It came to my attention very quickly that oraenv, my beloved friend in Oracle administration, is not present on Windows installations. When you have more than one database installed on a single server, or perhaps ASM (which has its own Oracle home), manually swapping back and forth to work with the different pieces gets really old really fast. For this reason, and because I’ve been anxious to learn how to make my own PowerShell modules, I created PSOraenv. Its purpose is to closely mirror the capabilities of oraenv and allow a similar level of simple, yet powerful, command-line flexibility on Windows systems. Let me give you some examples.

By default, the necessary environment variables for Oracle aren’t set when you fire up PowerShell or Command Prompt. (It seems to default to the most recently installed product.) Using my cmdlet Get-OraEnv, we can verify that they start empty.

In order to see what options are available to us, we run my cmdlet Get-OraSID to pull back a list of the database SID’s and associated Oracle homes on the local machine.

Now that we know what SID’s are available to us, my cmdlet Set-OraEnv can be used.

Now, if we run Get-OraEnv again, we can see that the environment variables have indeed been set.

And, just to prove that actually matters, sqlplus can verify which database we are currently working with. (My sandbox system defaults to lkftest2.)

And there you have it! We have easily viewed which databases are on the local server and quickly swapped to the one that’s needed. It’s just as easy to swap back to the lkfowler2 SID if needed.

This module can be installed from PowerShell Gallery using Install-Module, like the example below.

Install-Module -Name PSOraenv

(You can also find the PowerShell Gallery page at https://www.powershellgallery.com/packages/PSOraenv/1.0)

If you’re interested to see what’s in the code, that has been placed below. I welcome any comments, feedback, and functionality requests. Hope you enjoy!

<# 
 .Synopsis
  Show a list of Oracle SID's.

 .Description
  Show a list of Oracle SID's and their associated ORACLE_HOME values, pulled from the registry of the local machine.

 .Example
  Get-OraSID
#>

function Get-OraSID 
{
    #Create an empty object for display
    $display = @()

    #Get a list of Oracle homes from the registry
    $homes = Get-ChildItem -Path HKLM:\SOFTWARE\Oracle | where {$_.Name -match 'KEY_Ora'}

    #Loop through each home and add the desired information to the display object
    foreach ($path in $homes)
    {
        $dir = Split-Path $path.Name -Leaf
        $oraObject = Get-ItemProperty "HKLM:\SOFTWARE\Oracle\$dir"
      
        $objDisplay = New-Object System.Object
        $objDisplay | Add-Member -type NoteProperty -name OracleSid -value $oraObject.ORACLE_SID
        $objDisplay | Add-Member -type NoteProperty -name OracleHome -value $oraObject.ORACLE_HOME

        $display += $objDisplay
    }

    #Display the completed display object
    $display
}

<# 
 .Synopsis
  Displays the current value of your Oracle environment variables.

 .Description
  Displays the current value of ORACLE_SID, ORACLE_HOME, and ORACLE_BASE.

 .Example
  Get-OraEnv

#>
function Get-OraEnv
{
    #If ORACLE_SID has a value, display it
    if ($env:ORACLE_SID -ne $null)
    {
        Write-Host "ORACLE_SID: " $env:ORACLE_SID
    }

    else
    {
        Write-Host "ORACLE_SID  is empty"
    }

    #If ORACLE_HOME has a value, display it
    if ($env:ORACLE_HOME -ne $null)
    {
        Write-Host "ORACLE_HOME: " $env:ORACLE_HOME
    }

    else
    {
        Write-Host "ORACLE_HOME is empty"
    }

    #If ORACLE_BASE has a value, display it
    if ($env:ORACLE_BASE -ne $null)
    {
        Write-Host "ORACLE_BASE: " $env:ORACLE_BASE
    }

    else
    {
        Write-Host "ORACLE_BASE is empty"
    }
}

<# 
 .Synopsis
  Sets the Oracle environment variables based on a provided SID.

 .Description
  Sets the Oracle environment variables based on a provided SID.
  Information is obtained from the registry to set ORACLE_SID, ORACLE_HOME, and ORACLE_BASE.

 .Example
  # Set the current environment for the database DB1.
   Set-OraEnv DB1

#>
function Set-OraEnv 
{
param(
    [Parameter(Mandatory=$true)]
    [string]$oraSid
    )
    
    #Get a list of Oracle homes from the registry
    $homes = Get-ChildItem -Path HKLM:\SOFTWARE\Oracle | where {$_.Name -match 'KEY_Ora'}

    #Loop through each home to find the one we're working with based on the provided SID.
    #Then use the key's values to set our environment variables.
    foreach ($path in $homes)
    {
        $dir = Split-Path $path.Name -Leaf
        
        $oraObject = Get-ItemProperty "HKLM:\SOFTWARE\Oracle\$dir"
        
        if ($oraObject.ORACLE_SID -eq $oraSid)
        {
            $oraHome = $oraObject.ORACLE_HOME
            $oraBase = $oraObject.ORACLE_BASE
            
            Write-Host "Changing environment to:"
            Write-Host "ORACLE_SID : " $oraSid
            Write-Host "ORACLE_HOME: " $oraHome
            Write-Host "ORACLE_BASE: " $oraBase

            $env:ORACLE_SID = $oraSid
            $env:ORACLE_HOME = $oraHome
            $env:ORACLE_BASE = $oraBase
        }
    }  
}

#Make our functions available for use
export-modulemember -function Get-OraSID
export-modulemember -function Get-OraEnv
export-modulemember -function Set-OraEnv

 

Windows Server Core Jumpstart

Recently I’ve been looking into the potential that Windows Server Core holds for our environment. Like most eager new Core users, I imagine, I jumped in with grand visions of spinning up a VM quickly and being off to the races administering it from my desktop. The reality wasn’t quite the same, as I ran into a chicken and egg situation wondering how I could set up the machine when I could not yet connect to it. To complicate the issue, I couldn’t find a concise list of information on exactly what is needed to simply make the machine available so that I could begin to work with it.

With that in mind, I’ve compiled the following information in hopes of saving others the same headache. There’s nothing earth shattering here, but hopefully it will allow people to get started with Server Core quickly so that they can move on to more important things, like how the server will actually be used.

Let me know if you have any questions or suggestions. Hope it’s a help to you.

  • Ports to request from your firewall team.
    •  TCP
      • 5985, 5986 (WinRM)
      • 445 (SMB) –This is up to you. I wanted to be able to move files to/from the server.
      • 135
  • Local firewall rules to allow remote administration.
    • Enable Remote Management groups
      (Note: If you enable “Remote Service Management” on the host first, then you can do the others via PowerShell remoting. This can be helpful since copy/paste in things like VMWare console doesn’t always work.)
Enable-NetFirewallRule -DisplayGroup "Remote Service Management","Remote Event Log Management","File and Printer Sharing","Performance Logs and Alerts","Remote Volume Management","Windows Firewall Remote Management","Remote Desktop"

Default outbound traffic to allow

Set-NetFirewallProfile -Name Domain,Public,Private  –DefaultOutboundAction Allow

Enable Ping (optional)

Enable-NetFirewallRule -Name FPS-ICMP4-ERQ-IN
  • Remote management tools
    • Add the remote computer to Server Manager (available on Windows desktop and server versions).
      • Once added, you can easily launch Computer Management and PowerShell for that specific machine by right-clicking it.
    • Connect via PowerShell remoting.
      New-PSSession -ComputerName YourRemoteComputer | Enter-PSSession
      • Cross-domain PowerShell Remoting (ie Dev or Test domains)
        • If remoting isn’t enabled on your local machine, enable it.
          Enable-PSRemoting -Force
        • Add machines to the TrustedHosts list. (Depending on your setup, you might have to substitute IP addresses for the machine names in -Value.)
          Set-Item WSMan:\localhost\Client\TrustedHosts -Value 'machineA,machineB'

          Verify with:

          Get-Item WSMan:\localhost\Client\TrustedHosts
        • Use PSSession to connect
          New-PSSession -ComputerName machineA -Credential (Get-Credential) | Enter-PSSession
      • IIS management (run on remote machine)
        Install-WindowsFeature Web-Mgmt-Service
        
        Set-Service wmsvc -startuptype "automatic"
        
        Start-Service wmsvc
        • Set HKLM\SOFTWARE\Microsoft\WebManagement\Server\EnableRemoteManagement to 1.
          (This can be achieved using the local regedit tool and connecting it to the remote machine.)
        • Restart the WMSVC service.
          Restart-Service wmsvc
        • Connect from local IIS Manager for Remote Administration with the local administrator credentials of the remote machine.
      • You can either use sconfig or the following remote PowerShell commands to allow Remote Desktop. (This is especially helpful for quickly getting to sconfig and other commands that do not operate properly with remote PowerShell.)
        set-ItemProperty -Path 'HKLM:\System\CurrentControlSet\Control\Terminal Server'-name "fDenyTSConnections" -Value 0
        set-ItemProperty -Path 'HKLM:\System\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp' -name "UserAuthentication" -Value 1
  • Common configuration tasks
    • The utility “sconfig” can be used for most setup items.
    • For a more speedy and scriptable setup, below are some common configurations via PowerShell.
      • Change date\time
        Set-TimeZone -Name "Eastern Standard Time"
      • Change computer name
        Rename-Computer -NewName "machineA" -Restart
      • Add to the domain
        Add-Computer -DomainName "Contoso.com" -Restart

         

 

Using Powershell to Refresh a Test SQL Server Instance from Production

A project I’ve been wanting to work on for a long time is how to automate restores of our Production databases to the Test instance. There are a number of challenges associated with this. First, the restore has to be able to find which backup it needs to use. Secondly, many of our databases do not have their files configured in the same way (for example one may have a single .mdf and log whereas another may have multiple .ndf files). Third, restoring from a different instance causes the SQL Authentication users to be orphaned and therefore connections to the databases to fail. And this is just at the high level. There are many smaller complications along each of those roads. Because of these our restore model has typically been per-request, manually executed, and filled with many small and tedious steps. I wanted to replace it with one process that would restore all of the production databases from the proper backup, configure any “with MOVE” options dynamically, and fix any orphaned users. A secondary goal was also to make it as portable as possible so that I could easily reconfigure it for our other PROD/Test/Dev instances.

The result of this labor of love is copied below. You’ll notice that at the top you can configure the Source and Destination SQL instances to be used, as well as the Data\Log paths for both. This is the section that allows me to reconfigure it for other instances. You can make these parameters that are passed in if you want to call it manually. For my purposes it is a scheduled process that always runs against  specific instance. Following that section is an exclusion list, built to avoid system databases as well as any others you want to skip (use cautiously to avoid angering coworkers in the Development or Testing departments).

The only function is one called “FixOrphans”. If you’ve guessed that’s where the orphaned users are repaired, then you’re today’s winner! This works by pulling a list of available logins from the destination, creating a list of orphans, and using the Source to find which login users should be matched to.  It will also match “dbo” to a different login if that’s the way the Source database was configured. Of course, this breaks down if the logins are not named the same on both instances. This is the case for some of mine as well. In those cases I have a section at the bottom to take care of one-off scenarios by hard coding them. It isn’t ideal, but will have to do until I can change some policies in my organization.

A fun side note about the FixOrphans function. It started as a small section of the script, then quickly grew to become its own function. I became so interested in what could be done with it that I side-tracked for a while and wrote a stand-alone script just for repairing orphans. Being a faithful reader, you will remember it being covered in Fixing Orphaned SQL Users via PowerShell. So, the restore project was actually first and fixing orphans spun out of that. I then took that work and rolled it back into the restore script, so the code will be very similar to that first post.

After that function is declared we move on to the real work. A variable is created to store Source backup information so that we know which backup to use. It then loops through each of the objects stored in that variable. If the database is in the exclusion list it notes that and moves on. Otherwise, it sets a baseline for the restoration query and starts building on that. This is the portion that allows me to account for differing file configurations per database on the instance. For each database it will pull Source information about the files and dynamically build the MOVE statements. At the end it packages them all together and adds any remaining options, such as keep_cdc. After the full statement is built the script will set the database to single user, closing any connections. It will then drop the database and execute the restoration. Dropping isn’t entirely necessary, but our Test environment is often short on space. Dropping first allows me to free up room before starting the restore.

There are two things to note at this section. The first is that, while documentation will tell you that not specifying a timeout value for Invoke-Sqlcmd means that it’s unlimited, that simply isn’t true. Unless you specify one it will die after thirty seconds. Secondly, once the script successfully kicks off that restore command it will truck happily along its way to the next line, whether your restore finishes instantaneously or not (my wager is that it doesn’t). For that reason I built a Do…While loop to monitor for restore commands to finish and then allow the script to proceed. Otherwise it gets awkward trying to set your database to simple when it doesn’t actually exist yet. The commands to set that recovery option and shrink the log file are also both in the interest of saving space.

Once all of that is finished, it’s just a matter of resetting variables so that the next database has a clean run and calling the aforementioned FixOrphans function to give those SQL Auth users a home. After all of the elements in the array have been processed I write an error string that I’ve been compiling to a file and call it a day. Two files will be created. RefreshLog.txt contains all of the operational information, and Errors.txt contains, you guessed it, errors.

As with my other scripts, much of this will be different in your environment than it is in mine. However, I hope that this will be easily adaptable for your use and, if nothing else, that you can find useful elements that can be incorporated into  your own scripts. As always, feel free to send me any questions, and I hope this helps.

P.S. I chose to use Invoke-Sqlcmd so that all versions of SQL, new or old, would be compatible. For newer instances feel free to use SMO.

 

#Save current working directory.
Push-Location

#Import only the Cmdlet we need from the SQL Module without warning about name checking.
Import-Module -DisableNameChecking -Name SQLPS -Cmdlet Invoke-Sqlcmd

#Return to working directory.
Pop-Location

#Set source and destination info.
$SourceSQLInstance = 'COMPUTER_NAME\INSTANCE_NAME'
$DestSQLInstance = 'COMPUTER_NAME\INSTANCE_NAME'

$SourceDataRoot = 'H:\PROD_INSTANCE_DATA'
$SourceLogRoot = 'H:\PROD_INSTANCE_LOG'

$DestDataRoot = 'J:\TEST_INSTANCE_DATA'
$DestLogRoot = 'J:\TEST_INSTANCE_LOG'

#Collection of errors to write to file.
$ErrMessage = @()

#List of databases to skip (ie system ones).
$DBExclusionList = "master", "model", "msdb", "tempdb", "TroubleMakerDatabase"

#List of current destination databases.
$CurrentDestDBs = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select name from sys.databases
                                                                                            where name != 'master'
                                                                                            and name != 'model'
                                                                                            and name != 'msdb'
                                                                                            and name != 'tempdb'"



####################################################################################################################
# Functions
####################################################################################################################


#Function to fix orphaned users.
function FixOrphans ($db)
 {

    #Get list of available logins on the destination instance.
    $LoginList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select name from master..syslogins"

    #Get list of orphaned users on the destination instance.
    $OrphanList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $db -query "exec sp_change_users_login 'Report'" 

    #Check the orphan count, proceed if there are any.
    if ($OrphanList.Count -gt 0)
    {
        #Process each orphaned user.
        foreach ($Orphan in $OrphanList)
        {
            #Set database user pricipal name.
            $User = $Orphan.UserName

            #Set login name from source instance.
            $Login = Invoke-Sqlcmd -ServerInstance "$SourceSQLInstance" -Database $db -query "SELECT S.loginname FROM $db.sys.database_principals as P 
                                                                                                join master..syslogins S on P.sid = S.sid where P.name = '$User'"
            #Make the login name a string for easy use.
            $LoginName = $Login.loginname
            
            #Test for whether the login exists on this instance. If it does, match it to the user.
            if ($LoginList.name -contains $LoginName)
            {
                
                #Handle orphand dbo users. These cannot be updated by sp_change_users_login.
                if ($User -eq "dbo")
                {
                    
                    $LogTime = Get-Date
                    $Message = "Changing owner to $LoginName for database $db."
                    #Log time and message.
                    $Message

                    Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $db -query "EXEC sp_changedbowner '$LoginName';"     

                }

                #For all other cases outside of dbo, use sp_change_users_login.
                else
                {
                    $Message = "Matching user $User to login $LoginName for database $db on $DestSQLInstance."
                    $Message

                    #Update the user's login.
                    Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $db -query "exec sp_change_users_login 'update_one', '$User', '$LoginName'" 
                }
            }


            #Give an informative message when the login is not in the list.
            else
            {
                
                $ErrMessage = $ErrMessage + "Failed to update user $User for database $db because there is no login on $DestSQLInstance."

            }
        }
    }
    
    #Inform if there are no orphans for this database. Happy day!
    else
        {
            $ErrMessage = $ErrMessage + "There are no orphans in database $DatabaseName."
            
        }


 }

####################################################################################################################
# End Functions
####################################################################################################################


#Get list of all database names and path to the most recent backup.
$FilePaths = Invoke-Sqlcmd -ServerInstance "$SourceSQLInstance" -Database MSDB -query "SELECT msdb.dbo.backupset.database_name, msdb.dbo.backupmediafamily.physical_device_name 
                FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
                WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_finish_date, 102) >= GETDATE() - 7) AND  msdb.dbo.backupset.type = 'D' 
                ORDER BY msdb.dbo.backupset.database_name"

#Loop through each database.
foreach ( $file in $FilePaths ) 
{
    #Set backup file path.
    $BackupName = $file.physical_device_name

    #Set database name.
    $DBName = $file.database_name
   

    #Check for database names in the exclusions list.
    if ($DBExclusionList -contains $DBName)
        {
            echo "Database $DBName in Exclusion List"
        }

    
    #Process databases that have not been excluded.
    else
        {
            
            #Set database file information.
            $FileInfo = Invoke-Sqlcmd -ServerInstance "$SourceSQLInstance" -Database $DBName -query "SELECT name, physical_name FROM sys.database_files"

            #Set the foundation for the restore query.
            $RestoreQuery = "RESTORE DATABASE $DBName FROM  DISK = N'$BackupName' WITH "

            #Use the database file information to dynamically build the move file commands.
            foreach ($DBFile in $FileInfo)
            {
                #Build commands for data files.
                if ($DBFile.physical_name -like "*.mdf" -or $DBFile.physical_name -like "*.ndf")
                 {
                    $DataName = $DBFile.Name
                    $DataFile = $DBFile.physical_name.Replace($SourceDataRoot, $DestDataRoot)

                    #Append the move file command.
                    $FileCommand = $FileCommand + "MOVE N'$DataName' TO N'$DataFile', "
                
                 }
               
               #Build commands for log files.
               elseif ($DBFile.physical_name -like "*.ldf")
                 {
                    $LogName = $DBFile.Name
                    $LogFile = $DBFile.physical_name.Replace($SourceLogRoot, $DestLogRoot)

                    #Append the move file command.
                    $FileCommand = $FileCommand + "MOVE N'$LogName' TO N'$LogFile', "

                 }
            
            }
           
           #Add the completed move file commands to the overall query, and place remaining options on the end.
           $RestoreQuery = $RestoreQuery + $FileCommand + "REPLACE, keep_cdc"

           #$RestoreQuery >> C:\Users\lkfowler\Desktop\QueryOutput.txt

           echo "Restoring $DBName"

           #If database exists, set it to single user and drop it.
           if ($CurrentDestDBs.name -contains $DBName)
           {
                Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -query "ALTER DATABASE $DBName SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE"
                Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -query "DROP DATABASE $DBName"
           }


           #Execute the restore command.
           Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "$RestoreQuery" -querytimeout ([int]::MaxValue)

           #Loop until restore is finished         
           DO
            {
              $Restoring =  Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select count(*) from sys.dm_exec_requests where command = 'RESTORE DATABASE'" 
              Start-Sleep -s 5
            }
            WHILE ($Restoring -eq 1)
          

           #Set to simple.
           Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "ALTER DATABASE $DBName set recovery simple"

           #Shrink log file
           Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $DBName -query "DBCC SHRINKFILE ($LogName, 1)"

           #Clear command-building variables so that they can be reused for the next database.
           Clear-Variable FileCommand
           Clear-Variable RestoreQuery

           #Call function to fix any orphaned users.
           FixOrphans -db $DBName

        }
}


#Write to error log after processing has finished.
Out-File Errors.txt -InputObject $ErrMessage

Using PowerShell to Perform Availability Group Failovers

In the past we’ve explored how to use PowerShell to automate failovers on SQL Failover Clusters for Windows Updates and other scheduled maintenance. But what if you’re using Availability Groups instead of the traditional cluster? Fear not, there is still a PowerShell option for that. Usually I would have our night team use SSMS to fail over instances, but recently I transitioned to having them use the PowerShell method below. There are two primary reasons for this. 1) The system administrator does not need to have SQL Server rights in order to carry out the failover and 2) having pre-written commands helps cut down on human error.

For the purposes of this example we have two nodes (AGNODE1 and AGNODE2), each having their own instances (SQLINSTANCE1 and SQLINSTANCE2) that are part of an Availability Group (AGINSTANCE). This will walk through the process of installing Windows Updates on each of those nodes. We will assume that at the outset AGNODE1 is the primary for AGINSTANCE.

  1. Install updates on AGNODE2 and reboot as necessary.
  2. Log into AGNODE2.
  3. Right-click PowerShell and click “Run as Administrator”.
  4. Make AGNODE2 the primary by running the following command: Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\AGNODE2\SQLINSTANCE2\AvailabilityGroups\AGINSTANCE
  5. Confirm that the AG instance is now owned by AGNODE2 using this command: Get-ClusterGroup
  6. Install updates on AGNODE1 and reboot as necessary.
  7. Log into AGNODE1.
  8. Right-click PowerShell and click “Run as Administrator”.
  9. Make AGNODE1 the primary by running the following command: Switch-SqlAvailabilityGroup -Path SQLSERVER:\Sql\AGNODE1\SQLINSTANCE1\AvailabilityGroups\AGINSTANCE
  10. Confirm that the AG instance is now owned by AGNODE1 using this command: Get-ClusterGroup

A couple of key points to keep in mind are this: 1) you must run the command from the destination server and 2) all of the confusing syntax is simply specifying the node, instance name, and Availability Group name for that destination server.

I hope this helps, and as always feel free to send me any questions!

Fixing Orphaned SQL Users via PowerShell

In SQL Server, a login is an instance level object that is used for authentication. It is mapped to a database user, which controls permissions at the database level. These objects (login and user) are tied to one another via a SID. If the login is deleted and then recreated, or if you restore your production database to a test environment, those SID’s will no longer match and orphaned users will result.

Information on how to resolve this situation can be found on MSDN. However, if you need to fix more than one user this can be painful. It also does not work for the “dbo” user, requiring you to take additional steps in order to repair both. In the interest of handling refreshes to our development and test instances in a more efficient way I’ve created the script below. It takes two parameters, one for the source instance and another for the destination. It will cycle through the databases on the destination instance and query the source for login information pertaining to any orphaned users. Some informational messages have been built in to help you identify issues, such as a login not existing on your destination instance.

There are a couple of disclaimers. This script assumes that your logins will have the same name on both instances. If your production instance has an account named “sqlsvc” and the test instance equivalent is named “sqlsvc_test”, then it will not sync the user to that login. For the situation I’m working with there is no reliable standard in account names for me to rely on. If your environment is more standardized then please feel free to build in that additional logic.

I hope this will be of help to those out there fatigued by running sp_change_users_login one user at a time. You call the script as “./FixOrphans.ps1 -SourceSQLInstance YourSourceName -DestSQLInstance -YourDestinationName”. If you don’t provide the parameters up front it will prompt you for them.

As always, if you have any questions please let me know.

#Parameters passed in for source and destination instances.
Param(
  [Parameter(Mandatory=$True,Position=1)]
   [string]$SourceSQLInstance,
	
   [Parameter(Mandatory=$True)]
   [string]$DestSQLInstance
)

#Save current working directory.
Push-Location

#Import only the Cmdlet we need from the SQL Module without warning about name checking.
Import-Module -DisableNameChecking -Name SQLPS -Cmdlet Invoke-Sqlcmd

#Return to working directory.
Pop-Location

#Prompt for source and destination information. (Alternative to the parameter method at the top.)
#$SourceSQLInstance = Read-Host -Prompt 'Source Instance'
#$DestSQLInstance = Read-Host -Prompt 'Destination Instance'


#Get list of available logins on the destination instance.
$LoginList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select name from master..syslogins"

#Get list of databases on the destination instance.
$DatabaseList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database master -query "select name from sys.databases"

#Process each database that was pulled.
foreach ($Database in $DatabaseList)
{
    #Make the database name a string for easy use.
    $DatabaseName = $Database.name
    
    #Get list of orphaned users.
    $OrphanList = Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $DatabaseName -query "exec sp_change_users_login 'Report'" 
    
    #Check the orphan count, proceed if there are any.
    if ($OrphanList -ne $null -or $OrphanList.Count -gt 0)
    {
        #Process each orphaned user.
        foreach ($Orphan in $OrphanList)
        {
            #Set database user pricipal name.
            $User = $Orphan.UserName

            #Set login name from source instance.
            $Login = Invoke-Sqlcmd -ServerInstance "$SourceSQLInstance" -Database $DatabaseName -query "SELECT S.loginname FROM $DatabaseName.sys.database_principals as P 
                                                                                                join master..syslogins S on P.sid = S.sid where P.name = '$User'"
            #Make the login name a string for easy use.
            $LoginName = $Login.loginname
            
            #Test for whether the login exists on this instance. If it does, match it to the user.
            if ($LoginList.name -contains $LoginName)
            {
                
                #Handle orphand dbo users. These cannot be updated by sp_change_users_login.
                if ($User -eq "dbo")
                {
                    
                    $Message = "Changing owner to $LoginName for database $DatabaseName."
                    $Message

                    Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $DatabaseName -query "EXEC sp_changedbowner '$LoginName';"     

                }

                #For all other cases outside of dbo, use sp_change_users_login.
                else
                {
                    $Message = "Matching user $User to login $LoginName for database $DatabaseName on $DestSQLInstance."
                    $Message

                    #Update the user's login.
                    Invoke-Sqlcmd -ServerInstance "$DestSQLInstance" -Database $DatabaseName -query "exec sp_change_users_login 'update_one', '$User', '$LoginName'" 
                }
            }


            #Give an informative message when the login is not in the list.
            else
            {
                
                $Message = "Failed to update user $User for database $DatabaseName because there is no login on $DestSQLInstance."
                Write-Host $Message -ForegroundColor Yellow

            }
        }
    }
    
    #Inform if there are no orphans for this database. Happy day!
    else
        {
            $Message = "There are no orphans in database $DatabaseName."
            $Message
        }
}