Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 9 June 2017

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