MIRRORING

MIRRORING:

Q: Database Mirroring comes with which edition?
Ans: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400

Q: How to enable mirroring by Script ?

Ans: – Specify the partner from the mirror server

ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://A.corp.mycompany.com:5022';

– Specify the partner from the principal server

ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://B.corp.mycompany.com:5022';

Note: Replace the dbname before using the above script  Q: How to disable mirroring by script? Ans: ALTER DATABASE [AdventureWorks] SET PARTNER OFF

Note: Replace the dbname before using the above script

Q: How to do manual failover to Mirror when principle is working fine?
Ans: ALTER DATABASE <DB Name> SET PARTNER FAILOVER

Q: Why I’m getting the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring
Ans : You need to restore the Full backup from principal server using With NoRecovery option and also one transactional log backup from principal server using With NoRecovery option and then start configuring mirroring.

Q: Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?

Ans: Nope its not possible, both principal and mirror should have same edition

Q: Is it possible to take backup of mirrored database in mirror server?
Ans: No

Q: Is it possible to perform readonly operation at mirrored database in mirror server?
Ans: Yes, You can create database snapshot for the same

Q: How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec.

Ans: ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30

Q: What status of mirroring has if secondary is down?
Ans: If secondary is down principle or Mirror show status disconnected

Q: What status of mirroring has if principle is down?
Ans: If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring

Q: What status of mirroring has if mirroring is paused?

Ans: Is mirroring is set to paused from principle then then both principle & mirror in suspending 

Q: System Store Procedure to monitor Mirroring?

Ans: MSDB.SYS.SP_DBMMONITORRESULTS 

Q: What are different possible Mirroring Stats?

Ans:

SYNCHRONIZING

SYNCHRONIZED

SUSPENDED

PENDING FAILOVER

DISCONNECTED 

Q: Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint.

Ans: No 

Q: Can we configure mirroring between 32 and 64 bit machine?

Q: Can Log Shipping and mirroring configure together?

Q: Can we renamed mirrored database?

Q: Is drive letter required to be same on both the server?

Q: If drive letter is different on both the server. Then what is the steps to add a new datafile?

Q: Can we have different collation setting on both the database

Q: What is different role switching is available?

Q: What is hot standby and warm standby server?

Q: Please explain how log transmission and transaction commits work in high performance mode and in high safety mode?

Q: What is SQL query to set/change operating mode?

Q: Can we configure mirroring without setting up endpoints?

Q: Suppose your table size is of 300 GB and you have to rebuild it. Tell me Q: what is the precautionary step you will take so that your mirroring should not be effected?

Q: What is the step to move datafile in mirroring? Please step out every steps?

Q: What will happen if I delete any datafile on principle server? Will it affect mirroring session?

Q: Quorum types in Mirroring?

A Quorum is the relationship between the Witness,Principal and the Mirror.Depending on the mode of operation it is divided into 3.

Full Quorum —>This is when all 3 Witness,Principal and the Mirror can communicate with each other.Since witness is present automatic failover occurs.

Quorum —>This state exist if the Witness and either partner can communicate with it.

Partner-to-Partner —>When only the Principal and Mirror can communicate with each other.

Q: What are the requirements for setting up database mirroring? Additional  

Q: can you mix versions in mirroring (e.g. 2005 and 2000)?

A Requirements for database mirroring:

1.   Principal and mirror are running SQL Server 2005 or newer (Standard or Enterprise Edition).

2.   Principal and mirror have enough space for the database.

3.   For automatic failover, witness must be running.

4.   Principal database must be in FULL recovery model.

5.   Mirror database must be prepared prior to mirroring setup - one full backup and one transaction log backup need to be taken on principal and restored WITH NORECOVERY on mirror.

A to additional Q: yes, but principal has to be earlier then mirror, for example 2005 -> 2008. SQL Server 2000 does not support database mirroring at all.

Q: What is the endpoint in Mirroring?

Endpoint: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. It encapsulates a transport protocol and a port number.

