IT Journey: Implement Database Mirroring in SQL server

Here I will show how to implement Database Mirroring in SQL Server step by step

In order to set up database mirroring the database should be in full recovery model.Otherwise if you try to set up database mirroring you get the below error.

The database cannot be mirrored because it does not use the full recovery model.

In order to set up database mirroring full recovery model right click on database go to properties set to full recovery model.

You can set up the database in full recovery as shown in the above picture.

The second criteria for setting up the data

Before setting up database mirroring ensure that you had taken full backup of the database and restore the backup in secondary database. you will get the below error if you start setup the database mirror without restoring the database backup the secondary server.

We have to restore database backup to secondary server. You have to follow the sequence as mentioned below.

Perform FULL backup from primary database

Restore FULL backup in secondary server with NORECOVERY option

Perform Log backup in primary server

Restore the Log backup with Recovery.

you may get error while setting up the database mirror if you are not restore the database backup.

In order to set the database mirror first we need to configure the security.

Click on configure security,Configure database mirroring wizard will open.

Click on Next.Then we will create the database mirroring without witness.

Automatic failover will not happen if we do not configure witness server.

here first i will show to configure the database in synchronous mode without automatic failover. so i have selected witness server as No. When you are configuring the database mirroring wizard will automatically creates endpoint.

In order to communicate each primary server database with mirror server database we require the end points. The default port for end point is 5022.

end point name you can provide. Alternatively you can create end point using scripts.I have created endpoint using script.

T- Sql script for creating end point

CREATE ENDPOINT Principal_mirroring

STATE = STARTED

AS TCP ( LISTENER_PORT = 5022 )

FOR DATABASE_MIRRORING (ROLE=PARTNER);

Once you have created the endpoints you can verify the endpoint using the below queries

SELECT * FROM sys.database_mirroring_endpoints

select * from sys.tcp_endpoints

Next step is configuring endpoints to mirroring database. In order to configure through wizard you must connect to the database. Alternatively you cannot to mirror instance database and execute the above script.

Once you have connect to mirror server database you can specify the endpoint name

Click Next after creating endpoint in mirror server.

you can leave the service accounts. you are not working under domain controller. the logins i have provided here are domain credentials.

Once you have specify the domain credentials it will create two service accounts. once account on principal server database and another account on mirror server database.click on next.

The above screen shot will show the setting for principal server and mirror server.I have marked the domain credentials with black paint. Once we click on finish we have to perform an action as shown in the message in the last bullet .

Now we have configured the security.close this window.

Next step to configure the database mirror is to grant the service account to connect permissions. other wise you will get the below error.

The above error "The server network address "TCP:xxxxx" cannot be reached or does not exist.check the network address name and that ports for the local and remote end point are operational (Microsoft SQL server,Error:1418" .

This message will not help to trouble shoot the issue.

alternatively you can see the error logs using the below query

declare @errordetails As table (logdate datetime2,processinfo varchar(30),textdata nVarchar(max))

insert into @errordetails

exec xp_readerrorlog

select * from @errordetails order by logdate desc.

xp_readerrorlog : This extended system store procedure is used for reading error from error log file into table format. In order to sort the the error data i have created the temporary table variable and copied the data into temporary table variable.if your error log is huge you create a table and copy these errors into that table.

the error log is showing the message like this.

Database Mirroring login attempt by user 'DEV\01HW381721$.' failed with error: 'Connection handshake failed. The login 'DEV\01HW381721$' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.10.14.79]

Database mirroring has been terminated for database 'DBMirroring'. This is an informational message only. No user action is required.

Database Mirroring login attempt by user 'DEV\01HW381021$.' failed with error: 'Connection handshake failed. The login 'DEV\01HW381021$' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.10.14.127]

The above error is due to permission issues. you can grant the permission using the below T-SQL statement.

Execute the below Statement.

This two statements execute in principal server instance.

GRANT CONNECT ON ENDPOINT::Principal_Mirroring TO [DEV\01HW381721$];

