C H A P T E R  2

Pre-Installation Considerations

Unfortunately, in most production environments, you do not have enough time to preplan the installation of SQL Server. In addition to ensuring your current production environment performs as expected, you provide server specifications for new systems. This requires a lot of research to determine things such as how much RAM the server will need; what type and how powerful the CPUs should be; and the layout, size, and RAID (redundant array of independent disks) levels of your storage system, along with many other considerations. In addition to providing server specs, you also have to manage the implementation of processes and procedures after the installation is complete. If there is a lack of preparation and research time, you sometimes make the wrong assumptions or decisions when providing your recommendations. Although bypassing some research up front will save time initially, there may be an extreme cost to you and the company by not taking the time to preplan properly. For the next two chapters, we are going to talk about some of the things that you definitely want to consider before installing SQL Server 2008.

Choosing a SQL Server Edition

Microsoft SQL Server provides multiple editions to help organizations with different performance and price requirements to accomplish their goals. Table 2-1 gives a brief summary of these editions, which we will then talk about in more detail.

Based on the requirements received (that is, making a big assumption that you will actually receive good requirements), you can determine which edition of SQL Server enables you to fulfill those requirements. The editions range from the fully loaded SQL Server Enterprise Edition with all the bells and whistles to the stripped-down Express Editions that lack a large number of features. However, each edition provides applications with the features needed to have SQL Server as a backend. As always, the fully loaded option costs more than the stripped-down alternative; so instead of always going with the best edition on the market, do a little research and choose the edition needed to complete the requirements for the application. (The Developer Edition is not listed in Table 2-1 because it contains the same features as Enterprise Edition.)

Table 2-1. SQL Server Edition Feature Comparison

Feature	Enterprise	Standard	Workgroup	Web	Express
Resource Governor		x	x	x	x
Filtered indexes and statistics		x	x	x	x
Spatial support
FILESTREAM support
Change Data Capture and Change Tracking		x	x	x	x
Policy-Based Management
Performance data collection					x
Enhanced date and time support
Sparse column support		x	x	x	x
Data/backup compression		x	x	x	x
Enhanced SQL Server Audit		x	x	x	x
Transparent data encryption		x	x	x	x

Enterprise Edition

Enterprise Edition is the fully loaded, production-licensed edition of SQL Server. This edition provides the performance, scalability, security, and availability needed to perform as the data layer for enterprise-wide applications. Enterprise Edition runs on servers with x86 (or 32-bit systems), x64 (or 64-bit systems), and IA64 (or Itanium 64-bit chip set systems). Enterprise Edition is used on applications where you identify the features of that edition as a necessity to meet the requirements or future requirements for SQL Server. Unfortunately, the costs of licenses are too expensive to justify the purchase of the edition if the organization will not be taking advantage of the features that it provides.

Please spend time narrowing down the application requirements and thoroughly evaluating the editions prior to recommending the purchase of an edition. The company that employs you will appreciate it.

All the features that Microsoft provides are generally available in the Enterprise Edition. The good and bad news for you, the database administrator, is that all features available in SQL Server 2008 will be at your disposal. What does that mean? It means that you will assume all the risk associated with implementing the new features in your production environment. While figuring out the effective use of new features like the Resource Governor, there may be some mishaps or problems that arise from issues or situations that you did not think through or simply did not think about. Remember to always test (even though you do not have real test environments to test in) and to always know when and how to use the new features instead of using them because they are there. (We will discuss when and how to use the new features throughout this book.).

Standard Edition

Standard Edition is another licensed production system without all the features of the Enterprise Edition, but it is built to provide ease of use and manageability. Standard Edition runs on servers with x86 (or 32-bit systems) and x64 (or 64-bit systems). Standard Edition is run in environments where you have determined that the features provided only in the Enterprise Edition are not needed to accomplish the current and future requirements of all applications running on the server. Let's be honest: Asking for or receiving detailed requirements from management, customers, or clients probably will not happen. (You will be lucky if you can make them out through the beer stains on the napkin.) Therefore, when it comes down to determining the version that meets the bare-bones requirements you receive, you may have to go back to the requirements provider to ensure all the documentation is accurate and complete. Try asking the requirements provider a series of questions in different ways to help you determine what the real requirements are for the application. That way you will feel comfortable supporting the application on the edition of SQL Server chosen. Standard Edition is significantly cheaper than Enterprise Edition so be wary of management wanting to install Standard Edition even though the application needs Enterprise Edition features.

Stand your ground and make the application owner or management sign off on the functionality that you will not be able to provide if they insist on purchasing the cheaper edition. That way, when the blame game starts and people start pointing fingers, you can pull out your documentation and utilize your get-out-of-jail-free card.

In the Standard Edition, Microsoft has included many of the features required to manage a production environment in small to midsized organizations, and maybe even larger organizations, depending on the requirements of the application.

Highlights of the Standard Edition include the following:

• High availability
  • Clustering
  • Mirroring
  • Log shipping
  • Replication
• Manageability features
  • Central management servers
  • Policy-Based Management
  • Standard performance reports
