Docstoc

DB Mirroring for SharePoint

Document Sample
DB Mirroring for SharePoint Powered By Docstoc
					                 Welcome
* David E. Myers
 * DBA and Developer at the Upper Canada District
   School Board in Brockville
 * SQL Server MVP, MCDBA, MCSE, MCITP, MCT
 * OttawaSQL.net Founder
 * Kingston SQL Server User Group Leader
 * My Blog: www.DavidEMyers.com
 * Twitter: @DavidEMyers
               OttawaSQL.net
* A community of Ottawa area developers and IT Pros
* We focus on Microsoft’s data tier and BI technology
   * SQL Server
   * SQL Azure
   * SharePoint Business Intelligence
   * PerformancePoint
   * ADO.NET and Entity Framework
                • Professional Association for SQL Server (PASS) and Business Intelligence
                • Founded by Microsoft & CA in 1999
What is PASS    • Not-for-Profit, User-Run Organization




               • User-elected Board provides strategic direction, organizational management
               • Microsoft & CA provide executive level endorsement (Each contribute 2 representatives to the board)
               • Volunteers manage key roles in Special Interest Groups, Summit Program Committee, Technical
PASS Structure Publications, Education and Networking



                 • Over 25,000 SQL Server & Business Intelligence professionals worldwide
                 • Over 100 Regional Chapters
   PASS          • Distributed model with member communities in more than 24 countries
 Community
                     Agenda
* Lot’s of considerations… but this is only a
  75 minute session
  * Overview of SQL Server 2008 High Availability
  * SQL Server 2008 Database Mirroring Concepts
  * Implementing Database Mirroring for SharePoint
    to provide High Availability and Disaster Recovery
    capabilities
               High Availability
* How does the business define HA?
  *   What is the Recovery Time Objective (RTO)?
  *   What is the Recovery Point Objective (RPO)?
  *   What is the budget?
  *   What are the service level requirements?
  *   For which type of disaster scenario?
           High Availability
* How does a business user define HA?
Why can’t I access my project site!?!




    Flickr.com Creative Commons License: Laura604
 SQL Server 2008 High Availability
* What is SQL Server High Availability?
  * Percentage measure of time that the service and data is
    available (i.e. 99.9999%)
  * 2 major themes:
     * Prevention
     * Disaster Recovery
SQL Server 2008 High Availability
      Backup/Restore
      Database Snapshot
      Log Shipping
      Transactional Replication
      Failover Clustering
      Database Mirroring
                            Backup
• Permanent copy of data
• Online restore
   – Perform a restore operation while
     an instance of SQL Server is running
• Backup compression in SQL Server
  2008
   – Reduce volume by up to 50%
   – Allows reduced latency and faster
     restore
   – Applied per instance and per
     backup
• Cost effective - no specialist
  hardware is required
• Straightforward setup and
  administration
                    Backup
            Backup Media Mirroring



• Protection against lost or damaged backup
  media
  – All backup types can be mirrored (database, log,
    etc.)
  – Each device must be the same type
  – All devices must be present during Backup, but
    only one backup set is required for Restore
    Database Snapshot
•        Provides a read-only,
         consistent copy of database
•        Snapshot remains
                                           Client
         unchanged while database
         is modified
     –       Protects against user error
             and data corruption
•        Very fast to create
•        Preserves disk space
         –   “Copy-on-Write” technology
• Cost effective as no specialist
  hardware is required
• Straightforward setup and
  administration
                           Log Shipping
• Provides database redundancy
• Cost effective as no specialist hardware is
  required
• Straightforward setup and administration
• Running read operations such as reports
  on secondary server is permitted
    – Users are disconnected when log restore
      occurs
• Can maintain multiple secondary servers
• Optional Monitor server
    – Records history and status of
      backup/restore jobs
    – May be setup to raise alerts when jobs fail
           Transactional Replication
•   High performance – latency measured in seconds
•   Minimal load on the server
•   Cost effective as no specialist hardware is required
•   Straightforward setup and administration
•   Can be implemented at database or table level
•   Two types
    – Standard transactional replication
        • Easy to design, setup & manage
        • Subscriber (standby) can be used for reporting
    – Peer-to-peer transactional replication
        • Multi-master model; schema is identical on all sites
        • Supports distributed applications with data partitioning; enables load
          balancing
        • Does not handle conflicts; design to avoid/prevent conflicts
                      Replication Options
 Transactional Replication      Peer-to-Peer Replication
 Reporting + Redundancy         Query Scale Out + Redundancy

                                                        London
New York



                                         Boston

                                                        Shanghai
New Jersey




                               Seattle                  Tokyo
                 Failover Clustering
• Server hardware redundancy
   – Using a shared disk subsystem
   – Entire instance virtualized and fails
     over as a unit
   – Can include non-SQL Server
     resources
   – SQL Server 2008 no longer requires
     a drive letter for each instance
• Clustering can be combined with
  Database Mirroring, Log Shipping, or
  Replication
• Geographically Dispersed Failover
  Clustering provides protection even
  if the disk array fails
                  Failover Clustering
                SQL Server Editions
    Standard Edition           Enterprise Edition




2-node clusters only         As many nodes as the operating
                             system supports
                 Database Mirroring
•   Hot Standby protects against database or server failure
•   Provides a fault-tolerant database
•   Cost effective as no specialist hardware is required
•   Straightforward setup and administration
•   Automatic or manual failover
    – SQL Server 2008 does not require a database restart after manual
      failover
