Database Mirroring between Servers outside domain (workgroup)

Database Mirroring between Servers outside domain (workgroup)

Here is the highlight summary of the steps:
All the steps below should be done in each server instance(Principal/Mirror)
SQL scripts for each step you can find at the end,
and I suppose the you already restored the DB (and transaction logs, if needed)
in Mirror SQL Server with NORECOVEY of course.

1.In the master database, create a database master key.
2.In the master database, create an encrypted certificate.
3.Create a mirroring endpoint using the created certificate.
4.Back up the certificate to a file and copy it to the other server.

After you complete the steps above in both servers, do the following
also in both servers in master database:

5.Create a SQL Server login in each Server
6.Create a database user for created login.
7.Associate the certificate with the database user created in step 6.
8.Grant CONNECT permission on the SQL Server login for created
   mirroring endpoint.

Now let`s see the SQL Scripts,
note that the scripts have different numeration then general steps above.-------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance */
-------------------------------------------------------------------------------------------------------
-- Step 1 :
--                    CREATE MASTER KEY,CREATE CERTIFICATE
--                   CREATE ENDPOINT,BACKUP CERTIFICATE to file
---------------------------------------------------------------------------------
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
 --Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE PrincipalServerCertificate
WITH SUBJECT = 'PrincipalServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
 --Create the database mirroring endpoint for the principal server using the certificate for authentication
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE PrincipalServerCertificate
,ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE PrincipalServerCertificate
TO FILE = 'c:\PrincipalServerCertificate.cer'
GO
 --------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Mirror Instance */
---------------------------------------------------------------------------------------------------------------------------
-- Step 2 :
--              CREATE MASTER KEY,CREATE CERTIFICATE,
--              CREATE ENDPOINT,BACKUP CERTIFICATE to file
----------------------------------------------------------------------------------
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE MirrorServerCertificate
WITH SUBJECT = 'MirrorServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE MirrorServerCertificate,
ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE MirrorServerCertificate TO FILE = 'c:\MirrorServerCertificate.cer';
GO
------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance.    
     The MirrorServerCertificate.cer needs to be copied on the Principal Server.
*/
--------------------------------------------------------------------------------------------------------------------------- Step 3 :
--               CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Mirror server)
--               and grant the created User to it.
------------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN MirrorLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER MirrorUser FOR LOGIN MirrorLoginGO
CREATE CERTIFICATE MirrorServerCertificate AUTHORIZATION MirrorUser
FROM FILE ='c:\MirrorServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [MirrorLogin]GO
/* Execute this against the Mirror Instance.  
    The PrincipalServerCertificate.cer needs to be copied on the Mirror Server.
*/
------------------------------------------------------------------------------------------------------------------------ Step 4 :
--                CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Principal server)
--                and grant the created User to it.
----------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN PrincipalLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER PrincipalUser FOR LOGIN PrincipalLoginGO
CREATE CERTIFICATE PrincipalServerCertificate AUTHORIZATION PrincipalUser FROM FILE = 'c:\PrincipalServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [PrincipalLogin]GO
/* Execute this against the Mirror Instance.*/
------------------------------------------------------------------------------------------------
-- Step 5 :
--             Prepare the mirror server for the database mirroring session
------------------------------------------------------------------------------------------------
ALTER DATABASE TESTDB SET PARTNER = 'TCP://PrincipalServerIP5022'
GO
/* Execute this against the Principal Instance.*/
--------------------------------------------------------------------------------------------------- Step 6 :
--            Prepare the Principal server for the database mirroring session
-------------------------------------------------------------------------------------------------
ALTER DATABASE TestDBSET PARTNER = 'TCP://MirrorServerIP:5023'
GO
-- Set the Mirroring to Asynchronous mode (if needed)
ALTER DATABASE TestDB  SET PARTNER SAFETY OFF
GOThat`s all after Step 6 the mirroring should be initialized.