• Management tools
  • All management tools

Overall, the Standard Edition is not a bad choice when you do not have to utilize the greater number of features available in the Enterprise Edition. Remember, a “nice-to-have” is completely different than a feature that is absolutely necessary. The cost difference does not justify the purchase of the Enterprise Edition if the features are just nice-to-haves.

Developer Edition

The Developer Edition contains all of the features of the Enterprise Edition, but it is licensed for nonproduction systems. The Developer Edition runs on servers with x86 (or 32-bit systems), x64 (or 64-bit systems), and IA64 (or Itanium 64-bit chip set systems). This edition is ideal for developers or administrators looking to install and test out SQL Server 2008.

Developer Edition offers a great introductory platform for validating your application's functionality with the new version of SQL Server 2008, along with providing a playground for trying out features. While doing this, make a detailed evaluation of the features that your production environment requires. If the organization is planning to purchase the Standard Edition, then experiment with the features of Enterprise Edition by using the Developer Edition. That will help you determine if the production environment needs the features that the Standard Edition does not support. Likewise, if the organization is purchasing Enterprise Edition, then use the Developer Edition to evaluate what supporting your production environment would be like without all the features available in the Enterprise Edition.

For example, Enterprise Edition is required to take advantage of backup compression, a wonderful enhancement added by Microsoft. However, if the largest database supported by an instance of SQL Server is not that large, then how much value does the backup compression feature actually provide for that instance? By experimenting with the Developer Edition, not only do you get to use the backup compression feature, but you can also familiarize yourself with the commands and syntax of backup compression. You can also utilize the Developer Edition to generate documentation of the backup and restore processes with and without compression for comparison purposes. Documenting the results of your test prepares support for your recommendation of the next edition of SQL Server to purchase.

Workgroup Edition

The Workgroup Edition is a licensed production system with fewer features than the Standard and Enterprise Editions. The primary purpose of the Workgroup Edition is to provide data management and reporting platform for business applications running at branch locations. The Workgroup Edition runs on servers with x86 (or 32-bit systems) and x64 (or 64-bit sys-tems). Workgroup Edition is installed in environments where you or the organization has determined that there is a benefit to separate instances of SQL Server residing at the organization's multiple branches.

The Workgroup Edition includes the core database components of SQL Server along with remote synchronization, security, and management capabilities for supporting the branch applications. Following are some of the highlights of this edition:

• High availability
  • Log shipping
  • Subscriber in replication
• Manageability features
  • Policy-Based Management
  • Policy-Based Configuration
• Management tools
  • All management tools

Remember, the Workgroup Edition was created to satisfy specific organizational requirements. When it comes to validating the features and functionality of this edition, follow the same rules previously described. Document the feature information by using the Developer Edition, or from reading books or online content, to ensure that the Workgroup Edition will fulfill the business requirements that have been provided to you.

Web Edition

The purpose for the Web Edition, as the name implies, is for web-hosting companies that need to provide their customers with highly available and scalable solutions for a low cost. Web Edition runs on servers with x86 (or 32-bit systems) and x64 (or 64-bit systems). Web Edition has no restrictions on the amount of memory the instance can support or a cap on the size of databases. That increases its scalability options for web-hosting companies. The price is per processor, per month under the general guideline of the Service Provider Licensing Agreement (SPLA). Once again, this edition targets specific organizational requirements and should be researched further to determine if you can benefit from using this version.

The Web Edition includes the core database components of SQL Server and closely resembles the Workgroup Edition. For example, here are some of the highlights:

• High availability
  • Log shipping
  • Subscriber in replication
• Manageability
  • Policy-Based Management
  • Policy-Based Configuration
• Management tools
  • SQL Server Management Studio (Basic Version)
  • All other tools

Express Edition

A free version of SQL Server, the Express Edition, comes in three forms: SQL Server Express (the basic package with no extra tools), Express with Tools, and Express with Advanced Services. Regardless of which form you buy, Express Edition is the free version of SQL Server 2008 designed to help those who are not database administrators on a daily basis but who need a database backend to support their desktop and web applications. Express Edition runs on both x86 (or 32-bit systems) and x64 (or 64 bit systems), but it does not provide any advanced features. Express Edition supports one processor, 4 GB of storage, and 1 GB of memory. Express with Tools contains the core database engine, SQL Server Management Studio, and is the main edition of Express Edition. Express with Advanced Services includes all the features of Express with Tools, but it adds Full-Text Search and reporting services.

The bare SQL Server Express Edition includes only the core SQL Server database engine. The main usage of SQL Server Express Edition is in deployment scenarios. You can package your application and include SQL Server Express Edition for your deployment.

Surprisingly, the Express Edition does contain some of the new Enterprise Edition features and supports some high-availability solutions. Following are the highlights of this edition:

• High availability
  • Witness in database mirroring
  • Subscriber only in replication
  • Utilize SQL Server Change Tracking
• Manageability
  • Policy-Based Management
  • Policy-Based Configuration
