Lesson 1: Creating a Database Snapshot 607
Lesson Summary
■ A Database Snapshot is a point-in-time, read-only copy of a source database that
exists on the same SQL Server instance as the source database. As pages are
changed in the source database, the original image of the data page is written
into the sparse files of the Database Snapshot to preserve the state of the data at
creation time.
■ Although a Database Snapshot can be queried like any other database, you are
not allowed to modify data or structural elements. A Database Snapshot also
cannot be used for backup/restore operations. Even though it is required to exist
on the same SQL Server instance as the source database, a Database Snapshot
can be created against a mirror database within a Database Mirroring session.
Lesson Review
The following questions are intended to reinforce key information presented in this
lesson. The questions are also available on the companion CD if you prefer to review
them in electronic form.
NOTE Answers
Answers to these questions and explanations of why each answer choice is right or wrong are
located in the “Answers” section at the end of the book.
1. Which operations can be performed against a Database Snapshot?
A. BACKUP DATABASE
B. ALTER DATABASE
C. SELECT COLUMN1, COLUMN2 FROM TABLE
D. UPDATE TABLE1 SET COLUMN1 = 4
2. You can create a Database Snapshot against which types of databases? (Choose
all that apply.)
A. User database
B. Master database
C. Another Database Snapshot
D. Mirror database
C1662271X.fm Page 607 Friday, April 29, 2005 8:00 PM

608 Chapter 16 Managing Database Snapshots
3. What data is contained within a Database Snapshot? (Choose all that apply.)
A. All original pages of the source database
B. Only the original image of pages changed since the Database Snapshot was
created
C. A catalog of changed pages
D. Metadata about the database objects
C1662271X.fm Page 608 Friday, April 29, 2005 8:00 PM
Lesson 2: Reverting a Database from a Database Snapshot 609
Lesson 2: Reverting a Database from a Database Snapshot
A Database Snapshot provides a point-in-time copy of a source database. Because it
provides a copy of the data in the database, you can use it to recover in a variety of sit-
uations. In the event of data being accidentally damaged or if an administrative pro-
cess makes changes that are unwanted, you can extract the original version of the data
from the Database Snapshot and move it back into the source database using either
an INSERT or UPDATE statement.
In an extreme case, a DBA might want to restore the state of a database back to a pre-
vious point in time. This lesson will explain how to revert a source database to a pre-
vious point in time by using a Database Snapshot.
After this lesson, you will be able to:
■ Revert a database from a Database Snapshot.
Estimated lesson time: 10 minutes
Reverting a Database
Reverting a database is a special subclass of restore operation that you can perform
against a database. During a restore of a database, you can leave the database in a
recovering state to apply subsequent transaction logs to roll a database forward to a
specific point in time. Reverting a database will take a database back to a point in time;
however, you cannot restore subsequent backups after reverting the database to that
point in time.
Using a Database Snapshot to revert a database has some restrictions:

■ Only a single Database Snapshot can exist against a source database.
■ Any full-text catalogs on the source database are dropped and must be re-cre-
ated.
■ The transaction log is rebuilt, which breaks the log chain.
■ The source database and Database Snapshot are offline during the revert.
The syntax to revert a database from a Database Snapshot is as follows:
RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>
C1662271X.fm Page 609 Friday, April 29, 2005 8:00 PM
610 Chapter 16 Managing Database Snapshots
Quick Check
■ How many Database Snapshots can exist against a source database when
you are restoring?
Quick Check Answer
■ Reverting a database using a Database Snapshot causes all the changed
pages within a Database Snapshot to overwrite the corresponding pages in
the source database. Because this process changes the state of the database,
it would immediately invalidate all Database Snapshots except the one
used to revert from. Therefore, SQL Server enforces the restriction that only
a single Database Snapshot can exist against a source database when you
use the Database Snapshot to revert. In this way, it prevents the possibility
of having invalid Database Snapshots.
PRACTICE Reverting a Database from a Database Snapshot
In this practice, you will revert the AdventureWorks database to a previous version
using the Database Snapshot you created in the previous lesson in this chapter.
1. If necessary, open SSMS and connect to the SQL Server instance containing the
Database Snapshot you created in the previous lesson in this chapter.
2. Click New Query.
3. Type the following code:
RESTORE DATABASE AdventureWorks FROM DATABASE_SNAPSHOT = ’snapshottest’
4. Verify that the data added to the table in the AdventureWorks database during the

