Setting up Database Mirroring using Local Account
We had a need to setup Database Mirroring for a Disaster Recovery Project. We were at time running SQL 2005 Std SP1. i had to start the SQL Server Service with the startup parameter -T1400 to enable Mirroring.
For DB Mirriring to work the SQL Server Service must be started using a domain account (normally) so as to have access on both the Principal and the mirrored server.
This is where i had my first issue. The Sql Server Service Starts before the Network Service on this specific HP ML350 Box. So when i used Domain Account to start the service, it does not start, as it could not find the domain controller.I tried some tips i found on some forums about adding the service as dependency of other services. with no luck.
So i decided to use a local account. I Created a Local Account on the Principal and the Mirrored Server (Same User Name and Same password on both BOX)
Second Issue: SQL Server Agent Service doesnot start if i start the SQL Server Service with a Domain Account or any Local Account. And Mirroring doesnot work with LOCAL SYSTEM ACCOUNT. i had the following error in Event Log:
"SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role)."
To resolve the above:
I start by ensuring that this Local Account has Sysadmin role on the server. I even delete and recreate the SQLAgent login.
SP2 does not solve the problem either.
Then i enable AgentXPs using the below lines
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
Which infact solves the issue.
I was then in front of a 3rd issue: I could not Edit or Execute any Maintenence Plans!!!!!!! I got an error message like Agent Xps is not enable.
I had to re-run the above lines to enable again the Agent XPs.
Then i change the owner of each Maintenence PLAN and specify the Local user to be the owner.
I then implemented Database Mirroring which works fine.
Below is how to Manual Failover from the Mirrored Server
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS