SQLblog.com - The SQL Server blog spot on the web

Azure HDInsight Preview–Be Warned or you CC will suffer…

Davide Mauri — Sat, 25 May 2013 12:49:37 GMT

As soon as I had the possibility to test HDInsight on Azure I promptly started using it. Nothing really exciting, just creating the cluster and do some tests following the official tutorial you can find here:

http://www.windowsazure.com/en-us/manage/services/hdinsight/

I’m using my MSDN MVP subscription for which I have 1500 hours of “Compute Services”. Well, be careful to keep your HDInsight cluster turned on. Even if you don’t use it it will consume resources. The the resource created behind the scene is a “LargeSKU” VM, as the detailed usage report that you can download says.

And, wow, look! In just less then a week it has consumed ALL my available hours, and thus my CC started to be drained. Non that much, luckily, just a hundred of bucks (which is also not so few in this times of crisis) and even more luckily I’m very careful to monitor Azure expenses often since I still not trust the pay-per-use system so much to just leave it alone and without constant supervision. And as soon as I discovered that there was something strange going on I shout the HDInsight cluster down immediately.

Unfortunately the expenses are reported under the generic “Compute Hours – Cloud Services” summary so without downloading the detailed billing report and analyzing it with Excel was impossible to me to understand that the resources consumption came from the HDInsight cluster.

That’s why took me two days to understand the problem (at the beginning I thought the problem was my website) but on May 24th I finally understood what was happening.

To be honest this is the only case where I had such bad surprise (I’m also using VMs, Web Roles and SQL azure and I never consumed more then what I expected so far so I’m not blaming MS at all here), but this is a lesson learned that I want to share with the community, hoping to help someone to avoid even worse surprises.

The feedback to MS I’d like to give is that it would be very good if, at least for the “preview” features, there could be a specific settings to decide how much resources they could use before they are automatically shut down.

And the conclusion, on my side, is that, at least for now, playing with Hadoop on this area of the Big Data universe is better done on-premise on the spare machine I have in the office.

Of course what happened could also totally be my fault, even if I just did everything following the tutorial, but if someone with more experience on Azure HDInsight would like to leave a feedback I’ll be more than happy the ear it.

So…keep your eyes open and your money safe

Another feedback to MS: it would be very very very nice if the billing details can be accessed via OData. It would be a perfect match with PowerPivot capabilities!

New On-Line Resources for Windows, Virtualization, and Cloud!

KKline — Wed, 22 May 2013 19:29:00 GMT

Here are six new and useful tips to improve your skills with Microsoft Windows and virtualization....(read more)

Speaking - Tech Ed, Tech Ed, SSWUG, PASS

AllenMWhite — Wed, 22 May 2013 18:48:00 GMT

I feel like I've hit the speaker's lottery this year, starting with SQL Cruise Miami, and it just keeps getting better! After a great trip to SQL Bits I'm now preparing for my travel to New Orleans for more great SQL fun! In early June I'll be speaking at Tech Ed North America , this year in New Orleans. I'll be presenting the same session later in June at Tech Ed Europe , in Madrid. Here's the session details: Maintain SQL Server System and Performance Data with PowerShell Maintaining a solid set...(read more)

Not selected as a speaker at PASS Summit 2013 #sqlpass #sqlfamily

Marco Russo (SQLBI) — Wed, 22 May 2013 13:05:00 GMT

I received the communication from the PASS Program Committee that none of my proposals have been accepted at PASS Summit 2013. Probably this year there was too many proposals and I made a mistake not using all the possible proposals available per speaker. In fact, I just proposed one pre-conference day and two regular sessions:

