Hi all, this is my first step into the world of blogging so please bear with me...
In this post, as the title suggest I will walk you through the creation of database mirroring.
Before we start mirroring a database it is better to know answers for some general questions.
Q. What is mirroring?
A. Today user experience is given the utmost priority. We have an umpteen number of applications running with a database in the backend. Imagine an application running on a database and the database crashes; it will be a nightmare for the company. There will be data loss, which can be restored using a backup, but the application will not be available till the database is restored. And this can cost the company a fortune. But then mirroring comes to the rescue. Mirroring as the name suggests is the act of creating a copy of the database. But it is actually a lot more than that. Other than creating a copy of the database it also provides more availability by taking over as the principal database in case of a database failure.
Q. What is a principal database?
A. The database which is to be mirrored is known as the principal database.
Q. What is a mirror database?
A. The database on which the principal database is mirrored is called the mirror database?
Q. What is the use of mirroring?
A. The main use of mirroring is the make the database more available by creating a backup server which can readily be made available in case of a database failure.
Q. How does mirroring work?
A. There are two modes in which mirroring works:
a. Synchronous mode – Every transaction on the principal database is also executed on the mirror database. The principal database waits for the mirror database to complete the transaction because of which there is no possibility of data loss.
b. Asynchronous mode – Every transaction on the principal database in also executed on the mirror database but the principal database does not wait for the mirror database to complete the transaction. Because of which there is a possibility of some data loss.
Q. Can principal and mirror database be on the same machine?
A. No the principal and mirror database must be on different machines.
Now that we have got our basics right let us proceed and create a mirror database for our existing principal database.
Mirroring is a three step process:
1. Backup the principal database and restore it on the mirror database.
a. Backup includes a full backup and a transactional log backup.
b. CAUTION: Restore the backup onto the mirror database in NO RECOVERY mode.
2. Create endpoints on the principal and mirror database.
a. Endpoint is basically an address (the address of the machined and the prot) using which the two databases communicate.
b. The endpoints must provide appropriate access to connect to it
3. Set partner on the mirror and principal servers.
a. Set partner on the mirror server first and then on the principal server
b. Setting partner on the principal server automatically starts the mirroring process
It would be best to use the mirroring wizard for points 2 and 3. Please refer to the link http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.htm to know how to use the mirroring wizard.
But we can also perform steps 2 and 3 from a SQL script. Please refer to the link http://msdn2.microsoft.com/en-us/library/ms179306.aspx for more details.
The above approach should work fine when both the principal server and mirror server are in the same domain and can use windows credentials to authenticate each other. At other times, when the two machines are not in the same domain, we might have to use certificates to authenticate each other. Please refer to the following link http://msdn2.microsoft.com/en-us/library/ms191140.aspx for more details on using certificates for mirroring.
If you are lucky enough the above two approaches (with windows credentials and with certificates) will work for you in the first go. But if you are among the unlucky people like me you will encounter the error “Error: 1418 - Microsoft SQL Server - The server network address cannot be reached or does not exist. Check the network address name and reissue the command. The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.”
The biggest problem with the above error is the vagueness of the error message. From the error message you might end up interpreting that the principal server is probably not able to locate the mirror server. But the reasons might be varied.
Please refer to the link http://alan328.com/SQL2005_Database_Mirroring_Tutorial.aspx for solution to some common problems.
Also here is a check list of the possible reasons of the failure:
1. The firewall at either end is blocking the port
a. Either disable the firewall or add the SQL (default is 1433) and endpoint port the list of exceptions
2. Remote connections might not be enabled
a. Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration and enable TCP/IP protocol
b. Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Client Configuration and enable TCP/ IP protocol
3. You can also refer to the following links for troubleshooting
No comments:
Post a Comment