主页 / dba / 问题 / 287410
Accepted
Marcello Miorelli
Asked: 2021-03-21 10:26:37 +0800 CST

如何在 sql server 中编写服务器级对象的脚本？

  • 772

我经常在虚拟机或其他地方进行 sql server 安装（主要是 sql server 2016），并且我有很多东西，比如权限、链接服务器和端点，我几乎都编写了脚本，因此在新安装中我可以创建所有这些对象。

在此处输入图像描述

然而，在我正在处理的全新安装中，它应该是特定服务器的精确副本，我们称之为serverX.

serverX在我创建 sql server 服务器级对象脚本时没有指定几个端点， 因此它导致了我的麻烦 - 端口 4022 没有列出，没有监听，并且 powershell 连接测试不起作用

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

虽然所有的防火墙都已经设置好了。

为避免将来出现这种情况，有没有办法编写所有端点的脚本？或者更好的是根据上图编写所有服务器级对象的脚本？

显然，要编写此特定端点的脚本，您只需右键单击并编写脚本：

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

在我这样做之后netstat命令开始列出端口4022-服务代理默认值。

但只是为了automate将来的安装，有没有办法通过代码或powershell，编写所有或一些服务器级对象？

sql-server installation

2 个回答

  1. Best Answer

    编写服务器级对象脚本的一种方法是使用 SMO 和 PowerShell。下面是一个 PS 示例，它使用 SSMS 中包含的 SMO 程序集来编写服务器端点和触发器的脚本。SMO 在NuGet上也可用，以避免对 SSMS 安装的依赖。

    # 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
}
    • 3

  2. 我不知道是否所有服务器对象都可以从服务器内编写脚本，仅使用 T-SQL，但服务器触发器和服务代理端点肯定是：

    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);

    触发器与其定义一起保存，端点需要使用sys.endpoints表中的信息显式转换为 CREATE 脚本。

    如果您需要更多服务器对象，则必须查找它们在哪些 DMO 或 DMV 中发布给用户。

    我正在使用OPTION (RECOMPILE)查询提示来确保这些查询的计划不会保留在计划缓存中。请注意，您不想在编写的每个查询上都这样做，因为这可能会导致不必要的高 CPU 使用率，因为每次运行时都会编译每个查询。

    • 1

相关问题