Today was Microsoft Day.  All the sessions I attended were based on the new features and changes that will come in the next edition of SQL Server (Code name Denali).  The conference was kicked off by Christian Kleinerman. Christian has been working for Microsoft for a number of years and most recently has been involved with the Data Warehousing side of things for SQL Server.  Microsoft is really focused on shifting user and business expectations.  They understand that the future databases are going to be a great deal larger and to make those larger databases more effective they needed to help make a lot of things better. 

One of the biggest and most exciting features is the Column Store index.  This index is a new type of columnar nonclustered index.  You can only have one column store index on a table but you can name as many columns as you want.  It uses the Vertipaq technology from PowerPivot which is a client side in memory structure that handles the pivoting of data into what are called segments.  These segments are groupings of 1 million values and the data are stored as blobs.  This type of technology is expected to yield at least 10 times the performance for returning data.  This efficiency does come with a few drawbacks though.  For now, the columnar indexes are suited for tables that have mostly static data.  You can also only use common SQL server data types.  You also can’t change the data afterwards…although there are a few workarounds for this.  You can of course drop and recreate the indexes, but another means to avoid this drawback is to partition your table and then bring new data in and out by swapping out the partitions.  Also you can just do a union all with other tables or subsets etc.  None the less I am very excited to start playing with the new columnar index.  I will follow up with more posts on the feature later.

Another new feature is AlwaysOn.  AlwaysOn is an enhancement on the database mirroring technology and merged it with clustering and then used for high availability.  Previous to mirroring, which came out in SQL Server 2005, you only had clustering, log shipping or replication to support your needs of scaling out our processing power and high availability needs.  Previous versions of the mirroring were basically just standby versions that you couldn’t query or use until you switched over to it…that is unless you were using snapshots on top of the mirror.  The snapshots worked, but the whole process of maintaining them over the mirrors were cumbersome, and you could only use them for two SQL servers.  Now with AlwaysON, it is like mirroring but you can use it on many different servers and databases via Availability Groups that you can query against and move over to individually and use for scaling out your performance intensive queries.  You can add in a node for availability, scaling out to many other nodes by having secondary or more that are kept up to date with synchronous updates across great distances.  This is really cool, but in a way it was really needed after they took away the read only log shipping capabilities in 2008.  All in all a great edition and I welcome Microsoft’s initiative to recognize the needs of having synchronous copies of our databases for use.  Brent Ozar has a more thorough review of the technology here

Microsoft is also starting to deploy what are called Parallel Data warehousing products as an “appliance”.  When I say as an appliance, I mean they are taking all of the installation, hardware setup, configuration, and best practices and then bundling it all together in one package that they sell you that also includes all the servers, data storage, backup devices etc.  They have a few of the largest vendors in their program, but HP is the currently the front runner.  Essentially, you just order everything in one big package as a whole and you should be ready to handle all your data warehousing from the one package as an appliance.  Supposedly, you just add data.  This was the first I heard of such a thing, but it has been in the works since the end of last year and unveiled it in SQL Server 2008 R2.  They also now have a Fast Track package available which is basically a road map or “Cook Book” if you will that uses various vendor offerings and they tell you how to set everything up yourself using all the best practices and vendor specific details.  This is just the “Recipe” for success and you build and support it yourself on your own hardware and environment.  You can read more about it here.

Also announced were numerous T-SQL enhancements.  Denali will add what are called “contained databases” These databases include all the user and login metadata information all together with the database.  Gone are the days of worrying about orphaned users and matching up the logins and users.  Pretty cool.  They added in Throw statements which are used with errors, a new paging syntax with fetch offset, sequence generators, and metadata discovery.  There were also a number of other enhancements and offerings described that are listed here.

The final session of the day was Maciej Pileski’s SQL Server User Defined Functions, The Good, The bad, and the Ugly.  I have to say first, that Maciej is one of the most charismatic and entertaining presenters I have ever met.  He described the material effortlessly and with such high caliber quality.  I couldn’t believe that someone could make a session so informative and exciting at the same time.  Maciej described all the kinds of functions we have in SQL Server and listed out some performance issue we need to be aware of in their use.  He described why using scalar functions can be very performance intensive and showed just how misleading an execution plan can be.  In example, the execution plan can show 0% cost but if you take a look at the statistics IO or profiler you will see boat loads of activity and it executes RBAR(Row by Agonizing Row) for its operations.  Next he described tabled value functions and their use when inline, multi-statement, or by using CLR.  When we use inline they act as a view with a parameter.  When we use multistatements they act like a stored procedure but they only assume one row is returned so they are basically a “BLACK BOX.”  When we use CLR TVF’s we can take advantage of regular expressions.  By far this was my favorite session of the day. 

