Disaster Recovery / High Availability Solutions in SQL Server

What is a disaster recovery in SQL Server?

A SQL Server disaster is an event that causes data loss or any serious SQL Server disruption. Causes of a disaster can be numerous: power failure, hardware failure, virus attack, natural disaster, human error, etc. Some SQL Server disasters cannot be prevented. That’s why a good disaster recovery plan is important

A SQL Server disaster recovery plan (DRP) is a process to have SQL Server up and running, and to overcome data loss after a disaster. A disaster recovery plan must be planned and documented in order to prevent catastrophic data loss and incidents

High-Availability is the term that shows the percentage of time a system needs to be available. In some environments, it goes as high as 99.999% or just 5.26 minutes downtime per year.

A good disaster recovery plan must take into account numerous factors: sensitivity of data, data loss tolerance, required availability, etc. The plan can be based on few a solutions:

• Failover clustering
• Database mirroring
• Replication
• Log shipping
• Backup and restore 

Each solution has its own advantages and cost of implementing. Based on the needs, a disaster recovery plan should include on one or more available solutions.

SQL Server failover clusters:

SQL Server failover clusters are made of group of servers that run cluster enabled applications in a special way to minimize downtime. A failover is a process that happens if one node crashes, or becomes unavailable and the other one takes over and restarts the application automatically without human intervention.

The Backup and restore technique:

The Backup and restore technique should be used as basic option for assurance. There are two major concepts involved: backing up SQL Server data and restoring SQL Server data. Backed up data is moved to a neutral off-site location and restore is tested to assure data integrity. There are different types of backups available in SQL Server: a full backup, differential backup, transaction log backup, and partial backup. The backup strategy defines the backup type and frequency, how backups will be tested, and where and how backup media will be stored. The restore strategy defines who is responsible for performing restores and how restores should be performed to meet availability and data loss goals.

Log shipping:

Log shipping is based on automated sending of transaction log backups from a primary SQL Server instance to one or more secondary SQL Server instances. The primary SQL Server instance is a production server, while the secondary SQL Server instance is a warm standby copy. There can be a third SQL Server instance which acts as a monitoring server. The log shipping process consists of three main operations: creating a transaction log backup on the primary SQL Server, copying the transaction log backup to one or more secondary servers, and restoring the transaction log backup on the secondary server.

Replication:

Replication can be used as a technology for coping and distributing data from one SQL Server database to another. Consistency is achieved by synchronizing. Replication of a SQL Server database can result in benefits like: load balancing, redundancy, and offline processing. Load balancing allows spreading data to a number of SQL Servers and distributing the query load among those SQL Servers. A replication consists of two components: 

• Publishers - databases that provide data. Any replication may have one or more publishers
• Subscribers - databases that receive data from publishers via replication. Data in subscribers is updated whenever data the publisher is modified

Microsoft SQL Server supports three types of database replication:

• Merge replication: allows the publisher and subscriber to independently make changes to the database. The merge replication agent checks for changes on both sets of data and modifies each database accordingly. If changes conflict with each other, it uses a predefined conflict resolution algorithm to determine the appropriate data

• Snapshot replication: the publisher makes a snapshot of the entire database and makes it available for all subscribers

• Transactional replication: uses replication agents which monitor changes on the publisher and transmit these changes to the subscribers 

Database mirroring:

Database mirroring is a solution for increasing availability of a SQL Server database. It maintains two exact copies of a single database. These copies must be on different SQL Server instances. Two databases form a relationship known as adatabase mirroring session. One instance acts as the principal server, while the other is in the standby mode and acts as the mirror server. Two SQL Server instances that act in mirroring environment are known as partners, the principal server is sending the active portion of a transaction log to the mirror server where all transactions are redone

There can be two types of mirror servers: hot and warm. A hot mirror server has synchronized sessions with quick failover time without data loss. A warm mirror server doesn’t have synchronized sessions and there is a possibility of data loss

This solution will be removed in future versions of SQL Server .

Bangalore city Gallery

UB City:

