Configure SQL Server Database Mirroring Using SSMS

By:   |   Updated: 2011-08-15   |   Comments (18)   |   Related: 1 | 2 | 3 | 4 | > Database Mirroring


Problem

I have a need to setup SQL Server Database Mirroring in my environment.  I understand it can be complicated to setup. Can you provide an example on setting up SQL Server Database Mirroring?  Check out this tip for a basic look at how to setup this SQL Server feature.

Solution

In this tip I am going to outline my environment and then walk through the process of setting up Database Mirroring.  This will include the configurations, backups, restores and verification process.  Let's jump in.

My test environment consists of two separate VM's running VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named appropriately Principal and Mirror. The SQL Server and SQL Server Agent Services accounts are running as domain users (DOMAIN\User). Windows Firewall is OFF for the sake of this example.

I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.

Mirroring clip image001

1st step: Issue a full backup of the database.

BACKUP DATABASE TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.bak';

2nd step: Issue a transaction log backup of the database.

BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.trn'; 

Below are the two files in the file system:
Mirroring clip image003

3rd step: Assuming you have the backup folder shared on the Principal Server and you can access it from the Mirror Server, you will need to restore the full backup to the Mirror server with the NORECOVERY option.

RESTORE DATABASE TestMirror FROM DISK = N'\\Principal\Backup\Backup.bak' 
WITH FILE = 1, MOVE N'TestMirror_log' TO 
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf', 
NORECOVERY, NOUNLOAD, STATS = 10;

4th step: 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;

Mirroring clip image005

Now it's time to dig down and configure Database Mirroring. From the Principal server, right click the database and choose "Tasks" | "Mirror" or choose "Properties" | "Mirroring".

Mirroring clip image007

Click the "Configure Security" button and click "Next >" if the Configure Database Mirroring Security Wizard intro screen appears. The next screen should be the Include Witness Server screen:

Mirroring clip image009

This is where you would configure a witness server for your mirroring, but since we're just configuring a basic mirror we will skip this part. However, if you are configuring mirroring in an Enterprise environment it is recommended you configure a witness server because without one you will not have synchronous automatic failover option.

Select "No", then click "Next >" to continue the process.

The next screen will give you options to configure the Principal Server Instance:

Mirroring clip image011

Here we will be creating our endpoint, which is a SQL Server object that allows SQL Server to communicate over the network. We will name it Mirroring with a Listener Port of 5022.

Click the "Next >" button to continue.

The next screen will give you options to configure the Mirror Server Instance:

Mirroring clip image012

To connect to the Mirror server instance we will need to click the "Connect..." button then select the mirror server and provide the correct credentials:

Mirroring clip image013

Once connected, we also notice our endpoint name is Mirroring and we are listening on port 5022.

Click "Next >" and you'll see the Service Accounts screen.

Mirroring clip image015

When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains).

If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.

Since my service accounts are using the same domain account, I'll leave this blank.

Click "Finish" and you'll see a Complete the Wizard screen that summarizes what we just configured. Click "Finish" one more time.

Mirroring clip image016

If you see the big green check mark that means Database Mirroring has been configured correctly. However, just because it is configured correctly doesn't mean that database mirroring is going to start...

Next screen that pops up should be the Start/Do Not Start Mirroring screen:

Mirroring clip image017

We're going to click Do Not Start Mirroring just so we can look at the Operating Modes we can use:

Mirroring clip image018

Since we didn't specify a witness server we will not get the High Safety with automatic failover option, but we still get the High Performance and High Safety without automatic failover options.

For this example, we'll stick with synchronous high safety without automatic failover so changes on both servers will be synchronized.

Next, click "Start Mirroring" as shown below.

Mirroring clip image019

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

Mirroring clip image020
Mirroring clip image021 Mirroring clip image022

If Database mirroring did not start successfully or you received an error here are a few scripts to troubleshoot the situation:

Both servers should be listening on the same port. To verify this, run the following command:

SELECT type_desc, port 
FROM sys.tcp_endpoints;

