Docstoc

SQL Server - PDF

Document Sample
SQL Server - PDF Powered By Docstoc
					James Serra – Sr BI Architect
JamesSerra3@gmail.com
http://JamesSerra.com/
Our Focus:


   Microsoft Pure-Play Data Warehousing & Business Intelligence Partner

Our Customers:




 Our Reputation:
 "B.I. Voyage came in and proved their subject matter expertise and worked well with
 our crew. Our CEO is now sold on the business value and corporate impact of having
 modern day business intelligence solutions!"
 email from Steve Bonanno CIO, Direct Edge

 “I do count you guys as leading partners and this view is represented throughout the PDW
 business. “
 email from Christian Kleinerman, Head of PDW Engineering Microsoft
•
•
•
•
•
•

•
Agenda
   Data Warehouse
   Fast Track Data Warehouse (FTDW)
   Business Data Warehouse Appliance (BDW)
   Business Decision Appliance (BDA)
   Database Consolidation Appliance (DBC)
   Parallel Data Warehouse (PDW)
•
•
•
•
•
•
•
•
•
•
 Legacy applications + data marts = chaos        Enterprise data warehouse =
 Production                        Finance
                                                 order               Continuity
    Control                                                            Consolidation
       MRP                         Marketing                           Control
                                                                       Compliance
  Inventory                        Sales                               Collaboration
    Control
      Parts                        Accounting
Management                                                            Single
    Logistics                      Management                         version of
                                   Reporting                          the truth
    Shipping                       Engineering              Enterprise Data
                                                              Warehouse
 Raw Goods                         Actuarial

       Order                       Human
     Control                       Resources
  Purchasing                                           Every question = decision
•

    −

    −
    −
•
    −
    −
                                                                                             DISK   DISK
            SQL SERVER
            CPU CORES




                                     A




                                          FC SWITCH
                              FC
   SERVER

             WINDOWS




                                                                                         A
              CACHE


                             HBA     B                                                          LUN




                                                                             CACHE
                                                      A     STORAGE                  A
                                                      B    CONTROLLER                B       DISK   DISK
                              FC     A
                             HBA                                                         B
                                     B
                                                                                                LUN




CPU Feed Rate      SQL Server      HBA Port Rate          Switch Port Rate   SP Port Rate    LUN Read Rate   Disk Feed Rate
                 Read Ahead Rate
•




•
•


•
        −
        −
        −
        −


•

                                            Reference
             Guidance                     Architectures,                Appliances
                                        “Fast Track” brand
    •       Build it yourself       •     “Cooking recipe”        • Very fast time to value
    •       Custom configurations   •     Probably higher success• No options (besides ‘size’)
    •       High IT expertise       •     Can be ‘sold’ to customers
                                    •     Tied to HW vendor
Software:
   • SQL Server 2008 R2 Enterprise
   • Windows Server 2008

Configuration guidelines:
  • Physical table structures
  • Indexes
  • Compression
  • SQL Server settings
  • Windows Server settings
  • Loading

Hardware:
   • Tight specifications for servers,
     storage and networking
   • ‘Per core’ building block
•
•
•
•
•
•
•
• You put together after receiving all the hardware (you need
  to install the OS, the edition of SQL Server that you’ve
  purchased, and any other products such as SharePoint and
  PowerPivot for SharePoint)
• Eliminates guesswork and is designed to save you months
  of configuration, setup, testing and tuning
•
•
•
•
•
•
•
• You put together after receiving all the hardware (you need
  to install the OS, the edition of SQL Server that you’ve
  purchased, and any other products such as SharePoint and
  PowerPivot for SharePoint)
• Eliminates guesswork and is designed to save you months
  of configuration, setup, testing and tuning
•
    −

    −
    −
    −
    −
    −
         Option                           Pros                                        Cons

1. Basic Evaluation       Very fast system set-up and                Possibility of over-specified storage or
                           procurement (days to weeks)                 under-specified CPU
                          Minimize cost of design and evaluation
                          Lower infrastructure skill requirements




2. Full Evaluation        Predefined reference architecture          Evaluation takes effort and time (weeks
                           tailored to expected workload               to months)
                          Potential for cost-saving on hardware      Requires detailed understanding of
                          Increased confidence in solution            target workload



3. User-defined           Potential to reuse existing hardware       Process takes several months
Reference Architecture    Potential to incorporate latest            Requires significant infrastructure
                           hardware                                    expertise
                          System highly tailored for your use-       Requires significant SQL Server
                           case                                        expertise
These metrics are use to both validate and position Fast
Track RA’s
   Maximum Consumption Rate (MCR) – Ability of SQL
   Server to process data for a specific CPU and Server
   combination and a standard SQL query
   Benchmark Consumption Rate (BCR) – Ability of SQL
   Server to process data for a specific CPU and Server
   combination and a user workload or query
   User Data Capacity (UDC) – Maximum available SQL
   Server storage for a specific Fast Track RA assuming
   2.5:1 page compression factor and 300 GB 15K SAS.
   30% of this storage should be reserved for DBA
   operations
•
•
•
•
•

•
•
•
•
•
•
•
•
•


•
•
•
•
•
•
•
•
•

•

