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

Chapter 7: Log Shipping

There are few errors we may encounter with Log Shipping and most of them can be easily resolved. To get them resolved, we can get help from job history. Let's see this with the help of an example.

Frequently asked questions

  • Question 1: Why do we get the following error?

    Log Shipping is Out of Sync

    We have to dig the SQL Server Error Log and Job History to get more information. Follow these steps to troubleshoot the error:

    1. 1. Expand the Job Activity Monitor, right-click on the job, and click View history.

    2. 2. As we can see in the error message here, it says that the T-Log restore job has failed to restore the T-Log and is out of sync for the last 59 minutes.

    3. 3. We have to carefully see what is causing this job to trigger the error, as this error caused by restoration hasn't occurred for 59 minutes. Let's check the backup job, to see if the backup job was performed successfully before. When checking job history of the backup job, I noticed that the backup job failed because it could not find the network path where we have kept the backup files (see the following screenshot).

    4. 4. The next course of action is to check what has caused the network error. Some of the possible reasons could be:

      • Somebody from the system admin team has wiped out the NTFS permission

      • The folder that was shared by us is now unshared

      • The system has lost the network connection

      • Hardware failure (either disk or network)

    These are just an example and there could be some other possible reasons too. We have to carefully analyze them to troubleshoot the error.

  • Question 2: I get the following error:

    The log in this backup set begins at LSN xxxxxxxxxxxxxxxx, which is too late to apply to the database. An earlier log backup that includes LSN xxxxxxxxxxxxxxxx can be restored.

    Or, the following error at times:

    Error: The log in this backup set begins at LSN xxxxxxxxxxxxxxxx, which is too early to apply to the database. An earlier log backup that includes LSN xxxxxxxxxxxxxxxx can be restored.

    Answer: The main reason for this error to come up is the T-Log getting truncated. There are a few steps we need to perform to resolve this error.

    1. 1. We have to carefully check the last log backup file that was restored on the Secondary Server.

    2. 2. Disable all the Log Shipping jobs.

    3. 3. Check if the immediate backup file is there in the backup folder. If it is, restore that last backup file with the "No Recovery" or "Standby" option.

    4. 4. Enable all the Log Shipping jobs.

      Even the following error is possible:

      Could not apply log backup file "path\file" to secondary database "db", exclusive access could not be obtained because the database is in use.

      The error is self explanatory. It says that while applying a T-Log backup, the SPID could not obtain the exclusive access, which is required to restore database/log backup.

      Answer: We should check the disconnect users in the database when restoring backups option. This will disconnect the users while restoring the backup file.

  • Question 3: My primary server goes down. How can I make my secondary database as primary?

    Answer: There are a few steps we have to perform manually to make secondary database a primary one.

    1. 1. Check if we can have Tail Log backup of the primary database.

    2. 2. If we are able to take Tail Log backup, apply the tail log backup with Recovery option while restoring T-Log backup.

    3. 3. If the primary database doesn't allow us to take backup, execute the following command:

      restore database <dbname> with recovery
    4. 4. We also need to ensure that the logins and other metadata are in sync with the secondary database.