Next, you choose the mirror instance
in the Mirror Server Instance dialog box,
which looks similar to the dialog box in Figure 4. Then, the wizard will force you to
connect, and you'll get to make the same
choices for the witness in the Witness Server
Instance dialog box.
Now, configure the service accounts
for each server. If you leave these blank,
as Figure 5 shows, it's assumed
they're all the same domain accounts or
are non-domain accounts. I didn't need to
configure service accounts for my scenario
because I'm using the same account on all
my servers.
Finally, the Success dialog box tells you
if anything has gone wrong. After you close
the box, the wizard asks you if you want to start mirroring, as
you can see in Figure 6.
If you click Do
Not Start Mirroring, you can
start mirroring
any time from
the Mirroring
pane of the database's Properties
window. However, if you wait
too long and the
logs get out of
synch, you'll have
to apply another log backup and start the
wizard from scratch.
Backups
Now that you have a working mirroring
setup, you need to know how to manage
it. Naturally, because the mirror is just a
standby instance of the primary database,
you don't have to back it up. In fact, you
can't back it up because it isn't available for
any kind of connection. So if you have automated routines that back up all databases
on the server, you'll need to exclude the
mirror or you'll get an error every time the
job runs. Also, depending on how you have
your backup job set up, it might not continue backing up any of the other databases.
For example, if you have a stored procedure
that cursors through sys-.databases, backing up each-database as it goes, you have two choices. First, you could call the backup statement directly from the stored procedure, in which case the backup will stop on the first error. So if the first database in the cursor is the mirrored instance, the procedure will
fail and none of the other databases will get backed up. Your second choice is to build the backup statement,assign it to a variable, and execute the variable. This choice causes the backup statement to run outside the context of the stored procedure, and
although that statement will produce an
error on the mirrored instance, it will continue to move forward and back up the rest
of the databases. In either case, you'll want
to exclude the mirrored database from the
cursor because it will always produce an
error that you have to investigate.
Index Maintenance and Upgrades
You also have to understand the effect
mirroring will have on your maintenance
routines. If you're running mirroring in
full safety mode (synchronous), all of your
REINDEX and DEFRAG statements will
be applied to both servers in real time. This
requirement could easily increase your
maintenance time by 100 percent or more.
So if you have a strict maintenance window,
you might want to consider changing your
mirroring to asynchronous mode before
you begin maintenance. Let's look at the
choices you have for managing mirroring
with your maintenance routines.
- Leave in synchronous mode (safety on).
This choice will at least double your
maintenance time.
- Pause mirroring during maintenance.
This solution is better than using
synchronous mode if you have a
strict maintenance window, but once
maintenance is done and you resume
mirroring, your mirroring session will have to resynch. The primary database
will be available during the resynch,
but it won't be in synchronous mode
until the logs get caught up, so you'll
be unprotected for a time. To pause
mirroring, run ALTER DATABASE
against either the primary or the secondary server with the appropriate
setting: ALTER DATABASE AdventureWorks SET PARTNER SUSPEND.
- Switch to asynchronous mode (safety
off). The primary database still sends
its logs to the secondary database, but
it doesn't wait for the transactions to
harden on that secondary server before
it commits its own transactions. On
large implementations that support
lots of activity, this method is preferred
because you'll still be in asynchronous
mode while the two servers resynch,
but the resynch time will be shorter
than it would be if you paused mirroring. Thus, you'll greatly reduce your
exposure. To switch to asynchronous
mode, run ALTER DATABASE
against either the primary or the secondary server with the appropriate setting: ALTER DATABASE MirrorTest
SET PARTNER SAFETY OFF.
- Stop mirroring. You also have the
option of completely stopping mirroring and re-establishing it once maintenance is complete. This solution isn't
practical for most shops, but it is an
option. To stop mirroring, simply run
ALTER DATABASE against either
the primary or the secondary server
with the appropriate setting: ALTER DATABASE MirrorTest SET PARTNER OFF.
The same considerations come into play when you have schema changes. Especially when you're
using mirroring, you
must test schema changes
before you implement
them because some
changes can cause massive
log activity and you'll need
to benchmark ahead of time. For example, if
you add a column to a large table and fill it
with default values, you could double your
processing time and the upgrade time. For
a more detailed discussion of database mirroring performance considerations, see the
Microsoft article "Database Mirroring Best
Practices and Performance Considerations"
at http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx.
Automated Monitoring
You'll also want to monitor your mirroring
setup to make sure your secondary server
isn't getting too far behind if you're running
in asynchronous mode (safety off). To do so,
you can use the monitoring GUI that is
included in SQL Server 2005 Management
Services, Service Pack 1 (SSMS SP1). This
GUI will give you real-time statistics about
your mirroring setup. I'm not going to
go into details about the SSMS GUI here
because most DBAs don't have the time
to monitor a mirroring scenario for very long. Instead, I recommend using the Perfmon counters. You can add these counters
to existing monitoring solutions such as
MOM, OpenView, or Tivoli and set alert
thresholds, as you would with any counter.
You'll notice a new object in Perfmon: SQL
Server: Database Mirroring. In the object,
you'll find several interesting counters that
display statistics about how many bytes get
pushed between the two servers. The two
counters that will be most useful to you in
benchmarking and diagnosing performance
issues are the Log Send Queue and Redo
Queue counters. The larger these numbers
get, the further behind your mirror is getting, and you should look into the root
cause of the latency.
The Bottom Line
Mirroring can be a powerful tool in your
high-availability scenario if you pay attention to its limitations and benchmark and
monitor it properly. Mirroring is easy to
set up and provides zero data loss under
the right conditions. And although it can
complicate maintenance scenarios, it's easier
to support than replication or clustering.
Mirroring can also be an attractive alternative to the often-bloated costs of third-party
applications. You can download the T-SQL
scripts you need to set up mirroring and
some common management tasks at http://www.sqlmag.com. Click Download the
Code at InstantDoc ID 95293.