SQL Server 2012 (“Denali”): FileTable

Posted on February 22, 2012 by

FileTable is a new feature in SQL Server 2012 that is built on top of SQL Server FILESTREAM technology, which allows BLOB data to be stored as individual files separate from a database’s data files.  Interactions with the FileStream files took place only through T-SQL or through code which engaged with OpenSqlFileStream API.

FileTable goes one step further in adding support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server, accessing the files as if they were stored in the file system.  It also employs a familiar, hierarchical folder structure, and includes the storage of file attributes, such as created date and modified date.

FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic search – over unstructured data and metadata.

More info:

Beyond Relational – Installing SQL Server FileTable

Denali CTP 3 – How to Set Up the FileTable and access the unstructured data from SQL Server

Creating Your First FileTable in SQL Server Denali CTP3

SQL Server 2012 FileTables in T-SQL part 1: functions and methods

SQL Server 2012 FileTables in T-SQL part 2: new rows

SQL Server 2012 FileTable – Part 1

SQL Server 2012 FileTable – Part 2

Video Microsoft SQL Server Beyond Relational Landscape: Current and Future

Posted in 2012/Denali, SQLServerPedia Syndication | Leave a comment

Please vote for my SQLRally session

Posted on February 21, 2012 by

I’m hoping to do a presentation at SQLRally in Dallas on May 10-11, but I need your help!

There is a Community Vote for the remaining 20 sessions, so I would greatly appreciate it if you would go to http://www.sqlpass.org/sqlrally/2012/dallas/CommunityChoice.aspx and vote for my session (described below).  You have to be a PASS member to vote, but if you are not, it is free to register.  Thanks for your help!

Scaling SQL Server to HUNDREDS of Terabytes (Overview of Microsoft Appliances)

Learn how SQL Server 2008 can scale to HUNDREDS of terabytes for BI/DW solutions.  This session will focus on Fast Track Solutions and Appliances, Reference Architectures, and Parallel Data Warehousing (PDW).  Included will be performance numbers and lessons learned on the very first production PDW deployment in the world and how a successful BI solution was built on top of it using SSAS.

Learn about all the different appliances and how they can save you a tremendous amount of time and money instead of building your own: HP Business Decision Appliance (BDA), HP Business Data Warehouse Appliance (BDW), HP Enterprise Data Warehouse Appliance (EDW), and HP Database Consolidation Appliance (DBC).

If you are involved in the decision-making in your company for purchasing one or more servers to be used for SQL Server, this session will make you aware that there are better options outside of the usual ordering of a server and internally installing the hardware, OS, and SQL Server.  And then hoping it will handle your databases!

Posted in Presentation, SQLServerPedia Syndication | Leave a comment

SQL Server 2012 (“Denali”): PowerPivot

Posted on February 20, 2012 by

Version 2 of PowerPivot (download) is being made available with SQL Server 2012.  There are many new features, and my top 5 are:

Diagram View – Instead of looking at the defined relationships in a list format, v2 allows you to view the relationships in a diagram.  This makes it much easier to view how all the data fits together

KPIs – You can now create Key Performance Indicators

Hierarchies – There is now functionality to model parent-child relationships along with a PATH function to display the path of a parent-child relationship in a delimited format

Perspectives – You can define subsets of a model to provide a simplified view for the end-user (same as in SSAS)

Sort by Other Column – Allows for sorting one column by another column within the same table.  For example, the month name column can be sorted by the month number column

More info:

SQL Server 2012 PowerView and PowerPivot v2

What’s new in PowerPivot for Excel with SQL Server Code Name “Denali” CTP3

What’s New in PowerPivot

Video What’s New in Microsoft SQL Server Code-Named “Denali” for SQL Server Analysis Services and PowerPivot

Microsoft PowerPivot Code-Named “Denali” CTP3 Release Notes

Using PerformancePoint Services (PPS) with PowerPivot SQL Server 2012 RC0

Using the Calculation Area and DAX in SQL Server 2012

Posted in 2012/Denali, PowerPivot, SQLServerPedia Syndication | 1 Comment

SQL Server 2012 (“Denali”): Microsoft StreamInsight

Posted on February 17, 2012 by

Microsoft StreamInsight, first introduced in Microsoft SQL Server 2008 R2, is a way to effectively analyze large amounts of event data streaming in from multiple sources.  You can derive insights from critical information in near real-time.  StreamInsight is a platform for Complex Event Processing (CEP) giving you the ability to monitor, analyze, and act on data in motion and make informed decisions almost instantaneously.  Example application areas for CEP include financial applications, fraud detection, manufacturing, surveillance of internet traffic and real-time business intelligence.

