Web Hosting Java Blog

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 125 Use of Recovery Models Some of the database properties that were available in previous releases of SQL Server have been combined to form what is referred to in SQL Server 2000 as recovery models. Setting the appropriate model can enable most if not all data activity to be recovered in the event of system failures. Three models are supported: Simple, Bulk-Logged, and Full. The Simple recovery model ensures higher performance during bulk copy operations and maintains a smaller database log. However, the model does not support transaction log backups and, therefore, there will be data loss in the event of a failure because the database can be restored only to the point of the last full or differential database backup. Bulk-Logged recovery also allows for high-performance bulk procedures that use minimal log space. Some data might be lost, but because you can perform log backups, usually the only loss will be bulk operations since the last backup. If recoverability to a specific point in time with as little data loss as possible is the goal, then the Full recovery model should be set. The Full recovery model makes the heaviest use of the database logs. Backup and Disaster Recovery Usually backup and disaster recovery falls into the realm of the database and network administrators. In a total systems design strategy, a complete plan of recovery activity that includes a variety of scheduled backups and other tasks is documented and tested. This disaster recovery plan will be updated as needed, because test recovery and practicing the plan is sure to point out anything that might be otherwise missed. Though not a specific detail of implementation, the topic of recoverability would not be complete without at least the mention of a system-wide plan. Consider a regular exercise of simulating failures to test the plan. Standby Servers and Log Shipping A warm backup server or standby server is a lower-cost implementation that is often selected as an alternative to replication or clustering. The premise is to back up the production server on a regular basis, restoring it to a second machine that can be put into production in the event of failure in the first computer. A standby server NOTE User Accounts for Services Separate user accounts can be identified for each of the SQL Server services. Conversely, the same account can be used for all services and several servers. As a standard implementation, it is usually best to use the same account. You might want to use separate accounts for each server, particularly if you want each server to send and receive email as a separate identity.

For reliable and cheap web hosting services please check javaweb hosting website.

124 Part I EXAM PREPARATION Interval option in the Enterprise Manager by right-clicking the server, selecting Properties from the pop-up menu, and navigating to the Database Settings tab. FIGURE 3.1 General properties for operating system services. FIGURE 3.2 Recovery properties for operating system services.

For high quality java hosting services please check tomcat web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 123 In an attempt to achieve as little downtime as possible, it is essential to consider a strategy that involves multiple servers and redundant other hardware, as well as other issues on each machine. Data redundancy, adequate backups, and some form of disaster recovery plan must all be a part of a complete solution. Most of the topics surrounding server clustering fall out of the scope of this blog, although the partitioned views will be discussed at length within the section Multiple Server Implementations, later in this chapter. Other multi-server functionality, such as data replication, is addressed in Chapter 11, Implementing and Understanding Replication Methodologies. Though most of the topics related to recoverability fall into the realm of administration, you need to give some consideration to these processes when you put together a physical design. The following three sections explain these considerations as they pertain to database design. System and Data Recovery Recovering from outages and minimizing data loss in the event of hardware failures involves prior planning, adequate backups, and the setting of appropriate database and server options. On the server, the recovery interval and service startup options can be adjusted to lessen the time it takes for a SQL Server to be online and operational after a power failure or other serious service interruption. In each database, the recovery model can be set to determine the log usage and amount of lost data activity upon failure. Backups are one of the most important aspects of recovery. Backups must be maintained in a diligent and thorough manner. Finally, a plan of action that is regularly practiced must be part of a workable solution. Operating System Service Properties In SQL Server 2000, two aspects of the server allow for a successful server database restart in the event of failure. The operating system s services can be configured to automatically start upon computer startup and can also be set up to respond to service interruptions. To set service properties, you must locate the MSSQLSERVER service. This service can be found in your administrative tools, Services for Windows 2000, or Control Panel Services for NT. For the configuration options as displayed using the Windows 2000 services properties, see Figures 3.1 and 3.2. The database recovery interval can be set for the number of minutes each database takes to start up after an outage or controlled server startup. You can find the Recovery

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

