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
Credits
Foreword
About the Author
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface

Chapter 2: Implementing Clustering


Common troubleshooting — installation

Any time during setup if we encounter an error, we have to locate the installation summary file to troubleshoot the installation error. This summary file is located at %programfiles%\Microsoft SQL Server\100\bootstrap\log.

Network binding order

This error/warning is observed most often while installing failover cluster. To overcome this problem, we should correct the network order before installing failover cluster. The following are steps to perform that:

  1. 1. Go to Start | Control Panel | Network and Internet | Network and Sharing Center.

  2. 2. Click Change Adapter Setting on the left menu.

  3. 3. Press Alt + F and the Advanced menu will be visible to us.

  4. 4. Click Advanced settings; this will bring the dialog box wherein we can change the order of the network card.

  5. 5. Move the network card up on which we have configured the public network.

Problem while adding a second node

While installing a second node, we may face the error that may block our path to add a second node to the cluster.

We may face the error message Failover instance MSSQLServer cluster group not found, when the SQL Server Agent is not the part of cluster. To overcome this error and to proceed further successfully, we should manually change the registry by following Wael A. Kabli's blog entry: http://blogs.msdn.com/wael/archive/2009/12/07/cannot-add-a-second-node-to-an-sql-server-2008-failover-cluster.aspx.

  • Current SKU is invalid:

    While adding a second node, this error might show up; however, this was fixed with the first release of Cumulative Fix 50003415. Although there is another work around for this, if we are installing from the media that comes with Product Key embedded in it, then perform the following steps:

    1. 1. Dump the CD/DVD setup on the local disk.

    2. 2. Locate defaultsetup.ini.

    3. 3. Cut and paste this file to some other location.

    4. 4. Rerun setup.exe from the local disk.

  • All cluster disks available to this virtual server are owned by other node(s):

    This error comes when the cluster disks are owned by the other node; to resolve this error we should move the disk to the local node.

Common troubleshooting and tips — resources

Although DBAs always keep an eye on the Windows Event Viewer and SQL Server Error Log to monitor what is going on with the SQL Box, I would like to list some of the most common errors seen with Clustering.

  • Error: SQL Server cannot log on to the Network after it migrates to another node as it is not able to contact the domain controller.

    Resolution: We should check if there is a problem with the trust relationship with the cluster node and domain controller. The other reason could be the network congestion or DNS.

  • Error: SQL Server can't access the cluster disks.

    Resolution: Check if the firmware and drivers for the disks are the same on both the nodes.

  • Error: Failure of SQL Server Services or SQL Server Agent Services.

    Resolution:There could be several reasons for this, but the most common ones are:

    • Check if the password has been changed recently but not updated in services property.

    • The Services account is locked out or disabled.

    • Sometimes Network/Security Administrator re-applies the NTFS permission on the MS SQL Server folder after the audit, and some-times it wipes out the existing permission. We have to see if the services account has the proper permission on the MS SQL Server folder and subfolders.

    • Sometimes it returns errors that are related to protocols initialization error. Here are some links to resolve this issue (although these are for SQL Server 2005 it should work for SQL Server 2008):

      http://support.microsoft.com/kb/956378

      http://blogs.msdn.com/sql_protocols/archive/2007/02/01/tdssniclient-initialization-failed-with-error-0x50-status-code-0x50.aspx

      http://blogs.msdn.com/sql_protocols/archive/2006/01/10/511330.aspx

  • Error: Changing the IP address of Failover SQL Server cluster returned error.

    Resolution:The resolution is simple. What we should do is bring the SQL IP address and SQL network name offline, and then change the IP address and again bring the SQL IP address and SQL network online.

  • Error: The IP address is already in use.

    Resolution:This error explains itself; the IP address we are trying to assign is already in use and hence we should procure another IP address and then assign.

  • Error: The SQL Server failover cluster instance name could not be found as cluster resources.

    Resolution:Check whether the registry value for <instld>\Cluster\ClusterName is correct.