High Availability with SQL Server 2005

This was a great session.  It was cut a little short by an EMERGENCY EVACUATION!!!  Go figure.  :)  It focused on the new enhancements that SQL Server 2005 has added to provide for greater flexibility and implementation of high-availability networks.  For those who are members of PASS, the slides can be downloaded from www.sqlpass.org when the conference is over.  Here are my takeaways from the session:

• Covered Database Mirroring.
1. Can be a long ways away.

                                                               i.      Have to keep transactions in sync though, which can cost performance.

                                                             ii.      Don’t need to be on compatability list like clustering.

                                                            iii.      Should run equal hardware though; however, it’s not a requirement.

                                                           iv.      No shared disk.

2. Can pick synchronous/asynchronous.
3. Terminology

                                                               i.      A client attached to the “principal” server.

                                                             ii.      Principal server sends “secondary transactions” to the “mirror” server.

                                                            iii.      There is a “witness”.  Provides vote on who is principal and mirror.  Provides the winning vote.  Allows failover if the connection breaks between principal and mirror vs. the actual servers failing.

4. We can use database snapshots on the mirror, put it in read-only, and use it for reporting.
5. To become the Principal, a server must talk to at least one other server.  Can provide some long distance problems.
6. Big question came up on whether you can mirror the master database.??? 

                                                               i.      This is a great question.  The answer appears to be no.

                                                             ii.      Would be a problem.

7. It’s pretty efficient because it catches the writes in the log buffer and automatically sends the log entries to the other server.  There’s two types of commits:

                                                               i.      Safety mode:  Verifies the commit on the other side before it commits.  Has automatic failover capability.

                                                             ii.      Safety off:  Just forgets about the transactions after it sends them. 

8. Because it’s just sending stuff, when you rollback a transaction it’s slower.  It has to verify the rollback on the other side as well.
9. DDL Changes are captured.
10. Is able to handle multiple databases together with cross-database updates.

                                                               i.      Need to find out more information.  This wasn’t covered as the session was cut short.

11. Can you tell how much latency exists between the mirrors?

                                                               i.      Would be very efficient in a SAN configuration.

12. There’s a client library to automatically redirect to mirror.  Requires no changes to application code.  Client automatically redirected if session is dropped.  It’s aware of the principal and mirror servers.  The library caches mirror name upon initial connect to principal.
13. There is a manual failover process if you decide to use asynchronous.

                                                               i.      ALTER DATABASE db SET PARTNER FAILOVER

• Covered replication, log shipping, cold standby.
1. Not changing log shipping.  Lots of sessions on replication.
2. Good chart on how to choose different types of failover.
• Database Snapshots (new)
1. Allow recovery from user erros by allowing the database to go back in time.
2. Multiple snapshots are allowed.
3. It’s read only.  To drop the database it’s attached to, you need to drop the snapshot first.
4. CREATE DATABASE mydbsnap ON (filelist) AS SNAPSHOT OF mydb
5. DROP DATABASE mydbsnap
6. You can restore from a snapshot database.

                                                               i.      RESTORE DATABASE mydb FROM DATABASE_SNAPSHOT = ‘mydbsnap0600’.

                                                             ii.      Good Question:  Can you restore to a different database?