Database Mirroring for Non Domain MS SQL Servers

Configuration of Database Mirroring for Non-Domain based SQL Servers

Assumptions
2 SQL Servers running SQL 2005 SP1 (or greater)
Network connectivity between them
System privileges to create user accounts and change service log on credentials
Databases to be mirrored set to FULL recovery model

Initial decision is which server is going to be your primary

Once this is decided, create a new account with a strong password, that you will run SQL Server and the SQL Server Agent with
Add this account to the group SQLServer2005MSSQLUser$SERVER-NAME$SQLINSTANCE

Set the services "SQL Server ($SERVERINSTANCE)" and "SQL Server Agent ($SERVERINSTANCE)" to run under this account

This should be done on BOTH SQL Server Machines

On BOTH machines, run the following T-SQL queries to build the Mirroring Endpoints

CREATE ENDPOINT mirror
STATE = STARTED
AS TCP
(LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING
(AUTHENTICATION = WINDOWS, ENCRYPTION = SUPPORTED,ROLE = ALL)

Now, on the PRIMARY, select the database to be mirrored and back it up (Full Backup), copy this backup to the SECONDARY server and restore using the "WITH NORECOVERY" option This will leave the SECONDARY database in the Restoring state.

Back on the PRIMARY server, right click on the database and select Tasks > Mirror

Run through the "Configure Security..." wizard
Do not use a witness server
You may need to log on to the SECONDARY SQL Server using appropriate credentials. It will automatically detect the endpoint created earlier.
When prompted for a user for the principal and Mirror, DO NOT ENTER ANY DETAILS
The Mirroring Configuration Wizard will complete with 0 errors or Warnings.
Select Do Not Start Mirroring

Still on the primary server, select the Principal server address and change it so it is a FQDN (including a domain)
for example server1.sqltesting.com:5022
For the most likely to succeed option inspect the Mirror server address.

Once this is set, hit Start Mirroring. You will get an error message that the mirror database has insufficient transaction log data to preserve the log backup chain of the principal database...
Hit OK on this BUT do not ok or cancel off the datbase properties page for the PRIMARY database. This indicates that they can successfully communicate.

On Management Studio on the PRIMARY, right click on the database and select tasks> Backup
In the backup type, select Transaction Log and back it up to an appropriate location.

On the SECONDARY server,right click on the database to be mirrored and select Tasks > Restore Transaction Log
Select the path to the transaction log backup from the PRIMARY server.
Restore this ensuring to select the WITH NORECOVERY option on the options screen.

On the PRIMARY server, on the still open properties page, do not change the Operating mode unless you know what you are doing, then click Start Mirroring

There will be a slight delay and the Status will change to Synchronizing, hit refresh and as long as not too many transactions have gone through, it should change to Synchronized.

You can now close this window and have mirrored databases.