• Management tools
  • SQL Server Management Studio (Basic Version)
  • SQLCMD utility

Overall, we would say that for a free edition of SQL Server, Express Edition contains a large number features, as well as a core engine that makes it a viable option to install and play with.

As we wrap up the section on editions, we want to encourage you to make time to evaluate the features and functionality of each edition as it applies to your situation before providing a recommendation on which SQL Server edition to purchase. For some of you, it is a no-brainer: You will have to utilize the Enterprise Edition for a number of reasons. However, there are a large number of you who might think you need the Enterprise Edition, but you do not have the evidence to support that decision. Install the Developer Edition, try out the new features, and document your results. That way, when the time comes to discuss with management or the application owner the edition that will be purchased, you will have documentation, hard facts, and solid analysis to support your recommendation and why so much money should be spent on the Enterprise Edition.

Determining Hardware Requirements

Now that you have determined the edition of SQL Server 2008 that best fits your requirements, the process of figuring out the specifications for the hardware begins. The commonly used industry term for this process is “specing” or “spec'ing out” the server. To spec out the server is to create a document that contains specifications for the server (both brand and model), the CPU requirements (how fast and how many), and the storage and memory requirements. (There are additional things that will be included in this document, like NIC cards, but that is outside the scope of this book.)

Spec'ing out servers is a process that definitely is an art and not a science. So many key factors drive decisions such that the slightest change in the business requirements can dramatically change two different server specifications. Some of you currently work in an environment where documentation occurs during downtime, so the organization probably does not have a method of ensuring that all of you follow the same process. For discussion purposes, let us assume that most organizations have a documented process for creating the specifications of servers. Unfortunately, if you were to take hold of 100 companies' plans, you would probably find a large number of differences, specifically in the interpretation of business requirements and then transformed into physical hardware requirements. With all the variation that exists in the industry, it would be unwise for us to tell you how to spec out your server. The goal of this section is to provide you with tips and things to think about during the decision-making process at your organization.

Before you get started, there are a number of questions that should be answered regarding the software before you can realistically provide hardware requirements. If you lack such answers about the software requirements, then you are either going to have to research the answers to these questions or make intelligent guesses.

Some questions that you may want to consider are the following:

• What types of requests will the application be supporting (such as online transaction processing, online analytical processing, and so on)?
• How many concurrent users does the application expect?
• What is the expected response time of the application?
• What is the application usage pattern? What is the frequency of reads compared to writes? How does it use tempdb?
• What is the maximum number of transactions per second?
• What is the combined spaced needed to support all of the databases on that server?
• What are the future growth plans for the application over the next 3 to 5 years?
• What is the projected growth of the data over the next 3 to 5 years?
• What is the skill set of the developers writing the application? If this is an upgrade, then what is the performance of the current application? If this is a new application or internally built, then what is the reputation of the company or group? Do the developers follow normalization and indexing practices? Do they use stored procedures to access data? Figure out what you can about the developers.
• What are the availability requirements? It always helps to know if you will be purchasing more than one server.
• What is your budget?

Feel free to add as many questions to this list as you feel is necessary. The more information you know about the environment, the closer you will be to ensuring that your recommendation meets and exceeds your business requirements.

One other thing you need to know before getting started is what the minimum requirements are for the available SQL Server editions. See Table 2-2 for a brief summary of those requirements by edition.

Table 2-2. SQL Server Edition Minimum Requirements

