Register a Database As a DAC
Use either the Register Data-tier Application Wizard or a Windows PowerShell script to build a data-tier application (DAC) definition that describes the objects in an existing database, and register the DAC definition in the msdb system database (master in Windows Azure SQL Database).
Before you begin: Limitations and Restrictions, Permissions
To upgrade a DAC, using: The Register Data-tier Application Wizard, PowerShell
Before You Begin
The registration process creates a DAC definition that defines the objects in the database. The combination of the DAC definition and the database form a DAC instance. If you register a database as a DAC on a managed instance of the Database Engine, the registered DAC will be incorporated into the SQL Server Utility the next time the utility collection set is sent from the instance to the Utility Control Point. The DAC will then be present in the Deployed Data-tier Applications node of the Management Studio Utility Explorer and reported in the Deployed Data-tier Applications details page.
Limitations and Restrictions
DAC registration can only be performed on a database in SQL Database, or SQL Server 2005 Service Pack 4 (SP4) or later. DAC registration cannot be performed if a DAC is already registered for the database. For example, if the database was created by deploying a DAC, you cannot run the Register Data-tier Application Wizard.
You cannot register a DAC if the database has objects that are not supported in a DAC, or contained users. For more information about the types of objects supported in a DAC, see DAC Support For SQL Server Objects and Versions.
[Top]
Permissions
Registering a DAC in an instance of Database Engine requires at least ALTER ANY LOGIN and database scope VIEW DEFINITION permissions, SELECT permissions on sys.sql_expression_dependencies, and membership in the dbcreator fixed server role. Members of the sysadmin fixed server role or the built-in SQL Server system administrator account named sa can also register a DAC. Registering a DAC that does not contain logins in SQL Database requires membership in the dbmanager or serveradmin roles. Registering a DAC that contains logins in SQL Database requires membership in the loginmanager or serveradmin roles.
[Top]
Using the Register Data-tier Application Wizard
To Register a DAC Using a Wizard
In Object Explorer, expand the node for the instance containing the database to be registered as a DAC.
Expand the Databases node.
Right-click the database to be registered, point to Tasks, and then select Register As Data-tier Application…
Complete the wizard dialogs:
Introduction Page
Set Properties Page
Validation and Summary Page
Register DAC Page
[Top]
Introduction Page
This page describes the steps for registering a data-tier application.
Do not show this page again. - Click the check box to stop the page from being displayed in the future.
Next > - Proceeds to the Set Properties page.
Cancel - Terminates the wizard without registering a DAC.
[Register Wizard]
Set Properties Page
Use this page to specify DAC-level properties such as the application name and version.
Application name. - A string that specifies the name used to identify the DAC defintion, the field is been populated with the database name.
Version. - A numeric value that identifies the version of the DAC. The DAC version is used in Visual Studio to identify the version of the DAC that developers are working on. When deploying a DAC, the version is stored in the msdb database and can later be viewed under the Data-tier Applications node in SQL Server Management Studio.
Description. - Optional. Text that explains the purpose of the DAC. When deploying a DAC, the description is stored in the msdb database and can later be viewed under the Data-tier Applications node in Management Studio.
< Previous - Returns you to the Introduction page.
Next > - Verifies that a DAC can be built from the objects in the database, and displays the results in the Validation and Summary page.
Cancel - Terminates the wizard without registering the DAC.
[Register Wizard]
Validation and Summary Page
Use this page to review the actions the wizard will take when registering the DAC. The page transitions through three states as it verifies that a DAC can be built from the objects in the database.
[Register Wizard]
Retrieving Objects
Retrieving database and server objects. - Displays a progress bar as the wizard retrieves all of the required objects from the database and the instance of the Database Engine.
< Previous - Returns you to the Set Properties page to change your entries.
Next > - Registers the DAC and displays the results in the Register DAC page.
Cancel - Terminates the wizard without registering the DAC.
[Register Wizard]
Validating Objects
Checking SchemaName**.ObjectName.** - Displays a progress bar as the wizard verifies the dependencies of the retrieved objects, and verifies that they are all valid objects for a DAC. SchemaName**.**ObjectName identify which object is currently being verified.
< Previous - Returns you to the Set Properties page to change your entries.
Next > - Registers the DAC and displays the results in the Register DAC page.
Cancel - Terminates the wizard without registering the DAC.
[Register Wizard]
Summary
The following setting will be used to register your DAC. - Displays a report of the properties and objects that will be included in the DAC.
Save Report - Select this button to save a copy of the validation report to an HTML file. The default folder is a SQL Server Management Studio\DAC Packages folder in the Documents folder of your Windows account.
< Previous - Returns you to the Set Properties page to change your entries.
Next > - Registers the DAC and displays the results in the Register DAC page.
Cancel - Terminates the wizard without registering the DAC.
[Register Wizard]
Register DAC Page
This page reports the success or failure of the registration.
Registering the DAC - Reports the success or failure of each action taken to register the DAC. Review the information to determine the success or failure of each action. Any action that encountered an error will have a link in the Result column. Select the link to view a report of the error for that action.
Save Report - Select this button to save the registration report to an HTML file. The file reports the status of each action, including all errors generated by any of the actions. The default folder is a SQL Server Management Studio\DAC Packages folder in the Documents folder of your Windows account. The file name is in the format <DACPackageName>_RegisterDACReport_yyyymmdd.html, where <DACPackageName> is the name of the package being deployed, yyyy = the current year, mm = the current month, and dd = the current day.
Finish - Terminates the wizard.
[Register Wizard]
Register a DAC Using PowerShell
To register a database as a DAC using the Register() method in a PowerShell script
Create a SMO Server object and set it to the instance that contains the database to be registered as a DAC.
Add a variable that specifies the name of the database.
Specify the metadata for the DAC, such as the DAC name, version, and description.
Run the Register method with the information specified above.
[Top]
Example (PowerShell)
The following example registers a database named MyDB as a DAC.
## Set a SMO Server object to the default instance on the local computer.
CD SQLSERVER:\SQL\localhost\DEFAULT
$srv = get-item .
## Specify the database to register as a DAC.
$dbname = "MyDB"
## Specify the DAC metadata.
$applicationname = "MyApplication"
$version = "1.0.0.0"
$description = "This DAC defines the database used by my application."
## Register the DAC.
$registerunit = New-Object Microsoft.SqlServer.Management.Dac.DacExtractionUnit($srv, $dbname, $applicationname, $version)
$registerunit.Description = $description
$registerunit.Register()
[Top]