From 0 to DAX (preconference)
DAX is the new language for creating Business Intelligence in the Microsoft stack. You can use it to query and program both Microsoft PowerPivot for Excel 2013 and the new SQL Server Analysis Services Tabular model. Learning the basics of DAX is very simple, but mastering it is a different story because it requires a mindset that involves some study, both for BI Professionals and Information Workers.
During this one-day workshop, we will guide you in the process of learning the DAX language, through many examples and scenarios that help you understand what are the important concepts of DAX and how to leverage them in your everyday work.
The seminar is useful to many profiles: BI developers who want to learn the DAX language and information workers interested in exploiting the many interesting features of PowerPivot for Excel 2013.
Optimizing Data Models in Tabular & PowerPivot (regular session)
Is your new Tabular solution performing at its best? Are you using the best practices to reduce memory footprint, increase query speed and get the best out of the new engine? In this session, we will look at several techniques that can really make the difference in a Tabular solution. Distinct count reduction, join optimizations, condition consolidation, pros and cons of normalized data models, selection of the columns to store in the database are some of the topics covered in this session, which will highlight not only the best practices, but also practices that were best in Multidimensional and became worst in Tabular. As we are used to teach: if you known Multidimensional, you need to forget it in order to let the new concepts of Tabular shape your model.
Time Intelligence with DAX (regular session)
Time Intelligence is probably the most used feature of any analytical solution. Computing Year To Date, Month To Date, Same Period Previous Year is quite easy in DAX. However, the DAX formulas start to be harder to write as soon as the requests for time intelligence involve working days evaluation, complex and custom calendar shapes, seasonal pattern recognition, ISO-based weeks handling, 445 fiscal calendar.
In this session, we start showing how to compute the classical time intelligence by using the built-in DAX functions. Then, we will see some more complex time-intelligence formulas that require thinking out of the box, using advanced data modeling and querying techniques to produce interesting and useful formulas.

I have been informed that this year a new selection process has taken place. Abstracts reviews and speakers reviews have been made by two separate teams, and a final judgment has been made by combining the two ratings. In particular, abstracts team didn’t know the speaker’s name of each session. It seems a smart way to handle 850 submission in a transparent way. Unfortunately, I wasn’t aware of this new process.

In the last years, I and Alberto Ferrari had too many sessions selected, so the board decided to assign us only a few of them and the selected ones wasn’t always the best ones (because it wasn’t possible for us to establish an order of preference in case more submissions were selected). For this reason, this year we limited our submissions to the sessions that were more likely to be interesting, considering the current request on the market, the questions we received usually and the need to avoid presenting something already presented in the previous year. So we made a small selection of sessions avoiding to submit the maximum number of sessions per speaker. But this has been a wrong choice, because based on the new evaluation system, it would have been certainly better to propose more sessions and not only in the area in which we are most specialized.

I am not saying I have proposed sessions that was better than others selected. I have seen the proposals here and there are very interesting sessions (I still don’t know which ones have been selected at the moment of writing). I would have just proposed at least other 2 regular sessions more advanced and focused on specific use cases. Being part of a board selecting sessions in other conferences (like this) I know that every selection process has some drawbacks and the best thing you can do is to communicate very well how the selection process will work, so that everybody is able to help the board producing the best proposals. I thought that proposing a lower number of session would have been better, but I was wrong.

Well, I have been a speaker at PASS Summit in the last 4 years in a row (2009, 2010, 2011, 2012), so it’s probably time to take some rest from speaking!

May 2013 Cumulative Update for SQL Server 2008 SP3

AaronBertrand — Tue, 21 May 2013 12:14:00 GMT

Microsoft has released SQL Server 2008 Service Pack 3 Cumulative Update #11 Build # 10.00.5840 KB Article: KB #2834048 6 fixes Relevant for builds 10.00.5500 -> 10.00.5839 NOT for SQL Server 2008 R2 (10.50.xxxx)...(read more)

Huge news: Azure expanding to Australia!!!

Greg Low — Mon, 20 May 2013 22:31:00 GMT

I was so glad to hear today that Azure is expanding to Australia. This helps with two remaining areas of concern that I've heard from a variety of customers:

Compliance and data sovereignty (not wanting to store data outside Australia)
Latency (previously high latency even to our nearest external data centres)

