Database Structure migration using DAC (Data-tierApplication)

Source: Internet
Author: User
There are many methods to extract the table structure, objects, and permissions from an existing database and deploy them into an "Empty Database" without data. For example, GenerateScripts and Custom Script extraction and creation. In practice, I prefer DAC. In particular, it can be used in combination with PowerShell. What is DAC? What can it do?

There are many methods to extract the table structure, objects, and permissions from an existing database and deploy them into an "Empty Database" without data. For example, the built-in Generate Scripts function and Custom Script extraction and creation Scripts. In practice, I prefer DAC. In particular, it can be used in combination with PowerShell. What is DAC? What can it do?

There are many methods to extract the table structure, objects, and permissions from an existing database and deploy them into an "Empty Database" without data. For example, the built-in Generate Scripts function and Custom Script extraction and creation Scripts.

In practice, I prefer DAC. In particular, it can be used in combination with PowerShell.

What is DAC? What can it do?

Data-layer applications (DAC) simplify the development, deployment, and management of data-layer elements that support client-server or multi-layer applications. Each DAC runs as a single management unit throughout the development, testing, and production lifecycle of associated applications. DAC defines all database objects (such as tables and views) required by the application and instance objects (such as login names) associated with the database ). DAC also includes policies used to define the deployment prerequisites for DAC.

It provides the following functions: Data-layer applications

The following describes how to use DAC to migrate data structures:

1. Create a test database and log on. Then extract the database as a DAC package. This process has a wizard, which is very simple. It is basically the same as Next.

use mastergocreate database DAC_Testgocreate login DAC_User with password='P@ssword123'gouse DAC_Testgoselect * into tb1 from sys.objectsselect * into tb2 from sys.objectsgocreate user DAC_User for login DAC_Userexec sp_addrolemember 'db_owner','DAC_User'go

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215212H4-0.png "width =" 538 "height =" 305 "/>

2. Pay attention to the Application name, which will be used later.

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215214095-1.png "width =" 494 "height =" 264 "/>

3. Extracting DAC is not supported by all objects. The supported types are described in the symbol. I have encountered a database where Synonyms cannot be extracted. You can only delete it before extracting it.

Next, get a generated DAC package.

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215211027-2.png "width =" 540 "height =" 338 "/>

4. Create an empty database on the target instance. The same name is not required. Register the database as a DAC.

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/121521M54-3.png "width =" 571 "height =" 295 "/>

5. The registered Application name must be consistent with that in 2.

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215212434-4.png "width =" 467 "height =" 253 "/>

6. After successful registration, the DAC is displayed in Management-> Data-tier Application.

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215216133-5.png "width =" 455 "height =" 244 "/>

7. Copy the generated DAC package to a location that can be accessed by the target instance. Use Upgrade Data-tier Application to import the package. All the way to Next.

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/12152162V-6.png "width =" 458 "height =" 224 "/>

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215215354-7.png "width =" 499 "height =" 294 "/>

8. After completion, various objects in the source database will be available. Note that the Login imported to the target instance is disabled and the Role corresponding to the User in the target database is not the original db_owner, but public.

You need to manually set it.

650) this. width = 650; "title =" image "style =" border-right-width: 0px; border-bottom-width: 0px; border-top-width: 0px; "border =" 0 "alt =" image "src =" http://www.68idc.cn/help/uploads/allimg/151111/121521L61-8.png "width =" 596 "height =" 284 "/>

Summary

1. DAC is a powerful tool with many features.

2. If SQL Server can provide Backup Database .... WITH NO_DATA, there won't be so many things.

-------------------------------------

Author: Joe. TJ

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.