I saw a question in the forums related to inserting new rows into a SQL Server table only if they didn’t exist. The current solution was using an ADO.NET DataTable , checking for new rows and then pushing the rows back to SQL Server by calling the Update method on the DataAdapter. Although the solution works, the process becomes longer as each time the process is run the entire table is retrieved and compared.

There’s a number of approaches you could take to solve this problem. One solution is to use Table Valued Parameters which I’ve previously blogged about to push a batch of rows to SQL Server and add only new rows. This does require creating both a table type and stored procedure on the SQL Server and only works for SQL Server 2008 and higher:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

<#
/* FROM SSMS */
USE tempdb
GO
/* Create a Request table for testing purposes*/
CREATE TABLE Request
( PKID INT,
 MessageText varchar(max));
GO
/* Create a RequestList table type */
CREATE TYPE RequestList AS TABLE
( PKID INT,
 MessageText varchar(max));
GO
 
/* Create a procedure to use insert only new rows  */
CREATE PROCEDURE uspSetRequest
    @TVP RequestList READONLY
    AS
    SET NOCOUNT ON
    INSERT Request
    SELECT tvp.PKID, tvp.MessageText
    FROM @TVP tvp
    LEFT JOIN Request r ON
    tvp.PKID = r.PKID
    WHERE r.PKID IS NULL;
 GO
 #>
 
#FROM Powershell
#Create an ADO.NET DataTable matching the RequestList Table Type:
$dt = new-object Data.datatable
$col =  new-object Data.DataColumn
$col.ColumnName = 'PKID'
$col.DataType = [Int32]
$dt.Columns.Add($Col)
$col =  new-object Data.DataColumn
$col.ColumnName = 'MessageText'
$col.DataType = [String]
$dt.Columns.Add($Col)
 
#BEGIN INSERT foreach Loops to add records to DataTable
#Example below inserts only one record
#Add a Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('PKID') = 1
$dr.Item('MessageText') = 'It worked!'
$dt.Rows.Add($dr)
#END INSERT foreach Loops to add records to DataTable 
 
#Connection and Query Info
$serverName="$env:computername\sql1"
$databaseName='tempdb'
$query='uspSetRequest' 
 
#Connect
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$conn = new-object System.Data.SqlClient.SqlConnection $connString
$conn.Open()
 
#Create Sqlcommand type and params
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText= $query
$null = $cmd.Parameters.Add("@TVP", [System.Data.SqlDbType]::Structured)
$cmd.Parameters["@TVP"].Value = $dt
 
#Execute Query and close connection
$cmd.ExecuteNonQuery() | out-null
$conn.Close()

I saw this question in one of forums on backing up i.e. scripting out a database object. The problem is easy to solve, but only if you’re familiar with SMO . Even so, there some more obscure aspects of SMO like URNs which not many people are aware of. If you read the MSDN docs on SMO you’ll find URNs are referenced in a few places. I haven’t used them much, but for this case  it makes sense. Normally if you want to get to an object in SMO you’d reference the server, then the database then the object type collection (StoredProcedures, Views, etc.), and then the object;  however if you don’t know the object type you can call EnumObject method on the database to get a list of objects with its URN. The URN is like a primary key of objects in SMO. So, here’s my solution with with comments…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
 
#######################
<#
.SYNOPSIS
Backs up a database object definition.
.DESCRIPTION
The Backup-DatabaseObject function  backs up a database object definition by scripting out the object to a .sql text file.
.EXAMPLE
Backup-DatabaseObject -ServerInstance Z002 -Database AdventureWorks -Schema HumanResources -Name vEmployee -Path "C:\Users\Public"
This command backups up the vEmployee view to a .sql file.
.NOTES
Version History
v1.0   - Chad Miller - Initial release
#>
function Backup-DatabaseObject
{
    [CmdletBinding()]
    param(
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$ServerInstance,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$Database,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$Schema,
    #Database Object Name
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$Name,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$Path
    )
 
    $server = new-object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
    $db = $server.Databases[$Database]
 
    #Create a UrnCollection. URNs are used by SMO as unique identifiers of objects. You can think of URN like primary keys
    #The URN format is similar to XPath
    $urns = new-object Microsoft.SqlServer.Management.Smo.UrnCollection
 
    #Get a list of database object which match the schema and object name specified
    #New up an URN object and add the URN to the urns collection
    $db.enumobjects() | where {$_.schema -eq $Schema -and  $_.name -eq $Name } |
        foreach {$urn = new-object Microsoft.SqlServer.Management.Sdk.Sfc.Urn($_.Urn);
                 $urns.Add($urn) }
 
    if ($urns.Count -gt 0) {
 
        #Create a scripter object with a connection to the server object created above
        $scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server)
 
        #Set some scripting option properties
        $scripter.options.ScriptBatchTerminator = $true
        $scripter.options.FileName = "$Path\BEFORE_$Schema.$Name.sql"
        $scripter.options.ToFileOnly = $true
        $scripter.options.Permissions = $true
        $scripter.options.DriAll = $true
        $scripter.options.Triggers = $true
        $scripter.options.Indexes = $true
        $scripter.Options.IncludeHeaders = $true
 
        #Script the collection of URNs
        $scripter.Script($urns)
 
    }
    else {
        write-warning "Object $Schema.$Name Not Found!"
    }
 
} #Backup-DatabaseObject

And here’s example of sourcing and calling the function:

1
2

. ./Backup-DatabaseObject.ps1
Backup-DatabaseObject -ServerInstance Z002 -Database AdventureWorks -Schema HumanResources -Name vEmployee -Path "C:\Users\Public"

I’ve posted the code on PoshCode also.

I wanted show someone how to use table-valued parameters available in SQL Server 2008 and higher. The main use case of table-valued parameters is for sending a list or array of items as parameter to a SQL Server stored procedure or function. This is more efficient than parsing strings or XML on the SQL Server side. I couldn’t seem to find a complete example of table-valued parameters in the SQL Server documentation. The SQL Server docs only shows the T-SQL portion of the code and not the ADO.NET. I think its difficult to see how you would use this feature without having both the T-SQL and .NET code shown together so, here’s a simple T-SQL and Powershell script demonstrating table-valued parameters:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

