Configuring the mirroring in SQL SERVER

Configuring the mirroring

Step1: Take the full backup of principal Database.

BACKUP DATABASE [Database_Name] TO DISK = 'Path of.bak';

Step2: Take the transaction log backup of Principal database.

BACKUP LOG [Database_name] TO DISK = 'Path of .trn';

Step3:If database is in recovery mode then Remove the recovering of mirror database.

Restore database [Database_name] with recovery

Step4:We have the backup folder shared on the Principal Server and you can access it from the Mirror Server

Step5:Restore the full backup to the Mirror server with the NORECOVERY option.

RESTORE DATABASE [Database_Name] FROM DISK = N'\\Principal\Backup\Backup.bak' with norecovery

Step6: Restore log backup also with the NORECOVERY option.

RESTORE LOG TestMirror FROM DISK = N'\\Principal\Backup\Backup.trn'
WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;

Step7:configure Database Mirroring From the Principal server.

Right click the database and choose "Tasks"--> "Mirror" or choose "Properties"-->"Mirroring".

Step8:Click the "Configure Security" button and click "Next >" if the Configure Database Mirroring Security Wizard intro screen appears.

Step9:Select "No" for "Do you want to configure security to include a witness server instance".

Because we are configuring mirroring in Sychronized mode with High safty.
Then click "Next>" to continue the process.

Step10:Configure the Principal Server Instance.

with creating our endpoint communicate over the network. We will name it Mirroring with a Listener Port of 5022.

Click the "Next >" button to continue.
Step11: Configure the Mirror server instance.
click the "Connect..." button then select the mirror server.
Click the "Next >" button to continue.

Step12:Service Accounts

leave the fields blank all the server instances use the same domain account.
Click the "Next"

Step13:Complete the wizard screen that summarizes what we just configured.
Click the Finish.

Step14: Click the on Start mirroring.

If everything turned out right, Database Mirroring has been started successfully and we are fully synchronized.

Backout plan:

1.Remove the mirror configuration.
2.Mirror Database set with Recovery.

 

Test Plan:

1.Verify, Both servers should be listening on the same port.

SELECT type_desc, port
FROM sys.tcp_endpoints;

2.To verify, Database mirroring should be started on both servers.

SELECT state_desc
FROM sys.database_mirroring_endpoints;
The state_desc column on both the Principal and Mirror server should be started.

3.Verify to ROLES should be the same on both the Principal and Mirror Server.

SELECT role
FROM sys.database_mirroring_endpoints;