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.
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.)
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.
Note Microsoft provides you with the option to purchase licenses per processor or per CAL for Enterprise Edition. CAL stands for client access license, which gives users access to services on a server. Microsoft's web site provides the cost of SQL Server 2008 licenses.
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 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.
Note Microsoft provides you with the option to purchase licenses per processor or per CAL for the Standard Edition.
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:
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.
Note Standard Edition for Small Businesses includes all features and functionality of the Standard Edition, but contains licenses for a business environment with less than 75 computers.
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.
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.
Note Microsoft provides you with the option to purchase licenses per processor or per CAL for the Workgroup Edition.
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:
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.
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:
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:
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.
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:
tempdb
?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.
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.
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.
Tip Strive to use comparable servers from your own environment. Your environment already supports the number of users, typical transactions per second, and the quality of servers that run your existing applications. Management is also accustomed to the cost of those servers. Trust your existing servers to provide a good baseline server speciation.
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:
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.
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:
Knowing about these increases, you can consider them in light of some additional requirements that you have pertaining to the new server:
Finally, you also know a couple of additional facts:
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.
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.
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.
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.
Note If you are not upgrading and do not have vendor specification documents, then do not worry. We have another method to show you after this one.
Starting with your baseline, here are some of the questions that you may want to ask:
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.
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:
Next, you must estimate the size of each nonclustered index upon the table:
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.
Note Detailed instructions on computing row, column, and index sizes can be found at http://msdn.microsoft.com/en-us/library/ms187445.aspx
.
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.
Tip If given a choice between small fast drives or the large slower drives, choose the small fast drives. Smaller faster drives outperform larger slower drives.
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.
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:
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, 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, 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 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.
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:
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.
tempdb
, and backups on separate physical drives.tempdb
drive to RAID 10, or set it to 1 if the budget does not permit 10.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.
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:
tempdb
files for every physical processor. (Multiple cores count as multiple processors, but hyper-threading technology does not.)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).
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:
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.
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:
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.
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:
But there's a cloud to go along with every silver lining. Drawbacks to consolidation include the following:
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:
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?
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.
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.
18.225.72.245