<#
/* FROM SSMS */
USE AdventureWorks
GO
/* Create a CustomerList table type */
CREATE TYPE Sales.CustomerList AS TABLE 
( CustomerID INT );
GO
 
/* Create a procedure to use new table type */
CREATE PROCEDURE Sales.uspGetCustomer
    @TVP CustomerList READONLY
    AS 
    SET NOCOUNT ON
    SELECT c.*
    FROM Sales.Customer c
    JOIN @TVP t ON
    c.CustomerID = t.CustomerID;
 GO
 
/* Test type and procedure in SSMS */
 
/* Declare a variable that references the type. */
DECLARE @CustomerTVP AS Sales.CustomerList;
 
/* Add data to the table variable. */
INSERT INTO @CustomerTVP (CustomerID)
SELECT * FROM (
	VALUES (1),(2),(3),(4),(5)
) AS v (CustomerID)
 
/* Pass the table variable data to a stored procedure. */
EXEC Sales.uspGetCustomer @CustomerTVP;
GO
#>
 
#FROM Powershell
#Create an ADO.NET DataTable matching the CustomerList Table Type:
$dt = new-object Data.datatable  
$col =  new-object Data.DataColumn  
$col.ColumnName = 'CustomerID'  
$col.DataType = [Int32]
$dt.Columns.Add($Col)
 
#Add a Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 1   
$dt.Rows.Add($dr)  
 
#Add a 2nd Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 2   
$dt.Rows.Add($dr)  
 
#Add a 3rd Row to the DataTable
$dr = $dt.NewRow()
$dr.Item('CustomerId') = 3   
$dt.Rows.Add($dr)  
 
#Connection and Query Info
$serverName="$env:computername\sql1" 
$databaseName='AdventureWorks' 
$query='Sales.uspGetCustomer' 
 
#Connect
$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;" 
$conn = new-object System.Data.SqlClient.SqlConnection $connString 
$conn.Open()
 
#Create Sqlcommand type and params
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.CommandText= $query
$null = $cmd.Parameters.Add("@TVP", [System.Data.SqlDbType]::Structured)
$cmd.Parameters["@TVP"].Value = $dt
 
#Create and fill dataset
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$null = $da.fill($ds)
$conn.Close()
 
#Return results
$ds.Tables

Several months ago I described a solution for Delegated SQL Server Administration with Powershell. In the solution, the SqlProxy module audits all security administration activity to a custom Windows Event log. In this blog post, I’ll described a process to transform and incrementally load the audit data into a SQL Server table for reporting purposes.

Writing to the Event Log

First a quick review of how  SqlProxy module writes messages to the event log. This is important because as we’ll see in a moment, how the message is constructed helps in extracting Event log data. In the SqlProxy module I use a standard template in each function for logging messages to the Event log:

$PSUserName = $PSSenderInfo.UserInfo.Identity.Name
$logmessage =  "PSUserName=$PSUserName" + $($psBoundParameters.GetEnumerator() | %{"`n$($_.Key)=$($_.Value)"})
write-sqlproxylog -eventID $eventID."$($myinvocation.mycommand.name)" -message $logmessage

The message is constructed using several built-in variables written as key/value pairs.

1. The $PSSenderInfo variable is available inside of remote session and returns information about the user who started the PSSession. Since I’m using runas credentials I’ll grab the name of the person who is connected.
2. $psBoundParameters contains a hashtable of the parameters and their values for the current function.
3. This code may look a little odd, $eventID.”$($myinvocation.mycommand.name)”. I created hashtable called $eventid in the SqlProxy module to translate a function name from a an EventId. Since $myinvocation.mycommand.name returns the function name I’ll use this as the hashtable key as shown below:

$EventID = @{
"Add-SqlDatabaseRoleMember"=0
"Add-SqlLogin"=1
"Add-SqlServerRoleMember"=2
"Add-SqlUser"=3
"Remove-SqlDatabaseRoleMember"=4
"Remove-SqlLogin"=5
"Remove-SqlServerRoleMember"=6
"Remove-SqlUser"=7
"Rename-SqlLogin"=8
"Set-SqlLogin"=9
"Set-SqlLoginDefaultDatabase"=10
}

The write-sqlproxlog function is just a wrapper around write-eventlog as follows:

function Write-SqlProxyLog
{
    param(
    [Parameter(Position=0, Mandatory=$true)] $EventID,
    [Parameter(Position=1, Mandatory=$true)] $Message,
    [Parameter(Position=2, Mandatory=$false)] $EntryType='SuccessAudit'
    )
 
    write-eventlog -logname SqlProxy -source SqlProxy -eventID $eventID -message $message -EntryType $EntryType
 
} #Write-SqlProxyLog

A typical event log entry will look like this:

Extracting Data from the Eventlog

In order to load the Eventlog data into a SQL Server table I created a module called SqlTools which is collection of functions I use frequently for querying and loading data. I’ve posted the module here.

The initial load script as shown below makes use of SqlTools module:

import-module SqlTools
 
$ComputerName = 'Z001'
$ServerInstance = 'Z002\sql1'
$Database = 'SqlProxy'
 
$dt = Get-EventLog -LogName SqlProxy -ComputerName $ComputerName -EntryType 'SuccessAudit' | % { $ht = ($_.Message -replace "\\","/") |
    ConvertFrom-StringData; $xml = new-object psobject -Property $ht | ConvertTo-Xml -NoTypeInformation -As String;
    new-object psobject -Property @{'Index' = $_.Index; 'TimeGenerated'=$_.TimeGenerated;
    'EventId'=$_.EventId; 'MessageXml'=$xml} } | Out-DataTable
 
Add-SqlTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -DataTable $dt -AsScript | clip

At this point I’ll paste the T-SQL script into SSMS, modify column data types, null/not null, add primary key and finally create the table:

CREATE TABLE [dbo].[SqlProxyLog](
	[EventId] [int] NOT NULL,
	[TimeGenerated] [datetime] NOT NULL,
	[MessageXml] [xml] NOT NULL,
	[Index] [int] NOT NULL,
 CONSTRAINT [PK_SqlProxyLog] PRIMARY KEY CLUSTERED
(
	[TimeGenerated] ASC,
	[Index] ASC
)
)