An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.

Creation of an end point:-

Create endpoint <endpoint name> State=started/stopped/disabled

as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness) 

Q: What are the Operating Modes and explain them? a. High Availability (principle+mirror+witness) :- High-availability mode, runs synchronously. Requires a Witness Server instance. The Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.
principle is not available the witness and mirror will decide automatic failover .mirror becomes online.
b. High Protection (princeiple+mirror):- High-protection mode, runs synchronously. Always commit changes at both the Principal and Mirror. automatic failover is not possible.
c. High Performance:- High-performance mode, runs asynchronously and the transaction safety set to off. The Principal server does not wait for a response from the mirror server after sending the log buffer. The principal server running nice and fast, but could lose data on the mirror server.

Q: Mirroring configuration steps?

Configuring Mirroring – Steps

1.       Configuring security and communication between instances

a.       Configuring endpoint

b.       Creating logins for other servers service accounts

c.       Grant connect permission to this logins on endpoints

2.       Create mirror database

a.       Take full and T.Log backup from principle server and restore it in mirror server with

          NORECOVERY.

3.       Establish mirroring session using ALTER DATABASE command

Default port no is

5022 is default port number for mirroring in sql server

Q: Monitoring Mirroring?

Using Database Mirroring Monitor

We can monitor the following features

·         Unsent Log     (at principal)

·         Un restored Log (at mirror)

·         Transaction Rate

·         Commit Overhead (Transactions applied rate at mirror)  

Q: How to set Automatic failover timeout?

The default timeout for communication between the principal, mirror, and witness servers is 10 seconds.

Adjusting the automatic failover time for SQL Server Database Mirroring

ALTER DATABASE dbName SET PARTNER TIMEOUT 20 

Q: How to do manual failover?

To perform a manual failover

ALTER DATABASE AdventureWorks SET PARTNER FAILOVER; 

Q: Pausing or Removing Database Mirroring?

To pause the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER SUSPEND;

To resume the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER RESUME;

To remove the database mirroring session

ALTER DATABASE AdventureWorks SET PARTNER OFF; 

Q: What are the major new features introduced in Mirroring 2008 version?

1. Auto Page Repair.

Select * from sys.dm_db_mirroring_auto_page_repair

2. Transactions are sending to Mirror by compressing.

To view total bytes send from principal and total bytes received at mirror we can use (run in witness server)

Select * from sys.dm_db_mirroring_connections   

Q: Counters required to monitor Mirroring performance?

Monitoring database mirroring performance

To monitor the performance of database mirroring, SQL Server provides a System Monitor performance object (SQLServer:Database Mirroring) on each partner (principal and mirror). The Databases performance object provides some important information as well, such as throughput information (Transactions/sec counter). Following are the important counters to watch.

On the principal:

·  Log Bytes Sent/sec: Number of bytes of the log sent to the mirror per second.

·  Log Send Queue KB: Total kilobytes of the log that have not yet been sent to the mirror server.

· Transaction Delay: Delay (in milliseconds) in waiting for commit acknowledgement from the mirror. This counters reports the total delay for all the transactions in process at that time. To determine the average delay per transaction, divide this counter by the Transactions/sec counter. When running asynchronous mirroring this counter will always be 0.

· Transactions/sec: The transaction throughput of the database.This counter is in the Databases performance object.

·  Log Bytes Flushed/sec: The rate at which log records are written to the disk. This is the log generation rate of the application. It plays a very important role in determining database mirroring performance.This counter is in the Databases performance object.

·  Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks.

On the mirror:

· Redo Bytes/sec: Number of bytes of the transaction log applied on the mirror database per second.

· Redo Queue KB: Total kilobytes of hardened log that remain to be applied to the mirror database to roll it forward.

· Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks on the mirror. 

Q: Mirroring Requirements?

·         SQL Server 2005 with SP1 or SQL Server 2008

·         Database should be in FULL recovery model.

·         Service Broker should be enabled on the database.

·         Both the servers should have either Enterprise or standard editions.