122 Part I EXAM PREPARATION more costly form of fault tolerance than parity sets, losing a full 50% of available space to data redundancy. An alternative form of mirroring, duplexing, involves not only the duplication of hard drives but also redundant drive controllers. In using duplexing, you achieve fault tolerance over the loss of the controller as well as hard drive failure. To achieve up-to-the minute recovery in any failure, you might want to place your log files on a mirror or duplexed volume. RAID 5 (parity set) provides the best read performance while still giving the recoverability through data redundancy. In a parity set, the data is written across all the available drives in segments referred to as stripes. In each stripe, all but one drive will contain data, with the remaining drive containing the parity check information. Each time data is written, a checksum is calculated and written to the parity segment. If a failure causes the loss of a disk drive, the parity segment can be used to enable the stripe set to be regenerated. RAID 5 is usually referred to as a poor man s mirror because the more drives that are included in the set, the more cost-effective this solution. For example, if three drives are used, a third of the available space is lost to redundancy. If ten drives are used there is only a 10% loss of usable space. IN THE FIELD RAID: SOFTWARE VERSUS HARDWARE Even though software implementations of RAID must be known to pass certification exams and will be found in production systems, they are not nearly regarded as reliable as hardware RAID. For any high-volume, mission-critical application, it is therefore preferred to set up data redundancy mechanisms at the hardware level. Recoverability in the Event of Failure Two primary concerns in most data environments are data recover- ability in the event of the inevitable failures and considerations for minimal downtime. In the industry, one of the optimum ratings to strive for is the elusive five nines (99.999). This rating means that over any given period of time (generally accepted standard of 365 days minimum), the server remained online and servicing the end user 99.999 percent of the time. In other words, the total downtime for an entire year is a little over 5 minutes.

If you looking for unlimited one inclusive web hosting plan please check unlimited web hosting website.

122 Part I EXAM PREPARATION more costly form of fault tolerance than parity sets, losing a full 50% of available space to data redundancy. An alternative form of mirroring, duplexing, involves not only the duplication of hard drives but also redundant drive controllers. In using duplexing, you achieve fault tolerance over the loss of the controller as well as hard drive failure. To achieve up-to-the minute recovery in any failure, you might want to place your log files on a mirror or duplexed volume. RAID 5 (parity set) provides the best read performance while still giving the recoverability through data redundancy. In a parity set, the data is written across all the available drives in segments referred to as stripes. In each stripe, all but one drive will contain data, with the remaining drive containing the parity check information. Each time data is written, a checksum is calculated and written to the parity segment. If a failure causes the loss of a disk drive, the parity segment can be used to enable the stripe set to be regenerated. RAID 5 is usually referred to as a poor man s mirror because the more drives that are included in the set, the more cost-effective this solution. For example, if three drives are used, a third of the available space is lost to redundancy. If ten drives are used there is only a 10% loss of usable space. IN THE FIELD RAID: SOFTWARE VERSUS HARDWARE Even though software implementations of RAID must be known to pass certification exams and will be found in production systems, they are not nearly regarded as reliable as hardware RAID. For any high-volume, mission-critical application, it is therefore preferred to set up data redundancy mechanisms at the hardware level. Recoverability in the Event of Failure Two primary concerns in most data environments are data recover- ability in the event of the inevitable failures and considerations for minimal downtime. In the industry, one of the optimum ratings to strive for is the elusive five nines (99.999). This rating means that over any given period of time (generally accepted standard of 365 days minimum), the server remained online and servicing the end user 99.999 percent of the time. In other words, the total downtime for an entire year is a little over 5 minutes.