Then I’ll return to Powershell to execute the write-datatable function:

Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -Data $dt

In order to incrementally load only new events, I’ll modify the get-sqlproxylog.ps1 to first grab the max timegenerated or 1900-01-01 if its null and then use value for the -After param of the Get-Eventlog cmdlet:

import-module SqlTools
 
$ComputerName = 'Z001'
$ServerInstance = 'Z002\sql1'
$Database = 'SqlProxy'
 
$query = "SELECT ISNULL(MAX(TimeGenerated),'1900-01-01') AS TimeGenerated FROM dbo.SqlProxyLog"
$maxDtm = invoke-sqlcmd2 -ServerInstance $ServerInstance -Database $Database -Query $query | select -ExpandProperty TimeGenerated
 
$dt = Get-EventLog -LogName SqlProxy -ComputerName $ComputerName -EntryType 'SuccessAudit' -After $maxDtm | % { $ht = ($_.Message -replace "\\","/") |
    ConvertFrom-StringData; $xml = new-object psobject -Property $ht | ConvertTo-Xml -NoTypeInformation -As String;
    new-object psobject -Property @{'Index' = $_.Index; 'TimeGenerated'=$_.TimeGenerated;
    'EventId'=$_.EventId; 'MessageXml'=$xml} } | Out-DataTable
 
if ($dt) {
    Write-DataTable -ServerInstance $ServerInstance -Database $Database -TableName 'SqlProxyLog' -Data $dt
}

Some interesting points about this script:

• Since the message data is stored as key/value pairs, the built-in ConvertFrom-StringData cmdlet is used to create the hashtable $ht
• The hashtable is then used to create a psobject
• The psobject is converted into XML using ConverTo-Xml.

One minor issue I ran into is with unenclosed backslashes. If backslashes are enclosed in quotes its fine, if not it causes an error with ConvertFrom-StringData, so I replace them with forward slashes.

Data Is Loaded, Now What?

After I’ve loaded the data I’ll use XQuery to shred the message XML column into a relational data set. I created a function and view in SQL Server for this purpose:

CREATE FUNCTION [dbo].[ufn_GetEventMessage] (@MessageXml XML)
RETURNS @Message TABLE
(
	 ChangeOrder VARCHAR(128) NULL
	,dbname  VARCHAR(128) NULL
	,PSUserName   VARCHAR(255) NULL
	,name   VARCHAR(128) NULL
	,rolename   VARCHAR(128) NULL
	,[Description]   VARCHAR(128) NULL
	,sqlserver   VARCHAR(128) NULL
	,loginame   VARCHAR(128) NULL
	,[login]   VARCHAR(128) NULL
	,DefaultDatabase VARCHAR(128) NULL
)
AS
BEGIN
	INSERT @Message (ChangeOrder,dbname,PSUserName,name,rolename,Description,sqlserver,loginame,[login],DefaultDatabase)
	SELECT
	 Objects.Object.query('Property[@Name="ChangeOrder"]').value('.', 'varchar(128)') AS ChangeOrder
	,Objects.Object.query('Property[@Name="dbname"]').value('.', 'varchar(128)') AS dbname
	,Objects.Object.query('Property[@Name="PSUserName"]').value('.', 'varchar(128)') AS PSUserName
	,Objects.Object.query('Property[@Name="name"]').value('.', 'varchar(128)') AS name
	,Objects.Object.query('Property[@Name="rolename"]').value('.', 'varchar(128)') AS rolename
	,Objects.Object.query('Property[@Name="Description"]').value('.', 'varchar(128)') AS [Description]
	,Objects.Object.query('Property[@Name="sqlserver"]').value('.', 'varchar(128)') AS sqlserver
	,Objects.Object.query('Property[@Name="loginame"]').value('.', 'varchar(128)') AS loginame
	,Objects.Object.query('Property[@Name="login"]').value('.', 'varchar(128)') AS [login]
	,Objects.Object.query('Property[@Name="DefaultDatabase"]').value('.', 'varchar(128)') AS DefaultDatabase
	FROM @MessageXml.nodes('/Objects/Object') AS Objects(Object)
RETURN
END;
GO
 
CREATE VIEW [dbo].[vw_SqlProxyLog]
AS
SELECT l.*, m.*
FROM dbo.SqlProxyLog l
CROSS APPLY dbo.ufn_GetEventMessage(l.MessageXml) m
GO

The function requires I define all the possible attributes as columns. Here’s an example of parsing a typical message:

DECLARE @MessageXml XML
SET @MessageXml =
'<Object></objects><Property Name="DefaultDatabase">master</Property>
<Property Name="PasswordPolicyEnforced">False</Property>
<Property Name="Description" />
<Property Name="sqlserver">[Z002/SQL1]</Property>
<Property Name="password">System.Security.SecureString</Property>
<Property Name="name">test3</Property>
<Property Name="PasswordExpirationEnabled">False</Property>
<Property Name="ChangeOrder">1234</Property>
<Property Name="logintype">SqlLogin</Property>
<Property Name="PSUserName">Z002/u00</Property>
</Object></objects>'
 
SELECT * FROM dbo.ufn_GetEventMessage(@MessageXMl)

Finally I’ll add reference table for the SqlProxyEventType:

CREATE TABLE [dbo].[SqlProxyEventType](
	[EventName] [varchar](50) NOT NULL,
	[EventId] [smallint] NOT NULL,
 CONSTRAINT [PK_SqlProxyEventType] PRIMARY KEY CLUSTERED
(
	[EventId] ASC
)
);
 
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlDatabaseRoleMember', 0)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlLogin', 1)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlServerRoleMember', 2)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Add-SqlUser', 3)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlDatabaseRoleMember', 4)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlLogin', 5)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlServerRoleMember', 6)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Remove-SqlUser', 7)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Rename-SqlLogin', 8)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Set-SqlLogin', 9)
INSERT [dbo].[SqlProxyEventType] ([EventName], [EventId]) VALUES (N'Set-SqlLoginDefaultDatabase', 10)

The last thing I’ll do is setup a schedule SQL Agent job to run the get-sqlproxylog.ps1 script:

–Setup SQL Job with CmdExec Job Step:

–C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -Command c:\scripts\get-sqlproxylog.ps1

Summary

