If your boss wants you to make your SQL Server more reliable, there’s a lot of confusing options. I’ll walk you through the pros and cons of each method and show you how they can help make queries go faster too. We’ll finish with a list of books and blogs I’d recommend for each option.
This 30-minute session is for production DBAs who haven’t implemented clustering, replication, or log shipping before, and aren’t sure where to start.
Clustering Resources
Clustering had a bad reputation for being difficult to configure and manage, but Microsoft’s made dramatic improvements in recent versions of both Windows and SQL Server. Whether you’re just getting started with clustering for the first time, or you still think clusters require heartbeat networks, your first resource should be Pro SQL Server 2008 Clustering by Allan Hirt (@SQLHA).
Database Mirroring Resources
For the first couple of years that I used Database Mirroring, I thought it was brain-dead-simple: just Next-Next-Next your way through a wizard, and you’re in business. I couldn’t imagine how anyone could write an entire book about mirroring. That, however, was before I found out about Pro SQL Server 2008 Mirroring, which I’d definitely recommend. The book’s coauthors are Robert Davis (@SQLSoldier) and Ken Simmons (@KenSimmons).
For some quick free learning, check out Robert’s posts tagged with database mirroring.
Replication Resources
Replication’s been out for several SQL Server versions now, and it’s slowly changed with each version. As a result, there’s a ton of good free info out there, but it’s not all up-to-date. My favorite place to start is the Books Online topic for Designing and Implementing Replication, and then move on to:
- Simple Talk Replication Crib Sheet
- SQL Server Central Stairway to Replication
- BOL: Replication and Database Mirroring – Replication adds more moving parts to your infrastructure, and it can end up *less* reliable with so many points of failure. Mirroring helps mitigate those risks.
Log Shipping Resources
Log shipping is more of a technique than a technology: it just means restoring your backups continuously on another server. When database administrators first started doing this, they did it with their own scripts. Third party backup compression tools like LiteSpeed started offering cool easy-to-manage user interfaces for it too.
These days, the log shipping and backup compression built into SQL Server 2008 R2 Standard Edition put log shipping within everybody’s reach. The easiest place to get started is with the Books Online section on Log Shipping. If you use a third party backup compression tool, poke around in their documentation too.
SAN/VM Replication Resources
These solutions are all vendor-specific, and unfortunately a lot of vendors lock their documentation behind support sites that require logins. However, there’s one very good place to start: Mike Laverick’s free ebook Administering VMware Site Recovery Manager 4. This isn’t the most current information available, but don’t worry about that – just read Chapter 1 for an excellent vendor-neutral introduction to disaster recovery planning using SAN and VMware replication.
If your SAN happens to be covered in his book, skim through that chapter too just to get an idea of the difficulty level for these solutions. These don’t have the ease-of-use of, say, SQL Server log shipping, but it’s one pane of glass to manage all of your disaster recovery needs regardless of the operating system and application.
For more learnin’, check out Veeam’s free whitepaper section. Veeam is a vendor that sells their own virtualization backup and recovery software, and I’m not affiliated with ‘em or getting paid to say this, but I’m a big fan of vendors that give away free education to the community.
AlwaysOn Availability Groups Resources
This is a brand new feature of SQL Server 2012, so we don’t have a lot of good community resources out there yet. There’s a few screenshot walkthroughs, but they don’t really tell you anything you can’t figure out in five minutes of playing around. For now, the place to go is the Books Online section on Availability Enhancements.
Brent Ozar
Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.
Follow Me:
I love this video. I just sent a link to this to a client, and couldn’t help watching it again myself.
Sorry for all the pro-replication propaganda at the end. I’m a replication apologist at times!