How to FAILOVER databases in MIRRORING?

When our requirement is high data availability & Automatic failover is required in case of any disaster then we can opt for Mirroring of databases. In mirroring we need to maintain a separate WITNESS server if we want to AUTO-FAILOVER databases in case of any disaster. Without a WITNESS server also mirroring can be configured but in such case mirrored databases will not auto failover during disaster, DBA’s need to manually fail over the databases.

Below picture shows mirroring configured between Server A & Server B, Server C is configured as WITNESS server. So this witness server continuously monitors the health/availability of the Server A (i.e.., Main Server) and whenever the database of Server A becomes un-available the witness server will make the database of server B (secondary server) as the principal server.

In mirroring the database of main server is called PRINCIPAL Database & the secondary server database is called Mirrored database. This mirrored database will be in restoring state until principal database is available. Whatever the changes made to the Principal database will automatically move to the mirror database.

If we configured mirroring without a WITNESS server when PRINCIPAL database becomes un-available then in order to FAILOVER we can user below command:

ALTER DATABASE <Database Name>

SET PARTNER FAILOVER;

Or

we can failover through GUI by clicking on ‘Failover’ tab which is in properties of database -> click on Mirroring -> click on Failover.

The below tables are important for getting details of Mirroring.

select * from sys.database_mirroring_witnesses;

Go

select * from sys.database_mirroring_endpoints;

Go

select * from sys.database_mirroring;

‘mirroring_role_desc’ column in sys.database_mirroring table will give the type of the server either Witness, Mirror or Principal.

‘mirroring_state_desc’ column of sys.database_mirroring table will have details weather Mirroring is synchronized or disconnected.

Note: While considering any high data availability features we need to take into consideration many things like space requirements, network traffic, cost of maintenance & so on. Based on our server set up we need to select the best suitable one.

Thanks!!!

How to bring the secondary database online in Log shipping?

In Log shipping by default the secondary database will not come online automatically if the primary database becomes un-available.During log shipping, the secondary database will be in Standby or No Recovery mode that means it will be available for Read-Only.

In order to bring the secondary database involved in log shipping to online we have to use any of below commands. These command are to be used in case the primary database becomes un-available either because of database corruption or database going offline or any other damage to the primary database.

Restore Database <Database Name> WITH Recovery

(or)

Restore Log <Database Name> WITH Recovery

Thanks!!!

Unable to start execution of step 1 (reason: Error authenticating proxy ‘windowsNT\Vinay’, system error: The security database on the server does not have a computer account for this workstation trust relationship.). The step failed.

We are having few SQL job which runs SSIS packages. They were running fine from many days and all of a sudden today they started failing with below error:

Unable to start execution of step 3 (reason: Error authenticating proxy windowsNT\Vinay, system error: The security database on the server does not have a computer account for this workstation trust relationship.).  The step failed.

The windows login ‘windowsNT\Vinay’ is having access to SQL server and having DBO permission given on all the required databases. When verified the windows login is active in Active Directory also. Verified all the System, SQL logs and didn’t found any critical errors. The situation became much critical that all the jobs which are running SSIS failed with same error and they are the must to be run jobs.

After verification only thing noticed is SQL services have been re-started a day prior but no errors reported before or after the reboot. But the error says some where the login is missing the authentication keeping this in mind I have verified the SQL services and noticed they are running under ‘NT Authority\Network Service’ which is not normal, the services were supposed to be running under ‘windowsNT\Vinay’ account. After a quick confirmation I have changed the SQL services to run under ‘windowsNT\Vinay’ account. After restarting the SQL services, I have re-started the jobs which were failing earlier with the error. Now all the jobs started running fine without any error and completed successfully.

If you are receiving this error while logging into server itself then many of the forums have suggested to rejoin the domain. But in my case I was able to login to server without any issue but only issue is my SQL jobs were failing.

Thanks!!