With both these concerns now disappearing, it's time for more Australian customers to get involved with Azure if they've been resisting so far.

Two Azure sub-regions are to be added. One for New South Wales and another for Victoria. In addition, data geo-replication between the sub-regions will also be available.

AdventureWorks on Azure now hosted by Red Gate software

jamiet — Mon, 20 May 2013 21:48:39 GMT

In March 2013 I launched an initiative called AdventureWorks on Azure in which I hosted the AdventureWorks2012 sample database on Azure so that the SQL Server community at large could use it to get familiar with what ~~SQL Azure~~ Windows Azure SQL Database had to offer. I asked the SQL community to support the initiative by donating money to keep it afloat and that community responded in spades to the point where last month I was able to make a donation to the War Child charity on behalf of that community. In the interim period I added more sample data to the pot, made Northwind available, and also data related to Olympics medal tables down the years.

Today AdventureWorks on Azure takes on a new guise, Steve Jones of SQL Server Central fame announced via a press release that hosting of the database is now to be taken on by his employer Red Gate Software. Here’s what Steve had to say in today’s announcement:

One of the goals of Red Gate Software is that we not only sell products to the SQL Server community, but that we also participate in the community. In line with our goals of participating in the community, we have agreed to host the AdventureWorks 2012 sample database on the Azure platform.
Red Gate has worked to transition this set of databases to our own subscription and will assume the financial responsibility for making this available for the community to use.
http://www.sqlservercentral.com/articles/Adventureworks/99290/

This is great news. I no longer have to worry about finding the funds to keep the initiative afloat and the community can feel comfortable that this will be around for the foreseeable future, so thank you to Steve and red gate for this undertaking. Special thanks must also go to David Atkinson at Red Gate who first suggested that they take this on.

I have high hopes that Steve & Red Gate will build upon this with more offerings for the community and equally I also hope I can stay involved somehow. Watch this space!

@Jamiet

How Far Can You Push Tabular? Birds of a Feather at TechEd 2013–North America #msteched #ssas #tabular

Marco Russo (SQLBI) — Mon, 20 May 2013 13:15:00 GMT

I and Alberto Ferrari will moderate the following Birds of a Feather session at TechEd North America 2013:

CODE: BOF-ITP21
TITLE: How Far Can You Push Tabular?
SPEAKER: A. Ferrari; M. Russo
TIMESLOT: June 5, 2013 at 1:30 pm
ROOM: 263

Abstract: Tabular is the new engine in SQL Server Analysis Services. It is an in-memory columnar database capable of unprecedented performance but… how far can we go with an in-memory database? Will you be able to load your data warehouse in-memory to obtain the best performance? What should you care about when building the DWH? What does “fast” mean? What about real-time data? Can you query many billions rows really fast? In this BOF we will show some theory and share some of our experience, but we will welcome your questions and experience, to build a round-table of experts in order to speak about this fascinating topic.

I really look forward to this event – I and Alberto have some experience using Tabular in many different scenarios, but every time we meet new people at conferences we discover that there are new challenges ahead. This session will be the place to discuss together where are the limits today. Much far than many people realize, in my opinion!

Why We Write #5–An Interview With Jason Strate

drsql — Mon, 20 May 2013 02:40:59 GMT

My next guest is a person I have known for years, and have worked with on several occasions, Jason Strate ( @stratesql ). Jason is a very active writer and speaker (at my first SQL Saturday event, he spoke four times!), and always seems very busy. His blog ( jasonstrate.com ) had 23 posts just last month, and his twitter account is always active with interesting SQL and non-SQL tweets. If the blogs and tweets weren't enough, just this past year, Jason was a coauthor on one book last year ( Expert...(read more)

SQL Saturday #220 (Atlanta): Demos

Adam Machanic — Sat, 18 May 2013 18:33:00 GMT

