| Topic : Creating and Implementing a Disaster Recovery Plan |
|
|
|
||
|
Activity:
0 comments
181 views
last activity : 07 06 2010 20:18:04 +0000
|
||
|
|
Potential database system failures and Recovery Plans
The first set of failures would cause a SQL Server database to become unavailable to an application. I present them in order of the most catastrophic first.
One reason a database could become unavailable is through the loss of your data center. Whether a major offsite installation or a closet in a small business, a natural or man-made disaster could take the entire system out.
Chances are the database server is on a single network switch. If that switch goes down, it will be impossible to access the database. Some data centers provide redundant network switches to deal with this scenario.
Databases might become unavailable because the entire database server, meaning both its CPU and disk subsystem, is lost. In this scenario, the database with its configuration, data, and relationship to other database servers would all have to be re-created. Typical events that might cause this include power loss, fire, or other physical damage to the server.
The database becomes unavailable because the CPU of the system (motherboard, RAM) is lost even though the disk subsystem remains intact. Because the disk subsystem is intact, the SQL Server data files are usable.
The database might become unavailable because of a failure of the local disk subsystem. The database would have to be restored onto a fresh set of disks or another server. Typical events include disk drive failures, multiple disk failures on RAID, and controller failures. Generally, RAID 5 and 0+1 systems can handle up to two disk failures, but on RAID 0+1, if two disks that are mirrored fail, the entire volume must be rebuilt.
If you lose an MSCS failover cluster's external disk subsystem, you'll have to reconstruct the database from backups stored somewhere other than on the server or promote a secondary log shipping or replication subscriber with current data.
The database might become unavailable because it loses external Storage Area Network (SAN) storage. Typical events include multiple disk drive failures, SAN component failures, or perhaps power loss to the SAN.
The database becomes unavailable because network connectivity to the server is lost. Typically, this would be due to a malfunctioning network card. Database servers often contain more than one network switch to deal with this possibility.
If the server hardware is functional but the operating system needs to be repaired or reinstalled, SQL Server will have to be reinstalled as well, and the database will clearly be unavailable during that period. On a failover cluster, failover to the secondary node must take place to keep the database available.
The SQL Server might fail, making the database unavailable. In this case, the server hardware and operating system are functional, but SQL Server must be reinstalled. Typically, this would be due to a bad SQL Server installation, odd access violations, or memory leaks. Bear in mind that service pack upgrades also make SQL Server unavailable for the duration of the upgrade.
The SQL Server might be intact, but the database might become suspect or otherwise unavailable. Typically, this would be due to some kind of data corruption, but a malicious user or developer might have actually dropped the database. This is as serious as losing the server, and the database would have to be re-created from backup media or replaced by a warm standby server.
In the remaining set of failures, the database is available, but for some reason unusable.
In this scenario, the SQL Server database can be accessed, but the Tables aren't there or are unavailable for some reason. Again, the database might have to be re-created from backup media, or replaced by a warm standby server.
Suppose the Tables are present and can be queried, but data has been lost. Typically, this would be caused by accidental deletes or truncations of data, or bugs in the client software that mangle the data.
The Tables have data, but it's no longer current because a data feed of some kind has failed. This is still a failure, even though it might be outside of SQL Server as such. What's challenging here is to set up a mechanism for detecting this kind of failure.
In this scenario, data is in Tables but replication is no longer updating a subscriber. If your server is the subscriber, it might be that the distribution server has failed. If your server is the publisher, perhaps replication has failed for some reason, and the distribution server is malfunctioning. In either case, you need to plan for methods of detecting the outage (say, from alerts) and procedures for restoring replication.
The database might be available and have good data, but poor system performance might make the database unusable. For example, indexes are lost or corrupt, statistics are out of data, or a lot of blocking and deadlocking occur. Even though no data is lost, the problems must be addressed immediately.
DBAs and testers don't often consider this issue. What if the database can be queried successfully but the data is of poor quality due to inconsistency, age, or incompleteness? These problems could result from client software bugs, poor database design, or various user errors. In any case, a method for sanity-checking the data can be helpful in detecting these kinds of errors.
The first set of failures would cause a SQL Server database to become unavailable to an application. I present them in order of the most catastrophic first.
One reason a database could become unavailable is through the loss of your data center. Whether a major offsite installation or a closet in a small business, a natural or man-made disaster could take the entire system out.
Chances are the database server is on a single network switch. If that switch goes down, it will be impossible to access the database. Some data centers provide redundant network switches to deal with this scenario.
Databases might become unavailable because the entire database server, meaning both its CPU and disk subsystem, is lost. In this scenario, the database with its configuration, data, and relationship to other database servers would all have to be re-created. Typical events that might cause this include power loss, fire, or other physical damage to the server.
The database becomes unavailable because the CPU of the system (motherboard, RAM) is lost even though the disk subsystem remains intact. Because the disk subsystem is intact, the SQL Server data files are usable.
The database might become unavailable because of a failure of the local disk subsystem. The database would have to be restored onto a fresh set of disks or another server. Typical events include disk drive failures, multiple disk failures on RAID, and controller failures. Generally, RAID 5 and 0+1 systems can handle up to two disk failures, but on RAID 0+1, if two disks that are mirrored fail, the entire volume must be rebuilt.
If you lose an MSCS failover cluster's external disk subsystem, you'll have to reconstruct the database from backups stored somewhere other than on the server or promote a secondary log shipping or replication subscriber with current data.
The database might become unavailable because it loses external Storage Area Network (SAN) storage. Typical events include multiple disk drive failures, SAN component failures, or perhaps power loss to the SAN.
The database becomes unavailable because network connectivity to the server is lost. Typically, this would be due to a malfunctioning network card. Database servers often contain more than one network switch to deal with this possibility.
If the server hardware is functional but the operating system needs to be repaired or reinstalled, SQL Server will have to be reinstalled as well, and the database will clearly be unavailable during that period. On a failover cluster, failover to the secondary node must take place to keep the database available.
The SQL Server might fail, making the database unavailable. In this case, the server hardware and operating system are functional, but SQL Server must be reinstalled. Typically, this would be due to a bad SQL Server installation, odd access violations, or memory leaks. Bear in mind that service pack upgrades also make SQL Server unavailable for the duration of the upgrade.
The SQL Server might be intact, but the database might become suspect or otherwise unavailable. Typically, this would be due to some kind of data corruption, but a malicious user or developer might have actually dropped the database. This is as serious as losing the server, and the database would have to be re-created from backup media or replaced by a warm standby server.
In the remaining set of failures, the database is available, but for some reason unusable.
In this scenario, the SQL Server database can be accessed, but the Tables aren't there or are unavailable for some reason. Again, the database might have to be re-created from backup media, or replaced by a warm standby server.
Suppose the Tables are present and can be queried, but data has been lost. Typically, this would be caused by accidental deletes or truncations of data, or bugs in the client software that mangle the data.
The Tables have data, but it's no longer current because a data feed of some kind has failed. This is still a failure, even though it might be outside of SQL Server as such. What's challenging here is to set up a mechanism for detecting this kind of failure.
In this scenario, data is in Tables but replication is no longer updating a subscriber. If your server is the subscriber, it might be that the distribution server has failed. If your server is the publisher, perhaps replication has failed for some reason, and the distribution server is malfunctioning. In either case, you need to plan for methods of detecting the outage (say, from alerts) and procedures for restoring replication.
The database might be available and have good data, but poor system performance might make the database unusable. For example, indexes are lost or corrupt, statistics are out of data, or a lot of blocking and deadlocking occur. Even though no data is lost, the problems must be addressed immediately.
DBAs and testers don't often consider this issue. What if the database can be queried successfully but the data is of poor quality due to inconsistency, age, or incompleteness? These problems could result from client software bugs, poor database design, or various user errors. In any case, a method for sanity-checking the data can be helpful in detecting these kinds of errors.
0 comments on "Potential database system failures and Recovery Plans"
Sort by:
Most Recent
Top Rated
Found the article
"Potential database system failures and Recovery Plans"
interesting ?
Share with your connections and communities
Leading Recruitment firm
ETL.Ab Initio jobs in Pune
Cognos jobs in Pune
Cognos jobs in Kolkata
Viewers also viewed
|
|
|
Dynamic Management Views The dynamic management views (DMV’s) in SQL Server 2005 are designed... |
|
|
11 referals
6 comments,
1828 views
|
|
|
Business schools use the medium of case studies as a learning instrument. But, the case studies... |
|
|
120 referals
5 comments,
45 views
|
|
|
Though media vs Self |
|
|
157 referals
105 arguments,
3005 views
|
|
|
|
|
|
|
Recent Knowledge (49)
|
|
Isn't it amazing, that Indian Government has miserably failed to handle a major issue like this,... |
|
|
0 referals
2 comments,
153 views
|
|
|
We know that Ecommerce is the most happening trend of the day. After the emergence of ecommrce,... |
|
|
109 referals
1 comments,
53 views
|
|
|
Yes they are going to be here.. and the first destination is Bangalore... Google cars and trikes... |
|
|
1339 referals
12 comments,
443 views
|
|
|
|
|
|
|
Sponsored Jobs
More From Author
Anyone statement which cause some uncomfort to common man or mass of the people, nothing wrong in saying Sorry. She is good atleast said sorry. Raj Thakre, Rahul Gandhi, Digvijay Singh and many other politicians never say sorry for their mistakes. |
Thanks for sharing the article. Good one |
@Munshi: Please don't blame RSS for everything. The sounth India treats all north Indians as aliens. Tamilnadu people know Hindi language but feel shame to speak. They treat any others as outsiders... This all haterated are growing for the benifits of... |