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.
Click Configure Security. On the first page, select “Do not show this starting page again” and click Next.
The next screen asks if we want to use the witness server. Click Next when Yes is selected.
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.
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”.
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.
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.
On the next screen, we define accounts for principal, mirror and witness servers as follows.
In the next screen, the summary information is presented. Finish the process.
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.
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.
Then, when I enter the witnes server information as below, “high safety with automatic failover” becomes selectable.
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:
- If the ports that you specify are not open between the two servers or are used by another process,
- If you have not restored the Full backup and the log backup to the mirror server in norecovey mode,
- If the service accounts of the instances that you make a mirror are running with an account, such as LocalSystem,
- 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.
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)
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.
|
1 |
SELECT state_desc FROM sys.database_mirroring_endpoints; |
If endpoints are not running, you can run them with the following script.
|
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.
