Script to Database Mirroring in SQL Server by serverku

Apart from replication articles which I have written earlier, today i want to share for database mirroring configure with windows authentication without automatic failover. So let me share steps and required scripts for the same.

1. Make sure principal database has FULL recovery model.

2. Make sure Windows User on which mirror configured has enough access of both servers and databases.

3. Make sure TCPIP port open in firewall for both SQL Server instances on both server accordingly if firewall enabled.

4. Make sure database service running on windows user for which mirroring endpoint will be created.

5. Take a full backup of principal database and restore at partner server with norecovery.

6. Take a transaction backup of principal database and restore at partner server with norecovery and make sure all log backups restored with norecovery created after full backup of principal database.

7. Create a mirroring endpoint.

USE master
go

CREATE ENDPOINT [DatabaseMirroring]
AUTHORIZATION [<DomainName>\<UserName>]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = DISABLED)

8.  Get mirroring role and status details for confirmation.

USE master
go

SELECT 
 state_desc,
 type_desc 
FROM sys.database_mirroring_endpoints

9. Make sure 5022 port or whatever port used should be open in firewall if firewall enabled.

10. Run following command at partner server.

USE master
go

ALTER DATABASE <DatabaseName>
SET PARTNER ='TCP://<PrincipalHostName>.<DomainName>.local:5022'

11. Run following command at principal server.

USE master
go

ALTER DATABASE <DatabaseName>
SET PARTNER ='TCP://<PartnerHostName>.<DomainName>.local:5022'

12.Run following command in one of server if want to change high performance or high safety as per need and if it is supported.

USE master
go

-- OFF : High performance
-- FULL : High Safety
ALTER DATABASE <DatabaseName>
SET SAFETY OFF 

Hope you like this post and might be useful to you.