Setting Up SQL Server Database Mirroring

 This part will show you how to setup a database mirroring on SQL Server. Because this feature needs 2 different servers (3 if you use witness server) so I will use the server that I've already set on the previous part when showing how to setup SQL Server AlwaysOn Availability Group, you can check that part on: here

So in summary I already have 2 servers that host SQL Server, called SQL01 and SQL02, the servers are already joined to domain and can connect to one another. And now I want to setup a database mirroring from SQL01 to SQL02, here are the steps that should be done:

1. So in here, I created a new database called "Test_Mirror" and also a table inside the DB called SimpleTable on SQL01, I want to make this server the primary server

2. Now the next thing to do is, we need to backup and restore the Test_Mirror DB on the SQL02 which will become the mirror server (secondary server). Database mirroring is quite different than AlwaysOn AG because there is no automatic seeding for the initialization process so you must manually backup and restore the DB to secondary first in the recovering state.

3. Make sure you restore the database in the Mirror Server (SQL02) with no recovery so it will be in the (Restoring) state

4. Now go back to SQL01 and then go to Test_Mirror database properties and navigate to Mirroring page. Press on the Configure Security button then a new pop up window showing database mirroring wizard will show up

5. First, you will be asked if you want to configure a witness server, as for this tutorial I won't be using any witness server so select No

6. Next, you will need to create a mirroring endpoint if you don't have any, but because I already setup AlwaysOn AG before so I already have mirroring endpoint for SQL01 which is Hadr_endpoint. The port for mirroring endpoint is 5022 by default.
Notes: AlwaysOn AG and Database Mirroring use the same mirroring endpoint on SQL Server, so you don't have to setup a separate endpoint

 

7. Next, you are required to connect to the mirror server, in this case, SQL02, so just connect to SQL02 and because there is already Hadr_endpoint used for AlwaysOn AG on SQL02 too there won't be any need to create a new mirroring endpoint there.

8. Now you are asked for service account that will be used for mirroring purposes, but this is optional and you can leave them blanks because you already connected to the primary server and mirror server at the previous steps

9. Now you can just press on Finish and the mirroring security configuration will be all done

10. A new pop up window will appear and you can choose to start mirroring now or start mirroring later, for this tutorial I will immediately start the mirroring so I choose start mirroring

11. Now after starting the mirroring, you will notice that the database status for Test_Mirror in SQL01 (Primary Server) will be Principal, Synchronized and the database status in the SQL02 (Mirror Server) will be Mirror, Synchronized and in Restoring state.

Notes: Here I use the synchronous mirroring without automatic failover, if you want automatic failover for database mirroring then you need to setup the third server which is witness server.
Witness server is a SQL Server instance that's separated from the SQL01 or SQL02 but can be connected from either instance.

Also for synchronous mirroring it's good to be used when you are hosting the primary server and mirror server on same site, if you are hosting database mirroring through different sites (ex: DC and DR site) then it is better to use asynchronous mirroring because if you use synchronous and the network is not capable of handling low latency there will be performance degradation affecting the database queries.

That's all of the steps on setting up database mirroring, just to notice, database mirroring is different from AlwaysOn AG, the mirror server cannot be accessed like the secondary readable database in AlwaysOn AG. Also database mirroring can only host one Primary or Principal Server and one Mirror Server, while AlwaysOn AG can host multiple Secondary Server with readable capability.

If you want to read more details about database mirroring, you can refer to the Microsoft's documentation: Database Mirroring

Notes: it is said in the Microsoft's documentation that database mirroring will be removed in the future, but as of now until the newest version of SQL Server 2022, the feature is still supported