SQLAuthority News – Fast Track Data Warehouse 3.0 Reference Guide


I am very excited that Fast Track Data Warehouse 3.0 reference guide has been announced. As a consultant I have always enjoyed working with Fast Track Data Warehouse project as it truly expresses the potential of the SQL Server Engine. Here is few details of the enhancement of the Fast Track Data Warehouse 3.0 reference architecture.

The SQL Server Fast Track Data Warehouse initiative provides a basic methodology and concrete examples for the deployment of balanced hardware and database configuration for a data warehousing workload. Balance is measured across the key components of a SQL Server installation; storage, server, application settings, and configuration settings for each component are evaluated.

Description Note
FTDW 3.0 Architecture Basic component architecture for FT 3.0 based systems.
New Memory Guidelines Minimum and maximum tested memory configurations by server socket count.
Additional Startup Options Notes for T-834 and setting for Lock Pages in Memory.
Storage Configuration RAID1+0 now standard (RAID1 was used in FT 2.0).
Evaluating Fragmentation Query provided for evaluating logical fragmentation.
Loading Data Additional options for CI table loads.
MCR Additional detail and explanation of FTDW MCR Rating.

Read white paper on fast track data warehousing.

Reference: Pinal Dave (http://blog.SQLAuthority.com)


SQLAuthority News – Best Practices for Data Warehousing with SQL Server 2008 R2

An integral part of any BI system is the data warehouse—a central repository of data that is regularly refreshed from the source systems. The new data is transferred at regular intervals  by extract, transform, and load (ETL) processes.

This whitepaper talks about what are best practices for Data Warehousing. This whitepaper discusses ETL, Analysis, Reporting as well relational database. The main focus of this whitepaper is on mainly ‘architecture’ and ‘performance’.

Download Best Practices for Data Warehousing with SQL Server 2008 R2

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – A Brief Introduction to DW 2.0

The traditional form of storing digital data has been disk storage.  However, the huge advances in technology means that there has been a huge need for data storage to evolve to keep up with the fast-changing times.  Microsoft SQL Server has gone through a huge overhaul in order to keep up with the amount of data storage that is necessary, and that is where data warehousing comes into play.

For many online applications, there is a need to not only access small amount of information from disk storage, but large amounts in the forms of sets.  SQL Server allows access to these sets of data in a sequential manner that optimizes computer.  This proprietary technology makes SQL Server the go-to system to fit these computing needs.

SQL Server has also solved the problem of changing from “data marts” to data warehouses.  As companies grow, their digital storage needs change and expand as well.  Previously, transferring a system over to an SQL Server meant having to destroy old data during the transfer.  This was the source of a lot of data loss and server problems.  However, the new versions of SQL Server have built-in systems that help organizations transfer to the larger data storage system.

The costs of data storage and data warehousing are becoming a huge concerns for small businesses and corporations alike.  It is a known rule of thumb that the larger the main server, the more expensive it is.  SQL Server also addresses this problem by allowing data warehousing to be run on multiple processors – in the end rendering it much cheaper than having one main data warehousing server.  SQL Servers uses the “hub-and-spoke” system, which allows data warehousing to be broken up by multiple systems without losing any data.

A huge question is how SQL Server can store so much data.  As with any storage system, some data is compressed before it is stored.  Other system will undergo lengthy processes in order to retrieve this compressed data due to updates and possible loss of information, but SQL Server forgoes this updating process, which allows for quick and easy access as well as compression.

The issue of compression of too much data is also solved with parallel processing.  In this system, multiple processors are “enlisted” in order to help store the data.  SQL Server has the technology to divide the data appropriately, and then easily access this data on command.  Rather than slowing one server down, the data can be divided up to be easily handled.

One of the beauties of computing is that the computer can calculate probabilities much faster than a human – indeed, this was one of the main functions of computer development.  SQL Server uses this capacity to store data, using “Probability of Access” technology.  SQL Server can determine which data is “hot” and likely to be accessed the most, which is moderate, and which is cold – that is, there is a low probability of access.  With these designations, SQL Server can store the Hot data in “high performance” storage, so it can be quickly recovered, and cold data is stored elsewhere, freeing up the processors to work more efficiently.

SQL Server has not only optimized storage of “static” data, or individual points of information, but has created a technology that will also store “streaming” data.  Streaming data is a way to record multiple “events” over a period of time – like measurements of temperature thousands of times per minute for instance.  This kind of data storage would completely bog down another server, but SQL Server has developed the technology to efficiently store this data without slowing down processors.  And, of course, the data will be easily accessible because of the data warehousing systems already discussed.

Streaming data is difficult for most servers because of storage purposes.  This streaming data overwhelms the servers because they cannot store and retrieve this much data efficiently, and it bogs down the system.  Other companies have attempted to create programs that allow for access of streaming data, but it often comes at the cost of processor speed.  The usual process is to store streaming data as historical data then retrieving it as static data, which slows down all the processors.  SQL Server has created a system that allows for analysis of this data by showing it in the interactive sector.

In summary, SQL Server is at the very forefront of future data warehousing – DW 2.0.  SQL Server is able to store large amounts of data at a low cost and with maximum efficiency.  Microsoft has taken into account the need for multiple processors and an easy way to transfer old data into this new system.  The technology behind SQL Server is sure to make data access much more efficient.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Few Notes on Fast Track Data Warehouse

I recently delivered fast track data warehouse training. This training was very challenging as this training requires very specific hardware and extremely different way of looking at data warehousing. While training I have made few notes and I will now share the same notes with you. Please note that this are just notes and not learning material.

  • Fast Track Data Warehouse has a primary emphasis on eliminating potential performance bottlenecks.
  • It supports maximum of 48 TB data at this moment.
  • Currently HP, Dell, Bull, IBM and EMC2 provides necessary hardware for Fast Track Data Warehouse.
  • All the Software and Hardware comes in a single package.
  • The Base OS is Windows Server 2008 and Base Database is SQL Server 2008 R2.
  • FTRA stands for Fast Track Reference Architecture.
  • SMP stands for Symmetric Multiprocessing.
  • Fast Track is pre-configured and it just works out of the box.
  • PDW stands for Parallel Data Warehouse.
  • Fast Track Data Warehouse is based on Symmetrical Multi Processing (SMP) and PDW is based on Massively Parallel Processing (MPP).
  • Each CPU Core delivers 200 MB per second data.
  • This hardware architecture is available at $13,000 per Terabyte.
  • It uses RAID 1 Mirror sets
  • Recovery model of FTDW is recommended to Simple Recovery mode.
  • Turn off AutoGrow for user defined database.
  • Leave AutoGrow On for TempDB and set at 4 MB.
  • Update the database statistics regularly.

I really had great time doing training for this subject. If you have worked with Fast Track Data Warehouse, what was your experience?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fast Track Data Warehouse for SQL Server 2008

I recently attended a wonderful training session organized by Microsoft on Fast Track Data Warehouse Reference Architectures. If you are regular reader of my blog, you will be well aware of the fact that I am more of the Relational guy than a Business Intelligence professional. I was initially a bit skeptic about this training. However, once I start learning about it, to my surprise, I thought that I am the perfect guy to learn this. In fact, I realized that few of the tricks which this course is suggesting have already been implemented in my earlier consulting assignments.

Fast Track Data Warehouse is a very unique effort by Microsoft, where few Reference Architectures which offer scalability and reliability to huge database are proposed. Fast Track Datasheet provides some excellent points about this reference architecture. I am listing few of the same here.

  • Scale from 4 up to 48 Terabytes using compression capabilities in SQL Server 2008 Enterprise
  • Choose from industry-standard hardware from Dell, HP, Bull, IBM, EMC and other leading vendors
  • Pre-configured servers, storage and networking, specifically balanced and optimized for warehousing
  • Implement an enterprise-class solution for less than one-third the price of a comparable Oracle system
  • Fast Track Data Warehouse has a primary emphasis on eliminating potential performance bottlenecks.
  • Optimized for sequential IO rather than random IO, each Fast Track Data Warehouse is designed to provide up to 200 MB/s per CPU core

The Fast Track approach derives its strength from the advanced data warehouse enhancements included in SQL Server 2008 Enterprise such as compression, parallel partitioning and star join query optimization

    Well, I think it is great effort by Microsoft and I must express deep gratitude to Microsoft for giving me opportunity to learn this unique initiative. I clearly see that in near future, lots of Data Warehousing solutions will be switching to this solution. You can read more about this reference architecture over here. The image used in blog post is taken from MS official site of Fast Track.

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

    SQL SERVER – What is MDS? – Master Data Services in Microsoft SQL Server 2008 R2

    What is MDS?

    Master Data Services helps enterprises standardize the data people rely on to make critical business decisions. With Master Data Services, IT organizations can centrally manage critical data assets company wide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time. (Source: Replace with Microsoft)

    Today I will be talking about the same subject at Microsoft TechEd India. If you want to learn about how to standardize your data and apply the business rules to validate data you must attend my session. MDS is very interesting concept, I will cover super short but very interesting 10 quick slides about this subject. I will make sure in very first 20 mins, you will understand following topics

    • Introduction to Master Data Management
      • What is Master Data and Challenges
      • MDM Challenges and Advantage
    • Microsoft Master Data Services
      • Benefits and Key Features
      • Uses of MDS
      • Capabilities
    • Key Features of MDS

    This slides decks will be followed by around 30 mins demo which will have story of entity, hierarchies, versions, security, consolidation and collection. I will be tell this story keeping business rules in center. We take one business rule which will be simple validation rule and will make it much more complex and yet very useful to product.

    I will also demonstrate few real life scenario where I will be talking about MDS and its usage.

    Do not miss this session. At the end of session there will be book awarded to best participant.

    My session details:

    Session: Master Data Services in Microsoft SQL Server 2008 R2
    Date: April 12, 2010  Time: 2:30pm-3:30pm

    SQL Server Master Data Services will ship with SQL Server 2008 R2 and will improve Microsoft’s platform appeal. This session provides an in depth demonstration of MDS features and highlights important usage scenarios. Master Data Services enables consistent decision making by allowing you to create, manage and propagate changes from single master view of your business entities. Also with MDS – Master Data-hub which is the vital component helps ensure reporting consistency across systems and deliver faster more accurate results across the enterprise. We will talk about establishing the basis for a centralized approach to defining, deploying, and managing master data in the enterprise.

    Reference: Pinal Dave (http://blog.SQLAuthority.com)

    SQLAuthority News – SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update)

    Note: Download SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update) Authored by Microsoft

    SQL Server 2008 R2 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2008. The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism.

    The training kit is designed for the following technical roles:

    • Developers who build applications for the Microsoft platform.
    • Microsoft evangelists, technical specialists and consultants.

    Download SQL Server 2008 R2 Update for Developers Training Kit (March 2010 Update)

    Abstract courtesy Microsoft

    Reference: Pinal Dave (http://blog.SQLAuthority.com)