Using the Wait Statistics Report in Azure Data Studio

Azure Data Studio, which used to be called SQL Operations Studio, has a bunch of extensions available, including one called Server Reports from Microsoft. Last year they took my wait stats query (from here) and made it into a report as part of Server Reports extension. In this quick post I’ll show you how to install that extension and look at the report.

I’ve been using Azure Data Studio a lot as part of working with SQL Server 2017 on Linux in Azure and I like it a lot. It’s obviously not as comprehensive as SSMS, but for simple stuff it does the job.

You can see the pretty big list of available extensions by hitting the Extensions icon:

In the list that appears, find the Server Reports extension and click the Install button:

Once an extension is installed, you need to activate it. Without reading any documentation, the first time I used Azure Data Studio I couldn’t figure out why the extension wasn’t there once I’d installed it. Click the Reload button to active the extension:

The extension will now show up in the list of enabled extensions:

If the Server Dashboard screen isn’t showing, right-click on one of the servers you’re connected to and select Manage. All the active extensions will show as tabs at the top of the screen, so select Server Reports and then click the heart-shaped icon and you’ll see the current aggregate wait stats for the instance:

Note that the x-axis is percentage of all waits, not wait count. You’ll see that PREEMPTIVE_OS_FLUSHFILEBUFFERS is the top wait on my Linux instance – that’s by design and I’ll blog about that next. I’ve also submitted a GitHub change to add that wait to the list of waits filtered out by script the extension uses.

Anyway, you can drill in to the details by clicking the ellipsis at the top-right of the graph and selecting ‘Show Details’. That’ll give all the waits and by selecting each one you can see the usual output from my waits script. To get more information on what each wait means, select the bottom cell, right-click on the URL to copy it, and paste into your favorite browser to go to my waits library. And of course, you can refresh the results via the ellipsis as well.

There are many more useful extensions that you should check out too – enjoy!

Changing the SSH port for a RHEL Azure VM

I’m working with Red Hat Enterprise Linux (RHEL) VMs on Azure and one of the best practices that Microsoft recommends is to change the default SSH port from 22 to something else. I was looking for a consolidated set of instructions to do this and I couldn’t find one, so I thought I’d do a quick post on it.

The steps that need to be performed are:

  • Allow the new port in the RHEL firewall
  • Change the SSH daemon to listen on the new port
  • Add an incoming rule in the VM network security group for the new port
  • Remove the rule that allows port 22

Changing the SSH port has to be done after creating the VM, as the SSH daemon is set up to listen on port 22 by default, so as part of the VM configuration, make sure to allow port 22 to be opened. Once the VM is created and running, connect to it using your favorite SSH client.

Let’s choose port 52019 to use instead of 22.

To allow the port in the RHEL firewall:

sudo firewall-cmd --permanent --zone=public --add-port=52019/tcp
sudo firewall-cmd --reload

You should see a ‘Success’ message after each of these commands, and you can double check that the firewall rule was added using:

sudo iptables-save | grep '52019'

And you should see:

-A IN_public_allow -p tcp -m tcp --dport 52019 -m conntrack --ctstate NEW -j ACCEPT

To change the SSH daemon to use port 52019 we need to change it’s configuration and restart it. Edit the config file using:

sudo vi /etc/ssh/sshd_config

Now vi is not very user friendly, but it’s easy once you learn it. Use the down arrow so your cursor is over the # at the start of the line that says:

#Port 22

Hit your Insert key until the word REPLACE shows at the bottom of the SSH window. Then type:

Port 52019

Now hit Esc then :w and then Esc then :q to write the file and exit.

To restart the SSH daemon:

sudo service sshd restart

Now go into the Azure Portal and for that VM:

  • Click Networking in the Settings pane and click the blue ‘Add inbound port rule’ button on the right
  • In the wizard that comes up, change the ‘Destination port ranges’ to 52019 and the ‘Name’ to something like ‘IncomingSSH’
  • Click the blue ‘Add’ button

Once the rule has been added, you should be able to create a new SSH connection to the VM using port 52019. Once you’ve done that, drop the initial SSH connection that’s still using port 22 by going back to the Networking tab in the Azure Portal, finding the existing rule for port 22, click the ‘…’ on the right and selecting Delete.

Hope this helps anyone else looking for concise instructions.

New class: Immersion Event on Azure SQL Database, Azure VMs, and Azure Managed Instance

Tim’s been working closely with the development team on Azure Managed Instance, presented with them at Ignite recently, and has been presenting internally at Microsoft field offices to their clients, as well as working with our own clients extensively on Azure and Azure migrations. With all this Azure experience, and by popular demand, Tim’s updated his two-day IEAzure class and expanded it to four days!

It’s now IEAzure: Immersion Event on Azure SQL Database, Azure VMs, and Azure Managed Instance and it’ll debut in May 2019 in Chicago, as part of our usual set of Spring classes. Note: there’s a US$200 discount for registering before the end of 2018!

The modules are as follows:

  • Azure Virtual Machines
  • Migrating to Azure Virtual Machines
  • Azure SQL Database
  • Migrating to Azure SQL Database
  • Azure Managed Instance
  • Azure Security
  • Administration and Scheduling
  • Additional Azure Features

You can read a more detailed curriculum here and all the class registration and logistical details are here.

We hope to see you there!