previous exercise no longer exists.
Lesson Summary
■ The RESTORE DATABASE command contains a special clause that enables a
DBA to revert a database from a Database Snapshot. This operation would inval-
idate any other Database Snapshots created against the source database, so you
must drop all other Database Snapshots before you can perform a revert. Addi-
tionally, any operation that relies on a contiguous transaction log chain will be
interrupted because the restore process will rebuild the transaction log.
C1662271X.fm Page 610 Friday, April 29, 2005 8:00 PM
Lesson 2: Reverting a Database from a Database Snapshot 611
Lesson Review
The following question is intended to reinforce key information presented in this les-
son. The question is also available on the companion CD if you prefer to review it in
electronic form.
NOTE Answers
Answers to this question and explanations of why each answer choice is right or wrong are located
in the “Answers” section at the end of the book.
1. Which of the following are required before a database can be reverted from a
Database Snapshot? (Choose all that apply.)
A. Full text catalogs on the source database must be dropped.
B. Users cannot be accessing the source database or the Database Snapshot.
C. Log shipping must be stopped.
D. All Database Snapshots except the Database Snapshot used for the revert
must be dropped.
C1662271X.fm Page 611 Friday, April 29, 2005 8:00 PM
612 Chapter 16 Review
Chapter Review
To further practice and reinforce the skills you learned in this chapter, you can
■ Review the chapter summary.
■ Review the list of key terms introduced in this chapter.

■ Complete the case scenario. This scenario sets up a real-world situation involv-
ing the topics of this chapter and asks you to create solutions.
■ Complete the suggested practices.
■ Take a practice test.
Chapter Summary
■ A Database Snapshot is a point-in-time, read-only copy of a source database that
can be used for read activities.
■ In addition to read activities, you can use a Database Snapshot to revert a data-
base to a previous point in time defined by the instant in time at which you cre-
ated the Database Snapshot.
■ Because Database Snapshots can be used for read operations as well as recover-
ing to a previous point in time, they are ideal for use in situations in which DBAs
would normally create an interim backup to eliminate a significant amount of
time spent during maintenance operations.
Key Terms
Do you know what these key terms mean? You can check your answers by looking up
the terms in the glossary at the end of the book.
■ catalog of changed pages
■ copy-on-write
■ Database Snapshot
■ reverting a database
■ source database
■ sparse file
C1662271X.fm Page 612 Friday, April 29, 2005 8:00 PM
Chapter 16 Review 613
Case Scenario: Implementing Database Snapshots for
Administrative Actions
In the following case scenario, you will apply what you’ve learned in this chapter. You
can find answers to these questions in the “Answers” section at the end of this book.
Contoso Limited, a health care organization located in Bothell, WA, has a very volatile

database that contains patient claims data. The patient data is protected by privacy
laws, and all access to this data is required to be audited. Audit data is written into a
set of audit tables by the stored procedures that control all data access within the
patient claims database.
Auditors within the organization, along with external auditors, require access to audit
data in the patient claims database at specific points in time.
Each evening, Contoso receives data feeds from several external vendors who process
payments to patients. Data in the feeds frequently needs to be edited and reimported
based on validation scripts that reconcile the data within the patient claims database
with the data feeds submitted by the external vendors. During the time when the
import processes execute, no other transactions are issued against the patient claims
database. The current process creates a full backup of the patient claims database
before the import routines are executed.
1. How can Contoso DBAs reduce the amount of time it takes to import data feeds?
2. What mechanism can Contoso use to provide mutiple point-in-time copies of
the data for auditors to query while minimizing the amount of time spent on
administering this solution?
Suggested Practices
Database Snapshots are a very specific feature within SQL Server 2005. A Database
Snapshot has exactly one way to create it, and there is exactly one way to revert a data-
base using a Database Snapshot. Therefore, no additional practices exist for Database
Snapshots beyond those already specified within this chapter.
C1662271X.fm Page 613 Friday, April 29, 2005 8:00 PM
614 Chapter 16 Review
Take a Practice Test
The practice tests on this book’s companion CD offer many options. For example, you
can test yourself on just the content covered in this chapter, or you can test yourself on
all the 70-431 certification exam content. You can set up the test so that it closely sim-
ulates the experience of taking a certification exam, or you can set it up in study mode
so that you can look at the correct answers and explanations after you answer each

