If you are setting up mirroring in a non-domain environment, here are the scripts I "created" to set it up.
/* ---------- 0. Prerequisites- do these before you begin ----------- */
-- On All servers involved, edit the hosts file to have server names and ip addesses for other servers in group.
-- Replace IP addresses, passwords, share locations below as needed.
--Run each section on the appropriate server!!
--On the Primary Server
ALTER Database DBToMirrorName
SET RECOVERY FULL
Backup database DBToMirrorName
to disk = 'c:\share\DBToMirrorName.bak'
with format
backup log DBToMirrorName
to disk = 'c:\share\DBToMirrorNameLog.bak'
with format
--On the Mirror Server
-- Copy over the backups to c:\
RESTORE database DBToMirrorName
FROM DISK = 'c:\DBToMirrorName.bak'
with NORECOVERY
RESTORE log DBToMirrorName
FROM DISK = 'c:\DBToMirrorNameLog.bak'
WITH FILE=1, NORECOVERY
/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE PRIMARY_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password' -- Replace with real password
GO
CREATE CERTIFICATE PRIMARY_CERT
WITH SUBJECT = 'PRIMARY_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE PRIMARY_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO
BACKUP CERTIFICATE PRIMARY_CERT
TO FILE = 'C:\share\PRIMARY_CERT.cer'
GO
-- then copy certificate to other two machines
/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE SECONDARY_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password' -- Replace with real password
GO
CREATE CERTIFICATE SECONDARY_CERT
WITH SUBJECT = 'SECONDARY_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SECONDARY_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO
BACKUP CERTIFICATE SECONDARY_CERT
TO FILE = 'C:\SECONDARY_CERT.cer'
GO
-- then copy certificate to other two machines
/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE WITNESS_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password' -- Replace with real password
GO
CREATE CERTIFICATE WITNESS_CERT
WITH SUBJECT = 'WITNESS_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE WITNESS_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO
BACKUP CERTIFICATE WITNESS_CERT
TO FILE = 'C:\WITNESS_CERT.cer'
GO
-- then copy certificate to other two machines
/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */
/* enable inbound from the mirror */
DROP CERTIFICATE SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE CERTIFICATE SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'C:\Share\SECONDARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO
/* enable inbound from the witness */
DROP CERTIFICATE WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE CERTIFICATE WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:\share\WITNESS_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO
/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */
/* enable inbound from the primary */
DROP CERTIFICATE PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE CERTIFICATE PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:\PRIMARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO
/* enable inbound from the witness */
DROP CERTIFICATE WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE CERTIFICATE WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:\WITNESS_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO
/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */
/* enable inbound from the mirror */
DROP CERTIFICATE SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE CERTIFICATE SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'c:\SECONDARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO
/* enable inbound from the primary */
DROP CERTIFICATE PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE CERTIFICATE PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:\PRIMARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO
/* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */
ALTER DATABASE DBToMirrorName
SET PARTNER OFF
GO
ALTER DATABASE DBToMirrorName
SET PARTNER = 'TCP://xxx.xxx.xxx.xx2:7024'; --Replace IP
GO
/* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */
ALTER DATABASE DBToMirrorName
SET PARTNER OFF
GO
ALTER DATABASE DBToMirrorName
SET PARTNER = 'TCP://xxx.xxx.xxx.xx3:7024'; --Replace IP
GO
/* -------- 9. SET PRIMARY'S (and thus the Mirror's) WITNESS TO THE WITNESS SERVER -------- */
ALTER DATABASE DBToMirrorName
SET WITNESS OFF
GO
ALTER DATABASE DBToMirrorName
SET WITNESS = 'TCP://xxx.xxx.xxx.xx4 :7024'; --Replace IP
GO
--Mike