SQL Server 2008 R2 has introduced several SQL server tools to manage multiple instances of the database engine of the SQL server. These tools can be used to manage multiple SQL Server servers quickly and efficiently through the central server.
Tools for managing multiple instances of SQL engines
Using the tool browser in SQL Server Management Studio, existing SQL Server 2008 R2 data layer applications and database engine instances can be added to the SQL server tools for centralized management.
In addition, a management unit called Data Layer Application (DAC) has been introduced, which provides an application-based window for managing data layer objects in a single instance of a SQL server tool or database engine. This SQL server tool has the following functions:
– Create Utility Control Point (UCP): Install an instance of the SQL Server 2008 R2 database engine and upgrade it to UCP. This UCP is a central library for configuring and collecting performance data for all instances in the SQL server tool. UCP is the central reasoning point of the SQL server tool. It supports the application of activities such as central policy or the analysis of resource utilization trends in an example to predict when it will exceed the central resource utilization policy.
– Universal control points allow users to collect a large number of indicators of multiple instances of SQL Server and display these data in an easy-to-use dashboard report, through which users can grasp the operation of multiple servers in real time. This report provides an overview of the entire enterprise on only one interface of SQL Server Management Studio. Universal control points are implemented by creating databases on servers running as universal control points, and monitored instances upload their data to universal control points. For generic control points, Microsoft has set most default values. Users can modify the default settings by clicking on Tool Management Options below the Universal Control Point in Tool Browser. By default, Microsoft specifies a threshold of 70% for all overutilized settings and 0% for all underutilized settings. 70% of the overutilization settings may be appropriate for most people, but for different enterprise application environments, the standard server load of the system is still different, and users can make adjustments accordingly.
– Using the tool browser in SQL Server Management Studio, the existing SQL Server 2008 R2 data layer applications and database engine instances are added to the SQL server tools for centralized management.
– Set up central policy to adapt and control the operation of SQL server tools and their consoles.
– Use this tool browser to display a console and detailed information. This information includes resource utilization, resource health, and configuration information for all instances in the SQL server tool. This console allows users to quickly identify instances of data layer applications and database engines. They may be underutilized for basic hardware resources, or they may be overused. Next, users can make a plan to maximize the use of servers, such as merging underutilized instances or databases into a single server.
Using a central management server to manage multiple servers
In SQL Server 2008 R2, multiple servers can be managed by specifying a central management server and creating a server group, and an instance of SQL Server designated as a central management server maintains a server group, which maintains connection information for one or more instances of SQL Server. Transact-SQL statements and policy-based management policies can be executed simultaneously for server groups. SQL Server earlier than the 2008 version of SQL Server cannot be designated as a central management server.
Create a central management server and server group
The central management server contains connection information about the configuration target and only supports Windows authentication. Therefore, authentication information is not stored. In this way, the central management server can execute Transact-SQL statements on multiple servers at the same time. To create a central management server and server group, you need to use the Registered Servers window in SQL Server Management Studio. The central management server cannot be a member of the group it maintains.
– Multi-server query
To execute Transact-SQL statements simultaneously for all servers in the server group, you need to open the query editor from the server group in the Registered Servers window. Transact-SQL statements are written in the query editor for operations performed on all servers in the group. The results returned by the query can be merged into a single result pane, or they can be returned in a separate result pane. When merging results, the first server that responds sets the architecture of the result set. To merge result sets, queries must return the same number of columns with the same name from each server. When merging results, if a server does not match the architecture (column count and column name) returned by the first server when returning the results, a message will be displayed for the server. If the results are not merged, the result set in each server will be displayed in its own grid, using its own architecture. The query editor can choose to include columns (providing the name of the server that generates each row) and login names (for connecting to the server that provides each row).
– Policy-based management
Policies can be evaluated for registered servers or entire server groups. On-demand policy execution patterns allow for evaluation and application of policy patterns. If a connection to one or more servers cannot be established, these servers will be ignored and policies will continue to be evaluated separately for other servers.
Since connections to servers in central management server groups are performed in the context of users using Windows authentication, their effective permissions on servers in each server group may differ. For example, a user may be a member of the sysadmin fixed server role on an instance of SQL Server A, but only with limited privileges on an instance of SQL Server B.
SQL Server 2008 R2 can obtain relevant information of multiple servers through the above related technologies. Users can make intelligent decisions and make reasonable adjustments according to the actual situation of their enterprises. Which servers are overloaded and which servers have available space to balance the load between servers, so as to make full use of users’hardware resources. If the user finds that the database application on server A occupies too much CPU load, and can not normally correspond to other related programs, while server B only has a very low CPU load, then the database application may need to be suitable for migrating from one server to another server with a certain load, in order to achieve the rational utilization of equipment.
As Microsoft’s latest database products and business intelligence solutions, SQL Server 2008 R2 contains a large number of innovative and innovative new features, which really make people see the bright future. The powerful function of SQL Server 2008 R2 will surely insert wings for users’enterprise informatization.