13 Jan

Winnipeg SQL Server User Group – January 2012

Last night, I attended Mike DeFehr’s presentation at the monthly Winnipeg SQL Server Users Group meeting, held at the offices of Online Business Systems. More importantly, given Online’s location buried in the middle of one of the worst areas of the cesspool known as downtown Winnipeg, I escaped without incident. I was not stabbed, shot, or even accosted. There’s got to be a first time for everything and this was it.
Upon arrival, I found the doors locked. There was a sign directing any attendees to call D’Arcy at a certain number if the doors were locked, but I happen to be one of the last of a dying breed, or so it seems, without a cell phone. While hunting around for a pay phone, a group of people were leaving, so I got in when they opened the door.
With the aroma of free pizza in the air that lingered in my nostrils hours after the meeting, Mike went through a very informative presentation on the new features coming in SQL Server 2012, code-named “Denali”, scheduled for release some time in the first half of this year.
For starters, he talked of the new “AlwaysOn” availability features. There will be new mirroring features and applications connecting with “read-only intent”, as he put it, will be automatically redirected to a read-only server. I presume this will involve a new parameter in a connection string.
There will also be an option for failovers in groups, but it will require installing SQL Server on an Enterprise version of Windows that is a member of a Windows Server Failover Cluster. In this case, SQL Server will leverage the Windows clustering technology rather than provide its own.
All the “classic” methods of database mirroring will still be there, and, to the best of Mike’s knowledge, are not being deprecated.
Turning to security, the ability to create user-defined server roles will be introduced. Mike also demonstrated another new feature called the contained database, where the security is contained within the database without the need to map a database user to a login object.
There will be a new feature called FileTable, built on the FILESTREAM concept, which will allow access to specified directories on a file share using SELECT queries without having to resort to xp_cmdshell. Furthermore, the contents of individual files will be accessible through T-SQL and SQL Server’s full-text search capabilities can also be utilized on individual files, not just table content. Mike was unaware as to whether this feature will be available in all editions of SQL Server, so that’s something to investigate for those that are interested.
As expected, the GUI tools are based on Visual Studio 2010 and there will be a new set of data tools available. There will be integration with source control, a new table designer, and these tools will be available in all editions, including the free Express edition.
There is a potentially very valuable feature called Apollo, which allows for a ColumnStore index on a table. In this case, the index is created by columns, not by rows. The results are dramatic improvements in performance and speed of some complex queries, but there is a catch. Not only does putting a ColumnStore index on a table make it read-only, but the creation of a ColumnStore index requires a lot of server resources.
Mike wound up the presentation showing us some additional useful features. There will be built-in support for paging with an OFFSET clause in a SELECT query, the ability to create sequences, much like Oracle, and new T-SQL functions such as TRY_CAST, TRY_CONVERT, EOMONTH, and IIF.
It was definitely worth the trip, and for and professional in IT who works with SQL Server, I would highly recommend attending any future meetings. Mike is a highly-skilled SQL Server guru and professionals of all levels of experience can learn from his presentations. More details on the group can be found at http://winnipeg.sqlpass.org.