For high quality java hosting services please check tomcat web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 121 without the need for periodic emptying. Less frequent log backups are needed and the best possible log performance is achieved. RAID Considerations RAID (Redundant Array of Independent/Inexpensive Disks) is a technology where two or more disk drives can be configured in such a manner as to provide Larger volumes, because space on multiple disks is combined to form a single volume. Improved performance, by interacting with more than one physical disk at a time (disk striping). Safeguarding data, by providing mechanisms (mirror or parity) for redundant data storage. RAID is classified under many categories, each category assigned as a number. For more information about RAID hardware or other interesting information, visit the web site of the RAID advisory board: http://www.raid-advisory.com/CIC.html. this blog is concerned with only three RAID levels, 0 (zero), 1, and 5, although there are many different other qualifications for RAID. RAID 0 (stripe set) provides for multiple simultaneous read/write access across two or more disks. There is no data redundancy and thus no fault tolerance. A striped implementation is valid when strong backups exist and recovery time is not relevant. It might also be considered if the data is considered somewhat trivial and loss of data is unimportant. Parity sets provide optimum performance with no waste space allocated to data redundancy. Microsoft recommends a 64K stripe size, which should be considered if you are using RAID 0. RAID 1 (mirror) provides the exact duplication of one volume onto another. This solution offers quick recoverability but has a performance cost. Everything written to one volume is then written a second time to the alternate volume. A mirror implementation is valid for operating system drives or any other system where speed is not as important as recovery time in the event of a failure. In most implementations, if the first drive fails, the system has little or no downtime because it can operate fully on the mirror drive. Mirrors are a

For high quality website hosting services please check cheap web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 121 without the need for periodic emptying. Less frequent log backups are needed and the best possible log performance is achieved. RAID Considerations RAID (Redundant Array of Independent/Inexpensive Disks) is a technology where two or more disk drives can be configured in such a manner as to provide Larger volumes, because space on multiple disks is combined to form a single volume. Improved performance, by interacting with more than one physical disk at a time (disk striping). Safeguarding data, by providing mechanisms (mirror or parity) for redundant data storage. RAID is classified under many categories, each category assigned as a number. For more information about RAID hardware or other interesting information, visit the web site of the RAID advisory board: http://www.raid-advisory.com/CIC.html. this blog is concerned with only three RAID levels, 0 (zero), 1, and 5, although there are many different other qualifications for RAID. RAID 0 (stripe set) provides for multiple simultaneous read/write access across two or more disks. There is no data redundancy and thus no fault tolerance. A striped implementation is valid when strong backups exist and recovery time is not relevant. It might also be considered if the data is considered somewhat trivial and loss of data is unimportant. Parity sets provide optimum performance with no waste space allocated to data redundancy. Microsoft recommends a 64K stripe size, which should be considered if you are using RAID 0. RAID 1 (mirror) provides the exact duplication of one volume onto another. This solution offers quick recoverability but has a performance cost. Everything written to one volume is then written a second time to the alternate volume. A mirror implementation is valid for operating system drives or any other system where speed is not as important as recovery time in the event of a failure. In most implementations, if the first drive fails, the system has little or no downtime because it can operate fully on the mirror drive. Mirrors are a

For high quality java hosting services please check tomcat web hosting website.

NOTE 120 Part I EXAM PREPARATION Log Files Logs are not stored in file- groups. You can, however, use multiple log files and place them in different locations to obtain better and more varied maintenance and allow more storage space for log content. Sequential/Random Access Considerations Many processes performed within SQL Server can be classified as sequential or random. In a sequential process, the data or file can be read in a forward progression without having to locate the next data to be read. In a random process, the data is typically more spread out, and getting at the actual physical data requires multiple accesses. Where possible, it is desirable to keep sequential processes running without physical interruption caused by other processes contending for the device. Using file placement strategies to keep random processes separate from sequential ones enables the configuration to minimize the competition over the placement of the read/write heads. In an ideal configuration (somewhat tongue in cheek), you might want to separate the operating system from its page file. You would then place the log onto its one drive, separate from the data, with the data configured over a RAID volume as described in the following section. Take the seldom-used data (column or table data) and separate it from data that will be accessed more frequently. Place the indexes off on their own volume as well, and for about $150.00 $200,000.00, you have the optimum performance in a database server. In fact, while you re at it, why not throw in a couple of extra network cards and a few processors? Obviously, in most production environments the database team must balance an ideal configuration with the company bottom line. For many of these volume placements, a definitive cost must be budgeted. As a minimum requirement for almost any implementation, you should separate the normal sequential processing of the log files from the random processing of the data. You also improve recover- ability by separating the data from the log and placing them on separate physical volumes. If the volume where the data is stored is damaged and must be restored from backup, you will still have access to the last log entries. The final log can be backed up and restored against the database, which gives something very close to 100% recoverability right to the point of failure. An interesting and flexible strategy is to provide a separate drive solely for the log. This single volume does not have to participate in RAID architecture, but RAID might be desired for full recoverability. If you give the log the space of an entire volume, you give the log more room to grow and accumulate more of the log over time

