Introduction to Database High availability - Data Group by wangnianwu


									              Introduction to Database High availability Solution

                              -: NOTE :-
   Sharing with anybody or with any training institute without written
                    permission is 100% prohibited.

                     Please see the copy right information.

                                         Written by
         Zakir Hossain, Manager – Enterprise Architecture Configuration & Database,
                                 US Dept. of Defense
                                 CEO, Data Group
          CS Graduate (OSU), ITIL V3, OCP, OCA, MCDBA, MCIPT (Database Admin)
                                     Other Certifications:
Oracle RAC Admin, Oracle Backup & Recovery Admin, Oracle Performance & Monitoring Admin,
Oracle Application Server Admin, System Admin (Windows/RedHat), Certified Java Programmer

                 Introduction to Database High availability Solution

Introduction to High Availability Options in SQL Server:
We as a DBA need to keep in mind that data is business and business is data. How we can make data
always available to users, vendors, employees. It means database is available 99.99% of the time. It
calculates that 5 minutes a year your database may be down. It is very tough to maintain and very

After release of SQL Server 2008 R2, Microsoft claims six (6) 999999 availability meaning SQL Server
2008 R2 is the only database system that is available for 99.9999% of time comparing other popular
databases like Oracle, DB2, MySQL, Sybase.

Companies use high availability solutions of database to keep high uptime of a database and to distribute
data to multiple locations regardless of geographical locations.

Different methods of Database High Availability:
   1. Failover clustering
   2. Mirroring
   3. LogShipping
   4. Replication

   Failover Clustering: Is the best option. All the things happen here automatically. Setting is
           It provides high-availability for an entire instance of SQL Server
           Requires minimum of 2 nodes and can have up-to 16 nodes in a cluster
           Requires two or more shared disks
           Multiple instances act like one instance. End users do not notice which instance they are
             connected to. If there is any application running against a database and if there is a
             failure, application will not suffer from downtime. All applications automatically will be
             transferred to the available instance in the cluster
           Transparent failover: Users and applications connection automatically fails over to the
             other instance. Users do not notice.
           This feature is only available in Enterprise editions of all versions of SQL Server (2000,
             2005 and 2008). However, the Standard Edition SQL Server 2008 also support this
           It is the best and most complicated solutions among other database high availability
           It is built on top of windows server cluster. So, first need to install and configure
             windows cluster to install and configure SQL Server clustering solutions
           SQL Server 2008 uses a new architecture for clustering than all the previous versions of
             SQL Server

   Database Mirroring:
          Database mirroring option is only available in SQL Server 2008.
          It is a software solution
          Source database is called Principal database
          Target database is called Mirrored database
          Creates a mirror image of a database

               Introduction to Database High availability Solution

          Since the Mirrored database is inaccessible to clients/users.
          To take the Mirrored Database accessible to users, need to create a snapshot database of
           the Mirrored Database
          The Snapshot database provides read-only access to users. So companies uses Snapshot
           database for reporting purposes

       How to create:
              i. Mirror is created by restoring a backup of the principal database
             ii. Then restore the database from the backup in NO RECOVERY mode on the mirror
            iii. Then restore transaction log in NO RECOVERY mode
            iv. Now the principal server sends Active Transactions from the Transaction Log to
                 the Mirrored Database
             v. In a few seconds two databases will look like identical

              i. Failover can be as fast as a few seconds
             ii. Failover process can be automatic
            iii. Easy and simple to configure and manage
            iv. Data from principal server copy in real time

              i. Can have only one Mirrored Database copy of a database
             ii. Mirrored database is inaccessible to clients/users. It means clients/users cannot
                    use the Mirrored database
            iii. It is a per database solution, not per server solution. So, for every database you
                    have to create a separate Mirrored Database. However clustering is a solution
                    for entire instance
            iv. It captures only Data in the database. For example
                      1. It does not capture logins
                      2. It does not capture stored procedure, views etc
                      3. It captures data only added by end users
Log Shipping:
       It is a refined version of Traditional Replication solution. It is really a hot topic
       It is a software solution
       It is very similar to Database Mirroring
       Creates and maintains one or more copies of a single database
       Can have multiple copies of standby databases of the same database
       Standby databases are read-only. Lot of company uses as reporting server
       You can combine database mirroring with Log Shipping as well

It is called traditional Replication. It is the first technology in SQL Server to replicate a database.
However, a newer technology for replicating a database is called Log Shipping. The newest
technology for replicating a database is called database mirroring.

                 Introduction to Database High availability Solution

             It is an older solution, very complicated to configure and maintain
             Uses a publisher-subscriber model
             Publisher distributes data to one or more secondary servers
             Published data can be filtered. You can specify exactly which data you would like to

Difference between Log Shipping and Database Mirroring
                   Log shipping                                   Database Mirroring
1. Failover is manual                            1. Failover is automatic & Manual as well.
2. Can have multiple copies of the same database 2. Can have only one copy of the database
3. Can be used as Read-Only for reporting        3. Cannot be used as Read-Only unless you make a
purposes                                         snapshot of the database
4. Allows delays in applying logs. Default is 15 4. It applies right way – No Delay
minutes delay. It has advantages and
Example of advantages, if there is any problem   Example of advantages,
with data, it may not be on the standby database
Example of disadvantages, if there is an
application running on the standby server to     Example of disadvantages,
generate report, you may not 100% true data
5. It may not be 100% identical                  5. It is very much 100% identical

Why you should select one option over other option of High Availability:
  1. Budget: Clustering is really expensive in terms of hardware, configuration, and maintenance
  2. Acceptable downtime:
  3. Data Risk: How much data lose we can afford


To top