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.<\/p>\n
I\u2019ve 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.<\/p>\n
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!<\/p>\n
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.<\/p>\n
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\u2019s object browser!<\/p>\n
For this work, there is a useful built-in Cmdlet called Compare-Object. Once you\u2019ve 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\u2019s \u2018SideIndicator\u2019 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.<\/p>\n
Obviously, this would be wrapped into a function in general use. I\u2019ve unwrapped it here to make it easier to investigate.<\/p>\n
$Data = @{\r\n\r\n\"source\" = @{\r\n #this is the source server server you are comparing\r\n 'Server' = 'MySourceServer'; #The SQL Server instance\r\n 'instance' = '\\'\r\n 'username' = 'ArthurGig'; #leave blank if windows authentication\r\n }\r\n\"target\" = @{\r\n #this is the server you are comparing it with\r\n 'Server' = 'MyTargetServer'; #The SQL Server instance\r\n 'instance' = '\\'\r\n 'username' = 'PhilipJFactor'; #leave blank if windows authentication\r\n }\r\n}\r\n\r\n$BadChars = '[\\\\\\\/\\:\\.]' #characters that we don't want in filenames\r\n\r\nset-psdebug -strict # to catch subtle errors\r\n$ErrorActionPreference = \"stop\" # you can opt to stagger on, bleeding, if an error occurs\r\n# Load sqlserver module\r\n$popVerbosity = $VerbosePreference #remember current verbosity setting\r\n$VerbosePreference = \"Silentlycontinue\"\r\n# the import process is very noisy if you are in verbose mode\r\nImport-Module sqlserver -DisableNameChecking #load the SQLPS functionality\r\nif ([System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.Management.XEvent\") -eq $null)\r\n{\r\n throw \"Could not load library for Extended Events.\"\r\n}\r\n$VerbosePreference = $popVerbosity\r\n<# ---- now pick up the credentials if using SQL Server Authentication #>\r\n@($Data.source,$Data.target)|foreach{\r\n# get credentials if necessary\r\n if ($_.username -ne '') #then it is using SQL Server Credentials\r\n { #have we got them stored locally\r\n $SqlEncryptedPasswordFile = `\r\n \"$env:USERPROFILE\\$($_.username)-$($_.Server+$_.Instance -replace $BadChars, '').txt\"\r\n # test to see if we know about the password in a secure string stored in the user area\r\n if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)\r\n {\r\n #has already got this set for this login so fetch it\r\n $Sqlencrypted = Get-Content $SqlEncryptedPasswordFile | ConvertTo-SecureString\r\n $SqlCredentials = `\r\n New-Object System.Management.Automation.PsCredential($SqlUserName, $Sqlencrypted)\r\n }\r\n else #then we have to ask the user for it\r\n {\r\n #hasn't got this set for this login\r\n $SqlCredentials = get-credential -Credential $SqlUserName\r\n $SqlCredentials.Password | ConvertFrom-SecureString |\r\n Set-Content $SqlEncryptedPasswordFile\r\n }\r\n $_.Credentials=$SqlCredentials #save them with the server data\r\n }\r\n}\r\n\r\n$ms = 'Microsoft.SqlServer'\r\n$My = \"$ms.Management.Smo\" #\r\n\r\n<# now we use the information we have and the credentials to connect \r\nto the servers #>\r\n@($Data.source,$Data.target)|foreach{\r\n if ($_.username -eq '') #dead simple if using windows security\r\n { $s = new-object (\"$My.Server\") $_.Server+$_.Instance }\r\n else # if using sql server security we do it via a connection object\r\n {\r\n $ServerConnection = new-object \"$ms.Management.Common.ServerConnection\" (\r\n \"$($_.Server)$($_.Instance)\" , $_.username, $_.Credentials.Password)\r\n $s = new-object (\"$My.Server\") $ServerConnection\r\n }\r\n $_.ServerObject=$s\r\n}\r\n\r\n$sourceName=$Data.source.ServerObject.Name #for the result headings\r\n$TargetName=$Data.Target.ServerObject.Name #for the result headings\r\n<# now we start by collecting all the possible properties, excluding any that\r\nwe know give trouble. and we go through each collection, comparing the\r\nnames between the two servers #>\r\n$ComparisonList=$Data.source.ServerObject|gm -MemberType 'property'|\r\n where {$_.definition -like '*collection*'}| #all the collection objects\r\n select name | #filter out the ones that cause problems\r\n where {$_.Name -notin @('SystemMessages','OleDbProviderSettings')}|\r\n foreach { #for each collection name ...\r\n $currentType=$_.Name #we now get a list of objects for each\r\n $sourceList=$Data.source.ServerObject.$currentType|select name\r\n $TargetList=$Data.target.ServerObject.$currentType|select name\r\n #we check that they both have objects in the list\r\n if ($SourceList -ne $null -and $TargetList -ne $null)\r\n {Compare-Object $sourceList $TargetList -Property name|\r\n select name,SideIndicator, @{Name=\"Type\"; Expression = {$CurrentType}}}\r\n elseif ($TargetList -ne $null) # well it is easy, only in the source\r\n { $TargetList|\r\n select Name, \r\n @{Name=\"SideIndicator\"; Expression = {'=>'}},\r\n @{Name=\"Type\"; Expression = {$CurrentType}}}\r\n elseif ($SourceList -ne $null) # we know they are only in the target\r\n { $SourceList|\r\n select Name, \r\n @{Name=\"SideIndicator\"; Expression = {'<='}}, \r\n @{Name=\"Type\"; Expression = {$CurrentType}}}\r\n}\r\n\r\n#Now we get all the collections in the jobserver\r\n$ComparisonList+=$Data.source.ServerObject.Jobserver|gm -MemberType 'property'|\r\n where {$_.definition -like '*collection*'} | #get all the jobserver collection objects\r\n# all the Agent objects we want to script out\r\n Foreach {\r\n $currentType=$_.Name #get a list of all the objects\r\n $sourceList=$Data.source.ServerObject.JobServer.$currentType|select name\r\n $TargetList=$Data.target.ServerObject.JobServer.$currentType|select name\r\n if ($SourceList -ne $null -and $TargetList -ne $null)\r\n {Compare-Object $sourceList $TargetList -Property name|\r\n select name,SideIndicator, @{Name=\"Type\"; Expression = {$CurrentType}}}\r\n elseif ($TargetList -ne $null) \r\n { $TargetList|\r\n select Name, \r\n @{Name=\"SideIndicator\"; Expression = {'=>'}},\r\n @{Name=\"Type\"; Expression = {$CurrentType}}}\r\n elseif ($SourceList -ne $null) \r\n { $SourceList|\r\n select Name, \r\n @{Name=\"SideIndicator\"; Expression = {'<='}}, \r\n @{Name=\"Type\"; Expression = {$CurrentType}}}\r\n}\r\n# finally, we pick up the XEvents which are stored separately\r\n$Data.source.SqlConn = $Data.source.ServerObject.ConnectionContext.SqlConnectionObject\r\n$Data.Target.SqlConn = $Data.Target.ServerObject.ConnectionContext.SqlConnectionObject\r\n\r\n$Data.Source.XEstore = New-Object Microsoft.SqlServer.Management.XEvent.XEStore $Data.Source.SqlConn\r\n$Data.Target.XEstore = New-Object Microsoft.SqlServer.Management.XEvent.XEStore $Data.Target.SqlConn\r\n\r\n$sourceList=$Data.source.XEStore.Sessions | select name\r\n$TargetList=$Data.Target.XEStore.Sessions | select name\r\n\r\nif ($SourceList -ne $null -and $TargetList -ne $null)\r\n {$ComparisonList+= Compare-Object $sourceList $TargetList -Property name|\r\n select name,SideIndicator, @{Name=\"Type\"; Expression = {'Xevent Sessions'}}}\r\n elseif ($TargetList -ne $null) \r\n { $ComparisonList+= $TargetList|\r\n select Name, \r\n @{Name=\"SideIndicator\"; Expression = {'=>'}},\r\n @{Name=\"Type\"; Expression = {'Xevent Sessions'}}}\r\n elseif ($SourceList -ne $null) \r\n { $ComparisonList+= $SourceList|\r\n select Name, \r\n @{Name=\"SideIndicator\"; Expression = {'<='}}, \r\n @{Name=\"Type\"; Expression = {'Xevent Sessions'}}}\r\n\r\n<# Now we have the entire list we then list them out.#>\r\n$ComparisonList|\r\n select @{Name=$sourceName; Expression = {if ($_.sideIndicator -eq '<=') {$_.Name} else {''}}}, \r\n @{Name=$TargetName; Expression = {if ($_.sideIndicator -eq '=>') {$_.Name} else {''}}},\r\n @{Name=\"Type\"; Expression = {$_.Type}}\r\n\r\n<\/pre>\nHere 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\u2019re investigating<\/p>\n
DeepThink\\ BigThought\\ Type \r\n---------- ----------- ---- \r\n Nell Credentials \r\n Dan McGrew Credentials \r\n Abednego Databases \r\n Antipas Databases \r\n Archaelus Databases \r\n Daniel Databases \r\n Meshach Databases \r\n RedGateMonitor Databases \r\n ServerEvents Databases \r\n WebsiteUsage Databases \r\nAdventureWorks2012 Databases \r\ncontacts Databases \r\nCustomers Databases \r\nMarineMammals Databases \r\nNorthWind Databases \r\nWSLSOURCE LinkedServers \r\nReportingServer LinkedServers \r\n Wheezy LinkedServers \r\n BIGTHOUGHT\\Administrator Logins \r\n BIGTHOUGHT\\Nell Logins \r\n BIGTHOUGHT\\Posh Logins \r\n PercyTheGreenEngine Logins \r\nMSSecurityMtr Logins \r\nDEEPTHINK\\Administrator Logins \r\nDEEPTHINK\\Nell Logins \r\n HostDistribution Properties \r\n HostRelease Properties \r\n HostServicePackLevel Properties \r\n HostSku Properties \r\n RG_SQLLighthouse_DDLTrigger Triggers \r\n 208 Error Alerts \r\n InvalidObjectError Alerts \r\nBusiness JobCategories \r\n CheckClones Jobs \r\n CheckConfiguration Jobs \r\n InvalidObjectDetected Jobs \r\n RunPowerShellScript Jobs \r\n Nell ProxyAccounts \r\n PoshProxy ProxyAccounts \r\n RunItEveryMinute SharedSchedules\r\n AllErrors Xevent Sessions\r\n allLogins Xevent Sessions\r\n AllSQLStatementsExecuted Xevent Sessions\r\n AllWarnings Xevent Sessions\r\n BatchCompleted Xevent Sessions\r\n CheckingSPsAndSQLStatements Xevent Sessions\r\n MonitorErrors Xevent Sessions\r\n PermissionsErrors Xevent Sessions\r\n QueryTimeouts Xevent Sessions\r\n QuickSessionStandard Xevent Sessions\r\n QuickSessionTSQL Xevent Sessions\r\n sqlmonitor_session Xevent Sessions\r\n UncompletedQueries Xevent Sessions\r\n WhoChangedWhat Xevent Sessions\r\nMonitorSuspiciousErrors Xevent Sessions\r\nRecompile_Histogram Xevent Sessions\r\nRecompiles Xevent Sessions \r\n<\/pre>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
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……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-84044","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84044","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=84044"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84044\/revisions"}],"predecessor-version":[{"id":84050,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84044\/revisions\/84050"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=84044"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=84044"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=84044"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=84044"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}