Database Mirroring On SQL Server - Database Tutorials (2023)

Microsoft has announced that it will remove Database Mirroring in the future because it has developed the Always On technology. But since SQL Server 2005 and 2008 users cannot use the Always ON feature, we’ll refer to Database Mirroring and how to create it.

Database Mirroring is a solution that increases database level accessibility. (This means that if the database server becomes unserviceable, the application can access the database from the other server)

General information about Mirroring:

• Can be done based on database. Multiple databases cannot be mirrored at the same time. You must do mirroring for each database separately.

• The mirroring database must be in the full recovery model. You may want to read my article “What is Database Recovery Model“.

• To create mirroring on 2 instances, you must use the same version of sql server in 2 instance.

• If Mirroring is stopped, all transaction log backups in the primary database must be restored to the secondary server with no recovery before mirroring can be resumed.

• System databases cannot be mirroring.

• If the database is mirrored, the database name cannot be changed without breaking the mirror.

• Does not support FileStream. You cannot mirror a database with a File Stream File Group.

Mirroring technology requires at least 2 instances.

One of these servers is Principal Server and the other is Mirror Server.

You will need a third server/instance if you want the problem to be detected automatically and automatic failover occurs to the miror server in the event of a problem.

This server/instance is called a Witness Server.

You can implement Database Mirroring using two instances on the same server for testing, but there is no point in doing this for the production system.

Database Mirroring Setup using SQL Server 2014:

First, we get the full backup of the database we want to do Mirror, and we restore this backup to the mirror instance in NO RECOVERY mode.

Then we get the log backup of the master database and restore this log backup to mirror instance in no recovery mode.

You can see how to do this in the article “Backup Restore On SQL Server“.

Right-click on the AdventureWorks2014 database to click properties and come to the Mirroring tab as follows.

Database Mirroring On SQL Server - Database Tutorials (1)

Click Configure Security. On the first page, select “Do not show this starting page again” and click Next.

Database Mirroring On SQL Server - Database Tutorials (2)

(Video) Database Mirroring in SQL Server || Database Mirroring Configuration || Ms SQL

The next screen asks if we want to use the witness server. Click Next when Yes is selected.

Database Mirroring On SQL Server - Database Tutorials (3)

The next screen provides a list of instances to be configured. Principal and Mirror selection is compulsory, but witness selection is not.

When witness is selected, we click next.

Database Mirroring On SQL Server - Database Tutorials (4)

In the next screen, we specify the Listener port and the Endpoint Name (used to retrieve connections from other servers).

We can also determine whether to encrypt the data sent over this endpoint. We encrypt by choosing “encrypt data sent through this endpoint”.

Database Mirroring On SQL Server - Database Tutorials (5)

You can write another port for the port. If you are going to perform Database Mirroring (principal, mirror, witness) on 3 instance on the same server, you must use different ports.

We are filling up as above and proceeding next.

The next screen is about Mirror Instance.

I’m using different servers for principal and mirror server.

That’s why I left the port as 5022. If you are going to use different instances on the same server, you can change the port to 5023.

Database Mirroring On SQL Server - Database Tutorials (6)

The next screen is about the witness server. I’ll use another instance on the server where the principal server instance is for the witness server. So I set the port as below 5023.

Database Mirroring On SQL Server - Database Tutorials (7)

On the next screen, we define accounts for principal, mirror and witness servers as follows.

Database Mirroring On SQL Server - Database Tutorials (8)

In the next screen, the summary information is presented. Finish the process.

Database Mirroring On SQL Server - Database Tutorials (9)

Database Mirroring On SQL Server - Database Tutorials (10)

(Video) SQL Server DBA Tutorial 110-How to Setup Database Mirroring in SQL Server

Finally, click Start Mirroring to complete the process. If the process is completed correctly, a screen should come as follows. If you press the Failover button on the following screen, the mirror server will become the principal.

Database Mirroring On SQL Server - Database Tutorials (11)

In Operating mode, you can see “High Performnce (asynchronous)” and “High Safety with automatic failover (synchronous)”.

High Performance (asynchronous):

When user requests come to the principal database, transaction completion information is passed to the user before the operation is reflected on the mirror server. Then, on the back side, this operation is transferred to the mirror server.

High security with automatic failover (synchronous):

When user requests come to the principal database, transaction completion information is passed to the user after the operation is reflected on the mirror server.

Requires Witness server. It’s a safer way. But the network between the two servers must be sufficient (fast and secure). The user requests are automatically redirected to the mirror server when there is a problem with the Principal server.

