SQL upgrade scripts on a snap of finger.

As generally happen on Fridays, all the big stress filled news comes on this day :) On one of the friday,  I was told  that we need to upgrade our 30+ SQL 2008 servers to 2012. Operations wants clean SQL scripts to run on SQL 2012 and can we have all the needed scripts by Monday.

You may know, when you backup and restore databases from one version to another, databases will carry all the pertinent information about themselves but there is nothing to move the Login, roles, Linked Server, replication and Job information. Only way to move them is to script them out from old server and then run those scripts in new server.

Doing this manually is out of question, as you have 30 server for each environment and there were three different environments, Test, PPE and Production. I knew PowerShell can come to rescue, but  I didn’t know PowerShell. However, it is easy enough that you can learn it in few hours and write a script in few hours. I am sharing the scripts which I wrote after few hours of learning PowerShell. This is quick and dirty, one time use scripts. Feel free to improve it and share it.

This script will create all the desired SQL scripts. Make sure to update the server names and the output path in scripts. Second, run this with x86 version of PowerShell ISE. Somehow replication server had problem with 64 bit ISE.

Set proper execution policy and load SQL module before your execute this script.

Get-ExecutionPolicy
set-executionpolicy unrestricted
Import-Module “sqlps” -DisableNameChecking


Get SQL server PowerShell module from here.


# NOTE!!! To generate replication scripts you need to run this script from PowerShell ISE (x86) - Seriously

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO") | Out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication.ReplicationServer") | out-null

$SqlServers = "YOURSERVERNAME1" ,"YOURSERVERNAME2" ;
$nl = [Environment]::NewLine

