SQL Server consolidation pros and cons |
 |
| 18 Apr 2006 | SearchSQLServer.com |
|
|
The following checklist is excerpted from Chapter 1, The case for SQL Server consolidation, from of our expert e-book, "Consolidate SQL Servers for availability, scalability and cost savings." This chapter addresses how to determine if consolidation is right for you and critical considerations to ponder before taking on such a project. Click to download Chapter 1.
After reviewing the pros and cons of SQL Server and storage consolidation earlier in Chapter 1, there are several questions you must answer before you can determine whether it will work for you.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Checklist: Key consolidation questions to ask
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|  |
|
|
|
|
|
Do you need to optimize hardware resources?
Optimizing hardware resources means you are providing adequate resources for current and future SQL Server workloads. This is traditionally called sizing or capacity planning. It also means that your database objects are adequately tuned or optimized so that you get the most performance per dollar spent on hardware.
Vendors will help you with capacity planning. Naturally they will tend to sell you over-powered solutions, but this can work in your favor if you need room for future growth; you need a modular hardware solution that has headroom so you can plug in or remove resources as your workload changes; you feel a conservative margin of error never hurts; and you believe these machines can be repurposed or sold as the project completes its lifecycle.
Tuning and optimizing database objects for changing workloads are time-consuming tasks that are more art than science.
Tools such as Quest Software's Spotlight on SQL Server, BMC Software's Patrol or Symantec Corp.'s (formerly Veritas) i3 can help to identify which hardware and database objects are bottlenecks, as well as particular indexes or procedures that need tuning. Due to the time consuming nature of this task, proper baselining with an eye tuned for quick wins is a prudent strategy.
One of the benefits of consolidating SQL Server databases on a server with significant hardware resources is an overall increase in performance through parallel processing, and the fact that most workloads can take advantage of the 64-bit chipset.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Should you standardize on a single platform?
Good programming practices dictate that you should segregate development into tiers (i.e. a Web tier, middle tier and data tier), with an eye toward platform and vendor independence. However, in practice, each tier tends to be hand-tuned toward a particular vendor for performance reasons. As a consequence, porting from one vendor to another can be a major undertaking. Sybase's own SQL Server is one product that does port to Microsoft's SQL Server with a minimum of effort. However Sybase is entrenched on Unix platforms, which normally means some recoding on the middle tier.
You can also consolidate Microsoft SQL Server installations into single version, preferably SQL Server 2005. Microsoft has spent considerable resources to ensure that SQL Server 2000 will port flawlessly to SQL Server 2005, and there should be much fewer problems upgrading database applications if careful migration steps are taken. You also have the option of running your databases in SQL Server 7 or SQL Server 2000 compatibility mode on SQL Server 2005. If you do run into problems consolidating your application's database on SQL Server 2005 64-bit you may want to consolidate them onto a separate server running legacy 32-bit SQL Server 2000.
Another factor that may prevent you from consolidating onto a single DBMS vendor offering is that some IT departments like to diversify their platforms to prevent dependence on a single vendor. This corporate direction should not prevent the SQL Server consolidation effort because there are considerable benefits from this.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Do you want centralized database management?
Centralized management offers advantages such as efficient use of human resources, knowledge management, standards implementation and improved backup and recovery.
The most significant benefit is efficient use of human resources. After consolidation, you will be able to build DBA teams dedicated to specialty areas, including replication, backup and recovery, monitoring and tuning. By compartmentalizing by function, teams reap knowledge management benefits, gaining expertise in a particular area without having to get up to speed on different technologies. They can enforce standards that work best for them and the enterprise instead of implementing an uncoordinated set of procedures. Pain points will have higher visibility, and can be solved by carefully engineered solutions instead of quick fixes. Centralized management allows you to quickly create inventories of your SQL Server instances, and to implement standards and operating procedures for backups, restores, security and best operating practices.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Does your business plan call for a scalable environment?
Growth is the life blood of every industry. Your SQL Server environments must be able to keep up with business growth. A centralized environment allows for more dynamic allocation of resources as the resources are in a single location. Centralized management normally means highly modularized, and collecting information on them is a simple matter.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Do you need to reduce costs?
SQL Server consolidation can significantly reduce costs with careful planning. While there will be initial expenditures in purchasing clustered 64-bit hardware and licensing, there will be considerable freeing up or hardware resources and licenses which can be repurposed or sold. When making purchasing decisions you should carefully factor in headroom to account for growth to deliver a scalable solution. Consolidation also means more efficient use of DBAs by compartmentalizing functions.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Work on developing a standardized and centralized database environment. Read the complete chapter on considerations you must take to resolve SQL Server sprawl, and determine if consolidation is right for you. Download Chapter 1: The case for SQL Server consolidation.
About the author: Hilary Cotter has been involved in IT for more than 20 years a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and currently working on books on merge replication and Microsoft search technologies.
|
|
|
|