This way the principal server and the mirror server change roles.

You can change this option later. You can make asynchronous at first. Then you can change it to synchronous again.

But, after getting asynchronous from synchronous, witness instance information will lost.

First of all, you need to enter the witness server and port information that you defined before.

As you can see in the screenshot below, High safety with automatic failover is disabled and witness server information is blank.

Database Mirroring On SQL Server - Database Tutorials (12)

Then, when I enter the witnes server information as below, “high safety with automatic failover” becomes selectable.

Database Mirroring On SQL Server - Database Tutorials (13)

You’re in luck if you didn’t have a problem until you came to this section.

I have a few problems.

If your environment does not meet the following conditions:

  1. If the ports that you specify are not open between the two servers or are used by another process,
  2. If you have not restored the Full backup and the log backup to the mirror server in norecovey mode,
  3. If the service accounts of the instances that you make a mirror are running with an account, such as LocalSystem,
  4. If the ports you have defined are being used by another service

You may receive errors as follows.

First Error:

The Database Mirroring endpoint can not listen on port 5022 because it is in use by another process.

Database Mirroring On SQL Server - Database Tutorials (14)

(Video) SQL Server 2012 - Database Mirroring Configuration (Part 1 of 2)

In this error, port 5022 is used by another process. So I erased all the endpoints and repeated all the steps from scratch, and this time I set the 5026 and 5027 ports.

Second Error:

The server network address “TCP://domaindekisunucuismi:5026” 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: 14418)

Database Mirroring On SQL Server - Database Tutorials (15)

This error was caused by the fact that the service accounts of the instances where I made the mirror were “local system”.

I set the service accounts for these instances as the same domain account, and I removed the endpoints and repeated all the steps from scratch, and this time I was able to do it without problems.

You may receive these errors for other reasons.

To avoid these errors;

Be sure to restore the full backup and the log backup to the mirror server in norecovey mode.

Make sure that the ports you specify are turned on between the two servers.

Run the Cmd command line with Run As Administrator;

Type “Telnet destination_server_ip_or_name destination_port” and press enter.

You should write your server ip and port in the parts that begin with the target. If it says Connecting .. the port is closed. it must be open.

Make the service accounts of the instances you use to be the same domain user.

If you continue to receive errors despite all of this, run the following query on all servers and make sure the endpoints are running.

If endpoints are not running, you can run them with the following script.

(Video) SQL Server Database Mirroring with Simple Steps (Video)

1

ALTER ENDPOINT endpointisation STATE = STARTED

If not, you can try to authorize the service accounts as follows.

1

GRANT CONNECT ON ENDPOINT :: Mirroring TO [domain_name/ user_name]

I’ve written all the errors and solutions I’ve encountered before, which could be the cause of this problem. If, despite all this, your problem has not solved, you can email me.

Database Mirroring On SQL Server - Database Tutorials (16)

Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

FAQs

How do I mirror a SQL Server database? ›

How to Configure Database Mirroring for SQL Server
  1. Connect to the primary server.
  2. Ensure the database is at Full recovery model.
  3. Enable database mirroring.
  4. Configure database mirroring security.
  5. Define whether or not a witness server is used for automatic failover.
15 Sept 2014

Is database mirroring deprecated in SQL Server 2019? ›

SQL Server Database Mirroring is a technology used for disaster recovery scenarios and is used by many DBAs. Even though Microsoft states that it is a deprecated feature, it is still hanging around in SQL Server 2019.

What is the use of mirroring in SQL Server? ›

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations.

What is the difference between database mirroring and replication? ›

Mirroring is the copying of data or database to a different location. While replication is the creation of data and database objects to increase the distribution actions.

How do you create a mirror database? ›

The basic steps for establishing a mirroring session are as follows:
  1. Create the mirror database by restoring the following backups, using RESTORE WITH NORECOVERY on every restore operation: ...
  2. You can set up mirroring by using either Transact-SQL or the Database Mirroring Wizard.
14 Sept 2022

What is the difference between mirroring and always on? ›

With mirroring you must manually create those objects on the mirror server. AlwaysOn can combine multiple databases as a unit that can be failed over together. This is called the Availability Group.

How do I know if my SQL database is mirrored? ›

To view the status of a database mirroring session

After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree. Expand Databases, and select the database to be mirrored. Right-click the database, select Tasks, and then click Mirror.

Does SQL Server Standard support mirroring? ›

Database mirroring can be configured in SQL Server Standard edition. It supports manual failover and automatic failover using the witness server (High Safety with automatic failover).

Does SQL always on require a witness server? ›

In contrast to database mirroring, there is no witness role in Always On availability groups. The overall health of a WSFC is determined by the votes of quorum of nodes in the cluster.

What is difference between cluster and mirroring? ›

Failover clusters provide high-availability support for an entire Microsoft SQL Server instance, in contrast to database mirroring, which provides high-availability support for a single database. Database mirroring works between failover clusters and, also, between a failover cluster and a nonclustered host.

What is the difference between log shipping and mirroring in SQL Server? ›

Log shipping supports an unlimited number of secondary's for each primary database. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.

How do I mirror my server? ›

Take a copy of the Server directory for the mirror server installation. On the server machine(s) designated as the mirror server(s), create a directory called, e.g., MirrorServer. Place the copy in the MirrorServer directory on the server machine(s) designated as the mirror server(s).

What is mirroring database? ›

A database mirror is a complete backup of the database that can be used if the primary database fails. Transactions and changes to the primary database are transferred directly to the mirror and processed immediately so the mirror is always up-to-date and available as a “hot” standby.

What is the difference between mirroring and copying? ›

Mirroring is the process of creating and maintaining redundant copies of a database. On the other hand, Replication is the process of continuously copying data changes from one database to another database. Mirroring is performed on the database while replication is performed on both data and database objects.

Can we configure mirroring and replication on same database? ›

Database mirroring can be used in conjunction with replication to improve availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients.

How do I replicate a SQL database to another server? ›

Step 1: Open SSMS and establish a connection to your SQL Server instance. Step 2: Right-click on the “Replication” folder on the Object Explorer and select “Configure Distribution”. Step 3: The “Distribution Configuration Wizard” will popup. The wizard shows the general details about configuring the Distributor.

What is endpoint in SQL Server mirroring? ›

This endpoint is a special-purpose endpoint that is used exclusively to receive connections from other server instances. On a given server instance, every Always On availability groups or database mirroring connection to any other server instance uses a single database mirroring endpoint.

How can check SQL Server mirroring status? ›

To view the status of a database mirroring session

After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree. Expand Databases, and select the database to be mirrored. Right-click the database, select Tasks, and then click Mirror.

What is quorum in SQL Server? ›

The Microsoft SQL Server TechNet glossary defines quorum as: “in a database mirroring session with a witness server, a relationship in which the servers that can currently communicate with each other arbitrate who owns the role of principal server.” The witness server as a third party is required to create a quorum.

What is difference between replication and always on SQL Server? ›

AlwaysOn/Mirroring reads the log on the master server and transfers all the commands to the DR servers where it essentially restores the t-log from a backup. Replication reads the log and translates the transactions and commands into individual commands to insert/updated/delete one row at a time on the subscribers.

What is clustering in SQL Server? ›

SQL Server clustering is the term used to describe a collection of two or more physical servers (nodes), connected via a LAN, each of which host a SQL server instance and have the same access to shared storage.

How do you failover in mirroring? ›

To manually fail over database mirroring
  1. Connect to the principal server instance and, in the Object Explorer pane, click the server name to expand the server tree.
  2. Expand Databases, and select the database to be failed over.
  3. Right-click the database, select Tasks, and then click Mirror. ...
  4. Click Failover.
14 Sept 2022

How do I find a witness server in mirroring? ›

Procedure. Log in to the principal, mirror, and witness database server respectively to check whether any database is in disconnected or suspended state. Click to add a database connection. Register database mirroring to check the running status on the Database Mirroring Monitor.

How do you find the principal server in mirroring? ›

After database mirroring is configured, you can verify the database mirroring setup in several ways. View the status of the principal and mirror databases. Expand Databases in Management Studio to view the status of the principal and mirror databases. Figure 27.9 shows an example for the principal database.

Which are the mirroring modes SQL Server supports? ›

SQL Server Database Mirroring offers two methods for exchanging data: synchronously and asynchronously.

Which database Cannot mirror? ›

Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.

How many modes does database mirroring operate? ›

With database mirroring, you have the option of deploying in one of three modes: high safety with automatic failover mode (high availability with a witness server), high safety without automatic failover mode (high protection without a witness server), and high-performance mode.

What happens if quorum disk fails? ›

If the quorum disk fails, the cluster service on ALL nodes in the cluster will stop since none of the nodes will be able to successfully arbitrate for the quorum resource.

How do I set SQL to high availability? ›

While you are still in SQL Server Configuration Manager, right click on SQL Server Services to open the Properties dialog box. Navigate to the AlwaysOn High Availability tab, and select Enable AlwaysOn Availability Groups checkbox. Restart the SQL Server Service after making these changes.

What is split brain in SQL Server? ›

When the external cluster fails over the primary server to another server that's running SQL Server, the original primary server doesn't quit as expected. Therefore, a split-brain scenario occurs.

What are the types of replication in SQL Server? ›

There are four MS SQL Server replication types: snapshot replication, transactional replication, peer-to-peer replication and merge replication.

What is automatic failover in database mirroring? ›

In high-safety mode with automatic failover, once the database is synchronized, if the principal database becomes unavailable, an automatic failover occurs. An automatic failover causes the mirror server to take over the role of principal server and bring its copy of the database online as the principal database.

What is the difference between AlwaysOn and clustering in SQL Server? ›

AlwaysOn availability groups (AAGs)

Unlike a failover cluster, in an AAG, storage is not shared because SQL Server uses log shipping to replicate data from the primary database to the secondary database instances.

Can log shipping and mirroring configure together? ›

A given database can be mirrored or log shipped; it can also be simultaneously mirrored and log shipped.

Can we take full backup in log shipping? ›

If you have not already set up the destination database for log shipping, the wizard can do this for you by taking a full backup of the source database and restoring it to a new or existing database on the secondary SQL Server.

What is always on high availability in SQL Server? ›

The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012 (11. x), Always On availability groups maximizes the availability of a set of user databases for an enterprise.

How does mirroring of a server work? ›

Server mirroring is a process in network management through which an exact replica of a server is continuously created on run time. Server mirroring is a technique used for business continuity, disaster recovery and backup.

How can I bring mirror database online after principal server is down? ›

In the event of failure of the principal, the database service becomes unavailable. You need manual intervention to make the database service available. You must break the mirroring session and then recover the mirror database.

What is verbal mirroring? ›

Mirroring is when one person imitates the verbal or nonverbal behaviors of another. In business settings, this is often done to build rapport and goodwill.

Does database mirroring affect performance? ›

When transaction safety is OFF, the database mirroring session operates asynchronously, supporting only high-performance mode. This mode improves performance but has an impact on high availability. High-performance mode uses both the principal server and the mirror server.

What is mirroring in database recovery? ›

Database mirroring is the process of creating and managing multiple copies of a database for the purpose of database backup, recovery and/or performance optimization.

What is database mirroring or shadowing? ›

Mirroring is the process of generating multiple copies of databases and it is also known as shadowing. These database copy usually located on different machine.

How does SQL Server mirroring work? ›

Overview of Database Mirroring. Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations.

What are the benefits of database mirroring? ›

Benefits of Database Mirroring
  • Increases availability of a database. ...
  • Increases data protection. ...
  • Improves the availability of the production database during upgrades.
14 Sept 2022

Is it better to mirror or backup? ›

Mirror ensures the most recent changes made to any given file are on your computer and drive, while backup is appropriate for longer term plans, such as finding an old file that might've been deleted from the source on accident.

How do I create a mirror in SQL Server? ›

Getting Started
  1. Right click on the database and select Tasks>Mirror. ...
  2. Press the Configure Security button. ...
  3. The Configure Database Mirroring Security Wizard will be displayed. ...
  4. The first wizard will ask if you want a Witness. ...
  5. The next option will let you select where to save the security configuration.
6 Mar 2015

Can we read data from mirror database? ›

It is possible to read data from the mirror database. To do this, you need to create a database snapshot according to Sean's post.

How do I set up mirroring? ›

Right-click on the Principal Database, and select Task > Mirror. You can also open this popup by Properties and then the mirroring page. On this page, you can see there is a button named Failover. Click on that button.

How do I replicate a SQL database to another server? ›

Step 1: Open SSMS and establish a connection to your SQL Server instance. Step 2: Right-click on the “Replication” folder on the Object Explorer and select “Configure Distribution”. Step 3: The “Distribution Configuration Wizard” will popup. The wizard shows the general details about configuring the Distributor.

How do you mirror a server? ›

Configuring the Mirror Server
  1. Open the swServer. ini in the mirror server installation in a text editor.
  2. Configure the settings in the MirrorServer section using the below entries.
  3. Comment out the MainServer section.
  4. Save your changes.
