Migration of databases

The data-tier application (Data-tier appliCation, or DAC) is a management tool for the logical architecture of a database that defines the management of a single SQL Metadata for server database objects (including Table,view, and instance-level objects login, etc.). With the DAC, users can easily package the database into a DAC packages file with the suffix Dacpac, which can be deployed on other servers in a simple way, similar to a full backup of the database, except that the Dacpac file does not contain data. Includes only the metadata for database objects, which users can use to create an empty, identical database. With the DAC, the user can also package the schema and data of the database object into a backup packages file with the suffix Bacpac. With this file, the user is able to create a new database in another instance of SQL Server, and the new database contains all the data and schema information for the original database.

Through the DAC implementation of the database schema migration, the Dacpac file is mainly used to deploy the database schema, create a test environment for the product database, code logic test for the new business requirements, and the Bacpac file is logically equivalent to the full backup of the database, Primarily for the overall migration of the database schema and data, the Bacpac file supports export operations for backing up the database, and the import operation is used to create a new database on the target server, similar to a database restore operation.

One, implementing the schema migration of the database using a DAC

Complete schema migration operations, divided into extraction (Extract), registration (register), deployment (deploy), and upgrade (Upgrade):

• The Decimation (Extract) DAC creates a DAC package file from an existing database, extracting the definition of the database object and the associated instance-level elements, such as login, and the relationship between login and user.
• Registration (register) DACS Register database objects in the target SQL Server instance based on the metadata of the database objects contained in the Dacpackage.
• A deployment (deploy) DAC is a new db createdat the target SQL Server instance level using the database metadata contained in the DAC package.
• The Upgrade (Upgrade) DAC refers to the upgrade of existing database metadata and is a feature of the DAC as a lightweight database-schema Migration tool;

Second, migrating the database schema example

1, extracting DAC, opening Extract DAC Wizard

Select the schema to migrate the database Dac_study, right-click Dac_study, Open the menu, click "Tasks", "Extract data-tier application ..." To open the Extract DAC Wizard

From the Start interface (Introduction), it can be seen that the decimation (Extract) DAC is divided into three main steps: setting the DAC properties, validating and creating the package, requiring the user to configure the properties of the DAC, when extracting the DAC package, A unique application name (application name) must be filledin to identify the DAC.

2, unpacking (unpack) DAC

The unpack DAC is the unpacking that opens the Dacpac file to view the contents of the file. Select a Dacpac file and right-click the popup "Unpack ..." to place the split file in the specified destination folder.

A Dacpac file that is disassembled into four files, three XML files, a TSQL script file that contains the code for creating database objects,

3, registration (register) DAC

Create an empty database on the target SQL Server instance, the new database does not necessarily have the same name as the original database, name the new database dac_test, right-click the database, click "Tasks", "Register as Data-tier" Application ... ", Pop-up Registration (register) DAC wizard

The process of registering a DAC is divided into three main steps: setting properties, validating and registering the DAC, requiring the user to configure the "Set property", the user must fill in the correct application name, the property must be the same as the application name in the Dacpac file.

4, deploying (Deploy) DAC

Deploying a DAC on a SQL Server instance, in effect creating a new database using the Dacpac file, implementing the migration of the database schema, and when registering the DAC differs, the deployment DAC does not need to manually create an empty database.

When you deploy the DAC, right-click Databases, Pop-up the shortcut menu, click "Deploy Data-tier Application ..." To eject the Deploy DAC wizard,

The process of deploying a DAC is divided into four main parts: Select the Dacpac file, configure the deployment, review, and deploy. Depending on the wizard, select the Dacpack file that you want to deploy, configure the new database name, enter a new database name in the Name property, and then, unlike the original database name, click Next to Deploy. After the deployment is complete, an empty database is successfully created in the current instance of SQL Server.

4, Upgrade (Upgrade) DAC

The Upgrade (Upgrade) DAC is an update and upgrade of the schema of an existing database, provided that the database exists and is similar to the Register DAC.

Select the upgraded database, right-click the popup shortcut menu, click "Tasks", "Upgrade data-tier application ..." To open the Upgrade (upgrader) DAC wizard

The process of upgrading the DAC is simple, just select the Dacpac file containing the latest metadata and, as prompted by the wizard, simply click Next to complete the upgrade of the existing database.

Third, create a new database from the Backup package (. Bacpac)

Using the Bacpac file, you can create a new database that contains the schema and data, similar to a database-like restore, to create a new database that contains data and schemas.

Method one, using the SSMs Wizard

Right-click Databases, open "Import data-tier application", open the wizard, follow the UI prompts to configure

Method Two, use SqlPackage.exe to implement auto-import DAC

Command invocation example, for ease of viewing, I format the command, in the DOS command-line tool, do not break line.

"C:\Program Files (x86) \microsoft SQL Server\/ // Tsn:cgrd7z8kac. Database  //tu:mysysadmin@cgrd7z8kac/tp:pa55w0rd

Parameter explanation:

• Sf– Specifying BACPAC files
• tsn– specifies the SQL Server instance name to store the new target database
• tdn– Specify the name of the target database
• tu– Specifies the login used to access the instance of SQL Server
• tp– Specifies the password for the login that is used to access the instance of SQL Server

If you log in using Windows Authentication, you can use the TTSC margin instead of TU and TP.

-a:import-sf:c:\testdac\dac_name.bacpac-tsn:server_name-tdn:db_name-ttsc:true

parameter: TTSC: {True | False} Specifies whether secure Socket Layer (SSL) encryption is used to log on to the target SQL Server instance as Windows authentication.

Reference Documentation:

Data-tier applications

Export and Import BACPAC using command line

SqlPackage.exe

Three tools for migrating databases to SQL Azure

Migration of databases