31 Posts of using Sql Server with Powershell — Post 2: ServerConnection

The first post consisted of getting the powershell cmdlets installed/imported into your powershell environment (in my case, powershell_ise).  For day 2, we’re going to look at how we can connect to a Sql Server instance.

ServerConnection

The serverconnection is just what it sounds like; a connection to your sql server instance.  In the code below, all we’re doing is importing the SqlPS module, then creating a ServerConnection object and passing in the name of the server instance that we want to connect to, then opening said connection.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.Connect();

}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

I’ll explain a couple of the details the details here.  The Push-Location at the beginning of the code is used to store your current location, and the Pop-Location at the end just returns you to that location when we’re done running this code.

You don’t NEED to do this, but the Import-Module SqlPS will switch you to the SQLSERVER drive, which can then interfere with other cmdlets you may be running.  It’s just a good habit to get into when using the SqlPS provider, sadly not one I’ve consistently formed.

Secondly, in the catch statement I’m taking the current exception object, doing a format-list on it and doing a –Force.  The reason why I’m doing this is so you can see the details of the exception further down than the first error.  Best explained with an example.  Take the following code:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.LoginSecure = $false;
    $srvConn.Login = 'snewman'
    $srvConn.Password = 'Test'
    $srvConn.Connect();

}
catch{
    $_ #| fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

Notice I have the ‘ | fl –Force’ commented out in the catch statement.  When I run this code, I get the following error:

image

So, what is this error really telling me?  All I know is that it failed to connect, not what the problem really was.  Is my server name incorrect?  Was my password incorrect?

Now, run the same code again, this time without the ‘| fl –Force’ commented out:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.LoginSecure = $false;
    $srvConn.Login = 'snewman'
    $srvConn.Password = 'Test'
    $srvConn.Connect();

}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

After running this code, I get much, much more information about the error:

image

I can see that the issue was that the login was indeed incorrect.

Okay, now that that is all explained, let’s get back to the ServerConnection object.  If you want to use AD authentication with the ServerConnection object, there is a property named LoginSecure that will set this.  It is true by default, so to use AD auth, just open the connection on the ServerConnection object and it will use it by default.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.Connect();
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

If you want to use Sql Server authentication, you have to set a few more properties.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.LoginSecure = $false;
    $srvConn.Login = 'LoginName';
    $srvConn.Password = 'P@55w0rd'
    $srvConn.Connect();
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

First, you have to set the LoginSecure property to $false.  If you try to set the Login when the LoginSecure is not $false, you will receive an error:

image

There is also another property on the ServerConnection object named SecurePassword.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.LoginSecure = $false;
    $srvConn.Login = 'LoginName';
    $srvConn.SecurePassword = (ConvertTo-SecureString -String 'P@$$w0rd' -AsPlainText -Force)
    $srvConn.Connect();
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

This property exists so you can encrypt your passwords via a secure string (most likely held in a file) and use that to more securely handle passwords.  This same type of scenario also exists for the ConnectionString property & SecureConnectionString property.

The most common methods for this object are the Execute* properties for executing various types of Sql statements.

image

Here is a quick example of using the ExecuteNonQuery to create a table:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.Connect();

    $srvConn.ExecuteNonQuery("CREATE TABLE dbo.Test(ID INT, Value VARCHAR(100));");
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location

Now, let’s have some fun with inserting data into the table:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'Bacon'
    $srvConn.Connect();

    #$srvConn.ExecuteNonQuery("CREATE TABLE dbo.Test(ID INT, Value VARCHAR(100));");

    1..1000 | %{
        $srvConn.ExecuteNonQuery("INSERT INTO dbo.Test(ID, Value) SELECT $($_), 'Test $($_)'") | Out-Null
    }
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

Essentially, what this is doing is for 1 to 1000 (1..1000) execute the ExecuteNonQuery method and insert the current number ($_) into the ID column and insert ‘Test_[current number]’ into the Value column.  Now, this 1..1000 could be values read from a file, read from another database, a custom psobject, you get the idea.

