1

I regularly do sql server installations (mainly sql server 2016), in virtual machines or otherwise, and I have a bunch of things like permissions, and linked servers and endpoints that I have mostly all scripted so that in the new installs I can create all these objects.

enter image description here

On a brand new install that I am working on however, it was supposed to be an exact copy of a specific server let's call it serverX.

serverX had a couple of endpoints not specified on my creation of sql server server level objects script therefore it caused me troble - port 4022 was not listed, and not listening and the powershell connectivity test was not working

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection my_sql_server -port 4022'
 EXEC xp_cmdshell @sql

although all the firewall was already set up.

to avoid this in the future, is there a way to script all the endpoints? or better would be script all server level objects as per the picture above?

obviously to script this specific endpoint, you just need to right-click and script it:

USE [master]
GO

/****** Object:  Endpoint [ServiceBrokerEndpoint]    
   Script Date: 20/03/2021 16:04:05 
 ******/

CREATE ENDPOINT [ServiceBrokerEndpoint] 
    STATE=STARTED
    AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
    FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = DISABLED)
GO

and after I did that the netstat command started to list port 4022 - the service broker default.

but just to automate future installs, is there a way to via code or powershell, script all or some server level objects?

2

One way to script server-level objects is with SMO and PowerShell. Below is a PS example that uses the SMO assembly included with SSMS to script server endpoints and triggers. SMO is also available on NuGet to avoid the dependency on an SSMS install.

# generate scripts for server-scoped object collection
Function Create-ServerObjectScripts($serverCollection, $rootFolder, $typeName) {

    # exclude system objects
    $serverCollection = $serverCollection | Where-Object {
        $_.IsSystemObject -eq $false -or $_.IsSystemObject -eq $null
    }

    if($serverCollection.Count -eq 0) {
        return
    }

    Write-Host "Scripting $typeName for server $($server.Name)..."

    $scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
    $scriptingOptions.IncludeIfNotExists = $true
    # specify desired SQL version below
    $scriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version130
    $scriptingOptions.AppendToFile = $true
    $scriptingOptions.ToFileOnly = $true

    foreach ($serverObject in $serverCollection) {
        $scriptingOptions.FileName = "$rootFolder/$typeName.sql"
        [void](New-Item -Path "$($scriptingOptions.FileName)" -ItemType "file" -Force)
        $serverObject.Script($scriptingOptions)
    }

}

# ############
# ### MAIN ###
# ############

try {

    $serverName = "YourServer"
    $scriptRootFolder = "C:\Scripts\YourServer"

    $smoAssemblyDirectory = "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE"
    Add-Type -Path "$smoAssemblyDirectory/Microsoft.SqlServer.SMO.dll"

    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)
    if ($server.ServerType -eq $null) {
        throw "Unable to connect to server '$ServerName' "
    }

    Create-ServerObjectScripts -serverCollection $server.Endpoints -rootFolder "$scriptRootFolder" -typeName "Endpoints"
    Create-ServerObjectScripts -serverCollection $server.Triggers -rootFolder "$scriptRootFolder" -typeName "Triggers"

    Write-Host "Scripting completed"

}
catch {
    throw
}
2
  • 1
    @MarcelloMiorelli What's cool about this solution is SSMS is essentially using Powershell under the hood whenever you're using the Object Explorer, to query all the objects of the server. Depending on which server level objects you're looking for, they also live in various tables of the master and msdb databases.
    – J.D.
    Mar 21 at 4:15
  • Thanks again @DanGuzman, I had spent quite some time on this, this solution is great! Mar 21 at 22:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.