question.
MORE INFO Practice tests
For details about all the practice test options available, see the section titled “How to Use the Prac-
tice Tests” in this book’s Introduction.
C1662271X.fm Page 614 Friday, April 29, 2005 8:00 PM
615
Chapter 17
Implementing Database Mirroring
Database mirroring is a new Microsoft SQL Server 2005 availability technology that
lets you maintain a hot or warm standby server with automatic failover and no data
latency. Database mirroring, available currently as an evaluation feature that you
enable by using a trace flag, operates at the database level to provide a duplicate copy
of data on a mirror database and server. This chapter introduces you to database mir-
roring, which will be included as a supported feature of SQL Server 2005 in a future
service pack, and it explains each operating mode that you can configure for this long-
awaited feature.
MORE INFO Database mirroring
This chapter covers the basic data mirroring information you need for the 70-431 exam. For full
details about database mirroring, see the white paper “Database Mirroring in SQL Server 2005” by
Ron Talmage at www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx.
Exam objectives in this chapter:
■ Implement database mirroring.
❑ Prepare databases for database mirroring.
❑ Create endpoints.
❑ Specify database partners.
❑ Specify a witness server.
❑ Configure an operating mode.
Lessons in this chapter:
■ Lesson 1: Understanding Database Mirroring Roles. . . . . . . . . . . . . . . . . . . . . 618
■ Lesson 2: Preparing Databases for Database Mirroring . . . . . . . . . . . . . . . . . . 622

■ Lesson 3: Establishing Endpoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
■ Lesson 4: Understanding Operating Modes. . . . . . . . . . . . . . . . . . . . . . . . . . . . 634
■ Lesson 5: Failing Over a Database Mirror. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642
■ Lesson 6: Removing Database Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 645
C1762271X.fm Page 615 Friday, April 29, 2005 8:02 PM
616 Chapter 17 Implementing Database Mirroring
Before You Begin
To complete the lessons in this chapter, you must have
■ Three instances of SQL Server 2005 installed.
■ Either Standard or Enterprise Edition for all instances.
■ A copy of the AdventureWorks sample database on one of the instances.
■ Trace flag 1400 enabled on all three instances.
NOTE Enabling database mirroring with trace flag 1400
In the release-to-manufacturing (RTM) version of SQL Server 2005, database mirroring is not a
supported feature and can be enabled only by using trace flag 1400. In a future SQL Server 2005
service pack, database mirroring will be enabled and fully supported within the product. To set a
trace flag, refer to the SQL Server 2005 Books Online article “DBCC TRACEON (Transact-SQL).” SQL
Server 2005 Books Online is installed as part of SQL Server 2005. Updates for SQL Server 2005
Books Online are available for download at www.microsoft.com/technet/prodtechnol/sql/2005/
downloads/books.mspx.
Real World
Michael Hotek
Since I formally entered the database industry more than a decade ago, my pri-
mary focus has been on building systems to predictably achieve high levels of
availability. In the 1980s and 1990s, this was a rather difficult task. It usually
involved complex architectures, complicated components, and a large dose of
custom coding. The tools and technologies were immature at best and nonexist-
ent at worst.
You could divide the availability systems that we designed back then into two
basic categories. Client-oriented systems would receive a transaction and then

write it to multiple destinations. And we generally built server-oriented systems
around code to transfer backups between one or more systems.
As technology matured, we gained additional tools that allowed basic data dupli-
cation across multiple environments in a timely manner. However, this advance-
ment also introduced latency between the primary and secondary databases,
which could lead to data loss. We learned to deal with the potential problem
because we simply could not eliminate it.
C1762271X.fm Page 616 Friday, April 29, 2005 8:02 PM
Before You Begin 617
What we needed was an integrated database technology, transparent to applica-
tions, that would maintain a duplicate copy of the data without incurring
latency. If the technology could also provide mechanisms to automatically fail
over to the secondary database upon failure of the primary database, it would be
a significant evolutionary advancement in availability technologies.
With the addition of database mirroring in SQL Server 2005, we finally have a
technology that fills a significant gap in availability solutions and does not
require custom coding. I can now make a single database within an instance
highly available, with automatic failover, no latency between the primary and
secondary databases, and transparency to the application. It isn’t a perfect solu-
tion yet, but database mirroring is well on the way to fulfilling an availability
requirement that I’ve had for well over ten years.
C1762271X.fm Page 617 Friday, April 29, 2005 8:02 PM
618 Chapter 17 Implementing Database Mirroring
Lesson 1: Understanding Database Mirroring Roles
All new technologies add new terminology to our vocabulary, and database mirroring
is no different. In this lesson, you learn many of the key terms for database mirroring,
including principal database, mirror database, and witness server. And you see how
these different database mirroring roles interact with each other in a database mirror-
ing session.
After this lesson, you will be able to:

