SQL Server

The premise of setting up a database mirroring configuration in high availability mode is to provide a disaster recovery solution, provide data integrity, and keep your database downtime to a minimum. A high availability scenario consists of three server architecture. The first server is known as the principle server. This server is database server where the initial database resides. The second server in the configuration will be referred to as themirror. The mirror is the server where an offline replica of the principle database resides. The third server in the configuration will be called the witness. The role of the witness is to periodically monitor the state of the two other servers in the configuration. The witness performs this duty by periodically pinging the other two servers ensuring that they are both online.

The goal of this type of scenario is that if the witness server pings the principle and the principle appears to be offline, all traffic is forwarded to the monitor server. For this to work, the mirror server must be taken out of its offline mode and be set as the new principle server in this mirroring configuration. While the previous principle database server is offline, all transactions are written to the mirror servers transaction log and marked as uncommitted to the former principle. When the old principle server comes back online, all uncommitted transactions are applied to the database and the old principle takes on the role of the new mirror server. Ideally, in this type of scenario, there is little to no data loss and virtually no down time for any application running over the top of mirrored database.

n my implementation of the database mirroring scheme, I will be constructing a virtual network of three windows servers. These 3 VMs will live on a NATed virtual network on a Mac Mini. I will be implementing these 3 virtual machines via VMWare Fusion. The network will consist of 1 server running the Windows Server 2008 operating system. This server will act as a domain controller for the network and will also be the only DNS server on the network. The other two servers will be running the Windows Server 2003 operating system. Every one of these VMs will be running SQL Server 2008 Developer Edition for their database management system and be allocated 1GB or RAM.

Computer Name	IPv4 Address	Database Mirroring Role	Operating System
DAHPRIMARY	192.168.99.5	Principle	Windows Server 2008
DAHMIRROR	192.168.99.10	Mirror	Windows Server 2003
DAHWITNESS	192.168.99.15	Witness	Windows Server 2003

As you can see from the table above, all three servers in the configuration will be assigned static IP addresses.

To setup the Domain Controller and DNS server on the Windows Server 2008 instance, the process is as simple as following a wizard, therefore I will not provide any documentation. There is also a best practices for installing and configuring SQL Server 2008. I will briefly touch on some of
these key points in this paper, because they will be critical to the success of this mirroring configuration, but I will not walk through the installation process. To read up on SQL Server 2008 best practices visit the MSDN website. http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx

When installing each SQL Server instance, it is easiest to install the servers as the default instance on each server. This allows us to follow the naming convention of each SQL Server being named after the server it is installed upon (ex: DAHPRIMARY ).

SETTING UP THE SERVICE ACCOUNTS

As a best practice, when installing SQL Server it is a good idea to have a separate domain account for your SQL Server instance to run as. To do this, open up Active Directory on the Principle server. This can be done by going to Start->Administrative Tools->Active Directory Users and Computers. Under the Users folder, create a new user and give the user a useful name. Since this account was going to be the account running the database engine, I named the account SQLENGINE. Assign the user a password and click ok.
Once we reach the point in the SQL Server installations where we are asked to assign an account to the different database services, we can assign the newly made SQLENGINE account the database engine. Once this has been done we are done setting up service accounts for our configuration.
We will use this account to run as the service account for each of our three SQL Server instances.

CREATE LOGINS ON EACH SERVER

The first step is to allow login access on each instance of sql server to the service account running the database engine. The first step is to open SQL Server Management
Studio(SSMS). You can find this under Start->All Programs->SQL Server 2008->SQL Server Management Studio.
Once you have opened SSMS, you should be able to connect to the correct database server by simply clicking connect.
When you have SSMS open, click on the New Query button on the top hand left of SSMS.
Now you can type a SQL statement into the new query menu. Run this script on all three SQL Server instances.
```
   1:  CREATE LOGIN [WHODER\SQLENGINE] FROM WINDOWS
   2:  GO
```
This script creates the necessary login for the service account to perform the needed tasks for database mirroring on each server. The WHODER in the newly created login refers to the name of the domain that was created when the domain controller was configured.

CREATING THE ENDPOINTS

By definition, an endpoint in service oriented architecture is an entry point to a service, or process. In our case an endpoint is the entry point for the transmission of database mirroring specific information. We will need to create a database mirroring end point on each database server.

The first step is to create an endpoint on each the principle and the mirror server. The code to do so is the same on each instance.

