Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Configure SQL Server Database Mirroring Using SSMS

MSSQLTips author Brady Upton By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | More > 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.

Mirror1

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:
Mirror2

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;

Mirror3

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".

Mirror4

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:

Mirror5

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:

Mirror6

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:

Mirror7

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:

Mirror8

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.

Mirror9

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.

Mirror10

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:

Mirror11

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

Mirror12

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.

Mirror13

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

Mirror14
Mirror15 Mirror16

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:

Mirror17

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:

Mirror18

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;

Mirror19

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;

Mirror20

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.


Last Update: 8/15/2011


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, August 24, 2011 - 11:39:51 PM - Bunty Read The Tip

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

 

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

Better try and fail, instead of not trying at all...

 

Database Best Practices


Monday, September 17, 2012 - 12:32:58 AM - Esmail Read The Tip

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)

 


Sunday, April 14, 2013 - 8:51:46 AM - Jundi Read The Tip

Thanks for the fruitful information,,,

 


Wednesday, December 04, 2013 - 7:26:53 AM - Naren Read The Tip

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



Thursday, February 20, 2014 - 3:48:18 AM - Tu Pham Read The Tip

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


Wednesday, April 30, 2014 - 10:16:56 AM - Dhanesh Singh Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.