How SQL Server DBA’s can use Power BI Report Server

Reading Time: 4 minutes

For this post I want to cover how SQL Server DBA’s can use Power BI Report Server. So that others have ideas on how to use it.

In a previous post I went through a quick introduction of Power BI Report Server. With this in mind, I have decided to do a follow up on how SQL Server DBA’s can use Power BI Report Server.

As I mentioned in my previous post, Power BI Report Server is where you can install Power BI on your own servers. This now opens the floodgates for you to publish things you weren’t allowed to publish in Power BI before.  Possibly due to security concerns.

Security

For example, your boss might have prevented you from publishing a dashboard into Power BI service in the cloud in the past. Maybe because they contained server names and configuration details and they were worried about them being in the cloud.

Now that there is a version of Power BI that you can install on your own servers, you’re free to upload the dashboards onto there instead.

Of course, afterwards you’ll have other decisions to make. For example, how to secure your dashboards locally.

With this in mind, lets look at some dashboards you might want to Publish onto Power BI Report Server if you are a DBA.

Database Migration Assistant

One thing DBA’s can definitely take advantage of are dashboards that show the results of Database Migration Assistant checks.

Database Migration Assistant is a very powerful tool that advises you what you need to do if you are migrating a database from one version of SQL Server to another. For example, migrating a SQL Server 2016 database to SQL Server 2019.

You can find out more about Database Migration Assistant here.

I strongly recommend using this if you are doing migrations. Because it can help prevent a lot of migration issues.

Especially if you have your own assemblies and are migrating from an older version of SQL Server to SQL Server 2017 or above. Because of the introduction of the clr strict security option that was introduced and enabled by default. You can read about that in detail here.

I will cover two different ways you can use Database Migration Assistant with Power BI Report Server below.

Microsoft version

Until recently, I mostly looked to use the solution that has been developed by Microsoft to view the Database Migration Assistant results. It uses the command line version ‘dmacmd.exe’.

First you export the results in to json files, creating one file for each instance that contains databases that you want to analyse. Then you import the json files into a SQL Server database. After that you use the database as a source for a Power BI dashboard.

You can read more information about this method here.

Alternative version

However, last week I was made aware of an alternative solution to view the results of these checks in Power BI Report Server. It’s done by a guy called Dustin Ryan and is based on using csv files as the source instead.

You can read more about this alternative method here.

DMA Dashboard Considerations

Whichever of these solutions you use to check databases before migration you might have another big decision to make. If you’re supporting a large estate how do you present this data to application teams so that they can review and make the required changes?

Two potential options that you can use are as below:

• Create one dashboard for each application team so that they can view only their servers.
• Create one dashboard for everyone and change the security so that application teams view their own data.

If you don’t know Power BI Report Server that well than the first option is probably better. However, if you use one dashboard with the right security settings in place it makes the dashboard easier to maintain and update.

In fact, an MVP based in New Zealand called Reza Rad has a webinar all about Power BI Security Patterns. You can view it by clicking here.

dbachecks

Another thing you will be able to advantage of more in Power BI Report Server are the dbachecks dashboards. To clarify, dbachecks is a new offering from the dbatools team. It allows you to check the configuration of multiple SQL Servers at once using PowerShell.

It is supported by various members of the community and improving more over time. A lot of places are using this due to the fact it’s quick to implement and easy to customise. They have also designed a Power BI dashboard as well which you can look to publish into Power BI.

In fact, it was this project that introduced me to the Papercut mail application. You can find out more about dbachecks in detail here.

Final word

I hope my post about how SQL Server DBA’s can use Power BI Report Server proves useful for some of you?

What about yourself? Are there any dashboards you want to use or have already used? Feel free to add with a comment.