For the next post, we’ll look at getting data out of a database.

31 Posts of using Sql Server with Powershell: Modules

This isn’t a 31 days series, as I’ll undoubtedly miss a day here and there, so instead, this will just be a ’31 posts’ series instead.  I’m pragmatic.  And lazy.

I have noticed there is a bit of discomfort amongst most database administrators when it comes to dealing with powershell.  Do yourself a favor and learn it.  You’ll wonder how you ever got by without it before.

So here we go with post 1.

Importing Sql Server Cmdlets

In order to begin this series, you’ll need to import the sql server cmdlets that will enable you to work with the smo objects that you’ll use to work with sql server.  If you have sql server management studio 12 and above, you’re in luck.  All you need to do to import the sql server cmdlets is this:

Import-Module SqlPS -DisableNameChecking;

The –DisableNameChecking is optional, but if omitted you’ll get a warning that some of the verbs in the cmdlets are unapproved:

warning

A word of warning; this module loads extremely slow.  This has been fixed in Sql Server 2016, but as noted in the closing notes of this post, that requires that SSMS for Sql Server 2016 be installed.

If you’ve got Sql Server 2008 R2 and below, you’ll need to install a few things in order to get the sql server cmdlets working correctly.  More or less, the SqlPS module requires the use of Sql Server 2012 Shared Management Objects, so you’ll need to download 3 components from the Sql Server 2012 (or 2014) feature pack:

First, you’ll need download and install the SystemCLR Types for Sql Server 2012/14:

image

Next, you’ll need to download and install the Sql Server Shared Management objects:

image

And finally, download and install the Windows Powershell Extensions for Sql Server:

image

Once you have all three of these installed and restart your powershell environment, you should be able to run the Import-Module SqlPS command correctly.

Okay, post 1 complete.  Sort of.  As of Sql Server 2016, the SqlPS module is being replaced by a new SqlServer module, but that requires that the Sql Server 2016 SSMS must be installed to use it (at the time of this writing), so we’re just going to plod along using SqlPS for now.  Most of the functionality provided in the SqlServer module will still work.

Change Sql Server Configuration Manager IP Address

To change the IP address in sql servers’ configuration manager, you can use the following code.  Of note, when I changed the IP address in configuration manager under IP1 manually and tested connections, it really didn’t make a lick of difference.  I could connect whether or not the IP1 value reflected the correct IP address or not.  But, having a different IP listed in the configuration manager as opposed to what it really is just feels dishonest.

Import-Module SqlPS -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
cls

try{

    $srvName = "servername"
    $Instance = "mssqlserver"
    $urn = "ManagedComputer[@Name='$srvName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"

    $wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $srvName
    $tcp = $wmi.GetSmoObject($urn);
    $tcp.IPAddresses["IP1"].IPAddressProperties["IpAddress"].Value = '10.10.1.1';
    $tcp.Alter();

}
catch{
    $_ | fl -Force
}

Powershell & Sql Server Single User Mode

Sometimes (nigh, always) when you have to start a sql server in single user mode (the ‘/m’ after the net start…), executing queries in the sqlcmd prompt can be a bit of a painful endeavor.  What I usually do is use powershell to stop the service, then restart the service in single-user mode and grab a serverconnection to it immediately in powershell_ise:

Import-Module SqlPS -DisableNameChecking
cls

Stop-Service -Name "MSSQL`$SQL2012" -Force
net start "MSSQL`$SQL2012" /m
$sqlcmd = New-Object Microsoft.SqlServer.Management.Common.ServerConnection '.\sql2012'
$sqlcmd.Connect();

I can then reference the $sqlcmd object in other tabs in powershell_ise to work in a more pleasant environment:

sqlcmd

Notice, I can’t connect in SSMS:

ssms

Just a helpful tip.

Synchronize Sql Users and Logins with Powershell

