The case for SQL Server consolidation.pdf

Document Sample
The case for SQL Server consolidation.pdf Powered By Docstoc
					  SearchSQLServer.com


Consolidate SQL Servers for availability, scalability and cost savings


                        chapter 1
 The case for                                                   INSIDE

        SQL Server                                          • 02 Defining
                                                                 consolidation


Many SQL Server consolidation                               • 03 Causes of SQL
                                                                 Server sprawl
shops suffer from                                           • 05 Pitfalls of SQL
uncoordinated                                                    Server sprawl

installations,                                              • 08 How consolidation
wasted hardware                                                  can help

and licenses, lack                                          • 09 What to
of standards and                                                 consolidate:
                                                                 Servers and
security holes.                                                  storage
Can consolidation                                           • 5 Key consolidation
help?                                                            questions to ask



                                                                                     
Sponsored by
                                                                                     3
chapter 1



The case for SQL                                                                                “Consolidation
      Server consolidation                                                                                 is the
                                                                                                  combination
                 By Hilary Cotter                                                                of SQL Server
                                                                                                     databases




T
                                                                                                    running on
                                                                                                       different
                                                                                                      machines
            he number of deployed            versions that contain simi-                            — typically
            instances of Microsoft           lar feature sets priced to help                    geographically
            SQL Server in the enter-         build community. For example,                        dispersed —
            prise continues to grow          the Developer version of SQL
            more rapidly than the            Server has the same feature
                                                                                                 onto a smaller
            rollouts for any other           set as the Enterprise version. In                       number of
            database management              addition, Microsoft launched                       more powerful
     system (DBMS). The reasons              evangelistic efforts to build a                          machines
     are clear; Microsoft set out            community around the DBMS.                             in a central
     to provide a powerful DBMS                 These attributes and the con-                         location.”


                                                                                                s
     that meets multiple goals for           sequent popularity of SQL Serv-
     IT organizations. SQL Server            er have resulted in an explosion
     is designed to deliver scalable         of installations in the corporate
                                                                                                  SQL Server’s
     solutions while offering ease           environment. This phenomenon
                                                                                                  popularity has
     of use and deployment, self             is frequently described as SQL                       contributed to
     maintenance and tuning, low             Server sprawl, although it is                        an installation
     resource overhead, and low skill        sometimes disparagingly called                       boom known
     set requirements.                       an infestation.                                      as SQL Server
        Microsoft provides product              There’s a downside to this                        sprawl.
                                                                                                                    2

    Definition           SQL Server sprawl       Consolidation benefits   Servers and storage      Questions
                                                                                                                    73
chapter 1 The case for SQL Server consolidation



     boom. It has led to uncoor-             Causes of SQL Server sprawl
     dinated installations, wasted
     hardware and licenses, lack of          Large numbers of SQL Server               for shrink-wrapped software
     standards in an enterprise, and         installations frequently waste            thanks to its ease of deploy-
     security holes.                         hardware resources and licens-            ment, availability through the
        These problems can be                ing fees, and ultimately turn             free distribution of Microsoft
     mitigated by SQL Server                 unmanageable. Yet SQL Serv-               SQL Server Desktop Engine
     consolidation. Consolidation            er’s strengths are among the              (MSDE) or SQL Server 2005
     is the combination of SQL               root causes of this sprawl: It’s          Express, and community sup-
     Server databases running on             often just easier to add more             port for code development. The
     different machines — typically          instances of SQL Server than to           Microsoft platform is strongly
     geographically dispersed                be proactive about managing               biased to using SQL Server for
     — onto a smaller number of              the problem. Here I’ll explain            the DBMS. SQL Server also
     more powerful machines in               why:                                      tends to be the simplest to de-
     a central location. The result                                                    velop on, has the largest com-
     is greater performance and              ease of installation: For a               munity surrounding it, features
     reliability, particularly on 64-        successful SQL Server instal-             the best support, and offers the
     bit SQL Server 2005, which              lation, you are only required to          greatest number of books on
     provides overall improved               put the CD-ROM in the drive               every facet of development.
     performance and better                  and accept all defaults in the
     memory management than its              subsequent dialogs. These                 brand loyalty: SQL Server
     SQL Server 2000 counterpart.            dialogs are intuitive for even the        is often chosen as a depart-
                                             most junior database adminis-             mental DBMS because it car-
                                             trator (DBA), network adminis-            ries the Microsoft brand, and
