SERVERS: DATABASE CONSOLIDATION ON DELL POWEREDGE R810 SERVERS *See hardware details on Page 3.
Table of contents Executive summary ........................................................3 New Dell technology makes major savings easy .................4 Features of the new Dell PowerEdge R810 ........................ 4 Features of the new Intel Xeon Processor 7500 series ........ 4 Features of Microsoft SQL Server 2008 R2 ........................ 5 The power of consolidation ............................................. 5 Running the numbers ......................................................
Servers: Database consolidation on Dell PowerEdge R810 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 R810 featuring the latest Intel Xeon Processor 7500 series can let organizations realize significant consolidation savings.
Servers: Database consolidation on Dell PowerEdge R810 servers payback period of less than 12 months and a 3-year return on investment (ROI) of 224 percent. New Dell technology makes major savings easy The new Dell PowerEdge R810 features the latest Intel Xeon Processor 7500 series. It allows organizations to consolidate unprecedented numbers of SQL server databases on to a single server, resulting in significant savings.
Servers: Database consolidation on Dell PowerEdge R810 servers Intel Xeon Processor 7500 series to a new level of power and scalability. QuickPath. QuickPath Technology provides fast access to the increased memory addressable by these processors. Turbo Boost. Turbo Boost Technology automatically allows processor cores to run faster than the base operating frequency if the server is operating below power, current, and temperature specification limits.
Servers: Database consolidation on Dell PowerEdge R810 servers In this database-specific Guide, we address the consolidation of multiple stand-alone SQL Server 2000 instances to a system powered by the latest Intel Xeon Processor 7500 series. As we will demonstrate, consolidating multiple instances to one physical machine saves on space, hardware costs, licensing costs, power, cooling, and administrative overhead.
Servers: Database consolidation on Dell PowerEdge R810 servers Among the characteristics to examine on each server are the following: Processor utilization Memory requirements of the operating system and applications Disk layout Database size Expected database growth Maximum concurrent users Types and rates of transactions against the databases The server you select for consolidation must do more than match the combined capacity of all the other current servers today.
Servers: Database consolidation on Dell PowerEdge R810 servers Microsoft SQL Server 2000. We measured how many of those workloads the new Dell PowerEdge R810 solution could host. The new server ran Microsoft Windows Server 2008 R2 and Microsoft SQL Server 2008 R2. We detail the test results in The Dell PowerEdge R810 performance story section of this Guide.
Servers: Database consolidation on Dell PowerEdge R810 servers Savings continue after the payback period. By the end of year one, we project savings of $7,715 with the Dell PowerEdge 810 solution.
Servers: Database consolidation on Dell PowerEdge R810 servers 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. We used four internal hard drives (73GB 15,000 RPM SCSI) that we configured in two RAID 1 volumes, installing the OS on one volume and using the other volume for SQL Server logs.
Servers: Database consolidation on Dell PowerEdge R810 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 2008 R2 instance, containing one database.
Servers: Database consolidation on Dell PowerEdge R810 servers Our testing results As Figure 3 shows, we were able to run 24 SQL Server 2008 R2 database workloads (six databases on four instances), all simultaneously achieving an average throughput greater than the HP ProLiant DL385 solution’s orders per minute score.
Servers: Database consolidation on Dell PowerEdge R810 servers As Figure 4 shows, the combined OPM of 24 SQL Server database workloads on the Dell PowerEdge R810 solution were over 27 times greater than the single HP ProLiant DL385 solution database workload. Total orders per minute: 1 HP ProLiant DL385 solution vs. 24 workloads on 1 Dell PowerEdge R810 solution 200,000 Orders per minute 150,000 100,000 50,000 0 HP ProLiant DL385 solution Dell PowerEdge R810 solution Figure 4.
Servers: Database consolidation on Dell PowerEdge R810 servers Idle power usage: 24 HP ProLiant DL385 solutions vs. 1 Dell PowerEdge R810 solution 12,000 10,000 Watts 8,000 6,000 4,000 2,000 0 24 HP ProLiant DL385 solutions 1 Dell PowerEdge R810 solution Figure 5. Simulated idle power savings – 24 HP ProLiant DL385 solutions versus a single Dell PowerEdge R810 solution. Lower idle power is better.
Servers: Database consolidation on Dell PowerEdge R810 servers Appendix B.
Servers: Database consolidation on Dell PowerEdge R810 servers will be used strictly for iSCSI traffic. Use all three NICs on the Dell EqualLogic PS5000XV and three of the four NICs on the Dell PowerEdge R810. Configure the Dell PowerEdge R810 NICs to be used for iSCSI traffic, assigning each a valid IP address and subnet mask. Configure jumbo frames network optimizations on both the network switch and the NIC configuration inside Windows Server 2008 R2 if necessary.
Servers: Database consolidation on Dell PowerEdge R810 servers Figure 6. SQL Server 2008 R2 Installation Center options. Proceed through the first several installation steps, entering license information, and installing prerequisites with default options specified. On the Setup Role screen, choose a SQL Server Feature Installation. On the Feature Selection screen, select only what you need for your particular configuration. (See Figure 7.
Servers: Database consolidation on Dell PowerEdge R810 servers Figure 7. Choosing features for installation. On the first installation, choose to install the default instance. On subsequent installations on the same server, choose named instance and provide a name for the instance. Configure the credentials of the SQL Server service account and SQL Server Agent accounts. Specify SQL Server administrators and also specify the authentication mode desired for your configuration.
Servers: Database consolidation on Dell PowerEdge R810 servers instance of the database engine, you need only check the Database Engine checkbox. The instances share the workstation components, so you don’t need to install those components a second time. The second and more important change in the installation procedure for a named instance is on the Instance Configuration screen. Here you must choose the Named Instance option and supply a name for the instance.
Servers: Database consolidation on Dell PowerEdge R810 servers 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. This section provides a brief walkthrough; Appendix E gives more detailed instructions. 1. Select Start | All Programs | Microsoft SQL Server 2008 R2 November CTP | SQL Server 2008 R2 Upgrade Advisor.
Servers: Database consolidation on Dell PowerEdge R810 servers 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. 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 R810 servers We performed all SQL Server 2000 administration using Query Analyzer and Enterprise Manager, the two main tools in the SQL Server 2000 environment. Likewise, we performed all administration for the SQL Server 2008 R2 installation using SQL Server Management Studio, the main administration interface for SQL Server 2008 R2.
Servers: Database consolidation on Dell PowerEdge R810 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 R810 servers take some extra steps to ensure a smooth migration for SQL Server logins. 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.
Servers: Database consolidation on Dell PowerEdge R810 servers stored procedure, to view orphaned users and to map a user to a login. Appendix H provides an example of this process. 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.
Servers: Database consolidation on Dell PowerEdge R810 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 R810 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 R810 servers Acquisition costs include the purchase price of the server and storage hardware for the Dell PowerEdge R810 solution as well as the labor costs to plan and carry out the migration. Operating costs include hardware support costs, software licenses and support agreements, facility costs for space and data center ports, energy costs, and management costs. We assume operating costs are the same for each of the 3 years.
Servers: Database consolidation on Dell PowerEdge R810 servers Five benefits of the Dell PowerEdge R810 solution drive the cost savings for this solution: Replaces up to 24 HP ProLiant DL385 servers and 12 HP StorageWorks MSA30 storage enclosures. The increased capabilities of the Dell PowerEdge R810 solution enable consolidation of multiple older systems for the specific workload we tested. Fewer servers and storage arrays lower system administration labor costs.
Servers: Database consolidation on Dell PowerEdge R810 servers Acquisition costs Figure 13 details the acquisition costs.
Servers: Database consolidation on Dell PowerEdge R810 servers Costs for 1 Dell PowerEdge R810 solution Savings category Hardware support Servers and storage arrays Software costs Microsoft Windows Server and Microsoft SQL Server Facility costs Rack space and port costs Energy costs Power and cooling Management costs Labor for server and storage array administration Total Costs for 24 HP ProLiant DL385 solutions 3-year savings for Dell PowerEdge R810 solution $9,660 $43,848 $34,188 $20,820 $54,072
Servers: Database consolidation on Dell PowerEdge R810 servers Assumptions We made many assumptions about the hypothetical enterprise that drive the costs in this analysis. Different assumptions would create a different cost picture.
Servers: Database consolidation on Dell PowerEdge R810 servers 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 R810 solution and software assurance agreement for that software. The enterprise already owned the necessary Client Access Licenses (CALs).
Servers: Database consolidation on Dell PowerEdge R810 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. 2. 3. Hardware level a. CPU i. Vendor ii. Model iii. Number of cores per socket b. Memory i.
Servers: Database consolidation on Dell PowerEdge R810 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 R810 servers Appendix C. Preparing the storage For the testing we conducted for this Guide, we used two Dell EqualLogic iSCSI SAN arrays, connected to our server with a Dell PowerConnect 5448 Ethernet switch. On the storage, switch, and server we enabled jumbo frames on all NICs and followed other guidelines provided by Dell regarding best practices for iSCSI storage. Below we address the specifics of setting up your iSCSI storage.
Servers: Database consolidation on Dell PowerEdge R810 servers the specifics on configuring the internal storage, and then connecting to the iSCSI storage from Windows Server 2008 R2. Configuring the internal storage 1. 2. 3. 4. 5. 6. On boot, Press Ctrl+R to enter the RAID controller BIOS. Our controller was a PERC H700. Highlight the controller, press F2, then choose to create the new virtual disk. Provide the desired RAID setting, such as RAID 1, 5, 10, and so on.
Servers: Database consolidation on Dell PowerEdge R810 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 R810 servers 15. At the Server Configuration screen, choose the service account, preferably an Active Directory domain account, fill in a password if necessary, and click Next. 16. At the Database Engine Configuration screen, choose an authentication mode. If your legacy servers use SQL Server logins at all, then select Mixed Mode. If you exclusively use Active Directory domain accounts in your SQL Server environment, then choose Windows Authentication. 17.
Servers: Database consolidation on Dell PowerEdge R810 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. 2. 3. 4. 5. 6. 7. 8. Insert the SQL Server 2008 R2 DVD. On the Planning screen, click Install SQL Server Upgrade Advisor. Click Next to begin the installation wizard, accept the licensing terms, and click Next.
Servers: Database consolidation on Dell PowerEdge R810 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 R810 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 R810 servers folder, and enter a filename, such as DatabaseNameMigrationBackup.bak. 13. Click OK to close the Backup Device Location window, and then 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 R810 servers 26. Click Add to add the backup file location. Browse to the appropriate folder, find the backup file, select it, and click OK. Then 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 R810 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. 2. 3. 4. 5. 6. 7. 8. 9.
Servers: Database consolidation on Dell PowerEdge R810 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. 2. 3. 4. 5. 6. 7. 8. 9.
Servers: Database consolidation on Dell PowerEdge R810 servers 4. Enter the following command, and click Execute: EXEC sp_change_users_login 'Report'; 5. 6. 7. For each user output from the above step, map these ―orphaned‖ users to the appropriate matching SQL Server login by taking the following steps. Open a query window on the SQL Server 2008 R2 server using steps 1 through 3 above.
Servers: Database consolidation on Dell PowerEdge R810 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.
