Implementing Availability feature enhancements_Microsoft SQL Server 2008 R2 Administration Cookbook-QQ阅读女生古言网

上QQ阅读APP看书，第一时间看更新

Implementing Availability feature enhancements

Data recovery is a key step in providing availability to the systems. Hardware and software play a vital role in helping the system to minimize the downtime. The more complex and larger a database application, the more important it is to automate administrative tasks with proactive maintenance tasks to ensure data is available. The key factors for hardware and software solutions are cost, implementation, and feature support, which has its own advantages and disadvantages.

Any high-availability solution must have provisions for hardware availability and data availability. These two provisions must work in parallel to build an effective, high-availability strategy, which requires planning and implementation at the SQL Server instance configuration level. SQL Server 2008 R2 has no new features with regard to Availability; with the only enhancements in this area being the performance improvements to database mirroring, replication, and database backup strategies. This recipe covers the specific availability performance improvements to Database Mirroring from SQL Server 2008 R2 version, which includes the configuration of endpoints, post configuration, and failover process settings.

Database Mirroring (DBM) is the software solution for increasing the database availability, where all the bulk operations are fully logged. The main benefits of database mirroring are:

Increased data protection
Increased database availability
Automated failover support, which provides minimized downtime for the rolling upgrade of databases

Before we begin with this recipe, it is essential to highlight the authentication options that are required for the three SQL Server instances, which are referred to as partners: Principal, Mirror, and Witness to communicate with one another.

The partner that owns Principal role stands as the main database. For hot standby provision, the partner that owns Mirror role holds a copy of the database. The third partner in database mirroring is called Witness that supports automatic failover by verifying whether or not Principal server is functioning. It does not store any database information. By taking SQL Server 2008 R2 new performance enhancements, it is ideal to set up database mirroring pairs on a dedicated network adapter, as network configuration plays an important role in the performance and transactional safety.

By default, the DBM endpoint requires the encryption of data that is sent over DBM connections, which contains a bit of a performance lag. Unless you obtain a guarantee that both the mirroring partners' network is secure, you should not disable the encryption to gain performance.

The method of improving the performance and reliability of the storage depends on the configuration of drives, which implies the way data is divided between disks to distribute load, or mirrored to recover from disk failure. The implementation of RAID-based solutions is beyond the scope of this chapter. However, such a solution has been presented in the Chapter 10, Troubleshooting in this book.

Tip

Although, the core functionality of database mirroring has not changed, SQL Server 2008 R2 does introduce data stream compression and automatic page recovery. These improvements don't require changes to the existing syntax.

Getting ready

The databases that are involved in mirroring, must have the database recovery model at full to ensure the mirror will fully execute all bulk operations. The Mirror database must be prepared with a restore from the primary server's full database backup copy. Also, the server instances that are involved in DBM should have a dedicated network adapter. The endpoint configuration is important as it allows the database mirroring services to interact with each other. The configuration must be applied on the Principal server, Mirroring, and Witness server (if included) as per the relevant database mirroring mode. To configure a dedicated network line, the server must have two network interface cards with two different IP addresses. For the recipe, let us assume the configuration for Principal server and Mirror server is as follows:

Principal Instance Configuration:
- Server Name: DBIA-SSQA
- SQL Server Name: DBIA-SSQA\SQL2K8R2
- Edition: Enterprise Edition
- NIC 1: 161.19.70.01
- NIC 2: 161.19.70.02
Mirror Instance Configuration:
- Server Name: DBIA-SSQA
- Edition: Enterprise Edition
- SQL Server Name : DBIA-SSQA\SQL2K8
- NIC 1: 161.19.70.03
- NIC 2: 161.19.70.04
Witness Instance Configuration:
- Server Name: DBIA-SSQA\SQLEXPRESS
- Edition: SQLEXPRESS Edition
Isolate the network connection between NIC2 of Principal instance and NIC2 of Mirror instance.

Now, we are ready to implement the database mirroring endpoint configuration as a dedicated mirroring session.

How to do it...

The endpoint configuration setup is possible using the Configure Database Mirroring Security from SQL Server Management studio. For this recipe, we will create endpoints to configure dedicated DBM sessions using TSQL methods, and also show you how it looks when you configure with the wizards. The CREATE ENDPOINT command is used to create the mirroring endpoints. The syntax that applies to database mirroring is as follows:

CREATE ENDPOINT endPointName [ AUTHORIZATION login ] STATE = { STARTED | STOPPED | DISABLED } AS TCP (LISTENER_PORT = listenerPort ) FOR DATABASE_MIRRORING ( [ AUTHENTICATION = {WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]| CERTIFICATE certificate_name } ][ [ , ] ENCRYPTION = { DISABLED |SUPPORTED | REQUIRED } [ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] ][,] ROLE = { WITNESS | PARTNER | ALL })

To create the endpoints using the Wizard, you will need to complete the following steps:

