Failover consederations on Database Mirroring

These topics will give you the brief idea of what happens behind the scenes during failover occurs in Database mirroring. This topic assumes that the user is familiar with the database mirroring concepts.

Failover Considerations

Depending upon the Transactions safety option with witness server, you can have Automatic failover or manual failover or both. Below is the short reference of the same:-

Transaction safety level	Witness	Operating mode	Supported failover mode
FULL	Yes	High safety with automatic failover (synchronous)	Automatic or Manual
FULL	No	High safety (synchronous)	Manual
OFF	N/A	High performance (asynchronous)	Forced Service (with possible data loss)

When failover occurs several events occurs in the background in sequence:-

1) Failover occurs: - Failover happens as principal database become unavailable. There are below possible reasons for the failover:-

a) Power failure

b) Network failure

c) Storage Failure

d) Hardware failure

2) Failure detection: - Failure has been detected by witness & mirror server. The default timeout for the communications between Principal, Mirror & witness database is around 10 seconds. If witness did not get the response from principal within default timeout, it is considered as down. Anyhow there is no need to change the default timeout (i.e. 10 sec), but it needs to be reconfigured very cautiously, otherwise may lead to false failovers.

3) Redo Phase: - As database would be on restoring mode, so all the transactions from the redo queue needs to be applied on the mirror server to completely recover it.

4) Decision making: - Now mirror will coordinate with witness and decide that database nwill now failver to mirror server & it’s usually take 1 sec to confirm. If principal server came back before the redo phase then there would be no need for failover.

5) Mirror owns the role of Principal: - Mirror server will own the role of principal server & makes its database online. Clients are now connecting to the new principal server.

6) Undo Phase: - If there are any uncommitted transactions in the T-log, they are rolled back.

So in general, the manual failover takes place from the failover occurs till the time mirror server assumes the role of principal and server its database to the clients. But redo phase plays the major role in this scenario. How much time redo phase will take can be calculated from the system monitors Redo Queue (in KB) and redo bytes/sec. So, if you divide Redo Queue (in KB) from Redo bytes/sec, you will get the estimate time it will take to apply the logs in the redo queue of mirror database.

The other option to monitor the same is through with SQL trace on both Principal  & Mirror server by monitoring the Database Mirroring State change event. The columns of interest are Start time & Text data.

Hope, this article helps you understanding the events fired in the background when failover occurs in database mirroring.

Database Mirroring

Introduction

This blog covers the High Availability Solutions provided by SQL Server i.e. Database Mirroring.

Database mirroring is a solution in SQL Server by which we can have the redundant copy of the database (Principal server) available on another server (Mirror Server) & incase of any disaster we can can bring the database online from another server

Let’s Start with Database Mirroring now:-

Part1

Mirroring Overview

Database Mirroring is one of the best options provided by SQL Server. It is implemented on per database wise. Database only configured with Full Recovery models can only be used in mirroring.

Hence Simple & Full Logged recovery models are not supported in database mirroring...

Also, database mirroring cannot be implemented on system databases.

Database mirroring contains the two copy of the database that generally resides on two different instances of sql server. All the client requests are handled by one instance, which is called Principal server. The other instance acts as standby server. When the mirroring session is fully 'SYNCHRNISED' standby server acts as HOT standby server with no loss of data & when session is not SYNCHRONISED the stand by server acts as warm server with possible loss of data.

 Benefits

Data Protection: - Database mirroring provides complete protection to the data. Complete redundancy of data is maintained either the operating mode is High Safety or High performance.

Database Availability: - In the event of any disaster, Database will be available as soon as possible. When the database is running in High safety mode with Automatic Failover, automatic failover will occur & Standby server will acts as Principal server without loss of data & start serving the requests to clients. In other mode we need to force the service to the standby server to make it principal server with possible loss of data.

Productions Database availability during Upgrades: To reduce the downtime of mirrored database, we can upgrade the instance of sql server which is participating in database mirroring, sequentialy.

Database Mirroring, behind the scenes

 The Principal and Mirror servers act as partners in database mirroring session. There are two roles i.e. Principal role & Mirror role. The partner who owns the Principal role is known as Principal server & its copy of the database is known as Principal database. All the client requests will be full filled by Principal databases. The partner who owns the mirror role will act as Mirror server & its copy of database is known as mirror database.

Database mirroring involved redoing of every insertion, updation & deletion occurred at Principal database onto the mirror database ASAP. This is done by sending the Active transaction log to the mirror server & it applies the changes in the mirror database in sequence as soon as possible.

Its works in two operations:

1) Synchronous: - Database mirroring acts in this mode send the active transaction log to the mirror database & waits for its confirmation. This guarantees that both the copies of database are synch with each other at any given point of time. But we need to compromise with the performance in this mode.

 2) Asynchronous: - Database mirroring acts in this mode doesn’t wait for the confirmation from the mirror server. This maximizes the performance of the database.

There are two modes in mirroring. One is High Safety mode which support Synchronous mode. Once mirroring starts with this mode, mirror server tries to synch the mirror database with the Principal database as soon as possible. Once both the databases are synched transactions are committed on the both the server at same time. If any request comes to principal server it just send the transaction to the mirror server & waits till it gets the confirmation from the mirror server. Once it receives the confirmation its sends the confirmation to the client about the change.

The second operating mode is High performance which supports Asynchronous mode. The mirror server try to write the logs send by the Principal server as soon as possible. Generally the gap between the principal & mirror server is small but yes it can be large if the principal server is under too much work pressure.