Currently at version 1.2 of StreamInsight.  SQL Server 2012 will include version 2.0 of StreamInsight.

StreamInsight 2.0 requires Microsoft SQL Server 2012 Release Candidate 0 (RC 0) license and .NET Framework 4.0. It includes the following performance improvements along with few bug fixes.

Performance Improvements

The following issues that help improve performance of a StreamInsight application are fixed in this version.

  • Queries can deadlock when a join operator has input streams that originate from a common source.
  • The memory consumed by a StreamInsight application may grow infinitely when lifetime of the left-side reference stream overlaps with a right-side continuous real-time stream of a Left Anti Semi Join operator.

More info:

Video StreamInsight 1.2 – New in SQL Server Code Name “Denali” CTP3

Video Introduction to Microsoft SQL Server 2008 R2 StreamInsight

Microsoft StreamInsight 1.2 BOL

Microsoft StreamInsight 2.0 BOL

Microsoft StreamInsight Blog

StreamInsight Samples

Where Microsoft StreamInsight Fits In

Video Microsoft StreamInsight: Introduction to Complex Event Processing with SQL Server 2008 R2 StreamInsight

Video Introduction of MS StreamInsight

What is StreamInsight and why does it matter?

Getting started with StreamInsight

My StreamInsight Course for Pluralsight is Now Available

Posted in 2012/Denali, SQLServerPedia Syndication | Leave a comment

SQL Server 2012 (“Denali”): T-SQL Enhancements

Posted on February 15, 2012 by

SQL Server 2012 has a number of new T-SQL features.  Listed below are all of the new features, along with the best links I have found that describe each feature:

WITH RESULT SETS, OFFSET AND FETCH, THROW, SEQUENCE: See New T-SQL Features in SQL Server 2012 and SQL Server 2012-DENALI-whats new in T-SQL

FORCESEEK and FORCESCAN: See FORCESEEK and FORCESCAN – New enhancements with Table hints in SQL Server Denali CTP3

Conversion functions: See New Built-in Functions in SQL Server 2012 Denali and SQL Server 2012 – new conversion functions

Date and time functions: See New Date and Time Functions in SQL Server 2012

Logical functions: See New Logical Functions in SQL Server 2012 (IIF and CHOOSE)

String functions: See CONCAT() string function–SQL Server 2012 and SQL Server v.Next (Denali) : CTP3 T-SQL Enhancements : FORMAT()

Analytic functions: See The new Analytic functions in SQL Server 2012

More info:

Video The T-SQL Cookbook: What’s Cool in Microsoft SQL Server 2008 R2 and New in SQL Server Code-Named Denali”

Programmability Enhancements (Database Engine)

Posted in 2012/Denali, SQLServerPedia Syndication | 1 Comment

SQL Server 2012 (“Denali”): Contained Databases

Posted on February 13, 2012 by

A problem that has plagued SQL Server for a long time is that a database is not very portable.  Sure, you can backup or detach a database and restore or attach it elsewhere, but when you do that, you are missing a lot of the pieces that make that database a part of an application, and a lot of those pieces are really considered administration pieces as opposed to application pieces.  You don’t get any synchronization of “outside of the database” items such as security, roles, linked servers, CLR, database mail, service broker objects, replication, and SQL Server Agent jobs.

In SQL Server 2012, some of these issues are being addressed with a feature called “contained databases.”  A contained database is a concept in which a database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed.  Users connect to the database without authenticating a login at the Database Engine level.  Isolating the database from the Database Engine makes it possible to easily move the database to another instance of SQL Server.  Including all the database settings in the database enables database owners to manage all the configuration settings for the database.

SQL Server 2012 Release Candidate 0 (RC 0) includes a first step toward contained databases, introducing partially contained databases (also known as Partial-CDB).  Partially Contained Databases provide some isolation from the instance of SQL Server but do not yet provide full containment.

In SQL Server 2012, the first iteration of the contained databases feature will provide the following solutions:

  • You can create a database-specific user without a SQL Server login (and you can create multiple such users with the same name for different databases).  A user at the database level is called a “contained database user“.  The user authentication is done at the database level and the applications just need to change their connection strings.  This solves a very common problem of “Orphaned Users“
  • You can have *some* compatible use of tempdb with databases of different collations, since #temp tables will be created in the collation of the calling database context.  In SQL Server 2012, tempdb will automatically create the temporary objects by using the collation of the “Contained Database”,  instead of the collation of the server
  • You can use a DMV to show most objects or code you have that will threaten containment

Note in a partially contained database you cannot use replication, change data capture, or change tracking.

