Installing SQL Server 2012 in a Mirror Setup

http://blog.strobaek.org/2013/05/02/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.

The Add Roles and Features Wizard will be displayed

Click Next.

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

Click Next.

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

Select the feature (.NET Framework 3.5 Features)

Click Next.

You get a change to confirm the selections.

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.

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

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

Run the Setup.exe file.

Select Installation in the menu to the left.

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

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

Accept the suggested product key or enter the correct one.

Click Next.

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

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.

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

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.

Click Next.

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

Accept the default setting for the instance configuration.

Acc

Click Next.

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

Click Next.

If you have installed SQL Server 2008 and R2 you will notice that the default values for theServer 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.

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.

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.

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.

Click Next.

In the Error Reporting option, click Next

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

Click Install.

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.

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 selectTasks and then Mirrror….

In the Database Properties dialog, click Configure Security.

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.

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

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.

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

Repeat the steps for the Witness server.

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.

Click Next.

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

Click Danish. Sorry Finish. Bad joke.

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

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.

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

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

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.