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.