Installing SQL Server 2012 in a Mirror Setup

In my series on creating a SharePoint farm in Windows Azure we last time created the virtual machines for the two front-end servers, the two application servers and the three servers to be used by SQL Server.

In this sixth post we will look at how to enable SQL Server for high availability by enabling them in a mirror setup.

First of all, this is not really directly related to Windows Azure. The steps taken are the same as you would, should you enable a mirror on-premises, but as it is not something you do every day – at least I don’t – I thought it might be of interest.

Due to several factors, the structure on the underlying storage in Windows Azure being one of them, you cannot run a SQL Server Cluster in Windows Azure, so if you require redundancy and fast failover, you need something else, like a mirror.

There are two modes of database mirroring – synchronous and asynchronous. With synchronous mirroring, transactions cannot commit on the principal until all transaction log records have been successfully copied to the mirror (but not necessarily replayed yet). This guarantees that if a failure occurs on the principal and the principal am mirror are synchronized, committed transactions are present in the mirror when it comes online – in other words, it is possible to achieve zero data loss.

Synchronous mirroring can be configured to provide automatic failover, through the use of a third SQL Server instance called the witness server (usually hosted on another physically separate server). The sole purpose of the witness is to agree (or not) with the mirror that the principal cannot be contacted. If the witness and mirror agree, that mirror can initiate failover automatically. If synchronous mirroring is configured with a witness, the operating mode is known as high-availability mode and povides a hot standby solution. When no witness is defined, the operating mode is known as high-safety mode, which provides a warm standby solution.

With asynchronous mirroring there is no such guarantee, because transactions can commit on the principal without having to wait for database mirroring to copy all the transaction’s log records. This configuration can offer higher performance because transactions do not have to wait, and it is often used when the principal and mirror servers are separated by large distances (that is, implying a large network latency and possible lower network bandwidth). Consequently, the operating mode is also known as high-performance mode and provides a warm standby solution.

If a failure occurs on the principal, a mirroring failover occurs, either manually (in the high-performance and high-safety modes) or automatically (only in the high-availability mode). The mirror database is brought online after all the transaction log records have been replayed (that is, after recovery has completed). The mirror becomes the new principal and the applications can reconnect to it. The amount of downtime required depends on how long it takes for the failure to be detected and how much transaction log needs to be replayed before the mirror database can be brought online.

In the previous post on the subject we created three VMs and attached an extra disk. As was the case for the two domain controllers, you need to log on to the SQL Servers and attach the disk.

Before we begin I must make a comment about the screen shots. If some of the text is missing it is because I have removed it due to confidentiality related issues. I apologize.

First task is to install .NET 3.51. If this is not done the installation of SQL Server might hang. We do this by using Add Feature.

Open the Server Manager and select Manage and then Add Roles and Features.

image

The Add Roles and Features Wizard will be displayed

image

Click Next.

In the Select installation type dialog ensure the correct option is selected.

image

Click Next.

Select the server in the Select destination server dialog. Then click Next.

image

Select the feature (.NET Framework 3.5 Features)

image

 

image

Click Next.

You get a change to confirm the selections.

image

If you are satisfied click Install.

The installation will now begin. You can either Close the dialog right away or you can wait until the installation has completed.

image

We are now ready with the actual installation of SQL Server.

Download and attach media (here SQL Server 2012 SP1 Enterprise Edition)

image

Run the Setup.exe file.

Select Installation in the menu to the left.

image

On the installation page, select the New SQL Server stand-alone installation.

image

This will install the setup support files. Once that is done, click OK.

image

Accept the suggested product key or enter the correct one.

image

Click Next.

In the License Terms dialog accept the terms and click Next.

image

Ensure that all is green in the Setup Support Rules dialog. If the Windows Firewall rule is yellow it is most likely because port 1433 is not open. It will have no influence on the installation, but may be an issue later on.

Click Next.

image

In the dialog for Setup Role select the top option (SQL Server Feature Installation)

image

Click Next.

On the dialog for the Feature Selection select the required features. In my case I did not require Analysis Services nor Reporting Services, but I did select to install the management tools.

image

Click Next.

The setup process will now determine if any process will be blocked. If all is green click Next.

image

Accept the default setting for the instance configuration.

imageAcc

Click Next.

You should have enough space for the installation of the actual bits.

image

Click Next.

If you have installed SQL Server 2008 and R2 you will notice that the default values for the Server Configuration has changed.

You can keep the default settings, but if you plan to use this server in a mirror setup – which is the subject of this blog post – I will recommend that you use a domain account. It will make setting up the security during the mirror configuration so much easier. The reason being that the local account on Server 1 does not know anything about the local account on Server 2.

image

If you just keep the default values you can always change them later using the SQL Server Configuration Manager.

Click Next.

In the Database Engine Configuration dialog on the Server Configuration tab keep the default value for the Authentication Mode.

image

Select the Data Directories tab.

Change the Data root directory to the additional disk we attached. Again, if this had been a production setup, you would spread your directories over a lot more drives.

image

Select the FILESTREAM tab.