GRANT CONNECT ON ENDPOINT::Principal_Mirroring TO [DEV\01HW381021$];

This two statements execute in mirror server instnace.

GRANT CONNECT ON ENDPOINT::copy_mirroring TO [DEV\01HW381021$];

Once you have grant the connection. you may get same error as shown above.

"The server network address "TCP:xxxxx" cannot be reached or does not exist.check the network address name and that ports for the local and remote end point are operational (Microsoft SQL server,Error:1418" .

In errorlog you may get the following error like this

Database mirroring has been terminated for database 'DBMirroring'. This is an informational message only. No user action is required.

Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://01hw381021.dev.jmifa.local:5022'.

Database mirroring has been terminated for database 'DBMirroring'. This is an informational message only. No user action is required.

Error: 1443, Severity: 16, State: 2.

Error: 1474, Severity: 16, State: 1.

You have to create the login the add the logins for the database users to server logins using the following T-SQL statements.

This two statements execute in principal server instance.

CREATE LOGIN [DEV\01HW381721$] FROM WINDOWS

CREATE LOGIN [DEV\01HW381021$] FROM WINDOWS

This two statements execute in mirror server instance.

CREATE LOGIN [DEV\01HW381721$] FROM WINDOWS

CREATE LOGIN [DEV\01HW381021$] FROM WINDOWS

Once we have created the logins for database users [DEV\01HW381721$] and [DEV\01HW381021$] in both servers. the issues related to connectivity may be solved. In my case i got one more issue.

cannot generate sspi context -- this error i have got while connecting to the mirror server.

you may be solve the error by the following command

setspn 01HW381021 -- This instance name.

You may get some other error when click on setup database mirroring other than connectivity issue while click on start mirroring. You may face other error like this.

Alter database failed for database 'DBmirroring'

"The remote copy of the database "DBmirroring" has not been rolled forward to a point in time that is encompassed in the local copy of the database log."

You have restore the recent log backup and make sure that there is no additional old log backups int the secondary server. you may get error like this your log backups are not in sync with each other.

In this case remove all backup files in mirror server.take full backup from primary server with replace and restore the full backup with NORECOVERY option with override.then restore the log backup WITH RECOVERY, otherwise
you may get the below error.

After restoring log files in sequence. then start database mirror it will start working . You can able to see the status "synchronizing"

you may encounter other errors,here i have showed the possible errors.

Out of three operating modes we have set up the High safety without automatic failover. In this mode automatic fail over will not happen. we have to manually failover the database. It will commit the changes at both sides. so there will be transaction latency. In order to implement automatic fail over we have to add witness server.

We can pause the database mirroring by using the pause button

Click on yes to pause the database mirroring.

Now it is showing database mirroring is paused.

Alternatively you can pause or resume the database mirroring .

ALTER DATABASE DBMirroring SET PARTNER SUSPEND;

ALTER DATABASE DBMirroring SET PARTNER RESUME;

Click on configure security.

Select 'Yes' and Click on next.

Select the Witness server instance check box. follow the below steps.

You have to connect to witness server and specify the port.

Click next for providing service account. you can leave it blank.

Click next and finish.

Then you have add connect permissions to the end point in all server

you have add service account login to the all server.

GRANT CONNECT ON ENDPOINT::Principal_Mirroring TO [DEV\01HW381733$]

GRANT CONNECT ON ENDPOINT::Copy_Mirroring TO [DEV\01HW381733$]

GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [DEV\01HW381733$]

executed this query at each instance

CREATE LOGIN [DEV\01HW381733$] FROM WINDOWS

Now we have configured for automatic fail-over as shown below

You able to see principal server as its synchronized.

Mirrored server

You can test failover the database mirroring by click on failover. then principal server will become mirror server and mirror will act as principal server.

Principal Server

Mirror Server

We can select from one operation mode to other operation mode. here i have changed High safety with automatic failover to High performance mode. In high performance mode principal sever will send the committed transaction to mirror server without waiting for secondary server response. It will remove the witness server automatically.