Database backup is nowadays very critical aspect of any development and maintains project.

What is Database Mirroring?

Database mirroring is primarily a software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.

You cannot mirror the master, msdb, tempdb, or model databases.

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. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Benefits of Database Mirroring

Database mirroring is a simple strategy that offers the following benefits,

How Database Mirroring Works

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

Operating Modes

A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.

In high-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It does not wait for an acknowledgment from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation enables the principal server to run with minimum transaction latency, at the potential risk of some data loss.

All database mirroring sessions support only one principal server and one mirror server. This configuration is shown in the following illustration.

SQL Server

High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.

The following illustration shows a configuration that includes a witness.

SQL Server

Solution

Create a SQL agent job that does the following,

  1. Job Step 1 – Check if the database‘s mirroring role on the current instance is primary. Proceed to Step2 if condition is true; or abort the job with success
  2. Job Step2 – perform database backup

Above mentioned agent job can be created on both primary and mirrored instance and run on a schedule. Even if the database fails over, database backup completes successfully.

Sample Script

This script assumes that c:\Backup folder is available on machines running primary and mirrored instance.

  1. USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT
  2. SELECT
  3. @ReturnCode = 0 IF NOT EXISTS (
  4. SELECT
  5. name
  6. FROM
  7. msdb.dbo.syscategories
  8. WHERE
  9. name = N '[Uncategorized (Local)]’ AND category_class=1)
  11. BEGIN
  13. EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N' [Uncategorized (Local) ]’ IF (
  14. @@ERROR <> 0
  15. OR @ReturnCode <> 0
  16. ) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N’Backup Mirrored DB’,
  17. @enabled = 1,
  18. @notify_level_eventlog = 0,
  19. @notify_level_email = 0,
  20. @notify_level_netsend = 0,
  21. @notify_level_page = 0,
  22. @delete_level = 0,
  23. @description = N’No description available.’,
  24. @category_name = N '[Uncategorized (Local)]’,
  26. @job_id = @jobId OUTPUT
  28. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  30. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Check if database on current instance is not primary’,
  32. @step_id=1,
  34. @cmdexec_success_code=0,
  36. @on_success_action=3,
  38. @on_success_step_id=0,
  40. @on_fail_action=1,
  42. @on_fail_step_id=0,
  44. @retry_attempts=0,
  46. @retry_interval=0,
  48. @os_run_priority=0, @subsystem=N’TSQL’,
  50. @command=N’DECLARE @mirroring_role int
  52. SELECT @mirroring_role = mirroring_role
  54. FROM sys.database_mirroring m, sys.databases d
  56. WHERE m.database_id = d.database_id
  60. AND d.name = ”DemoDatabase”
  62. — If mirroring role is 2 ; ie not principal
  64. IF (@mirroring_role = 2)
  66. BEGIN
  68. RAISERROR (N”Mirroring role is 2”,16,1)
  70. END’,
  72. @database_name=N’master’,
  74. @flags=0
  76. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  78. EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Backup a Mirrored Database’,
  80. @step_id=2,
  82. @cmdexec_success_code=0,
  84. @on_success_action=1,
  86. @on_success_step_id=0,
  88. @on_fail_action=2,
  90. @on_fail_step_id=0,
  92. @retry_attempts=0,
  94. @retry_interval=0,
  96. @os_run_priority=0, @subsystem=N’TSQL’,
  98. @command=N’– Script to backup database
  100. BACKUP DATABASE [DemoDatabase]
  102. TO DISK = N”C:\Backup\DemoDatabase.bak” ‘,
  104. @database_name=N’master’,
  106. @flags=0
  108. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  110. EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
  112. IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  114. EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local) ’ IF (
  115. @@ERROR <> 0
  116. OR @ReturnCode <> 0
  117. ) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback : IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave : GO

Hope this helps !!!!