FullMirroringScript_for download.sql
When you're looking for a high-availability solution, you might find that although
third-party providers offer good solutions and complete packages, their products can get pretty expensive. Once you evaluate the costs, you might decide that implementing your own database mirroring solution is a no-brainer. However, implementing
and supporting a successful mirroring solution takes some thought and planning. Sure,
you can slap together a mirroring solution with little effort, but if you don't have a
handle on every aspect of your environment, you could be in for a bumpy ride. You
must consider such aspects as network speed, security, maintenance, upgrades, and
failover. (For information about how to decide whether mirroring is right for you, see
the Web sidebar "Should You Choose Database Mirroring?", linked at the top of this article page.) If you don't include all the necessary aspects in your implementation, you could bring your
production environment down in a matter of minutes.
So, let's look at how to set up and support mirroring as
an alternative to an expensive third-party high-availability solution. In this example, we
might not need the entire server to fail over; instead, we'll assume we're working in
an environment in which we need only a single database or two to be mirrored. What
you'll find is that although mirroring is an excellent high-availability technology for
individual user databases, it's far from a complete disaster-recovery technology. For one
thing, mirroring doesn't protect system databases, whereas third-party products typically do. So although you wouldn't use mirroring to fail over an entire server, it can be
a good choice for protecting important data and it doesn't involve the complexity of
replication.
Configuring Your Network
Getting mirroring going is easy enough, but the process can reveal previously unknown
network problems. Therefore, it's always best to get your mirroring setup running on a
non-production system before taking the plunge with your important data.
First, you need to make sure you have the proper Fully Qualified Domain Name
(FQDN) for both your primary and secondary servers. You can verify the FQDN by
connecting to the server console either directly or through Windows 2000 Server Terminal Services. In Terminal Services, go to Start, Run, and type cmd. Then, type ipconfig/all. You should see a screen like the one that
Figure 1 shows.
Notice in Figure 1 that the second line
in the result set is Primary Dns Suffix. This
setting is what SQL Server is looking for
when setting up mirroring. You'll have to
work out any DNS issues before you can
set up mirroring, so this setting is a good
place to start. If the primary DNS suffix isn't
what you're expecting to see, the easiest way
to fix the problem is to add the proper DNS
suffix in the connection's IP properties. You
can see how to add the DNS suffix on the
DNS tab of the Advanced TCP/IP Settings
dialog box in Figure 2.
Why does SQL Server require you
to use the server's FQDN instead of the
NETBIOS (domain name) or even the IP
address? This security requirement lets SQL
Server be sure it is talking to the desired
server for Secure Sockets Layer (SSL) and
the mirroring network validation checks.
(You must provide a canonical name that
can be compared to a self definition of the
machine.) You also have to address any network problems before you have a successful
mirroring setup.
Getting Set Up for Mirroring
Now, you can begin to set up mirroring.
Follow these basic steps:
- Take a full backup and a log backup of
the primary database.
- Restore both backups to the secondary
server.
- Create the endpoints.
- Establish partnerships.
- Start mirroring.
This list is oversimplified, so let's walk
through the details of the setup that you'll
encounter in the mirroring pane of the
database properties window and the Database Mirroring Security Wizard. Note that
you must perform the backups and restores
yourself.
That's a good place to
start—so begin by performing
a full backup and a log backup,
then restore them both on the
secondary server. You must
restore your backups with the
same names that they have
on the primary database, but
the restored databases don't
have to be in the same location on the secondary server.
In addition, it's OK to run
the log backup right after the
full backup; you don't have to
leave any time between them.
At this point, you might
wonder why you even need
the log backup? If you've
already restored the full
backup with no recovery, why
take a log backup? Shouldn't
mirroring just be able to pick
up where the full backup left off? The answer is yes and no. Although
you could use only the full backup, SQL
Server requires the first log backup to be
restored to ensure that no bulk logged
pages are in the database. The log backup
includes any bulk logged pages and clears
the flag that indicates the existence of bulk
logged pages. If you took a log backup after
the one applied to the mirror, that backup
could include bulk logged pages that were
generated and backed up but left no traces
in the log. In this case, the bulk logged data
wouldn't make it to the mirror because it's
not in the log stream and no bulk logged
bits are set.
Once the backups are restored on the
secondary server, we can start the wizard
and establish the mirroring session. Rightclick the primary database and go to Tasks,
Mirror or go to Properties, then select the
Mirroring tab. Click Configure Security
to start the Configure Database Mirroring
Security Wizard that Figure 3 shows. In the
Include Witness Server dialog box, you'll
almost always want to configure a witness.
In addition to being needed for failover, the
witness prevents what's known as a split-brain
scenario, in which the link between the two
servers gets broken for some reason and each
one thinks the other is down. In such a case,
the secondary database will become the primary database, and when the link is re-established, both databases think they're primary.
Not pretty. Having a witness prevents this
error. You can use any edition of SQL Server
as your witness, so if you don't already have a
free server, you can install SQL Server Express
somewhere reliable and you'll be fine.
The principal server instance defaults to
the server you started the wizard from, so be
sure to start the wizard from the principal. You also have to start the wizard from the
right database. The wizard doesn't have
a place to pick the database to mirror; it
assumes you started it from the database you
want. You can pick your own port and endpoint name in the Principal Server Instance
dialog box that Figure 4 shows, but it's best
to accept the defaults. The only reason to
pick something different is if the current settings are already in use or are restricted. So
for example, if you already had an endpoint
named Mirroring, you'd have to choose
another name. Or you might have to choose
an alternative port if the listener port is in
use by another service or firewall restrictions
prevent you from using the default. If you
don't have any of these circumstances, there's
no reason to change the defaults unless you
just want to.
Prev. page
[1]
2
next page 
