如何在SSDT项目中包括自定义数据迁移和静态/参考数据？ [英] How to include custom data migrations and static/reference data in an SSDT project?

问题描述

我们有一个中等规模的SSDT项目（约100个表），已部署到数十个不同的数据库实例中。在构建过程中，我们会生成一个.dacpac文件，然后在准备升级数据库时，我们会生成一个发布脚本并针对该数据库运行它。一些数据库实例在不同的时间进行升级，因此对于这些升级和版本控制，我们有一个结构化的过程很重要。

We have a moderately-sized SSDT project (~100 tables) that's deployed to dozens of different database instances. As part of our build process we generate a .dacpac file and then when we're ready to upgrade a database we generate a publish script and run it against the database. Some db instances are upgraded at different times so it's important that we have a structured process for these upgrades and versioning.

大多数生成的迁移脚本正在删除和（重新）创建proc，函数，索引并执行任何结构更改，以及部署后脚本中包括的一些数据脚本。我想知道这是与数据有关的两个项目：

Most of the generated migration script is dropping and (re)creating procs, functions, indexes and performing any structural changes, plus some data scripts included in a Post-Deployment script. It's these two data-related items I'd like to know how best to structure within the project:

1. 需要自定义数据迁移版本之间

1. Custom data migrations needed between versions

静态或参考数据

版本之间需要自定义数据迁移

有时我们希望一次性执行数据迁移作为升级的一部分，但我不是确保将其纳入我们的SSDT项目的最佳方法。例如，最近我添加了一个新的位列 dbo.Charge.HasComments 来包含基于另一个表的（冗余）派生数据，并将通过触发器保持同步。令人讨厌但必要的性能改进（仅在经过仔细考虑和测量后才添加）。作为升级的一部分，SSDT生成的发布脚本将包含必要的 ALTER TABLE 和 CREATE TRIGGER 语句，但是我还希望根据另一个表中的数据更新此列：

Sometimes we want to perform a one-off data migration as part of an upgrade and I'm not sure the best way to incorporate this into our SSDT project. For example, recently I added a new bit column dbo.Charge.HasComments to contain (redundant) derived data based on another table and will be kept in sync via triggers. An annoying but necessary performance improvement (only added after careful consideration & measurement). As part of the upgrade the SSDT-generated Publish script will contain the necessary ALTER TABLE and CREATE TRIGGER statements, but I also want to update this column based on data in another table:

update dbo.Charge 
set HasComments = 1 
where exists ( select * 
               from dbo.ChargeComment 
               where ChargeComment.ChargeId = Charge.ChargeId ) 
and HasComments = 0 

在我的SSDT项目中包括此数据迁移脚本的最佳方法是什么？

What's the best way to include this data migration script in my SSDT project?

当前，我将每种类型的迁移都放在一个单独的文件中，该文件包含在Post-Deployment脚本中，所以我的Post-Deployment脚本看起来像这样：

Currently I have each of these types of migrations in a separate file that's included in the Post-Deployment script, so my Post-Deployment script ends up looking like this:

-- data migrations
:r "data migration\Update dbo.Charge.HasComments if never populated.sql"
go
:r "data migration\Update some other new table or column.sql"
go

这是正确的方法吗，还是有某种方法可以更好地与SSDT结合使用以及其版本跟踪，因此在运行SSDT Publish时这些脚本甚至都不会运行针对已经是最新版本的数据库。我可以拥有自己的表格来跟踪已运行的迁移，但是如果有一种标准的方法可以执行此操作，则不愿自己动手。

Is this the right way to do it, or is there some way to tie in with SSDT and its version tracking better, so those scripts aren't even run when the SSDT Publish is being run against a database that's already at a more recent version. I could have my own table for tracking which migrations have been run, but would prefer not to roll-my-own if there's a standard way of doing this stuff.

静态或参考数据