This post demonstrated extracting information from a specially crafted message in an event log and loading the data into a SQL Server table. Its kind of interesting to think about how easy it is to transform data from one thing to the next using Powershell. In this case the data was converted six times:

1. To an Event log message data stored as key/value pairs using Write-Eventlog/Get-Eventlog
2. To a hashtable using ConvertFrom-StringData
3. To a psobject using New-Object -property hashtable
4. To a DataTable using Out-DataTable
5. Into a SQL Server table using Write-DataTable
6. Out of a SQL Server table XML column to relational data set using XQuery

That said, next time I should probably just write the damn thing directly to a database to begin with.

As part of troubleshooting Kerberos authentication for SQL Server I had to verify SPNs so I thought I’d blog about the process I went through. Sometimes I think a post blog is nothing more than public documentation of  a complex problem …

Get SQL Server SPNs from Active Directory

Before Powershell I would use the command-line utility setspn.exe to retrieve a list of SPNs for a given account. The syntax is:

setspn -L AccountName

Although this works reasonably well there are a couple of issues first the  output is text instead of objects which means I’d have to do a lot parsing and if you find you’re parsing text in Powershell too much there’s a high likelihood you’re doing it the hard way!. The second issue with setspn -L is that it expects an account and doesn’t retrieve ALL SPNs for a given service. For these reasons I created a script called Get-SqlSpn which will query Active Directory for all SQL Server SPNs. Querying AD for SPNs still requires a bit of parsing (I’ve taken care of this for you in the Get-SqlSpn function), but not near as much as starting from setspn. Let’s look at how I use Get-SqlSpn…

Using Get-SqlSpn

1. Download Get-SqlSpn
2. Source the function

. ./Get-Sqlspn.ps1

#Get the SQL Server SPNs

 $spns = Get-SqlSpn

If I’m interactively exploring data I like to use a tool for that specific purpose which for me is either loading the data into SQL Server or using Excel. For this particular I think Excel is a better fit. There is some tidying up and normalizing data that needs to be done which is really easy with Excel. Although you could mess with additional scripts to export data directly into Excel or or convert CSV files–a quick and dirty way to get data into Excel is simply copy and paste from out-gridview:
3. Open a new Excel document and rename a worksheet servers
4. Get Column Headers

$object = $spns | select -first 1
$ht = @{}
foreach($property in $object.PsObject.get_properties()) {
  $ht.add($property.Name.ToString(),$property.Name.ToString())
}

5. Copy/Paste heading row to Excel (Ctrl-A, Ctrl-C)

new-object psobject -Property $ht | out-gridview

6. Copy/Paste spns to Excel (Ctrl-A, Ctrl-C)

$spns | out-gridview

Now that you have a list of all SQL Server SPNs from AD, let’s grab some information from all of our SQL Servers to match against. For this purpose I created a script called Get-SqlWmi..

Using Get-SqlWmi

1. Download Get-SqlWmi and Invoke-Sqlcmd2
2. Source the functions

. .\invoke-sqlcmd2.ps1
. .\get-sqlwmi.ps1

I use a  query against System Center Configuration Manager database to pull in a list of SQL Servers. A second approach, if you maintain a SQL Server Center Management Server (CMS)  is to query sysmanagement tables. I’ll then pipe the list of servers to the get-sqlwmi function:

$servers = invoke-sqlcmd2 -ServerInstance 'smsserver\sql10' -Database 'dbautility' -query "select server_name AS 'ComputerName' from sms_sql_server_name_vw" | get-sqlwmi

3. Rename Excel worksheet Sheet2 to servers
4. Get Column Headers

$object = $servers | select -first 1
$ht = @{}
foreach($property in $object.PsObject.get_properties()) {
   $ht.add($property.Name.ToString(),$property.Name.ToString())
}

5. Copy/Paste heading row into Excel

new-object psobject -Property $ht | out-gridview

6. Copy/Paste servers to Excel

$servers | out-gridview

Now that I collected the data, I start analyzing…

Analyzing SPNs

Note: The steps which follow  involve using some basic data analysis functions in Excel. If you’re not sure how to filter, define named ranges or use Excel functions you may brush up on Excel. I’ve also included an Excel document with the appropriate fields and formulas for download here.

First I’ll look for invalid SPNs. I’ve seen issues where people will manually create SPNs incorrectly. The correct format is:

MSSQLSvc/<SQL Server computer name>:1433 AccountName

MSSQLSvc/<SQL Server FQDN>:1433  AccountName

Strangely enough I’ve seen semi-colons or commas instead of colons used which simply doesn’t work. The easiest way to find these SPNs is to use the Text Filter using contains ; or ,

The next thing I’ll do is normalize the data. Get-SqlSpn lists the accounts without the domain prefix, while Get-SqlWmi does so I’ll do a global replace to remove the domain slash.

In order to define a unique key, I’ll create a column which combines the SPN and service account name:

= B6 & ” ”  &C6

I’ll name the column searchSPN, this will allow me to do matching against the server list as we’ll see in a moment.

On the servers worksheet I’ll create two new columns of spn and combined as follows:

= “MSSQLSvc/” & D2 & “.contoso.com:” &A2

= E2 & ” ” &C2

I’ll then name the combine column (unique key) searchServer

Next I’ll add a column called matched to each worksheet and use the MATCH function to look for well, matches between SPN and Server lists:

=MATCH(E2,searchServer,0)

=MATCH(F2,searchSpn,0)

If there’s a match the match column will list the row number of corresponding match and if not you’ll see #NA. You can then filter on #NA and try to figure out why there are mismatches. Did I miss a server in inventory? Are SPNs defined for servers no longer on the network, etc.

And finally I’ll add one more column of a setspn command to delete or add SPNs using a Excel formula:

=”setspn -D ” & E2

=”setspn -A ” & F2

It may seem a little odd to build up commands in this way rather than using Powershell script, but I’m really paranoid about SPNs and want to verify each action. If you’re not careful you can cause some authentication problems by deleting needed SPNs or adding duplicates.

Note: a quick way to find duplicate SPNs is to use the command-line setspn -T * -X. You can also use  Excel’s conditional formatting to quickly identify duplicate values as described in here.  I’ve seen duplicate SPNs cause SQL Server to fallback to NTLM instead of using Kerberos. Searching for duplicate SPNs should be part of  your reconciliation. To fix simply remove the duplicate SPN.

