For an instructor lead, in-depth look at learning SQL click below.
Database mirroring is a powerful feature of SQL Server that allows you to create and maintain redundant copies of a database, also known as ‘mirrors’. The main objective of this feature is to increase the availability of the database and to safeguard it from any inadvertent losses due to system failures or disasters.
Understanding Database Mirroring
The concept of database mirroring in SQL Server involves two separate copies of a single database that usually reside on different machines. We have the principal server hosting the principal database, and the mirror server hosting the mirror database. Achieved by implementing a relationship between the principal server and the mirror server, Database Mirroring essentially guarantees that every transaction made on the principal database is also applied to the mirror database.
Implementing Database Mirroring
Before we get into the SQL code to set up Database Mirroring, it is crucial to understand that the SQL Server instances on both the principal and mirror server must be running the same edition of SQL Server. Now let’s dive into how to implement Database Mirroring using SQL Server Management Studio (SSMS).
First, restore the full backup of the database on the mirror server with NORECOVERY.
|
1 2 3 4 5 6 |
-- Syntax RESTORE DATABASE dbName FROM DISK = 'pathToBackupFile' WITH NORECOVERY |
Next, set the partner on the principal server.
|
1 2 3 4 5 |
-- Syntax ALTER DATABASE dbName SET PARTNER = 'TCP://mirrorServerAddress:Port' |
Then, set the partner on the mirror server.
|
1 2 3 4 5 |
-- Syntax ALTER DATABASE dbName SET PARTNER = 'TCP://principalServerAddress:Port' |
The Role of the Witness Server
Apart from the principal and mirror server, Database Mirroring can also include a third server, known as the ‘Witness Server’. The Witness Server aids in automatic failover by ensuring the principal server is up and running. If it isn’t, the Witness Server initiates a failover to the mirror server.
Here is the SQL command to set up a Witness Server:
|
1 2 3 4 5 |
-- Syntax ALTER DATABASE dbName SET WITNESS = 'TCP://witnessServerAddress:Port' |
Conclusion
In conclusion, Database Mirroring is a subject of vital importance in assuring database availability and data protection. Constructed correctly, it can provide almost instant failover from a failed SQL Server instance to a standby server with no data loss.
Your business needs and technology infrastructure will determine what kind of redundancy plan you need for your SQL Server database. However, for a system that requires high availability, data protection, and the assurance of business continuity, database mirroring is a feature worthy of consideration.