I have a previous post about syncing users and logins via powershell, but seeing as that one uses the soon to be deprecated (if not already…no listing for this proc for sql server 2014 on MSDN…) ‘sp_change_users_login’, I thought I’d re-do it to be current.  Use at your own risk.

Keep in mind, a database login can indeed be set to use a different server login, so just bear that in mind if you find this not syncing all of them.  I don’t do that (and can’t imagine why anyone would want to make their lives more complicated by doing so….), so I’ve not coded for that scenario.  This also looks for system logins with ‘##’ at the beginning and just skips those.

#requires -module SqlPS

Import-Module SqlPS -DisableNameChecking

$serverName = 'ServerName'

try{

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName
    $logins = $srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin} | select -ExpandProperty Name
    $srv.Databases | %{
        $dbName = $_.Name
        $sb = New-Object System.Text.StringBuilder
        $_.Users | where{$_.Name -in $logins} | %{
            if($_.Name -like '##*'){return};
            $sb.AppendLine("ALTER USER $($_.Name) WITH LOGIN = $($_.Name);") | Out-Null
        }
        $_.ExecuteNonQuery($sb.ToString());
    }
}
catch{
    $_ | fl -Force
}

Timeouts in SqlPS Backup-SqlDatabase & Restore-SqlDatabase

Often when backing up a big database using SqlPS’s Backup-SqlDatabase cmdlet you’ll find yourself hitting a timeout after 600 seconds (10 minutes).  In order to alleviate this, you’ll need to pass in an SMO Server object to the Backup-SqlDatabase instead of defining the –ServerInstance:

try{

    $srv = new-object Microsoft.SqlServer.Management.Smo.Server "MyServerName"
    $srv.ConnectionContext.StatementTimeout = 0
    Backup-SqlDatabase -InputObject $srv -Database "MyBigDatabase" -BackupAction Database -BackupFile "f:\Backups\MyBigDatabase.bak" -CopyOnly -CompressionOption On 

}
catch{
    $_ | fl -Force
}

The same trick *should* work (as I’ve not tried it yet) via the Restore-SqlDatabase, as it also takes an –InputObject of type smo server as well.  This was supposedly fixed in Sql Server 2012 SP2, but if you have just the SqlCLR, SharedManagement, & PowerShell tools installed installed sans sql server, it can be an un-necessary pain to have to apply a service pack just to fix this issue.

Call RoboCopy via Powershell

Just a quick function I knocked up to copy files via RoboCopy in powershell.  If the destination directory does not exist, the function will create it.  Use at your own risk.

function Copy-RoboCopy{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory)]
        [ValidateScript({[System.IO.Directory]::Exists($_);})]
        [string]$SourceDir,
        [Parameter(Mandatory)]
        [string]$Destination
    )
    begin{

    }
    process{

        if(!(Test-Path $Destination -PathType Container)){
            New-Item -ItemType Directory -Path $Destination -Force
        }

        if(!(test-path -Path "C:\Windows\System32\robocopy.exe" -PathType Leaf)){
            throw "Robocopy is not installed."
        }

        robocopy "$SourceDir" "$Destination" *.* /S /MT:32 /XJ /R:25 /W:5 /NP /XX 

    }
    end{

    }
    
}

NetBackup BPList via Powershell

Quick post on how to get netbackup information back using BPList and splitting the image information to powershell objects.  You can get more information from BPList using the –l argument such as size, account, etc… but I’ve just not coded that up yet.  The parameters for this default to the last 24 hours.  Use at your own risk.