某些数据库表包含我们所谓的内容静态或参考数据，例如可能的时区，设置类型，货币，各种类型表等的列表。目前，我们通过为每个表（在部署后脚本中运行）运行一个单独的脚本来填充这些表。每个静态数据脚本都会将所有正确的静态数据插入表变量中，然后根据需要插入/更新/删除静态数据表。根据表的不同，可能只适合插入或仅适合插入和删除，而不适合更新现有记录。因此，每个脚本如下所示：

Some of the database tables contain what we call static or reference data, e.g. list of possible timezones, setting types, currencies, various 'type' tables etc. Currently we populate these by having a separate script for each table that is run as part of the Post-Deployment script. Each static data script inserts all the 'correct' static data into a table variable and then inserts/updates/deletes the static data table as needed. Depending on the table it might be appropriate only to insert or only insert and delete but not to update existing records. So each script looks something like this:

-- table listing all the correct static data
declare @working_data table (...)

-- add all the static data that should exist into the working table
insert into @working_data (...) select null, null null where 1=0
union all select 'row1 col1 value', 'col2 value', etc...
union all select 'row2 col1 value', 'col2 value', etc...
...

-- insert any missing records in the live table
insert into staticDataTableX (...)
select * from @working_data
where not exists ( select * from staticDataTableX
                   where [... primary key join on @working_data...] )

-- update any columns that should be updated
update staticDataTableX
set ...
from staticDataTableX
inner join @working_data on [... primary key join on @working_data...]

-- delete any records, if appropriate with this sort of static data
delete from staticDataTableX
where not exists ( select * from staticDataTableX
                   where [... primary key join on @working_data...] )

，然后我的部署后脚本中的部分如下：

and then my Post-Deployment script has a section like this:

-- static data. each script adds any missing static/reference data:
:r "static_data\settings.sql"
go
:r "static_data\other_static_data.sql"
go
:r "static_data\more_static_data.sql"
go

有没有更好的选择还是将常规的静态数据脚本构造为SSDT项目一部分的常规方法？

Is there a better or more conventional way to structure such static data scripts as part of an SSDT project?

推荐答案

跟踪字段是否具有已经初始化，请尝试在执行初始化时添加扩展属性（也可以用来确定是否需要初始化）：

To track whether or not the field has already been initialized, try adding an Extended Property when the initialize is performed (it can also be used to determine the need for the initialize):

要添加扩展属性：

EXEC sys.sp_addextendedproperty 
@name = N'EP_Charge_HasComments', 
@value = N'Initialized', 
@level0type = N'SCHEMA', @level0name = dbo, 
@level1type = N'TABLE',  @level1name = Charge,
@level2type = N'COLUMN', @level2name = HasComments;

要检查扩展属性：

SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 
    'SCHEMA', 'dbo', 
    'TABLE', 'Charge', 
    'COLUMN', 'HasComments');

有关参考数据，请尝试使用MERGE。比使用的三组查询要干净得多。

For reference data, try using a MERGE. It's MUCH cleaner than the triple-set of queries you're using.

MERGE INTO staticDataTableX AS Target
USING (
VALUES  
        ('row1_UniqueID', 'row1_col1_value', 'col2_value'),
        ('row2_UniqueID', 'row2_col1_value', 'col2_value'),
        ('row3_UniqueID', 'row3_col1_value', 'col2_value'),
        ('row4_UniqueID', 'row4_col1_value', 'col2_value')


    ) AS Source (TableXID,  col1, col2)
        ON Target.TableXID = Source.TableXID
WHEN MATCHED THEN
   UPDATE SET 
        Target.col1 = Source.col1,  
        Target.col2 = Source.col2  

WHEN NOT MATCHED BY TARGET THEN
   INSERT (TableXID,  col1, col2)
   VALUES (Source.TableXID,  Source.col1, Source.col2)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE; 

这篇关于如何在SSDT项目中包括自定义数据迁移和静态/参考数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！