SQL Server 2008 R2 application and multiple server management _mssql2008

SQL Server 2008 R2 has launched a SQL Servers tool that manages multiple instances of the SQL Server database engine, using these tools to quickly and efficiently manage multiple SQL Server servers through a central server.

Tools for managing multiple instances of SQL engine

Using the Tools browser in SQL Server Management Studio, you can add an existing SQL Server 2008 R2 data-tier application and a database engine instance to a centralized administration in the SQL Servers tool.

A snap-in called the data-tier application (DAC) is also introduced, which provides an application-based window for managing data-tier objects in a separate instance of SQL Server tools or the database engine. This SQL Server worker has the following features:

-Create a SQL Server common control point (Utility POINT,UCP): Install an instance of the SQL 2008 R2 Database engine and then upgrade it to UCP. This UCP is a central library for configuring and collecting performance data for all instances of SQL Server tools. UCP is the central inference point for SQL Server Tools. It supports activities such as Application Center policy or analysis of the resource utilization trends of an instance to predict when a central resource utilization policy will be exceeded.
-Universal control points allow users to gather metrics for a large number of multiple SQL Server instances and display them in Easy-to-use Dashboard reports that enable users to gain real-time access to multiple server operations. This report provides an overview of the entire enterprise in only one interface of SQL Server Management Studio. Common control points are implemented by creating databases on servers that run as common control points, and monitored instances upload their data to common control points. For common control points, Microsoft sets the majority of default values. Users can modify the default settings by clicking the "Tools management options" in the tool browser's common control point. By default, Microsoft specifies a critical value of 70% for all overused settings, and a critical value of all underutilized settings is 0%. 70% of the overuse settings may be suitable for most people, but for different enterprise application environment, the system's standard server load is still different, the user can make adjustments accordingly.
-Use the Tools browser in SQL Server Management Studio to add an existing SQL Server 2008 R2 data-tier application and database engine instance to a centralized administration in the SQL Servers tool.
-Set up a central policy to accommodate 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 enables users to quickly identify instances of data-tier applications and database engines. They may be too underutilized for the underlying hardware resources, perhaps too much for these resources. Next, users can make a plan to maximize the use of the server, such as consolidating an underutilized instance or database to a single server.

Managing multiple servers with a central management Server

In SQL Server 2008 R2, you can manage multiple servers by specifying a central management Server and creating a server group, which is designated as a SQL Server instance of the central Management Server that maintains server groups that maintain connection information for one or more instances of SQL Server. You can perform both Transact-SQL statements and policy-based management policies on a server group. SQL Server older than SQL Server version 2008 cannot be designated as a central management server.

Create a central management Server and server group

The central Management Server contains connection information about configuration targets and only Windows authentication is supported. Therefore, no authentication information is stored. This allows the central management Server to execute Transact-SQL statements concurrently on multiple servers. 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 concurrently on all servers in a server group, you need to open the Query Editor from the server group in the Registered Servers window. The actions that will be performed on all servers in the group Transact-SQL statements are written in the Query Editor. You can combine the results returned by a query into a single results pane, or you can return these results in a separate results pane. When the result is merged, the first server of the response sets the schema of the result set. To combine a 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 the result is returned, a message is displayed for the server. If you do not merge the results, the result set in each server is displayed in its own grid and uses its own schema. The query Editor can optionally include columns that provide the name of the server that generated each row, as well as a login (to connect to the server that provides each row).
-Policy-based Management
You can evaluate a policy for a registered server or for an entire server group. On-demand policy execution mode allows policy patterns to be evaluated and applied. 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
Because the connections to servers in the central Management Server group are performed by using Windows Authentication in the user's context, their effective 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 instance of SQL Server A, but only with limited permissions for instances of SQL Server B.

Summarize

SQL Server 2008 R2 through the above related technology to obtain information about multiple servers, users can make intelligent decisions according to the actual situation of their respective enterprises, make reasonable adjustments. Which server overload, which server also has free space, to balance the load between the servers, user-friendly hardware resources to make full use of. If the user discovers that the database application on server A is consuming too much CPU load, can not normal corresponding other related programs, and B server only a very low CPU load, then the database application may need to migrate from one server to another server a certain load, to achieve the reasonable use of equipment.

SQL Server 2008 R2 as Microsoft's newest database product and business intelligence solution, which contains a number of new and innovative features that are really seen as one of the things that comes to light, SQL Server 2008 The powerful function of R2 is bound to take off the wings of the enterprise informatization of the user.