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 4: Transactional Replication

  • Question: I have two subscriptions configured and one of them is not working, what should I check?

    Answer:We should check if the distribution agent is running, and check if there are any errors reported. If required try to reinitialize subscription and use replication monitor to watch what is happening.

  • Question: I get the following error: Insert failed because of conflict with identity range check constraint. How do I solve it?

    Answer: We should be careful when we use Identity with replication. Ideally we should manually assign the range at both the ends — Publisher and Subscriber — if we are using updatable subscription. Also, it is to be noted that Identity keys and triggers should be marked as Not for Replication.


    Here are the URLs of two good articles on managing Identity values in replication: and

  • Question: OLEDB provider "SQLNCLI" for linked server "XXXX" returned message as No transaction is alive (.Net SqlClientData provider).

    Answer: We need to make sure that we have configured SQL Server Agent to run using domain account and not "local system" or "network services" account, as this account would have different SID on each machine and they would not be able to access common network resources.

  • Question: Sometime I see an error "Subscription(s) have been marked as inactive and must be reinitialized, NoSync subscriptions will need to be dropped and recreated". How do I solve it?

    Answer: In most cases, re-intializing will work, and if not, query the system table Mssubscriptions and note down the record for which status = 0. If you find such an entry, execute a code mentioned here:

    update distribution..MSsubscriptions set status = 2 where publisher_id = 'VALUE'
    publisher_db = 'SSCitationSR'
    publication_id = 'VALUE'
    subscriber_id = 'VALUE'
    subscriber_db = 'DB_NAME'


    Note: This should be executed in case of the last option as this modifies the entry in the system table. As a first aid, we should try to a make fake update so that the initialization will get triggered or check if modifying the retention period can be of any help. If this option doesn't work, re-initialize the subscription.

  • Question: I get the following error message: Log Reader Agent failed to construct a replicated command from Log Sequence Number (LSN). How do I resolve it?

    Answer:Query the sysarticles table at the Publisher server and note down the name of the article if it shows value missing in the column filter. Note down the article name, remove it from the publication, and then re-add them. Once you re-add them, re-initialize the snapshot and it should work fine.