microsoft sql server 2008r2 mirroring · pdf fileauthored by: avinash kumar singh company:...
TRANSCRIPT
Authored by: AVINASH KUMAR SINGH
COMPANY: PAXCEL TECHNOLOGIES PVT.LTD
Microsoft SQL Server 2008R2
Mirroring
CONFIGURING SQL SERVER 2008 R2 MIRRORING
1
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
2
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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
3
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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
used, the client specifies the FAILOVER_PARTNER in the connection string. After a
4
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
5
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
6
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
Note: By default TCP/IP is disabled
7
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
8
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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
RECOVERY option as shown.
9
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
10
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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
11
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
7. After that on the pop-up window click on Configure-Security Button as shown.
8. After clicking on that button mirroring wizard will appear on the screen. Click Next
12
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
13
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
14
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
15
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
15. After clicking next wizard will show the summary just review it and click finish as
shown it will configure endpoints for mirroring
16
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
17
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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.
18
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
20. Register your database at the mirroring monitor
19
Mic
roso
ft S
QL
Ser
ver
20
08
R2
Mir
rori
ng
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