Monitoring Data-tier Applications
A data-tier application (DAC) can be monitored from the Utility Explorer and Object Explorer in SQL Server Management Studio (SSMS), along with system views and tables. In addition, all objects in the database contained in the DAC can be monitored using standard database and Database Engine monitoring techniques.
Monitoring DACs using the SQL Server Utility
The Deployed Data-tier Applications detail page in the Management Studio Utility Explorer displays a dashboard that reports the resource utilization of all DACs that have been deployed to managed instances of the Database Engine. The top pane of the details page lists each deployed DAC with visual indicators showing whether their utilization of CPU and file resources are outside the policies defined for the SQL Server Utility. If you select any DAC in the list view, further details are displayed in tabs in the bottom pane of the page. For more information about the information presented on the details page, see Deployed Data-tier Application Details (SQL Server Utility).
After using the Deployed Data-tier Applications detail page to quickly identify any DACs that are either under-utilizing or stressing their hardware resource, you can make plans to address any problems. Multiple DACs that are not fully utilizing their current hardware resources could be consolidated to a single server, freeing some of the servers for other uses. If a DAC is stressing the resources on its current server, the DAC can be moved to a larger server, or additional resources can be added to the current server.
The minimum and maximum limits for resource usage are defined by application monitoring policies defined in the Utility Administration details page. Database administrators can tailor the policies to match the limits established by their organizations. For example, one company might set 75% as the maximum CPU utilization for a DAC, while another company might set the maximum at 80%. For more information about setting application monitoring policies, see Utility Administration (SQL Server Utility).
To view the Deployed Data-tier Applications detail page:
Select the View/Utility Explorer menu.
Connect the Utility Explorer to the utility control point (UCP).
Select the View/Utility Explorer Details menu.
Select the Deployed Data-tier Applications node in the Utility Explorer.
The information in the Deployed Data-tier Applications detail page comes from the data in the utility management data warehouse, which defaults to collecting the data every 15 minutes. The interval can also be tailored using the Utility Administration details page.
Viewing DACs in the Object Explorer
The SSMS Object Explorer displays basic configuration information about each DAC deployed to an instance of the Database Engine. This includes both managed instances that have been enrolled in the SQL Server Utility, and stand-alone instances that cannot be viewed in the Utility Explorer.
To view the details of a DAC deployed to an instance of the Database Engine:
Select the View/Object Explorer menu.
Connect to the instance of the Database Engine from the Object Explorer pane.
Select the View/Object Explorer Details menu.
Select the server node in Object Explorer that maps to the instance, and then navigate to the Management\Data-tier Applications node.
The list view in the top pane of the details page lists each DAC deployed to the instance of the Database Engine. Select a DAC to display the information in the detail pane at the bottom of the page.
The right-click menu of the Data-tier Applications node is also used to deploy a new DAC or delete an existing DAC.
Using the DAC System Views and Tables
The msdb.dbo.sysdac_history_internal system table records the success or failure of all DAC management actions performed on an instance of the Database Engine. The table records the time each action occurred, and which login initiated the action. For more information, see sysdac_history_internal (Transact-SQL).
The DAC system views report basic catalog information. For more information, see Data-tier Application Views (Transact-SQL).
Monitoring DAC Databases
After a DAC has been successfully deployed, the database contained in the DAC operates the same as any other database. Use standard Database Engine techniques and tools for monitoring the performance, log, events, and resource utilization of the database. For more information, see Monitoring (Database Engine).