Run the statement below on both the principle and the mirror.

   1:  CREATE ENDPOINT [Mirroring]
   2:  STATE = STARTED
   3:  AS TCP(LISTENER_PORT = 5033, LISTENER_IP = ALL)
   4:  FOR DATA_MIRRORING
   5:  (
   6:  ROLE = PARTNER,
   7:  AUTHENTICATION = WINDOWS NEGOTIATE,
   8:  ENCRYPTION = REQUIRED ALGORITHM RC4
   9:  )
  10:  GO

By default, SQL Server creates these endpoints on port 5022. As a best practice, it is a good idea to create your endpoints on a different port than the default.

Now run the statement below on the witness server. The only difference is the Role clause of the statement.

   1:  CREATE ENDPOINT [Mirroring]
   2:  STATE = STARTED
   3:  AS TCP(LISTENER_PORT = 5033, LISTENER_IP = ALL)
   4:  FOR DATA_MIRRORING
   5:  (
   6:  ROLE = WITNESS,
   7:  AUTHENTICATION = WINDOWS NEGOTIATE,
   8:  ENCRYPTION = REQUIRED ALGORITHM RC4
   9:  )
  10:  GO

GRANT CONNECT PRIVILEGES

The next step is to grant the connect privileges on the newly created endpoints. We will grant this privilege to the account we created in the first step.
Open SSMS.

Run the script below against each of the three database servers:

   1:  GRANT CONNECT ON ENDPOINT::[Mirroring] TO [WHODER\SQLENGINE]

CREATE A DATABASE AND A TABLE

The next step is to create a database to be mirrored.

Run the following script only on the principle server:

   1:  CREATE DATABASE principle
   2:  GO
   3:  USE principle
   4:  GO
   5:  CREATE TABLE table1
   6:  (
   7:  principleId INT IDENTITY(1,1) PRIMARY KEY,
   8:  col1 INT NULL,
   9:  col2 VARCHAR(40) NULL,
  10:  col3 VARCHAR(90) NULL
  11:  )

The next step is to enter some data into our newly created table:

   1:  INSERT INTO [principle].[dbo].[table1]
   2:  ([col1]
   3:  ,[col2]
   4:  ,[col3])
   5:  VALUES
   6:  (1000
   7:  ,'A string of sorts'
   8:  ,'Another string, containing no meaning')
   9:  GO
  10:  INSERT INTO [principle].[dbo].[table1]
  11:  ([col1]
  12:  ,[col2]
  13:  ,[col3])
  14:  VALUES
  15:  (136
  16:  ,'More fake stuff'
  17:  ,'Nobody likes a phony')
  18:  GO
  19:  INSERT INTO [principle].[dbo].[table1]
  20:  ([col1]
  21:  ,[col2]
  22:  ,[col3])
  23:  VALUES
  24:  (1895
  25:  ,'Testing Testing 123'
  26:  ,'How much wood could a wood chuck, chuck')
  27:  GO

RESTORING A BACKUP TO THE MIRROR

The next step is to take a full backup of the database we created. In a real life scenario, it is important to make sure that the database is in full recovery mode, but since we didn’t state a recovery model, our database defaulted to the full recovery model. After the full backup is taken, we also need to take a transaction log backup as well.

Open SSMS.
Browse to the database node of your server and expand it out. Once you see your principle database, right click on the database.
The select Tasks->Back up…
It should be fine to select the defaults at this point, but make sure that the Backup Type is set to Full. Make note of the location where the backup will be stored. If you have to change this location to a location that is easy to remember. Click OK.
Once again select Tasks->Back up…
This time the Backup Type should be changed to Transaction Log. It should be fine to restore the backup to the same location as the last full backup.
Now browse to the location where our backup’s our stored.
Now copy this single backup file to a network location where the file can be accessed from the mirror server.
Now connect to SSMS on the mirror server to the mirror instance database server.
Now we must create a database to restore our backup too:
```
   1:  CREATE DATABASE principle
   2:  GO
```
Now expand the database node of this server instance and right click on the newly created database and select Tasks->Restore->Database.
Select the From Device radio button and click the ellipsis (…) button.
Make sure the Backup Media is set to File and click the Add button.
This will open a file browser. Locate the database backup file that you moved in a previous step.

Dusty Hoppe

Senior Software Engineer | JobAdder & LevelUpFantasy.com

SQL Server Database Mirroring in High Availability Mode with Automatic Failover