■ Define what the principal database does.
■ Define what the mirror database does.
■ Define what the witness server does.
■ Understand how the database mirroring roles work together.
Estimated lesson time: 10 minutes
Database Mirroring Roles
Database mirroring comprises two mandatory roles and a third optional role. You
must define a database in a principal role and another database in a mirror role. You
can also optionally define a SQL Server instance in the role of witness server to govern
automatic failover from the primary to the mirror database. Figure 17-1 shows a refer-
ence diagram for a database mirroring configuration.
Figure 17-1 Database mirroring components
Application
SQL Server
Principal
SQL Server
Mirror
Witness
C1762271X.fm Page 618 Friday, April 29, 2005 8:02 PM
Lesson 1: Understanding Database Mirroring Roles 619
Principal Role
The database you configure in the principal role becomes the source of all transactions
in a data mirroring session. The primary database is recovered, it allows connections,
and applications can read data to and write data from it. Note that you must specify
the Full recovery model for the database to participate in a database mirroring session,
a requirement that Lesson 2 covers in more detail.
NOTE Serving the database
When an instance has a database that allows transactions to be processed against it, it is said to be
“serving the database.”
Mirror Role

The database you define in the mirror role is the partner of the primary database and
continuously receives transactions from the principal database. The database mirror-
ing process is constantly replaying transactions from the primary database into the
transaction log and flushing the transaction log to the data files on the mirror data-
base so that the mirror database includes the same data as the primary database. The
mirror database is in a recovering state, so it does not allow connections of any kind,
and transactions cannot be written directly to it. However, you can perform a database
snapshot against a mirror database to give users read-only access to the database’s
data at a specific point in time. (See Chapter 16, “Managing Database Snapshots,” for
information about database snapshots.)
NOTE Transient operating states
The principal and mirror roles are transient operating states within a database mirroring session.
Because the databases are exact equivalents and are maintained in synchronization with each
other, either database can take on the role of principal or mirror at any time.
Witness Server
The witness server is the third and optional role you can configure within a database
mirroring session. You use this server to implement automatic failure detection and
failover. You configure the witness server by using the High Availability operating mode,
which Lesson 4 discusses. Although database mirroring allows a principal and mirror
to occur only in pairs (for example, a principal cannot have more than one mirror, and
vice versa), a witness server can service multiple database mirroring pairs. Each
C1762271X.fm Page 619 Friday, April 29, 2005 8:02 PM
620 Chapter 17 Implementing Database Mirroring
database mirroring pair that a witness server services has a single row of information
in the sys.database_mirroring_witnesses catalog view. The sole purpose of the witness is
to serve as an arbiter within the High Availability operating mode to ensure that the
database can be served on only one SQL Server instance at a time. If a primary data-
base fails, and the witness confirms the failure, the mirror database can take the pri-
mary role and make its data available to users.
IMPORTANT Database-level vs. server-level roles

Database mirroring’s principal and mirror roles occur at a database level and must be defined
within SQL Server 2005 instances that are either Standard or Enterprise Edition. However, you
define the witness role at an instance level. The instance of SQL Server 2005 that you use for the
witness server can be any edition, including SQL Server Express Edition, which is why we refer to a
principal or mirror database but a witness server.
Quick Check
■ What are the three database mirroring roles and what functions do they
serve?
Quick Check Answer
■ The principal database is currently serving the database to applications.
■ The mirror database is in a recovering state and does not allow
connections.
■ The optional witness server is an instance of SQL Server that is used for
automatic failure detection and failover from a primary to a mirror
database.
Lesson Summary
■ A database participating in a database mirroring session can be in one of two
roles: principal or mirror.
■ The principal database is the database that allows connections and transactions
to be processed.
■ The mirror database is inaccessible to applications and receives transactions
sent from the principal database.
C1762271X.fm Page 620 Friday, April 29, 2005 8:02 PM
Lesson 1: Understanding Database Mirroring Roles 621
■ The witness is a SQL Server instance that functions as an arbiter within a data-
base mirroring session. This is an optional component that you use when you
want to implement automatic failure detection and failover.
Lesson Review
The following questions are intended to reinforce key information presented in this
lesson. The questions are also available on the companion CD if you prefer to review