Edition	Memory	Processor
Enterprise (64-bit) IA64	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: Itanium Processor Speed: Minimum Recommended 1.0 GHz or faster
Enterprise (64-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: See web site Processor Speed Minimum 1.4 GHz, Recommended 2.0 GHz or faster
Standard (64-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: See web site Processor Speed: Minimum 1.4 GHz, Recommended 2.0 GHz or faster
Developer (64-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: 1.4 GHz Processor Speed: Minimum 1.4 GHz, Recommended 2.0 GHz or faster
Workgroup (64-bit)	Minimum: 512 MB, Recommended: 2.048 GB, Maximum: OS Max	Processor Type: 1.4 GHz Processor Speed Minimum 1.4 GHz, Recommended 2.0 GHz or faster
Web (64-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: See web site Processor Speed: Minimum 1.4 GHz, Recommended 2.0 GHz or faster
Express with Tools (64-bit)	Minimum: 512 MB Recommended: 1 GB Maximum: 1 GB	Processor Type: See web site Processor Speed: Minimum 1.4 GHz, Recommended 2.0 GHz or faster
Express with Advanced Services (64-bit)	Minimum: 512 MB Recommended: 1 GB Maximum: 1 GB	Processor Type: See web site Processor Speed: Minimum 1.4 GHz, Recommended 2.0 GHz or faster
Enterprise (32-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: Pentium III or higher Processor Speed: Minimum 1.0 GHz, Recommended 2.0 GHz or faster
Standard (32-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: Pentium III or higher Processor Speed: Minimum 1.0 GHz, Recommended 2.0 GHz or faster
Developer(32-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: Pentium III or higher Processor Speed: Minimum 1.0 GHz, Recommended 2.0 GHz or faster
Workgroup (32-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: Pentium III or higher Processor Speed: Minimum 1.0 GHz, Recommended 2.0 GHz or faster
Web (32-bit)	Minimum: 512 MB Recommended: 2.048 GB Maximum: OS Max	Processor Type: Pentium III or higher Processor Speed: Minimum 1.0 GHz, Recommended 2.0 GHz or faster
Express with Tools (32-bit)	Minimum: 512 MB Recommended: 1.024 GB Maximum: 1.024 GB	Processor Type: Pentium III or higher Processor Speed: Minimum 1.0 GHz, Recommended 2.0 GHz or faster
Express with Advanced Services (32-bit)	Minimum: 512 MB Recommended: 1.024 GB Maximum: 1.024 GB	Processor Type: Pentium III or higher Processor Speed: Minimum 1.0 GHz, Recommended 2.0 GHz or faster

Determining CPU Needs

Determining the amount of CPU needed to handle the workload of the application or applications running on a server can sometimes be a little challenging. We would love to provide you with absolute metrics, but there are too many variables to provide that level of detail. Instead, we would like to discuss some of the factors that contribute to the amount of CPU needed and walk through a process that helps you build servers that resemble the server(s) you have in-house.

Deriving a Baseline Specification

To begin, you need baseline information for comparing your questions. If an external vendor developed the application you will be supporting, then hopefully they provided recommendations for CPU needs based upon users, workload, and so on. If you are purchasing a server for consolidation or to upgrade existing hardware, then utilize the existing hardware to determine usage statistics. If you are implementing a brand-new application in your environment with no supporting documentation, then find an application that closely resembles the expected usage patterns and user base and use its SQL Server for your baseline. As a last resort, go out on the web, find an application that is similar in functionality and features, and use its user and workload recommendations.

Consider Future Utilization

Now that you have a baseline server to start from, start looking at the causes of increased CPU utilization. Do not forget to pay close attention to how high the CPU usage is within your baseline and compare that to the level where you would like to keep the CPU usage of the new server. For example, if your baseline server constantly runs at 80% CPU utilization and the new server should run at 50% or lower, then make sure to consider that factor when determining how much CPU is needed.

Here are some questions to think about when deriving a new server specification from an existing, baseline server:

• What is the difference in the number of concurrent users?
• What is the transaction per second difference?
• What is the response time difference?
• Are the application usage patterns the same?
• Did the quality of the developers change?

I hope that these questions stimulated your mind with even more questions to consider. Please add as many questions to this list as possible. Try to consider all the factors and think through all of the scenarios that can affect CPU utilization.

Example Using the CPU Questions

Let's say that your baseline server runs on average at 60% CPU utilization. Your company plans to upgrade the application, and you are getting ready to purchase a new server to support that upgrade. Because of the new features of the application:

• Your user base is going to increase by 1½ times what it is now.
• Your transactions per second are going to double.

Knowing about these increases, you can consider them in light of some additional requirements that you have pertaining to the new server:

• You want to keep the same response time.
• You want your system running at 40% CPU utilization instead of 60%.

Finally, you also know a couple of additional facts:

• The usage pattern of the application will stay the same.
• The vendor still has not figured out how to partition its tables or any other performance improvement technique.

Reviewing this scenario definitely shows you the need to purchase more CPU than what currently exists in your baseline server. How much to increase the CPU? We cannot tell you that. As the DBA, you have to determine the importance of each answer and factor in what it will mean to the increase or decrease in CPU utilization for the new server. This method will take some getting used to, and the process may not be perfect the first couple of times through. As you fine-tune your questions and establish what the answers mean for the CPU, you will begin to develop a consistent method for determining the amount of CPU needed for any new server that you are spec'ing out.

Disk Subsystems

Determining the design, size, and utilization of the disk subsystem is either complex or extremely simple. Unbelievably, there are still DBAs out there who support systems with one physical hard drive that contains the operating system files, the data, log, and backup files for SQL Server. In some instances, servers were set up this way because of budget reasons. In other instances, servers were set up this way due to the lack of knowledge in the spec'ing out phase of the disk subsystem. The goal of this section is to provide you with some information and tips about determining the space needed to store the database. We also hope to help with terminology and to present options for laying out the data, log, tempdb, and backup files on the server.

For those who have had conversations with server administrators or hardware specialists, you know they use terms not commonly used in the database world. If you are not prepared and do not understand these terms when discussing a disk subsystem, then you may miss out on a key design decision. Therefore, here is a brief list of some common terms and their definitions to help you with that conversation.

• Physical hard drive: The actual tangible unit attached to a server.
• Logical hard drive: Provides usable storage capacity on one or more physical disk drives.
• Local disks: Disks that are controlled and connected to the hard disk controller.
• Spindle: Responsible for turning the hard disk platters. This is another way to reference a physical hard drive.
• Logical unit number (LUN): The number assigned to a logical unit.
• Disk subsystem: The disks on the system.
• RAID level: Used to simultaneously use multiple disks for better performance.
• Storage area network (SAN): Architecture used to attach remote computer storage devices to servers.
• Disk array: A disk storage system that contains multiple disk drives.
• Hot standby: Also known as a hot spare, it refers to a physical disk that resides in the disk array waiting for another disk to fail so it can fill in.

The first decision probably made for you by the organization is how the disk subsystem is attached to the server. Generally, any established company has already made the strategic decision to purchase a SAN, use a fiber channel to connect to the disk array, or to use local disks. So we are not going to spend any time discussing how the disk will be attached to your SQL Server. For the most part, you will need to fit in with whatever storage infrastructure your organization has already chosen.

Database Sizing

Choosing the sizes of your disks is a different matter. It is absolutely within your purview to specify how much disk space your database will need for log files, data files, and so forth. There are multiple ways to propose the estimated sizes of your databases. One method is to guess, but the method that we like the most takes you back to the baseline server. This method is a lot easier if you are upgrading the server, have a good comparable server in your environment, or have specification documents from a vendor.

Estimating Size from a Baseline

Starting with your baseline, here are some of the questions that you may want to ask:

• What is the expected user difference?
• How large are the current databases (applies to upgrading only)?
• How many years did it take to accumulate that amount of data (applies to upgrading only)?
• What is the difference between the data retention policies for the baseline and new databases?
• How many years is your data projection for?
• Are there any projected data retention policy changes?
• Is there a difference between the data storage practices used by the application your baseline database supports vs. the new application that you are installing?

As we said before, please add your own questions to this list. The questions listed here are to help stimulate ideas. The more questions you come up with, the better your database size estimation will be. Do not forget to determine how much each answer factors into the final recommendation for the sizing of your disks.

Estimating Size Based Upon Row and Column Sizes

If the previous method does not work for you, then Microsoft provides some information about sizing your database. The size of a database consists of the sum of the individual sizes of the tables within that database. The size of a table is determined by the number of indexes, the types of indexes, and/or the heap if the table does not have any indexes.

Following is a brief summary of Microsoft's size estimation process. First, for each table, you must calculate the amount of space required to store data on the heap:

1. Calculate the space used to store data in the leaf-level pages.
2. Calculate the space used to store index information in the table's clustered index, if there is one.
3. Total the calculated values.

Next, you must estimate the size of each nonclustered index upon the table:

1. Calculate the space used to store index information in the leaf level of the nonclustered index.
2. Calculate the space used to store index information in the non-leaf levels of the nonclustered index.
3. Total the calculated values.

Repeat the preceding two processes for each table in your database. Sum all the values together. When you have gone over every table and index, you will have a reasonable estimate of the amount of disk space that you need to allocate to your database.

Regardless of the method used to determine the projected size of your databases, you still have to figure out the sizes for the log, tempdb, and backup drives. The backup drive will have to be large enough to store the database backup file. It will also need to be large enough to hold multiple copies of the backup file if holding multiple backups is part of your backup strategy. The backup drive also needs room for transaction log backups, if you plan to back up your log files.

The size of the log drive should be 25% of the database drive, and the tempdb drive should also be 25% of the database drive. Once you have determined the sizes of your new database and the sizes of the drives needed for any additional files, the next step is for you to determine the RAID levels for the drives and how you want them configured.

Determining RAID Levels

Hopefully, there is still some flexibility in the decision-making process for the RAID levels of the physical disks. Which level you choose depends upon your requirements for fault tolerance and performance. Cost is also a significant factor. If you want the best in both fault tolerance and performance, then be prepared to pay for it.

Understanding RAID Levels

There are three basic RAID levels that are used on SQL Server: RAID 1, RAID 0, and RAID 5. Also commonly used is a combination of 1 and 0, which is called RAID 10. Following are descriptions of these levels:

• RAID 0 (striped disks): Stripes data across multiple drives; no redundancy.
• RAID 1 (mirrored disks): Each drive has its data copied to another drive; good redundancy.
• RAID 5 (striped disks with distributed parity): Stripes data across multiple drives, and parity information for one drive is distributed across all drives; good redundancy.
• RAID 10 (mirrored and striped): Data is striped across multiple drives and then copied to another drive; good redundancy.

RAID 0

Of the three RAID levels previously listed, RAID 0, or striped, is the best for input and output (IO) performance. When the disk subsystem is set up on RAID 0, the disk controller ensures the striping of data occurs evenly across all disks in an array. Unfortunately, if one disk fails in the array, then the data stored within those disks are lost. RAID 0 has no fault tolerance, we don't use it on systems that are required to be available, and data loss is unacceptable. RAID 0 is the cheapest of the listed RAID levels because of the lack of redundancy. You do not have to purchase additional drives which means all the drives in a RAID 0 array are used.

RAID 1

RAID 1, or mirrored disks, copies all data written to the primary disk to a secondary disk. Both disks are available for reads, which makes RAID 1 very fast for data retrieval. Because data writes occur to multiple disks, then a failure in the primary or a secondary disk does not cause data loss or application unavailability. That is the reason why RAID 1 provides redundancy within the disk array. The redundancy provided by RAID 1 is what makes this option expensive. In order to set up RAID 1, every physical drive purchased for writes has to have an additional drive in order to copy the data. You can see how the cost for using this RAID level increases rapidly.

RAID 5

RAID 5, or striped disks with distributed parity, stripes data across multiple drives and writes parity bits across all drives. Data retrieval on RAID 5 is very fast, but writes are extremely costly. Because of the parity bit, when one command writes to disk, one write occurs to write the data to disk, and then a parity write occurs to write to the parity drive, followed by a check to ensure that a disk failure did not occur during the write operation. A single write command potentially costs four IO; that is costly in an environment with heavy writes.

On the other side, the parity bits ensure that, in the event of drive failure, the re-creation of the data stored on that disk occurs without loss of availability to the application or data loss. The redundancy of RAID 5 is good as long as you don't lose multiple drives. It is good practice to use a hot-swappable drive in RAID 5. A hot-swappable drive is one that is part of the array, unused until a failure occurs in one of the disks. That drive has all the data from the failed drive re-created on it, and then it steps in and becomes a part of the array. RAID 5 is a relatively inexpensive option for providing redundancy. Writes occur on all drives, minimizing the need for extra drives not used for writing. At a minimum, RAID 5 requires three drives, which means 33% of each drive will be used for parity.

RAID 10

RAID 10 combines the benefits of RAID 1 and RAID 0 to create an awesome RAID level. RAID 10 stripes data across mirrored drives. Therefore, the mirroring occurs first and then stripes data across the mirrored or secondary drives to improve performance. Be careful not to confuse RAID 01 with RAID 10. The implementation of RAID 01 occurs by striping first and then mirroring the striped set. RAID 10 provides the benefit of striping to RAID 1 while creating the needed redundancy for RAID 0, making the combination of the two RAID levels nice complements to one another. Clearly, you can see the cost of RAID 10 over the previously discussed RAID levels. At a minimum, RAID 10 needs four drives, and the numbers quickly increase from there. Luckily, hard drives are getting cheaper, making this option more feasible in today's environments.

Choosing Your RAID Level and Laying Out Your Files

Now that you understand RAID levels, we can discuss the layout of SQL Server's files and the RAID levels of the drives that will support them. Here are a couple of things to keep in the back of your mind:

• The RAID level selected may increase the cost of your disk subsystem. So know your budget as you read the tips for laying out your SQL Server files.
• The size of the system that you are designing may not require you to implement all of the tips that we provide. If you are creating a system for a couple of users with low transactions per second, then it is probably overkill to implement everything we suggest in this section.

We like to set up all of our servers the same, but that decision of how you set up your own servers is left up to you. Depending on the disk subsystem that you have at your organization, you may not have much decision-making power when it comes to determining the RAID levels of whatever new server you are configuring. In some environments, SAN administrators predetermine the RAID levels for the drives. Do not get upset—there is no reason to worry about decisions you have no control over. Just worry about what you do have control over.

• Place your data, log, tempdb, and backups on separate physical drives.
• Set the RAID level on your data drive to RAID 10, or set it to 1 if the budget does not permit 10.
• Set the RAID level on your log drive to RAID 1.
• Set the RAID level on your tempdb drive to RAID 10, or set it to 1 if the budget does not permit 10.
• Set the RAID level on your backup drive to RAID 5.

Right now, not everyone in the industry agrees with setting up your data drives on RAID 1 or RAID 10. There are people who would like to place data files on a RAID 5 disk array. We have supported databases on both setups and could argue both ways. Our preference is RAID 10 or 1, and that is why we listed those RAID levels in the section. If you have the time, then do your own research so you can make an informed decision.

Final Disk Storage Configuration

After the drives are set up, there are just a few more things that we recommend you doing to ensure your disk subsystem is ready for production:

• Ensure your disk subsystem can handle your SQL Server IO production load by utilizing IO subsystem stress tools.
• Create separate tempdb files for every physical processor. (Multiple cores count as multiple processors, but hyper-threading technology does not.)
• Make sure the disk offset of your drives are set up to 64 KB. (Feel free to research disk offsets and how and why to validate your disk offset online.)

Proper configuration of your physical and logical disk is essential to SQL Server running effectively. Make enough time to test the IO subsystem of your SQL Server before the server goes into Production. Validate the correctness of your supplied configuration options. We have reviewed newly built servers and identified partitions that were on the same physical drive with different logical drive letters instead of separate physical drives with different logical drive letters. So, verify the settings or get the server administrator to show you how if you do not know. Learn to use the physical disk monitors in Performance Monitor (covered later on in this book) to proactively monitor your disk subsystem. That way, you are the first one to know when your disk starts to become a bottleneck. Take advantage of the information and implement or verify that these tips are in place on your server(s).

Determining Memory Requirements

Generally, we try to purchase as much memory as a server will hold. The more memory you have, that much more of your database can be stored in memory, decreasing the amount of time it takes to retrieve data from the disk. Unfortunately, when budgets are tight, nobody has the luxury of spending extra money.

When accurately trying to determine the amount of memory needed for a new server, we always resort to our baseline servers and start asking questions. The following questions are not meant to be the only questions asked. They are designed to get you thinking about the changes within your application that would affect the amount of memory needed. For the following set of questions, we are going to add a little more reasoning around each question and why it is important to memory contributions:

• What is the difference in the number of concurrent users between your baseline database and the new one you are configuring? The more concurrent users there are, the greater the need to keep frequently accessed data in memory. The greater the memory, the more data that are kept in memory, preventing physical disk reads.
• What is the transaction per second difference? If transactions increase, then the amount of data needed to process the requests likely will increase. The more memory you have, the more data pages you can keep in memory instead of on physical disk.
• Is the quality of the developers different? If you have developers who do not take advantage of proper database practices, then their queries will pull entire tables into memory for table scans. Depending on the amount of data, the performance of those queries maybe poor, and their execution may hog memory for an extended period of time, forcing other frequently accessed data to be removed from memory.
• What is the difference between the size of your baseline database and the system you are implementing? What about expected growth? The bigger the database, the more memory you want. Generally, larger databases require larger data sets in memory to fulfill user queries. The more memory you have, the more queries you can process without having to go to a physical disk.

As we said before, please add any questions you would like to the preceding list. Figure out how the answers to these questions will factor into the overall increase or decrease of needed memory. Never underestimate the amount of memory you need. In fact, you should over-estimate when it comes to memory. After all, what is the worst thing that would happen if your entire database were in memory? Our guess is that you would have some happy customers.

Choosing the Server

There are a number of vendors that build servers that allow enough customization in their server models to build a SQL Server to meet and exceed the needs of any application. No one pays us to advertise for one company or the next, so we really do not have a vendor preference. And in many cases, an organization will have a standard vendor that you will be required to use, except in the rare case when requirements dictate otherwise.

When researching models provided by your hardware vendor, make sure you pay attention to the maximum memory, CPU, and so forth that each model supports. Leave yourself a bit of headroom for future expansion. That way, if one of the requirements was wrong, some of the requirements changed after ordering the server, or one of your best guesses was a little off, you have some wiggle room in the currently ordered hardware. We would prefer to go back to management and request additional money to buy more memory instead of requesting money to purchase a new server because the server cannot handle any additional memory.

Another important note about server vendors is that they are building most of their servers with 64-bit support. Following are some of the benefits of a 64-bit SQL Server compared to a 32-bit SQL Server:

• Larger, directly addressable space, increasing the memory available for processing complex queries
• Enhanced parallelism and support for up to 64 processors, which increases scalability
• Capability to handle a larger number of concurrent users with improved response time
• Improvements within the bus architecture, increasing the amount of data passed through to processor and cache, in turn improving performance

There are some benefits to putting SQL Server 2008 on a 64-bit server, especially when the hardware that you purchase is designed to run 64-bit applications. When the time comes to pick out the server, find the model that is big enough to meet your minimum requirements and large enough to handle any unexpected changes with the application. After you have determined the model of the server to use, finish preparing your neatly formatted document, including all of the specifications that you have just determined. Congratulations, your server specification document is complete.

Documenting a repeatable process for consistently spec'ing out new servers ensures that the decision-making process for all new servers will cover the same considerations. The old way of undocumented, intelligent analysis does not work if you want all DBAs performing this task the same way. Sit down with your team, go over the questions that should be considered, and determine the factor of increase or decrease based on the response to each question.

Creating a sound, server specification document will not only save you time when spec'ing out servers, but it will also save support time when you are up and running in production. Spend the time to do the work, and document the process so that that you can repeat it and improve it going forward.

SQL Server Consolidation

Consolidating SQL Server instances onto a single or multiple servers has become increasingly popular with 64-bit hardware. 64-bit servers have enabled you to utilize servers with up to 32 processors and the maximum amount of RAM supported by the operating system directly without having to use other technologies to address the memory. That kind of power within a single server has helped organizations easily make the switch to consolidated setups.

What does it mean to consolidate your SQL Server? To consolidate your SQL Server is to combine multiple, separate database servers into one bigger, more powerful database server. The consolidation process can be smooth and simple or extremely complex, depending on the applications that the individual database servers are supporting. The purpose of this section is to provide you with some benefits of SQL Server consolidation along with tips and things to watch out for if you are thinking about consolidation.

For those of you scratching your head trying to figure out why in the world one would want to consolidate multiple SQL Server instances, let's review some of the pros and cons.

Benefits of server consolidation include the following:

• Reduces the amount of management required to support physical servers
• Decreases the number of SQL Server licenses needed
• Increases the application uptime and availability
• Increases performance within the applications

But there's a cloud to go along with every silver lining. Drawbacks to consolidation include the following:

• Management of the consolidated server is more challenging in terms of maintenance windows, patching, scheduled jobs, and so on.
• When the consolidated server goes down, then all the applications on that server are down.
• The upfront cost of purchasing a big beefy server, which includes the license and hardware, is expensive. You may want to consider purchasing a redundant server for the consolidated server. When you have multiple applications on one server, you definitely don't want to loose that server for any period of time.

Although there are a couple of issues to think about before deciding to consolidate, we definitely think exploring the benefits of SQL Server consolidation in your environment is a worthwhile task. We're going to provide you with some lessons that we have learned while supporting consolidated environments:

• Do not put mission-critical applications on a consolidated server. We suggest that you deal with those applications individually.
• Write clearly documented guidelines for applications to abide by in prior to adding the application to the consolidated server. For example, application users can have database owner rights to their database only. Do not allow users to have system administrator rights because that impacts more than just the application they are using.
• Name your automated processes with some form of application identification. In a consolidated server environment, multiple applications will likely have their jobs that need to run. Identifying jobs for an application without any form of identification can be time consuming. That is not the situation you want to be in, especially when you have management on your back about rerunning a process.
• Watch out for out-of-control applications and set up a policy for removing that application from the consolidated server. Remember, one bad application impacts all the other applications on that server.
• Only add one application/database at a time to the consolidated server. This enables you to monitor the server after the addition of an application to ensure that the server resources are at a manageable level.
• Determine what the normal resource utilization should be. Make sure you know when you need to stop adding applications.
• Group similar application types and functionality together on the same consolidated database server. In most cases, similar applications have similar usage patterns, maintenance windows, and other similarities that enable the support of those groups of applications easier.
• Spend some time monitoring an application before adding it to your consolidated server. That way, you estimate the amount of resources it will consume on the consolidated server. You do not want to wait until the addition of the application to the server to realize that it will negatively influence the resources on the consolidated server.
• Create service level agreements for every application on the server. With that information, you will know the order in which to get applications back up in case of emergency.

If you are considering consolidating your SQL Server instances but would like more information about the servers in your environment to help identify hardware and software similarities, then use the Microsoft Assessment and Planning (MAP) toolkit. MAP will provide you with detailed reports of all the instances of SQL Server installed in your environment. MAP scans the network and collects detailed information from each computer using Windows Management Instrumentation (WMI) calls, Remote Registry service, and Simple Network Management Protocol (SNMP). MAP then stores the collected information in a SQL Server database for later reporting.

Before consolidating your SQL Server instances, you may also want to consider ensuring the compatibility of the features on the various SQL Server instances. Apparently, the features selected during installation can dictate how SQL Server stores information within the data files. When databases are set up on editions containing those features, they cannot be migrated to a SQL Server that does not support them. Running the sys.dm_db_persisted_sku_features dynamic management view will allow you to compare the features that are restricted by the edition. If the view does not return results, then the database does not contain any edition-specific features.

Do not get discouraged after reviewing the preceding tips. Consolidating SQL Server is not always complex, and it provides benefits to you and the organization. Look at your database environment and identify servers that have minimal CPU utilization and low transactions/batches per second. Start with small, low-impact servers and then determine if you want mission-critical applications on a consolidated server. Make sure you have thought through and tested your high-availability plan. Having a server go down and being without one application is stressful, but losing ten applications at the same time without a recovery plan is just plain madness. So spend a little time up front to document and test your recovery plan before adding databases to the consolidated server. Who knows? With the money you save the company in license fees and server maintenance, you may actually see the benefit in your paycheck. Well, we can dream can't we?

Consolidation with Virtual Servers

Virtual servers are becoming increasingly popular for SQL Server installations, especially for development and sandbox environments. Virtual servers enable you to consolidate multiple SQL Server instances onto one host, creating a consolidated server. The smaller the server, the more likely it is a candidate for virtualization. Servers within the two processor/4 GB of RAM range are easily virtualizable, while servers within the four processor/8 to 16 GB of RAM range are candidates for virtualization, but require a little more planning and effort. Try avoiding servers larger than the preceding requirements if possible. If you decide to use virtual servers for your production environment, we strongly recommend that you test the disk subsystem to ensure that the virtual server performs at acceptable levels. Use techniques discussed earlier in this chapter and performance counters discussed in Chapter 14 to monitor the performance of your subsystem. Just keep in mind that virtualizing servers within your environment may be an option to explore when building out your new environments.

Summary

There are many decisions that must be made prior to installing SQL Server. At a minimum, you have to determine which edition of SQL Server to install, the hardware that SQL Server will run on, and whether to add the database to an existing consolidated server. We know planning and preparing is something that you do not have enough time to do. Remember your Five P's: Proper Planning Prevents Poor Performance. Repeat that phrase to encourage yourself during the times when properly preparing for an installation doesn't seem likely.

You may have noticed that this chapter covers pre-installation from the hardware and software considerations. Another major pre-installation consideration is ensuring that your system meets the availability requirements of post-production. The next chapter is going to focus on high availability and the various solutions that are available in SQL Server 2008. The solution implemented to keep your system available is as important as the hardware and software you choose. So make time to prepare for all of these considerations before installing SQL Server 2008.