foreach($SQLSvr in $SQLServers)
{
$rootDir = "C:\test\"; # NOTE: Change this output directory as desired
$rootDir = $rootDir + $SQLSvr;
[IO.Directory]::CreateDirectory($rootDir);

#
#Replication Server
#

$repsvr=New-Object "Microsoft.SqlServer.Replication.ReplicationServer" $SQLSvr

if ($repsvr.ReplicationDatabases.Count -gt 0)
{
# similarly, if we don't have any publications then there's no point in carrying on
[int] $Count_Tran_Pub = 0
[int] $Count_Merge_Pub = 0

foreach($replicateddatabase in $repsvr.ReplicationDatabases)
{
$Count_Tran_Pub = $Count_Tran_Pub + $replicateddatabase.TransPublications.Count
$Count_Merge_Pub = $Count_Merge_Pub + $replicateddatabase.MergePublications.Count
}

if (($Count_Tran_Pub + $Count_Merge_Pub) -gt 0)
{
$rootReplicationDistributor = $rootDir + "\Deploy_Replication_Distributor.sql"

$scriptargs = [Microsoft.SqlServer.Replication.scriptoptions]::Creation `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeAll ;

$line = "-- Parameters: " + $nl + ":SETVAR PublisherName 'SQLBMachineName'" + $nl + ":SETVAR SubscriberName 'SQLAMachineName'" + $nl + ":SETVAR JobLogin 'AccountName'" + $nl + ":SETVAR Password ''" + $nl + ":SETVAR ReplcationShare '\\SQLBMachineName\RepleData'"
$line | Out-File $rootReplicationDistributor -Force 

foreach($replicateddatabase in $repsvr.ReplicationDatabases)
{
if ($replicateddatabase.TransPublications.Count -gt 0)
{
foreach($tranpub in $replicateddatabase.TransPublications)
{

"-- Writing to file script for transactional publication: " + $tranpub.Name | Out-File $rootReplicationDistributor -Append
$nl | Out-File $rootReplicationDistributor -Append

[string] $myscript=$tranpub.script($scriptargs)
$myscript | Out-File $rootReplicationDistributor -Append

}
}
}

if ($replicateddatabase.MergePublications.Count -gt 0)
{
foreach($tranpub in $replicateddatabase.TransPublications)
{

"-- Writing to file script for merge publication: " + $tranpub.Name | Out-File $rootReplicationDistributor -Append
$nl | Out-File $rootReplicationDistributor -Append

[string] $myscript=$tranpub.script($scriptargs)
$myscript | Out-File $rootReplicationDistributor -Append

}
}

} # If for transaction and Merge publication
}

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQLSvr

#
# LinkServers
#

$linkedServers = $srv.LinkedServers;

if ($linkedServers.Count -gt 0 )
{
$rootLinkedServerDir = $rootDir + '\Deploy_LinkedServers.sql'
Out-File $rootLinkedServerDir -Force
"-- LinkServer" + $nl | Out-File $rootLinkedServerDir -Append
foreach($linkSrv in $linkedServers)
{
$linkSrvSql = "EXEC master.dbo.sp_addlinkedserver @server = [" + $linkSrv.DataSource + "] , @srvproduct=[" + $linkSrv.ProductName + "], @provider=N" + $linkSrv.ProviderName + ", @datasrc=[" + $linkSrv.DataSource + "]" + $nl ;
Write-Host $linkSrvSql
$linkSrvSql | Out-File $rootLinkedServerDir -Append

"-- Logins" + $nl | Out-File $rootLinkedServerDir -Append 

if ($linkSrv.LinkedServerLogins.Count -gt 1 )
{
$linkSrvLoginSql = "EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=[" + $linkSrv.DataSource + "], @rmtuser=" + $linkSrv.LinkedServerLogins[1] + "" + $nl ;
Write-Host $linkSrvLoginSql
$linkSrvLoginSql | Out-File $rootLinkedServerDir -Append
}
else
{
$linkSrvLoginSql = "EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=" + $linkSrv.DataSource + $nl ;
Write-Host $linkSrvLoginSql
$linkSrvLoginSql | Out-File $rootLinkedServerDir -Append
}
}

"GO;" + $nl | Out-File $rootLinkedServerDir -Append
}

#
# Create Login
#

$rootLoginDir = $rootDir + '\Deploy_Logins.sql'

# Create Role

$sysdefinedroles = @("sysadmin","serveradmin","securityadmin", "processadmin","setupadmin","bulkadmin","diskadmin","dbcreator")

'-- Create Role' | Out-File $rootLoginDir -Force 

foreach ( $role in $srv.Roles)
{
if ( $sysdefinedroles + "public" -notcontains $role )
{
$line = $nl + "IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE NAME='" + $role + "' AND TYPE = 'R')" + $nl + "BEGIN" + $nl + "CREATE ROLE " + $role + $nl + "END" + $nl + "GO;"
Write-Host $line
$line | Out-File $rootLoginDir -Append
}
}

'-- Create Loging' | Out-File $rootLoginDir -Append 

$SQLLogins = $srv.Logins
$SysAdmins = foreach($SQLUser in $SQLLogins)
{

$uName = $SQLUser | Select-Object Name
if ($uName -imatch "redmond" -or $uName -imatch "NTDEV" -or $uName -imatch "phx")
{
$line = "CREATE LOGIN ["
$line = $line + $uName.Name
$line = $line + "] FROM WINDOWS" + $nl ; # WITH DEFAULT_DATABASE = [" + $SQLUser.DefaultDatabase + "]" + $nl ;
Write-Host $line
$line;
# Get roles and add this login to the role.
foreach($role in $SQLUser.ListMembers())
{

if ( $sysdefinedroles -notcontains $role )
{
$roleStatement = "ALTER ROLE [" + $role + "] ADD MEMBER [" + $uName.Name + "]" + $nl ;
}
else
{
$roleStatement = "ALTER SERVER ROLE [" + $role + "] ADD MEMBER [" + $uName.Name + "]" + $nl ;
}
Write-Host $roleStatement ;
$roleStatement;
}
"GO;" + $nl;
}

};

"-- Logins" + $nl + $SysAdmins | Out-File $rootLoginDir -Append 

#
# Create Job
#

$jobs = $srv.JobServer.Jobs
$jobFile = $rootDir + "\Deploy_jobs.sql"
Out-File $jobFile -Force 

ForEach ( $job in $jobs )
{
[string] $category = $job.Category
if ( $category -notmatch "REPL" )
{
"-- Auto Deployment script " + $job.Name + $nl | Out-File $jobFile -Append
$job.Script() + "GO;" + $nl | Out-File $jobFile -Append
}
}
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s