Configure Database Mirroring in SQL Server

  1. Principal database server: a source database that you configure for the mirroring.
  2. Mirror database server: a destination database that you must restore with NORECOVERY. Ideally, a mirrored database should be on a separate server.
  3. Witness Server: an optional component that you can set up to configure the mirror with automatic failover.
  4. Endpoint: the communication medium used by the principal and mirror server. The mirror listens on port number 5022; however, you can change it during the configuration.
  1. Principal and mirror instance must be running on SQL Server 2005 SP1 and later edition.
  2. SQL Server version and edition must be the same on the principal and mirror instance.
  3. Principal and mirror databases must have the same name and database collation. Both databases must be running in the FULL recovery model.
  4. You need to restore a full backup and at least one transaction log backup on the mirrored database. For the mirrored database restore, you must use NORECOVARY.
  5. If you want to configure the automatic failover, then you must set up a witness server. A witness server can be any version of SQL Server. For cost savings, you can use SQL Server Express edition as a witness server.

Configure the database mirror

USE [master]
BACKUP DATABASE [AdventureWorks2017] TO DISK = N'\\DC.Local\Backup\AdventureWorks2017.bak' WITH COMPRESSION, STATS = 5
GO
USE [master]
BACKUP Log [AdventureWorks2017] TO DISK = N'\\DC.Local\Backup\ Adv_Logbackup.trn' WITH COMPRESSION, STATS = 5
GO
USE [master]
RESTORE DATABASE [AdventureWorks2017] FROM DISK = N'\\DC.Local\Backup\AdventureWorks2017.bak' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
RESTORE LOG [AdventureWorks2017] FROM DISK = N'\\DC.Local\Backups\Adv_Logbackup.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
SELECT 
d.name as [Mirrored database],
mirroring_state_desc as [Mirrored database state],
mirroring_role_desc as [Mirrored database role],
mirroring_partner_instance as [Mirrored database instance],
case(m.mirroring_witness_state_desc) when 'Unknown' then 'No Witness Server Configured'
else m.mirroring_witness_name end as 'Witness Server'
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
WHERE mirroring_state_desc is not null
ORDER BY d.name,mirroring_state_desc

Test the failover

/*Create demo table*/
CREATE TABLE [Person](
[BusinessEntityID] [int] NOT NULL,
[PersonType] [nchar](2) NOT NULL,
[NameStyle] [dbo].[NameStyle] NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [dbo].[Name] NOT NULL,
[MiddleName] [dbo].[Name] NULL,
[LastName] [dbo].[Name] NOT NULL,
[Suffix] [nvarchar](10) NULL,
[EmailPromotion] [int] NOT NULL
CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED
(
[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/*Insert Data*/
insert into person (BusinessEntityID,PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailPromotion)
select BusinessEntityID,PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailPromotion from Person.Person
Where Title is not null
/*Output */
(1009 rows affected)
  1. Stop the SQL Server services on SQL01.dc.local.
  2. Insert demo records in employee tables.
  3. Start the SQL Server services on SQL01.dc.local.
SELECT 
d.name as [Mirrored database],
mirroring_state_desc as [Mirrored database state],
mirroring_role_desc as [Mirrored database role],
mirroring_partner_instance as [Mirrored database instance],
case(m.mirroring_witness_state_desc) when 'Unknown' then 'No Witness Server Configured'
else m.mirroring_witness_name end as 'Witness Server'
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
WHERE mirroring_state_desc is not null
ORDER BY d.name,mirroring_state_desc
select Top 100 BusinessEntityID,PersonType,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailPromotion from Person.Person
Where Title is null
/*Output */
(100 rows affected)
ALTER DATABASE [AdventureWorks2017] SET PARTNER FAILOVER
Select * from person where title is null

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store