时间:2021-07-01 10:21:17 帮助过:11人阅读
Register DAC是将DAC包含的object definition 和 related instance-level elements 注册到msdb系统数据库中,从视图 msdb.dbo.sysdac_instances 查看注册的DAC Instance,从表 msdb.[dbo].[sysdac_history_internal] 查看注册的历史记录,后缀是internal,不要修改这些表的记录。
Registration process builds a data-tier application (DAC) definition that describes the objects in an existing database, and register the DAC definition in the msdb system database.
Upgrade DAC是指对现存DB的object definition 和 related instance-level elements 进行upgrade,进行db schema 版本升级。
A DAC upgrade is an in-place process that alters the schema of the existing database to match the schema defined in a new version of the DAC. The new version of the DAC is supplied in a DAC package file.
二,Usage Example
1,Create Data
Use DAC_Study go Create Table dbo.dt_1 ( id int, name varchar(10), CreateDate datetime) go insert into dbo.dt_1 DEFAULT values; go 11 create view vw_dt as select id,name,CreateDate from dbo.dt_1 go create procedure dbo.usp_get_ID_Name as begin select id,name from dbo.dt_1 end go create function dbo.udf_GetDate( @id int) returns DateTime begin declare @dt datetime select @dt=CreateDate from dbo.dt_1 return @dt end go
2, Extract a DAC
选中一个DB,点击Tasks->Extract Data-tier Application...,打开Extract DAC Wizard
从Left Pane 可以看出,Extract DAC主要分为Set Properties,Validation 和 build Package 三步。
Step1, Set DAC Properties
Application name 属性必须填写正确,用于标识DAC。
These properties are used to identify the DAC and help distinguish it from others.
Application Name- This name identifies the DAC. It can be different than the name of the DAC package file and should describe your application.
Overwrite existing file - Select this check box to replace the DAC package file if one already exists with the same name.
Step2,Validation and Summary
对属性设置的Validation 和 Summary
Step3, build Package
创建DAC,在Right Pane中有Result 字段,用以 indicate 创建DAC的结果。
创建DAC成功之后,会在Specified Folder下生成 DAC_Study.dacpac 文件。
3,unpack DAC
unpack DAC 就是拆包,打开dacpac 文件,查看文件的内容。
Use the Unpack Data-tier Application dialog box to unzip the scripts and files from a data-tier application (DAC) package. The scripts and files are placed in a folder where they can be reviewed before the package is used to deploy the DAC into a production system. The contents of one DAC can also be compared with the contents of another package unpacked to another folder.
右键点击dacpac文件,弹出Unpack...,选择拆分文件所存放的folder。
拆解之后,共有四个文件,能够打开逐一查看。
A Transact-SQL script that contains the statements for creating the objects defined in the DAC.
4,Register DAC
在目标实例上创建一个空的DB,DB 不一定和Extract的DB 同名,这里创建的Empty DB是DAC_Test,点击Task->Register as Data-tier Application...,弹出Register DAC Wizard
Step1, Set Properties
Application Name 属性必须填写正确,必须和Extract DAC时填写的Application Name 相同。
Application name. - A string that specifies the name used to identify the DAC defintion, the field is been populated with the database name.
Step2,Vaildation and Summary
Step3, Register DAC
Register DAC,并查看Register的结果。
Step4, 从MSDB system DB中查看Register DAC的结果
从视图 msdb.dbo.sysdac_instances 查看注册的DAC Instance,从表 msdb.[dbo].[sysdac_history_internal] 查看注册的历史记录,后缀是internal,不要修改这些表的记录。
5,Upgrade DAC
A DAC upgrade is an in-place process that alters the schema of the existing database to match the schema defined in a new version of the DAC. The new version of the DAC is supplied in a DAC package file.
选中注册的DB,点击 Tasks->Upgrade Data-tier Application...,打开 Upgrader DAC Wizard
Step1,Select package
Step2,Detect Change
Use this page reports the results of the wizards check for changes made to the database that make it‘s schema different than the schema definition stored in the DAC metadata in msdb. For example, if CREATE, ALTER, or DROP statements have been used to add, change, or remove objects from the database after the DAC was originally deployed. The page first displays a progress bar, and then reports the results of the analysis.
Detecting change, this may take a few minutes - Displays a progress bar as the wizard checks for differences between the current schema of the database and the objects in the DAC definition.
Step3,Option
Step4,Review Upgrade Plan
Step5,Summary
Step6,Upgrade DAC
查看Action,Upgrade DAC 就是一个Deployment 的过程,最终Register Metadata。
三,Check Generation
打开 DAC_Test DB,查看产生的Object,可以看到,通过Extract,Register 和 Upgrade,实现DB Schema的Migration。
参考文档:
Data-tier Applications
DAC Usage2:通过Extract,Register 和 Upgrade DAC,实现DB Schema的Migration
标签: