The Bit Bucket

Solving Errors with Existing BLOB Leases When Using BACKUP TO URL

BACKUP TO URL was introduced as an add-on in Cumulative Update 2 for SQL Server 2012 Service Pack 1 and as a built-in feature for SQL Server 2014. I previously described this in a blog post.

We have been using this in a variety of ways from on-premises systems:

For example, it is an easy way to distribute a backup of a database to a large number of systems. Imagine you have a chain of retail stores that needs product and other reference information updated regularly. You can keep this data in a separate database at the head office, back it up to an Azure Storage account, and have each store download it separately.  This has major bandwidth and reliability improvements over other solutions such as having each store maintain a VPN connection to the head office.

2014-07-09

Step one in producing Shared Access Signatures for SQL Server Data Files in Azure - Generating a SHA2 256 HMAC Hash using Native T-SQL

One of the projects I have been working on as time permits is to construct a way to create a shared access signature using native T-SQL. Shared access signatures are needed when working with SQL Server data files in Azure.

Mostly, DBAs would use a programmatic utility such as Azure Storage Explorer to generate these signatures.

It is also straightforward to do so with SQL CLR based code but this is not enabled in all environments.

2014-07-09

Fix for SQL Server 2012 SP2 Data Corruption Issue Now Available

Microsoft recently announced service pack (SP) 2 for SQL Server 2012. However, we were aware of a potential data corruption issue related to online index rebuilds, so we had been recommending that customers avoid installing it until the fix was available.

That hotfix is now available: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2969896&kbln=en-us  

Note that there is also a fix for SQL Server 2012 SP1 and for SQL Server 2014 RTM.

2014-06-30

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.

2014-06-28

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: https://podcast.sqldownunder.com Enjoy! 

2014-06-22

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:

2014-06-21

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

2014-05-31

SQL Server Data Tools–BI for Visual Studio 2013 Re-released

Customers used to complain that the tooling for creating BI projects (Analysis Services MD and Tabular, Reporting Services, and Integration services) has been based on earlier versions of Visual Studio than the ones they were using for their other work in Visual Studio (such as C#, VB, and ASP.NET projects).

To alleviate that problem, the shipment of those tools has been decoupled from the shipment of the SQL Server product. In SQL Server 2014, the BI tooling isn’t even included in the released version of SQL Server. This allows the team to keep up-to-date with the releases of Visual Studio. A little while back, I was really pleased to see that the Visual Studio 2013 update for SSDT-BI (SQL Server Data Tools for Business Intelligence) had been released. Unfortunately, they then had to be withdrawn.

2014-05-25

BI Project Templates for Visual Studio 2013 are also now available

One quiet addition to the tools that have been released recently was the set of project templates for creating BI projects (Integration Services, Analysis Services, Reporting Services) within Visual Studio 2013.

It is really great to be able to use the same version of these as we are using for other code development in Visual Studio.

Thanks to the team for updating them so quickly!

2014-04-04

SDU Show 62: Paul Larson - SQL Server 2014 Underlying Technologies

I had the distinct honour (honor) this week of recording a new SQL Down Under podcast with Paul Larson.

Paul is a principal researcher at Microsoft and is one of the keen minds involved in the technologies behind the in-memory tables (Hekaton) and clustered columnstore indexes in SQL Server 2014.

In this podcast, Paul explains his role, and discusses how these core enhancements in SQL Server 2014 have been implemented.

2014-04-04