them in electronic form.
NOTE Answers
Answers to these questions and explanations of why each answer choice is right or wrong are
located in the “Answers” section at the end of the book.
1. Which role is valid for database mirroring?
A. Publisher
B. Principal
C. Primary
D. Monitor
2. Which of the following are valid actions for a witness? (Choose all that apply.)
A. Arbitrates a failover for the High Protection operating mode
B. Arbitrates a failover for the High Availability operating mode
C. Serves the database when the principal and mirror are offline
D. Services multiple database mirroring sessions
C1762271X.fm Page 621 Friday, April 29, 2005 8:02 PM
622 Chapter 17 Implementing Database Mirroring
Lesson 2: Preparing Databases for Database Mirroring
You configure database mirroring on a database-by-database basis. Each database you
define must use the Full recovery model to participate in a database mirroring ses-
sion. And you must initialize each mirror database to ensure that it is synchronized
with the principal before you start the mirroring session. This lesson walks through
the four general steps you need to take to prepare for database mirroring:
1. Ensure that databases are set to use the Full recovery model.
2. Back up the primary database.
3. Restore the database to the instance hosting the mirror database by using NORE-
COVERY.
4. Copy all necessary system objects to the instance hosting the mirror database.
After this lesson, you will be able to:
■ Perform the prerequisite steps for enabling database mirroring.
Estimated lesson time: 10 minutes

Recovery Model
SQL Server offers three recovery models for databases: Simple, Bulk-Logged, and Full.
The Simple recovery model minimally logs transactions, removing the inactive por-
tion of the transaction log at each checkpoint. The Bulk-Logged recovery model does
not fully log certain operations such as BULK INSERT, BCP, or CREATE INDEX oper-
ations. Because database mirroring maintains both the primary and mirror databases
as exact duplicates, including synchronizing all internal structures such as Log
Sequence Numbers (LSNs), the Simple and Bulk-Logged recovery models are incom-
patible with database mirroring. Therefore, the only recovery model that a database
can use to participate in database mirroring is the Full recovery model.
NOTE Full recovery model required
You cannot configure database mirroring if the participating databases are not using the Full recov-
ery model. In addition, you cannot change the recovery model of a database that is participating in
database mirroring.
C1762271X.fm Page 622 Friday, April 29, 2005 8:02 PM
Lesson 2: Preparing Databases for Database Mirroring 623
Backup and Restore
Because the principal and mirror databases are duplicates of each other, a mechanism
is needed to ensure that both databases are initialized to the same state. The process
of initialization for database mirroring involves performing a backup of the principal
database and restoring it to the mirror.
When restoring the database to the mirror, it is essential that you specify the NORE-
COVERY option for the RESTORE command, which guarantees that the starting state
of the mirror reflects the state of the principal database, including the LSNs.
You will find that the backup and restore process consumes the most amount of time
during database mirroring configuration. However, you probably will not be able to
take the primary database offline to initialize database mirroring. Instead, because the
database on the mirror is in an unrecovered state, you can apply a chain of transaction
logs to bring the mirror up-to-date.
BEST PRACTICES Initializing the mirror

