Microsoft SQL Server 2008 High Availability

Chapter 1: Understanding Windows Domains, Domain Users,

and SQL Server Security 7

Windows domains and domain users 8

Understanding SQL Server Security 10

Windows authentication 10

What is clustering? 13

How clustering works 16

MSDTC 17

Types of clusters 19

Single-instance Cluster 19

Multi-instance Cluster 20

Multi-site Failover Cluster 21

Guest Failover Clustering 23

Components of clustering 23

Shared disk array 24

The Quorum 26

Public and Private Network 30

Summary 31

Chapter 2: Implementing Clustering 33

What is Hyper-V? 34

Installation prerequisites for SQL Server 2008 R2 35

Hardware requirements 35

Software requirements 35

Operating system requirements 36

Server role and feature 36

IP addresses 36

Network name 37

Shared disks 37

Installing a Single-instance Failover Cluster 38

Adding a node to an SQL Server failover cluster 60

Installing the SQL Server failover cluster using the command prompt 65

Installing SQL Server failover cluster using the Configuration file 67

Installing Multi-instance (Active/Active) Failover Cluster 71

Adding a node to a SQL Server failover cluster 83

Configuration 88

Write caching 89

File shares 89

Maximum memory size 89

Minimum memory size 89

Preferred owners 89

LooksAlive 90

IsAlive 90

Restart 90

Affect the Group 90

Dependencies 90

File share 90

Anti Virus tool 91

Summary 91

Chapter 3: Snapshot Replication 93

Components of the replication 94

Types of replication 95

Snapshot Replication 95

Transactional Replication 96

Merge Replication 96

Peer-to-Peer Replication 97

Replication topologies 97

How replication works 98

Prerequisites for Snapshot Replication 99

Installing 100

Configuring Distributor and Publisher 101

Creating publication 106

Creating subscription 116

Configuring Snapshot Replication 125

Snapshot Agent 125

Distribution Agent 126

Maintenance jobs 126

Agent history clean up: distribution 126

Distribution clean up: distribution 127

Expired subscription clean up 127

Reinitialize subscriptions having data validation failures 127

Replication agents check-up 127

Replication monitoring refresher for distribution 127

Configuring publication properties 128

Configuring alerts 131

Snapshot Agent parameters 133

Adding and dropping an article and a column to the existing

publication using T-SQL 134

Summary 136

Chapter 4: Transactional Replication 137

Prerequisites for Transactional Replication 138

Working with Transactional Replication 139

Installing Transactional Replication 140

Creating new publication for Transactional Replication 140

Creating subscription for Transactional Replication 144

Installing publication and subscription using T-SQL 150

Configuring Transactional Replication 152

Parameters to be configured with Distribution Agent profile 153

Summary 155

Chapter 5: Merge Replication 157

Merge Replication usages 157

How Merge Replication works 158

Prerequisites for Merge Replication 158

Installing Merge Replication 161

Creating publication 161

Creating subscription 167

Configuration 173

Configuring publication using T-SQL 173

Configuring the Subscriber using T-SQL 175

Creating alternate synchronization partner using T-SQL 177

Dropping an alternate synchronization partner using T-SQL 178

Uninstalling subscription 178

Configuring download-only articles 179

Configuring alerts using SSMS 180

Summary 184

Chapter 6: Peer-to-Peer Replication 185

What is Peer-to-Peer Replication? 186

How Peer-to-Peer Replication works 186

Prerequisites for Peer-to-Peer Replication 187

Installing Peer-to-Peer Replication 188

Configuration 194

Configuring alerts 194

Removing a node from Peer-to-Peer Replication 196

Adding SQL Server 2005 as a Peer-to-Peer node 199

Summary 202

Chapter 7: Log Shipping 203

What is Log Shipping? 204

How Log Shipping works 204

Prerequisites for Log Shipping 205

Installing Primary Server 206

Setting up the secondary database 208

Setting Monitor Server instance 213

Configuration 214

Setting up Primary Server for Log Shipping 215

Setting up Secondary Server 218

Summary 222

Chapter 8: Database Mirroring 223

What is Database Mirroring 223

Different components of the Database Mirroring 224

How Database Mirroring works 225

Asynchronous a.k.a. High Performance mode 225

Synchronous a.k.a. High Safety mode 225

Prerequisites 225

Installing Database Mirroring 226

Preparing for Database Mirroring 226

Installing Database Mirroring 229

Starting Database Mirroring 236

Manual or forced failover 239

Adding the Witness Server 242

Configuration using T-SQL 246

Monitoring the Database Mirroring status

using Database Mirroring Monitor 249

Configuring the threshold for Database Mirroring 250

Summary 253

Appendix A: Troubleshooting 255

Chapter 2: Implementing Clustering 255

Common troubleshooting—installation 255

Network binding order 255

Problem while adding a second node 256

Common troubleshooting and tips—resources 256

Chapter 3: Snapshot Replication 258

Troubleshooting using Replication Monitor (RM) 259

Chapter 4: Transactional Replication 261

Chapter 5: Merge Replication 263

Index on columns used in filters 263

LOB data types 264

Avoid using identity column as primary key 264

Frequently asked questions 265

Chapter 6: Peer-to-Peer Replication 266

Chapter 7: Log Shipping 267

Frequently asked questions 267

Chapter 8: Database Mirroring 271

Appendix B: External References 273

Chapter 1: Understanding Windows Domain, Domain Users,

and SQL Server Security 273

Chapter 2: Implementing Clustering 273

Chapter 3: Snapshot Replication 275

Chapter 4: Transactional Replication 276

Chapter 5: Merge Replication 277

Chapter 6: Peer-to-Peer Replication 278

Chapter 7: Log Shipping 278

Chapter 8: Database Mirroring 280

Index 281