The Power Query story keeps getting better

It seems like anyone that's talked to me this year has heard me going on and on about how Power Query is just the nicest new bit of tech that's come from Microsoft lately. We cover it as part of our one-day Power BI Core Skills training class and as part of day 2 in our BI Core Skills classes that we recently updated for SQL Server 2014. Out of all the technologies that people see during that week, Power Query is the one that seems to promote the biggest discussions.

It's also a product that keeps getting improved constantly. Another new update for Power Query is out and you can find it here: http://www.microsoft.com/en-us/download/details.aspx?id=39379&WT.mc_id=Blog_PBI_Announce_DI

Here is a video that shows what's been updated: https://www.youtube.com/watch?v=Q9boOzu298Q

The blog post from the team that shows what's changed is here: http://blogs.msdn.com/b/powerbi/archive/2014/06/26/6-new-updates-in-power-query-june-2014.aspx

For me, the one big thing that Power Query is now missing is a corporate story beyond Excel/Power BI. The queries that you create in Power Query are actually M language queries. This is a very capable language (unrelated to what the industry used to call the M language), that Microsoft have documented here: http://go.microsoft.com/fwlink/?LinkID=320633

I would so love to be able to take the queries that I generate in Excel and paste them into an SSIS data source, or use them as a data source in SSAS tabular. Once those types of options appear (surely they must), this is going to be even more of a killer application.

SQL Down Under Show 63 Jimmy May – Clustered Columnstore Indexes in SQL Server 2014

It was great to catch up with Jimmy May recently. Jimmy is a principal database architect at Microsoft and was part of the SQL CAT team (customer advisory team) for quite a while.

 

We recorded a new SQL Down Under podcast.

 

In this podcast, Jimmy discusses his experiences with columnstore indexes and the new updatable clustered columnstore indexes.

 

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

 

Enjoy! 

Easier to Implement SQL Server Availability Groups on Azure

With each update to Azure, it’s been getting easier to implement options for SQL Server. One of the challenges for the teams that write documentation and training is this rate of change.

For a long time, Availability Groups weren’t able to be implemented properly on Azure as there was a limit of one IP address per VM. That problem was fixed some months back.

I recently needed to set up Availability Groups wholly within Azure. I followed the documentation here:

(Tutorial for setting up Availability Groups in Azure)  http://msdn.microsoft.com/en-us/library/dn249504.aspx 

and the documentation here:

(Tutorial for adding a listener)  http://msdn.microsoft.com/en-us/library/dn376546.aspx 

which has just been updated here:

(Tutorial for adding a listener)  http://msdn.microsoft.com/en-us/library/dn425027.aspx

As I was reading the listener tutorial, I noted that the tutorial used Powershell for creating the endpoint as there was no GUI based method for enabling Direct Server Return on the endpoint. I thought “I’m sure I’ve seen that”. And this is the challenge for the documentation. In the portal, when I was creating the endpoint, the following dialog appeared:

image

This is wonderful in several ways. Not only has the option been added, the popup help text is actually very helpful, and also provides a link to details related to how to use it. (I’m a firm believer that help should first and foremost be helpful). Kudos to whoever implemented that.

The one remaining real roadblock for Availability Groups in Azure was the use of them for disaster recovery. The VMs involved needed to be contained in the same VPN. That’s fine for local high availability and for read-only replicas for load balancing but it’s not OK for disaster recovery where we want a secondary VM to be in another datacenter in another region.

That roadblock has also been removed now. The Azure team added support for interconnecting different regions via secure tunnels. This is documented here:

http://msdn.microsoft.com/en-us/library/azure/dn690122.aspx?WT.mc_id=Blog_SQL_General_DI

This allows you to construct a VPN like this: (image from the article)

 

image

In fact, with the site to site connectivity, you can also combine your on-premises systems as well: (image from the article)

image

 

 

This is important news for those wanting to implement Availability Groups in Azure, with a need for DR. Support for this has been announced:

http://blogs.technet.com/b/dataplatforminsider/archive/2014/06/19/sql-server-alwayson-availability-groups-supported-between-microsoft-azure-regions.aspx

And the guidance for using it has also already been updated here:

http://msdn.microsoft.com/en-us/library/jj870962.aspx?WT.mc_id=Blog_SQL_General_DI

Kudos to all involved in making this possible so quickly.

Azure now in the leader quadrant for IaaS from Gartner

Gartner tends to publish magic quadrant leader boards related to a variety of technology areas.

It was interesting to note that the latest leader board has Azure moved up into the Leader quadrant. The only other player in that quadrant is Amazon. That's a big step up for the team, given the IaaS business really only went to GA in April last year.

You'll find details here: Gartner Report