All together this was an extremely great start to the conference.  I learned a lot about the new features and I am itching to get cracking on them as soon as I can.  To top the day off I had dinner with Brent Ozar and Maciej Pilecki.

WOW!!!!  Where do I begin???  It’s 1 AM and I just got back from eating an amazing dinner at the Vineyard Grill at the Ritz Carlton.  Beautiful restraunt, nice atmosphere, and really really great food.  I have to admit having just a steak dinner here alone, is enough to make anyone’s night, but to get the chance to sit down and chat with two of the world’s database masters, Maciej Pilecki and Brent Ozar for the evening was simply priceless.   A lot of people will hear this, pause and just think, REALLY?  Well, I am delighted to say that we had a blast together.  They are two of the most down to earth and truely likeable guys you could ever meet. 

What blew me away most was discovering just how incedibly passionate and devoted these folks truely are about databases.  I knew they were passionate and knowlegeable already, but they really eat, sleep, and breath databases all day long and it’s in everything that they do.   To accomplish what these guys do everyday, and the way they do it, is simply amazing.  It’s hard enough to find good database people in this world nowadays, but these two are really electrifying in their presentations as well.  Their deep level of understanding is second nature to them, and they make the most bland and mondane subjects, enjoyable for everyone no matter the knowledge and experience. They break down the most complex structures, so it’s so easy to absorb and remember, forever.  What was the most interesting thing for me though was listening to them talk about their families and their lives in general.

I am a family guy, married and I have two little girls.  I could never imagine making the level of commitment that these guys do everyday. From preparing interesting material for presentations, blogging, twitter, reaching out in the community, writing books, and not to mention all the traveling that they do, all while balancing and maintaining their family life too.  Amazing!  Maciej Pilecki has more airline miles, accumulated in three months alone, that I have ever had in my entire life!  And this is a guy is married to a lovely wife, whom I also met tonight, and they have three small children.  They are from Poland, which my wife is also from, so it was nice to chat about the old country for a while, and hear all about how the two of them met.  Brent has no children at the moment, but has been together with his girlfriend for 11 years.  He hasn’t tied the knot just yet, but I have a feeling it just may be coming soon.  We will see.

On to the sessions today…all in all, they were all really good.  They had really good presenters, and then they had just off the wall really great presenters like Brent and Maciej.   Today was a Microsoft Day, so it was focussed on enhancements and new features of SQL Server Denali, the next version of SQL Server.  Microsoft’s focus for this edition is going to be all about changing expectations.  And to do that, they added a number of features and offerings.   I was excited to learn more about SQL Server AlwaysON, the addition of a number of TSQL enhancements, like contained databases, parallel datawarehouse (Microsoft’s DW as an appliance) etc., but the most intriguing new feature for me was the new index type called columnstore.   The columnstore index is really going to be a game changer. 

I will write more about them all and the individual sessions too, later in greater detail, as I need to get to bed now and prepare for day 2.  I can’t believe it’s only been one day so far, and I still haven’t heard sessions with Paul Randal and Kimberly Tripp.   This conference has been really special thus far, and it really is amazing to know it has only begun!

I’ve been so busy working on a number of projects, and I appologize for not making the time to post more frequently, but you all know how it is.  I will be heading to Orlando this weekend for the DEVConnections Conference, and I figured I will start back up with a few new posts on the conference since it is going to be choc full of lot’s of goodies.  I am really looking forward to meeting some of the Database Gurus in person. Perhaps I can even make up with some of the ones that I frustrated a little bit last year too. It looks like it’s going to be a really great conference with so many great presentations, so I should have lot’s to report back on each day. Some of the features I am really interested in learning more about, will be the SQL Server “Always ON” features and some of the SAN and Virtualization best practices. The schedule for the DevConnections conference and a description of the sessions can be located here.   Let me know if you have any specific questions on any of the sessions and I will try to get your questions answered for you here on my blog.  Ta-Ta for now.

Red Gate software has recently added SQLTech Consulting as a Friend of Red Gate.   This will enable SQLTech to beta test and review all of the latest and greatest database tools before they are released.  We are extremely excited about this new partnership and it will prove extremely helpful for keeping our knowledge and expertise for our clients in the forefront of technology.  Red Gate will also sponsor some events and provide us with various software and other swag for us to give out to our event attendees.