function Get-SqlNetBackups{
    param(
        [Parameter(Mandatory=$true, ValueFromPipeline=$true)]
        [string]$ServerName,
        [Parameter(Mandatory=$false, ValueFromPipeline=$false)]
        [DateTime]$Start = (get-date).AddDays(-1),
        [Parameter(Mandatory=$false, ValueFromPipeline=$false)]
        [DateTime]$End = (Get-Date)
    )

    #HKEY_LOCAL_MACHINE\SOFTWARE\VERITAS\NetBackup\CurrentVersion
    $installDir = Get-ItemProperty "HKLM:\SOFTWARE\VERITAS\NetBackup\CurrentVersion" | select -expandproperty INSTALLDIR
    if($installDir -eq $null){
        throw "Netbackup is not installed."
    }

    $bpList = "$installDir\NetBackup\bin\bplist.exe"
    if(!(Test-Path $bpList -PathType Leaf)){
        throw "BPList was not found at $bpList.  This cmdlet uses (abuses) BPList to "
    }
    #Push-Location
    sl "$([System.IO.Path]::GetDirectoryName($bpList))"
    $objects = @();

    try{

        $fStart = $Start.ToString("MM/dd/yyyy")
        $fEnd = $End.ToString("MM/dd/yyyy")
        $type = ""

        $ErrorActionPreference = "stop";
        <#
            -l to show file details, the following is the format with -l.  We can get the size and account, which is nice. When I find the time I'll implement...
            -rw------- SQLAcctSe SQLAcctSe     2949120 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
            -rw------- SQLAcctSe SQLAcctSe           0 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
            -rw------- SQLAcctSe SQLAcctSe           0 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
            -rw------- SQLAcctSe SQLAcctSe           0 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
            -rw------- SQLAcctSe SQLAcctSe           0 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
        #>
        $list = .\bplist -C $ServerName -t 15 -S seaveritas247 -s $fStart -e $fEnd -R \      

        $fmtString = "yyyyMMddHHmmss"
          
        $list -split "`r`n" | select -Unique | %{

            $backupType = ""
            $arr = $_.Split(".");  #the image string...

            switch($arr[3]){  #0-based, so 4th item 1-based...used to tell the backup type...
                "db"{
                    $backupType = "Full"
                }
                "trx"{
                    $backupType = "TranLog"
                }
                "inc"{
                    $backupType = "Diff"
                }
                default{
                    $backupType = "Other"
                }
            }

            $objects += [PSCustomObject]@{
                ServerName = $arr[0]
                DatabaseName = $arr[4] 
                Date = [DateTime]::ParseExact($arr[8], $fmtString, $null)
                Type = $backupType
                Image = $_
            } 
    
        }

        $objects; 
    }
    catch{
        $_ | fl -Force
    }
}

Powershell Central Management Server Recursive Server List

I had a need to recursively iterate my Central Management Server, yet always return the topmost GroupName.  The function takes an array of strings to return that correlate to the names of the first level groups.  Use at your own risk.

#requires -Module SqlPS 
#requires -Version 5

Import-Module SqlPS -DisableNameChecking

function Get-CMSServers{
    param(
        [string[]]$CMSGroup = @('Group1', 'Group2')  #default groups...
    )

    function Get-RegisteredServers{  
        param(
            $ServerGroup,
            $ParentGroup
        )

        $ServerGroup.RegisteredServers | %{
            $objects.Add([PSCustomObject]@{
                ServerName = $_.ServerName
                GroupName = $ParentGroup 
                GroupDescription = $_.Description 
                ParentName = $ServerGroup.Name
            }) | Out-Null
        }

        if($ServerGroup.ServerGroups.Count -gt 0){
            $ServerGroup.ServerGroups | %{
                Get-RegisteredServers -ServerGroup $_ -ParentGroup $ParentGroup
            }
        }
    }

    $objects = New-Object System.Collections.ArrayList
    Set-Variable -Name objects -Option AllScope

    try{

        $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection &quot;ServerName&quot;
        $ServerStore = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore $srvConn
        $ServerStore.DatabaseEngineServerGroup.ServerGroups | where{$_.Name -in $CMSGroup} | %{
            Get-RegisteredServers -ServerGroup $_ -ParentGroup $_.Name 
        }

        return $objects;
    }
    catch{
        $_ | fl -Force
    }
    finally{
        $srvConn.Disconnect();
    }

}

Get-CMSServers -CMSGroup @('Production', 'Reporting')