SQL Server Manageability Enhancements

SQL Server deployments are growing more numerous and more common in organizations. This fact demands that all database administrators be prepared by having the appropriate tools to successfully manage their SQL Server infrastructure. Recall that the previous releases of SQL Server included many new features tailored toward manageability. For example, database administrators could easily
leverage Policy Based Management, Resource Governor, Data Collector, Data-tier applications, and
Utility Control Point. Note that the product group responsible for manageability never stopped investing in manageability. With SQL Server 2012, they unveiled additional investments in SQL Server tools and monitoring features. The following list articulates the manageability enhancements in SQL Server 2012:

• SQL Server Management Studio With SQL Server 2012, IntelliSense and Transact-SQL debugging have been enhanced to bolster the development experience in SQL Server Management Studio.

• IntelliSense Enhancements A completion list will now suggest string matches based on partial words, whereas in the past it typically made recommendations based on the first character.

• A new Insert Snippet menu This new feature is illustrated in Figure 1-4. It offers developers a categorized list of snippets to choose from to streamline code. The snippet picket tooltip can be launched by pressing CTRL+K, pressing CTRL+X, or selecting it from the Edit menu.

• Transact-SQL Debugger This feature introduces the potential to debug Transact-SQL scripts on instances of SQL Server 2005 Service Pack 2 (SP2) or later and enhances breakpoint functionality.

• Resource Governor Enhancements Many organizations currently leverage Resource Governor to gain predictable performance and improve their management of SQL Server workloads and resources by implementing limits on resource consumption based on incoming requests. In the past few years, customers have also been requesting additional improvements to the Resource Governor feature. Customers wanted to increase the maximum number of resource pools and support large-scale, multitenant database solutions with a higher level of isolation between workloads. They also wanted predictable chargeback and vertical isolation of machine resources.

The SQL Server product group responsible for the Resource Governor feature introduced new capabilities to address the requests of its customers and the SQL Server community. To begin, support for larger scale multitenancy can now be achieved on a single instance of SQL Server because the number of resource pools Resource Governor supports increased from 20 to 64. In addition, a maximum cap for CPU usage has been introduced to enable predictable chargeback
and isolation on the CPU. Finally, resource pools can be affinitized to an individual schedule or a group of schedules for vertical isolation of machine resources. A new Dynamic Management View (DMV) called sys.dm_resource_governor_resource_pool_affinity improves database administrators’ success in tracking resource pool affinity. Let’s review an example of some of the new Resource Governor features in action. In the following example, resource pool Pool25 is altered to be affinitized to six schedulers (8, 12, 13, 14, 15, and 16), and it’s guaranteed a minimum 5 percent of the CPU capacity of those schedulers. It can receive no more than 80 percent of the capacity of those schedulers. When there is contention for CPU bandwidth, the maximum average CPU bandwidth that will be allocated is 40 percent.

ALTER RESOURCE POOL Pool25
WITH(
MIN_CPU_PERCENT = 5,
MAX_CPU_PERCENT = 40,
CAP_CPU_PERCENT = 80,
AFFINITY SCHEDULER = (8, 12 TO 16),
MIN_MEMORY_PERCENT = 5,
MAX_MEMORY_PERCENT = 15,
);

• Contained Databases Authentication associated with database portability was a challenge in the previous versions of SQL Server. This was the result of users in a database being associated with logins on the source instance of SQL Server. If the database ever moved to another instance of SQL Server, the risk was that the login might not exist. With the introduction of contained databases in SQL Server 2012, users are authenticated directly into a user database without the dependency of logins in the Database Engine. This feature facilitates better portability of user databases among servers because contained databases have no external dependencies.

• Tight Integration with SQL Azure A new Deploy Database To SQL Azure wizard, pictured in Figure 1-5, is integrated in the SQL Server Database Engine to help organizations deploy an on-premise database to SQL Azure. Furthermore, new scenarios can be enabled with SQL Azure Data Sync, which is a cloud service that provides bidirectional data synchronization between databases across the datacenter and cloud.

• Startup Options Relocated Within SQL Server Configuration Manager, a new Startup Parameters tab was introduced for better manageability of the parameters required for startup. A DBA can now easily specify startup parameters compared to previous versions of SQL Server, which at times was a tedious task. The Startup Parameters tab can be invoked by right-clicking a SQL Server instance name in SQL Server Configuration Manager and then selecting Properties.

• Data-Tier Application (DAC) Enhancements SQL Server 2008 R2 introduced the concept of data-tier applications. A data-tier application is a single unit of deployment containing all of the database’s schema, dependent objects, and deployment requirements used by an application. SQL Server 2012 introduces a few enhancements to DAC. With the new SQL Server, DAC upgrades are performed in an in-place fashion compared to the previous side-byside upgrade process we’ve all grown accustomed to over the years. Moreover, DACs can be deployed, imported and exported more easily across premises and public cloud environments, such as SQL Azure. Finally, data-tier applications now support many more objects compared to the previous SQL Server release.