|
Principal
|
Mirror
|
|
Prepare instances
|
|
Create database
master key, if needed
|
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<strong password>’
GO
|
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<strong password>’
GO
|
|
If you want or
need to delete the data base master key
|
USE master
GO
DROP MASTER KEY
GO
|
USE master
GO
DROP MASTER KEY
GO
|
|
Make certificates
|
USE master
GO
CREATE CERTIFICATE LAB_SRV1_cert
WITH SUBJECT
= ‘LAB-SRV1
certificate’
GO
|
USE master
GO
CREATE CERTIFICATE LAB_SRV2_cert
WITH SUBJECT
= ‘LAB-SRV2
certificate’
GO
|
|
If you want or need to
delete certificates
|
USE master
GO
DROP CERTIFICATE LAB_SRV1_cert
GO
|
USE master
GO
DROP CERTIFICATE LAB_SRV2_cert
GO
|
|
Create mirroring
endpoint
|
USE master
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE =
STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING(
AUTHENTICATION
= CERTIFICATE
LAB_SRV1_cert
, ENCRYPTION = REQUIRED ALGORITHM
AES
, ROLE = ALL
)
GO
|
USE master
GO
CREATE ENDPOINT Endpoint_Mirroring
STATE =
STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING(
AUTHENTICATION
= CERTIFICATE
LAB_SRV2_cert
, ENCRYPTION = REQUIRED ALGORITHM
AES
, ROLE = ALL
)
GO
|
|
If you want or need to delete endpoint
|
USE master
GO
DROP ENDPOINT [Endpoint_Mirroring]
GO
|
USE master
GO
DROP ENDPOINT [Endpoint_Mirroring]
GO
|
|
Backup certificates
|
USE master
GO
BACKUP CERTIFICATE LAB_SRV1_cert
TO FILE
= ‘C:\BACKUPS\LAB_SRV1_cert.cer’
GO
|
USE master
GO
BACKUP CERTIFICATE LAB_SRV2_cert
TO FILE
= ‘C:\BACKUPS\LAB_SRV2_cert.cer’
GO
|
|
Copy certificates
between instances
|
Use any secure copy method
|
Use any secure copy method
|
|
Configure inbound connections
|
|
Create logins
|
USE master
GO
CREATE LOGIN LAB_SRV2_login
WITH PASSWORD
= ‘<strong
password>’
GO
|
USE master
GO
CREATE LOGIN LAB_SRV1_login
WITH PASSWORD
= ‘<strong password>’
GO
|
|
Create user for
login
|
USE master
GO
CREATE USER LAB_SRV2_user FOR
LOGIN LAB_SRV2_login
GO
|
USE master
GO
CREATE USER LAB_SRV1_user FOR
LOGIN LAB_SRV1_login
GO
|
|
Associate the
certificate with the user
|
USE master
GO
CREATE CERTIFICATE LAB_SRV2_cert
AUTHORIZATION LAB_SRV2_user
FROM FILE
= ‘C:\BACKUPS\LAB_SRV2_cert.cer’
GO
|
USE master
GO
CREATE CERTIFICATE LAB_SRV1_cert
AUTHORIZATION LAB_SRV1_user
FROM FILE
= ‘C:\BACKUPS\LAB_SRV1_cert.cer’
GO
|
|
Grant CONNECT
permission at login for the remote mirroring endpoint
|
USE master
GO
GRANT CONNECT
ON ENDPOINT::Endpoint_Mirroring TO
[LAB_SRV2_login]
GO
|
USE master
GO
GRANT CONNECT
ON ENDPOINT::Endpoint_Mirroring TO
[LAB_SRV1_login]
GO
|
|
Prepare database
|
|
Create database
|
CREATE DATABASE [DBMTest] ON PRIMARY
(NAME =
N’DBMTest’,
FILENAME = N’C:\MSSQL\DATA\DBMTest.mdf’)
LOG ON
(NAME =
N’DBMTest_log’ ,
FILENAME = N’C:\MSSQL\DATA\DBMTest_log.ldf’)
GO
USE [DBMTest]
GO
CREATE TABLE [dbo].[TTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DayAndTime] [datetime] NOT NULL
)
GO
|
|
|
Backup database
|
BACKUP DATABASE [DBMTest]
TO
DISK =
N’C:\BACKUPS\DBMTest.bak’
WITH NOFORMAT,
INIT,
NAME = N’DBMTest-Full
Database Backup’,
SKIP,
NOREWIND,
NOUNLOAD,
STATS =
10
GO
|
|
|
Backup transaction
log
|
BACKUP LOG [DBMTest]
TO DISK = N’C:\BACKUPS\DBMTest_log.bak’
WITH NOFORMAT,
INIT,
NAME = N’DBMTest-Transaction
Log Backup’,
SKIP,
NOREWIND,
NOUNLOAD,
STATS =
10
GO
|
|
|
Restore database
|
|
RESTORE DATABASE [DBMTest]
FROM
DISK =
N’C:\BACKUPS\DBMTest.bak’
WITH
FILE =
1,
MOVE N’DBMTest_log’
TO N’C:\MSSQL\DATA\DBMTest.ldf’,
NORECOVERY,
NOUNLOAD,
STATS =
10
GO
|
|
Restore transaction
log
|
|
RESTORE LOG [DBMTest]
FROM
DISK =
N’C:\BACKUPS\DBMTest_log.bak’
WITH
FILE =
1,
NORECOVERY,
NOUNLOAD,
STATS =
10
GO
|
|
Configure the mirroring
partners and start mirroring
|
|
First, on the mirror
instance
|
|
ALTER DATABASE DBMTest
SET PARTNER
= ‘TCP://LAB-SRV1:5022′
GO
|
|
And then, on
the principal instance
|
ALTER DATABASE DBMTest
SET PARTNER
= ‘TCP://LAB-SRV2:5022′
GO
|
|
|
Configure performance
and safety mode
|
|
High performance (asynchronous
mode)
|
ALTER DATABASE DBMTest SET
PARTNER SAFETY
OFF
GO
|
|
|
High safety
(synchronous mode)
|
ALTER DATABASE DBMTest SET
PARTNER SAFETY
FULL
GO
|
|
|
Managing database
mirroring
|
|
Pause mirroring
|
ALTER DATABASE DBMTest SET PARTNER SUSPEND
|
|
Resume mirroring
|
ALTER DATABASE DBMTest SET PARTNER RESUME
|
|
Manual failover (asynchronous
mode)
|
ALTERDATABASE DBMTest SET SAFETY FULL
GO
ALTERDATABASE DBMTest SET PARTNER FAILOVER
GO
ALTERDATABASE DBMTest SET SAFETY FULL
GO
|
|
|
Manual failover
(synchronous mode)
|
ALTERDATABASE DBMTest SET PARTNER FAILOVER
GO
|
|
|
Remove database mirroring
|
ALTER DATABASE DBMTest SET PARTNER OFF
|