For reliable and cheap web hosting services please check cheap web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 119 This is an important aspect if the time to perform a full backup of the entire database is too lengthy. To perform a backup in this manner, you would create a schedule to back up the individual filegroups (after an initial full database backup). In between each of the filegroup backups you then schedule log backups. Using this strategy enables you to break up an exceedingly large and long backup into more manageable increments. After a determination has been made to use a filegroup strategy for storing data, always ensure that when a backup is performed against a filegroup that the indexes are also backed up at the same time. This is easily accomplished if the data and indexes are stored in the same filegroup. If they are located on separate filegroups, ensure that both the data and index filegroups are included in a single backup operation. File Placement After the decision has been made to go with filegroups, then comes the next major decision in the physical design: where to WARNING SQL Server Does Not Enforce Backup Be aware that SQL Server does not enforce backup of data put the filegroups. Also, although logs are not stored into file- groups, they are stored in files and the placement of these files is and index filegroups in a single very important. operation. You must ensure that Considerations in the placement within the file system depend on the files associated with the indexes tied to a particular data set a number of variables. The first consideration is sequential versus are backed up with the data during random access. When a file is being read sequentially, the moving a filegroup backup. parts of the physical data device do less work (assuming no frag mentation). A large read/write process can use multiple physical devices at one time if they are placed on appropriate RAID hardware. Of course, there is also a software implementation of RAID that might not outperform the hardware one but is still beneficial. Another consideration for file placement is system recoverability. When files are spread amongst multiple physical volumes, a fuller and faster recovery becomes possible in the event of hardware failure. Also, many other operations can benefit from appropriate file placement. The next four topics look at these considerations and discuss some of the instances where they each might be implemented.

For high quality website hosting services please check tomcat web hosting website.

Chapter 3 PHYSICAL DATABASE DESIGN AND IMPLEMENTATION 119 This is an important aspect if the time to perform a full backup of the entire database is too lengthy. To perform a backup in this manner, you would create a schedule to back up the individual filegroups (after an initial full database backup). In between each of the filegroup backups you then schedule log backups. Using this strategy enables you to break up an exceedingly large and long backup into more manageable increments. After a determination has been made to use a filegroup strategy for storing data, always ensure that when a backup is performed against a filegroup that the indexes are also backed up at the same time. This is easily accomplished if the data and indexes are stored in the same filegroup. If they are located on separate filegroups, ensure that both the data and index filegroups are included in a single backup operation. File Placement After the decision has been made to go with filegroups, then comes the next major decision in the physical design: where to WARNING SQL Server Does Not Enforce Backup Be aware that SQL Server does not enforce backup of data put the filegroups. Also, although logs are not stored into file- groups, they are stored in files and the placement of these files is and index filegroups in a single very important. operation. You must ensure that Considerations in the placement within the file system depend on the files associated with the indexes tied to a particular data set a number of variables. The first consideration is sequential versus are backed up with the data during random access. When a file is being read sequentially, the moving a filegroup backup. parts of the physical data device do less work (assuming no frag mentation). A large read/write process can use multiple physical devices at one time if they are placed on appropriate RAID hardware. Of course, there is also a software implementation of RAID that might not outperform the hardware one but is still beneficial. Another consideration for file placement is system recoverability. When files are spread amongst multiple physical volumes, a fuller and faster recovery becomes possible in the event of hardware failure. Also, many other operations can benefit from appropriate file placement. The next four topics look at these considerations and discuss some of the instances where they each might be implemented.

For reliable and cheap web hosting services please check cheap web hosting website.