UB City is the biggest commercial property project in Bangalore, India. Pioneered by the chairman of UB Group, Dr.Vijay Mallya, in Joint Venture with Prestige Group, it is built on 13 acres of land and hosts 1,000,000 sq ft of high-end commercial, retail and service apartment space. UB City has four towers namely, UB Tower, Comet, Canberra and Concorde.

Chinnaswamy Stadium:

The M. Chinnaswamy Stadium, located in Bangalore, Karnataka, is one of the cricket stadiums of India. Flanked by the picturesque Cubbon Park, Queen's Road, Cubbon and uptown MG Road, this four decade old stadium is situated in the heart of the city of Bangalore. Formerly known as the Karnataka State Cricket Association stadium, the ground was later rechristened in tribute to Mr. M. Chinnaswamy.

Nehru Planetarium:

Nehru Planetarium is the name given to five planetariums in India, named after India's first Prime Minister, Jawaharlal Nehru. These are located in Mumbai, New Delhi, Pune and Bangalore, plus there is a Jawahar Planetarium in Allahabad. Nehru Planetarium in Pune was the first one in Asia, established in 1954.

Vidhana Soudha:

Vidhana Soudha is the largest legislature-cum-office building in the country. No other state capital boasts of a comparable structure. A grand edifice of the Dravidian style of design and architecture, it has been built almost entirely with Bangalore granite.

 

 

Lalbagh Botanical Garden

This enchanting garden is set on 240 acres with a rare collection of tropical and sub-tropical trees and plants, and a majestic glasshouse built on the lines of London's Crystal Palace. The garden was made in the 17th century -- started by Hyder Ali, and later completed by his son Tipu Sultan. It derives its name "Lal Bagh" from a collection of red roses that bloom throughout the year there. The glasshouse, which was constructed in the year 1889 to commemorate the visit of the Prince of Wales, is the jewel of this garden.

Lalbagh takes on a festive look during India's Independence Day and Republic Day, with a captivating show of over 200 varieties of flowers. The show also features an exhibition of hybrid vegetables.

Attara Kacheri (High Court) and Surroundings

This two-storied building, built in 1867 under the reign of Tipu Sultan, is painted in red with Corinthian columns in Gothic style architecture. It's located at the entrance to Cubbon Park, and houses the High Court and many lower courts.

Close to the Court is the State Central Library building, which has the "Pompeian red" color and architectural style of stone and fluted pillars, with walls finished in lime plaster. Nearby, the Government Museum houses many exotic collection of coins, sculptures, inscriptions, and old paintings. Adjacent to the Museum is Venkatappa Art Gallery, displaying famous paintings, plaster of Paris works and wooden sculptures of the well known artist Venkatappa, and of other contemporary artists.

Cubbon Park

Located in the heart of city, set on a 300 acre area, Cubbon Park is a great place to laze around in the lush green paradise. It has a treasure of different species of exotic plants. The park was named after the erstwhile Commissioner of Bangalore, Sir Mark Cubbon. Many ornamental and flowering trees, both exotic and indigenous, can be found in the park. It's a popular place for walkers, joggers, and nature lovers. Bal Bhavan in the park is a great place for the kids to enjoy. An aquarium is also located in Cubbon Park, with many varieties of exotic and ornamental fish.

Bangalore Palace

Built in 1887 by Chamaraja Wodeyar, Bangalore Palace is inspired by England's Windsor Castle. This beautiful palace has Tudor style architecture, with fortified towers, arches, green lawns, and elegant woodcarvings in its interiors.

Tipu Sultan's Palace and Fort

Tipu Sultan's Palace and Fort was originally built by Chikkadeva Raya in mud. Later, Hyder Ali started reconstruction in Indo-Islamic architecture. This was completed by his son, Tipu Sultan, in 1791. The Hindu temple seen in the courtyard of the fort is the proof of Tipu Sultan's religious tolerance.

Ulsoor Lake

The beautiful Ulsoor Lake is spread over an area of 125 acres. It was constructed by Kempegowda II. Boating and swimming pool are the great attractions of the lake.