DAC Usage2: Realizing the migration of the DB schema via the Extract,register and Upgrade DACs

One, introduce

The Extract DAC creates a DAC from an existing db, extracting the definition of DB object and its associated instance-level elements, such as login, and the relationship between login and user.

The extraction process creates a DAC package file contains definitions of the database objects and their related inst Ance-level elements. For example, a DAC package file contains the database tables, stored procedures, views, and users, along with the logins T Hat map to the database users.

The register DAC registers the object definition and related instance-level elements contained in the DAC into the msdb system database, from the view msdb.dbo.sysdac_ Instances view the registered DAC Instance, from the table msdb.[ DBO]. [Sysdac_history_internal] View the registered history, suffix internal, and do not modify the records of these tables.

Registration process builds a data-tier application (DAC) definition that describes the objects on an existing database, a nd register the DAC definition in the msdb system database.

The Upgrade DAC refers to an existing DB object definition and related instance-level elements Upgrade for DB schema version upgrades.

A DAC upgrade is an in-place process this 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.

II, Usage Example

1,create Data

 UseDac_studyGoCreate Tabledbo.dt_1 (IDint, namevarchar(Ten), CreateDatedatetime)GoInsert  intodbo.dt_1DEFAULT Values;Go  OneCreate ViewVw_dt asSelectid,name,createdate fromdbo.dt_1GoCreate procedureDbo.usp_get_id_name asbegin    SelectId,name fromdbo.dt_1EndGoCreate functionDbo.udf_getdate (@id int)returns DateTimebegin        Declare @dt datetime    Select @dt=CreateDate fromdbo.dt_1return @dtEndGo

2, Extract a DAC

Select a DB, click Tasks->extract data-tier Application ..., open the Extract DAC Wizard

As can be seen from the left Pane, the Extract DAC is mainly divided into set properties,validation and build package three steps.

STEP1, Set DAC Properties

The application Name property must be filled in correctly to identify the DAC.

These properties is used to identify the DAC and help distinguish it from others.

Application name- This name identifies the DAC. It can 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 and Summary for property settings

STEP3, Build Package

Create a DAC with a result field in right pane to indicate the results of creating the DAC.

After the DAC is created successfully, the Dac_study.dacpac file is generated under the specified folder.

3,unpack DAC

The unpack DAC is unpacking, opening the Dacpac file, and viewing the contents of the file.

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 was used to deploy the DAC into A production system. The contents of one DAC can also is compared with the contents of another package unpacked to another folder.

Right click on the Dacpac file, pop up unpack ..., select the folder where the split file is stored.

After the disassembly, there are four files, which can be opened and viewed individually.

A Transact-SQL script that contains the statements for creating the objects defined in the DAC.

4,register DAC

Creating an empty db,db on the target instance does not necessarily have the same name as the extract db, the empty db created here is Dac_test, click Task->register as Data-tier application ..., Eject Register DAC Wizard

STEP1, Set Properties

The application Name property must be filled in correctly and must be the same as the application name that was filled in when extract the DAC.

application name. -A String that specifies the name used to identify of the DAC defintion, the field is been populated with the database name.

Step2,vaildation and Summary

STEP3, Register DAC

Register DAC and view the results of the register.

STEP4, viewing the results of the register DAC from msdb system db

View the registered DAC Instance from view msdb.dbo.sysdac_instances, from the table msdb.[ DBO]. [Sysdac_history_internal] View the registered history, suffix internal, and do not modify the records of these tables.

5,upgrade DAC

A DAC upgrade is an in-place process this 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.

Select the registered DB, click Tasks->upgrade data-tier Application ..., open the 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 it ' s schema different th The schema definition stored in the DAC metadata in msdb. For example, if CREATE, ALTER, or DROP statements has 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.

Detecting change, this could take a few minutes-displays a progress bar as the wizard checks for differences between the C urrent schema of the database and the objects in the DAC definition.

Step3,option

Step4,review Upgrade Plan

Step5,summary

Step6,upgrade DAC

Viewing the Action,upgrade DAC is a deployment process, the final register Metadata.

Third, Check Generation

Open Dac_test db to see the resulting object, and you can see that the migration of the DB schema is implemented through Extract,register and Upgrade.

Reference Documentation:

Data-tier applications

DAC Usage2: Realizing the migration of the DB schema via the Extract,register and Upgrade DACs