Fast Track Data Warehouse at Microsoft MTC

Some thoughts from Day 1  (dump)

Fast Track Data Warehouse is cool, and after a session on Fast Track Data Warehouse (FTDW) training Day 1 at the Microsoft MTC in Irving, TX, I have a much better idea of what is trying to be accomplished. Here’s my takeaway from yesterday in dump format:

• Hosts/Trainers: Roger Moore & David Browne
• Applicance : FTDW is not an appliance!
• FTDW:  is a set of pre-engineered configurations for your data warehouse hardware and software setup
• Hardware used: HP and Dell
• Datallegro – Came up with the configurations for FTDW
• FTDW – a method for designing cost-effective and balanced hardware configurations (it’s a methodology)
• FTDW – includes a best practice for data layout.  These can be used for other data warehouse setups as well that are not Fast Track, but some are specific only to FTDW.
• Dedicated san is used in this set up. (Geezsh haven’t we been preaching this forever anyway?)
• FTWD – set up to use 2x-3x fewer spindles
• MCR - max consumpt. rate per core amt sql server can process data for a specific cpu. mb/sec from cache. 200mb/cpu second is the throughput that they are going for.
• BCR - benchmark cons rate user workload or query (end to end)
• Uses 2.5:1 page compression factor
• 4gb/cpu is all memory they are allocating for query processing for this system
• These are 4TB+ systems up to 24TB.  Not suggested if your data warehouse is less than 4TB in size
• -E switch does allocations of 4 extents at a time.  This came out in SQL Server 2005 and is used in FTDW configurations
• -T1117 switch - change how autogrow works.  CAN USE autogrow for FTDW but it is not recommended.
• Data Architecture whitepaper - http://msdn.microsoft.com/en-us/library/dd459178.aspx
• They referenced SQL Server best practice often at - http://msdn.microsoft.com/en-us/library/cc966412.aspx
• NT Service\MSSQL$<InstanceName>  - new to Windows Server 2008 R2 and Windows 7. See http://msdn.microsoft.com/en-us/library/ms143504.aspx

http://www.microsoft.com/sqlserver/2008/en/us/fasttrack.aspx