Summary

This post demonstrated how to reconcile SQL Server SPNs between Active Directory and the SQL Server services. I think its  important to point out that you don’t need to take a 100% Powershell approach to a problem. In this example I could have used various Powershell cmdlets (compare-object, where-object, group-object, etc.), however for moderate to complex data analysis I prefer to use tools suited to task of data analysis either Excel or SQL Server. I’ll still use Powershell to gather the data and for simple data analysis tasks.

Join system administrators IT professionals and database professionals in addition to managers at all levels who work with Microsoft technologies for the Sarasota IT Pro Camp on Saturday, February 18th, 2012.

IT Pro Camps are focused on serving the needs of  IT Pro’s (Windows system administrators). IT Pro’s haven’t had many events like our developer counterpart’s code camps and this is why I’m excited to be a part of IT Pro Camp. There’s a definite need for events which serve the IT Pro community. We’ve done Tampa, South Florida, Orlando and in two weeks we’ll add Sarasota to our growing list of cities. We’re also planning on Jacksonville, Pensacola and for the second year we’ll be returning to Tampa and South Florida. We hope to have dates for the remainder of 2012 finalized by the end of March. I’ll be sure to post an update once we work out scheduling and venues.

The Sarasota IT Pro Camp will feature 24 one-hour sessions on topics covering Powershell, BI, SQL Server, Cloud, Active Directory, System Center and Data Security. I’ll be presenting on Storing Powershell Output. Although there are many good sessions to choose from, I’d like to highlight a few sessions which peaked my interest:

We”ll have three sessions, on a topic we haven’t had at previous camps, Data Security.

Adam Malone – Cyber Crime and the FBI

Joseph Schorr – Rule 1: Cardio

Jeff Wolach - Introduction to Next Generation Firewalls

I think this this is one of the interesting things about having IT Pro Camps in different cities, there tends to be a strong technical community around particular disciplines and for Sarasota I’d say its Data Security.

Jose Chinchilla  (blog|twitter) is doing a two-part session on Business Intelligence.

Jose Chinchilla - Introduction to Microsoft Business Intelligence

Jose Chinchilla - Taking Business Intelligence to the next level with SharePoint 2010!

This is a good opportunity for attendees to get both an introduction and more advanced overview on business intelligence. Two-part series are kind of unique to community events and you tend not to see a two-part series at paid conferences.

Be sure to register to attend the free Sarasota IT Pro Camp event. A continental breakfast and lunch will be provided. Please tell your colleagues about IT Pro Camps. I look forward to seeing you there!

–Chad Miller

Ed Wilson (Blog|Twitter) aka Scripting Guy is kicking off another guest blogger week  (Nov 28th 2011) with my guest blog post, Four Easy Ways to Import CSV Files to SQL Server with PowerShell. The post demonstrates the following approaches to importing CSVs into a SQL Server table:

• T-SQL BULK INSERT command
• LogParser command-line
• LogParser COM-based scripting
• A Windows Powershell-based approach using several functions

Managing SQL Server security changes in mass is something which screams automate it. Let’s look a at few examples using either T-SQL, a Centeral Management Server and Powershell.

Task #1 Remove a SQL Server Login from the Sysadmin Role

Let’s say you’re given the task to remove a login from the sysadmin role on 50 servers.  For this task we can use the built-in system stored procedures sp_dropsrvrolemember  and sp_helpsrvrolemember.

Since we want to do this across multiple servers, we’ll use a multiserver query from the registered central management:

1. First create a before removal “backup” using sp_helpsrvrolemember:

sp_helpsrvrolemember 'sysadmin'

2. Save the output
3. Next run

EXEC sp_dropsrvrolemember 'CONTOSO\SQL_SecurityAdmin', 'sysadmin'

Error handling is generally good thing, but there are cases were can do a task without much error handling especially if the task is interactive and you have backups. Although you can wrap some T-SQL code to check if the login exists on the server or if the login is member of the server before attempting to removing from the sysadmin, its not necessary any errors can safely be ignored. A Central Management Server is great at one off commands with good T-SQL coverage

Task #2 Remove Linked Server Login Mappings

After removing the login from the sysadmin you discover they are also mapped to sa on over 100  Linked Servers. Since you can’t  remove the login from the server because they still need non-administration access, your task to to remove the linked server login mapping. Changing linked server security  in mass is something where Powershell provides a good solution. You’ll probably want to backup the linked server by scripting out the linked server before any changes which is difficult to do in T-SQL, but easy with Powershell. Also removing a login mappings from all linked servers  is very procedural which is awkward in T-SQL. So here’s a Powershell solution I created in the form of a few Powershell filters and functions called LibraryLinkedServer.

. .\LibraryLinkedServer.ps1
$logins = @(
'Contoso\Bill'
'Contoso\John'
'Contoso\Jill'
)
Get-CMRegisteredServer "Z001\SQL1" "PRD" | Backup-LinkedServer -LinkedServerLogins $logins
Get-CMRegisteredServer "Z00\SQL1" "PRD" | Remove-LinkedServerLogin -LinkedServerLogins $logins

I would suggest running the code from Powershell ISE so you can step through the code by highlighting and running each line.

The solution first sources the LibraryLinkedServer functions and filters i.e. loads the functions into our current Powershell session. Next we define our list of logins t removing from linked server mappings in an array called $logins. The final two steps involve obtaining a list of SQL Servers from the Central Managment Server Z001\SQL1 and the server group PRD. Next we’ll script out the linked servers using the Backup-LinkedServer function. At this stage I would manually verify the backup files before proceeding with removing the login mappings using Remove-LinkedServerLogin.

Task #3 Remove a Linked Server

You discover linked servers in development which point to production. The linked server should be removed entirely. Your task is to remove a specific linked server named PROD1  from all development servers.  Here again we’ll use the LibraryLinkedServer functions:

. .\LibraryLinkedServer.ps1
Get-CMRegisteredServer "Z001\SQL1" "DEV" | Backup-LinkedServer -LinkedServer "PROD1"
Get-CMRegisteredServer "Z001\SQL1" "DEV" | Remove-LinkedServer -LinkedServer "PROD1"

