How to script server level objects in sql server?

Question

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.

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?

Answer 1

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
}

Answer 2

I don't know if all server object are scriptable from within the server, using only T-SQL, but server trigger and service broker endpoints certainly are:

SELECT
  ST.name,
  SSM.definition
FROM
  sys.server_triggers AS ST WITH(NOLOCK)
  LEFT JOIN sys.server_sql_modules AS SSM WITH(NOLOCK) ON SSM.object_id = ST.object_id
WHERE
  ST.is_ms_shipped = 0
OPTION(RECOMPILE);

SELECT
  *
FROM
  sys.endpoints AS E WITH(NOLOCK)
WHERE
  type = 3 /*SERVICE_BROKER*/
OPTION(RECOMPILE);

Triggers are saved with their definition, the endpoint needs to be converted to CREATE script explicitly using the information from the sys.endpoints table.

If you needed more server objects, you would have to look up in which DMOs or DMVs they are published to the user.

I'm using the OPTION (RECOMPILE) query hint to ensure plans for these queries aren't retained in the plan cache. Note that you don't want to do that on every query you write since that can result in unnecessarily high CPU usage due to every query being compiled every time they run.