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)

10 thoughts on “SQL SERVER – A Brief Introduction to DW 2.0

  1. Hi Pinal,
    I am a beginner in SQL server and i am very greatful to you that i have got immense knwoledge from your blog. I really appretiate your efforts and time.

    I have a situation which popped up this error msg:
    Msg 296, Level 16, State 3, Line 1
    The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

    Could you please tell me why this is happening? what can we do to avoid this problem except convert function.


    • Hi,
      The range for smalldatetime is January 1, 1900, through June 6, 2079. Make sure that the char value you are converting is in this date range. Could you post the sql, we could troubleshoot it better.


    • Hi Dinkar,

      As you stated in your response, first you should verify your input if its valid or in the range or not….

      the value you’re putting should be in a range and valid for the datatype SMALLDATETIME


    • Hi Dinkar,
      The smalldatetime has a range of January 1, 1900, through June 6, 2079. Make sure that the char value is in a proper smalldatetime format and is within the range.


    • Hi Dinkar,

      As you stated in your response, first you should verify your input if its valid or in the range or not….

      the value you’re putting should be in a range and valid for the datatype SMALLDATETIME


  2. I belive that SQL SERVER has been improved but is still 2 years back from other DW Tecnologies. Top 3 gaps that SQL SERVER need to cover to be considered DW 2.0. compliance are:

    – Mixed Workload Capability
    – Manage hot and cold data
    – Business Continuity

    Best Regards

    Enrico Executive DW manger


  3. Hi Enrico,

    How we can configure the Datawarehouse in sql server 2008 R2, i am little bit confuse that how configure and store data in the Datawarehouse.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s