Thursday, November 21, 2013

Setup Database Mirroring between SQL Server 2005 and SQL Server 2012

SQL 2005SP4 SQL2012SP1
USE [master]
GO
CREATE LOGIN [donghua] WITH PASSWORD=N'p_ssw0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'donghua', @rolename = N'sysadmin'
GO
USE [master]
GO
CREATE LOGIN [donghua] WITH PASSWORD=N'p_ssw0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [donghua]
GO
create database TestDb;
go
use TestDb;
go
create table t(c1 varchar(20));
insert into t values(getdate());
go
select * from t;
Backup & Restoration
Set Full recovery model USE master;
GO
ALTER DATABASE TestDB
SET RECOVERY FULL;
GO
Backup Database BACKUP DATABASE TestDb
    TO DISK = 'C:\Backup\TestDb_Full.bak'
    WITH FORMAT
GO
Copy the backup to this Server
Restore Database RESTORE DATABASE TestDb
   FROM DISK='C:\backup\TestDb_Full.bak'
   WITH NORECOVERY,
      MOVE 'TestDb' TO
         'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb.mdf',
      MOVE 'TestDb_log' TO
         'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb_log.LDF';
GO
Backup Log BACKUP LOG TestDb
    TO DISK = 'C:\Backup\TestDb_Log.bak'
    WITH FORMAT
GO
Copy the backup to this Server
RESTORE LOG TestDb
    FROM DISK = 'C:\Backup\TestDb_Log.bak'
    WITH NORECOVERY
GO
The two server instances run in nontrusted Windows domains, so certificate-based authentication is required
On the master database, create the database master key, if needed. USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!QAZXSW#$%e';
GO
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '093j45&*he';
GO
Make a certificate for this server instance. USE master;
GO
CREATE CERTIFICATE SQL2005_cert
   WITH SUBJECT = 'SQL2005 certificate',
   START_DATE = '20121031',
   EXPIRY_DATE = '20151031';
GO
USE master;
GO
CREATE CERTIFICATE SQL2012_cert
   WITH SUBJECT = 'SQL2012 certificate',
   START_DATE = '20121031',
   EXPIRY_DATE = '20151031';
GO
Create a mirroring endpoint for server instance using the certificate. CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE SQL2005_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=7024
      , LISTENER_IP = ALL
   )
   FOR DATABASE_MIRRORING (
      AUTHENTICATION = CERTIFICATE SQL2012_cert
      , ENCRYPTION = REQUIRED ALGORITHM AES
      , ROLE = ALL
   );
GO
Back up the HOST_A certificate, and copy it to other system, HOST_B. BACKUP CERTIFICATE SQL2005_cert TO FILE = 'C:\Backup\SQL2005_cert.cer';
GO
BACKUP CERTIFICATE SQL2012_cert TO FILE = 'C:\Backup\SQL2012_cert.cer';
GO
Setup inbound connection
Create a login on HOST_A for HOST_B. USE master;
CREATE LOGIN sql2012_login WITH PASSWORD = '1Sample_Strong_Password!!#';
GO
USE master;
CREATE LOGIN sql2005_login WITH PASSWORD = '2Sample_Strong_Password!!#';
GO
Create a user for that login. CREATE USER sql2012_user FOR LOGIN sql2012_login;
GO
CREATE USER sql2005_user FOR LOGIN sql2005_login;
GO
Associate the certificate with the user. CREATE CERTIFICATE SQL2012_cert
   AUTHORIZATION sql2012_user
   FROM FILE = 'C:\backup\SQL2012_cert.cer'
GO
CREATE CERTIFICATE SQL2012_cert
   AUTHORIZATION sql2012_user
   FROM FILE = 'C:\backup\SQL2012_cert.cer'
GO
Grant CONNECT permission on the login for the remote mirroring endpoint. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sql2012_login];
GO
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sql2012_login];
GO
Setup mirror database
On the mirror server instance on HOST_B, set the server instance on HOST_A as the partner (making it the initial principal server instance). --At HOST_B, set server instance on HOST_A as partner (principal server):
ALTER DATABASE TestDb
    SET PARTNER = 'TCP://WIN2003SQL2005:7024';
GO
On the principal server instance on HOST_A, set the server instance on HOST_B as the partner (making it the initial mirror server instance) --At HOST_A, set server instance on HOST_B as partner (mirror server).
ALTER DATABASE TestDb
    SET PARTNER = 'TCP://WIN-HN1Q39OK9JF:7024';
GO
This example assumes that the session will be running in high-performance mode. To configure this session for high-performance mode, on the principal server instance (on HOST_A), set transaction safety to OFF. --Change to high-performance mode by turning off transacton safety.                                                     ALTER DATABASE TestDb
    set partner SAFETY off
GO
Setup mirror database
Create database snapshot CREATE DATABASE TestDb_SS ON
( NAME = TestDb, FILENAME = 'D:\SQL2012\MSSQL11.PROD\MSSQL\DATA\TestDb.ss' )
AS SNAPSHOT OF TestDb;
GO

Msg 946, Level 14, State 1, Line 1
Cannot open database 'TestDb_SS' version 611. Upgrade the database to the latest version.
Msg 1823, Level 16, State 7, Line 1
A database snapshot cannot be created because it failed to start.
Perform insert in SQL2005 use testdb
go
insert into t values(getdate());
go
Manual Failover USE master
go
ALTER DATABASE TestDb SET PARTNER FAILOVER
go

Msg 1477, Level 16, State 1, Line 1
The database mirroring safety level must be FULL to manually failover database "TestDb".  Set safety level to FULL and retry.
Force Service in a Database Mirroring Session  use master;
go
ALTER DATABASE TestDb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
go
Msg 1455, Level 16, State 106, Line 1
The database mirroring service cannot be forced for database "TestDb" because the database is not in the correct state to become the principal database.
Use the restore command to open the DB Use master
go
ALTER DATABASE TestDb SET PARTNER OFF
go
RESTORE DATABASE TestDb WITH RECOVERY
go

-- Data is in-sync