•
•
•
• Offered as a reference architecture
Design, Build & Deploy in weeks rather than months

            Custom-built solution                             Integrated & Optimized
                                                                     Appliance
             Assess and understand workload
                                                           Design   Choose appliance for workload
                                                                                                    1
             Define architecture              1
   Design                                                   Build   Acquire appliance
             Evaluate alternatives
                                                                    Install appliance
             Design specific implementation                                                         2
                                                           Deploy   Extract & load data
             Acquire HW & SW components       2
                                                                    Stand-up in production
             Build solution
                                                                    Monitor & Manage                3
   Build     Load data                            Months
                                                                    Extract and manipulate data
             Proof Of Concept & Validation    3             Use
                                                                    Generate reports                4
             Tune & Balance HW & SW
                                                                    Make decisions
             Integrate in environment
   Deploy    Burn in & Stability
                                              4
             Monitor and troubleshoot
             Extract and manipulate data
    Use                                       5
             Generate reports
             Make decisions
                               Scale Out
  for both Performance and Capacity simultaneously by adding racks



                                        A prepackaged or pre-configured balanced
                                        set of hardware (servers, memory, storage
                                        and I/O channels), software (operating
                                        system, DBMS and management software),
                                        service and support, sold as a unit with
                                        built-in redundancy for high availability
                                        positioned as a platform for data
                                        warehousing.
         Control Rack
                        10 Node Data
                            Rack
HP PDW 1 Rack                              HP PDW 4 Rack:
17 Servers                                 47 Servers
22 Processors /132 Cores                   82 Processors / 492 Cores
125 TB                                     500 TB
              SMP                        MPP with PDW
HW advancements increasing       HW advancements increasing
ability to scale-up              ability to scale-up & scale-out
  Scaling is limited               Scaling to 1 PB+
  High end SMP very expensive      Scale out is relatively low cost
Extremely high concurrency for   Relatively high concurrency for
some workloads                   complex workloads
Less than 1-2 TB of data SMP     > 10 TB (typically) up to 1 PB
will almost always be better.    Limited SQL Server functionality
Usually <10TB                    HA is built in
Full SQL Server functionality
HA must be architected in
                         Failover Clusters
Control Node (2)         Dual networks
Single connection        Mirrored drives
point for SQL queries.   Hot swap drive
Single touch point for
DBAs.                    Dual power supplies
                         Dual cooling fans
Management Servers (2)
Patch management.
Active Directory.         Storage Node (10)
Node Image.               11 or 24 Disks each.
                          Dual network cards.
Landing Zone (1)
                          Dedicated SAN.
Server and storage
dedicated to loading
data.
                          Compute Node (11)
Backup Node (1)           A SQL Server 2008
Server and storage        Instance.
dedicated to handling     Highly Tuned SMP.
backup.                   8 Cores each.
                          8 Disks each (TempDB).
Query 1   ?   Query 1 is
              standard T-SQL
          ?   submitted to SQL
          ?   Server on Control
              Node
          ?
          ?
              Query is executed
          ?   on all 10 Nodes
          ?
          ?   Results are sent
          ?   back to client
          ?
?           ?
    ?           ? ? ??   L ?? ?? ? ???
                                         Multiple queries are
?                                        simultaneously
                ?? ??    L ?? ?? ? ???   executed across all
                                         nodes.
    ?                    L ?? ?? ? ???
?                        L ?? ?? ? ???
                         L ?? ?? ? ???   PDW supports
?       ?                L ?? ?? ? ???   querying while data
                                         is loading.
                         L ?? ?? ? ???
    Load                 L ?? ?? ? ???
                   L     L ?? ?? ? ???
                         L ?? ?? ? ???
        Replicated                 Distributed              Ultra shared nothing

• A table structure that   • A table structure that is   • The ability to design a
  exists as A full copy      hashed on a single            schema of both
  within each discrete       column and uniformly          distributed and
  DBMS instance.             distributed across all        replicated tables to
                             nodes on the appliance.       minimize data
                             Each distribution is A        movement
                             separate physical table     • Small sets of data can be
                             in the DBMS.                  more efficiently stored
                                                           in full.
                                                         • Certain set operations
                                                           are more efficient
                                                           against full sets of data.
•

    −
    −
    −


•
    −
As an end user or DBA you think about 1 table: LineItem.
You run “select * from LineItem”

PDW is an appliance, simple to use!
You don’t care or need to know that there are actually 320 tables
representing your 1 logical table.
That each of those 320 is using it own clustered index and has
range partitioning.
              Departmental
               Reporting


               SQL Server




 Regional                      High-Performance
 Reporting   Central EDW Hub       Reporting


SQL Server                       SQL Server
 FastTrack                     Analysis Services



             Landing Zone




                ETL Tools
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•   http://bit.ly/w1cAh5
•
•   http://bit.ly/yWEp9C
•                                                      http://bit.ly/yyuElC
•                                                   http://bit.ly/y7bXY5
•                          http://bit.ly/xAZy9h
•                                             http://bit.ly/yBz2qj
•                                      http://bit.ly/AqmTm8
•                                  http://bit.ly/ykS1PD

•                                     http://bit.ly/wzSsdd
•                                                                             http://ibm.co/zmYihU
•                                                 http://bit.ly/y5DEUy
•   http://bit.ly/xuTAnu
•                                                http://bit.ly/AvZQ79

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:10
posted:8/1/2012
language:English
pages:43