Expect Microsoft to move toward have true fully contained databases in future versions of SQL Server.

More info:

Contained Databases in SQL Server 2012

Step-by-Step guide to Implement Contained Databases

SQL Server v.Next (Denali) : Contained Databases

CONTAINED DATABASE – SQL SERVER 2012

Video SQL Server Code Named “Denali” Contained Database Authentication Demo

What is Contained Database in SQL Server

SQL Server 2012 – New Features – Contained Databases

SQL Server 2012: Sometimes Partial Is Preferable

Contained Database Authentication in SQL Server 2012

Posted in 2012/Denali, SQLServerPedia Syndication | 6 Comments

SQL Azure

Posted on February 10, 2012 by

SQL Azure is a cloud-based service from Microsoft that uses a special version of SQL Server as its backend.  SQL Azure helps to ease provisioning and deployment of multiple databases.  Developers do not have to install, setup, patch or manage any software, as all that is taken care of by Microsoft with this platform as a service (PAAS).  High availability and fault tolerance is built-in and no physical administration is required.  It is an elastic platform which provides you the means to instantly increase or decrease your server capacity.  You only pay for what you use and don’t have to estimate capacity needs and buy a reserve capacity.

SQL Azure is getting very popular, and while I think it will be a few years before many companies replace their internal data centers with it, it is a good idea to become familiar with it now.  Unfortunately it does not include SSAS yet, but does include SSRS and you can use SSIS to move data into and out of SQL Azure.  Slowing its adoption is it does not yet support all the SQL Server features such as: integrated security, SQL Server agent, transaction log backups, cross-database references, linked servers, fulltext indexes, connection pooling, multiple database collations, data compression, table partitioning, replication, mirroring.  See SQL Server Feature Limitations (SQL Azure Database) and Unsupported Transact-SQL Statements (SQL Azure Database)

It’s real simple to get to know how SQL Azure works by creating an account for free and playing with it.  Here is how to quickly start:

1) If you have an MSDN subscription, you can create a Windows Azure account for free.  To do this, login to MSDN and go to “My Account” and choose “Activate Windows Azure”.  Check out the benefits.  If you do not have MSDN, you can get a 1 month trial (no credit card required) or a 3 month trial (credit card required).

2) Watch the following short videos SQL Azure at a Glance, SQL Azure: Creating Your First Database, and Interacting with a SQL Azure Database.  Another good resource is Introduction To SQL Azure by the Microsoft Virtual Academy.

That’s it!  I spent about 30 minutes creating my account and watching those videos, and I was then able to create a server, create a database, and tinker around with creating and querying tables.  There is even a wizard to help you migrate databases from SQL Server to SQL Azure.

More info:

Windows Azure Team Blog

SQLAzure – My First Cloud

SQL Azure Tools

Dipping My Toes Into SQL Azure

Dipping My Toes Into SQL Azure – Part 2 – Protection Mechanisms

SQL Azure – why use it and what makes it different from SQL Server?

Windows Azure Learning Plan – SQL Azure

SQL Azure Performance and Elasticity Guide

Video Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations

Video Go Large with SQL Azure Federations

Video Developing with SQL Azure: Tools & Frameworks In Action

Video Getting Started with Cloud Business Intelligence

Video Microsoft SQL Azure Overview: Tools,Demos and Walkthroughs of Key Features

Video Using Cloud (Microsoft SQL Azure) and PowerPivot to Deliver Data and Self-Service BI at Microsoft

Video Microsoft SQL Azure Performance Considerations and Troubleshooting

Video Using Microsoft SQL Azure with On-Premises Data: Migration and Synchronization Strategies and Practices

Video SQL Azure-Design Concepts

SQL Denali T-SQL features in SQL Azure now

Now Available: SQL Azure Q4 2011 Service Release

Posted in SQL Azure, SQLServerPedia Syndication | Leave a comment

What is Microsoft Connect?

Posted on February 8, 2012 by

Microsoft Connect (blog, twitter) is a site where you can post feedback to improve the quality of many Microsoft products and impact the direction of those products.  As of this writing, the site accepts bugs for 51 products and suggestions for 34 products, all from the community.  You will need a Microsoft Live ID to submit a bug or suggestion.

The site has a nice dashboard where you can view feedback you have selected to watch, feedback you have submitted, and feedback you have participated in.  Each line in the dashboard shows the number of comments, the number of workarounds, and the status.

On many occasions I have run into a bug with SQL Server, and have used connect to see if someone else has notified Microsoft of the bug.  And many times Microsoft or another community member will have posted a work around.  I have also posted suggestions for SQL Server, along with many others, and have seen those suggestions implemented.  So if you find a bug, or have an idea to improve a Microsoft product, check out Microsoft Connect.

