Skip to content

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

Reading Time: 3 minutes

In a previous post I went through a quick introduction of Power BI Report Server. 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 a PowerBI in the past. Maybe because they contained server names and configuration details and they were worried about them being in the cloud.

Now there is a version of Power BI that you can install on your own servers you’re free to look to upload the dashboards onto there. After you have done this you’ll have other decisions to make like how to secure your dashboards locally.

With that 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 showing 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 to another. It prevents a lot of migration issues.

I strongly recommend using  this if you are doing migrations.  In fact I cover it in my session that I recently talked about in a previous post. I will talk about two versions here.

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, one file
for each instance containing databases you want to analyze . 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 it here.

Alternative version

However, last week I was made aware of an alternative solution to view the results of these checks in Power BI. 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 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 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 that well the first might be a better option. However if you use one dashboard with the right security settings in place it makes the dashboard easier to maintain and update. An expert called Reza Rad has a webinar about Power BI Security Patterns here.

DBAChecks

Another thing you will be able to advantage of more in Power BI Report Server is DBAChecks dashboards. Which is a new offering from the dbatools team. It allows you to check the configuration of multiple servers using Powershell.

It is supported by various members of the community and improving 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.

It was actually this project that introduced me to the Papercut mail application. You can find out more about DBAChecks here.

Final word

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

Published inSQL Server

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *