T-SQL reference for Database Mirroring

• How to manually failover to partner server?

There are 2 ways to manually failover to partner server in SQL server database mirroring session.
First is from primary server server and second is from secondary server. See below script for partner failover.

--1. Manual Failover from Primary server

ALTER DATABASE <Database_Name> SET PARTNER FAILOVER;

--2. Manual Failover from Secondary server

ALTER DATABASE <Database_Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

Failover from primary server does not cuase any data loss but if you do it from secondary server then you are forcing secondary server to loss data. Data loss will only happend if primary server is not accesible while failover.

• How to remove database mirroring session?

Use below script to remove database mirroring session. This script can be used on either primary server or on secondary server.

--Remove mirroring for database

ALTER DATABASE <Database_Name> SET PARTNER OFF;

• How to stop and start databaes mirroring session?

Use below script to pause and resume the database mirroring session. You can SUSPEND mirroring session from either primary server or from secodary server. But It can only be RESUMEd from primary server.

 

--Pause/Suspend database mirroring session

ALTER DATABASE <Database_Name> SET PARTNER SUSPEND;

--Resume database mirroring session

ALTER DATABASE <Database_Name> SET PARTNER RESUME;

• How to add/remove witness server to/from already running database mirroring session?

Use below script to add or remove witness server to/from already running database mirroring session

--Add witness server to database mirroring session

ALTER DATABASE <Database_Name> SET WITNESS <witness_server>;

--Remove witness server from database mirroring session

ALTER DATABASE <Database_Name> SET WITNESS OFF;

SET WITNESS is only allowed on primary server.