Chapter 10: High Availability

Chapter 10: High Availability

Presented by Scott Whigham

1

p. 1

• Log Shipping • Database Mirroring

Clustering & Replication

• Overview of Chapter • What is High Availability?

Log Shipping & Mirroring

Overview

What We’re Going to Cover

• Clustering • Replication • Peer to Peer Replication

p. 2

What We’re Going to Cover

• Our final chapter is an overview of the various areas related to high availability – Log Shipping – Database Mirroring – Clustering – Peer-to-peer replication* • Not exactly “high availability” but often used in conjunction with the above

p. 3

High Availability

• High availability is a design that attempts to ensure users always have access to the database – Unplanned unavailability in some systems is not acceptable • Flight systems, emergency response teams, military operations

– Planned unavailability is allowed for maintenance

p. 4

High Availability

• Availability and uptime are not the same thing – A system can be up yet, if the network is down, it is not available

• Downtime could be a combination of things – – – –

Time spent identifying problem Time spent repairing problem Restore time Service or OS restarts

p. 5

High Availability

• To calculate total availability of a system, summarize: – Network downtime – Application downtime – Database downtime – Operating system downtime

p. 6

High Availability

• To increase a system’s availability – Decrease the amount of downtime – Decrease the frequency of downtime

p. 7

High Availability

• Applications and services often feature service level agreements (“SLAs”) that list minimum availabilities: – 90% availability means allowing 36.5 days of unplanned unavailability – 99% availability means allowing 3.65 days of unplanned unavailability – 99.999% (the “five nines”) availability means allowing ~00:05:15 of unplanned unavailability

p. 8

High Availability

• As a SQL Server DBA, your job is to ensure the availability and uptime of your – Server/service – Database(s)

p. 9

High Availability

• In this chapter, you will learn about various built-in techniques to help you achieve high availability – Some are expensive to implement – In all cases there are 3rd party solutions which may offer better alternatives – Often you will combine techniques to increase both uptime and availability • Example: A clustered instance with database mirroring

p. 10

In the next video

• Let’s get started with log shipping!

p. 11

Chapter 10: High Availability

Presented by Scott Whigham

1

p. 1

• Log Shipping • Database Mirroring

Clustering & Replication

• Overview of Chapter • What is High Availability?

Log Shipping & Mirroring

Overview

What We’re Going to Cover

• Clustering • Replication • Peer to Peer Replication

p. 2

Log Shipping Best Practices

• There are a few categories of Best Practices as relate to Log Shipping – Backup and Restore – Network – Security

p. 3

Log Shipping Best Practices

• Backup and Restore Best Practices for Log Shipping: 1. Give enough time for your backups to complete •

If you schedule backups every 5 minutes and a backup takes 7 minutes to complete, what happens to the next backup? –

Answer: The next backup will not take place until the next scheduled time

p. 4

Log Shipping Best Practices

• Backup and Restore Best Practices: 2. The more frequently you backup, the larger MSDB becomes •

Check up on sp_delete_backuphistory stored proc for clearing out msdb periodically

3. Use backup compression if you can •

Makes for faster backups, faster network copies, and faster restores - just make sure you have the CPU power to do it!

p. 5

Log Shipping Best Practices

• Backup and Restore Best Practices: 4. The more frequently you backup, the less possible data loss at the target •

SQL Server 2008 allows for as frequent as 10 seconds!

p. 6

Log Shipping Best Practices

• Network Best Practices for Log Shipping 1. Fast networks are good; slow networks are bad

p. 7

Log Shipping Best Practices

• Network Best Practices for Log Shipping 2. If you can’t do native backup compression (available in Enterprise only) and speed of file copy is an issue, consider using 3rd party compression • • •

It does no good to take backups every 5 mins if they take 15 mins to copy 7-Zip is an industry favorite despite it’s being “unsupported” Depending on the type of data, can compress 1:1, 5:1, 20:1 or more

p. 8

Log Shipping Best Practices

• Security Best Practices for Log Shipping 1. Secure all folders that participate in log shipping • •

Having an unauthorized person get to backups is terrible Having a sysadmin who is trying to “clean up disk space” delete critical backup files means you cannot restore

p. 9

Log Shipping Best Practices

• Security Best Practices for Log Shipping 2. Use your log shipping backups in your normal disaster recovery scenario •

•

When you take a log backup for log shipping, that is your log backup for your local disaster recovery plan as well Make sure you copy to the same location as your FULL/DIFF backups!

p. 10

Chapter 10: High Availability

Presented by Scott Whigham

1

p. 1

• Log Shipping • Database Mirroring

Clustering & Replication

• Overview of Chapter • What is High Availability?

Log Shipping & Mirroring

Overview

What We’re Going to Cover

• Clustering • Replication • Peer to Peer Replication

p. 2

Database Mirroring Best Practices

• There are a few categories of Best Practices as relate to Database Mirroring – Architecture – Network – Security is not generally in this list • All data transfer is encrypted if using same domain

p. 3

Database Mirroring Best Practices

• Architecture Best Practices for Database Mirroring: – “Architecture” is an all encompassing term that can include • • • •

Transaction safety levels Editions used Size of database(s) Usage scenario(s)

p. 4

Database Mirroring Best Practices

• Architecture Best Practices for Database Mirroring: 1. Transaction safety levels can influence the performance of database mirroring • Safety OFF (i.e. asynchronous mirroring) means that transactions are committed at principal without waiting for confirmation from witness/mirror – If transactions cannot be sent, it forms a queue at principal

• Safety FULL (synchronous mirroring) requires confirmation from mirror that the log has been hardened

p. 5

Database Mirroring Best Practices

• Architecture Best Practices for Database Mirroring: 1. Transaction safety levels can influence the performance of database mirroring • Because of how differently applications use a database, it is impossible to just say, “Always use Safety ‘X’ because it performs better” • Only one thing you can do: read the whitepaper! – http://technet.microsoft.com/library/cc917681.aspx

p. 6

Database Mirroring Best Practices

• Architecture Best Practices for Database Mirroring: 2. Use Enterprise edition for maximum configurability • Standard edition can only run Safety FULL transaction safety level – Safety FULL will have an impact on transaction throughput at the principal

• Thankfully log compression is enabled in both Standard and Enterprise editions – Log compression is a new feature in SQL 2008

p. 7

Database Mirroring Best Practices

• Architecture Best Practices for Database Mirroring: 3. Get creative when it comes to bulk imports • Database mirroring requires FULL recovery model • You can: – – – – –

Disable mirroring Perform the steps to bulk import Switch back to FULL recovery Perform a log backup and restore the log backup at the target Turn on mirroring again

p. 8

Database Mirroring Best Practices

• Network Best Practices for Database Mirroring: 1. Consider using database mirroring for local or metro-area networks •

The farther away the servers are, the longer it takes to stream the transactions – Also introduces higher potential for communication issues

• Network roundtrip times can be 100x faster on a LAN than on a WAN (Wide Area Network) – 1ms vs. 100ms

p. 9

Database Mirroring Best Practices

• Network Best Practices for Database Mirroring: 2. Faster networks mean promotions and plaudits •

Slow networks mean updated resumes and m-e-e-t-in-g-s to explain why it doesn’t work like the vendor said it would…

p. 10

Chapter 10: High Availability

Presented by Scott Whigham

1

p. 1

• Log Shipping • Database Mirroring

Clustering & Replication

• Overview of Chapter • What is High Availability?

Log Shipping & Mirroring

Overview

What We’re Going to Cover

• Clustering • Replication • Peer to Peer Replication

p. 2

SQL Clustering Best Practices

• There are a few categories of Best Practices as relate to SQL Clusters – Hardware – Installation – Configuration

p. 3

SQL Clustering Best Practices

• Hardware best practices involve: 1. Do not use the quorum disk for any SQL Serverrelated files 2. Use identical network adapters with identical software/drivers 3. Manually set the speed and duplex mode of any adapters that allow multiple speeds •

Setting to “Auto Select” may result in dropped packets

p. 4

SQL Clustering Best Practices

• Hardware best practices involve (cont.): 4. Since the clustered disks are a single point of failure, ensure they are sufficiently redundant •

RAID 10 is quite popular

p. 5

SQL Clustering Best Practices

• Installation best practices involve: 1. Start the installation from the node that owns the disk resources 2. If you have to install SQL 2008 onto a cluster with SQL 2000 already on it, install SP4 for SQL 2000 on all nodes first, then install SQL 2008 onto the cluster 3. For a 2008 install onto a pre-existing 2005 cluster, install SQL 2005 SP2 first and then SQL 2008 p. 6

SQL Clustering Best Practices

• Configuration best practices involve: 1. On Active/Active clusters, make sure to set max server memory for each instance so that you allow for multiple instances running on one node at the same time 2. Consider using a slipstreamed installation DVD •

Slipstreaming is the process of creating an installation media that already contains a service pack and/or hotfix