Instead of performing a backup to initialize the mirror, I always use the last full backup of the pri-
mary database and then apply all subsequent transaction logs. After all log backups are taken, I
execute a final transaction log backup to capture all remaining transactions and then initiate data-
base mirroring. An alternative method uses log shipping to maintain the two databases in synchro-
nization and as the initialization mechanism for database mirroring. In this case, you might still
have to apply at least one transaction log backup before you can initiate the database mirroring
session.
BEST PRACTICES Backup/restore and log shipping
For more information about backup/restore and log shipping, please refer to Chapter 11, “Backing
Up, Restoring, and Moving a Database,” and Chapter 18, “Implementing Log Shipping.”
Copy System Objects
Database mirroring operates at a database level, so it is not responsible for any other
objects on the server. So although you can configure database mirroring to automati-
cally fail over to the mirror database, to allow applications to function after a failover,
you must ensure that all other objects are transferred to the instance hosting the mir-
ror database.
The most common objects that require transfer are the logins that allow applications
to authenticate for database access. You can also have linked servers, SQL Server
C1762271X.fm Page 623 Friday, April 29, 2005 8:02 PM
624 Chapter 17 Implementing Database Mirroring
Integration Services (SSIS) packages, SQL Server Agent jobs, customer error mes-
sages, or other objects configured on the server. Copying all of these objects to the
instance hosting the mirror database is the final step in the initialization process.
NOTE Using SSIS tasks to transfer objects
To transfer objects to the instance hosting the mirror database, you can use SSIS, which includes
the Transfer Logins task for transferring logins from one instance of SQL Server to another while
keeping any passwords encrypted. SSIS also provides tasks for transferring SQL Server Agent jobs,
error messages, and other types of objects.
Quick Check
■ What is the process for preparing a database to participate in a database

mirroring session?
Quick Check Answer
■ Change the recovery model to Full, back up the primary database, restore
to the instance hosting the mirror database with the NORECOVERY option,
and then copy all system objects such as logins and linked servers.
PRACTICE Preparing Databases for Database Mirroring
In this exercise, you will practice preparing databases for database mirroring using
the AdventureWorks database.
1. Connect to the instance hosting the AdventureWorks database that you want to
use as the principal database.
2. Right-click the AdventureWorks database and choose Properties. Select the
Options page.
3. From the Recovery Model drop-down list, select Full. Click OK.
4. Back up the AdventureWorks database.
5. Copy the backup to the machine running the instance on which you want to
host the mirror database.
6. Restore the AdventureWorks database, ensuring that you specify not to recover
the database.
C1762271X.fm Page 624 Friday, April 29, 2005 8:02 PM
Lesson 2: Preparing Databases for Database Mirroring 625
7. Back up the transaction log on the AdventureWorks database, copy the backup to
the machine running the instance in which the mirror database is being hosted,
and restore the transaction log.
8. Transfer to the instance hosting the mirror all logins, jobs, linked servers, and
other objects external to the database that are needed for the application to
work.
Lesson Summary
■ Database mirroring maintains synchronization between the two databases in
the mirroring session.
■ All databases that participate in database mirroring must be set to the Full recov-

ery model to ensure that all transactions are applied to the mirror.
■ You then must initialize the mirror by restoring a backup, ensuring that the
NORECOVERY option is specified.
■ Because database mirroring is responsible only for copying the contents of a
database to the server hosting the mirror database, you must separately copy
over all other server objects, such as logins, linked servers, and jobs.
Lesson Review
The following questions are intended to reinforce key information presented in this
lesson. The questions are also available on the companion CD if you prefer to review
them in electronic form.
NOTE Answers
Answers to these questions and explanations of why each answer choice is right or wrong are
located in the “Answers” section at the end of the book.
1. Which of the following is a valid step for preparing a database to participate in a
database mirroring session? (Choose all that apply.)
A. Configure distribution.
B. Back up the database.
C. Restore the database with RECOVERY.
D. Restore the database with NORECOVERY.
C1762271X.fm Page 625 Friday, April 29, 2005 8:02 PM
626 Chapter 17 Implementing Database Mirroring
2. Which database setting is valid for database mirroring?
A. Full recovery model
B. 80 compatibility level
C. Read only
D. Bulk-Logged recovery model
C1762271X.fm Page 626 Friday, April 29, 2005 8:02 PM
Lesson 3: Establishing Endpoints 627
Lesson 3: Establishing Endpoints
SQL Server 2005 introduces a stronger, revamped, multilayer security model. The first

