Database Mirroring Basics Need to Know and Keywords and Failover and facts

Principal server

Mirror Server

Principal You can access the data

Mirror Server you can not access the data

Fail over we can do two ways based on Operating Mode

1. Script

2. GUI

1. Script

a) If Operating Mode is Synchronous then we can directly issue command below on Principal Server

ALTER DATABASE databasename SET PARTNER FAILOVER


b) If Operating Mode is ASynchronous then we can  not directly issue command below on Principal Server because We can not directly fail over when database is in ASynchronous Mode it will not allow

We Need to convert/Change Operating Mode from  ASynchronous to Synchronous Mode and then Failover Manually

If you are using asynchronous mode issue this statement for each database:

ALTER DATABASE databasename SET SAFETY FULL-----------------to make synchronous mode

Once the databases are synchronized then run this statement for each database:

ALTER DATABASE databasename SET PARTNER FAILOVER-----------------to do manual failover

If you need to turn the asynchronous mode on again run this statement for each database:

ALTER DATABASE databasename SET SAFETY OFF-----------------------to make asynchronous mode

Demo:

ALTER DATABASE databasename SET SAFETY FULL-----------------to make synchronous mode

To know status as see below

ALTER DATABASE databasename SET PARTNER FAILOVER-----------to do manual failover @ principal

Now we need to run above command on Principal

After refreshed we can see it changed from Principal to Mirror

now we check the operating mode status on new principal server

ALTER DATABASE databasename SET SAFETY OFF-----------------------to make asynchronous mode

Now we Need to back to Asynchronous Mode to run above query on New principal server

now check the status it should be back to Asynchronous mode

That is it

Explanation on statements:

1)

ALTER DATABASE dbname SET SAFETY OFF-----------Make operating mode to Asynchronous

2)

ALTER DATABASE dbname SET SAFETY FULL-----------Make operating mode to Synchronous


3)

ALTER DATABASE dbname SET PARTNER FAILOVER-----------Make Manual Fail Over

4)

To break database Mirroring:

-------------Run it on Principal

ALTER DATABASE dbname SET PARTNER OFF---------to break DB mirroring

5)

To Presume Database Mirroring 

we need to run it on mirror first on below query

ALTER DATABASE TestDBMirror SET PARTNER ='TCP://USER-PC.LOCAL:5022'---mention principal info
then 

---------- we need to second run it on Principal

ALTER DATABASE TestDBMirror SET PARTNER ='TCP://USER-PC.LOCAL:5023'---mention mirror info

6) To set Witness on Database Mirroring run it on Principal

ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://DBSERVER3:7022' ---------------to setup Witness server


6) To drop/Remove Witness on Database Mirroring run it on Principal

ALTER DATABASE Adventure Works SET WITNESS OFF

Ref:

https://www.mssqltips.com/sql-server-tip-category/64/database-mirroring/

https://www.mssqltips.com/sqlservertip/2701/steps-to-apply-a-service-pack-or-patch-to-mirrored-sql-server-databases/


Facts:

1)We can not set Database mirroring between Enterprise to Standard Edition we could get following Error

Feature Name	Enterprise	Business Intelligence		Standard	Web	Express with Advanced Services	Express with Tools	Express
Server Core support1	Yes	Yes		Yes	Yes	Yes	Yes	Yes
Log Shipping	Yes	Yes		Yes	Yes
Database mirroring	Yes	Yes (Safety Full Only)		Yes (Safety Full Only) Synchronous only	Witness only	Witness only	Witness only	Witness only
AlwaysOn Failover Cluster Instances	Yes (Node support: Operating system maximum	Yes (Node support: 2)		Yes (Node support: 2)
Backup compression	Yes	Yes		Yes
Database snapshot	Yes
AlwaysOn Availability Groups	Yes
Connection Director	Yes
Online page and file restore	Yes
Online indexing	Yes
Online schema change	Yes
Fast recovery	Yes
Mirrored backups	Yes

2)

Principal ---Enterprise
Mirror-------Enterprise
Witness------Express or Developer or Standard Edition

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/add-a-database-mirroring-witness-using-windows-authentication-transact-sql

Can I use SQL 2008 R2 as a Witness Server for 2 instances of SQL 2012 Servers being mirrored?

No witness server has to be SQL 2012, you can use SQL Express 2012 though

All server participating in database mirroring should be running same version of SQL server.

Except during an upgrade from an earlier version of SQL Server, the server instances in a mirroring session must all be running the same version of SQL Server. For example, a SQL Server 2005 witness is supported when you are upgrading from a SQL Server 2005 mirroring configuration but cannot be added to an existing or new SQL Server 2008 or later mirroring configuration.

Database Mirroring will be deprecated in future release of SQL Server if possible use AlwasyOn Availability Group

Feature Name	Enterprise	Business Intelligence		Standard	Web	Express with Advanced Services	Express with Tools	Express
Server Core support1	Yes	Yes		Yes	Yes	Yes	Yes	Yes
Log Shipping	Yes	Yes		Yes	Yes
Database mirroring	Yes	Yes (Safety Full Only)		Yes (Safety Full Only) Synchronous only	Witness only	Witness only	Witness only	Witness only
AlwaysOn Failover Cluster Instances	Yes (Node support: Operating system maximum	Yes (Node support: 2)		Yes (Node support: 2)
Backup compression	Yes	Yes		Yes
Database snapshot	Yes
AlwaysOn Availability Groups	Yes
Connection Director	Yes
Online page and file restore	Yes
Online indexing	Yes
Online schema change	Yes
Fast recovery	Yes
Mirrored backups	Yes