For many shops                               trator or non-technical person.           Microsoft products are already
it’s “all Microsoft                                                                    entrenched in the department.
                                             community: SQL Server is                  Employees are familiar with
all the time.”                               often the DMBS of choice                  using Microsoft products, they
                                                                                                                          3

     Definition          SQL Server sprawl       Consolidation benefits    Servers and storage          Questions
                                                                                                                          73
chapter 1 The case for SQL Server consolidation


                                                                                             Can SQL Server
     know how to navigate Micro-             total cost of ownership. Howev-                sprawl be blamed
     soft support options and they           er, the reality is that most shops              on SQL Server’s
     feel comfortable with the look          supporting a variety of plat-                     strengths?
     and feel of Microsoft prod-             forms have more DBAs manag-
     ucts. For many shops it is “all         ing the other vendors’ products,
     Microsoft all the time.” While
     other DBMSs do offer ports to
                                             even for a comparable number
                                             of servers, than they have DBAs
                                                                                       pServer instances can
                                                                                        SQL
                                                                                        be added easily by even the
     the Windows platform there              managing SQL Servers.                          most junior DBA.
     is a general bias against them
     because they are considered to          familiarity: The number of
     be outside of the department’s          people familiar with SQL Server
                                                                                       pServer available
                                                                                        SQL
                                                                                        deploy and
                                                                                                   is easy to

     comfort level.                          is growing exponentially due to                through MSDE.
                                             the fact that MSDE is shipping
     self maintenance and self               with every version of Office
     tuning: SQL Server is largely           and Visual Studio. Microsoft’s            p to required for
                                                                                        Little no administrative
                                                                                        input is
     self maintaining and self tuning,       mantra is that these products
                                                                                            maintenance and tuning.
     requiring little or no adminis-         are “better together,” while
     trative input. This adds to the         the reality is “lock yourself
     favorable impressions of SQL            in; you’ll love it,” according to         pServer’scompared to
                                                                                        SQL
                                                                                        is attractive
                                                                                                      pricing model
     Server, and increases the com-          InfoWorld’s Tom Yager (thanks
     fort level for department man-          to PASS President Kevin Kline for              other DBMSs.
     agers. Managers want simple             pointing out this nugget). This
     lives without complexity. Some          broad familiarity will only in-           p entrenched are
                                                                                        Microsoft products
                                                                                        already            in
     DBMSs are notorious for their           crease now that there is a free
     support overhead and difficulty.        version of SQL Server 2005                     many departments and
     Figures are hard to come by,            — namely SQL Server Express.                   work “better together.”
     and each vendor boasts how              This may replace MySQL as
     their product offers the lowest         the platform of choice for open
                                                                                                                      

     Definition          SQL Server sprawl       Consolidation benefits    Servers and storage           Questions
                                                                                                                      73
chapter 1 The case for SQL Server consolidation



     source development and in aca-         Pitfalls of SQL Server sprawl
     demic communities. Computer
     science students soon will be
                                            The boom in uncontrolled and uncoordinated SQL
     graduating with SQL Server             Server deployments has led to numerous challenges
     experience under their belts.
                                            for the enterprise.
     low cost: SQL Server is
     competitively priced as one of         let’s start with wasted                 patching large numbers of SQL
     the lowest of all DBMSs — the          hardware resources. In                  Server installations distributed
     pricing model makes it very            many departmental imple-                over a LAN or WAN. These
     attractive.                            mentations of SQL Server the            problems are exacerbated
                                            hardware is grossly oversized           by the fact that DBAs or net-
                                            for the workloads. Sometimes            work administrators may not
                                            excess hardware is pushed by            have such base information
“SQL Server sprawl                          overzealous hardware vendors;           as who administers individual
 spells headaches for                       and sometimes the department            machines, the administrator
                                            overestimates its workload or           passwords or the machines’ lo-
 managers when it                           growth. You may also find that          cations. They may not have ne-
 comes to maintain-                         the workload would have been            gotiated a maintenance window
 ing or patching a large                    more appropriate for a desk-            with the clients or know the
                                            top PC. Checking CPU usage              consequences of the servers
 number of installa-                        reveals that these SQL Server           going off line. Consider the case
 tions distributed over                     configurations seldom consume           where a virus targeting SQL
 a LAN or WAN.”                             more than 5 to 10% CPU utili-           Server hits your network, and
                                            zation.                                 you need to patch all of your
                                               SQL Server sprawl spells             SQL Servers immediately. Lo-
                                            headaches for managers when             cating them and patching them
                                            it comes to maintaining or              is a challenge when there is
                                                                                                                        5

     Definition         SQL Server sprawl      Consolidation benefits   Servers and storage          Questions
                                                                                                                        73
