- \n
- SSIS Transfer Logins Task<\/a> is an SSIS based version of xp_help_revlogin but it doesn\u2019t retain the SQL account passwords for you.<\/li>\n<\/ul>\n
- \n
- For a more complete solution, including objects like linked server, check out this SSIS \/ C# solution on MSSQLTips, here<\/a>.<\/li>\n<\/ul>\n
- \n
- Phil Factor has provided us with a PowerShell solution<\/a> which I prefer, if you do not have input into your deployment processes or if a lot of ad hoc scripts and configurations end up in production.<\/li>\n<\/ul>\n
All of these options have their merits and I have personally used the last bullet for many servers in the past. However, I prefer to keep all of the server objects in sync at deploy-time, whenever I can. Being able to interject a step into your company\u2019s deployment process might be a dream for some of us but that is what I am recommending.<\/p>\n
Synchronization method<\/h2>\n
The method that I recommend is a pre-deployment step. For those of you using SSDT, this would be like a pre-pre<\/strong><\/em>-deployment step because I am referring to an exterior process that will come before the SSDT pre-deployment script. With Availability Groups specifically, we can take advantage of the dynamic management view\u00a0sys.availability_replicas<\/a>\u00a0to identify the replicas in the group. This is useful because your deployers can push to the primary node, or the Availability Group Listener, and your scripts will still update all nodes without any form of hard coding.<\/p>\n
Below is a PowerShell script that I have used to deploy SQL files to the various nodes of my Availability Groups. First it will detect the nodes of the AG, and then spawn asynchronous jobs to deploy the server objects to each one.<\/p>\n
Finding the nodes (T-SQL)<\/h3>\n
SELECT r.replica_server_name\n, g.name [availability_group_name]\nFROM sys.availability_replicas r\nINNER JOIN sys.availability_groups g\nON g.group_id = r.group_id<\/code><\/pre>\nPowerShell deploy<\/h3>\n
###################################################################################\n###\t\t\t\t\t\t\t\tHelp Context\t\t\t\t\t\t\t\t\t###\n###################################################################################\n\n<#\n.SYNOPSIS<\/p>\n\n
.DESCRIPTION<\/p>\n\n
.EXAMPLE<\/p>\n\n
.NOTES<\/p>\n\n
.INPUTS\n[string]$serverName - \"The name of one of the replicas in an Availability Group\"\n[string]$availabilityGroupName - \"The name of the availability group that you are targeting. Optional, if there is only one group.\"<\/p>\n\n
.OUTPUTS\nNone.\n#><\/p>\n\n
[CmdletBinding(SupportsShouldProcess=$TRUE)]\nParam\n(\n\t[Parameter(Mandatory=$true,\n\t\t\t ValueFromPipeline=$true,\n\t\t\t ValueFromPipelineByPropertyName=$true,\n\t\t\t HelpMessage=\"The name of one of the replicas in an Availability Group\")]\n\t[string]$serverName,\n [Parameter(Mandatory=$false,\n\t\t\t ValueFromPipeline=$true,\n\t\t\t ValueFromPipelineByPropertyName=$true,\n\t\t\t HelpMessage=\"The name of the availability group that you are targeting. Optional, if there is only one group.\")]\n\t[string]$availabilityGroupName\n)<\/p>\n\n
##############################################\n
Set global variables<\/h1>\n##############################################<\/p>\n\n
$Error.Clear();\n$currentLocation = Get-Location;\n$errorLogFileName = \"$currentLocation\\DatabaseServerObjects-Deployment-Log.txt\";\n$deploymentFile = \"$currentLocation\\DatabaseServerObjects-Deployment.sql\";\n$queryTimeout = 60;\n$connectionTimeout = 30;\n$date = Get-Date -Format G;<\/p>\n\n
##############################################\n
Establish error handling<\/h1>\n##############################################<\/p>\n\n
Start-Transcript $errorLogFileName;\nWrite-Host \"Executing scripts in $deploymentFile | begun at: $date \" -foregroundcolor darkblue -backgroundcolor green;<\/p>\n\n
Trap\n\t{\n\t\t# Handle the error\n\t\t$err = $_.Exception\n\t\twrite-error $err.Message\n\t\twhile( $err.InnerException ) {\n\t\t\t$err = $err.InnerException\n\t\t\twrite-error $err.Message\n\t\t};\n\t}<\/p>\n\n
set-psdebug -strict\n$ErrorActionPreference = \"continue\"<\/p>\n\n
##############################################\n
Initialization<\/h1>\n##############################################<\/p>\n\n
Import-Module SQLPS -DisableNameChecking;<\/p>\n\n
##############################################\n
Set variables & Input validation<\/h1>\n##############################################<\/p>\n\n
$connString = \"Data Source=$serverName; MultiSubnetFailover=True; Integrated Security=True; Connection Timeout=$connectionTimeout\";\n$conn = New-Object System.Data.SqlClient.SqlConnection $connString;\n$selectedReplica = New-Object Microsoft.SqlServer.Management.SMO.Server $conn;<\/p>\n\n
if($selectedReplica.Databases[\"master\"] -eq $null)\n{\n\tthrow \"A connection for $serverName could not be established.\";\n\treturn;\n}<\/p>\n\n
$sqlCmd = \"SELECT r.replica_server_name, g.name [availability_group_name] FROM sys.availability_replicas r INNER JOIN sys.availability_groups g ON g.group_id = r.group_id\";\n[System.Data.DataTable]$availabilityGroupConfiguration = $selectedReplica.Databases[\"master\"].ExecuteWithResults($sqlCmd).Tables[0];<\/p>\n\n
if($availabilityGroupConfiguration.Rows.Count -eq 0)\n{\n write-warning \"No Availability Group found. Deploying to only the server instance inputted.\";\n [string]$availabilityGroup = $availabilityGroupName;\n [System.Data.DataRow]$newRow = $availabilityGroupConfiguration.NewRow();\n $newRow[\"availability_group_name\"] = $availabilityGroupName;\n $newRow[\"replica_server_name\"] = $serverName;\n [System.Data.DataTable]$availabilityGroupConfiguration.Rows.Add($newRow);\n}\nelse\n{\n $availabilityGroup = $availabilityGroupConfiguration.availability_group_name | Get-Unique;\n}<\/p>\n\n
If there is only one AG, then the object type will be System.String.<\/h1>\n
System.Object[] indicates multiple rows, so we need to look for the AG name from the input and purge other records.<\/h1>\nif($availabilityGroup.GetType().FullName -eq 'System.Object[]')\n{\n for($i = 0; $i -lt 0; $i++)\n {\n if($availabilityGroupConfiguration.Rows[$i].availability_group_name -ne \"$availabilityGroupName\")\n {\n $availabilityGroupConfiguration.Rows[$i].Delete();\n }\n }\n\n
if($availabilityGroupConfiguration.Rows.Count -eq 0)\n {\n throw \"Could not determine Availability Group or the inputted Availability Group name was invalid.\";\n }<\/p>\n\n
[string]$availabilityGroup = $availabilityGroupName;\n}<\/p>\n\n
if(($availabilityGroup -ne $availabilityGroupName) -and ($availabilityGroupName.Length -gt 0))\n{\n throw \"Derived Availability Group does not match the inputted Availability Group name.\";\n}<\/p>\n\n
##############################################\n
Execute<\/h1>\n##############################################<\/p>\n\n
$jobs = @();\n[string]$serverList = \"\";\nforeach($replica in $availabilityGroupConfiguration)\n{\n $serverList += ($replica.replica_server_name);\n $serverList += \", \";<\/p>\n\n
[string[]]$args = @();\n $args += $replica.replica_server_name;\n $args += \"master\";\n $args += $queryTimeout;\n $args += $connectionTimeout;\n $args += $deploymentFile;\n $args += $currentLocation;<\/p>\n\n
$scriptBlock = {[Environment]::CurrentDirectory = $args[5]; Invoke-Sqlcmd -ServerInstance $args[0] -Database $args[1] `\n -QueryTimeout $args[2] -ConnectionTimeout $args[3] -InputFile $args[4] -Verbose }<\/p>\n\n
$jobs += Start-Job -ScriptBlock $scriptBlock -ArgumentList $args;\n}\n$serverList = $serverList.Substring(0,$serverList.Length-2);<\/p>\n\n
$jobs | Wait-Job | Receive-Job | Remove-Job;<\/p>\n\n
##############################################\n
Output<\/h1>\n##############################################<\/p>\n\n
$date = Get-Date -Format G;\nWrite-Host \"The scripts in $deploymentFile have been executed on for: $serverList | completed at: $date \" -foregroundcolor darkblue -backgroundcolor green;<\/p>\n\n
Stop-Transcript;<\/p>\n\n
if ($Error.Count -ne 0)\n{\n\t$ErrorActionPreference = \"stop\"\n\tthrow;\n}<\/code><\/pre>\n<\/p>\n
Create object scripts<\/h3>\n
You can see, above, that I am calling a T-SQL file named\u00a0DatabaseServerObjects-Deployment.sql<\/em>. This is where all of the real work happens. I like to use the SQLCMD r: command<\/a>\u00a0to call smaller, more manageable scripts rather than one large script. I\u2019d start with this\u00a0directory structure.<\/p>\n
![\"\"](\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/07\/project-dir-tree.png\")
<\/a><\/p>\nEach file should contain repeatable T-SQL (read more on repeatable T-SQL here<\/a><\/em>), such as \u2026<\/p>\n
USE [master]\n\nIF EXISTS (SELECT TOP 1 1 FROM sys.server_principals WHERE name = N'LIBERTY\\demo1')\n\tAND IS_SRVROLEMEMBER ('sysadmin','LIBERTY\\demo1') = 0\nBEGIN\n\tEXEC sp_addsrvrolemember @loginame = 'LIBERTY\\demo1', @rolename = 'sysadmin'\nEND<\/p>\n\n
IF EXISTS (SELECT TOP 1 1 FROM sys.server_principals WHERE name = N'LIBERTY\\demo2')\n\tAND IS_SRVROLEMEMBER ('sysadmin','LIBERTY\\demo2') = 0\nBEGIN\n\tEXEC sp_addsrvrolemember @loginame = 'LIBERTY\\demo2', @rolename = 'sysadmin'\nEND<\/code><\/pre>\n<\/p>\n
Then, use this T-SQL script to call the various pieces.<\/p>\n
:setvar dtm \"SYSUTCDATETIME()\"\n:setvar dtm2012 \"CONVERT(DATETIME2,'1\/1\/2012')\"\n:setvar dtm2013 \"CONVERT(DATETIME2,'1\/1\/2013')\"\n:setvar userAdmin \"'00000000-0000-0000-0000-000000000000'\"\n\nBEGIN\nDECLARE @vServerObjectsDeploymentStartTime DATETIME = GETDATE();\nPRINT 'Server Context: ' + @@SERVERNAME\nPRINT\n'\n +-+-+-+-+-+ +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+\n Begin -- Server Objects Deployment Script\n +-+-+-+-+-+ +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+\n'\n+ CONVERT(VARCHAR(30),GETDATE(),120);<\/p>\n\n
--Security\nPRINT 'Executing CreateLogins.sql'\n:r .\\Logins\\CreateLogins.sql<\/p>\n\n
PRINT 'Executing ServerRoleMemberships.sql'\n:r .\\ServerAccess\\ServerRoleMemberships.sql<\/p>\n\n
PRINT 'Executing ServerPermissions.sql'\n:r .\\ServerAccess\\ServerPermissions.sql<\/p>\n\n
PRINT 'Executing SystemDatabaseUsers.sql'\n:r .\\SystemDatabaseObjects\\SystemDatabaseUsers.sql<\/p>\n\n
PRINT 'Executing SystemDatabaseRoleMemberships.sql'\n:r .\\SystemDatabaseObjects\\SystemDatabaseRoleMemberships.SQL<\/p>\n\n
--SQL Agent Operators\nPRINT 'Executing DBA.sql'\n:r .\\SQLAgent\\Operators\\DBA.SQL<\/p>\n\n
--Extended Event Sessions\nPRINT ' Executing Successful-Logins.sql'\n:r .\\ExtendedEventSessions\\Successful-Logins.sql<\/p>\n\n
--SQL Agent Jobs\nPRINT 'Executing VerifyReplicaLogins.sql'\n:r .\\SQLAgent\\Jobs\\VerifyReplicaLogins.sql<\/p>\n\n
PRINT '\n +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+\n Server Objects Deployment Script - Duration =\n +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+-+ +-+\n '\n+ CONVERT(VARCHAR(5),DATEDIFF(ss,@vServerObjectsDeploymentStartTime,GETDATE())) + ' seconds';\nPRINT '\n +-+-+-+ +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+\n End -- Server Objects Deployment Script\n +-+-+-+ +-+-+-+-+-+-+-+-+-+-+-+-+-+-+ +-+-+-+-+-+-+ +-+\n '\n + CONVERT(VARCHAR(30),GETDATE(),120);\nEND<\/code><\/pre>\n<\/p>\n
Once that is all in place, you can call the PowerShell script as a pre-deployment step every time and your entire Availability Group will be in-sync across all replicas.<\/p>\n
\n![\"\"](\"\/wp-content\/uploads\/hammer-derik-172x140.png\")
Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\nDerik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n
For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
After we create a strategy for synchronizing our data for disaster recovery, we then need to consider keeping our server objects synchronized. Depending upon which objects you are working with there are various solutions available to you. xp_help_revlogin (2005 and below, 2008 and higher) is a MS created, T-SQL based, solution for moving logins while […]<\/p>\n","protected":false},"author":1,"featured_media":574,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-119","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/119","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=119"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/119\/revisions"}],"predecessor-version":[{"id":381,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/119\/revisions\/381"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/574"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=119"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=119"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=119"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Phil Factor has provided us with a PowerShell solution<\/a> which I prefer, if you do not have input into your deployment processes or if a lot of ad hoc scripts and configurations end up in production.<\/li>\n<\/ul>\n
- For a more complete solution, including objects like linked server, check out this SSIS \/ C# solution on MSSQLTips, here<\/a>.<\/li>\n<\/ul>\n