Running SQL Server 2016 Express in an Azure VM

While working through the provisioning guide for SQL Server in Azure VMs I hit a problem.

I chose the 2016 Express Edition, followed all the steps to create a server that was available publicly and then attempted to connect using the SQL server extension for Visual Studio 2015.

Try as I might, I couldn't connect, getting back this error:

Cannot connect to xyz.eastus.cloudapp.azure.com

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53 The network path was not found

Eventually I discovered that the problem is not due to the VM firewalls, Azure NSGs or SQL Server configuration. Instead I loaded up Sql Server Configuration Manager and checked the enabled protocols, only shared memory was enabled, so I enabled both the Named Pipes and TCP/IP Protocols as follows:

Finally, success! I was able to connect to my SQL server instance over the internet.

Buying a .uk domain and WHOIS Address Opt Out

I've known for some time that domain names you purchase (such as .co.uk) can be protected against identity theft via DomainsByProxy.com, such as those purchased via godaddy.com.

What this means is that a WHOIS lookup on a protected domain returns details along the lines of
    Registrant:
        Name withheld. This Registrant is using a privacy service.

    Registrant's address:
        Address withheld. This Registrant is using a privacy service.

    Privacy service:
        Domains By Proxy, LLC

However, I hadn't realised that .uk domains cannot be protected in this manner as they a governed by different rules.

So, during my recent purchase of a new .co.uk domain, when I was asked if I wanted to purchase the .uk as well, I thought, "Yeah, OK", without realising the differences between these domain extensions and privacy.

After some head-scratching (googling) I found that it was still possible to protect some of the registrants data (address) via an Opt Out. This meant logging into Nominet and requesting the Opt Out for the new domain.

Date shifting with PowerBI and the DateAdd DAX function

I was recently playing with PowerBI Desktop to build a report which compared one years' figures to another.

So for example, if you have a column of dates such as

01 August 2016
02 August 2016
03 August 2016

I wanted to create a new column that time shifts these dates to the previous year to give:

01 August 2015
02 August 2015
03 August 2015

Simple... or so I thought. The DateAdd function seemed a likely contender, so I tried

Column = DATEADD(MyTable[Date],-1,YEAR)

What stumped me was that this returned nothing for each entry, just empty cells.

After re-reading the MSDN function reference and reaching out via this PowerBI blog post I found that DateAdd only returns dates that are in the original data set, i.e.

"The result table includes only dates that exist in the dates column."

Ultimately, the most elegant solution for me was to create the modified dates in the Query Editor using M and the Date.AddYears function (not using DAX functions within PowerBI formula):

Custom = Date.AddYears([Date], -1)

Credit goes to KGrice for helping find this solution.

Debugging the PowerBI Enterprise Gateway

I recently configured the PowerBI Enterprise Gateway to connect to two different types of data source:

• A local File on the same server as the gateway, and
• A Web datasource, a URL reachable from the gateway server 

I added the data sources to PowerBI Online via "Manage Gateways". I could see the data sources refreshing and available within PowerBI Online. So I built a report that used the data sources and published the report to PowerBI Online.I then configured the datasets to use the Gateway connection and also scheduled a daily refresh.

A day later, I revisited my reports to check that the data sources had successfully refreshed overnight, but instead had a failure as follows:

The data gateway abcdef is offline, so refresh scheduling is currently disabled. Last refresh failed: Fri Aug 12 2016 17:09:06 GMT+0100 (GMT Summer Time)
Cannot connect to the mashup data source. See error details for more information.

Concerned that the gateway service had gone down I logged onto the server running the gateway and checked the service was running and the logs files here:

C:\Users\PBIEgwService\AppData\Local\Microsoft\on-premises data gateway\Gateway*.log

See this link for more troubleshooting tips.

It turned out that even though the File data source was still available, the Web data source had become unreachable.

Therefore, rather than just indicate that one data source was unavailable, PowerBI Online appears to report the entire data gateway as offline. Which isn't entirely true, the gateway service was running, but one of the data sources was unavailable.