In this mode Principal server does not wait for the acknowledgement main from the mirror server. As soon as principal send the log information to mirror server, it send the confirmation to the client at the same time. As Principal server doesn’t not wait till the log gets written to the mirror server database, there is a risk of potential data loss in case on any disaster happened to the principal server.

Although below is the default configuration of Database mirroring with principal & mirror server:-

Once our Mirroring is configured with High Safety with Automatic failover, we have another server called witness. Although this servers doesn’t not process any requests to the clients, but it keeps an eye on the Principal server that it’s up & running. It will automatically initiate the failover only if:-

a) Mirror server is connected with witness server after both are disconnected from principal server.

Below is the configuration of Database Mirroring with witness server

This topic covers the basic information about the database mirroring in SQL Server. More Parts on the same topic will be published soon.

.

How to Upgrade SQL Server 2000 Instance to SQL Server 2005

Introduction

The purpose of this tutorial is to lay out the general guidelines for planning a SQL Server 2005 upgrade. As the new version of SQL Server is coming, all the companies want to upgrade the older version of SQL Server to the newer version. Here the DBA role has again come into the picture for doing the same.

Why do we require to Upgrade

The newer version contains additional features & improvements in the newer product. Below are the few reasons for upgrading SQL server:-

1) High Availability solutions like Database Mirroring

2) Managing VLDB (Very large Databases) easily & efficiently

3) Stronger Security

4) BIDS (Business Intelligence Development Studio)

For more information, please refer the below link @Microsoft, "Why to upgrade to SQL Server 2005"

http://www.microsoft.com/sql/techinfo/whitepapers/why-upgrade.mspx

SQL Server Upgrade menthods

In SQL server we are provided with two methods for SQL upgradtion:-

1) In-Place Upgrade

2) Side-by-side Upgrade

In this blog I will cover the In-Place upgrade method. Second option will be covered in the next blog very soon.

In-Place Upgrade

This option is the easiest way of upgrading the SQL Server to the newer version. If everything goes well its good, otherwise its very time consuming approach to roll back the changes back to the older vrsion.This is the automatic process in which you replace the older version of SQL server instance to the newer version, called, In-Place upgrade.

Below are the few characteristics of In-Place upgrade

Number of instances	One only
Number of servers involved	One
Data transfer	Automatic
Server instance configuration	Automatic
Helping utility	SQL Server Setup

The main drawback of using this option is that you cannot upgrade the single database to the newer version. In that case your need to adopt another option called, Side-by-Side upgrade.

Secondly, remember you cannot upgrade “CROSS-BIT” upgrade like, upgrading SQL server 32 bit instance  to SQL server 64 bit instance or vice versa. This can be only possible with Side-by-Side upgrade method.

Below diagram shows how the SQL Instance is replaced with the newer version of SQL Server.

 

Advantages of In-place Upgrade:-

1)    Its speedy

2)    It’s easy to perform

3)    It’s a automated process

4)    Less  downtime  required

5)    No additional hardware required

Disadvantages of In-Place Upgrade

1)    Having to upgrade the whole instance

2)    Not able to upgrade the single database

3)    Disk  space issues during upgrade process

4)    Check the instance for Backward compatibility

5)    Rollback  is complex in case of failure

Pre-requisite for In-Place upgrade

1)    Download /Install .Net Framework 2.0 or higher

2)    Download/Install Windows Installer 3.1 Service.

3)    A remote Registry service is required during up gradation. So, try to start this service & see if it started successfully. If it doesn’t check its dependencies & see if RPC is running or not. Without this you are not able to install the Service Packs on the upgraded SQL server Instance because it will show you the error that upgradtion has not completed successfully.

4)    Backup the System  databases

5)    Run DBCC Checkdb to ensure database are clean

How to do in place upgrade:-

Run the setup from the SQL 2005 & pass on all the dialog box until the below one

In the above window, you need to select the instance to upgrade; in our case we will take the default instance. Now click on the Installed Instances button

Here you will have to check the Check box to upgrade the installed instance on the SQL server. For coming windows after the above window, select the default values.

Post Upgradtion Tasks

1)    Change the Compatibility Modal of Databases to 90

2)    Executed DBCC UPDATEUSAGE on destination databases to update usage counters to ensure that correct values exists for Table and index row counts.

3)    Executed DBCC CHECKDB WITH DATA_PURITY on destination databases to check the databases for column values that are not valid or are out of range.

4)    Take the fresh Full backups

Hope this topic helps the DAB to perform the in place up gradation smoothly & efficiently.

Recovery Modals in SQL Server

Being a SQL Server DBA, one should be able to understand how many recovery modals do we have in SQL Server.While determining the Backup starategies for you databases , we should give a deep dive into the recovery modals of the databases as they define the types of backups that we can perform with the databases.

There are three types of Recovery Models in SQL Server:-

1) Full Recovery Model
2) Bulk-logged Recovery Model
3) Simple Recovery Model

Full Recovery Model:-

In this Model, we can take FULL, Differential & Log backups . Every transaction in is logged in the databases log file.

Bulk-logged Recovery Model

In this Model, we can take FULL, Differential & Log backups. The main difference between Full & Bulk logged is that all the Bulk Load operaions are minimally logged into the log file of the databases. In other words , we can say that only the allocation & deallocation of pages that aee affected with the Bulk operations are logged into the log file.

Simple Recovery Model

Under this recovery model, we are only capable of taking Full & Differential backups of the databases. Log backup is not possible in this recovery model.


How to change the Recovery Models in SQL Server

1) Go to the database properties
2) Go to the option tab

3) Select the recovery modal from the drop down box

Sanjee-SQLMaster

Dear All,

very good maorning to all of you.