SQL Server 2008 R2 introduces SQL Server tools to manage multiple instances of the SQL Server database engine. These tools allow you to quickly and efficiently manage multiple SQL Server servers through a central server.
Tools for managing multiple instances of SQL engines
Use the tool browser in SQL Server Management Studio to add existing SQL Server 2008 R2 data layer applications and database engine instances to SQL Server tools for centralized management.
A management unit called Data Layer Application (DAC) is also introduced, which provides an application-based window for managing data layer objects in separate instances of SQL server tools or database engines. This SQL server tool has the following functions:
- Create a SQL Server Universal 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 SQL Server Tools. UCP is the central reasoning point of SQL server tools. It supports activities such as application center policies or analyzing a single instance's resource utilization trend to predict when it will exceed the central resource utilization policy.
- General control points allow users to collect metrics for a large number of multi-SQL Server instances and display these data in an easy-to-use dashboard report. Users can use this report to understand the operation of multi-server in real time. This report provides an overview of the entire enterprise on just one interface in SQL Server Management Studio. General control points are implemented by creating databases on servers running as general control points, and monitored instances upload their data to general control points. For general control points, Microsoft sets the vast majority of default values. Users can modify the default settings by clicking "Tool Management Options" below in the general control point in the tool browser. By default, for all over-utilized settings, the threshold specified by Microsoft is 70%, and the threshold for all under-utilized settings is 0%. A 70% overuse setting may be suitable for most people, but for different enterprise application environments, the system's standard server load still differs, and users can make corresponding adjustments.
- Use the tool browser in SQL Server Management Studio to add existing SQL Server 2008 R2 data layer applications and database engine instances to the SQL Server tool for centralized management.
- Set up central policies to adapt and control the operation of SQL server tools and their consoles.
- Use this tool to display a console and detailed information. This information includes resource utilization, resource health, and configuration information for all instances in the SQL Server Tools. This console allows users to quickly identify instances of data layer applications and database engines. They may not be utilizing the basic hardware resources, and may use too much of these resources. Next, the user can develop a plan to maximize the use of the server, such as merging underutilized instances or databases into a single server.
Manage multiple servers with a central management server
In SQL Server 2008 R2, multiple servers can be managed by specifying a central management server and creating a server group, specifying a central management server instance maintenance server groups that maintain connection information for one or more SQL Server instances. Transact-SQL statements and policy-based management policies can be executed simultaneously on server groups. SQL Server earlier than SQL Server 2008 cannot be specified 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 supports Windows authentication only. Therefore, authentication information is not stored. In this way, the central management server can execute Transact-SQL statements on multiple servers simultaneously. To create a central management server and server group, you need to use the Registered Servers window in SQL Server Management Studio. A central management server cannot be a member of the group it maintains.
- Multi-server query
To execute Transact-SQL statements simultaneously on all servers in the server group, you need to open the query editor from the server group in the Registered Servers window. The Transact-SQL statements executed on all servers in the group are written in the query editor. You can merge the results returned by the query into a single result pane or return these results in a separate result pane. When the results are merged, the first server of the response will set the schema of the result set. To merge the result set, the query must return the same number of columns with the same name from each server. When merging results, if a server does not match the schema (column count and column name) returned by the first server when it returns the result, a message is displayed for that server. If the results are not merged, the result sets in each server will be displayed in its own grid and use its own schema. The query editor can choose to include some columns (providing the name of the server that generates each row) and a login name (providing it to connect to the server that provides each row).
- Policy-based management
The policy can be evaluated for registered servers or entire server groups. On-demand policy execution mode allows evaluation and application of policy modes. If a connection to one or more servers cannot be established, those servers are ignored and the policies are continued to be evaluated separately for the other servers.
- Security
Since connections to servers in the central management server group are performed in the context of the user by using Windows authentication, their valid permissions on servers in each server group may vary. For example, a user may be a member of the sysadmin fixed server role on an SQL Server A instance, but only has limited permissions for an SQL Server B instance.
Summarize
SQL Server 2008 R2 can obtain relevant information from multiple servers through the above related technologies, and users can make intelligent decisions and make reasonable adjustments based on the actual situation of their respective enterprises. Which servers are overloaded and which servers still have space available to balance the load between each server and facilitate the full utilization of user hardware resources. If the user finds that the database application on server A takes up too much CPU load and cannot correspond to other related programs normally, while server B only has a very low CPU load, then the database application may need to be suitable for migration from one server to another to achieve reasonable utilization of the device.
As Microsoft's latest database product and business intelligence solution, SQL Server 2008 R2 contains a large number of new and creative new features, which really makes people shine after seeing it. The powerful functions of SQL Server 2008 R2 will surely give users a wing to take off their corporate informatization path.