Book Image

Microsoft SQL Server 2008 High Availability

By : Hemantgiri S. Goswami
Book Image

Microsoft SQL Server 2008 High Availability

By: Hemantgiri S. Goswami

Overview of this book

<p>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.<br /><br />High availability is all about the site being accessible all the time. High availability solutions minimize the downtime for these mission critical applications.</p> <p>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.</p> <p>This book will teach you how best to use these readily-available options and save you time in making your websites highly available.<br /><br />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.<br /><br />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.<br /><br />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.</p>
Table of Contents (18 chapters)
Microsoft SQL Server 2008 High Availability
About the Author
About the Reviewers


The term High Availability means that the servers or systems that host or run the business-critical applications should be highly available 24 X 7. As the word itself defines how important it is to make these applications and data available for end-users as well as business users, if this data is not available for a short time, it will be a big problem for both sets of users. Imagine a bank spread across the country and having a huge customer base. One fine day, their server crashes! If the bank relies only on backups, then it might end up losing approximately 15 to 30 minutes of data, depending on the backup strategy. However, the HA options related to SQL Server such as clustering, replication, log shipping, and database mirroring will help overcome this situation.

By the end of the book, you will be able to find yourself in a position where you can easily install and configure the different High Available solutions for SQL Server. You will also be able to troubleshoot most common issues yourself by following the troubleshooting appendix.

What this book covers

Chapter 1, Understanding Windows Domains, Domain Users, and SQL Server Security, will help you understand what is Windows domain, what are domain users, and the basic security concepts for Windows and SQL Server to get yourself prepared for the next chapter.

Chapter 2, Implementing Clustering, will help you understand the prerequisites for SQL Server Clustering and guide you on how to install and configure SQL Server Cluster using both T-SQL and SSMS. The chapter also helps you on how to add or remove a node from an existing cluster.

Chapter 3, Snapshot Replication, will help you understand prerequisites for installing Snapshot Replication using SQL Server. It guides you in installing and configuring Snapshot Replication using both T-SQL and SSMS.

Chapter 4, Transactional Replication, will give you information on how to install and configure Transactional Replication. It also helps you understand how replication works and the different options available to configure and install Transactional Replication.

Chapter 5, Merge Replication, helps you install and configure Merge Replication. It also makes you understand the different components of Merge Replication, and how it works. It guides you on how to configure Merge Replication, using both T-SQL and SSMS.

Chapter 6, Peer-to-Peer Replication, explains how to install and configure Peer-to-Peer Replication, using both T-SQL and GUI. It also explains how to add or remove nodes.

Chapter 7, Log Shipping, describes what Log Shipping is, how it works, and what are the prerequisite components for its installation. The chapter also helps understand how to install Log Shipping using both T-SQL and SSMS.

Chapter 8, Database Mirroring, explains what Database Mirroring is all about, how it works, and what are the different components we need to implement it. We also learn different types of Database Mirroring and how to install and configure it using both T-SQL and SSMS.

Appendix A, Troubleshooting, contains the troubleshooting tips for the common issues faced in all of the previous chapters.

Appendix B, External References, contains the external references that we might need to refer, in order to gain further information on topics covered in all of the previous eight chapters.

What you need for this book

High Availability options can be used to make systems or servers highly available, so that the work isn't hindered in case of any emergency or failure of resources. So, to make your system and server capable of installing SQL Server 2008 High availability options such as clustering, replication, database mirroring, and log shipping, the following are the prerequisites:

  • Processor type: Pentium-3 or higher.

  • Processor speed: 1.0 Gigahertz or higher.

  • RAM: At least 512 MB, but 2 GB is recommended.

  • Display: VGA or higher resolution.

  • Operating system: Windows 7 Ultimate, Windows Server 2003 (x86 or x64) , Windows Server 2008 (x86 or x64).

  • Disk space: Minimum 1 GB.

  • .NET framework.

  • Windows Installer 4.5 or later.

  • Microsoft Data Access Component (MDAC) 2.8 SP1 or later. MDAC can be obtained from

For complete information on prerequisites, a reader can refer to the prerequisites section of each chapter.

You may refer to the external references for the further reading and can post back your queries to any SQL Server forums out their such as or

Who this book is for

If you are a SQL Server Developer, or a System Administrator, or even a novice DBA, then this book is for you. It requires you to have only a basic understanding of how SQL Server works to get you through the installation of SQL Server HA.


In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "We should set the value as true for the @stream_blob_columns of sp_addmergearticle parameter if there are LOB data types to publish."

A block of code is set as follows:

use [ReportServer$SQL2008R2]
@dbname =N'ReportServer$SQL2008R2',
@optname =N'merge publish',

Any command-line input or output is written as follows:


New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "In the Snapshot Agent dialog box, check the Create a snapshot immediately option to apply it to the subscriber immediately."


Warnings or important notes appear in a box like this.


Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book — what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to , and mention the book title via the subject of your message.

If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on or e-mail .

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.


Downloading the example code for this book

You can download the example code files for all Packt books you have purchased from your account at If you purchased this book elsewhere, you can visit and register to have the files e-mailed directly to you.


Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books — maybe a mistake in the text or the code — we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from


Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.


You can contact us at if you are having a problem with any aspect of the book, and we will do our best to address it.