On the twitter hashtag #sqlhelp I saw a really dangerous (dangerous because it could cost your company a lot (somewhere between tens of thousands to hundreds of thousands of pick your favorite currency) bit of advice, that someone has received from their software reseller.

I’ll paraphrase the tweet so as to protect the guilty: “our reselller said that if we bought a license of enterprise edition, we could run the database engine on one server, and SQL Server Reporting Services on another.” This is 100% wrong, and always has been. Per the SQL Server 2019 licensing guide.

Even though SQL Server Reporting Services is separate installation now, the licensing is exactly the same as SQL Server. I think some of the confusion in the Twitter stream is related to the one of the terms of Power BI Reporting Service. If you purchase Power BI Reporting Services through your SQL Server licensing, it is treated exactly like any other SQL Server component for the purposes of licensing. That means, if you need a SQL Server database engine for your report server databases (the database that contains the PBI-RS metadata), you have two choices:

1) Install the database engine side by side with your PBI services

2) Buy additional cores to run the database engine on a different server.

This last bit is where it gets a little confusing. If you buy your PBI RS licenses through having Premium capacity in the Power BI service, you can install SQL Server standard edition, exclusively for the purposes of Power BI or other products like SSRS or SSIS that require a SQL Server database.

That’s from the volume licensing guide.

tl;dr Always assume you need a license for production, unless you are paying for PBI premium and then you may have an engine license you can use just for that.

One of the things that was really painful in the early days of Azure, especially for those of us who are consultants with many customers, was the process of switching tenants and logged in sessions. To Microsoft’s credit, they have made this process much better, it’s a single click to switch between logins and/or subscriptions. However, when I working with Azure programmatically as I often do, logging in from my laptop is a little bit more painful.

The workaround for this has been to use cloud shell within the Azure Portal. You can even do this on a mobile device, which can be really handy, if something bad happens and you don’t have a laptop handy. However, the one problem I had with Cloud shell was that it was hard to debug in. I would develop a script “offline” and then paste it into the shell, and sometimes miss obvious variables or cut and paste errors. Also, if you were using the PowerShell version of cloud shell, saving scripts was not intuitive.

However, when I logged in this morning in an effort to run some PowerShell code against a customer’s tenant. I was greeted by a message that I didn’t get a screen shot of and can no longer recreate that said vim, nano, emacs, and code (VS Code) were available as text editors in cloud shell. Let’s try it out.

Note, that is how you exit vi like a boss (escape+:x!). So I created a trivial file, big deal.

I can also see my file. That’s also pretty nifty. But check this out:

By typing code followed by my file name, I get a limited version of Visual Studio Code (btw, I checked and dark mode seems to be the only choice). You can’t highlight code and execute it using F8, but you do get really nice editing functionality in the portal. And you can save a file and it stays in your home drive in cloud shell.

Note: This is my first blog post in a while. You too may have been stressed by recent events in world. I’m also doing a large content project I’ll hope you get to enjoy in the near future too. This post actually ties into working from home.

So anyway, one of our customers recently spun up a new Azure SQL Database in the Azure South Africa North region, and two of their team members couldn’t connect to it from their homes. The common element–both of them used Verizon Fios as their ISP.

It’s important to note that connectivity for Azure SQL Database is to a public IP address. Each region has a public IP address and a lookup takes place, and then you are connected through a gateway and you eventually connect to the database. The IP address is just a public endpoint. If I run a trace route to a database running in the Azure US East region I see:

Josephs-MacBook-Pro-3:Dropbox joey$ traceroute dcac-demo.database.windows.net
traceroute to cr4.eastus2-a.control.database.windows.net (52.167.104.0), 64 hops max, 52 byte packets
1 192.168.115.1 (192.168.115.1) 2.224 ms 2.849 ms 1.953 ms
2 96.120.76.77 (96.120.76.77) 12.961 ms 12.978 ms 14.438 ms
3 68.86.213.153 (68.86.213.153) 10.461 ms 9.803 ms 8.983 ms
4 68.86.211.170 (68.86.211.170) 16.069 ms 6.967 ms 12.133 ms
5 68.85.158.49 (68.85.158.49) 11.460 ms 15.657 ms 11.314 ms
6 be-201-ar03.ivyland.pa.panjde.comcast.net (96.108.181.21) 14.815 ms 13.470 ms 13.775 ms
7 be-33287-cr01.newark.nj.ibone.comcast.net (68.86.93.173) 18.519 ms 17.500 ms 19.012 ms
8 be-1301-cs03.newark.nj.ibone.comcast.net (96.110.36.89) 16.457 ms 23.452 ms 20.628 ms
9 be-2303-pe03.newark.nj.ibone.comcast.net (96.110.37.74) 15.248 ms 21.317 ms 22.250 ms
10 50.248.118.226 (50.248.118.226) 16.467 ms 18.986 ms 23.555 ms
11 ae23-0.ear01.ewr30.ntwk.msn.net (104.44.239.159) 24.966 ms 22.497 ms
ae24-0.ear01.nyc30.ntwk.msn.net (104.44.239.155) 26.774 ms
12 be-21-0.ibr02.ewr30.ntwk.msn.net (104.44.20.122) 32.792 ms
be-20-0.ibr01.nyc30.ntwk.msn.net (104.44.20.124) 27.437 ms
be-20-0.ibr01.ewr30.ntwk.msn.net (104.44.20.120) 34.036 ms