23 Sept 2022

What is a mirrored database? ›

A database mirror is a complete backup of the database that can be used if the primary database fails. Transactions and changes to the primary database are transferred directly to the mirror and processed immediately so the mirror is always up-to-date and available as a “hot” standby.

What is difference between cluster and mirroring? ›

Failover clusters provide high-availability support for an entire Microsoft SQL Server instance, in contrast to database mirroring, which provides high-availability support for a single database. Database mirroring works between failover clusters and, also, between a failover cluster and a nonclustered host.

What are the types of replication in SQL Server? ›

There are four MS SQL Server replication types: snapshot replication, transactional replication, peer-to-peer replication and merge replication.

What is difference between replication and always on SQL Server? ›

AlwaysOn/Mirroring reads the log on the master server and transfers all the commands to the DR servers where it essentially restores the t-log from a backup. Replication reads the log and translates the transactions and commands into individual commands to insert/updated/delete one row at a time on the subscribers.

How do I replicate data from one server to another? ›

Step 3: Set up server-to-server replication
  1. Add the source server. Select the Add button. ...
  2. On the All Connections page, select the source server.
  3. Select Storage Replica from Tools panel.
  4. Select New to create a new partnership. ...
  5. Provide the details of the partnership, and then select Create (as shown in Figure 3).
25 Aug 2022

How does mirroring of a server work? ›

Server mirroring is a process in network management through which an exact replica of a server is continuously created on run time. Server mirroring is a technique used for business continuity, disaster recovery and backup.

How do I create a mirror in SQL Server 2016? ›

How to Setup Mirroring in SQL Server 2016 - SQL Server 2016 DBA Tutorial

What is endpoint in SQL Server mirroring? ›

This endpoint is a special-purpose endpoint that is used exclusively to receive connections from other server instances. On a given server instance, every Always On availability groups or database mirroring connection to any other server instance uses a single database mirroring endpoint.

What is the difference between mirroring and copying? ›

Mirroring is the process of creating and maintaining redundant copies of a database. On the other hand, Replication is the process of continuously copying data changes from one database to another database. Mirroring is performed on the database while replication is performed on both data and database objects.

Does database mirroring affect performance? ›

When transaction safety is OFF, the database mirroring session operates asynchronously, supporting only high-performance mode. This mode improves performance but has an impact on high availability. High-performance mode uses both the principal server and the mirror server.

Can we take backup of mirrored database? ›

Mirrored database backup feature is supported in SQL Server 2005 Enterprise Edition and all later versions. This feature supports all types of backups: full, differential, and transaction log backups. It can even be used for split backup files. There are also some drawbacks, to using mirrored backups.

What is automatic failover in database mirroring? ›

In high-safety mode with automatic failover, once the database is synchronized, if the principal database becomes unavailable, an automatic failover occurs. An automatic failover causes the mirror server to take over the role of principal server and bring its copy of the database online as the principal database.

What is the difference between mirroring and log shipping? ›

Log shipping supports an unlimited number of secondary's for each primary database. Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.

What is the difference between AlwaysOn and clustering in SQL Server? ›

AlwaysOn availability groups (AAGs)

Unlike a failover cluster, in an AAG, storage is not shared because SQL Server uses log shipping to replicate data from the primary database to the secondary database instances.

Videos

1. How to Setup Mirroring in SQL Server 2016 - SQL Server 2016 DBA Tutorial
(TechBrothersIT)
2. Configure SQL Server Database Mirroring with monitoring
(SQLDATABASE)
3. Database Mirroring in SQL Server 2008 R2 - Part 1
(metamanager)
4. Database mirroring(witness server)
(Kunjan Patel)
5. SQL Server Database Mirroring
(SQL Server Online Tutorials)
6. SQL Server Mirroring 1 - Using Full Backup
(Mandar Gogate)
Top Articles
Latest Posts
Article information

Author: Trent Wehner

Last Updated: 01/31/2023

Views: 5668

Rating: 4.6 / 5 (76 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Trent Wehner

Birthday: 1993-03-14

Address: 872 Kevin Squares, New Codyville, AK 01785-0416

Phone: +18698800304764

Job: Senior Farming Developer

Hobby: Paintball, Calligraphy, Hunting, Flying disc, Lapidary, Rafting, Inline skating

Introduction: My name is Trent Wehner, I am a talented, brainy, zealous, light, funny, gleaming, attractive person who loves writing and wants to share my knowledge and understanding with you.