·         Both the servers should have same edition.

·         Witness server can have any edition.

·         Database name should be same

·         Collation should be same

·         You can’t mirror more than 10 db in 32 bit server, because an instance can only have one end point which could be a bottle neck if there are lots of db’s in that instances.

·         You cant attach or detach

·         Mirroring Ports should be open and functionable

·         Service Account should be same  for sql and sql agent on Instance

·         Cross DB transactions and distributed transactions not permitted.

Q: How many Databases can configure Mirroring?

can't configure more than 10 DB's on 32 bit but we can on 64 Bit.  But not recommended. 

Q: Can we configure mirroring on different domain.

Yes. both domain's should be trust each other.

Q: What is cross db transaction and distributed transaction.  

Q: can we configure mirroring on difference SQL Service packs.

Yes(build no should be same. 

Q: Advantages of Mirroring?

• Hardware or software upgrades can be simplified.
• It increases the data protection(disaster recovery).
• Increases the database availability on syn mode.
• Cost of DB mirroring less compare to clustering.
• It is robust and efficient than log shipping and replication.
• It support the full text.
• Failover is fast compare to cluster.
• Mirror server can be used to host databases for other application
  Q: Disadvantages
• Does not support filestream.
• potential loss of data in async mode(high performance).
• Mirror server is not available for read-only purpose.
• It works at the database level, not at the server level.
• Multiple database fail-over.

• Q: Enhancements in 2008

• Automatic page repair(823,824,829 page errors).
• compression of mirroring data stream.
• Log send buffers - efficient use
• Write-ahead event enhanced in 2008
• Page read-ahead during the undo phase
  Q: What is the page error's in mirroring
  Automatic page repair(823,824,829 page errors).
   823 syclic redendancy failure
   824 logical errors
   825 restore pending
  Data page read error in principal then entry in suspect pages table in msdb db for the particler page. page is marked as restore pending making it in accessible to application queries. mirror state is suspended till recover the page.
  Can check suspect pages:
  SELECT * FROM msdb..suspect_pages;
  Q: What is the default of end points (port numbers) of principal, mirror and witness servers? How to find the Port numbers?
  The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024.
  To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints.
  Q: Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. 1400 Trace flag is used in mirroring.
  To set trace flag for Database mirroring:- Configuration Manager > Right click on server instance > Properties > Advanced tab > Startup parameters > -t1400 (add).
  Q: How to monitoring Mirroring?
  There are six methods are available for monitoring the Database Mirroring
  a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.
  To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.
  b) SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
  c) SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.
  d) Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
  e) Profiler:- Profiler many events are providing the status of the Database mirroring
  f) System Stored Procedures:-
  ⦁ sp_dbmmonitoraddmonitoring
  ⦁ sp_dbmmonitorchangemonitoring
  ⦁ sp_dbmmonitorhelpmonitoring
  ⦁ sp_dbmmonitordropmonitoring
  Q: What is Hardening?
  As quickly as possible, the log buffer is written to the transaction log on disk, a process called hardening.
  Q: What is Log buffer?
  A log buffer is a special location in memory (RAM). SQL Server stores the changes in the database’s log buffer.
  Q: How to Setup Fully Qualified Names for Database Mirroring?
  I. FQDN Error
  One or more of the server network addresses lacks a fully qualified domain name (FQDN).  Specify the FQDN for each server, and click Start Mirroring again.
  The syntax for a fully-qualified TCP address is:
  TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>
  Section 1.01
  Section 1.02 II. RECTIFYING FULLY QUALIFYED NAMES
  1) To View Endpoints:-SELECT * FROM sys.database_mirroring_endpoints;
  2) Remove existing all Endpoints from Principal, Mirror and Witness servers :- DROP ENDPOINT [ENDPOINT_NAME]
  3) Adding "local" as the primary DNS suffix as follows:-
  a) Right-click My Computer, and then click Properties. The System Properties dialog box will appear.
  b) Click the Computer Name tab.
  c) Click Change. The Computer Name Changes dialog box will appear.
  d) Click More. The DNS Suffix and NetBIOS Computer Name dialog box will appear.
  e) Enter the appropriate DNS suffix for the domain.
  f) Select the Change primary DNS suffix when domain membership changes check box.
  g) Click OK to save the changes, and then click OK to exit the Computer Name Changes dialog box.
  h) Click OK to close the System Properties dialog box, and then restart the computer for the change to take effect.
  4) Reconfigure the Database mirroring either GUI or T-SQL
  Q: Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498)
  Answer:
  This is a common error & everyone is know to this error. Database mirroring is officially supported from SQL Server 2005 SP1, hence in the RTM version database mirroring is disabled by default. You can use TRACE 1400 to enable in RTM version or else update your SQL Server with latest service pack.
  Adding Trace Flag to Startup parameter