layer of security occurs at the connection point to an instance. And endpoints control
the capability to connect to an instance. Because database mirroring relies on connec-
tivity among up to three instances of SQL Server 2005, you must establish endpoints
to enable communications among these instances. In this lesson, you review the con-
cept of endpoints, walk through endpoint options, and see how to configure end-
points specifically for database mirroring.
After this lesson, you will be able to:
■ Explain endpoint configuration options and best practices.
■ Create endpoints for database mirroring.
Estimated lesson time: 10 minutes
Endpoint Types
In SQL Server 2005, you can create two types of endpoints: TCP or HTTP. Database
mirroring uses TCP endpoints for communications. HTTP endpoints, on the other
hand, service SOAP requests.
MORE INFO HTTP endpoints
For information about HTTP endpoints, see Chapter 13, “Working with HTTP Endpoints.”
Along with a type definition for an endpoint, you specify a payload. TCP endpoints can
have a payload of TSQL, SERVICE_BROKER, or DATABASE_MIRRORING. For a database
mirroring session, you create TCP endpoints with a payload of DATABASE_MIRRORING.
You create an endpoint at the SQL Server instance level instead of at the database level.
So for each SQL Server instance, you can create only one endpoint, which has a payload
of DATABASE_MIRRORING.
MORE INFO Endpoints
For more information about endpoints, see the SQL Server 2005 Books Online article “CREATE
ENDPOINT (Transact-SQL).” SQL Server 2005 Books Online is installed as part of SQL Server 2005.
Updates for SQL Server 2005 Books Online are available for download at www.microsoft.com/
technet/prodtechnol/sql/2005/downloads/books.mspx.
C1762271X.fm Page 627 Friday, April 29, 2005 8:02 PM
628 Chapter 17 Implementing Database Mirroring
Endpoint Security

Endpoints provide multiple layers of security that you can configure for your needs.
The first level of security is in the type and payload definition, as you just saw. When
you create an endpoint for a database mirroring session, the endpoint will not
respond to any requests other than for database mirroring. The endpoint will refuse
any HTTP, Transact-SQL, or Service Broker requests.
The second layer of security is the TCP configuration of the endpoint. Each TCP end-
point requires that you specify a port number. The default port number for a TCP end-
point is 5022. You then configure the Listener IP for the TCP endpoint. By default, the
endpoint accepts connections on any valid IP address (the ALL option). But to further
restrict the requests to which this endpoint responds, you can specify a particular IP
address for it to listen to for requests.
BEST PRACTICES Port numbers
Because port 5022 is the default port number for a TCP endpoint, you should specify a different
port number. Not using the default port number helps foil potential hackers—or at least makes
their job more difficult—by requiring them to use a port scanner instead of just blindly connecting
to port 5022 for a denial of service attack (DoS) or other hacking attack. However, the general rec-
ommendation is to leave the Listener IP set to the default of ALL because a given instance could
have multiple database mirroring sessions running.
The third and fourth layers of security for an endpoint are the authentication method
and the encryption setting. You can use either Microsoft Windows–based authentica-
tion or certificates. You specify Windows-based authentication by selecting the
NTLM, KERBEROS, or NEGOTIATE option. The NEGOTIATE option causes the
instances to dynamically select the authentication method. You can set up certificate-
based authentication by using a certificate from a trusted authority or by generating
your own Windows certificate.
BEST PRACTICES Authentication
When all database mirroring instances reside within a single domain or across trusted domains, you
should use Windows-based authentication. When instances span nontrusted domains, you should
use certificate-based authentication.
All communications between endpoints can be encrypted, and you can specify which

encryption algorithm to use for the communications. The default algorithm is RC4,
but you can specify the much stronger Advanced Encryption Standard (AES)
algorithm.
C1762271X.fm Page 628 Friday, April 29, 2005 8:02 PM
Lesson 3: Establishing Endpoints 629
BEST PRACTICES Encryption
Use RC4 for minimal encryption strength and best performance. Use AES if you require strong
encryption, but note that this algorithm requires more calculation overhead and will affect
performance.
The fifth and sixth layers of security regard state options for an endpoint. You have to
grant CONNECT authority to an endpoint for a connection to be established. Addi-
tionally, you must set the state of the endpoint to STARTED. An endpoint with a state
of STOPPED returns an error for any connection attempt, whereas an endpoint with
a state of DISABLED does not respond to any request. The default option is STOPPED.
Database Mirroring Endpoints
Endpoints that support database mirroring are a special implementation of a TCP
endpoint and have the following characteristics:
■ Endpoint type of TCP
■ Payload of DATABASE_MIRRORING
■ Only one endpoint supporting database mirroring allowed per SQL Server
instance
Database mirroring endpoints establish a seventh layer of security through the use of
the ROLE option. You can specify that an endpoint be a PARTNER, WITNESS, or ALL.
An endpoint specified as PARTNER can participate only as the principal or the mirror.
An endpoint specified as WITNESS can participate only as a witness. An endpoint
specified as ALL can function in any role.
NOTE Endpoints on Express Edition
If you are creating a database mirroring endpoint on SQL Server 2005 Express Edition, it will
support only a role of WITNESS.
The following Transact-SQL example shows how to create a database mirroring