chapter 1 The case for SQL Server consolidation



     no central point of administra-           Standardization suffers when            SQL Servers can consume large
     tion. This becomes even more            SQL Server implementations                amounts of a DBA’s time trying
     problematic with developer              get out of hand. With SQL                 to recover from failures if en-
     machines when many of them              Server 7 it was common to have            terprise-level disaster recovery
     are running MSDE, SQL Server            SQL Servers using mixed-mode              plans don’t factor in a depart-
     Express or SQL Server Personal          authentication with a blank               ment’s reduced availability re-
     Edition.                                “sa” password. This was not               quirements. Such firefighting is
        Individual SQL Servers in            only a security fault, but Trojans        an ineffective use of the DBA’s
     such dispersed environments             could scan a network looking              workday. Unless rigorous plan-
     are poorly managed. For exam-           for such unsecured machines               ning is done to ensure point-
     ple, they may not have the lat-         and launch exploits on them.              in-time backups have been
     est service pack or patch level,        A lack of standards can cause             completed and verified, there
     or may be two versions out of           other problems, such as the               may be partial or complete
     sync. This leads to two prob-           lack of backup procedures or              data loss. Part of this equation
     lems. First, the version might be       transaction log maintenance, or           entails frequently moving the
     scheduled to retire from active         unchecked code, which might               backups off the machine to a
     support; and, second, the DBA           leave SQL Servers prone to SQL            vault, preferably offsite. If copy-
     team may not be familiar with           injection attacks. Microsoft              ing the dumps to tapes is part
     the version. That means that it         Baseline Security Analyzer does           of this solution, the tapes must
     takes the DBA longer to fix the         help to identify servers with             be tested and archived as they
     problem, and it takes him away          security vulnerabilities.                 age and outlive their useful life
     from other essential tasks at             Smaller, departmental SQL               span. Remember that tapes
     hand. In addition, gaps in man-         Servers do not have high avail-           received a bad name for some
     agement may mean that index-            ability requirements, so an               companies affected by the Sept.
     es are not maintained, which            uptime of 9-to-5, Monday-                 11 terrorist attacks when they
     hampers overall performance             through-Friday is sufficient for          found the tape backups to be
     for applications using the data-        most database applications.               unusable.
     base.                                   However, these departmental                  The dispersed server also
                                                                                                                             

     Definition          SQL Server sprawl       Consolidation benefits    Servers and storage            Questions
                                                                                                                             73
chapter 1 The case for SQL Server consolidation



     may house the little applica-
     tion that grew. This is an ap-              Choosing your SQL Server licensing mode
     plication that was written by
     an amateur or someone with a             per-seat licensing: Best when you have a small, known number
     lower skill set, and has become          of clients connecting to SQL Server.
     critical despite its shortcom-
     ings. While this application may         per-processor licensing: Best if you have a large number of
     have initially performed well,           users connecting to SQL Server.
     it simply is not scalable. Solv-
     ing one bottleneck only reveals
     another. This application may           your SQL Server. Per-processor             licenses with five client access
     require more maintenance than           licensing will work best if you            licenses (CALs). Consolidating
     a better written application due        have a large number of users.              these two databases on a single
     to flaws such as its reliance on           Things get tricky and prob-             SQL Server installation would
     cursors or poor indexes.                lems can emerge when you                   mean a savings of a server
        There’s a financial impact to        start consolidating SQL Serv-              license.
     SQL Server sprawl that goes             ers and stack databases on a                  However, you will commonly
     beyond hardware and admin-              single server or cluster. With             find the number of users con-
     istrator costs. That is wasted          luck, you will have the same               necting to your consolidated
     licensing. A single SQL Server          users accessing different appli-           SQL Server solution increase,
     implementation with the cor-            cation databases on the same               so the per-processor license
     rect licensing actually may be          server, and this will result in an         will make more sense. By con-
     over-licensed. There are two            overall net savings in per-seat            solidating on state-of-the-art
     licensing modes for SQL Server:         licensing. Assume that you had             machines you can leverage
     per-seat and per-processor.             an accounting group of five                Moore’s law and get more
     Per-seat licensing is best used         users accessing two SQL Serv-              performance for your hardware
     when you have a small, known            ers. Without consolidation this            dollars.
     number of clients connecting to         would require two SQL Server                  Some workloads require you
                                                                                                                           

     Definition          SQL Server sprawl       Consolidation benefits     Servers and storage          Questions
                                                                                                                           73