We are listening on port 5022. This should be the same on the Principal and Mirror servers:

Mirroring clip image023

Database mirroring should be started on both servers. To verify this, run the following command:

SELECT state_desc
FROM sys.database_mirroring_endpoints;

The state_desc column on both the Principal and Mirror server should be started:

Mirroring clip image024

To start an Endpoint, run the following:

ALTER ENDPOINT <Endpoint Name>
STATE = STARTED 
AS TCP (LISTENER_PORT = <port number>)
FOR database_mirroring (ROLE = ALL);

ROLES should be the same on both the Principal and Mirror Server, to verify this run:

SELECT role 
FROM sys.database_mirroring_endpoints;

Mirroring clip image025

To verify the login from the other server has CONNECT permissions run the following:

SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions  SP , sys.endpoints EP
WHERE SP.major_id  = EP.endpoint_id
ORDER BY  Permission,grantor, grantee;

Mirroring clip image026

You can see here from the State and Permissions column that the user has been Granted Connect permissions.

Next Steps
  • To learn more about the three different operating modes involved in database mirroring check out this previous tip
  • Before implementing database mirroring make sure this is the high availability option you need for you company. Log shipping, replication, and clustering are also high availability options that may bring more benefit than mirroring depending on the needs.
  • Check out all of the Database Mirroring tips.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-08-15

Comments For This Article




Saturday, February 22, 2020 - 10:03:52 PM - Samrawit Back To Top (84731)

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'employeemanegment'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://LAPTOP-H6TAS1GF:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3128&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


Thursday, September 26, 2019 - 7:21:54 AM - Lakshay Arora Back To Top (82575)

Hi Brady, Thanks for the great article. I understood the concept very well. But I want to ask you some thing regarding database restoration.

When I restore the database from the backup(which is on other instance), it shows me "Access Denied" error. I am trying to fix this problem from past couple of days, still no luck. 

I tried giving "EVERYONE" permissions on both the source and target folders , also gave the "EVERYONE" permissions to each and every folder in the UNC path;and also gave FULL permissions to the sql server account on both the source and target(where I am restoring backup) servers. Also, ran SSMS in ADMIN mode. Still not working.

It would be great if you can help me out in this case.

Thanks in Advance!! :)


Monday, February 11, 2019 - 11:30:32 PM - Steven Nguyen Back To Top (79008)

Hello all

I have a question here? Before I config mirroring, must our application be stopped to make sure that backup principal database and restore mirror database are same version? Thank you!


Thursday, September 6, 2018 - 12:12:53 PM - noreply Back To Top (77377)

For the security conscious, the encryption algorithm is RC4 which can be confirmed using

select is_encryption_enabled,encryption_algorithm_desc,* from sys.database_mirroring_endpoints 

 

This can be changed by running this command post mirroring setup on both database instances:

ALTER ENDPOINT Mirroring FOR DATABASE_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM AES);

and rerunning the select statement above to confirm.

 

 


Thursday, May 3, 2018 - 10:54:06 AM - Pugal Back To Top (75856)

Everything is Fine.

--------

Upto, 'start Mirroring'. I got following error:

The server network address "TCP://servername:5022" can not be reached 

or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.(Micorsoft SQL Sever, Error: 1418)

 

Then I follow further steps what you told. When run at following query,

SELECT role 

FROM sys.database_mirroring_endpoints;

I got roles are differnts (i.e. for Principal, 1 and Mirroring-> 3). Now what can I do..?

Note:

The status still like this, 'This database has not been configured for mirroring'


Monday, July 18, 2016 - 1:48:59 PM - Don Back To Top (41909)

I too was getting the error "The Server Network Address cannot be reached or does not exist. Error 1418". This was resolved by specifying the domain\serviceaccount names when it ask for them even though the article aboves says they are not required if they are the same domain account. However, mine were both using the same domain account and specifying them was REQUIRED. Apparently, this is needed for SSMS to create the login endpoints on each server. So even if your accounts are the same, you must specify them, at least initially to avoid this error.

