[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Microsoft SQL Server 2008 High Availability: Installing Database Mirroring, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
by
Hemantgiri S. Goswami | January 2011 |
Enterprise Articles Microsoft
In this article, by Hemantgiri Goswami, author of Microsoft SQL Server 2008 High Availability, we will learn how to install database mirroring.
This article covers:
- Installing Database Mirroring
- Starting Database Mirroring
- Manual or forced failover
- Adding the Witness Server
Microsoft SQL Server 2008 High Availability
 |
Minimize downtime, speed up recovery, and achieve the highest level of availability and reliability for SQL server applications by mastering the concepts of database mirroring,log shipping,clustering, and replication
|
| Read more about this book |
(For more resources on this subject, see here.)
Introduction
First let's briefly see what is Database Mirroring. Database Mirroring is an option that can be used to cater to the business need, in order to increase the availability of SQL Server database as standby, for it to be used as an alternate production server in the case of any emergency. As its name suggests, mirroring stands for making an exact copy of the data. Mirroring can be done onto a disk, website, or somewhere else.
Now let's move on to the topic of this article – installation of Database Mirroring.
Preparing for Database Mirroring
Before we move forward, we shall prepare the database for the Database Mirroring. Here are the steps:
- The first step is to ensure that the database is in Full Recovery mode. You can set the mode to “Full Recovery” using the following code:
- Execute the backup command, followed by the transaction log backup command, and move the backups to the server we wish to have as a mirror.
- As we have a full database and log backup file, move them over to the Mirror server that we have identified.
- We will now perform the database restoration, followed by the restore log command with NORECOVERY.
I have run the RESTORE VERIFYONLY command after backup completes. This command ensures the validity of a backup file. It is recommended to always verify the backup.
It is necessary to use the NORECOVERY option so that additional log backups or transactions can be applied.
Installing Database Mirroring
As the database that we want to participate in the Database Mirroring is now ready, we can move on with the actual installation process.
- Right-click on the database we want to mirror and select Tasks | Mirror.....
- It will open the following screen. To start with the actual setup, click on the Configure Security... button.
- In this dialog box, select the No option as we are not including the Witness Server at this stage and will be performing this task later.
- In the next dialog box, connect to the Principal Server. Specify the Listener Port and Endpoint name, and click Next.
- We are now asked to configure the property for the Mirror Server, Listener port, and Endpoint name.
- In this step, the installation wizard asks us to specify the service account that will be used by the Database Mirroring operation.
- In the next dialog box, make sure that the configuration details we have furnished are valid. Ensure that the name of the Principal and Mirror Server, Endpoints, and port number are correct. Click Finish.
- Ensure that the setup wizard returns a success report at the end.
If a person is using local system account as a service account, he/she must use Certificates for authentication. Generally, these certificates are used by the websites to assure their users that the information is secured. Certificates are the digital documents that store digital signature or identity information of the holder for authenticity purpose. They ensure that every byte of information being sent over the internet/intranet/vpn, and stored at the server, is safe. Certificates are installed at the servers, either by obtaining them from the providers such as http://www.thwate.com or can be self-issued by Database Administrator or Chief Information Officer of the company using the httpcfg.exe utility. The same is true for SQL Server. SQL Server uses certificates to ensure that the information is secured and these certificates can be issued by self, using httpcfg.exe, or can be obtained from issuing authority.
| Read more about this book |
(For more resources on this subject, see here.)
Starting Database Mirroring
Once we are done preparing for and installing Database Mirroring, we can start with the actual Database Mirroring. Please follow the steps, as mentioned here:
- As soon as the setup wizard finishes, we will be presented with the option to start mirroring. Please note that the Operating mode is set as High safety without automatic failover (synchronous).
- In the next step, we can change the operating mode of the Database Mirroring we have configured and/or we can pause or remove the mirroring, if we wish to do so for any reason.
- As we are done with the Mirroring installation, let's check the status of the databases on the Principal and Mirror server.
In the following snapshot, the left-hand side shows the Principal Server; notice that the database status shows Principal, Synchronized. The right-hand side shows the Mirror server. Notice again that the database status is Restoring…., which means there are transactions that could be applied on the mirrored database, running in the single user mode.
The transactions are written in the synchronous mode of operating, and they commit on Principal and Mirror at the same time.
Manual or forced failover
Until now, we have learned how to install Database Mirroring. Now let's see how to failover manually just to ensure that the installation went fine and is working properly.
- Right-click the database name we have configured for mirroring, and from the options, select Tasks | Mirror. This will bring the main configuration dialog box for Database Mirroring. Once there, click on the Failover button. It will then ask us to confirm whether or not we wish to proceed with failover; click Yes.
- In the next screenshot, notice that the status of the database on the instance [local\SQL2008R2] has been changed to Mirror, Synchronized / Restoring…, whereas database status on the instance [local\SQL2K8LS] has been changed to Principal, Synchronized.
At this stage, [local\SQL2008R2] is Principal server and [local\SQL2008LS] is the Mirror server.
Adding the Witness Server
We have not included the Witness Server while we installed Database Mirroring, as we want it to be added later, once we finish with the installation. The reason was, what if we don't have a server that we can configure as a Witness Server while installing Database Mirroring? Can we add a Witness Server later? Well, the answer is Yes, and here is how we go about adding a Witness Server:
- Right-click on the database we have configured for Database Mirroring and select Tasks | Mirror. From the setup dialog box, click on configuration. It will ask us if we want to include the Witness Server; this time, select the Yes option and then click Next.
- In the next dialog box, choose the server to configure. Here, select Witness Server instance and click Next.
- As we have failed over, the Principal server instance is now [local\SQL2K8LS]. Click Next.
| Read more about this book |
(For more resources on this subject, see here.)
On the Mirror server, we have named the endpoint as Mirror when we installed Database Mirroring. So, do not get confused by these names as these names have been chosen for better understanding.
Summary
After reading through this article, I am sure a reader will be very much clear about the installation procedure of Database Mirroring.
Further resources on this subject:
- Microsoft SQL Server 2008 - Installation Made Easy[article]
- Installation And Configuration of Microsoft Content Management Server: Part 1[article]
- Microsoft LightSwitch Application using SQL Azure Database[article]
About the Author :
Hemantgiri S. Goswami
Hemantgiri S. Goswami is a SQL Server consultant based in Surat, India. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of http://www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com
He has more than 11 years of experience in the IT industry, of which nine years have been as a hard core DBA focusing on High Availability area. During his career, he has worked with the top five IT giants of India. In addition to SQL Server, Hemantgiri also possesses excellent knowledge of Windows Server OS(s) and Networking.
In his free time, he prefers to watch cartoons, read and sometimes sketches.
You can reach him via his blog – http://www.sql-server-citation.com , twitter – http://www.twitter.com/ghemant or by writing to him at Hemantgiri [at] sql-server-citation.com
Books From Packt
|
|
Post new comment