http://www.sqlservercentral.com/blogs/steve_jones/2007/06/05/sql-server-always-on/ Printed 2017/05/25 10:22AM
SQL Server Always OnEveryone 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
- 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
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
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
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
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.