Today at SQL Saturday #220 in Atlanta I presented a new brand new session , "SQL Server Query Plan Analysis: The 5 Culprits That Cause 95% of Your Performance Headaches." This session is designed to help people quickly analyze query plans and find likely culprits without being query tuning experts; I find that in a huge number of cases the root cause of problems is one of just a few potential situations. Thanks to everyone who joined me today for the deliveries! In addition to it being a new session...(read more)

SQL in the City Event Dates Announced!

andyleonard — Sat, 18 May 2013 06:00:00 GMT

Red Gate rocks. If you didn’t know that already, you know it now. The latest evidence to support this claim is the publication of the schedule for US SQL in the City events. They are: 9 Oct – Pasadena 11 Oct – Atlanta 14 Oct – Charlotte Registration for these events opens 24 May. :{>...(read more)

Announcing the 2013 Biml Workshop 15 Oct 2013 in Charlotte NC!

andyleonard — Sat, 18 May 2013 04:00:00 GMT

When : October 15, 2013 8:45 am - 4:45 pm Charlotte, NC Where : Wake Forest University Charlotte Center 200 North College Street Charlotte, NC 28202 What : Business Intelligence Markup Language (Biml) automates your BI patterns and eliminates the manual repetition that consumes most of your time. Come see why BI professionals around the world think Biml is the future of data integration and BI. Registration is just $69. Register before July 15th and receive early bird discount of just $49. Breakfast,...(read more)

sp_spaceused Alternative

Andrew Kelly — Fri, 17 May 2013 20:35:00 GMT

I don’t know why but for some reason I have never liked using sp_spaceused. It probably started a long time ago when the results were often inaccurate due to the meta data being out of date. I am pretty sure that was fixed somewhere along the line but the system stored procedure had some other limitations that usually prevented me from seeing the data that I really wanted in most cases. So at some point I created my own stored procedure to view the data I was most interested in almost on a daily...(read more)

Join Matt Masson and I for Developing Extensions for SSIS 22 May at 11:00 AM EDT

andyleonard — Fri, 17 May 2013 12:44:10 GMT

Matt Masson and I are co-presenting Developing Extensions for SSIS 22 May 2013 at 11:00 AM EDT. If you’ve never heard Matt present, you are in for a treat. Matt is knowledgeable (he helped build Integration Services 2012!) and entertaining. This is going to be a good one, folks! Abstract Join Matt Masson and Andy Leonard for a discussion and demonstrations on extending SSIS with custom tasks and data flow components. This code heavy session walks you through the creation of a custom SQL Server Integration...(read more)

Private Cloud and Virtualisation Strategy Event - Brisbane, Sydney, Melbourne

Greg Low — Fri, 17 May 2013 03:50:00 GMT

Fellow MVP Alessandro Cardoso sent me information today about a private cloud and virtualisation event that's running later this month.

It's being offered in Brisbane, Sydney, and Melbourne.

If that's of interest, you'll find more details here: http://virtualisationandmanagement.wordpress.com/2013/05/08/private-cloud-virtualisation-strategy-events-in-brisbane-melbourne-sydney-may-and-june-2013/

Reflections on SQLBits XI

jamiet — Thu, 16 May 2013 22:03:50 GMT

Its been a couple of weeks since SQLBits XI happened in Nottingham and I thought I’d jot down a few thoughts for posterity.

First the venue. I think its fair to say that the overall consensus was that the East Midlands Conference Centre was the best SQLBits venue these has been so far – I’ve been to more than half of them and based on my experience I would agree with that sentiment. The hotel especially was top quality – I was pleased with my room and the breakfasts were way better than the Travelodge standards I’ve become accustomed to at such events. Perhaps the great weather over the weekend helped lift the spirits but I’d say the bar has been set high, I hope future SQLBits conferences are at similar standard venues.

You may have noticed that many of my blog posts over the past year have been related to SSDT and my SQLBits sessions this time around followed that trend. I delivered a session on the Friday, jointly with Craig Ottley-Thistlethwaite, entitled “Real World SDDT” and also delivered my first ever day-long pre-conference seminar on the Thursday entitled “SSDT Database Projects from the ground up”. Delivering a pre-con was slightly petrifying and I spent a large part of the three months previous preparing for it; I hope it was worth it. I had 30 attendees which I was delighted with (especially given this was my first pre-con) and we established that the furthest anyone travelled was from Romania, though I’ll assume it wasn’t just so he could come to my pre-con . I had some pleasant feedback via Twitter afterwards from some of the attendees and I’m crossing my fingers that the official feedback is in a similar vein.

The session I did with Craig went fairly well I thought. I did the first 30minutes where I covered the basics of SSDT deployment from a high level before Craig ratcheted the complexity up a few notches by demoing some interaction between SSDT, MSBuild & Git – really great stuff for those who like to get into the nuts and bolts of this stuff. I was delighted that Craig was willing to do the session with me (in fact it was his idea) as this was his debut public speaking gig and I’m hoping its given him the desire to do more in the future.

On the Saturday my wife’s uncle, John Milne, came to the conference. John has been working in customer service for years but of late has decided that he wants a change of career and to that end has been studying an Open University course in IT. He told me he had particularly enjoyed the database-focused modules of his course and hence I suggested he come to the free Saturday of SQLBits to try and get a flavour of what the industry is all about and perhaps learn about some real-world experiences to add to his academic travails. By the end of the day John told me he’s had a fantastic time, learned a lot, and was hooked. Mission accomplished I’d say. John lives in Leeds so I introduced him to Chris Testa-O’Neill who helps to run the Leeds user group and John should be going along to some user group events in the near future – if you happen to meet him there please welcome him into the fold!

All that remains for me to say is a massive thank you to the SQLBits committee who do such an amazing job, all voluntarily, in putting this all together. Thank you Simon Sabin, James Rowland-Jones, Chris Webb, Darren Green, Allan Mitchell, Tim Kent, Chris Testa-O’Neill & Martin Bell. I also want to thank all of the volunteer SQLBits helpers that worked tirelessly on the weekend to make sure the whole thing ran smoothly.

Bring on SQLBits XII!

@Jamiet

INSERT SELECT is Broken. Ask Microsoft to Fix It.

Adam Machanic — Wed, 15 May 2013 16:42:00 GMT

Imagine that you're moving thousands or millions of rows between two tables . Maybe it's between a staging table and a data warehouse in an ETL process. Maybe you're manipulating some data via a temp table as you're preparing lookup data for your OLTP system. Maybe you're preparing data for an end user. It doesn't really matter what your use case, because there are so many of them. We, as database developers, spend all day moving data back and forth . Unfortunately, sometimes our processes break...(read more)

Part of the journey: failure

Rob Farley — Tue, 14 May 2013 00:09:00 GMT

The topic for this month’s T-SQL Tuesday is about the journey. Wendy Pastrick’s choice (I’m hosting again next month!).

There are a lot of journeys. There are some that just keep going, and others that seem to finish (some in success; some in failure). Of course, many of the ones that finish end up being the start of new journeys, but sometimes they don’t need to continue – they just need closure. There are things that can be learned regardless of how things went, whether or not goals were reached, and whether or not there was failure.

There’s been a few things recently to remind me of this...

I visited a company recently who has put a video together promoting the idea of failure. It wasn’t asking that people fail, but said “Go ahead and fail,” because failure happens. They had been through a rough time, but were persisting and seeing things turn around.

Just the other night, we saw the musical Chitty Chitty Bang Bang (you probably know the movie – the Bond film where Dick van Dyke stars as the guy with the gadget-car, who takes on Goldfinger and falls for the girl with the inappropriate name). Anyway, there’s a brilliant song in that called “The Roses of Success” (YouTube link there). It has the same sentiment – “…from the ashes of disaster grow the roses of success!”

A few years ago, my kids started saying “FAIL!” when someone did something wrong. I can’t say I liked the insult. Far worse would’ve been “DIDN’T TRY!” It would be very easy to just stay in bed and ‘avoid failure’ that way, but anyone who fails has at least done something. To fail, you must at least be active.

I talk to a lot of people about Microsoft Certification, particularly people who have failed an exam. I tell everyone (not just those who have failed before) to try the exams before they feel they’re ready for them, . What’s the worst that can happen? Worst case, they don’t pass. But how is that a bad thing? It might feel less than brilliant (I know, it’s happened to me before), but it gives an opportunity to target the weak areas before having a subsequent attempt. It doesn’t matter how many attempts it takes to get a passing score – the wrong option would be to give up. Studying can be excellent, but not to the point of causing extra stress.

There are things in life we do easily, and there are things that we struggle with. I know there’s a bunch of stuff in my own life that falls into both categories. I don’t want this post to be a list of the things that I’m not doing well – I simply want to point out that I want to keep trying.

With God’s help, I can improve in the areas in which I’m not excelling, and start to smell the roses of success.

Israeli SQL Server Usergroup: Locking & Blocking in active environment

Michael Zilberstein — Mon, 13 May 2013 13:38:47 GMT

A week ago I gave my session on the subject. It is only first part of the two – second will be next time when there is an open slot. Demos from my session are available here ....(read more)

New Article on the SQL Server 2012 Backup and Restore PowerShell Cmdlets

AllenMWhite — Mon, 13 May 2013 13:08:00 GMT

While I was on vacation last week in Scotland Simple Talk published a new article I wrote called Backup and Restore SQL Server with the SQL Server 2012 PowerShell cmdlets. Hope you have as much fun with it as I did writing it. Allen...(read more)

SQL Down Under Show 59 - Guest Reza Rad now available for download

Greg Low — Mon, 13 May 2013 00:51:00 GMT

Hi Folks,

On Thursday night, I got to record a podcast with Reza Rad. I was interested to speak to Reza after reading his SQL Server 2012 Integration Services Cookbook.

In the show, we discuss SQL Server Integration Services (SSIS) extensibility and some aspects of performance tuning for SSIS.

You'll find the show here: http://www.sqldownunder.com/Podcasts

Enjoy!

Excited to be speaking at #SQLSATATL next weekend

dbaduck — Sun, 12 May 2013 05:06:00 GMT

I will be on for speaking next week at SQL Saturday #220 #SQLSATATL. My sessions will be on SQL Server TDE and SMO Internals for High Performance PowerShell. Both are great fun to present and good information to have. Hope to see you there in Alpharetta, GA....(read more)

Enterprise Storage Systems - EMC VMAX

jchang — Fri, 10 May 2013 15:52:00 GMT

I generally do not get involved in high-end SAN systems. It is almost impossible to find meaningful information on the hardware architecture from the vendor. And it is just as impossible to get configuration information from the SAN admin. The high-end SAN is usually a corporate resource managed in different department from the database. The SAN admin is generally hard set on implementing SAN vendor doctrine of "Storage as a Service" and does not care to hear input on special considerations from...(read more)

Performance-Based Management and Andy’s Law

andyleonard — Fri, 10 May 2013 13:29:00 GMT

Let’s begin with an assertion: “People are more important than process.” – Andy, circa 2008 Whenever an enterprise or institution adopts a new process or policy, that policy should serve people and not shackle them. The nicest thing that can be said about a policy that binds people is, “It wasn’t well thought out.” It’s a bad idea, in other words. What’s the logical thing to do when we encounter a bad idea? Reverse it, as quickly as possible. Is it a good idea to hang on to the bad idea because (hypothetically)...(read more)

SQLCLR Performance Session at TechEd US

Adam Machanic — Wed, 08 May 2013 16:14:00 GMT

I am super-excited to visit New Orleans next month for Microsoft TechEd; it will be my sixth time speaking at the show. My session takes an in-depth look at some of the techniques I've developed for using SQLCLR modules -- and some of the great performance gains I've been able to achieve. Hope to see you in NOLA! If you're not attending the show, the video will be available on demand a few days after I give the talk....(read more)