Database Mirroring in Azure

Not many people that I know like to try things that they are not familiar with because unfamiliar is scary. However, working in startup, like my current company, basically forces one to always learn more and learn faster. Hence, after getting approval from the top management, my senior and I migrated our web applications to Microsoft Azure.

Just when we thought we did everything beautifully, our instances on Azure went down for 72 minutes on 4 August, one month after the migration. The reason given by Microsoft team is that there was an issue in one of the clusters within the DC. 3 weeks later, our database instance on Azure went down again for 22 minutes because of a scheduled system update.

Fortunately, Microsoft Singapore is willing to guide us to make high availability in our web applications possible. I am very happy to have Chun Siong, Technical Evangelist from Microsoft Singapore, to help us out.

Last month, Chun Siong successfully to have the database mirroring setup for our database instances on Azure. Since he did all of the work himself, in order to learn and to master the database mirroring, I had to do everything myself from the beginning again.

In this post, I will share the mistakes I made when I tried doing database mirroring myself so that I won’t repeat the same mistakes again.

Beginning of the Journey

There is an easy-to-follow tutorial available on MSDN about how to implement database mirroring in Azure. I used it as a reference to setup one principle database server, one mirror database server, and one witness server within the same availability set.

Elements in my simple database mirroring setup.

Elements in my simple database mirroring setup.

Mistake #1: Firewall Blocking Remote Access of SQL Server

If I had read the tutorial carefully, I wouldn’t have to make this mistake because it’s mentioned in the beginning of the tutorial.

I found out this mistake only when I tried to connect to the mirror server from the principal database server. It kept throwing me the Error 1418 saying that the mirror server was not reachable. After reading a checklist of the error, I found out that it’s because I never create an inbound rule on Windows Firewall to allow the access of the SQL server.

Thanks Chun Siong for pointing it out also. =)

By the way, on the article about Error 1418 (http://msdn.microsoft.com/en-us/library/aa337361.aspx), there is a checklist to check if everything is done correctly. I copied and pasted it below for quick reference.

  1. Make sure that the mirror database is ready for mirroring.
  2. Make sure that the name and port of the mirror server instance are correct.
  3. Make sure that the destination mirror server instance is not behind a firewall.
  4. Make sure that the principal server instance is not behind a firewall.
  5. Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. If either endpoint is not started, execute an ALTER ENDPOINT statement to start it.
  6. Make sure that the principal server instance is listening on the port assigned to its database mirroring endpoint and that and the mirror server instance is listening on its port. If a partner is not listening on its assigned port, modify the database mirroring endpoint to listen on a different port.

If the items above are not helpful to you, there is also another detailed blog post about this Error 1418 written by Pinal Dave.

There is a need to allow the access of SQL server in three instances.

There is a need to allow the access of SQL server in three instances.

Mistake #2: Typo when Creating Certificates

In the tutorial, the recommended way to deploy database mirroring is to use certificates. After certificates of three servers are created, we need to grant login permission on each server to another two servers. That is when we will use the certificates to create a common login account id called DBMirroringLogin.

I had one typo in the password in one of the certificates.  only realized it at the very end when I tried to connect to my witness server. So, yup. Be careful during the database mirroring configuration steps. One small mistake can waste us time to find out why.

Grant login permissions to other two servers.

Grant login permissions to other two servers.

Mistake #3: Mismatch Edition of Principal and Mirror

I only had time to learn database mirroring using my personal account after work. So I screamed in my room at the moment when I realized that it is not allowed to have mirror server using Standard Edition while the principal is not using Standard Edition.

The mirror server instance cannot be Standard Edition if the principal server instance is not Standard Edition.

The mirror server instance cannot be Standard Edition if the principal server instance is not Standard Edition.

So in the end, I shut down the mirror instance and created another virtual machine which has Enterprise Edition SQL Server installed. Fortunately, it could be done quite fast on Microsoft Azure. I did not want to use back the old name so I named the new mirror server mydb-01-kagami.

Kagami means "mirror" in Japanese. (Image Credit: Lucky Star)

Kagami means “mirror” in Japanese. (Image Credit: Lucky Star)

Mistake #4: Three Virtual Machines Not in Same Availability Set

The principal database, witness, and mirror database instances need to be put inside the same availability set.

When I was deploying the database mirroring, I forgot to have the witness instance in the same availability set as principal and mirror. So end up I couldn’t successfully connect to the witness from the principal.

Three instances need to be in the same available set.

Three instances need to be in the same available set.

Work and Learn

I spent about three days in Microsoft office to learn from Chun Siong. I then took another one month to do it myself. Wait, what? One month, seriously? Don’t be surprised. As usual, I have only little time (about half an hour per day) after work to do my personal projects. Sometimes, once I reached my room from office, I just jumped into bed and fell asleep within minutes. So, in fact, I only spent about 15 to 20 hours on learning database mirroring myself. Hence, I am really glad that I have colleagues as well as friends from Microsoft to be willing to support me in my learning journey.

Finally, some little notes to myself and readers who want to try out database mirroring (on Azure).

  1. Be very careful during the whole database mirroring configuration process. Don’t have typo or set something wrongly. You may need to delete and create a new instance because of the mistakes;
  2. Witness (but not principal and mirror) can use Express Edition of SQL Server. So, to save cost, please use that;
  3. Set database to full recovery model before backing up the database on principal;
  4. Remember to enable named pipes;
  5. Use Database Mirroring Monitor to understand more about the status of mirroring session.
  6. Some good resources to refer to:
It's enjoyable to work in Microsoft Singapore office. You can see the beautiful MBS from there.

It’s enjoyable to work in Microsoft Singapore office. You can see the beautiful MBS from there.

Advertisements

One thought on “Database Mirroring in Azure

  1. Pingback: Journey to Microsoft Azure: Good and Bad Times | cuteprogramming

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s