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 5: Merge Replication


Index on columns used in filters

It is important to create index on the columns that are used for filtering and joins so as to perform faster retrieval of data. Whenever merge agent runs, it searches for the data, if database engine finds an index on the columns that are used for filter and join it can make the retrieval faster, otherwise it would have to search through the table.

Note

Reference article on index:

I would strongly recommend reading the article on index(s) available at http://technet.microsoft.com/en-us/library/cc966523.aspx. This is the best ever article I have read on index(s); although for SQL Server 2000 it is equally true and applicable for any other version of SQL Server. The following are some more articles that could be of assistance:

  • http://blogs.technet.com/josebda/archive/2009/03/17/indexing-best-practices-for-sql-server-2008.aspx

  • http://research.microsoft.com/pubs/115263/ICDE10_conf_full_294.pdf

  • http://sqlblogcasts.com/blogs/ssqanet/archive/2008/02/19/sql-server-2005-index-optimization-best-practices.aspx

  • http://sqlserverpedia.com/blog/sql-server-bloggers/my-top-5-sql-server-indexing-best-practices/

LOB data types

Microsoft recommends that we should separate the columns that have LOB as data type to another table using a one-to-one relationship. This would help us reduce the overhead on performance.

Avoid using identity column as primary key

There has been a lot said about using identity as primary key in replicated database. The primary issue behind this is the manageability — every time identity value reaches its threshold value, we will have to reseed the identity value and adjust identity range at Publisher end; this happens when we restore backup at Subscriber end. We will have to find the last inserted identity value and adjust the identity range at Publisher using the following steps:

/* Step 1: Execute below T-SQL for every subscriber
*/
selectident_current('tblName')
go
/* Step 2 :Note down the highest value
found from subscribers
*/
/*
Step 3 : Execute below T-SQL at publisher
*/
dbcc checkident('tblName',reseed,value+1)
-- Note: value = heighest value found in step 1
/*
Step 4: At publisher execute below T-SQL
*/
executesp_adjustpublisherIdentityrange PublicationName, tblName

Note

Here is an article on my blog for reference, suggesting why not to use identity as PK: http://www.sql-server-citation.com/2009/12/common-mistakes-in-sql-server-part-2.html.

Frequently asked questions

  • Question: I get the following message: Violation of Primary Key. And now I am unable to add a new value. So, how do I resolve it?

    Answer: As we are all aware, the primary key violation may occur only if there is a conflict of key value, and we should be careful while troubleshooting this error. Typically, we should check the following:

    • If the identity column is used, whether it is set to the "not for replication" option

    • Whether the identity management is auto or manual and what is the range specified

    • Is there any schema difference in any of the Subscriber and/or with Publisher

    • Check whether the duplicate record exists, and if yes, act accordingly

    There are so many reasons depending on the situation, but I would certainly recommend referring to an article by Hilary Cotter, available at http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/.

  • Question: Sometimes I notice the following error: The Schema script 'some file name' could not propagated to the subscriber (Error number: -2142010001) or OS Error 2 or System cannot find the file specified. Why is it so?

    Answer: Ensure that the agent account responsible to run the merge account has the required (read, list and write) share and NTFS permission to the snapshot folder.

  • Question: Why does Merge Agent or Distribution Agent keep on getting timeout error?

    Answer: To overcome the problem, go to Agent Properties and change the value of QueryTimeOut parameter as shown in the following screenshot:

    Note

    This is what we generally do, but there could be other factors that may have caused these errors such as number uploads size of row, the latency, or the network congestion. I recommend analyzing any error thoroughly before applying a solution.