• Automatic, transparent client redirect ***
• No shared components; two separate copies of data
• SQL Server 2008 compresses the log stream from Principal to
  Mirror
Database Mirroring
Recovering suspect pages

• SQL Server 2008 uses a
  checksum to validate page
  writes
• Inconsistent pages can be
  recovered automatically
  from the mirror server

                              Principal   Mirror
SQL Server High Availability Licensing

License only active servers

• Passive servers do not require a license
• If a failover occurs a license is not required for 30
  days

Most cost effective high availability model
amongst leading database vendors
            SQL Server 2008 HA Summary
 Database Mirroring
    Primary disaster site for databases                          Replication
                                                                                Database
                                                                                Scale Out
 Log Shipping                                                                  For Queries

    Additional disaster sites for
     databases
    Logical Recovery                Clustering
                                                               Database Mirroring
                                                                                    Hot
 Replication                                                                       Standby

                                                  Production
    Database reporting and read                  Database
     scale out with redundancy                                   Log Shipping
                                                                                    Warm
                                                                                    Standby
 Clustering
    Local server redundancy                                   Log Shipping
                                                               with Restore Delay   Logical
 Always On Storage Partner Solutions                                               Recovery
                                                                                    Standby
    Site storage HA                               Backup

    Highest hardware reliability
                    Agenda
* Overview of SQL Server 2008 High Availability
* SQL Server 2008 Database Mirroring Concepts
* Implementing Database Mirroring for SharePoint to
  provide High Availability and Disaster Recovery
  capabilities
Database Mirroring
Asynchronous, high performance configuration
 Data is mirrored
  asynchronously
  outside of
  transactions
 Manual failover if     Client

  Principal server
  fails
 Automatic Client
  redirection
 Not a good option
  for all SharePoint          Principal   Mirror
  databases
Database Mirroring
Synchronous, high protection configuration
 Data is mirrored
  synchronously as
  part of a
  transaction
 Manual failover if      Client

  Principal server
  fails
 Automatic Client
  redirection
 Might be your
  best option for              Principal     Mirror
  SharePoint 2007
Database Mirroring
Synchronous, high availability configuration
 Data is mirrored
  synchronously as
  part of a
  transaction
 Automatic                Client
  failover if                               Witness
  Principal server
  fails
 Automatic Client
  redirection
 Might be your
                                Principal             Mirror
  best option for
  SharePoint 2007
 Best option for
  SharePoint 2010
                    Agenda
* Overview of SQL Server 2008 High Availability
* SQL Server 2008 Database Mirroring Concepts
* Implementing Database Mirroring for SharePoint to
  provide High Availability and Disaster Recovery
  capabilities
          Mirroring within a farm
* You can use mirroring within a farm, or across farm
  environments.
* Between farms, fewer databases can be mirrored
  and restored.
* Within a farm, mirroring can provide redundancy for
  all databases.
  * This scenario will be the focus of today’s session, for other
    scenarios see the white paper for details
         Mirroring within a farm
* Note: Mirroring within a farm provides database
  availability only—that is, when databases fail over,
  we assume that your front-end Web servers remain
  available.
Web, query and application server




                                                             Witness server (Optional)



Web, query and application server          SQL Server 1                                    SQL Server 2


                                                                    High
                                                                  availabilty
                                                                  mirroring
                                        Principal instance                                   Mirror instance
  Index and application server

                                           Content                                              Content

                                        WSS search                                           WSS search

                                         SSP search                                           SSP search

                                             SSP                                                 SSP

                                        SSP content                                          SSP content

                                    Central administration                               Central administration

                                        Configuration                                        Configuration
A Good Practice (Performance):
    64K NTFS Cluster Size
A Good Practice (Performance, HA):
        Multiple Volumes
A Good Practice (Performance, HA):
   Separate Data and Log Files
A Good Practice (Security):
 Domain Service Accounts
A Good Practice (Security):
Segregate Service Accounts
A Good Practice (Management):
    Use named instances for portability
A Good Practice (Management):
        Use DNS for portability
                 Recommendation:
Disable firewall while configuring, test, create rules, test again.
   A Good Practice (Security):
Kerberos Authentication (SQL Client Authentication)
 A Good Practice (Security):
Disable SQL Browser Service
  A Good Practice (Security):
Set a fixed, high numbered port
   A Good Practice (Security):
Create SQL Native Client Aliases (both 32 and 64 bit)
        A Good Practice (Security):
Set SA password, then switch to Windows Only Authentication
  A Good Practice (Security):
Use a Domain Security Group for DB Admins
      A Good Practice (Security):
Use AES encryption for your database mirroring end points
   A Good Practice (Security):
Kerberos Authentication (Endpoint Authentication)
A Good Practice (Performance):
   Segregate Network Traffic
  A Good Practice (Performance):
x64 Platform for more DBM Sessions
Required for SharePoint 2007:
 Latin1_General_CI_AS_KS_WS Collation
                  Resources
* White Paper:
* Using Database Mirroring with Office SharePoint
  Server and Windows SharePoint Services
* http://go.microsoft.com/fwlink/?LinkId=83725&clcid
  =0x409
                 Resources
* White Paper:
* Scaling SharePoint 2007: Storage Architecture
* http://www.knowledgelake.com/whitepaper/Scaling
  %20SharePoint%202007%20-
  %20Storage%20Architecture.pdf

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:8/26/2011
language:English
pages:50