SQL Server Mirroring

Before starting Mirroring we need 3 SQL Server’s instance which means same version (standard or enterprise), but for third server it can be standard, Enterprise, workgroup or express. Witness server will be the one pinging the other 2 servers if there’s something wrong. This is the server that has the ability to initiate an automatic failover.

Witness server will not contain any database so we can use any server other than Express Edition

Step 1:  we need to have below 3 servers in hand

Principal

Mirror

Witness

Step 2:  SQL Server is using an Active Directory account. Ideally, use the same account for all SQL Servers

Step 3: Check Primary Database is in Full Recovery model

Step 4: Backup the database on the Principal SQL Server

Step 5: Create a database with the same name from the Principal SQL Server on the Mirroring SQL Server, and then restore the backup on the Mirroring SQL Server with the option to overwrite the existing database checked and RESTORE WITH NORECOVERY option.

Step 6: You will notice it's in a Restoring mode this will be in a permanent Restoring state to prevent users from accessing the database. It will be only user accessible if the database fails over to the Mirror and now the old Principal will go to the recovering state.

Step 7: Start the mirroring configuration process on the Principal SQL Server. Right-click the Database –> Properties –> Mirroring and click Configure Security.

Step 8: On the Include Witness Server screen, select Yes and click next

Step 9: On choose servers to Configure screen, select witness server instance and click next

Step 10: Now choose Principal SQL Server Instance and click next

Step 11: Now choose mirror server instance and click next

Step 12: now choose witness Instance

Step 13: Now enter the SQL Server Service Accounts for each SQL Server Instance, but if all of your SQL instances are using the same account, then just leave it blank.

Step 14: click Finish