SERVERS: DATABASE CONSOLIDATION ON DELL POWEREDGE R910 SERVERS *See hardware details on Page 3. A Principled Technologies report commissioned by Dell Inc.
Table of contents Executive summary ........................................................3 New Dell technology makes major savings easy .................4 Features of the new Dell PowerEdge R910 ........................ 4 Features of the new Intel Xeon Processor 7500 series ........ 4 Features of the new Dell PowerVault MD3220 ................... 5 Features of Microsoft SQL Server 2008 R2 ........................ 6 The power of consolidation .............................................
Servers: Database consolidation on Dell PowerEdge R910 servers Executive summary Advances in processor, server, and database technologies enable enterprises to reduce costs by consolidating multiple older databases onto a single newer, faster server. The new Dell PowerEdge R910 featuring the latest Intel Xeon Processor 7500 series and the new Dell PowerVault MD3220 storage array (which we refer to as the Dell PowerEdge R910 solution) can let organizations realize significant consolidation savings.
Servers: Database consolidation on Dell PowerEdge R910 servers and decreases power consumption by over 91.5 percent. The result is an estimated payback period of less than 6 months and a 3-year return on investment (ROI) of 439 percent. New Dell technology makes major savings easy The new Dell PowerEdge R910 features the latest Intel Xeon Processor 7500 series. It allows organizations to consolidate unprecedented numbers of SQL Server databases onto a single server, resulting in significant savings.
Servers: Database consolidation on Dell PowerEdge R910 servers the expansion from two-socket (2S) to four-socket (4S) platforms, elevates the Intel Xeon Processor 7500 series to a new level of power and scalability. QuickPath. Intel QuickPath Technology provides fast access to the increased memory addressable by these processors. Turbo Boost Technology.
Servers: Database consolidation on Dell PowerEdge R910 servers Features of Microsoft SQL Server 2008 R2 SQL Server 2008 R2 is the latest release of Microsoft’s database management platform. As with each release, Microsoft has added new features to expand on the capabilities of their Database Management System (DBMS) platform.
Servers: Database consolidation on Dell PowerEdge R910 servers hardware utilization—all while maintaining the previous level of application performance. Consolidation can also yield a variety of cost savings: Hardware savings. Buying, powering, and supporting fewer servers brings obvious savings. Other potential hardware cost savings include the need for fewer racks and network switches: as the number of servers decreases, these costs decrease as well. Software license savings.
Servers: Database consolidation on Dell PowerEdge R910 servers Types and rates of transactions against the databases The server you select for consolidation must do more than match the combined capacity of your older servers today. It must also have enough excess capacity to still perform well at the end of its expected life span. The Dell PowerEdge R910 we tested for this Guide is a highperformance server that can readily support significant consolidation.
Servers: Database consolidation on Dell PowerEdge R910 servers This consolidation could deliver payback in less than 6 months and an ROI of 439 percent after 3 years. Dell PowerEdge R910 solution pays back the initial investment in under 6 months $900,000 Cumulative costs $800,000 $700,000 $600,000 $500,000 $400,000 $300,000 $200,000 $124,653 acquisition costs $100,000 $140,938 savings at 1 year $672,121 savings at 3 years Payback in 5.
Servers: Database consolidation on Dell PowerEdge R910 servers The Dell PowerEdge R910 solution uses a little less than 1/12th the power, 1/48th the Microsoft SQL Server licenses, 1/12th the Microsoft Windows Server licenses, and about 1/14th the data center rack space of the 48 HP ProLiant DL385 solutions, and requires less administrator time to manage fewer servers and storage arrays. See Appendix A for more information on these savings and calculations.
Servers: Database consolidation on Dell PowerEdge R910 servers which we report in this Guide. For more details about the DS2 tool, see http://www.delltechcenter.com/page/DVD+Store. Our test bed setup On the HP ProLiant DL385 server running Windows Server 2003 R2 with SP2 and one instance of SQL Server 2000, we used 4 GB of RAM, allocating 3 GB of RAM to user processes via the boot.ini /3GB switch as was typical in a server of its time.
Servers: Database consolidation on Dell PowerEdge R910 servers Figure 2. Consolidated SQL Server 2008 R2 instance layout for our testing. For client machines, we used desktop systems running Windows Server 2003 R2 with SP2. Each of these client machines ran the DS2 workload, which spawned 32 threads against each database workload and ran with no think time. On the HP ProLiant DL385 server, we used one client and one SQL Server 2000 instance, containing one database.
Servers: Database consolidation on Dell PowerEdge R910 servers greater than the HP ProLiant DL385 solution’s orders per minute score. The average OPM delivered by each of the 48 workloads on the Dell PowerEdge R910 solution was 6,985, while the average OPM delivered by a single workload on the HP ProLiant DL385 solution was 6,639. Orders per minute per workload 7,000 6,000 OPM 5,000 4,000 3,000 2,000 1,000 0 1 HP ProLiant DL385 workload Dell PowerEdge R910 (average of 48 workloads) Figure 3.
Servers: Database consolidation on Dell PowerEdge R910 servers Total orders per minute: 1 HP ProLiant DL385 solution vs. 24 workloads on 1 Dell PowerEdge R910 solution OPM 400,000 300,000 200,000 100,000 0 HP ProLiant DL385 solution Dell PowerEdge R910 solution Figure 4. Combined orders per minute of 48 database workloads on the Dell PowerEdge R910 solution versus a single HP ProLiant DL385 solution database workload. Greater OPM is better.
Servers: Database consolidation on Dell PowerEdge R910 servers Idle power usage: 48 HP ProLiant DL385 solutions vs. 1 Dell PowerEdge R910 solution 25,000 Watts 20,000 15,000 10,000 5,000 0 48 HP ProLiant DL385 solutions 1 Dell PowerEdge R910 solution Figure 5. Simulated idle power savings – 48 HP ProLiant DL385 solutions versus a single Dell PowerEdge R910 solution. Lower idle power is better.
Servers: Database consolidation on Dell PowerEdge R910 servers Number of logins on this SQL Server instance, and what type of logins these are (Windows or SQL) Current backup strategy and schedule for the databases on this server Replication details for this SQL instance, if any Detailed information regarding permissions and roles SQL Agent jobs on this SQL Server After moving your databases to their new SQL Server instance, you must make sure that any system or application using the database h
Servers: Database consolidation on Dell PowerEdge R910 servers Installing SQL Server 2008 For this Guide, we assume that Windows Server 2008 R2 is installed on the server. Although not required, you should, when possible, deploy SQL Server 2008 R2 on a member server in an Active Directory domain. Do not make the SQL Server service domain accounts members of the Domain Administrators group.
Servers: Database consolidation on Dell PowerEdge R910 servers options specified. On the Setup Role screen, choose a SQL Server Feature Installation. 5. On the Feature Selection screen, select only what you need for your particular configuration. (See Figure 7.) In our case, we chose the Database Engine with Full-Text search and the management tools. Figure 7. Choosing features for installation. 6. On the first installation, choose to install the default instance.
Servers: Database consolidation on Dell PowerEdge R910 servers To install one or more named instances, as we did for our testing, follow the same procedures as installing a default instance, with the exception of a few configuration changes during the process. First, on the Feature Selection screen, if you are installing a named instance of the database engine, you need only check the Database Engine checkbox.
Servers: Database consolidation on Dell PowerEdge R910 servers 2. Click Next to begin the installation wizard, accept the licensing terms, and click Next. 3. Click Next to accept the default Registration information, click Next to accept the default installation path, and click Next to begin the installation. Once you have installed the SQL Server 2008 R2 Upgrade Advisor, you can use this software to scan your SQL Server 2000 instances for potential migration issues.
Servers: Database consolidation on Dell PowerEdge R910 servers 4. Provide proper authentication to SQL Server 2000, and select the database you want to analyze. If necessary, you can also input SQL script files and trace files here. 5. You may have legacy Data Transformation Services (DTS) packages on the SQL Server 2000 server. The DTS packages may be present in the file system or in the database itself.
Servers: Database consolidation on Dell PowerEdge R910 servers administration for the SQL Server 2008 R2 installation using SQL Server Management Studio, the main administration interface for SQL Server 2008 R2. NOTE: Be aware that various methods exist for migrating databases between instances and versions of SQL Server, including detach/attach, backup/restore, and the copy database wizard. We chose to use the backup/restore method.
Servers: Database consolidation on Dell PowerEdge R910 servers NOTE: If the time a full backup requires is unacceptable given your migration maintenance schedule, you can take the full backup at a previously scheduled time, and at this point of the migration take only a differential or transaction log backup. If the transaction log size is small relative to the data file size, this approach can decrease migration time significantly.
Servers: Database consolidation on Dell PowerEdge R910 servers Below, we describe how to script both Windows authenticated logins and SQL Server authenticated logins to a query window and recreate them on the new SQL Server 2008 R2 server. Step 6 refers solely to the SQL Server login type. Please see Appendix G for details on transferring Windows logins and Appendix H for details on transferring SQL Server logins. To transfer logins, take the following steps on the SQL Server 2008 R2 server.
Servers: Database consolidation on Dell PowerEdge R910 servers SQL Server Agent jobs Almost all installations schedule SQL Server Agent jobs that run against their databases. You must migrate these jobs to your new SQL Server 2008 R2 server. Locate the jobs you need to migrate in SQL Server Management Studio (on your SQL Server 2000 server) under the SQL Server Agent, right-click them, and choose Script Job As | Create To | New Query Window.
Servers: Database consolidation on Dell PowerEdge R910 servers Appendix A. Return on investment This section estimates the return on investment and expected payback period for a hypothetical enterprise that plans to consolidate SQL Server databases from multiple legacy servers and storage solutions onto a single newer, more powerful Dell serverand-storage solution.
Servers: Database consolidation on Dell PowerEdge R910 servers Power savings We measured power to the test servers and storage when idle and while running the benchmark under peak load. We measured energy usage of the HP StorageWorks MSA30 with both a full enclosure and a half enclosure and use the full enclosure results for this ROI analysis. We averaged the idle and peak load results to estimate a power usage under typical load for the test configurations.
Servers: Database consolidation on Dell PowerEdge R910 servers offsetting its acquisition costs by major savings in operating costs and delivered a significant ROI over 3 years. Acquisition costs include the purchase price of the server and storage hardware for the Dell PowerEdge R910 solution as well as the labor costs to plan and carry out the migration.
Servers: Database consolidation on Dell PowerEdge R910 servers Figure 12. Estimated 3-year costs for the 48 HP ProLiant DL385 solutions and a single Dell PowerEdge R910 solution. Lower costs are better. Five benefits of the Dell PowerEdge R910 solution drive the cost savings for this solution: Replaces up to 48 HP ProLiant DL385 servers and 24 HP StorageWorks MSA30 storage enclosures.
Servers: Database consolidation on Dell PowerEdge R910 servers Acquisition costs Figure 13 details the acquisition costs for the Dell PowerEdge R910 solution.
Servers: Database consolidation on Dell PowerEdge R910 servers Savings category Costs for 1 Dell PowerEdge R910 solution Costs for 48 HP ProLiant DL385 solutions 3-year savings for Dell PowerEdge R910 solution $6,807 $117,792 $110,985 $24,036 $108,144 $84,108 $1,179 $29,664 $28,485 $9,144 $69,840 $60,696 $27,500 $540,000 $512,500 $68,666 $865,440 $796,774 Hardware support Servers and storage arrays Software costs Microsoft Windows Server and Microsoft SQL Server Facility costs Rack sp
Servers: Database consolidation on Dell PowerEdge R910 servers Assumptions We made many assumptions about the hypothetical enterprise that drives the costs in this analysis. Different assumptions would create a different cost picture. We made the following assumptions in estimating acquisition costs: The enterprise would dedicate the entire newer Dell PowerEdge R910 server and the Dell PowerVault MD3220 array to the tasks carried out by the 48 HP ProLiant DL385 solutions it was replacing.
Servers: Database consolidation on Dell PowerEdge R910 servers purchasing one license and software assurance agreement for each processor. The enterprise saved the cost of the freed up software assurance agreements. These agreements were due for renewal at the time of the solution acquisition. The enterprise purchased a SQL Server 2008 R2 Enterprise Edition per server license for the Dell PowerEdge R910 solution and software assurance agreement for that software.
Servers: Database consolidation on Dell PowerEdge R910 servers Appendix B. Example database survey In this appendix, we provide an example survey of detailed information you may want to gather about the SQL Server instances and databases you’re targeting for consolidation. While this survey is a good starting point, it may not contain every relevant detail for your particular environment. 1. Hardware level a. CPU i. Vendor ii. Model iii. Number of cores per socket b. Memory i. Total quantity in GB ii.
Servers: Database consolidation on Dell PowerEdge R910 servers 4. Database level a. Database properties and options b. Recovery model, auto-shrink, etc. c. Files and filegroups (size and location) d. Backups i. How many full backups, and on what schedule? ii. How many differential backups, and on what schedule? iii. How many log backups, and on what schedule? iv. Current backup window? v. Can backup window shift to accommodate the consolidated backup window? e. Other database issues i.
Servers: Database consolidation on Dell PowerEdge R910 servers Appendix C. Preparing the storage For the testing we conducted for this Guide, we used one Dell PowerVault MD3220 array with three Dell PowerVault MD1220 arrays attached, which we connected to our server directly using two Dell 6Gbps SAS Adapters with two cables connected to each adapter. Below are the specifics of setting up your Dell PowerVault MD3220. Setting up the storage 1.
Servers: Database consolidation on Dell PowerEdge R910 servers 5. Highlighting the new virtual disk, press F2, Initialization, and Start Init. This will fully initialize your virtual disk 6. Repeat steps 2 through 5 for the remaining virtual disks in your server. Configuring the storage in Microsoft Windows Server 2008 R2 1. Choose Storage, and choose Disk Management. 2. Right-click the uninitialized external volume, choose Initialize Disk, and choose MBR. 3.
Servers: Database consolidation on Dell PowerEdge R910 servers Appendix D. Installing SQL Server 2008 Install an instance of Microsoft SQL Server 2008 R2 by following these steps. This installation walkthrough only covers the installation of the Database Engine and Management Components. For other components, such as Reporting Services, Integration Services, or Analysis Services, see Microsoft documentation at http://msdn.microsoft.com/enus/library/ms143219(SQL.105).aspx.
Servers: Database consolidation on Dell PowerEdge R910 servers Active Directory domain accounts in your SQL Server environment, then choose Windows Authentication. 18.If necessary, enter a password for the system administrator (SA) account, click Add Current User, and click Next. 19.At the Error Reporting screen, click Next. 20.At the Installation Configuration Rules screen, click Next. 21.At the Installation screen, click Install. 22.At the Complete screen, click Close. 23.
Servers: Database consolidation on Dell PowerEdge R910 servers Appendix E. Installing Upgrade Advisor In this example, we walk through the steps to install and run the SQL Server 2008 R2 Upgrade Advisor and save reports. 1. Insert the SQL Server 2008 R2 DVD. On the Planning screen, click Install SQL Server Upgrade Advisor. 2. Click Next to begin the installation wizard, accept the licensing terms, and click Next. 3.
Servers: Database consolidation on Dell PowerEdge R910 servers 10.On the SQL Server parameters screen, select one or more databases and, if you so desire, supply a trace file or SQL script file to analyze. Should you wish to use a trace file, you must generate it before you begin the Upgrade Advisor. For instructions about how to generate a trace file, see http://msdn2.microsoft.com/enus/library/ms187929(SQL.105).aspx 11.
Servers: Database consolidation on Dell PowerEdge R910 servers Appendix F. Migrating databases In this appendix, we give detailed instructions about a basic sideby-side migration of a user database from a SQL Server 2000 server to a SQL Server 2008 R2 server. There are multiple methods of accomplishing this task, including using TSQL commands and automated tools.
Servers: Database consolidation on Dell PowerEdge R910 servers and enter a filename, such as DatabaseNameMigrationBackup.bak. 13.Click OK to close the Backup Device Location window, and click OK to close the Select Backup Destination window. NOTE: To simplify moving the database later, we recommend creating the backup in a shared local folder. 14.Optionally, on the Options tab, select the checkbox to Verify your database backup upon completion.
Servers: Database consolidation on Dell PowerEdge R910 servers 26.Click Add to add the backup file location. Browse to the appropriate folder, find the backup file, select it, and click OK. Click OK to return to the Restore Database window. 27.Select the checkbox that now appears in the Restore column. Optionally, click Options on the left, and ensure the file locations are appropriate in the Restore As column. 28.Click OK to begin the restore.
Servers: Database consolidation on Dell PowerEdge R910 servers Appendix G. Transferring Windows logins In this appendix, we provide detailed instructions for migrating transferring Windows-based logins from a SQL Server 2000 server to SQL Server 2008 R2 in an Active Directory environment. For information about transferring logins when using SQL Server authentication, see Appendix H. 1.
Servers: Database consolidation on Dell PowerEdge R910 servers Appendix H. Transferring SQL Server logins In this appendix, we give detailed instructions on transferring SQL Server logins from a SQL Server 2000 server to SQL Server 2008 R2. For information about transferring logins when using Windows authentication, see Appendix G. Transferring logins 1.
Servers: Database consolidation on Dell PowerEdge R910 servers 4. Enter the following command, and click Execute: EXEC sp_change_users_login 'Report'; 5. For each user output from the above step, map these ―orphaned‖ users to the appropriate matching SQL Server login by taking the following steps: a. Open a query window on the SQL Server 2008 R2 server using steps 1 through 3 above. b.
Servers: Database consolidation on Dell PowerEdge R910 servers About Principled Technologies We provide industry-leading technology assessment and fact-based marketing services. We bring to every assignment extensive experience with and expertise in all aspects of technology testing and analysis, from researching new technologies, to developing new methodologies, to testing with existing and new tools. When the assessment is complete, we know how to present the results to a broad range of target audiences.