More info:

Use Microsoft Connect to Drive Product Improvement

Posting An Issue To Microsoft Connect

HOW TO: SUBMIT AN ITEM TO MICROSOFT CONNECT

Posted in Microsoft, SQLServerPedia Syndication | 2 Comments

SQL Server 2012 (“Denali”): Data Quality Services

Posted on February 6, 2012 by

Data Quality Services (DQS) is a brand new feature in SQL Server 2012.  In short, DQS enables you to build a knowledge base, and use that knowledge base to perform a variety of critical data quality tasks such as correction, enrichment, standardization and de-duplication of your data.  DQS enables a data steward or IT professional to maintain the quality of their data and ensure that the data is suited for its business usage.

Building a knowledge base is easy – using your own data, DQS allows you to discover knowledge directly from samples of your data, combining computer-assisted and interactive experiences.  In addition, you can also extend your knowledge base with 3rd party data providers, using cloud-based Reference Data Services (RDS) from Windows Azure Marketplace, such as Melissa Data, Digital Trowel, Loqate and CDYNE Corp.  You define the rules that DQS will apply when validating data, and the action taken when those rules are breached. 

DQS also provides batch capabilities with a SQL Server Integration Services (SSIS) component, as well as integration with the Master Data Services (MDS) Excel add-in.

More info:

How to add Reference Data Services in Data Quality Services (DQS)

SQL Server Data Quality Services – First Look

New and Exciting in SQL Server Code Name “Denali”: Knowledge Driven Data Quality Services (DQS)

Video Using Knowledge to Cleanse Data with Data Quality Services

SQL Server Data Quality Services & SSIS

Using the SSIS DQS Cleansing Component

Video How Do I: Cleanse my data with SQL Server Denali SSIS DQS Cleaning Component?

SQL Server 2012 RC0 – What’s New in DQS?

Getting Started with Data Quality Services (DQS) 2012

Building Out a DQS Knowledge Base

Posted in 2012/Denali, SQLServerPedia Syndication | 1 Comment

Slowly Changing Dimensions

Posted on February 3, 2012 by

When designing a data warehouse, how you handle changes to dimensional data over time is the most important decision to make.  It is rare that a dimension will remain static over time.  For example, a customer may change their phone number or their address, or a sales person may change their sales territory.  For a customer phone number change, it usually not important to track the history of phone numbers a person has – you just overwrite the previous phone number.  However, for an address change, you will usually want to track the history of a customer address in order to properly report the history of sales by geography.  The same goes for tracking the history of a sales persons sales territory.  For example, say in a dimension record a sales person is assigned to Territory A in 2010 and that territory makes $10 million in sales of which the sales person makes a commission.  Let’s say Territory B made $5 million in sales.  Then in 2011, this sales person is assigned to Territory B.  If you just overrode his dimension record and assigned him to Territory B, when doing a sales commission report for 2010 he would show with only $5 million in sales.  That would make for one angry sales person.  This is why you need to record the history of changes.

The term Slowly Changing Dimension (SCD) is about tracking the variation in dimensional attributes over time.  Don’t let the word slowly in this context fool you – the changes could very well happen rapidly.  But in general they will happen “slowly” over time.

The way you handle changes falls into three categories:

Type 1: No history information is stored.  Existing data is overridden by new values

Type 2: The history of data changes is preserved.  A new record is inserted each time a change is made.  Every data row has a valid from date and valid to date indicating the time period of the data’s validity, and each row usually has as isCurrent type of field that is set to Yes for the active record with the others set to No.  When a fact table record is inserted, it will be given the appropriate surrogate key of the dimension record

Type 3: This method traces changes using separate columns (but no new rows).  This means there is a limit to history preservation based on the number of columns in each row that are designated for storing historical data.  For example, a record may have the fields Territory1, Territory1EffectiveDate, Territory2, Territory2EffectiveDate, etc.  Type 3 is rarely used

Note that Type 0 means a record is never updated – it remains exactly as it was when it was first created.  There is actually a Type 4 and a Type 6, and some “combination” types, but I have never seen those used so they are not worth discussing here.

More info:

Dimensional Modeling, Role Playing Dimensions!!! and Slowly Changing Dimensions!!!! and
Slowly Changing Dimension Part 2

What’s Data Warehousing

Slowly Changing Dimensions Part 1

Perfectly Partitioning History With The Type 2 Slowly Changing Dimension

Posted in Dimensions, SQLServerPedia Syndication | Leave a comment