How to: 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 SQL Azure).
Before You Begin
You can register a DAC from a SQL Azure Database or a database in SQL Server 2005 Service Pack 4 (SP4) or later. For 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.
Note 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 Azure requires membership in the dbmanager or serveradmin roles. Registering a DAC that contains logins in SQL Azure requires membership in the loginmanager or serveradmin roles.
Limitations
DAC registration can only be performed on SQL Azure, or SQL Server 2005 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 contains objects that are not supported in a DAC. For more information about the types of objects supported in a DAC, see DAC Support For SQL Server Objects and Versions.
Register a DAC Using PowerShell
Create a file RegisterDAC.ps1 containing the following code.
Add code to create a SMO Server object and set it to the instance containing the database from which you want to extract a DAC. This example sets a Server object to the default instance on the local computer:
## Set a SMO Server object to the default instance on the local computer. CD SQLSERVER:\SQL\localhost\DEFAULT $srv = get-item .Add code to specify the database. This example specifies a database named MyDB:
## Specify the database to register as a DAC. $dbname = "MyDB"Add code to specify the metadata for the DAC. This example specifies the DAC name, version, and description:
## Specify the DAC metadata. $applicationname = "MyApplication" $version = "1.0.0.0" $description = "This DAC defines the database used by my application."Add code to run the Register method with the information specified above.
## Register the DAC. $registerunit = New-Object Microsoft.SqlServer.Management.Dac.DacExtractionUnit($srv, $dbname, $applicationname, $version) $registerunit.Description = $description $registerunit.Register()
Run RegisterDAC.ps1 from either a PowerShell session in which you have loaded the SQL Server PowerShell snapins, or by using the sqlps command prompt utility.
Using the Register Data-tier Application Wizard
In Management Studio, you can launch the Register Data-tier Application Wizard by:
- Right-click a database node in Object Explorer, point to Tasks, and then select Register Data-tier Application…
The wizard creates a DAC definition that defines the objects in the database represented by the node you selected in the Object Explorer hierarchy. The DAC definition is stored in the msdb system database (master in SQL Azure). The combination of the DAC description and the database form a DAC instance. Information about the DAC instance is uploaded into the SQL Server Utility when the next utility collection set is transmitted to the Utility Control Point. The information can be viewed in the Deployed Data-tier Applications node of the Utility Explorer.
Click on a link in the list below to navigate to details for a page in the wizard:
Introduction Page
Set Properties Page
Validation and Summary Page
Register DAC Page
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.
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.
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.
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.
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.
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 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.
Change History
Updated content |
|---|
Added section on using PowerShell. |