sql-server - SQL Azure : How to execute a common DDL script across all schemas?

Question

是否有一种内置方法可以跨所有模式执行通用 DDL 脚本？

我正在开发一个 Multi-Tenancy 应用程序，它为每个租户创建一个数据库架构。每个架构都包含每个租户的相同表定义。例如:

Schema named "tenant1" contains tables: tenant1.Users, tenant1.HistoryRecords, etc.
Schema named "tenant2" contains tables: tenant2.Users, tenant2.HistoryRecords, etc.

当我添加字段时，我希望将其添加到租户 1 架构、租户 2 架构等中。

初步想法:我有一个表，其中包含租户的架构名称和相关信息。我正在考虑向该表添加数据库版本字段以跟踪架构更改。然后，我将创建一个接受 DDL 脚本和架构版本作为参数的存储过程。

CREATE PROCEDURE UpdateSchema(DDLScript, InitialSchemaName, DbVersion)
    @DDLScript nvarchar(5000), 
    @InitialSchemaName nvarchar(10), 
    @DbVersion nvarchar(5)...

该脚本将循环遍历一组架构，为每个架构运行 DDL 脚本，将 InitialSchemaName 替换为当前循环的架构名称，如果成功，则提交所有更改。

这是一个合理的计划，还是我错过了一个更常见的方法？

Answer 1

对于 Multi-Tenancy 环境，您必须非常小心。有很多简单的方法可以把事情搞定。正如您所指出的，您的 DDL 脚本必须根据架构进行编辑，这意味着更改脚本内的对象名称。这很可怕，但我明白这是必要的。它引入了 SQL 注入(inject)的向量。

我希望您有一个或多个“安全”模式可供测试。哎呀，我希望你有一个完整的测试世界来测试。但是 - 抛开所有担忧，这是我过去用来将更改应用于多模式环境的脚本的脚手架:

create type dbo.ObjectNamesType as table ( Name sysname )
go
create procedure RunDDL( @scriptTemplate nvarchar( max ), @objName sysname, @objType nvarchar( 10 ), @schemas dbo.ObjectNamesType readonly )
as
begin
    set nocount on

    declare @script nvarchar( max )
    declare @objectName nvarchar( 256 )

    declare c cursor for
        select N'[' + s.name + N'].[' + o.name + N']' 
        from 
            sys.objects o 
            inner join
            @schemas s
            on
                o.schema_id = schema_id( s.Name )
        where
            o.name = @objName
            and
            o.type = @objType
    open c
        while ( 1=1 )
        begin
            fetch next from c into @objectName
            if ( @@fetch_status != 0 ) break;
            select @script = replace( @scriptTemplate, N'@objectName', @objectName )
            exec sys.sp_executesql @script
        end
    close c
    deallocate c
end
go

...并测试它...

declare @script nvarchar( max )
declare @objName sysname
declare @objType sysname
declare @schemas dbo.ObjectNamesType

insert @schemas values( 'dbo' )
select @objName = 'someTable'
select @objType = 'u'
select @script = 'select * from @objectName' --> not really ddl, eh?
exec dbo.RunDDL @script, @objName, @objType, @schemas

我实际使用的要复杂得多 - 所以我只是留下了有趣的部分。一些注意事项:

输入的设置方式使得脚本可以针对一组模式运行。这使您可以首先在测试架构上运行它，看看是否可以 - 假设您喜欢它，然后您可以针对其余架构全体运行它。

在我的世界中，@templateScript、@objName 和 @objType 驻留在我加入的表中 - 并且不会传递我不建议使用外部世界的输入来运行这样的程序，因为这是一场灾难的邀请......但为了说明/测试，它达到了目的。另外，在我的世界中，输入表有一个版本 ID 和一个序列。对于版本 x 的任何模式，我们按顺序运行所有脚本并假设成功，将该模式的版本升级到 y。每个脚本适用于单个对象。

这里的要点是，您需要根据数据库更新制定一个例行工作流程 - 而此过程是该工作流程的核心。

请注意，它从 sys.objects 中进行选择，而不仅仅是相信输入。这只是防止脚本因名称拼写错误而崩溃的另一个小保护措施。如果我们编辑的对象数量与指定的对象数量不匹配，我们会为自己记录一条警告。

这种类型的过程还应该尝试/捕获脚本的实际执行，并且应该记录它在此过程中尝试的所有内容。它应该在出现错误时回滚。确保您有足够的事务日志空间，因为即使很小的 DDL 也会导致大量更改。

它通过指定的对象工作，将@templateScript编辑为@script变量，然后运行@script变量sys.sp_executesql。这样，它就永远不会更改源变量，因此替换目标保持不变。

不幸的是，您不能使用变量作为 tsql 对象名称，否则可能会减少注入(inject)攻击的范围。因此，建议使用输入表而不是参数。这也意味着 SQL 无法真正执行任何操作来参数化/重用语句/执行计划 - 但话又说回来，这不是运行数十亿次的东西，对吧？