You can see that on steps 7-9 it my connection jumps onto the Comcast backbone (ibone.comcast.net) network, and at step 11 jumps onto the Microsoft Azure network (msn.net). I truncated the output of the trace route there.

Monica Rathbun (b|t ) who works with me at DCAC, also had Fios and was able to help us troubleshoot this. When Monica ran a trace route to the public IP address the  of Azure SQL Database in South Africa North it looked like:

C:\Users\monica>Tracert egpaftz.database.windows.net

Tracing route to cr1.southafricanorth1-a.control.database.windows.net [102.133.152.0]

over a maximum of 30 hops:

  1    <1 ms     1 ms    <1 ms  Fios_Quantum_Gateway.fios-router.home [192.168.1.1]

  2     3 ms     1 ms     2 ms  102.133.152.0

That is not how the internet is supposed to work. While we tried to figure out how to get in touch with someone at Verizon to fix this, which through normal consumer channels is not fun. So while we waited for that to get fixed, we had another options to fix this.

Introducing Private Link

Azure Private Link was recently introduced as generally available for Azure SQL Database and allows for you to have a truly private connection to your database. For several years now you have been able to use network endpoints to allow a specific VM or App Service to connect to Azure SQL, however that design had some limitations. The first being that it still routed the connection to the database over the public IP address. The second was that network endpoints did not support multi-region scenarios. With Private Link your database has it’s own private IP address on the virtual network where it is deployed.

In our client’s case, this work around involved connecting to an Azure VPN in US East which would then be connected to South Africa North. One interesting thing about Private Link is that it doesn’t support Azure Virtual Network peering, so you will need to create a Gateway if you want your traffic to traverse virtual networks. Additionally, we were working in two different subcriptions, which mean we had to use the PowerShell from here. Private Link is easy to configure from your Azure SQL server logical server, but be sure you have the right permissions in Azure RBAC. I needed to be granted the network contributor role in order to get the GUI to work.

The Conclusion

I had some issues that were permissions related which delayed our implementation of Private Link, and I was fortunate enough to have some members of the Azure Networking product group helping me out. Since Microsoft and Verizon are technically partners (Verizon is an ExpressRoute partner) they were able to get in touch with some folks as at Verizon, and they were able to resolve the problem. Sort of–the trace routes still look weird, but everyone can connect. Verizon reports that the trace routes from what they can see look normal. Which tells me they are doing something really weird on the router itself with how Azure traffic is routed.

Photo by Pixabay on Pexels.com

In January I had the chance to attend Storage Field Day 19 in Santa Clara, where we got to meet with a wide variety of startups and large enterprise storage companies. One of the more interesting companies we meet with was MinIO which has a really interesting and compelling object-based storage product.

I’ve talked about object storage here before, but it’s a very different paradigm than the traditional block based storage you may currently be using. With block storage files are split into evenly sized blocks of data (typically somewhere between 64 KB and 1 MB depending on your vendor). Data protection is provided by traditional RAID options.

Object storage on the other hand doesn’t split files into blocks. Files are stored as objects which contain the file data, metadata, and a unique identifier. There is no limit on the size or amount of the metadata associated with the file. If you have ever created a managed disk in Azure, taken a backup to URL, or used an Azure SQL Database you’ve used object based storage. In object based storage, redundancy is generally provided by maintaining three copies of the object (e.g. a write isn’t considered complete until it writes to all three copies).

Object storage is designed to solve problems of scale. One of the things I learned at Comcast was that the cost of SAN storage didn’t scale to some of the massive petabyte scale data problems we had. The management overhead, the cost, and sometimes even the storage itself does not scale. This is a problem largely for companies like Microsoft, Amazon, Google, Facebook, etc, who have massive amounts of data to store.  But as data volumes grow there are lots of other firms who have very large volumes that they need to manage.

MinIO is a firm that offers such a solution. MinIO offers open source storage management software that offers extremely fast (183 GB/s reads and 171 GB/s writes). It is fully compatible with Amazon’s S3 API, which has somewhat become the de facto standard for object storage. They were working on Azure Blob Storage support when we visited.

One of the ways MinIO is able to get such good performance out of pretty standard hardware is by taking advantage of SIMD processor instructions, which all more text and number crunching to be performed per CPU instruction which dramatically increases performance. SQL Server uses this through the query processor’s use of batch mode.

MinIO’s storage can also be used as a persistent store for Kubernetes (drink), or used for systems like Spark, TensorFlow, and a replacement for Hadoop HDFS. Where you would probably use this in your environment would be to replace your file servers, or as a target for container storage, or maybe even an analytic store. Or you want to become a cloud storage provider and you need to host 50 PB of data in your data center.