chapter 1 The case for SQL Server consolidation



     to consolidate SQL Servers run-         How consolidation can help
     ning on machines with greater           Consolidation offers many benefits. These include:
     horsepower and memory. On
     SQL Server 2000, this may               • improved uptimes: Consolidated environments normally mean
     mean upgrading to Enterprise            better managed machines running on clusters with a storage area
     Edition, which offers access to         network (SAN) in the background. This helps deliver enterprise-wide
     greater memory and cluster-             four to five “nines” reliability and availability.
     ing. Unlike its predecessor, SQL
     Server 2005 Standard Edition            • improved performance: Consolidated environments allow you
     already offers better accessible        to have many databases running on high performance hardware,
     memory and clustering.                  typically with a fast disk subsystem, ample memory and 64-bit
       You will also find a limit on         machines. This means queries run in parallel and much faster than on
     how many databases you can              32-bit machines.
     support on a single instance
     of SQL Server. I don’t mention          • improved monitoring: A consolidated environment supports many
     operating system license con-           databases on a single machine, so uptime becomes very important.
     solidations because most peo-           Monitoring becomes proactive rather than reactive, optimizing
     ple who decommission a SQL              performance and uptimes.
     Server repurpose the machine
     for some other use, and the             • more efficient use of human capital: A consolidated
     operating system license is also        environment typically requires fewer people, freeing DBAs
     repurposed. You may save costs          from routine tasks to focus on planning and engineering.
     in monitoring licenses through          Compartmentalizing functionality into a dedicated group leverages
     use of NetIQ Corp. tools or             knowledge that may not otherwise be available: A recovery group
     Microsoft Operations Manager            dedicated solely to recovering SQL Servers will learn all aspects of
     (MOM).                                  backup and restore, tailor the best solution for each problem and offer
                                             better turn around times over a DBA who is not familiar with recovery
                                             or a server’s recovery solution.
                                                                                                                       8

     Definition          SQL Server sprawl      Consolidation benefits   Servers and storage       Questions
                                                                                                                       73
chapter 1 The case for SQL Server consolidation



     What to consolidate
                                                                                          PolyServe
     In a perfect world consolidating all SQL Server instances would be
                                                                                          SQL Server
     a simple task. However, in the real world, consolidation requires
     careful planning and consideration. I’ll begin with important consid-                 Utility                         ™


     erations when consolidating servers. Then I’ll go into consolidation
     issues for storage.                                                                 SAVE BIG with the best
                                                                                         consolidation solution
                                                                                         for SQL Server:
     Server ConSoLiDation                    that a Service Level Agreement
     When consolidating servers,             (SLA) is established so that ex-            • Reduce server counts by at
                                                                                           least 50%
     you should expect politics to be        pectations are fully understood
     a factor. Quite frequently, de-         by the user department and the              • Reduce management time by
                                                                                           up to 75%
     partments are unwilling to turn         new server owner.
                                                                                         • Save up to 70% on your total
     over management of their data-            Server consolidation also                   cost of ownership over a
     bases to another group. Some-           calls for fault tolerant hardware.            three-year period
     times, these concerns stem              When consolidating multiple
                                                                                         See for yourself!
     from turf wars. More often the          databases onto a single server,             Click on the button below to estimate your own
     worry is that the SQL Server            realize that if that server fails,          ROI. Enter data about your existing SQL Server
     databases will no longer enjoy          you no longer have just a single            environment and add up the savings you can
     the level of support that users         database out of commission.                 realize by consolidating with PolyServe.

     are accustomed to once the              Every database on the con-
     servers gain new owners. Man-           solidated server is at risk, and
     agers must take care to ensure          visibility is now much higher.
     that the consolidated solution          Instead of a single department
     will offer better support than          being inconvenienced, many
     what they are currently receiv-         departments now feel the im-
     ing in-house, and it is essential       pact of the failure, and this can                        www.polyserve.com                   9

     Definition          SQL Server sprawl       Consolidation benefits    Servers and storage                Questions
                                                                                                                                          73
