Monday, May 11, 2009

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.

7 comments:

Marching Bug said...

Your post saved my hours of banging my head against the wall.

Thanks

Anonymous said...

Thanks, the steps were very helpful.

Kevin said...

I did everything exactly as you mentioned, but as soon as i hit the button Start Mirroring for the first time, i get the error that the server network address tcp://:5022 does not exist, and there was an error in ALTER DATABASE (error 1418).

I'm sure that the firewalls are OFF, that the port is actually there (watched with netstat).
The database on the secondary host went from (Restoring...) to (In recovery)
So it did it's ALTER DATABSE SET PARTNER thing there...

Somehow the Principal cannot connect to the mirror host?

(No problem with the Management studio cross server and fileshares, so network is OK).

What can i do(I tried this really like 10 ten times allready!)

Kevin said...

I got it!

You have to add the login you've created for the services to run at, and grant them connect permission on the endpoints on both servers.

daspeac said...

it seems you have never heard about the way of recover database sql

Zach said...

Kevin, I owe you a coke!
Thanks Man!

Autonoleggio Italia said...

Confrontare offerte di autonoleggio da tutti i fornitori in Italia Prezzi bassi per B-RENT, AUTOCLICK, MAGGIORE, SIXT & INTERNATIONAL di autonoleggio