Also, you can use the SAME port numbers as long at the instances of SQL Server are on DIFFERENT machines. Different port numbers are often seen in articles but this only because the author is using separate SQL Server instances on the SAME machine.

Hope this helps someone.

 


Monday, December 28, 2015 - 12:19:42 PM - guna Back To Top (40317)

 Hi

 

my principal server in remote location . and my wittness and mirror server on local.

 

principal : tcp://ip:5022

mirror    :tcp://systemname:5023

witness  : tcp://systemname:5024

 

i have to check all the port .everything is open(i have to check with telnet commanad). but when i try start the mirroring i have tcp://systemname:5023 doesnot exist error will come . how can i solve this . kindly help me

 


Tuesday, September 29, 2015 - 11:39:06 PM - Mulumebet Maruf Back To Top (38786)

This youtube video helped me revolve  error message 1418.

https://www.youtube.com/watch?v=9v0OW2WfoIo

 


Monday, December 15, 2014 - 1:50:11 PM - Jonathan Back To Top (35611)

Setting up a high safety mirror at my company I was looking on the web.  You say "Both servers should be listening on the same port" and it is wrong.  The principal and the mirror should have separate endpoints, therefore separate ports.  Undoubtedly you got it to work both ways, but just giving you the Microsoft directions.  


Friday, December 5, 2014 - 5:03:20 AM - Cibi John Back To Top (35520)

 

I managed to resolve the issue. I hope those who experience similar problem might find it helpful.

Have a look at this youtube video

https://www.youtube.com/watch?v=EFMag5x6yfI

The Database Engine service account is controlling it. So make sure that all the SQL Instances are running under the same domain account or the account which is used should have permission on all the servers.

I hope you will find it helpful.

 


Thursday, December 4, 2014 - 12:41:07 PM - Cibi John Back To Top (35510)

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS format

 

I am also getting the same error as mentioned by Ashok Kumar.

Can you please assist ?


Monday, August 18, 2014 - 8:25:10 AM - Ashok Kumar Back To Top (34178)

Hi All,

I have verified all cases everything is fine but when i start mirroring getting error like this. please help me to resolve this problem.

Error message is The Server Network Address cannot be reached or does not exist. Error 1418

I have already verified by telnet for both network address. it is fine.


Wednesday, April 30, 2014 - 10:16:56 AM - Dhanesh Singh Back To Top (30563)

Hi All

 

When i remove sysadmin privilage from windows authentication which is mapped in sql server ..my mirroring is going out of sync...and i have used this account while configuring mirroring and my sql server is also running with the same acount.

 

Plz reply


Thursday, February 20, 2014 - 3:48:18 AM - Tu Pham Back To Top (29510)

If the witness server is down, how can we resolve this issue?


Wednesday, December 4, 2013 - 7:26:53 AM - Naren Back To Top (27681)

Hi Friends,

i setup for mirror  b/w different domains member servers  and different class A segments (eg 10.1.0.10 to 10.1.50.86) . i done as per mirror configured while starting mirroring getting error



Sunday, April 14, 2013 - 8:51:46 AM - Jundi Back To Top (23345)

Thanks for the fruitful information,,,

 


Monday, September 17, 2012 - 12:32:58 AM - Esmail Back To Top (19524)

Hi Dear

I do all of your tips step by step but it has an error, i dont know what to do , may you please help me?? my error is::


An error occurred while starting mirroring.

===================================

Alter failed for Database 'DB1'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
   at Microsoft.SqlServer.Management.Smo.Database.Alter()
   at Microsoft.SqlServer.Management.SqlManagerUI.DBPropMirroring.StartMirroring(Object sender, EventArgs e)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()

===================================

The server network address "TCP://Salimian:1445" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

------------------------------
Server Name: NASR\FARZIN
Error Number: 1418
Severity: 16
State: 1
Line Number: 1


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

 


Wednesday, August 24, 2011 - 11:39:51 PM - Bunty Back To Top (14509)

Thanks for sharing..... Step by step configuration tip helped me to configure my 1st mirroring task successfully... Thanks















get free sql tips
agree to terms