chapter 1 The case for SQL Server consolidation



     undermine confidence in the
     consolidation effort. This means
     you need to cluster your SQL
     Server installations using SQL
     Server 2000 Enterprise Edi-
     tion or SQL Server 2005 Stan-
     dard Edition and above, along
     with Windows 2000 Server
     or Windows Server 2003. You
                                             ed to a client as a single server.
                                             Clients connect to the virtual-
                                             ized server and access the re-
                                             sources of a single node. Should
                                             this node fail, clients reconnect
                                             to another node in the cluster.
                                                Microsoft implements two
                                             flavors of clustering: single-
                                             instance (active-passive) and
                                                                                  m   Server
                                                                                      consolidation:
                                                                                            An approach to the
                                                                                               efficient usage of
                                                                                               computer server
                                                                                             resources in order
                                                                                             to reduce the total
     may also need more expensive            multi-instance (active-active).              number of servers or
     hardware and clustering train-          In single instance, one node is           server locations that an
     ing for your DBAs and network           active and the other node is
     administrators.                         offline. Should the active node             organization requires.
        Clustering is a technology           fail, the passive node is brought
     that allows the virtualization of       online and resources are failed
     shared resources to be present-         over to the newly active node.              In server consolidation you
                                             In multi-instance clustering,             must consider how accounts
                                             both nodes are active at the              and security models overlap.
“When consolidating                          same time; should one node                Occasionally you will find SQL
 multiple databases onto                     fail over, the other node’s re-           Server authentication accounts
                                             sources fail over to an active            or even local machine accounts
 a single server, realize                    node and clients access both              with the same names but dif-
 that if that server fails,                  nodes simultaneously. In this             ferent passwords. When you
 you no longer have just                     case, all nodes must have hard-           consolidate the servers these
                                             ware resources not only to run            accounts collide.
 a single database out of                    their own workload but also to              Memory limitations in a 32-
 commission.”                                support workloads from other              bit system will likely require
                                             nodes that may fail over.                 you to migrate to a 64-bit sys-
                                                                                                                         0

     Definition          SQL Server sprawl       Consolidation benefits    Servers and storage          Questions
                                                                                                                         73
chapter 1 The case for SQL Server consolidation



     tem. SQL Server 2000 32-bit            available RAM on SQL Server              the potential costs incurred
     can only access 2 GB of virtual        Enterprise Edition. The SQL              from investing in larger serv-
     memory (3 GB with the 3-GB             Server 2000 64-bit version did           ers, enterprise editions, 64-bit
     boot.ini switch in Windows             not support all functionality of         architectures and the per-pro-
     Server 2003). With the Physi-          the 32-bit version, including            cessor licensing model. In some
     cal Address Extension (PAE)            third-party iFilters, Data Trans-        circumstances, you may also
     switch the data cache will be          formation Services (DTS) and             need to stack your databases
     able to access up to 8 GB of           SP_OA procedures.                        on different instances to pre-
     memory on SQL Server Enter-               SQL Server 2005 64-bit is             vent one SQL Server from con-
     prise Edition running on Win-          the best choice in consolida-            suming all of tempdb or to try
     dows Server 2003 Enterprise            tion projects because there are          to isolate databases by work-
     Edition, and 32 GB on Windows          no memory limitations, though            load.
     Server 2003 Data Center Edi-           some companies are taking a
     tion. However, the user, pro-          cautious approach with this re-          Storage ConSoLiDation
     cedures and log caches will be         lease and waiting until Service          You also have the option to
     able to access only 1 GB of the        Pack 1 comes out. There are              consolidate storage, which nor-
                                            also some upgrade issues that            mally means either using a stor-
                                            may complicate migrating ap-             age area network (SAN) or net-
“SQL Server 2005 64-                        plications to SQL Server 2005.           work-attached storage (NAS).
 bit is the best choice in                  For a list of these problems, run        Most departmental servers will
                                            SQL 2005 Upgrade Advisor or              use locally attached storage,
 consolidation projects                     check the online help file, which        or as they are frequently called
 because there are no                       ships with the Upgrade Advisor,          JBOD (“just a bunch of disks”).
 memory limitations.”                       and search on Resolving Up-              Windows supports a total of
                                            grade Issues.                            26 drive letters — A through Z.
                                               Don’t forget to consider cost         Typically, the largest number of
                                            in planning your consolidation           available drives will be only 22,
                                            effort. I have already mentioned         assuming that one drive is as-
                                                                                                                         

     Definition         SQL Server sprawl       Consolidation benefits   Servers and storage          Questions
                                                                                                                         73
chapter 1 The case for SQL Server consolidation



     signed to the CD-ROM, drives
     A and B are not used, and C is
     used by the operating system.
     Most installations will con-
     solidate storage on one or two
     RAID arrays consuming two to
     three drive letters. However,
     some applications will require
     a multitude of file groups as-
                                             large enterprises each server
                                             to be backed up could have
                                             two network cards, one facing
                                             public and another connected
                                             to a private network used by
                                             backup agents. Also, if a server
                                             goes down it is frequently dif-
                                             ficult to migrate the storage to
                                             another server. The difficulty
                                                                                     m      Storage
                                                                                               consolidation
                                                                                             Storage consolidation,
                                                                                                 also called storage
                                                                                                   convergence, is a
                                                                                            method of centralizing
                                                                                               data storage among
     signed to their own letter.             may be a driver problem, but                           multiple servers.
                                             more frequently it lies in the
     locally-attached storage                need to move the RAID array or
                                                                                                  The objective is to
     can be faster than SANs for             enclosure from one rack to an-                  facilitate data backup
     some workloads. Although,               other. Finally, it is often tricky to                 and archiving for
     SANs with large caches can              increase the drive size without
     offer better performance than           bringing the server down or the                       all subscribers in
     locally attached storage; a large       drive itself offline.                             an enterprise, while
     checkpoint or a transactional                                                             minimizing the time
     system can easily overwhelm             sans not only solve all of
     the 128 MB or 256 MB cache of           these problems, they add                             required to access
     a typical, locally attached stor-       many features that are essen-                           and store data.
     age controller. Locally attached        tial to large enterprises. For
     storage can have some levels            example, most storage vendors
     of fault redundancy, but it pres-       provide real-time mirroring                  ies of the backup can be made
     ents several problems in large          capabilities where a byte-level              simultaneously. Enterprise-wide
     enterprises.                            backup of even live files can                backups and recovery can be
       It can be difficult to back up        be performed in seconds, and                 performed quickly and efficient-
     local drives over a network. In         sometimes even multiple cop-                 ly off the SAN rather than over
                                                                                                                             2

     Definition          SQL Server sprawl       Consolidation benefits       Servers and storage          Questions
                                                                                                                             73
the private backup network.              one data center and typically             they come with a significant
  SANs offer greater reliability         in one enclosure, managed by              cost and require a dedicated
than locally-attached stor-              one dedicated team with deep              skill set. NAS, on the other
age for several reasons. SAN             knowledge of the particular               hand, is best used for shar-
vendors tend to have a rigor-            vendor’s SAN.                             ing large numbers of smaller
ous drive certification process                                                    files among servers (i.e. Web
where they ensure that the               nas is network-attached                   pages, documents), but it is not
drives used in their SANs of-            storage somewhat similar to a             ideal for database files or other
fer the longest mean time to             SAN. However SANs dedicate                write-intensive applications.
failure, surpassing those of the         a drive or a logical unit number          Locally attached storage is
major hardware vendors. They             (LUN) to a server or a cluster.           cheaper and less complex than
also continually perform online          A NAS provides a share point              a SAN, but it is not optimal for
diagnostics and benchmark-               so that one or more servers               large databases or files, and it
ing to anticipate failing drives.        can access the data. NAS is not           does not offer the performance
When a drive is considered to            normally used with SQL Server             level of a SAN for large write-
be failing, the data is reallocat-       because a NAS provides only a             intensive operations.
ed to new drives and the failing         file share, not a drive to the SQL



                                                                              s
drive can be swapped out with            Server. While SQL Server can
a new drive. SAN drives can be           use this share, the result typi-
dynamically resized without the          cally is poor disk access perfor-               Locally attached storage
drive going offline. If a server         mance.                                          is cheaper and less
goes down, another server                In terms of making your stor-                   complex than a SAN, but
can quickly be reattached to             age investment, you’ll find that                it is not optimal for large
the “drives” used by the old             SANs work well with clusters,                   databases or files.
server with a minimal amount             offer better recovery and copy-
of reconfiguration. SANs also            ing options for large databases
work well with clustered serv-           or files, and perform better than
ers. SANs are centralized in             locally attached storage. But
                                                                                                                       3

Definition           SQL Server sprawl       Consolidation benefits    Servers and storage              Questions
                                                                                                                       73
PolyServe SQL Server Utility                                                                                                                                          ™




The only SQL Server consolidation solution that…
• Is designed for mission-critical database applications
• Provides full bare-metal system performance
• Assures full database and server fail-over without added cost or complexity
• Actually simplifies management of SQL Server architecture
A better approach for                                                                 SQL Server Utility
SQL Server consolidation                                                              Finally, you have a dedicated solution that enables multiple SQL Server
Now you can avoid the pitfalls of                                                     instances to be consolidated onto fewer servers. Powerful benefits of the SQL
virtual machine strategies that                                                       Server Utility include:
limit performance and increase
                                                                                      • Improved Uptime: High Availability services that are guaranteed for all SQL