You want to select both the Enable FILESTREAM for Transact-SQL access and the Enable FILESTREAM for file I/O access options. You don’t need to enable the last one.

image

Click Next.

image

In the Error Reporting option, click Next

Setup will run some additional checks. If all is green in the Installation Configuration Rules click Next.

image

Click Install.

image

The installation will begin and it is time to go and get a cup of coffee.

If all goes as expected, you should have a lot of green markers and you can close the dialog and exit the setup.

image

One down and two more to go. Repeat the above process for the other two SQL Servers. Once all are installed we will have the primary, mirror partner and witness servers and we are ready to enable the mirror.

However, before actually do this, we need to install SharePoint. The reason is that the mirror is enabled by backing up and restoring databases, hence we need something “in” SQL so to speak. As I am not a SharePoint person, I will refrain from trying to describe the process.

Therefore:

And we have a working SharePoint installed in to the primary SQL Server (in this case SP-SQL1).

First step is to ensure that all SQL logins are present on the primary and mirror server.

Then we must ensure that all databases are running with recovery mode set to full. RDP into SP-SQL1 (the primary) and open op SQL Server Management Studio.

Ensure there are no data connections to the SQL Server (you may want to close down the SP site).

Execute the following T-SQL to set recovery mode:

USE master;
GO
ALTER DATABASE AdminContent SET RECOVERY FULL;

We then first backup the database

USE master;
GO

BACKUP DATABASE AdminContent
TO DISK = ‘F:\BackUp\AdminContent.bak’
WITH FORMAT
GO

and afterwards the log:

USE master;
GO

BACKUP LOG AdminContent
TO DISK = ‘F:\BackUp\AdminContent_log.bak’

GO

Copy the files to the mirror partner (SP-SQL2). Ensure they are placed in the same location, e.g. F:\BackUp in the above example. It is not a requirement, but the syntax of the T-SQL is slightly different if the location is different.

Connect to the mirror partner (SP-SQL2) from the open Management Studio or RDP into the server and open SSMS from here.

First we restore the database

USE master;
GO

RESTORE DATABASE AdminContent
FROM DISK = ‘F:\BackUp\AdminContent.bak’
WITH NORECOVERY
GO

and then the log

USE master;
GO

RESTORE LOG AdminContent
FROM DISK = ‘F:\BackUp\AdminContent_log.bak’
WITH FILE=1, NORECOVERY
GO

We are now ready to enable mirroring. In SSMS right click on one of the databases and select Tasks and then Mirrror….

image

In the Database Properties dialog, click Configure Security.

image

The first step in the configuration wizard is to decide whether or not a witness server should be used. As want automatic failover, I select the Yes option and click Next.

image

In the dialog to choose what servers to configure, ensure that all three are selected.

image

Click Next.

During the configuration you will have to connect to each SQL Server.

As I was working from SP-SQL1 and this is going to by my Principal server I am already logged in and can just click Next.

image

Next select the Mirror server. This is going to be SP-SQL2. Click Connect and enter your credentials. When done click Next.

image

Repeat the steps for the Witness server.

image

Click Next.

You now have to set up the Service Accounts information. If you your SQL Servers are running under a domain account this is going to be easy. If not you will afterwards have to enable the local accounts on each server. Doable, but a lot more hassle.

Enter the required information for each server.

image

Click Next.

You have made it to the end of the wizard and can review the information.

image

Click Danish. Sorry Finish. Bad joke.

If all goes well you should see something like the figure below. Click Close to close the dialog.

image

When you close the dialog you can either start the mirror right away or you can do so later.

I just hit the Start Mirroring button.

image

If the mirror process is able to start you will return to the initial dialog and the status should be Synchronizing.

image

Looking in SSMS at the databases you can also see that mirroring has been set up and is active.

image

This was a really long post for which I apologize.

There is a lot more to SQL Server mirroring than the above, but I hope it will serve as an introduction and maybe enable people not working with SQL on a daily basis to get up and running more quickly.

About strobaek

.NET developer/architect. Runner, espresso drinker and lover of gourmet food.
This entry was posted in Azure. Bookmark the permalink.

