Microsoft SQL Server 2008 high availability

✍ Scribed by Goswami, Hemantgiri S.; Haines, Adam; Vohra, Deepak

• Publisher: Packt Publishing; Books24x7.com
• Year: 2011
• Tongue: English
• Leaves: 308
• Category: Library

✦ Synopsis

If you are an SQL Server developer, system administrator, or even novice DBA, this book is for you. You only need a basic understanding of how SQL Server works to get you through the installation of SQL Server HA. With clear step-by-step instructions, real-world examples, and plenty of screenshots, this book provides all the support and guidance you will need as you install SQL Server HA options. It starts with an introductory chapter about the Windows domain, domain users, and the handshake methods available with Windows server and covers the different authentication methods available with SQL Server, giving you an insight into the importance of security. Then your journey to installing SQL Server HA starts, covering the concepts of database mirroring, log shipping, clustering, and replication. One appendix covers troubleshooting common issues you may encounter while installing or managing the HA options in your environment; a second appendix provides references to external resources.

✦ Table of Contents

Cover......Page 1
Copyright......Page 3
Credits......Page 4
Foreword......Page 5
About the Author......Page 7
Acknowledgement......Page 8
About the Reviewers......Page 9
www.PacktPub.com......Page 11
Preface......Page 18
Chapter 1: Understanding Windows Domains, Domain Users, and SQL Server Security......Page 24
Windows domains and domain users......Page 25
Windows authentication......Page 27
What is clustering?......Page 30
How clustering works......Page 33
MSDTC......Page 34
Single-instance Cluster......Page 36
Multi-instance Cluster......Page 37
Multi-site Failover Cluster......Page 38
Components of clustering......Page 40
Shared disk array......Page 41
The Quorum......Page 43
Public and Private Network......Page 47
Summary......Page 48
Chapter 2: Implementing Clustering......Page 50
What is Hyper-V?......Page 51
Software requirements......Page 52
IP addresses......Page 53
Shared disks......Page 54
Installing a single-instance failover cluster......Page 55
Adding a node to an SQL Server failover cluster......Page 77
Installing the SQL server failover cluster using the command prompt......Page 82
Installing SQL Server failover cluster using the Configuration file......Page 84
Installing multi-instance (Active/Active) failover Cluster......Page 88
Adding a node to a SQL Server failover cluster......Page 100
Configuration......Page 105
Preferred owners......Page 106
File share......Page 107
Summary......Page 108
Chapter 3: Snapshot Replication......Page 110
Components of the replication......Page 111
Snapshot Replication......Page 112
Merge Replication......Page 113
Replication topologies......Page 114
How replication works......Page 115
Prerequisites for Snapshot Replication......Page 116
Installing......Page 117
Configuring Distributor and Publisher......Page 118
Creating publication......Page 123
Creating subscription......Page 133
Snapshot Agent......Page 142
Agent history clean up: distribution......Page 143
Replication monitoring refresher for distribution......Page 144
Configuring publication properties......Page 145
Configuring alerts......Page 148
Snapshot Agent parameters......Page 150
Adding and dropping an article and a column to the existing publication using T-SQL......Page 151
Summary......Page 153
Chapter 4: Transactional Replication......Page 154
Prerequisites for Transactional Replication......Page 155
Working with Transactional Replication......Page 156
Creating new publication for Transactional Replication......Page 157
Creating subscription for Transactional Replication......Page 161
Installing publication and subscription using T-SQL......Page 167
Configuring Transactional Replication......Page 169
Parameters to be configured with Distribution Agent profile......Page 170
Summary......Page 172
Merge Replication usages......Page 174
Prerequisites for Merge Replication......Page 175
Creating publication......Page 178
Creating subscription......Page 184
Configuring publication using T-SQL......Page 190
Configuring the Subscriber using T-SQL......Page 192
Creating alternate synchronization partner using T-SQL......Page 194
Uninstalling subscription......Page 195
Configuring download-only articles......Page 196
Configuring alerts using SSMS......Page 197
Summary......Page 201
Chapter 6: Peer-to-Peer Replication......Page 202
How Peer-to-Peer Replication works......Page 203
Prerequisites for Peer-to-Peer Replication......Page 204
Installing Peer-to-Peer Replication......Page 205
Configuring alerts......Page 211
Removing a node from Peer-to-Peer Replication......Page 213
Adding SQL Server 2005 as a Peer-to-Peer node......Page 216
Summary......Page 219
Chapter 7: Log Shipping......Page 220
How Log Shipping works......Page 221
Prerequisites for Log Shipping......Page 222
Installing Primary Server......Page 223
Setting up the secondary database......Page 225
Setting Monitor Server instance......Page 230
Configuration......Page 231
Setting up Primary Server for Log Shipping......Page 232
Setting up Secondary Server......Page 235
Summary......Page 239
What is Database Mirroring......Page 240
Different components of the Database Mirroring......Page 241
Prerequisites......Page 242
Preparing for Database Mirroring......Page 243
Installing Database Mirroring......Page 246
Starting Database Mirroring......Page 253
Manual or forced failover......Page 256
Adding the Witness Server......Page 259
Configuration using T-SQL......Page 263
Monitoring the Database Mirroring status using Database Mirroring Monitor......Page 266
Configuring the threshold for Database Mirroring......Page 267
Summary......Page 270
Network binding order......Page 272
Common troubleshooting and tips—resources......Page 273
Chapter 3: Snapshot Replication......Page 275
Troubleshooting using Replication Monitor (RM)......Page 276
Chapter 4: Transactional Replication......Page 278
Index on columns used in filters......Page 280
Avoid using identity column as primary key......Page 281
Frequently asked questions......Page 282
Chapter 6: Peer-to-Peer Replication......Page 283
Frequently asked questions......Page 284
Chapter 8: Database Mirroring......Page 288
Chapter 2: Implementing Clustering......Page 290
Chapter 3: Snapshot Replication......Page 292
Chapter 4: Transactional Replication......Page 293
Chapter 5: Merge Replication......Page 294
Chapter 7: Log Shipping......Page 295
Chapter 8: Database Mirroring......Page 297
Index......Page 298