system failure risk. Through
                                                                                        Server instances and can be easily configured to run on a mix of servers.
PolyServe’s unique approach to
server virtualization for SQL Server                                                  • Improved Performance: SQL Server still runs directly on the Windows
consolidation, you can finally                                                          Server OS; no virtual machine overhead, no CPU limitations, just bare-metal
reduce the cost and time required                                                       performance.
to manage many mission-critical
                                                                                      • Improved Monitoring: Matrix Manager provides a single cluster manage-
SQL Server databases.
                                                                                        ment point for monitoring cluster-wide IO performance, storage capacity
                                                                                        and usage and server health. Establish multi-tiered failover targets for
The power of shared data clustering                                                     guaranteed availability.
PolyServe’s unique approach features a shared data cluster file system that
                                                                                      • Improved DBA efficiency: Push installs, hot-fixes and updates of one
allows you to consolidate up to 16 multi-processor servers and 240 instances
                                                                                        instance to all failover targets automatically. Re-host online instances to
of SQL Server into a single, unified cluster. There’s no virtual machine software
                                                                                        another server via “drag and drop” to perform server maintenance or
to limit performance and it actually reduces the number of software installs to
                                                                                        capacity upgrades. Significantly simplify and streamline backups.
manage. With full support for 64-bit computing and SQL Server 2005, the
PolyServe SQL Server Utility offers unmatched power, flexibility and ease of
use for any SQL Server deployment. Learn more:
Visit the SQL Server Utility resource page
Download the SQL Server™ Utility white paper                                                                         www.polyserve.com                                    

          Definition                    SQL Server sprawl               Consolidation benefits            Servers and storage                   Questions
                                                                                                                                                                          73
chapter 1 The case for SQL Server consolidation




     Key consolidation questions to ask                                                        Consolidation
     Now that we’ve looked at the pros and cons of                                             questions to
     SQL Server and storage consolidation, there are                                           ask yourself:
     several questions you must answer before you
     can determine whether it will work for you.                                          1    Do you need to
                                                                                               optimize hardware
                                                                                               resources?



1    Do you neeD to oPtimize HarDWare reSourCeS?
     Optimizing hardware resources means you are providing ad-
                                                                                          2    Should you
                                                                                               standardize on a
                                                                                               single platform?
     equate 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 opti-
     mized so that you get the most performance per dollar spent on
                                                                                          3    Do you want
                                                                                               centralized database
                                                                                               management?
     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
                                                                                          4    Does your
                                                                                               business plan
                                                                                               call for a scalable
                                                                                               environment?
     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.
                                                                                          5    Do you need to
                                                                                               reduce costs?

        Tuning and optimizing database objects for changing work-
     loads are time-consuming tasks that are more art than science.
                                                                                                                      5

     Definition          SQL Server sprawl     Consolidation benefits    Servers and storage          Questions
                                                                                                                      73
chapter 1 The case for SQL Server consolidation


                                                                                  After consolidation,
     Tools such as Quest Software’s Spotlight on SQL Server, BMC                  you will be able to
     Software’s Patrol or Symantec Corp.’s (formerly Veritas) i3 can              build DBA teams
     help to identify which hardware and database objects are bot-
     tlenecks, as well as particular indexes or procedures that need
                                                                                  dedicated to
     tuning. Due to the time consuming nature of this task, proper                specialty areas,
     baselining with an eye tuned for quick wins is a prudent strat-              including replication,
     egy.
        One of the benefits of consolidating SQL Server databases on              backup and
     a server with significant hardware resources is an overall in-               recovery, monitoring
     crease in performance through parallel processing, and the fact              and tuning.
     that most workloads can take advantage of the 64-bit chipset.

2    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.



                                                                               s
     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.                          “Tuning and optimiz-
     Sybase’s own SQL Server is one product that does port to Mi-                             ing database objects
     crosoft’s SQL Server with a minimum of effort. However Sybase                            for changing work-
     is entrenched on Unix platforms, which normally means some                               loads are time-con-
     recoding on the middle tier.                                                             suming tasks that
                                                                                              are more art than
        You can also consolidate Microsoft SQL Server installations                           science.”
     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
                                                                                                                      

     Definition         SQL Server sprawl     Consolidation benefits   Servers and storage                Questions
                                                                                                                      73
