DATABASE MIRRORING Applies To: Microsoft SQL Server 2008 R2 Note: How to create database mirroring with synchronous mode and with no witness server. Must Prepare: Principal Server Mirrored Server

: IT107\SQLSVR_1 : IT107\SQLSVR_2

1

1. Create a database DB1 on the principal server, using the following transact SQL statement. Example: For Server Database Task

: : : :

PRINCIPAL SERVER IT107\SQLSVR_1 Master Create Database

----code:start USE [master] GO IF

EXISTS (SELECT name FROM sysdatabases WHERE name = N'Northwind_Sample') DROP DATABASE [Northwind_Sample]

GO USE [master] GO CREATE DATABASE [Northwind_Sample] ON PRIMARY ( NAME = N'Northwind_Sample' , FILENAME = N'C:\DB Principal\Northwind_Sample.mdf', SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Northwind_Sample_1' , FILENAME = N'C:\DB Principal\Northwind_Sample_1.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ----code:end

2

2. Create a database DB1 on the mirrored server, using the following transact SQL statement Example: For Server Database Task

: : : :

MIRRORED SERVER IT107\SQLSVR_2 Master Create Database

-----code:start USE [master] GO IF

EXISTS (SELECT name FROM sysdatabases WHERE name = N'Northwind_Sample') DROP DATABASE [Northwind_Sample]

GO USE [master] GO CREATE DATABASE [Northwind_Sample] ON PRIMARY ( NAME = N'Northwind_Sample' , FILENAME = N'C:\DB Mirror\Northwind_Sample.mdf', SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Northwind_Sample_1' , FILENAME = N'C:\DB Mirror\Northwind_Sample_1.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO

-----code:end

3

3. Let’s backup the database and transaction on the principal server using the following transact SQL statement. Example: For Server Database Task

: : : :

PRINCIPAL SERVER IT107\SQLSVR_1 Master Backup Database Principal

-----code:start use [master] go Backup database [HRD] to disk ='C:\Backups Principal\HRD.Bak' with init go

-----code:end -----code:start use [master] go Backup database [Northwind_Sample] to disk ='C:\Backups Principal\Northwind_Sample.Bak' with init go -----code:end

4

4. Restore the database on the target server using the following transact SQL statement. Example: For Server Database Task

: : : :

MIRRORED SERVER IT107\SQLSVR_2 Master Restore Database Principal To Mirrored Server

-----code:start use [master] go restore database [Northwind_Sample] from disk ='C:\Backups Principal\Northwind_Sample.Bak' with norecovery, replace, move 'Northwind_Sample' to 'C:\DB Mirror\Northwind_Sample.mdf', move 'Northwind_Sample_1' to 'C:\DB Mirror\Northwind_Sample_1.ldf' go

-----code:end

5

5. Configure the database [Northwind_Sample] on the principal server for database mirroring. a. Using SQL Server management studio, expand the databases and click on the Database [Northwind_Sample].
(Principal Server)

b. Right click on the database [Northwind_Sample] and select properties.

* In the properties window select the "Mirroring" option as shown below.

Click “OK”.

6

6. a. Now click on the "Configure Security" button
and you will see the following screen. b. Since we are not going to setup the witness server, select the option "No" and click next.

Click “Next >”.

7

7. a. Select the default port and the endpoint name chosen by the SQL server management studio and click Next. b. If you are choosing some other port, then make sure that port is open and available. Click “Next >”.

.

8

8. a. Now select the mirrored server name, click on the "Connect" button and make sure you can connect to the mirrored server. b. Select the default port and the endpoint name chosen by the SQL server management studio and click Next. c. If you are choosing some other port, then make sure that port is open and available. Click “Next >”.

9

9. Type the appropriate service account you want to use for the database mirroring. Click “Next >”.

Click “Finish”.

10

10.

Click “Close”.

11

11. On the next screen, click on the button "Start Mirroring"

12. The following screen shows that database mirroring is configured and running

12

13. a. Click OK and refresh the databases. b. You can see the caption of the [Northwind_Sample] database has changed in both principal and mirrored server.

Source: http://www.databasejournal.com/features/mssql/article.php/3828341/Database-Mirroringin-SQL-Server-2008.htm

13