Expand the database pane and select the desired database to be chosen for database mirroring.
Right-click on the database and select either the Mirror or the properties option.
If the properties option is selected, then click on Mirroring on the left-hand side of the database properties screen.
On the right-hand screen from the status column we can see the value This database has not been configured for mirroring.
Click on the Configure Security button to open up the configuration. This is where we begin to setup the Endpoint configuration and data encryption option for database mirroring pairs.

On the PRINCIPAL instance, execute the following from the query editor:

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022,LISTENER_IP=(161.19.70.02))

FOR DATABASE_MIRRORING (ROLE=PARTNER)

The following screenshot is presented to set the listener port and data encryption when using the wizard for the PRINCIPAL instance:

On the MIRROR instance, execute the following:

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5023,LISTENER_IP=(161.19.70.04))
FOR DATABASE_MIRRORING (ROLE=PARTNER)

The following screenshot illustrates the listener port and data encryption when using the wizard for the MIRROR instance:

On the WITNESS instance, execute the following (see the next screenshot):

CREATE ENDPOINTMirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5024,
FOR DATABASE_MIRRORING (ROLE=WITNESS)

It is possible to SUSPEND a mirroring session and later RESUME it. If the mirroring is suspended for an extended period of time then, ensure sufficient disk space is available on the drive where the transaction log file is located.

The WITNESS is an essential instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate automatic failover, when no user data is stored on the witness server.

To enable Database Mirroring between two dedicated network adapters that is, between NIC2 of the Principle and NIC2 of the Mirror, we need to have a Full Qualified Domain Name(FQDN) for each of those instances. To perform this, complete the following steps:

Add the corresponding FQDN of NIC2 on the host file of each server.
The host file is located in the C:\Windows\System32\drivers\etc folder. Append the IP address of the corresponding instance and FQDN at the end of the host file:
```
161.19.70.02 DBM-MIRO.DBIA-SSQA.com 161.19.70.04 DBM-PRIM.DBIA-SSQA.com
```
Now, restart the Principal instance and Mirror instance to avoid any connectivity issues between the instances.

Set the partner for the MIRROR instance using the below script:

ALTER DATABASE <DatabaseName>
SET PARTNER = 'TCP://DBM-PRIM.DBIA-SSQA.com:5022'
Set the partner for PRINCIPAL instance using below script:
ALTER DATABASE <DatabaseName>
SET PARTNER = 'TCP://DBM-MIRO.DBIA-SSQA.com:5023'

If the DBM instances are not running under the same 'domain login', you will need to create a windows login on each participating instance. This can be achieved with a TSQL script as follows:

USE master GO CREATE LOGIN [SSQAPROD\DBMAdmin]FROM WINDOWS GO

The previous script must be executed on a Mirror instance in order to allow access to the principal and witness SQL Server instances. Additionally, you must explicitly grant the remote login access to the configured endpoints.

GRANT CONNECT ON ENDPOINT::Mirroring TO [SSQAPROD\DBMAdmin] GO

How it works...

Connection management for database mirroring is based on endpoints. An endpoint is also treated as one of the server objects that enables SQL Server to communicate over the network.

For database mirroring, a server instance requires a dedicated database mirroring endpoint to receive database mirroring connections from other server instances. The database mirroring endpoint of a server instance is associated with the port on which the instance listens for database mirroring messages. Each database mirroring endpoint server listens on a unique TCP port number.

The wizard or TSQL method will configure the endpoints including granting permissions to a service account to communicate using the endpoint, as per the CREATE ENDPOINT syntax. Each SQL Server instance can have only one database mirroring endpoint. All databases in that instance—that are involved in database mirroring—must use the same endpoint. If the principal, mirroring, or witness instances exist on the same physical server then you are required to use different port numbers on their individual endpoints.

The next step for a database mirroring endpoint requires the encryption of data that is sent over mirroring connections. In this case, the endpoint can connect only to endpoints that also use encryption. Database mirroring endpoints support two encryption algorithms—RC4 and AES.

The final step is to ensure that the mirroring endpoint is created and configured correctly. The verification settings can be accomplished by querying the sys.database_mirroring_endpoints system catalog view. The following query confirms the name of the endpoint, the state (whether or not it has started) and other mirroring related attributes:

SELECT d.name, d.database_id, m.mirroring_role_desc,
m.mirroring_state_desc, m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL

There's more...

In order to continue the transactions from the primary server to the mirror server, it is ideal to restore the transaction log backup (which has been taken after the full database backup) on the mirror server. It is also essential that no transactions should occur on the primary database until the transaction log is restored on the mirror server.

Restoring a user database doesn't bring along the necessary SQL or Windows logins to the server containing the mirrored database. Any SQL or Windows logins mapped to database users in the principal database should also be created on the mirrored SQL Server instance. These logins should be ready in the event of a failover; when the mirror database takes over the role as the principal. If the logins are not on the mirror database SQL Server instance, the database users within the mirrored database will be orphaned (the database users, without any associated logins will not be able to be access the database).