chapter 1 The case for SQL Server consolidation



     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 compatibil-
     ity 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 consoli-
     date 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                       A better approach for
     Server consolidation effort because there are considerable ben-                   SQL Server consolidation
     efits from this.                                                                  Avoid the pitfalls of virtual machine strategies
                                                                                       that limit performance and increase system
                                                                                       failure risk. Through PolyServe’s unique

3    Do you Want CentraLizeD DatabaSe
     management?                                                                       approach to server virtualization for SQL Server
                                                                                       consolidation, you can finally reduce the cost
     Centralized management offers advantages such as efficient use                    and time required to manage many mission-
     of human resources, knowledge management, standards imple-                        critical SQL Server databases.

     mentation and improved backup and recovery.
                                                                                       Learn more:
       The most significant benefit is efficient use of human resourc-
                                                                                       Visit the SQL Server Utility resource page
     es. After consolidation, you will be able to build DBA teams
     dedicated to specialty areas, including replication, backup and                   View an on-demand SQL Server Utility webcast
     recovery, monitoring and tuning. By compartmentalizing by                         Download the SQL Server™ Utility white paper
     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                                     www.polyserve.com                    

     Definition          SQL Server sprawl     Consolidation benefits    Servers and storage                  Questions
                                                                                                                                          73
chapter 1 The case for SQL Server consolidation



     uncoordinated set of procedures. Pain points will have higher                    ABouT THE AuTHor
     visibility, and can be solved by carefully engineered solutions
     instead of quick fixes. Centralized management allows you to                     Hilary Cotter has been involved
     quickly create inventories of your SQL Server instances, and to                  in IT for more than 20 years as
     implement standards and operating procedures for backups,                        a Web and database consul-
     restores, security and best operating practices.                                 tant. Microsoft first awarded
                                                                                      Cotter the Microsoft SQL
4    DoeS your buSineSS PLan CaLL for a SCaLabLe
     environment?
                                                                                      Server MVP award in 2001.
                                                                                      Cotter received his bachelor of
     Growth is the life blood of every industry. Your SQL Server en-                  applied science degree in me-
     vironments must be able to keep up with business growth. A                       chanical engineering from the
     centralized environment allows for more dynamic allocation of                    university of Toronto and stud-
     resources as the resources are in a single location. Centralized                 ied economics at the university
     management normally means highly modularized, and collect-                       of Calgary and computer sci-
     ing information on them is a simple matter.                                      ence at uC Berkeley. He is the
                                                                                      author of a book on SQL Server
5    Do you neeD to reDuCe CoStS?
     SQL Server consolidation can significantly reduce costs with
                                                                                      transactional replication and is
                                                                                      currently working on books on
     careful planning. While there will be initial expenditures in pur-               merge replication and Micro-
     chasing clustered 64-bit hardware and licensing, there will be                   soft search technologies.
     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.



                                                                                                                         8

     Definition          SQL Server sprawl      Consolidation benefits    Servers and storage          Questions
                                                                                                                         73
chapter 1 The case for SQL Server consolidation




     Summary
     In this chapter we looked at the causes and pitfalls of
     SQL Server sprawl, including:
     • Poor management and lack of standards
     • Difficulty patching large numbers of SQL Servers
       in geographically dispersed locations
     • Inefficient use of hardware
     • Wasted licensing

        Consolidating SQL Servers            example, DBAs can spend                   SQL Server consolidation,
     allows you to avoid these issues        more time planning and being              including how to correctly
     by managing your enterprise             proactive as opposed to                   size your server farm, how to
     more efficiently, monitoring            managing and fighting fires.              identify if a SQL Server is a
     more effectively, enforcing                SQL Server consolidation               good consolidation candidate
     standards, providing higher             does require careful planning             and how to handle some
     availability and getting greater        and SLA negotiation with                  consolidation challenges.
     performance for your hardware           clients; poor planning will affect        Chapter 3 will present a
     dollar.                                 many customers and may                    methodology to follow while
        An added bonus of a                  cause them to lose confidence             planning, testing, deploying and
     consolidated SQL Server                 in your consolidation efforts.            monitoring your consolidated
     environment is better overall           To help you prepare for this              solution.
     performance and labor                   undertaking, Chapter 2 will
     compartmentalization. For               focus on how to plan your
                                                                                                                          9

     Definition          SQL Server sprawl       Consolidation benefits    Servers and storage          Questions
                                                                                                                          7

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:28
posted:5/5/2012
language:English
pages:19
handongqp handongqp
About