Microsoft SQL Server 2008R2 Mirroring

(1)

Authored by: AVINASH KUMAR SINGH COMPANY: PAXCEL TECHNOLOGIES PVT.LTD

Microsoft SQL Server 2008R2

Mirroring

CONFIGURING SQL SERVER 2008 R2 MIRRORING

(2)

ro so ft SQL Ser v er 2 0 0 8 R 2 Mir ro rin g

SQL SERVER HIGH AVAILABLITY SOLUTIONS

SQL SERVER DATABASE MIRRIORING

o Operates at the database scope.

o Uses a single, duplicate copy of the database

o Uses standard servers

o Provides limited reporting on the mirror server by using database snapshots.

o When it operates synchronously, provides for zero work loss through delayed commit on the principal database.

Database mirroring offers a substantive increase in availability over the level previously possible with SQL Server and offer an easy-to-manage alternative to failover clustering.

Disk mirroring involves keeping two separate copies of a disk; while database mirroring lets solution providers create a single duplicate database copy.

Database mirroring has two advantages that failover clustering doesn't have. First, it operates at the database level instead of the server level, which means that database mirroring gives you much more flexibility than failover clustering.

The other advantage is that you can use standard servers for database mirroring. Microsoft recommends that customers purchase servers that are compatible with Windows Servers, but they don't have to buy hardware that is specifically certified for failover clustering. And, with database mirroring, you don't have to worry about the cost and complexity of using shared storage.

Proven SQL Server Architectures for High Availability and Disaster

Recovery

SQL Server 2005 and SQL Server 2008 include many technologies that can be used to minimize downtime and maximize data protection so that database administrators can ensure smooth operation, continuous access to business critical data, and meet availability levels according to various service level agreements.

Sometimes high-availability and disaster-recovery architectures are unfortunately designed without considering the necessary business requirements—possibly there is already an incumbent technology, or the designers are familiar with a certain technology and choose it as the basis for a new architecture. This choice, when coupled with a lack of understanding of the capabilities of the various high-availability and disaster-recovery technologies, can lead to an architecture that fails to meet the business needs.

(3)

Mic ro so ft SQL Ser v er 2 0 0 8 R 2 Mir ro rin g

It is imperative that the high-availability and disaster-recovery requirements of the business are the drivers when evaluating which technologies are suitable as part of the architecture. The two major business needs to consider are:

 The duration of acceptable application downtime, whether from an unplanned outage or from scheduled maintenance/upgrades (i.e. the defined Recovery Time Objective— RTO).

 The ability to accept potential data loss from an outage (i.e. the defined Recovery Point Objective—RPO).