20 Responses to Installing SQL Server 2012 in a Mirror Setup

  1. Arif says:

    Thank you mate, excellent instruction.
    I am very new with SQL 2012 and I have few questions.
    I have two SQL 2012 Servers as result one of them is principal and other one is mirror. No witness server available. In this case how should I configure the mirroring? As there is no witness server is available how the fail-over will take place?
    Thank you in advance.

  2. strobaek says:

    Hi Arif,

    Thank you for your kind words.

    You can mirroring between two SQL servers without the use of a witness server. The witness is only required if you want automatic failover, hence you need to do it manually. This can be done either through the SSMS (SQL Server Management Studio) or via T-SQL.

    When you initiate the configuration wizard for the mirror setup, you will be asked how many servers you have and if a witness should be used. Just de-select the witness option and continue through the setup.

    Since you are using SQL Server 2012 have you consider using AlwaysOn Availability groups instead? Unless your application does not support AlwaysOn I would go with this option for redundancy/high availability instead of mirroring.

    Karsten

  3. Arif says:

    Hi Karsten

    Thank you for your time and excellent support. We are using SQL 2012 Server Standard edition as a result AlwaysOn option is not available. Thank you again.
    Regards,
    Arif

  4. sudarshan reddy says:

    Hi Karsten

    Thank you very much for your document. It helps me a lot

  5. Zach says:

    Thanks for the great article. I’m having an issue with the restore of the databases. When I run the command you have specified the database stays in a “Restoring” state. If I specify with RECOVERY the db is running as normal, but then I cannot restore the log file. What am I doing wrong here?

    Zach

  6. Zach says:

    My bad it’s supposed to be in RECOVERY mode and then you can set up the mirror. =)

  7. Dominique says:

    Hello,

    I am trying to do the mirroring between two servers and a witness but when starting the Mirroring I am getting an error:
    – The server network address “TCP://Servername.doamin:5022 ” can not be reached or doesn’t exist.
    error: 1418
    The service account used is the same for the 3 servers
    The DB is on Principal and Mirror only , does it it to be on Witness?
    The ports 5022 works both ways between the 3 servers tested with TELNET.

    Any idea?
    Thanks,
    Dom

  8. strobaek says:

    Hi Zach,

    Sorry for never getting back to you. I’m glad you solved your problem.

    Have a nice weekend.

    Karsten

  9. strobaek says:

    Hi Dominique,

    Sorry for the late reply; I don’t know if you are still facing issues.

    I assume that you have enabled full recovery and created a backup of a database (and log) which have been restored on the mirror, all this prior to trying to enable the mirror?

    Did you get all green “lights” when configuring the endpoints? This is usually the problem if the Mirror cannot be started.

    Karsten

  10. said rashid says:

    Hi,
    my question is where is the best option to create sql instance for Witnes server:
    Principle server or mirror server?

  11. strobaek says:

    Hi Said,

    Sorry for the late reply. The location of the witness depends on your requirements. You can put in one one of the existing servers (principle or mirror) or a dedicated one. Load wise there is no difference. It really depends on your requirements to your availability.

  12. Ranajit says:

    Hi

    without domain account can configure mirror (high availability mode) ?
    If principal and Mirror server are different domain can configure , how ?

    Regards,
    Ranajit

  13. heka says:

    Hello, please help me i have a problèmes ans i am blocked. I have 3 machine with sql servrr 2012..I have enabled full recovery and created a backup of a database (and log) which have been restored on the mirror.but when i have tout choose thé miror instance

  14. Kishore M. K. says:

    Hi Karsten,
    I am facing the same issue as faced by Mr. Dominique. i.e. The server network address “TCP://Servername.doamin:5022 ” can not be reached or doesn’t exist. error: 1418.

    Also…
    1. Will Database Mirroring technique works on VMs ?
    2. Is any Window’s User Account role if SQL Server Systems are in different domain ?
    3. Does it any effect, if all SQL Server are using different port other than 1433 ?

  15. strobaek says:

    Hi Ranajit,

    Sorry for the very late reply. Yes, you can set mirroring up without using a domain account. It is slightly more tricky the reason being that you need to ensure the accounts used have access to all SQL Servers. If you search the web, there are articles on how to do this.

    Kind Regards,
    Karsten

  16. strobaek says:

    Hi,

    Have you checked the firewall settings on the SQL Servers? You need to open up for traffic on the required port.

    As to your questions.

    Re. 1. I’m not quite sure I understand your question. If the question is related to whether or not Database Mirroring works on virtual machines and not just physical machines, then for sure. Most of the labs I’m running, I’m using a virtualized environment.
    Re. 2. It is easiest to set up mirroring if you are using the same account, but it is not required and they do not have to be domain accounts. The later is slightly more complicated.
    Re. 3. No, the port can be whatever you decide. However, please ensure the firewall settings are set up correctly.

    Karsten

  17. Kishore M. K. says:

    Thanks Strobaek

  18. Mohd Ali says:

    Hi Karsten,

    I am trying to configure mirroring ,I am using SA account than Service accounts. I am getting below error while configuring.
    “The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://xxx:5024’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.”

    I have given the SA accounts on each server sysadmin permissions. Do I need to add these accounts on local server accounts on each server.

    Please guide me how to proceed further.

  19. strobaek says:

    Hi Mohd,
    Thank you for your comment.
    If I understand you correctly you are not using a domain account to set up the mirroring. I would recommend this as it makes things a lot easier. If you need to use local accounts the process is somewhat more elaborate. If you search the web, I’m sure you can find the guidance.
    Karsten

  20. Awan says:

    Hi Karsten,

    I am trying to set up Database Mirroring Using TDE , I am using domain account ‘abc’ but I am getting error ” Database ‘databasename’ cannot be opened. It is in the middle of restore.” (Microsoft SQL Server , Error:927)
    But if I try to setup Database Mirroring without TDE I am successfully able to setup using same domain account ‘abc’ .
    Please give me some tips

Leave a Reply

Your email address will not be published.