endpoint:
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED);
ALTER ENDPOINT [Mirroring] STATE = STARTED;
C1762271X.fm Page 629 Friday, April 29, 2005 8:02 PM
630 Chapter 17 Implementing Database Mirroring
This code creates an endpoint to service database mirroring sessions on port 5022,
responding to requests from all valid IP addresses. The ROLE = PARTNER option spec-
ifies that the endpoint allows only databases hosted on this SQL Server instance to
participate as a principal or mirror using the RC4 encryption algorithm.
NOTE Configuring database mirroring
You typically configure database mirroring within SQL Server Management Studio (SSMS) from the
Database Properties, Mirroring page. On this page, you click Configure Security, which launches the
Configure Database Mirroring Security Wizard that lets you specify several options. For example,
you can use this wizard to specify whether you plan to use a witness server instance in your
mirroring configuration. When you’re finished with your selections, the wizard executes the two
commands shown in the preceding example—CREATE ENDPOINT and ALTER ENDPOINT—against
each instance hosting a database that participates in a database mirroring session.
Quick Check
■ What are the seven levels of security provided by TCP endpoints servicing
database mirroring sessions?
Quick Check Answer
■ The first layer is the type and payload definition.
■ Layer two defines the TCP options of the port number and Listener IP.
■ Layer three is the authentication method required for the endpoint.
■ Layer four adds encryption options for all communications between
partners.
■ Layer five requires that the account authenticating the connection have
CONNECT permissions.

■ Layer six specifies STATE options that determine whether the endpoint will
allow or respond to connection requests.
■ Layer seven in the endpoint security model restricts the database mirroring
roles that an endpoint supports.
PRACTICE Establishing Endpoints for Database Mirroring
In this practice, you establish the endpoints required for a database mirroring session.
You configure endpoints for a principal, a witness, and a mirror to allow the creation
of a database mirroring session using any operating mode.
C1762271X.fm Page 630 Friday, April 29, 2005 8:02 PM
Lesson 3: Establishing Endpoints 631
1. Connect to the instance hosting the AdventureWorks database that you plan to
use as the principal database.
2. Right-click the AdventureWorks database and choose Properties.
3. Select the Mirroring page.
4. Click Configure Security to launch the Configure Database Mirroring Security
Wizard.
5. On the first screen, the splash screen, select the Do Not Show This Starting Page
Again check box. Click Next. You will now define endpoints for all three
database mirroring roles: principal, mirror, and witness.
6. On the Include Witness Server page, verify that Yes is selected. This option
enables you to configure an endpoint’s security for the witness server instance.
Click Next.
7. On the Choose Servers To Configure page, you see that the Principal Server
check box is selected and unavailable because it is assumed that you are running
the wizard from that instance. Verify that the Mirror Server Instance and Witness
Server Instance check boxes are also selected. Click Next.
8. On the Principal Server Instance page, by default, the Principal Server instance is
already selected. In the Listener Port text box, specify a port number. In the
Endpoint Name text box, type a name for the endpoint. Also verify that the
Encrypt Data Sent Through This Endpoint check box is selected to ensure

secured communications. Click Next.
NOTE Retrieving endpoint information
If an endpoint for database mirroring has already been created for the instance, SQL Server
will retrieve this information and display it in this screen; you cannot edit this information.
9. On the Mirror Server Instance page, click Connect, specify the instance name
and login credentials for the instance on which you want to host the mirror data-
base, and then click Connect. This creates a connection to the instance hosting
the mirror. Specify the port number and a name for the endpoint, and select the
Encrypt Data Sent Through This Endpoint check box to ensure secure commu-
nications. Click Next.
BEST PRACTICES Specifying an endpoint name
I always specify Mirroring as the endpoint name, which standardizes the naming convention
for these types of endpoints so that I can easily distinguish them from other types of
endpoints.
C1762271X.fm Page 631 Friday, April 29, 2005 8:02 PM