There is an existing whitepaper, “High-Availability with SQL Server 2008” (available at

http://msdn.microsoft.com/en-us/library/ee523927.aspx), that contains information about each of the high-availability technologies in SQL Server 2008, as well as further links to other whitepapers and technical resources. It also describes how to evaluate business requirements and technical/non-technical limitations to help choose appropriate technologies.

However, there is a lack of information regarding proven architectures and real-life customer deployments, where the high-availability and disaster-recovery architecture was chosen after careful requirements analysis and technology evaluation.

This whitepaper provides a consolidated description of proven and commonly deployed high-availability and disaster-recovery architectures, in terms of the technologies used and the business requirements they are able to meet.

Furthermore, before committing to the implementation of any technology strategy, many companies would like some level of reassurance that what they are attempting has been successfully accomplished previously. To meet this need, Microsoft regularly publishes case studies showing how their technologies have been used. This whitepaper also includes references to relevant case studies of real-life customer deployments for each of the architectures described.

Together these two whitepapers will provide the information necessary to allow the design of an appropriate and successful high-availability and disaster-recovery architecture.

Failover Clustering for High Availability with Database Mirroring for

Disaster Recovery

In this architecture, failover clustering provides the local high availability and database mirroring provides the disaster recovery capability. A failover cluster on its own protects against physical server, Windows Server, and SQL Server failures but does not maintain a redundant

(4)

copy of the data and so does not protect against a major outage like an I/O subsystem failure, power failure, or failure of the network link to the primary data center.

Database mirroring is one way to provide a redundant copy of a single database on a separate physical server, where the server can be in the same data center or geographically separated. This architecture is widely adopted by customers who are familiar and comfortable with the installation, configuration, and maintenance of failover clusters.

A typical implementation of this architecture involves a failover cluster in the primary data center with database mirroring to a secondary data center or disaster-recovery site, as shown

in Figure 1 below.

Failover clustering combined with database mirroring

There are a number of variations and configuration options for this architecture depending on the business requirements, including the following:

1. Each data center has a failover cluster with database mirroring between them. If the business requirements state that the workload performance should not be impacted after a failover to the secondary data center, the mirror server needs to have the same hardware configuration (and hence workload servicing capability) as the failover cluster in the primary data center. The alternative, of course, is to have a less capable stand-alone server as the mirror server—however; this is not a recommend best practice. 2. Synchronous vs. asynchronous database mirroring. Synchronous database mirroring can

allow a zero data-loss requirement to be met, potentially with some workload performance impact depending on the type of workload and the network bandwidth between the two data centers. Asynchronous database mirroring does not guarantee zero data loss in the case of a disaster, but has no impact on workload performance. 3. Automatic client connection to the secondary data center. If explicit client redirection is

(5)

database mirroring failover has occurred, the client simply has to reconnect and the connection will automatically be made to the secondary data center. Alternatively, some form of external routing can be used (some installations have used DNS routing, for instance).

Configuring SQL SERVER 2008R2 Mirroring

Here we will show how SQL Server R2 clustering is configured .We have installed three instance of SQL SEERVER on two nodes i.e. two servers .Following are the details of server both the nodes are running under same DNS –Domain Name Service.

 SERVERA -(PRINCIPAL SERVER)

 SERVERB -(MIRRIOR SERVER)

 SERVERB\WITNESS -(WITNESS SERVER)

1. Now configure the service accounts of SQL SERVER and SQL SERVER AGENT of the entire instances under the same domain account as shown in the below screen shot.Here we have configured them under pax\administrator.

(6)

(7)

2. Now enable TCP/IP Services on all of the instances installed and restart the services of SQL SERVER in order to bring changes to effect.

(8)

3. Now at this point we have configured our instances now we need to configure our database accordingly in order to achieve mirroring. First change the recovery model at principal instance of SQL Server to full as shown.

(9)

4. Now after changing the recovery model of database take the full backup of the database as shown.

5. Copy that back file to the second node and then restore the database with NO

(10)

(11)

6. Now at this point we have configured our database for mirroring .Now go to the principal server i.e. SERVERA to configure mirroring services .Before that run the following query on the on both principal and mirrored instance to give grant rights to the login under which mirroring will be configured .

GRANT CONNECT ON ENDPOINT::Mirroring TO [PAX\Administrator]

Now launch mirroring on this database as shown

(12)

7. After that on the pop-up window click on Configure-Security Button as shown.

(13)

9. After clicking next it will prompt including witness server as shown choose yes and click next.

10. It will prompt to configure server instance just check the checkbox in front of witness server and click next to proceed as shown.

(14)

11.Now it will be by default connected to the principal server click next to proceed as shown.

12.Now it will prompt to connect for mirrored instance just select the mirrored

instance i.e. SERVERB on which we have restored the backup as shown by giving the required credentials and click next to proceed.

(15)

13.Now it will prompt for the credentials for witness sever to connect, fill in the required credential and click next to proceed as shown.

14.Now it will prompt for service accounts of SQL Server as shown we had configured the entire instance on same domain account fill in the textboxes accordingly as shown.

(16)

15.After clicking next wizard will show the summary just review it and click finish as shown it will configure endpoints for mirroring

(17)

16.At this point we have configured mirroring now it will prompt for starting mirroring services. Click on start button to start mirroring

17.Now at this point we have configured mirroring with high safety with automatic failover (synchronous) -Requires a witness server instance. Commit changes at both principal and mirror server if both are available. The witness server controls the automatic failover to the mirror server if principal becomes unavailable as shown.

(18)

18.Now a small change in the connection string of the .NET application will make it accessible to the mirrored instance database as shown.

Data Source=myServerAddress;Failover Partner=myMirrorServerAddress; Initial Catalog=myDataBase;Integrated Security=True;

19.We can even monitor database mirroring through Database mirroring Monitor as shown.

(19)

(20)

21.Now you can monitor mirroring of your database from this console.

Why Mirroring, Why not clustering?

 We achieved this by zero downtime in the production environment and it is better alternative to clustering because clustering requires much more downtime rather than this one, more over Microsoft recommends that customers purchase servers that are compatible with Windows Servers, but they don't have to buy hardware that is specifically certified for failover clustering. And, with database mirroring, you don't have to worry about the cost and complexity of using shared storage.

 Also it operates at the database level instead of the server level, which means that database mirroring gives you much more flexibility than failover clustering.

 It also keeps the copy of database as in case of clustering there is only one making it more venerable in case of disk failure