SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server Always On

Everyone wants 5 9's of uptime, 24x7 support, and instant fixes for issues with their database server. Never mind that I'd argue that most DBAs don't really need the "Always On" technology, but it's something that sells. So I thought Kim Tripps "Demo Fest" would be a cool seminar to see for a DBA.
Kim is a great speaker, entertaining and very comfortable up there, so if you get the chance to see her, I think you'll enjoy it. This was a demo session, really a couple slides and then demos. She was looking to cover:
 - Mirroring
 - Snapshot no mirror
 - Online Indexing
 - Peer to peer replication

 Using VPC images, we got to see the various demo items. One of the intersting things is she showed some tips on how to setup your lab or test environment. Kim has 5 instances on her image, but she also has them set to not start by default. Instead there are batch files to start and stop services for specific labs.

 Mirroring was the first step, which is one of the hot new features of SS2K5. An interesting fact that I knew, but you might not, is that the witness server, the third (optional) server in mirroring, can run on SQL Server Express. So it's a very lightweight requirement for this server instance. Plus the cool thing about a witness is it doesn't cost anything with Express.

 I don't think mirroring is that hard to understand as it's simlar to what I've been doing with custom log shipping scripts, but it's something many new people to SQL Server have to dig into a little to understand. A great, simple explanation was given on what mirroring is and the limitations and restrictions. Like driving home the point the primary can only be in full recovery mode, no simple or bulk-logged, and that the mirror cannot have its recovery model changed. So reporting cannot be directly done off the mirror.

 Scripting mirroring isn't something that's easy to do. No wizard buttons for it and while Profiler can be used, it's a hassle. Kim Trip has a script that should go up on SQLSkills.com, using SQLCMD, after TechEd.

 If you haven't looked at SQLCMD, you should. As Kim mentioned, it's got a lot of advantages in its scripts: variables, error handling, loops, labels, etc. She also gives good reasons to be sure that your directory structures are the same on the principal and mirror. Some good tips and tricks to be sure that you don't get yourself into trouble with this technology. Another big one is to be sure that you don't get into trouble with the setup because of the timing of the restore to allow your mirror to "catch up."  

There's also a DMV for mirroring, sys.database_mirroring for those of you that don't want to work with the Mirroring Monitor.

In the Object Explorer, your database will show as eitherthe principal or mirror and whether it's synchronized. The mirror also shows it's in the "Restoring" state.

For monitoring, one of the cool things was seeing a small applet that watches a table on both the principal and mirror and counts rows. It actually connects to the principal, but the attempt to connect to the secondary, it gets redirected to the mirror. Kind of an interesting way to show the implicit redirection. If you put the mirror in your string, then you'll move directly to the principal. If you want to connect to the principal with a failover, then you need both servers in your connection string. One issue with the connecting to the mirror instance, is that it must be available. If it's down, you'll get an error.

Failover can be via SSMS or T-SQl, as well as a real failure of your principal of course :). The connections that exist, when something fails, connections break and transactions rollover. So there isn't a transaction movement from one instance to the other.

Be aware that if your mirror is down for a long time, you might have a "Synchronizing" state for some time to get back in synch. That means that you aren't really protected while things are catching up. Which means you need to get your mirror back up as quickly as possible.

The witness checks both the principal and mirror for "aliveness", but it's forming a quorum with one of the other two servers. This means that it can die and it doesn't affect the log on the principal, or even the transactions moving to the mirror. There is a problem if the witness goes down in that if the principal loses connectivity to the mirror, it thinks it might be down, and the mirror and witness can see each other. So it closes the database down, which means losing the witness and the mirror will bring down the principal. Not a great situation.

A Reporting Mirror
You can't use the mirror to report, but you can create a snapshot on the mirror database. I had someone ask the question at the Colorado Code Camp that we can create a snapshot on the mirror, we can read the snapshot, which goes back to the mirror, why can't we read the mirror? I asked and was told it's coming. Not sure about Katmai, but should be out by at least the release after that. Same for snapshot backups.

My answer at the Code Camp, when no one else had one, was "it's a feature" :)

Some interesting questions. What if you have a clsutered principal that has a mirrored database. If you have a failover on the cluster, you might have a mirror failover as well because of the time to restart the cluster. You can change the mirror timeout to fix this.

Someone also asked if the principal/mirror transfer is a two-phase commit. It's not two-phase commit, but a new algorithm that writes the transaction to the Principal's log, then writes it to the Mirror log, then commits on the Mirror, then commits it on the Principal. If the Mirror is down, it still commits on the principal's log. If the witness is down.

It's not complicated, but there are some moving parts and it can get confusing in some scenarios.

No license required for mirror being used a mirror only. If you report off it, you got to get a new license for that server.

Snapshot files look big, but check the space on disk. They're "sparse files" and are really small.

Online indexing, some blocking that could occur at the beginning and end of the process, but not that much.

A quick look at Peer to Peer
Because of time, this was just a 5 minute session. Different than mirroring. Mirroring seems simpler, and more protective in that it happens quickly.

Repl is more scalable because you can do partial replication, only move certain data, move to multiple places. This means that you can have multiple peers and have data move among them all with bi-directional transactional replication between them all. Requires some app work, but it's pretty cool.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.