Mirroring in SQL Server Part 2

Mirroring Setup Step by Step

• Let’s start to setup mirroring server in SQL server 2008 R2 in the first I setup SQL Server 2008R2 and 3 instance in SQL Server 2008

1- Principle instance (Elmasry-PC\Principle)

2- Mirroring instance (Elmasry-PC\Mirroring)

3- Witness Instance (Elmasry-PC\Principle)

• In my example I will make mirroring type high availability (Synchronize mode)
• In principle server I will create database Mirroring_DB
• Take full backup Mirroring_DB.bak
• then take log Transaction backup Mirroring_DB.trn
• Make restore to this backup full mirroring server with No recovery option
• Then right click on Mirroring_DB >>Tasks>>Restore>>Transaction Log
• Then Select your transaction log backup (Mirroring_DB.trn )
• Don’t forget restore with no recovery option

 

High Availability Mode [Automatic Failover]

Principle Server

• Right click on database Mirroring_Db >> tasks >> Mirror >> Configure Security
• You will ask if you want to setup witness server or not? select yes.
• Then you will ask to select your principle server [connect to Elmasry-PC\Principle] and make the port is 5021 and the endpoint name is Principle then Click next
• You will ask again to select your Mirroring server [connect to Elmasry-PC\Mirroring] and make the port is 5022 and the endpoint name is mirroring then click next.
• You will ask again to select your witness server [connect to Elmasry-PC] and make the port is 5023 and the endpoint name is Witness then click next .
• In this step he will ask you to put your user name in your windows and this user must be add in the SQL Server so I write my username is [my pc name\windows username] [Elmasry-PC\Elmasry] .then click finish
• SQL server after you click finish it will make configuration to the Principle Server , Mirroring Server , Witness Server
• After this configuration is finish SQL server will give you massage if you want to Start mirroring or not select start and Waite few mints then mirroring will start and you see the status is [Synchronized: the databases are fully synchronized]

• Now go to make refresh to the servers [principle – mirroring]

Principle Server [Elmasry-PC\Principle]

1- Create Table Name Employee

CREATE TABLE Employee
(Emp_Id INT NOT NULL PRIMARY KEY IDENTITY,
Emp_Name NVARCHAR(50) NOT NULL,
Tel NVARCHAR(12) NULL)

2- Insert data in this table

INSERT INTO dbo.Employee
( Emp_Name, Tel )
VALUES ( N’Mostafa’, — Emp_Name – nvarchar(50)
N’0172788327′ — Tel – nvarchar(12)
) , (‘Mohamed’ , ‘015876565645’)

3- Now if you select data from Employee Table you will see 2 rows

4- Right click on the principle server and make stop to the server

Mirroring Server [Elmasry-PC\Mirroring]

1- Refresh the server (you will see the database change from mirroring to principle database and if you open the database you will see the Employee table and the 2 rows

2- Now try to insert in this table another 2 rows

INSERT INTO dbo.Employee
( Emp_Name, Tel )
VALUES ( N’Omar’, — Emp_Name – nvarchar(50)
N’0172788327′ — Tel – nvarchar(12)
) , (‘Kemo’ , ‘015876565645’)

3- Stop the server and start the principle server [Elmasry-PC\Principle] you will see the database is mirroring make stop to the Mirroring server [Elmasry-PC\Mirroring] you will see the difference the mirroring database in the principle server will change to Principle sever and if you open the Employee table you will see 4 rows not 2 because the 2 rows you insert into database in mirroring server transfer automatically to another server

Mnaual Failover

Before I start to explain Mirroring High Safety mode I will explain something is very good that’s is manual Failover Yes you can make manual failover in the previous Example I make Failover (meaning change the server principle will be the mirroring and the mirroring will be the principle) I make this operation but Automatically when I make stop to the server and this operation make  Automatically because I make mirroring [High Availability] but in the manual failover you can make the same operation by your hand :

1-    Go to principle server then right click and select tasks >>>>> Mirror

2-    Then Click on Failover you will see the massage Click yes

3-    Then make refresh to the server you will see the change 

See Mirroring Part 3 https://mostafaelmasry.wordpress.com/2011/12/25/mirroring-sql-server-part-3/

Good Luck