SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 15 of 365 Next >

Microsoft SQL Server includes several technologies that not only provide protection for your databases, but also allow you to move data from one location to another for other purposes. Database Mirroring is one of those technologies.

There are various levels of protection your applications need, and SQL Server provides a lot of options to help you develop your shop's level of recovery. All of these options come with varying degrees of complexity and cost. You should work with your management staff to help them understand what the risks your application environment faces, and what you can do about those risks. I’ve covered the process of disaster recovery planning in another tutorial, and in this one I'll spend a little time on a specific recovery mechanism that SQL Server gives you: Database Mirroring. I’ll cover an overview of the feature and a simple two-system test you can try out on your own (testing) servers.

Database Mirroring is a technology that was introduced in SQL Server 2005 (Service Pack 1) that transfers the data in the database log from one server to another.

Database Mirroring Background

Database Mirroring is used to transfer an entire database between just two systems. If you need to transfer the database to more than one system, you should investigate clustering, replication, log-shipping or other features.

Although you can use Database Mirroring to copy a database from one system to another to provide security similar to a SQL Server Cluster, it doesn't require the same hardware on both systems. You just need to ensure that you have enough room to handle the database and log transfers for the source data.

Another difference between Clustering and Database Mirroring is that the latter technology doesn't alter the identity of either server. Both servers remain independent of each other. Depending on your needs, this is either a good thing or a bad thing. On the positive side, there are no tricky network or naming situations that you need to worry about. On the negative side, although the transfer of data is automatic, you need to either code your applications to detect the change or manually change the clients to point to the new data location when the primary system fails to the standby system.

A final comparison of Database Mirroring and Clustering is that you can mirror a single database — you don’t need to copy the entire Instance of SQL Server with all of its databases as in Clustering.

For Database Mirroring to work, the database you're mirroring must be in the FULL recovery model, where the system writes every change through the log. That makes sense, because the whole process is based around sending the log files from one system to another. What you need to be careful about are operations that avoid the log. Since BULK LOGGED records don't go through the log, they can't be transferred by the Database Mirroring Process.

The systems involved in the process need to trust each other. If you're on the same domain this usually isn't a problem, but if the systems aren't then you'll have to set up a security certificate between them. I'll show you how to do that in another tutorial.

Database Mirroring Terminology

There are a lot of terms in Database Mirroring, some of which are shared with other technologies, so the place to start is with the definitions of the key concepts. The first are the names of the servers involved in the process. The server that houses the database you want to mirror is called the Principal. The server that receives the data and acts as the backup is called the Mirror. You can involve a third server to watch the other two, which is called a Witness. If you use a Witness server, it can automatically set the Mirror to become the Principal for you. Otherwise, you'll have to manually do that. All of these servers are called Partners.

The second set of terms deals with the level of protection you're after, called the Safety Level. If you want the switchover from Principal to Mirror automatically, that's called High Availability. To do that the Principal needs to be able to talk to the Mirror constantly, and you'll need that third server (the Witness). If you are willing to cut over the systems manually, then you can use a mode called High Protection, where you don't have to include a Witness server. The last mode is High Performance, where you don't have to use a Witness. In this mode you have to manually fail over the system, and you aren't completely protected because some data might be lost in the interval of time between transmissions.

Example: Setting up Database Mirroring

The process for setting up Database Mirroring has three basic steps, although Books Online has about six to include the optional features. You can use a wizard to set up the mirroring, so I won't cover those screens in this tutorial. Instead I'll show you the command-line version of the same process.

Step One: Back Up and Restore the Mirrored Database

First, take a backup of the database you want to mirror on the server that you will designate as the Principal. Remember, the database must be in the FULL recovery model for the process to work. Transfer the backup file to the server you will designate as the Mirror, and restore it, but leave it in the WITH NO RECOVERY mode. That leaves the database open to more data from the log restore process. If you’re not familiar with restoring databases and the WITH NO RECOVERY mode, check the links at the end of this tutorial.

Set and Enable Endpoints on the Servers