This task is very similar to task #2 and much of the code is the same, except here we’re specifying a linked server name rather than an array of logins. Again I would suggest manually verifying the backup files before proceeding with removing the linked server.

Note: If you use this solution make sure you test, make backups and verify.

Although you can use SQL Server Management Studio or T-SQL to manage AlwaysOn, SQL Server Denali CTP 3 includes 25 cmdlet which together provide complete coverage for creating, confiiguring and administering the AlwaysOn database feature. In this post we’ll look at using Powershell to perform various management tasks for AlwayOn.

Note: This blog post describes features in SQL Server Denali CTP 3 which may change on final product release.

Getting Started

You’ll need  a simple Windows 2008 R2 cluster with two standalone installs of SQL Server. I say simple because you don’t have to worry about shared storage, quorum disks and shared MSTDTC installations like you would in a traditional SQL Server installation on a Windows Server Failover Cluster. All you need are two servers running Windows Server 2008 R2 Enterprise Edition. For test purposes I’ve setup a two-node Windows Server Failover Cluster as follows:

1. Configure a Private virtual machine network for intra-cluster communication. Note this step is optional and not really necessary for a bare minimum cluster, but I setup it up anyways to mimic close to what I’ll have in production. This network uses a separate IP subnet than the Internal only network I had already setup in Hyper-V.

2. Setup the private only network  which allows communication between virtual machines only.

1. Right-click virtual machine
2. Select Virtual Network Manager > Select Private > Add

3. Add the private network to each of the virtual machines.

1. Shutdown each machine
2. Select machine in Hyper-V Manager
3. Select Settings
4. Select Add Hardware and choose Network Adapter and click Add
5. Select the private network you created from the Network drop down list and click OK

4. On the virtual machines assign IP addresses under Network and Sharing Center. Here’s a table of my setup:

DC1= Domain Controller

Cluster1 = cluster management IP (assigned during cluster configuration)

Availability Group Listener (assigned during AlwaysOn  Availability Group Listener configuration)

*Don’t worry about these for now.

5. Since we’re using a two-node cluster without a quorum disk it is suggested to use a Node and File Share Majority so I’ll setup network share which is read/write accessible by the Cluster Service account. For my testing purposes I created share on my DC1 machine called \\DC1\Share1 located on DC1 C:\Share1 folder.

Setting Up Windows Failover Clustering

1.  Add the Failover Cluster Manage feature to both modes by running the following  PowerShell commands

import-module ServerManager
Add-WindowsFeature -Name Failover-Clustering

2. Create the cluster by running the following PowerShell commands on one node:

import-module FailoverClusters
new-cluster clusterxm -Node node1,node2 -StaticAddress 192.168.1.70 -NoStorage

3. Set the quorum mode to Node and File Share Majority by running the following command on one node:

 Set-ClusterQuorum -NodeAndFileShareMajority \\DC1\share1">\\DC1\share1

Install SQL Server on Both Nodes

Install SQL Server and this important – As a standalone instance. Sorry no Powershell commands here just run through the installation screens. Be sure to set the SQL Server service account to a domain account (I had issues when using Local System).

Database Prerequisites

You need to have a database which is not already part of an AlwaysOn Availability Group in FULL recovery mode and has been backed up. As a test I’ll just use the old school pubs sample database. Run the instpubs.sql file and create a backup using Powershell.

Start SQL Server Management Studio and select “Start PowerShell” from Object Explorer. Run the following command to backup the database to the default backup directory:

PS SQLSERVER:\SQL\NODE1\DEFAULT\Databases\pubs> Backup-SqlDatabase -Database pubs

You’ll need to create a share accessible by both nodes for storing the SQL Server database and transaction log initialization backups. For my example I’ll create a folder called sqlrec under Node1’s C drive C:\sqlrec and share named sqlrec \\node1\sqlrec

AlwaysOn Powershell Documentation

The CTP3 version of Books Online contains some documentation and scripts for configuring AlwaysOn however as to be expected with pre-release software some topics are not covered and there are documentation errors in other sections. As I’ve encountered documentation errors I  submitted Connect Items  (see Connect Items below for details).  Relevant helps topics included in CTP3 are listed below:

Specify the Endpoint URL When Adding or Modifying an Availability Replica

Enable and Disable AlwaysOn

Create and Configure an Availability Group

Rather than use the scripts includes with Books Online which only handle part of the configuration or write my own script I think its more important to demonstrate the commands to create and configure AlwaysOn. By approaching Powershell as simply running command versus vs. writing a script you’ll learn how to use Powershell commands for new administration functions. Once you’re happy with the results of the commands you then can turn the series of commands into a script. Let’s get started…

Using Powershell to Create and Configure AlwaysOn

Note: The following examples work within the SQLServer provider while connected to specific SQL Server machines. In my example I’m using machines named node1 and node2  running a default instance. Pay particular attention to the context in which the commands are run on either Node1 (primary) or Node2 (secondary).

Enable HADRService on both nodes:

PS SQLSERVER:\SQL\NODE1\DEFAULT> Enable-SqlHADRService -ServerInstance NODE1 -force
PS SQLSERVER:\SQL\NODE1\DEFAULT> Enable-SqlHADRService -ServerInstance NODE2 -force

Note: The force switch. Enabling or disabling HADR requires SQL Server service to be restarted. If you omit the force switch you’ll be prompted to confirm SQL Server restart.

Optionally confirm HADRService enabled on both nodes:

PS SQLSERVER:\SQL\NODE1\DEFAULT> get-item . | select IsHadrEnabled
 
IsHadrEnabled
-------------
True
 
PS SQLSERVER:\SQL\NODE1\DEFAULT> pushd
PS SQLSERVER:\SQL\NODE1\DEFAULT> cd SQLServer:\SQL\NODE2\DEFAULT
PS SQLSERVER:\SQL\NODE2\DEFAULT> get-item . | select IsHadrEnabled
 
IsHadrEnabled
-------------
True
 
PS SQLSERVER:\SQL\NODE2\DEFAULT> popd

Note: In order to retrieve the IsHadrEnabled property I need to to cd to node2  and in order to easily change directories back I’m using the pushd and popd commands to store the current location (pushd) and switch back (popd).