p. 7

SQL Clustering Best Practices

• Configuration best practices involve (cont.): 3. Integration Services is not installed as a clustered resource •

Although you could make it a clustered resource, it is better to install it locally to each node

p. 8

Chapter 10: High Availability

Presented by Scott Whigham

1

p. 1

• Log Shipping • Database Mirroring

Clustering & Replication

• Overview of Chapter • What is High Availability?

Log Shipping & Mirroring

Overview

What We’re Going to Cover

• Clustering • Replication • Peer to Peer Replication

p. 2

SQL Server Replication

• Replicating data is the process of copying data and table definitions from database to database – Example: • User enters data at the Los Angeles server • Replication copies the change servers in Sydney, London, and Mumbai • After copying the change, all servers have the same data

p. 3

SQL Server Replication

• Basic terms: – Publisher: the source database – Publication: the tables, views, and stored procedures that the publisher is publishing – Subscriber: the database receiving the changes – Subscription: how the subscriber receives the data – Distributor: the server responsible for delivering the publication to the subscriber

p. 4

SQL Server Replication

Publisher (source)

Distributor

Subscribers

p. 5

SQL Server Replication

• When the data is copied to a database, it is considered synchronized – Synchronization can be instant or scheduled • Instant - as the change is entered at Publisher it is instantly replicated; Subscribers and Publisher always have the same data • Scheduled - allows latency between when data is entered at Publisher and when Subscribers have data

p. 6

SQL Server Replication

• There are three basic types of replication: – Snapshot – Merge – Transactional

• Let’s look at each!

p. 7

Snapshot Replication

• Snapshot replication: – Publisher generates a snapshot - a point-in-time copy of the publication – Distribution then replaces copy of subscribers’ data with the snapshot • Typically one-way replication (data entered only at publisher)

– Analogy: Newspaper industry

p. 8

Snapshot Replication

p. 9

In the next video…

• Let’s continue our tour of the types of replication with Merge and Transactional

p. 10

Chapter 10: High Availability

Presented by Scott Whigham

1

p. 1

• Log Shipping • Database Mirroring

Clustering & Replication

• Overview of Chapter • What is High Availability?

Log Shipping & Mirroring

Overview

What We’re Going to Cover

• Clustering • Replication • Peer to Peer Replication

p. 2

Merge Replication

• Merge replication: – Basic premise: Anyone can change the data – Transactions are replicated • Unlike Snapshot replication which replicates entire snapshot

– Changes are synchronized between all servers

p. 3

Merge Replication

• Merge replication: – Setup flow: 1. Create the publication and generate the snapshot 2. Distribute snapshot to subscribers –

At this point, Publisher and Subscribers have same data

3. Someone changes the data (could be Pub or Sub) 4. Merge Agent handles merging the data so that, in the end, everyone has the same data

p. 4

Merge Replication

• Conflicts can occur in Merge Replication – What happens when two sites change the same data at the same time?

• You can help lessen the chance of a conflict: – You can specify that changes to the same row by different sites is allowed provided that they change different columns – At synchronization, the Merge Agent merges the row and synchronizes all servers so that everyone has the same data

p. 5

Merge Replication

• You can define how conflicts are resolved in several ways: – By setting priority levels for each site • In the event of a conflict between two sites, the highest priority site’s changes “win”

– By setting, “First in wins” or “Last in wins” • Whoever made the change first/last “wins”

– By writing custom business rules

p. 6

Merge Replication

p. 7

Transactional Replication

• Transactional replication: – Transactions are replicated between servers – Transactional can: • Be one-way: only the Publisher can replicate changes • By multi-way: changes can occur at any site

– Transactional may or may not have conflict detection • Depends on topology • Rule of thumb: “Last in wins” unless you configure differently p. 8

Transactional Replication

p. 9

P2P Transactional Replication

• Peer to Peer (P2P Transactional replication: – Allows both scale-out and high availability • Easily scales since each site acts as Publisher and Subscriber (called “Nodes”) • Every site has the same data so clients can connect to any site thus allowing a degree of load balancing • If one site goes down, users just connect to another site

– Debuted in SQL 2005

p. 10

P2P Transactional Replication

p. 11

P2P Transactional Replication

• Conflicts can occur in P2P – What happens when a user connected to the Dallas node updates a row and, at the same time, a user on the Houston node deletes that row? • Default: Last in wins • Can enable “conflict detection” - default setting is to stop distribution when a conflict is found

p. 12

Let’s demo!

• Let’s demo P2P!

p. 13