Endpoints are a new feature starting in SQL Server 2005 that allow direct network port connections for processes and users. You'll need one endpoint on each of the servers involved, and then you'll need to turn the port monitoring on. For this tutorial, I'll just set up a Principal and a Mirror on two servers using the same port.

NOTE

I’m using a pretty simple example here, and my two servers are in the same domain, with the same user account starting the SQL Server services on both systems. That’s important — since those services are started with the same account, I don’t have to grant special permissions to make all of this work. If your systems are not in this configuration, don’t worry — you can create accounts and grant permissions on the endpoints, use certificates and more. There’s a deeper explanation in Books Online, once you finish this simple tutorial.

Here's an example that sets up an endpoint on a test server that uses TCP/IP port 5025:

CREATE ENDPOINT DBMirroringTest 
AS TCP (LISTENER_PORT = 5025)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = ENABLED);

This syntax creates and names an endpoint as a TCP listener on port 5025. It also specifies that this server will be a partner (not a witness), and that the communications between them will be encrypted.

I'm using a physically separate machine as the Mirror, so I can use the same port on that server. In fact, I can run the same statement again on that system to set up its endpoint:

CREATE ENDPOINT DBMirroringTest 
AS TCP (LISTENER_PORT = 5025)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = ENABLED);

If you want to try this out using two instances on the same server, change one of the port numbers above. Even though you are running multiple copies of the database server, the underlying Operating System is the same and that's where the port definitions in this case are.

Now that I have the ports, I have to tell them to listen on the network. I can do that with this command, run once on both Instances:

ALTER ENDPOINT DBMirroringTest STATE = STARTED;

Define and Enable the Database for Mirroring

With the database backed up and restored and the endpoints created and listening, all I have left to do is enable a database for mirroring and in the process define the Principal and Mirror servers.

You'll need to obtain the fully-qualified domain name (FQDN) for the servers involved. My test servers are principalsvr.buckwoody.com and mirrorsvr.buckwoody.com. I used a couple of virtual servers for this test, so I made the names easy to follow.

In my case, I'm going to mirror a database called TestDB that is located on the principalsvr server. You can follow along on your servers, just make sure you watch the server and database names, and use the port numbers you set up earlier. I’ll start on the Principal server:

ALTER DATABASE TestDB
SET PARTNER = ’TCP://mirrorsvr.buckwoody.com:5025’;

Notice that the partner name (the Mirror) is prefaced with 'TCP://, and at the end I've got the port number I set up earlier.

Now I'll move over to the Mirror server, and you can do the same on yours. I repeat the same command, only I change the server name to point back to the Principal:

ALTER DATABASE TestDB
SET PARTNER = ’TCP://principalsvr.buckwoody.com:5025’;

I have one final command in this step. Earlier I spoke about the levels of safety. I have to select one of those levels to finalize the mirroring. There are two settings I can specify: FULL or OFF. With the FULL setting, I need either a Witness or at least a constantly connected network link, which maps to the High Availability or High Protection modes I mentioned. The OFF setting is for the lower level of protection, called High Performance. Since I've got a good network connection and no Witness, I'll stick with OFF. Keep in mind that this means I'll have to manually recover the system if I have a failure.

I'll switch back to the Principal server and run this code to set the safety level:

ALTER DATABASE TestDB SET SAFETY OFF;

That's all there is to it.

In future tutorials I'll explain how to manually recover the system when you're in this configuration. I'll also talk about other considerations in Database Mirroring. It’s important to note that basically what is happening is similar to log-shipping — in effect, the transactions from one system are constantly being “replayed” on the other system, which is NOT available for use. It’s being constantly restored, so the database isn’t open for users. There are ways around this (a little) which I’ll cover in another article.

InformIT Articles and Sample Chapters

To find out about how Database Mirroring compares with other High-Availability options in SQL Server, check out this section of Eric Brown's sample chapter, Enterprise Data Management in SQL Server 2005.

Books and eBooks

I cover more about High Availability strategies (including Database Mirroring) in my book, Administrator's Guide to SQL Server 2005.

Online Resources

For a more complicated treatment of this topic for using certificates and so on, check out this MSDN article.

< Back Page 15 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address