Configure HADR Endpoints

Configure HADR Endpoints and set state to started:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

PS SQLSERVER:\SQL\NODE1\DEFAULT> cd .\Endpoints
PS SQLSERVER:\SQL\NODE1\DEFAULT\Endpoints> New-SqlHADREndpoint -Name "hadr_endpoint" -Port 5022
 
Name
----
hadr_endpoint
 
PS SQLSERVER:\SQL\NODE1\DEFAULT\Endpoints> dir | Set-SqlHADREndpoint -State "Started"
 
Name
----
hadr_endpoint
 
PS SQLSERVER:\SQL\NODE1\DEFAULT\Endpoints> cd SQLServer:\SQL\NODE2\DEFAULT\Endpoints
PS SQLSERVER:\SQL\NODE2\DEFAULT\Endpoints> New-SqlHADREndpoint -Name "hadr_endpoint" -Port 5022
 
Name
----
hadr_endpoint
 
PS SQLSERVER:\SQL\NODE2\DEFAULT\Endpoints> dir | Set-SqlHADREndpoint -State "Started"
 
Name
----
hadr_endpoint

Backup Database and Transaction Log

1
2
3

PS SQLSERVER:\SQL\NODE2\DEFAULT\Endpoints> cd SQLServer:\SQL\NODE1\DEFAULT
PS SQLSERVER:\SQL\NODE1\DEFAULT> Backup-SqlDatabase pubs \\NODE1\sqlrec\pubs.bak
PS SQLSERVER:\SQL\NODE1\DEFAULT> Backup-SqlDatabase pubs \\NODE1\sqlrec\pubs.trn -BackupAction Log

Create Replicas

Note: This doesn’t actually create the replicate, rather the –AsTemplate parameter allows you to create a definition of the replica which is stored in the $replica1 and $replica2 variables. These variables will be used when creating the availability group next.

1
2

PS SQLSERVER:\SQL\NODE1\DEFAULT> $replica1 = New-SqlAvailabilityReplica -Name NODE1 -EndpointURL "TCP://NODE1:5022" -AsTemplate -AvailabilityMode SynchronousCommit -FailoverMode Automatic  -ConnectionModeInSecondaryRole AllowAllConnections
PS SQLSERVER:\SQL\NODE1\DEFAULT> $replica2 = New-SqlAvailabilityReplica -Name NODE2 -EndpointURL "TCP://NODE2:5022" -AsTemplate -AvailabilityMode SynchronousCommit -FailoverMode Automatic -ConnectionModeInSecondaryRole AllowAllConnections

Create Availability Group

1
2
3
4
5

PS SQLSERVER:\SQL\NODE1\DEFAULT> New-SqlAvailabilityGroup AVGPubs -AvailabilityReplica ($replica1,$replica2) -Database pubs
 
Name                 PrimaryReplicaServerName
----                 ------------------------
AVGPubs              NODE1

Join Availability Group on Secondary Node

1
2
3
4

PS SQLSERVER:\SQL\NODE1\DEFAULT> pushd
PS SQLSERVER:\SQL\NODE1\DEFAULT> cd SQLServer:\SQL\NODE2\DEFAULT
PS SQLSERVER:\SQL\NODE2\DEFAULT> Join-SqlAvailabilityGroup -Name AVGPubs
PS SQLSERVER:\SQL\NODE2\DEFAULT> popd

Optionally Verify Availability Groups

1
2
3
4
5
6
7

PS SQLSERVER:\SQL\NODE1\DEFAULT> cd .\AvailabilityGroups
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> dir | select -ExpandProperty AvailabilityReplicas | select name, ConnectionModeInPrimaryRole, ConnectionModeInSecondaryRole
 
Name                                                ConnectionModeInPrimaryRole           ConnectionModeInSecondaryRole
----                                                ---------------------------           -----------------------------
NODE1                                                       AllowAllConnections                     AllowAllConnections
NODE2                                                       AllowAllConnections                     AllowAllConnections

Restore Database and Transaction Log On Secondary Node

1
2
3

PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> cd SQLServer:\SQL\NODE2\DEFAULT
PS SQLSERVER:\SQL\NODE2\DEFAULT> Restore-SqlDatabase pubs \\NODE1\sqlrec\pubs.bak  -NoRecovery
PS SQLSERVER:\SQL\NODE2\DEFAULT> Restore-SqlDatabase pubs \\NODE1\sqlrec\pubs.trn  -RestoreAction "Log" -NoRecovery

Add Database to Availability Group on Secondary Node

1
2

PS SQLSERVER:\SQL\NODE2\DEFAULT> cd .\AvailabilityGroups
PS SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups> dir | Add-SqlAvailabilityDatabase -Database pubs

Create Availability Group Listener

Note: This is what you connect to (or a least that’s my impression) from your client machines. The listener provides a network name and IP Address which will failover between nodes.

1
2
3
4
5

cd SQLSERVER:\
PS SQLSERVER:\> New-SqlAvailabilityGroupListener -Name Network1 -StaticIp 192.168.1.73/255.255.255.0 -path SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs
Name                 PortNumber      ClusterIPConfiguration
----                 ----------      ----------------------
Network1             1433            ('IP Address: 192.168.1.73')

Optionally Verify Listener Connectivity

1
2
3
4
5
6
7

PS SQLSERVER:\SQL\NODE1\DEFAULT>cd SQLSERVER:\SQL\NODE1\DEFAULT
PS SQLSERVER:\SQL\NODE1\DEFAULT> Invoke-Sqlcmd -ServerInstance Network1 -Database master -Query "select @@servername"
WARNING: Using provider context. Server = NODE1.
 
Column1
-------
NODE1

Determine AlwaysOn Health

SQL Server includes three cmdlets for verifying the health of the various AlwaysOn components:

1
2
3
4
5
6
7

PS SQLSERVER:\SQL\NODE1\DEFAULT> get-command -module sqlps -Name test-*
 
