The Bit Bucket

There were a number of great announcements from Scott Guthrie today: http://weblogs.asp.net/scottgu/azure-sql-databases-api-management-media-services-websites-role-based-access-control-and-more

One that particularly caught my eye was a subtle change to the billing for Azure SQL Database. Databases used to be billed at a daily charge.

While this might not have seemed a big deal, the only way to create a transactionally-consistent backup of the database was to first copy it using CREATE DATABASE AS COPY OF original database, then to use the database export functions on the copy. The export needed to be done on the copy (not on the original) as it just copied the data in each table, one table at a time.

2014-09-14

Many customers want to install Books Online (BOL) when working with SQL Server. Generally I always think it’s a good idea.

Not all environments though, allow external connectivity for the systems where the clients want to install BOL.

A friend of mine Rob Sanders documented how to install BOL in an offline method a while back on his blog.

The problem with this was that the T-SQL Reference documentation was not included in the download for offline installation. Given the T-SQL language reference was the major reason that most people wanted to install BOL, it all seemed quite pointless.

2014-09-14

I’ve been taking a look at some of the 3rd party tools for SQL Server. Today, I looked at DBForge Studio for SQL Server from the team at DevArt.

Installation was smooth. I did find it odd that it defaults to SQL authentication, not to Windows but either works fine.

I like the way they have followed the SQL Server Management Studio visual layout. That will make the product familiar to existing SQL Server Management Studio users.

2014-08-25

One of the things I’ve been keeping an eye on for quite a while now is the development of the Azure Search system.

While it’s not a full replacement for the full-text indexing service in SQL Server on-premises as yet, it’s a really, really good start.

Liam Cavanagh, Pablo Castro and the team have done a great job bringing this to the preview stage and I suspect it could be quite popular. I was very impressed by how they incorporated quite a bit of feedback I gave them early on, and I’m sure that others involved would have felt the same.

2014-08-25

This is more for my own reference than anything else but today I was needing to enter a backslash key while using a US keyboard but with UK keyboard settings.

After trying pretty much every key on the keyboard in all combinations, I realised there was no key combination that would do this directly. Curiously it’s a common problem and I found untold blog and forum entries that were not helpful. They basically said to change your input to US when using a US keyboard.

2014-07-14

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

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

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

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

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