You are previewing Microsoft SQL Server 2008 High Availability.
O'Reilly logo
Microsoft SQL Server 2008 High Availability

Book Description

This is the complete guide to keeping your SQL server applications up and running at all times. You’ll learn how to minimize downtime, speed up recovery, and achieve the highest levels of availability and reliability.

  • Install various SQL Server High Availability options in a step-by-step manner

  • A guide to SQL Server High Availability for DBA aspirants, proficient developers and system administrators

  • Learn the pre and post installation concepts and common issues you come across while working on SQL Server High Availability

  • Tips to enhance performance with SQL Server High Availability

  • External references for further study

  • In Detail

    Every business has it's mission critical applications and data. Therefore, it is very important to keep database servers up and running all the time – 24 X 7. There is a need to store and process terabytes of data to cater for business needs, and it is vital to make data highly available.

    High availability is all about the site being accessible all the time. High availability solutions minimize the downtime for these mission critical applications.

    Microsoft SQL Server is a powerful relational database engine, widely used to store and maintain data in Enterprises of various levels be they small, medium or large.

    This book will teach you how best to use these readily-available options and save you time in making your websites highly available.

    This Microsoft SQL Server 2008 High Availability book will take you through pre and post installation concepts and common issues you come across while working with SQL Server HA. It will teach you how these various HA solutions can be installed using GUI and the command line. It will also show you how to troubleshoot common issues you may encounter whilst installing or managing the HA option in your environment. It provides references to external links for more advanced learning on the topic.

    This book starts with an introductory chapter into the windows domain, domain users and various handshake methods available with Windows server. It also offers information the different authentication methods available with SQL Server - giving you an insight into the importance of security. After you are through with the security aspects, your journey to installing SQL Server HA will start. It will cover the concepts of database mirroring, log shipping, clustering, and replication.

    By the end of this book you will be confident enough to take up the challenge to install any of the SQL Server HA options.

    A step-by-step guide to SQL Server HA for DBA aspirants, proficient developers and system administrators

    Table of Contents

    1. Microsoft SQL Server 2008 High Availability
      1. Microsoft SQL Server 2008 High Availability
      2. Credits
      3. Foreword
      4. About the Author
      5. Acknowledgement
      6. About the Reviewers
        1. Support files, eBooks, discount offers and more
          1. Why Subscribe?
          2. Free Access for Packt account holders
      8. Preface
        1. What this book covers
        2. What you need for this book
        3. Who this book is for
        4. Conventions
        5. Reader feedback
        6. Customer support
          1. Errata
          2. Piracy
          3. Questions
      9. 1. Understanding Windows Domains, Domain Users, and SQL Server Security
        1. Windows domains and domain users
        2. Understanding SQL Server Security
          1. Windows authentication
        3. What is clustering?
        4. How clustering works
        5. MSDTC
        6. Types of clusters
          1. Single-instance Cluster
          2. Multi-instance Cluster
          3. Multi-site Failover Cluster
          4. Guest Failover Clustering
        7. Components of clustering
          1. Shared disk array
            1. Internet Small Computer System Interface
            2. Storage Area Network (SAN)
            3. Redundant Array of Independent Disks
        8. The Quorum
        9. Public and Private Network
        10. Summary
      10. 2. Implementing Clustering
        1. What is Hyper-V?
        2. Installation prerequisites for SQL Server 2008 R2
          1. Hardware requirements
          2. Software requirements
          3. Operating system requirements
          4. Server role and feature
          5. IP addresses
          6. Network name
          7. Shared disks
        3. Installing a Single-instance Failover Cluster
          1. Adding a node to an SQL Server failover cluster
        4. Installing the SQL Server failover cluster using the command prompt
        5. Installing SQL Server failover cluster using the Configuration file
        6. Installing Multi-instance (Active/Active) Failover Cluster
          1. Adding a node to a SQL Server failover cluster
        7. Configuration
          1. Write caching
          2. File shares
          3. Maximum memory size
          4. Minimum memory size
          5. Preferred owners
          6. LooksAlive
          7. IsAlive
          8. Restart
          9. Affect the Group
          10. Dependencies
          11. File share
        8. Anti Virus tool
        9. Summary
      11. 3. Snapshot Replication
        1. Components of the replication
        2. Types of replication
          1. Snapshot Replication
          2. Transactional Replication
          3. Merge Replication
          4. Peer-to-Peer Replication
        3. Replication topologies
        4. How replication works
        5. Prerequisites for Snapshot Replication
        6. Installing
          1. Configuring Distributor and Publisher
          2. Creating publication
          3. Creating subscription
        7. Configuring Snapshot Replication
        8. Snapshot Agent
        9. Distribution Agent
        10. Maintenance jobs
          1. Agent history clean up: distribution
          2. Distribution clean up: distribution
          3. Expired subscription clean up
          4. Reinitialize subscriptions having data validation failures
          5. Replication agents check-up
          6. Replication monitoring refresher for distribution
        11. Configuring publication properties
          1. Configuring alerts
          2. Snapshot Agent parameters
          3. Adding and dropping an article and a column to the existing publication using T-SQL
        12. Summary
      12. 4. Transactional Replication
        1. Prerequisites for Transactional Replication
          1. Working with Transactional Replication
        2. Installing Transactional Replication
          1. Creating new publication for Transactional Replication
          2. Creating subscription for Transactional Replication
        3. Installing publication and subscription using T-SQL
        4. Configuring Transactional Replication
          1. Parameters to be configured with Distribution Agent profile
        5. Summary
      13. 5. Merge Replication
        1. Merge Replication usages
        2. How Merge Replication works
        3. Prerequisites for Merge Replication
        4. Installing Merge Replication
          1. Creating publication
          2. Creating subscription
        5. Configuration
          1. Configuring publication using T-SQL
          2. Configuring the Subscriber using T-SQL
          3. Creating alternate synchronization partner using T-SQL
          4. Dropping an alternate synchronization partner using T-SQL
          5. Uninstalling subscription
          6. Configuring download-only articles
          7. Configuring alerts using SSMS
        6. Summary
      14. 6. Peer-to-Peer Replication
        1. What is Peer-to-Peer Replication?
        2. How Peer-to-Peer Replication works
        3. Prerequisites for Peer-to-Peer Replication
        4. Installing Peer-to-Peer Replication
        5. Configuration
          1. Configuring alerts
          2. Removing a node from Peer-to-Peer Replication
        6. Adding SQL Server 2005 as a Peer-to-Peer node
        7. Summary
      15. 7. Log Shipping
        1. What is Log Shipping?
        2. How Log Shipping works
        3. Prerequisites for Log Shipping
          1. Installing Primary Server
          2. Setting up the secondary database
        4. Setting Monitor Server instance
        5. Configuration
          1. Setting up Primary Server for Log Shipping
          2. Setting up Secondary Server
        6. Summary
      16. 8. Database Mirroring
        1. What is Database Mirroring
        2. Different components of the Database Mirroring
        3. How Database Mirroring works
          1. Asynchronous a.k.a. High Performance mode
          2. Synchronous a.k.a. High Safety mode
        4. Prerequisites
        5. Installing Database Mirroring
          1. Preparing for Database Mirroring
          2. Installing Database Mirroring
          3. Starting Database Mirroring
          4. Manual or forced failover
          5. Adding the Witness Server
        6. Configuration using T-SQL
          1. Monitoring the Database Mirroring status using Database Mirroring Monitor
          2. Configuring the threshold for Database Mirroring
        7. Summary
      17. A. Troubleshooting
        1. Chapter 2: Implementing Clustering
          1. Common troubleshooting — installation
            1. Network binding order
            2. Problem while adding a second node
          2. Common troubleshooting and tips — resources
        2. Chapter 3: Snapshot Replication
          1. Troubleshooting using Replication Monitor (RM)
        3. Chapter 4: Transactional Replication
        4. Chapter 5: Merge Replication
          1. Index on columns used in filters
          2. LOB data types
          3. Avoid using identity column as primary key
          4. Frequently asked questions
        5. Chapter 6: Peer-to-Peer Replication
        6. Chapter 7: Log Shipping
          1. Frequently asked questions
        7. Chapter 8: Database Mirroring
      18. B. External References
        1. Chapter 1: Understanding Windows Domain, Domain Users, and SQL Server Security
        2. Chapter 2: Implementing Clustering
        3. Chapter 3: Snapshot Replication
        4. Chapter 4: Transactional Replication
        5. Chapter 5: Merge Replication
        6. Chapter 6: Peer-to-Peer Replication
        7. Chapter 7: Log Shipping
        8. Chapter 8: Database Mirroring