• Goto RUN --> Type sqlservermanager.msc
• Right click on SQL Server(instancename) service and click on properties
• Click on Advanced tab
• In the startup parameters enter this ;-T1400 and click on OK
• Restart SQLservices and then try configuring db mirroring
  Or
• Update SQL Server to latest service pack.

What are the Mirroring modes

Table 13-11 summarizes the different database mirroring modes and the pros/cons for each of the modes.

Mode Name	Synchronous or Async	Witness present?	Pro/Con
High- Availability	Synchronous	Yes	Supports automatic failover and is the most hardened. If mirror disappears but principal and witness are connected, operations   continue. Mirror catches up when it comes back online.
High-Protection	Synchronous	No	automatic failover and if mirror unavailable, principal database goes offline.
High- Performance	Asynchronous	Yes	Fast performance but data not guaranteed on the other side and no automatic failover. Useful for low-bandwidth connections between mirror and principal since performance is best.

What are the Mirroring States?

PRINCIPAL	MIRROR	Discription
	RESTORING
	IN RECOVERY	WHILE CONFIGURING
Principal syncronized	Mirror syncronized/restoring	HIGH SAFETY WITH AUTOMATIC FAILOVER
Principal suspended	Mirror suspended/restoring	If Paused principal
Mirror syncronized/restoring	Principal syncronized	Principal Failover

Mirroring States:

SYNCHRONIZING: Indicates that the mirror database is trying to catch up with the principal database. This is typically seen when you just start database mirroring or in high-performance mode.

SUSPENDED: Indicates that the mirror database is not available. During this time the principal is referred to as running exposed, as it is processing transactions but not sending any transaction log records to the mirror.

PENDING_FAILOVER: Indicates the state that the principal goes through before transitioning to the mirror role.

DISCONNECTED: Indicates that the partners are unable to communicate with each other.

Operating Modes in Mirroring?

Database Mirroring Operating Modes
Operating Mode	Transaction safety	Transfer mechanism	Quorum required	Witness server	Failover Type
High Availability	FULL	Synchronous	Y	Y	Automatic or Manual
High Protection	FULL	Synchronous	Y	N	Manual only
High Performance	OFF	Asynchronous	N	N/A	Forced only

Mirror Monitoring

The following query returns the descriptions for basic database mirroring session information about either the principal or the mirror.

SELECT
      DB_NAME(database_id) AS 'DatabaseName'

    , mirroring_role_desc

    , mirroring_safety_level_desc

    , mirroring_state_desc

    , mirroring_safety_sequence

    , mirroring_role_sequence

    , mirroring_partner_instance

    , mirroring_witness_name

    , mirroring_witness_state_desc

    , mirroring_failover_lsn

FROM sys.database_mirroring

WHERE mirroring_guid IS NOT NULL;

The following is an analogous query returns relevant descriptive session information about the witness server that you run on the witness.

SELECT

      Database_name

    , safety_level_desc

    , safety_sequence_number

    , role_sequence_number

    , is_suspended

    , is_suspended_sequence_number

    , principal_server_name

    , mirror_server_name

FROM sys.database_mirroring_witnesses;