It is all to easy to build a database on a server and then expect it to be fully functional. Not so fast: There are a number of possibilities in terms of server-based functionality that can defeat you. Databases are likely to have scheduled jobs and alerts, and may have server triggers or even message queues. They are going to have different credentials and logins, proxy accounts, shared schedules and so on. Database applications will sometimes consist of several database components or may require linked servers to be set up. For this reason, it is important to be certain what this server-based functionality is, where it is, and to ensure that it is scripted out into source control so it can be used when provisioning a server for the application.
I’ve written in the past about how to script out server code. However, there is a different problem which is to work out the difference in configuration between two servers. Once you know the differences between the server configuration of a SQL Server instance that holds a working database application and the instance on which you wish to build a version of the application, then you can produce the relevant scripts to provide the database with the required working environment. This is a fairly common DevOps requirement, but one that seems to have few tools to help with the task. The possible reasons for this become apparent as soon as you look at the settings, properties and attributes of the server. Not only are there a great number of them, but few are likely to be relevant. When you are just starting out with this problem, It is much better to have an overview of the differences rather than become overwhelmed with a tsunami of possibly irrelevant data.
To get an overview, I prefer to examine what SMO regards as the server collections. These include such things as databases, endpoints, agent jobs, alerts and linked servers. The simplest comparisons are on the actual names. This tells you whether the two servers have, for example, a linked server of the same name. It is a start, but two servers can have, say, the same agent job with the same name, but that do different things, or are at different versions. You will still need to script them out and check that the scripts are the same, though you should then beware of false negatives due to headers with dates in them.-scripts will show up as different when the difference is actually just the date you did the scripting!
There is an important distinction to be made. The server can consist of a range of objects, such as a database. If we examine the name, we can tell fairly well what databases should be on the server we are provisioning. However, if you compare two databases, you can say whether the names are the same or different, but we are not saying whether the databases with the same name are identical or different. It is the same with settings: we can say that servers have the same settings, but they may have wildly different values. Now the fact that they have different values may, or may not, be important to the provisioning process. It would be foolhardy to say what is important because that will depend on your circumstances. After all, the fact that the server has a different name is very unlikely to be interesting.
We therefore will concentrate, in this script, on comparing the names of the objects in the various collections. This will at least tell you if a component is missing and is a lot quicker than tooling about with SSMS’s object browser!
For this work, there is a useful built-in Cmdlet called Compare-Object. Once you’ve understood the way it works it is very handy for doing comparisons. Its only problem is that it is not the most intuitive visual way of reporting differences, so we use the Compare-Object’s ‘SideIndicator’ for building up the results and then convert the results in one go, into something that is easier to understand. Note that I’m not listing objercts that are equal, just those that exist only on one server or the other. That is a knob you can twiddle but I’d only do that where there are likely to be just a limited number of objects.
Obviously, this would be wrapped into a function in general use. I’ve unwrapped it here to make it easier to investigate.
$Data = @{
"source" = @{
#this is the source server server you are comparing
'Server' = 'MySourceServer'; #The SQL Server instance
'instance' = '\'
'username' = 'ArthurGig'; #leave blank if windows authentication
}
"target" = @{
#this is the server you are comparing it with
'Server' = 'MyTargetServer'; #The SQL Server instance
'instance' = '\'
'username' = 'PhilipJFactor'; #leave blank if windows authentication
}
}
$BadChars = '[\\\/\:\.]' #characters that we don't want in filenames
set-psdebug -strict # to catch subtle errors
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load sqlserver module
$popVerbosity = $VerbosePreference #remember current verbosity setting
$VerbosePreference = "Silentlycontinue"
# the import process is very noisy if you are in verbose mode
Import-Module sqlserver -DisableNameChecking #load the SQLPS functionality
if ([System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.XEvent") -eq $null)
{
throw "Could not load library for Extended Events."
}
$VerbosePreference = $popVerbosity
<# ---- now pick up the credentials if using SQL Server Authentication #>
@($Data.source,$Data.target)|foreach{
# get credentials if necessary
if ($_.username -ne '') #then it is using SQL Server Credentials
{ #have we got them stored locally
$SqlEncryptedPasswordFile = `
"$env:USERPROFILE\$($_.username)-$($_.Server+$_.Instance -replace $BadChars, '').txt"
# test to see if we know about the password in a secure string stored in the user area
if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)
{
#has already got this set for this login so fetch it
$Sqlencrypted = Get-Content $SqlEncryptedPasswordFile | ConvertTo-SecureString
$SqlCredentials = `
New-Object System.Management.Automation.PsCredential($SqlUserName, $Sqlencrypted)
}
else #then we have to ask the user for it
{
#hasn't got this set for this login
$SqlCredentials = get-credential -Credential $SqlUserName
$SqlCredentials.Password | ConvertFrom-SecureString |
Set-Content $SqlEncryptedPasswordFile
}
$_.Credentials=$SqlCredentials #save them with the server data
}
}
$ms = 'Microsoft.SqlServer'
$My = "$ms.Management.Smo" #
<# now we use the information we have and the credentials to connect
to the servers #>
@($Data.source,$Data.target)|foreach{
if ($_.username -eq '') #dead simple if using windows security
{ $s = new-object ("$My.Server") $_.Server+$_.Instance }
else # if using sql server security we do it via a connection object
{
$ServerConnection = new-object "$ms.Management.Common.ServerConnection" (
"$($_.Server)$($_.Instance)" , $_.username, $_.Credentials.Password)
$s = new-object ("$My.Server") $ServerConnection
}
$_.ServerObject=$s
}
$sourceName=$Data.source.ServerObject.Name #for the result headings
$TargetName=$Data.Target.ServerObject.Name #for the result headings
<# now we start by collecting all the possible properties, excluding any that
we know give trouble. and we go through each collection, comparing the
names between the two servers #>
$ComparisonList=$Data.source.ServerObject|gm -MemberType 'property'|
where {$_.definition -like '*collection*'}| #all the collection objects
select name | #filter out the ones that cause problems
where {$_.Name -notin @('SystemMessages','OleDbProviderSettings')}|
foreach { #for each collection name ...
$currentType=$_.Name #we now get a list of objects for each
$sourceList=$Data.source.ServerObject.$currentType|select name
$TargetList=$Data.target.ServerObject.$currentType|select name
#we check that they both have objects in the list
if ($SourceList -ne $null -and $TargetList -ne $null)
{Compare-Object $sourceList $TargetList -Property name|
select name,SideIndicator, @{Name="Type"; Expression = {$CurrentType}}}
elseif ($TargetList -ne $null) # well it is easy, only in the source
{ $TargetList|
select Name,
@{Name="SideIndicator"; Expression = {'=>'}},
@{Name="Type"; Expression = {$CurrentType}}}
elseif ($SourceList -ne $null) # we know they are only in the target
{ $SourceList|
select Name,
@{Name="SideIndicator"; Expression = {'<='}},
@{Name="Type"; Expression = {$CurrentType}}}
}
#Now we get all the collections in the jobserver
$ComparisonList+=$Data.source.ServerObject.Jobserver|gm -MemberType 'property'|
where {$_.definition -like '*collection*'} | #get all the jobserver collection objects
# all the Agent objects we want to script out
Foreach {
$currentType=$_.Name #get a list of all the objects
$sourceList=$Data.source.ServerObject.JobServer.$currentType|select name
$TargetList=$Data.target.ServerObject.JobServer.$currentType|select name
if ($SourceList -ne $null -and $TargetList -ne $null)
{Compare-Object $sourceList $TargetList -Property name|
select name,SideIndicator, @{Name="Type"; Expression = {$CurrentType}}}
elseif ($TargetList -ne $null)
{ $TargetList|
select Name,
@{Name="SideIndicator"; Expression = {'=>'}},
@{Name="Type"; Expression = {$CurrentType}}}
elseif ($SourceList -ne $null)
{ $SourceList|
select Name,
@{Name="SideIndicator"; Expression = {'<='}},
@{Name="Type"; Expression = {$CurrentType}}}
}
# finally, we pick up the XEvents which are stored separately
$Data.source.SqlConn = $Data.source.ServerObject.ConnectionContext.SqlConnectionObject
$Data.Target.SqlConn = $Data.Target.ServerObject.ConnectionContext.SqlConnectionObject
$Data.Source.XEstore = New-Object Microsoft.SqlServer.Management.XEvent.XEStore $Data.Source.SqlConn
$Data.Target.XEstore = New-Object Microsoft.SqlServer.Management.XEvent.XEStore $Data.Target.SqlConn
$sourceList=$Data.source.XEStore.Sessions | select name
$TargetList=$Data.Target.XEStore.Sessions | select name
if ($SourceList -ne $null -and $TargetList -ne $null)
{$ComparisonList+= Compare-Object $sourceList $TargetList -Property name|
select name,SideIndicator, @{Name="Type"; Expression = {'Xevent Sessions'}}}
elseif ($TargetList -ne $null)
{ $ComparisonList+= $TargetList|
select Name,
@{Name="SideIndicator"; Expression = {'=>'}},
@{Name="Type"; Expression = {'Xevent Sessions'}}}
elseif ($SourceList -ne $null)
{ $ComparisonList+= $SourceList|
select Name,
@{Name="SideIndicator"; Expression = {'<='}},
@{Name="Type"; Expression = {'Xevent Sessions'}}}
<# Now we have the entire list we then list them out.#>
$ComparisonList|
select @{Name=$sourceName; Expression = {if ($_.sideIndicator -eq '<=') {$_.Name} else {''}}},
@{Name=$TargetName; Expression = {if ($_.sideIndicator -eq '=>') {$_.Name} else {''}}},
@{Name="Type"; Expression = {$_.Type}}
Here is a result from running it on a couple of development servers. The first two columns have the names of the two servers at the top, and the third column has the type of object we’re investigating
DeepThink\ BigThought\ Type
---------- ----------- ----
Nell Credentials
Dan McGrew Credentials
Abednego Databases
Antipas Databases
Archaelus Databases
Daniel Databases
Meshach Databases
RedGateMonitor Databases
ServerEvents Databases
WebsiteUsage Databases
AdventureWorks2012 Databases
contacts Databases
Customers Databases
MarineMammals Databases
NorthWind Databases
WSLSOURCE LinkedServers
ReportingServer LinkedServers
Wheezy LinkedServers
BIGTHOUGHT\Administrator Logins
BIGTHOUGHT\Nell Logins
BIGTHOUGHT\Posh Logins
PercyTheGreenEngine Logins
MSSecurityMtr Logins
DEEPTHINK\Administrator Logins
DEEPTHINK\Nell Logins
HostDistribution Properties
HostRelease Properties
HostServicePackLevel Properties
HostSku Properties
RG_SQLLighthouse_DDLTrigger Triggers
208 Error Alerts
InvalidObjectError Alerts
Business JobCategories
CheckClones Jobs
CheckConfiguration Jobs
InvalidObjectDetected Jobs
RunPowerShellScript Jobs
Nell ProxyAccounts
PoshProxy ProxyAccounts
RunItEveryMinute SharedSchedules
AllErrors Xevent Sessions
allLogins Xevent Sessions
AllSQLStatementsExecuted Xevent Sessions
AllWarnings Xevent Sessions
BatchCompleted Xevent Sessions
CheckingSPsAndSQLStatements Xevent Sessions
MonitorErrors Xevent Sessions
PermissionsErrors Xevent Sessions
QueryTimeouts Xevent Sessions
QuickSessionStandard Xevent Sessions
QuickSessionTSQL Xevent Sessions
sqlmonitor_session Xevent Sessions
UncompletedQueries Xevent Sessions
WhoChangedWhat Xevent Sessions
MonitorSuspiciousErrors Xevent Sessions
Recompile_Histogram Xevent Sessions
Recompiles Xevent Sessions
The post Comparing SQL Server Instances: Objects by Name appeared first on Simple Talk.
from Simple Talk http://bit.ly/2UTFQNB
via