CommandType     Name                                                Definition
-----------     ----                                                ----------
Cmdlet          Test-SqlAvailabilityGroup                           Test-SqlAvailabilityGroup [[-Path] <string   []>] [...
Cmdlet          Test-SqlAvailabilityReplica                         Test-SqlAvailabilityReplica [[-Path] <string   []>]...
Cmdlet          Test-SqlDatabaseReplicaState                        Test-SqlDatabaseReplicaState [[-Path] <string   []>...

The easiest way to run the test cmdlets is within the SQL Server provider context as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

PS SQLSERVER:\SQL\NODE1\DEFAULT> cd .\AvailabilityGroups
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> dir | Test-SqlAvailabilityGroup
 
HealthState            Name
-----------            ----
Healthy                AVGPubs
 
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> dir .\AVGPubs\AvailabilityReplicas | Test-SqlAvailabilityReplica
 
HealthState            AvailabilityGroup    Name
-----------            -----------------    ----
Healthy                AVGPubs              NODE1
Healthy                AVGPubs              NODE2
 
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups> dir .\AVGPubs\DatabaseReplicaStates | Test-SqlDatabaseReplicaState
 
HealthState            AvailabilityGroup    AvailabilityReplica  Name
-----------            -----------------    -------------------  ----
Healthy                AVGPubs              NODE1                pubs
Healthy                AVGPubs              NODE2                pubs

Manually Failing Over an Availability Group

To manually fail over an Availability Group we use the Switch-SqlAvailabilityGroup cmdlet. Interestingly enough I could not figure out a way to fail over the availability resource using the GUI in CTP3, so Powershell is the only way to I could do this for now which is fine by me

Note: Be aware the context in which you run the Switch-SqlAvailabilityGroup cmdlet. The cmdlet should be run from whichever node is functioning as the secondary as we will see in a moment:

1
2

PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups>cd AVGPubs
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> Switch-SqlAvailabilityGroup

Running the switch-sqlavailabilityGroup command on the prmiary produces the following error:

1
2
3
4
5
6
7
8
9
10

Switch-SqlAvailabilityGroup : The local availability replica of availability group 'AVGPubs' cannot accept signal 'FAIL
OVER_PENDING' in its current role 'PRIMARY_NORMAL' and state (configuration is in Windows Server Failover Clustering st
ore, local availability replica has joined).  The availability replica signal is invalid given the current replica role
.  Verify that the signal is permitted based on the current role of the local availability replica, and retry the opera
tion.
At line:1 char:28
+ Switch-SqlAvailabilityGroup <<<<
    + CategoryInfo          : InvalidOperation: (:) [Switch-SqlAvailabilityGroup], SqlException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.Hadr.FailoverSqlAvailabilityGr
   oupCommand

At first I thought I had configured something incorrectly, but then was able to to failover the availability group through Failover Cluster Manager. It was then I realized this needs to be run from the context of the secondary node.

1
2
3
4
5

PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> pushd
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> cd SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups\AVGPubs
PS SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups\AVGPubs> Switch-SqlAvailabilityGroup
PS SQLSERVER:\SQL\NODE2\DEFAULT\AvailabilityGroups\AVGPubs> popd
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> Switch-SqlAvailabilityGroup

Pausing and Resuming an Availability Group

You can pause and then resume synchronization of the Always database using the suspend-SqlAvailabilityDatabase and Resume-SqlAvailabilityDatabase cmdlets:

1
2
3

PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs> cd .\AvailabilityDatabases
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs\AvailabilityDatabases> dir | Suspend-SqlAvailabilityDatabase
PS SQLSERVER:\SQL\NODE1\DEFAULT\AvailabilityGroups\AVGPubs\AvailabilityDatabases> dir | Resume-SqlAvailabilityDatabase

Summary

This post has demonstrated the Powershell cmdlets available to help you manage your AlwaysOn configuration. The commands could be used to build a reusable script to provide a consistent configuration of AlwaysOn.

My testing was completed on SQL Server CTP3 as I encountered documentation issues I logged Connect Items. I’ve included a list of Connect Items below. There are other issues with the documentation which I did not log because of missing documentation rather than documentation bugs. I think not having all the documentation complete is to be expected while a product is still in CTP.

I’ll save my commentary on the AlwaysOn cmdlets for a future post, but for now I will say I’m impressed with the coverage and ease of use provided by the AlwaysOn cmdlets and SQL Server provider.

Connect Items

Determine Whether AlwaysOn Availability Groups is Enabled

Enable and Disable AlwaysOn (SQL Server) Documentation

Create and Configure an Availability Group (SQL Server PowerShell) Doc Error

New-SqlAvailabilityReplica cmdlt Allows Incompatible settings

Set-SqlAvailabilityReplica Cmdlet Does Not Take Pipeline input

New-SqlAvailabilityGroupListener Help Example Incorrect

Creating a Network Name for AlwaysOn Availability Groups Obsolete

If you ever need to flatten out groups which may include nested local and AD groups there’s a really easy way to do this in  the System.DirectoryServices.AccountManagement.GroupPrincipal GetMembers method. Here’s some PowerShell code which works against both local and AD groups. The code can easily be adapted into a function and in fact I’m using similar code in the SQLPSX project for SQL Server permission auditing:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

add-type -AssemblyName System.DirectoryServices.AccountManagement
 
$domain = "$env:computername"
$groupname = "Administrators"
 
#Determine if domain a machine or domain
try {
    $domainName = [System.DirectoryServices.ActiveDirectory.Domain]::GetComputerDomain() | select -ExpandProperty Name
    $isDomain = $domainName -match "$domain\."
}
catch {
    $isDomain = $false
}
 
if ($isDomain)
{ $ctype = [System.DirectoryServices.AccountManagement.ContextType]::Domain }
else
{ $ctype = [System.DirectoryServices.AccountManagement.ContextType]::Machine }
 
#Create objects to filter based on group name and ContextType--Domain or Machine
$principal = new-object System.DirectoryServices.AccountManagement.PrincipalContext $ctype,$domain
$groupPrincipal = new-object System.DirectoryServices.AccountManagement.GroupPrincipal $principal,$groupname
$searcher = new-object System.DirectoryServices.AccountManagement.PrincipalSearcher 
$searcher.QueryFilter = $groupPrincipal
 
#Note GetMembers($true) recursively enumerates groups members while GetMembers() simply enumerates group members
$searcher.FindAll() | foreach {$_.GetMembers($true)}