If you haven’t heard of it before, it was founded by Brent Ozar ( b | t ) back in the day and features some big speakers in the SQL Server community each year. Just check out the lineup below 😀

You can register for the event and checkout the other details here:
https://mailchi.mp/groupby/may-2021-reg-d

After the live event, we upload all the content onto YouTube for anyone who missed a session or just wants to check it out after the fact. (You don’t need to be signed up – you can see all the previous sessions here: https://www.youtube.com/channel/UCEHf_UKwG3GMkb9wIVBTeQA ) Hope to see y’all there.

Backstory

I needed to start the refresh of a data model using the new Start-RsRestCacheRefreshPlan function I created, and then check a few seconds later to make sure it had started, using the Get-RsRestCacheRefreshPlanHistory function. Sounds easy enough right? The problem is that the service doesn’t update instantly to tell you that it had started. Also, the service sometimes takes longer, depending on load.

Start-Sleep isn’t ideal

Originally I had used the Start-Sleep command to wait 3 seconds ( Start-Sleep 3
). That worked fine on my machine, but when I deployed it to the server, I found I needed to bump it up to 6 seconds. At first that worked, but then a week later I needed to bump it up to 9 seconds. The problem here is obvious, if we force it to wait 9 seconds every time, even if the task was updated after 4 second, we’re wasting extra time. And those seconds are going to add up.

Instead, I was asked to allow the Get-RsRestCacheRefreshPlanHistory function to wait up to 15 seconds to get the answer it was looking for, but to stop as soon as it got the right answer. In this particular case @($someHistory).count -eq 1
is the “right answer”. But if it takes more than 15 seconds, then we just assume failure.

How to WHILE

The only problem with this request for me to “use a polling loop” was that I didn’t quite remember off the top of my head how to do something like this. So I pinged Doug Finke ( b | t ) and about 18 seconds later I had my solution.

Stepping through the logic

1. First, we need to start the refresh
2. Next, we find out what time it is
3. Now we need to check the .count property of the $someHistory variable and see if it is less than 1, as soon as the .count property is 1 or more, the loop will stop
   1. The first time we do this check the variable will be empty, and will therefore be less than 1
4. If we pass that check, we then go ahead and run our command to check the history and load the result of that check into the $someHistory variable
5. Next, we check the current time and see if 15 seconds have elapsed yet. If 15 seconds have elapsed we run whatever the code inside the {} is, in this case it’s break keyword which is the other way we can end our loop.
6. If we get all the way down to that last little } and neither of our conditions have been met, we just start the whole loop over again.

Here’s that chink of code again in case you need to build something similar.

Start-RsRestCacheRefreshPlan
$timer= Get-Date
while ((@($someHistory).count -lt 1)) {
  $someHistory = Get-RsRestCacheRefreshPlanHistory –RsReport “/ReportCatalog”
  if(((Get-Date)–$timer).seconds -ge 15) {break}
}

We can finally do our check to see if @($someHistory).count -eq 1
and move on to the next step in our command.

Again, this is just the solution that worked well for me. If you know of an easier way to do this kind of polling, please share it in the comments

Until someone shows me a better way, I’m going to use this same technique to wait for my SQL Servers to be ready when I build them in Docker.

I have created two new PowerShell functions for Granting or Revoking permission on items in the Power BI Report Catalog. These two will be the probably be the last two functions I create for Power BI Report Server for a little while. I’ll be working on SSAS cmdlets for the next week or two.

RECAP

I have recently created 4 new functions for working with Scheduled Refresh Plans (mostly covered here). As well as two more functions, one for Testing your DataSource connection, the other for inspecting the permissions for an item in the Report Catalog (mostly covered here). So, with these two, that makes 8 new functions this year. All of these functions have been built against the REST API, which is necessary for making them work in PowerShell 7.

Giant Thanks to Doug Finke!

I could not have created these two new functions ( Grant-RsRestItemAccessPolicy and Revoke-RsRestItemAccessPolicy ), without the help of Doug Finke ( b | t ). Doug Took a few minutes out of his busy schedule to teach me how to add & subtract (INSERT & DELETE) objects, and then convert them to the proper JSON payload. It was kind of crazy how fast he was able to create the working code.

Pester Tests are Important

Building these two new commands was also a lot faster because I didn’t really have to create new Pester Tests for them, per se. Instead, I took the existing Pester test for the old SOAP commands, created a copy, and basically sapped out the names. This was kind of a design goal too. I wanted to be able to prove that the two new commands could do the exact same operations that the two old commands could. How could I possibly prove this better than duplicating the existing Pester test?

Available Today

You can now get all of these command from the ReportingServicesTools module in the PowerShell Gallery.

Update-Module ReportingServicesTools

If you want to see for yourself the new commands that have been added, just run the code below (assuming you also downloaded the old version of the module at some point):

Import-Module ReportingServicesTools –RequiredVersion 0.0.5.8
$58 = Get-Command –Module ReportingServicesTools –CommandType Function
Remove-Module ReportingServicesTools
Import-Module ReportingServicesTools –RequiredVersion 0.0.6.4
$64 = Get-Command –Module ReportingServicesTools –CommandType Function
Compare-Object –ReferenceObject $58 –DifferenceObject $64 –Property Name –PassThru

That’s it for now. If you happen to have any requests to improve the PowerShell cmdlets for SSAS, please reach out to me with your suggestion. I’m already working on making those better.

A few months ago I was in a meeting where we were all asked how we could take on a complex, but very necessary task. As they went around to everyone on the call, everyone said it wasn’t possible to do without a major (dedicated personnel) effort. When they got to me, I said something like “well, maybe, if only I could do these two thigs in PowerShell, I might be able to achieve the end result you’re after”. (Please picture the scene in The Princess Bride where Inigo & Fezzik ask Westley to figure out how to raid the castle. Westley tells them it’s impossible. And then a moment later he asks for a wheelbarrow & cloak.) Well, that’s exactly where I was, I needed just 2 things to attempt the impossible.

Side Note: If you’re a regular reader here, before you say it, I know what you’re thinking. You’re thinking “wow Aaron, you’ll do *anything* to come up with a reason to blog about PowerShell, won’t you?”

Solution time:

One of the two things I needed to be able to do in PowerShell was to query a DB2 database. I had a look on the PowerShell Gallery and the search didn’t return anything for DB2. So, I did what I’m fairly well known for doing, I asked for help on twitter.

Just a few minutes later Tim replied and pointed me to some code on Ember Crooks’ blog.

<blockquote class=”twitter-tweet”><p lang=”en” dir=”ltr”>This might be handy.<a href=”https://t.co/0WoDdPvMFF”>https://t.co/0WoDdPvMFF</a></p>&mdash; Tim (@hantu0) <a href=”https://twitter.com/hantu0/status/1328793837003812866?ref_src=twsrc%5Etfw”>November 17, 2020</a></blockquote> <script async src=”https://platform.twitter.com/widgets.js” charset=”utf-8″></script>

This was a huge help! It wasn’t quite what I needed, but it was close enough to get me going. I took the code from Ember Crooks’ GitHub and merged it with some code from way back when the old SQLPS module had a whopping 5 cmdlets. After I got it working, I turned it into a PowerShell function, to make it easier to use.

I’ve put this code in a Gist so that I can get to it easily. But since I don’t work with DB2 very often I figured the code would have a better home back with Ember, so I did a PR against her repo.

How to use it:

You easily can download a copy of the script using PowerShell:

PS C:\temp> Invoke-RestMethod –Uri https://gist.githubusercontent.com/SQLvariant/e9bede8a6bf4e65408da1f0a7f7faffc/raw/65a3f934e3d14223173d56edcb2d079d8ec58441/Invoke-DB2Query.ps1 –OutFile Invoke-DB2Query.ps1

If you found this blog post because you work with DB2 and are new to PowerShell, I’ll give you a couple quick tips. I wrote this script to be a function, a function is like a baby cmdlet. To use the function you can either copy paste the code into your session and run it, or you can dot-source it like below.

PS C:\temp>. .\Invoke-DB2Query.ps1

After that, you can you the Get-Help cmdlet to get PowerShell to tell you a few examples of how you can use this function.

PS C:\temp>Get-Help Invoke-DB2Query –Full

OK, that’s about it for the quick tour.

Some other things:

You might notice I didn’t include parameters for username & password, and that’s because I included trusted_connection=true. If you need to use a username & password instead, just swap out that piece with User Id=;Password= from Ember’s code.

Finally, you might be wondering what was the other thing I needed in PowerShell, or what the ‘impossible’ task was. Those will take multiple blog posts to cover, but I will get around to them before too long, I promise.

I can’t tell you how many times I’ve started to work on a report I was told was working, only to find the connection info was invalid. This wastes valuable time, especially when you’ve already made changes to the report.

Other times, I’ve been asked to figure out why a bunch of subscriptions weren’t working, only to find out it was a simple connection issue. I’ve always wanted a simple PowerShell command to check the credentials of a bunch of reports before I touch anything.

Turns out, it wasn’t that hard to build at all. I only wish I had built it years ago.

This command will check every credential within your report, and will return the success/failure of each connection separately. I have built this command to work with both Power BI reports & Paginated reports on Power BI Report Server. I used the REST API so these commands are available on PowerShell 7.

While I was working on the ReportingServicesTools module I added two more commands as well. I added Get-RsRestCacheRefreshPlanHistory for checking the success/failure of last few refreshes of a Power BI report. I built this command to help me find out when a report refresh stopped working.

I also built Get-RsRestItemAccessPolicy which is basically a re-creation of the Get-RsCatalogItemRole command, but using the REST API instead of the SOAP API. I have more work I plan to do to create [ Grant | Revoke ] commands. But first I need to learn how to add & subtract ( INSERT / DELETE ) lines of JSON from PowerShell. 😀

You can now Create (New), Get, & Start a CacheRefreshPlan of a Power BI report deployed to a PBIRS instance.  For clarity, these only apply to reports using an Imported model, not those using Direct Query.

Get-Command -Module ReportingServicesTools -Noun *CacheRefresh*

These can help you to better integrate Power BI reports into a DevOps pipeline and do more comprehensive deployment testing.  Furthermore, the Start-RsRestCacheRefreshPlan
function could be called as the last step in your ETL process.

In a Bob Ross style “Happy little accident” the Start-RsRestCacheRefreshPlan
function also works against a subscription of a paginated report.

This is the first release of these commands but we have been successfully using these functions for reports since late last year.  If you see some ways these new commands could be improved, please don’t hesitate to contribute to them 😀

I’m really excited to join the lineup of speakers for this event because the organizers are using this event to raise money to provide food and necessary living items for children affected by COVID pandemic. It’s a great cause to support and I hope you’ll join us.

SESSIONS: All Time Slots Below Are In EST (GMT -5)

The concept is fairly simple, first you run the Get-RsDeploymentConfig
command to pull in all the deployment-target details from the SSRS project file. In SSRS projects you can have multiple deployment configurations, so you can specify which configuration you want to use by supplying the name of that configuration for the -ConfigurationToUse parameter. This will give you back a PSObject with all the info it collected.

After that, you need to add the URL of the report portal manually (unfortunately, these are not included in the SSRS Project config files).

You can put all of that together and see the results like this:

$RSConfig = Get-RsDeploymentConfig –RsProjectFile ‘C:\source\repos\Financial Reports\SSRS_FR\SSRS_FR.rptproj‘ –ConfigurationToUse Dev01 $RSConfig | Add-Member –PassThru –MemberType NoteProperty –Name ReportPortal –Value ‘http://localhost/PBIRSportal/‘

$RSConfig

Once that looks good to you, all you have to do is pipe that object to the Publish-RsProject command, and your deployment should start.

$RSConfig | Publish-RsProject

Some quick notes:

• Obviously, the account running these commands will need a copy of the SSRS project it can point to, as well as the necessary credentials to deploy to the SSRS/PRIRS server you point it to.
  
• For the Get-RsDeploymentConfig command, the SSRS project you are using must be in the VS 2019 project format. Otherwise, the command won’t know where to look for the correct info.
  
• If you don’t know the name of the configuration you want to use, just point Get-RsDeploymentConfig to the project file, and it will give you back a list of configuration options to choose from.
  
• Make sure you run Update-Module ReportingServicesTools
  to get these new commands.
  

FYI: I only had two SSRS projects available to test these commands with. They worked great for those two projects, but your SSRS project might include some complexities that I just didn’t have in either of the projects I tested with. If you have any trouble making this work, please give me a shout or file a bug on the GitHub project and I will try to help out.

Big thanks to Doug Finke ( t ) for his code contributions, and Mike Lawell ( t ) for his help testing, to make these two commands a reality.

Deploy SSRS Projects with these two new PowerShell commands in the ReportingServicesTools module!

Tweet

In Azure Data Studio, open up a new Notebook and change the drop-down menu next to “Kernel:” and select PowerShell

At this point, if you haven’t tried out the Python kernel yet, you will be asked if you want to use an existing install of Python, or let Azure Data Studio install Python. I already have Python installed, so I will point it to that install.
If you have already used the Python 3 kernel, you can skip these next two screenshot and head straight down to installing the powershell_kernel.

(At this next point, if you’ve never installed or used any kernel besides the SQL kernel, you will see a lot of downloading & installing happening. This could take a little while.)

To get PowerShell Notebooks up & running you’ll need to install the powershell_kernel package. To do that, you’ll need to go through the following steps:

1. Back inside that Notebook, click on the “Manage Packages” button (if the “Manage Packages” button isn’t being displayed, just choose the “Python 3” kernel or close the Notebook and re-open it)
2. When the fly-out menu pops out from the right, click in “Add new“
3. In the search box type in powershell_kernel
4. Click “Search“
5. Once the powershell_kernel is found, click “Install“

After all that, the powershell_kernel should finish installing rather quickly.

Once the install completes, go back to your Notebook and switch the Kernel dropdown to PowerShell if it isn’t already.  Next, a new Code cell and start adding your PowerShell code to your Notebook.

Note: Sometimes the dropdown gets stuck on Changing Kernel, if this happens to you, the easiest thing may be to close out of that Notebook and start a new one.

Make sure to install the PowerShell extension

In order to get all the nice intellisense and tab completion features of the PowerShell language inside your PowerShell Notebooks, be sure to install the PowerShell extension from the Azure Data Studio marketplace.

Once you have your command ready to go, just hit the “Run cell” button and you’ll see your results returned in that same cell. When you save your Notebook file, you can choose to clear out these results or leave them in there, so the next person knows what the results are supposed to look like. Or, you just know that yes your code did work the last time you ran it

Now before you get rocking & rolling with PowerShell Notebooks there are a few things you should know.

1. PowerShell Notebooks run Windows PowerShell on Windows, and PowerShell Core on macOS & Linux (this was just the easiest / fastest kernel to get working)
2. [For now] when you run a code cell in a PowerShell Notebook it runs the code by passing whatever code you gave it to PowerShell.exe as a scriptblock and also passes the -NoProfile switch. This makes sense to do as a default, but I suspect some customers will want an option to turn off -NoProfile for some of their PowerShell Notebooks
3. You can set a variable in one cell, and pull the values out of it in a different cell. Until you close your Notebook or Azure Data Studio, you can reuse variables from one code cell to another. Each code cell is participating in the same common execution of PowerShell

So that’s all well and good. But if you’ve read many of my blogs or articles over the years, you know I still have one burning question: Will PowerShell Notebooks work with Out-GridView?

I am happy to tell you that, if you’re on Windows, yes, PowerShell Notebooks does work with Out-GridView!

That’s pretty much everything I have to show you about PowerShell Notebooks for today. There are a few other features I will call out in a future post or two, but this should be enough to get you started with PowerShell Notebooks.

I do have a few open enhancement requests in the Azure Data Studio repo with ideas of how to make PowerShell Notebooks even better. Please have a look comment with what you plan to do with PowerShell Notebooks you build?

The Power Platform team has released a preview of their PowerShell module for managing your Data Gateways. You can install it now from the PowerShell Gallery using PowerShell Core. I’ve been working with the cmdlets for a few weeks now, and even contributed code to enhance two of the cmdlets.

Install-Module DataGateway

It’s PowerShell Core Only

You read that right, and it’s the first module I can remember working with which is PowerShell Core only. Before you go trying to install the DataGateway module on Windows PowerShell, just open PowerShell Core instead. If you don’t already have it, you can find instructions for how to install PowerShell Core here.

If you’re running VS Code or Azure Data Studio on Windows, you might need to switch the version of PowerShell you’re running in order to use this module. See this short post on how easy it is to do that.

What Can the DataGateway Module do?

The DataGateway module comes with 23 cmdlets. So far, the ones I find myself using the most are Get-DataGatewayCluster, Get-DataGatewayClusterDataSource, & Get-DataGatewayClusterDataSourceUser cmdlets. (Those last two are the ones I did a very small amount of work on to make more user friendly.) You can pipe them all together and get the data source & permissions data out of your On-Premises Data Gateway connections. This makes it very simple to gather the data you need for an audit, or other governance needs.

Other important questions these cmdlets can answer are things like:

• How many On-Premises Data Gateways do I even have in my organization?
• How many of those are Enterprise vs. Personal Gateway.
• How many member-servers do I have in a Cluster?
• What version of the Data Gateway software are these machines running?

I have included a Gist with some of the data I’m collecting out of the
On-Premises Data Gateways with PowerShell. I hope you find them useful

NOTE: I am a Power BI Admin and am an Administrator on every On-Premises Data Gateway I have run these cmdlets against. If you’re a Power BI Admin but not an Administrator on an On-Premises Data Gateway, you may not get all the results you need. Furthermore, in my testing, it seems like you need to be at least a User each of the Data Sources in order to retrieve it. Two things I want to stress about that last sentence: 1) My testing has been limited to only two tenants to which I have admin access. 2) Again, these cmdlets are in preview, so the behavior may change as more work gets done on finalizing them.

It’s great to see the Power Platform team joining the other product teams which allow you to easily manage and report on your assets, with PowerShell. 👍

On Tuesday October 8^th at 3 PM EDT (GMT -4) the PowerShell Virtual Group of PASS will host Tyler Leonhardt presenting: Tips & Tricks for the PowerShell extension in Azure Data Studio.

Tips & Tricks for the PowerShell extension in Azure Data Studio

The PowerShell extension in Azure Data Studio now provides the best-ever experience for developing PowerShell code against your SQL Server instances. You can run a PowerShell script against your SQL Server instance and see the changes to that instance in Object Explore or via T-SQL query, all in the same editor. Come learn from Tyler Leonhardt how to make your PowerShell development experience even easier.

Tyler Leonhardt

Tyler is a developer on the PowerShell team at Microsoft. In addition to PowerShell itself, he works on the Visual Studio Code extension for PowerShell, helped ship the PowerShell experience in Azure Functions v2, and more! He also live streams coding at work. He’s a developer experience nut, loves coffee and loves to customize mechanical keyboards.

Registration Links

Use this link to register for the webinar with your PASS account. (Scroll down and click the Register button) http://powershell.pass.org/Home.aspx?EventID=14350

PowerShell extension for Azure Data Studio

If you haven’t started using Azure Data Studio yet, you should! The new tool is a companion to SSMS and is an Open Source Project based on VS Code, that anyone can contribute to. The best part is, the PowerShell extension from VS Code has been added to the Azure Data Studio marketplace and will receive regular updates as the version in VS Code gets updated. This new option is by far the best experience for developing SQL PowerShell code. https://docs.microsoft.com/en-us/sql/azure-data-studio/powershell-extension

I will update this post with a link to the recording once it’s posted.

Remove-PowerBIReport

The Remove-PowerBIReport cmdlet can come in handy in CI/CD scenarios with Power BI. Let’s say you have copied a Report from a QA Workspace over to a Production Workspace, and chose not to overwrite the Dataset in the Production Workspace. (For clarity: In this scenario, the Dataset in the QA Workspace is connected to a QA database, while the Dataset in the Prod Workspace is connected to a Production database. And you want it to stay that way). This is a great way to test your Reports before you release them for everyone to see, but you’re left with the old version of the Report in that Production Workspace when you’re done. The Remove-PowerBIReport cmdlet can remove the old version of that(those) Report(s) in a Production Workspace after you have copied over the version from QA.

I have posted a Gist to demonstrate this scenario & technique, but I haven’t had time to blog about it just yet. Maybe in a week or two.

Get-PowerBIActivityEvent

You can now download Power BI Audit Logs using the new Get-PowerBIActivityEvent cmdet. This cmdlet saves you from having to be granted “Audit Logs or View-Only Audit Logs role in Exchange Online” permissions in Office 365. Instead, as long as you’re a Power BI Admin, you will be able to retrieve the audit logs as-is. This can be extremely useful for Power BI Governance, because being granted the View-Only Audit Logs role gives you access to all audit records across Office 365. Some organizations will not grant that capability to everyone who is a Power BI Admin.

The Get-PowerBIActivityEvent cmdlet is a little quirky (at least, to me it is). But hey, this is the first version of the cmdlet, so let’s file items on GitHub with how it could be improved. In the meantime, let’s check out how to use it, and why it’s better than the Search-UnifiedAuditLog cmdlet in the long run.

Let’s go through some limitations:

1. You must be granted the Power BI Admin role.
2. You must select a timeframe within a single day, and by day, I mean date. You cannot pick a 24-hour period which spans two different days. You can’t even pick a 1-hour period which spans two days.
3. You have to use a very specific date format. Quoting from the Help files it says: “It should be in UTC format and ISO 8601 compliant“.
4. It only outputs the results as either a JSON String or a JSON object.

The good news?

1. I took care of limitations 2 & 3 in the above list for you.
2. The Get-PowerBIActivityEvent cmdlet is not limited to outputting only 5,000 rows, or so I’m told. I don’t actually have a tenant with over 5,000 Power BI Audit Log Events in a single day, so I can’t verify.
3. When you choose to have the output as a JSON String, it’s farily easy to get the file imported into Power BI Desktop. Maybe 4 or 5 steps? Hopefully, my friend Reza Rad ( blog | twitter ) can chime in with an easy to follow blog post on how to do that.

I have put together two options for you and posted them in a Gist. The first option goes through the last 90 days of your Power BI Audit Log and creates a separate .json file for each day. The second option goes through the same 90 days of your Power BI Audit Logs, but this time it puts everything into a single file. Pick the approach that works best for you.

To make the switch, just hit Ctrl + Shift + P to bring up the Command Pallette. Once it’s up, type `Show Session Menu` and then select that from the drop-down.

Once you’ve selected that, a new menu-list will appear. Next, pick which version of PowerShell Core you want to run. (You can even run one of the Preview versions of PowerShell Core, download from here.)

After this, the PowerShell session currently running in your PowerShell Integrated Console will close; and a new PowerShell session running PowerShell Core will start with your selected version of PowerShell.

Yep, that’s all there is to it.
It’s easy, it’s peasy. And I hear, it’s even lemon-squeezey 😃
Cheers!

Scheduling SQL Notebooks in Azure Data Studio

Let’s start by taking a look at when you click that “Schedule Notebook” button in Azure Data Studio; which brings up a dialog asking you which instance of SQL Server you want to schedule this SQL Notebook on. After that, you are present with a side panel asking you some specifics about how you want to configure your SQL Notebook to run.

You’re presented with a few options and this varies a little from your typical SQL Agent Job, so let’s dig in a little. First, you’ll see an option for the Storage Database, this is a key part which is new. When you choose your Storage Database you’re choosing where SQL Server will store the SQL Notebook that you’re currently scheduling. You’re also choosing where it will store the results from that SQL Notebook every time you run it.

The next option, Execution Database, is where the SQL Notebook will start running from. In this case, I’m choosing to start BPCheck.ipynb (Best Practices Check from the SQL Tiger Team) in the master database. This is pretty similar to what you would do when scheduling any T-SQL script. The rest of the options are pretty standard too, so I won’t go through those.

Once you get through the step above, you should have two new tables in whichever database you choose as your Storage Database. You should also have a new SQL Agent Job. You’ll be able to see there is a new tab under [Manage >] SQL Agent, called Notebook Jobs. Under there you’ll find your newly scheduled SQL Notebook (or, unscheduled in my case).

OK, enough about the GUI, let’s talk about the new Invoke-SqlNotebook cmdlet

Invoke-SqlNotebook cmdlet

I have best test-driving the Invoke-SqlNotebook cmdlet for a while now and I like it. Granted, I do a ton more Power BI / Data Visualization / Data Lake work than I do relational SQL Server these days; but I have run the cmdlet at least a hundred times already.

First up, let’s take the Invoke-SqlNotebook cmdlet for the most basic drive possible. We’ll give it only 3 input parameters: -ServerInstance, -Database, & the name of SQL Notebook file we want to run. Since we didn’t specify anything for the -OutputFile parameter, the cmdlet will go ahead and build an output file with the same name as the SQL Notebook we told it to run, and it will append “_out” to the end of the name. (I’ll cover some additional options for file naming later).

Once the output file is generated, if you have the PowerShell extension loaded in Azure Data Studio, you can open the output file by simply saying Open-EditorFile and telling it the name of the file. Note: on my machine it usually takes about 30 seconds to open that results-notebook.

As you scroll through that new file, you’ll see all the results from all the queries which were run. This is great because you can now easily save the history of your SQL Notebooks and version control the results separately from the code itself.

If you have a notebook that you need to run on multiple Instance or multiple Database, and have the results file named to indicate where it was run against, I have some code you can use to make that happen. I have posted a gist with some code I whipped up while testing the cmdlet.

In this example, I’m using $($_.NetName) to grab the name of the SQL Server instance from the pipeline and add it to the name of the name of the -OutputFile that I’m building. I also went ahead and grabbad the current datetime and added it to the file name as well. You could replace the datetime or add something else, like the name of database you’re running the notebook against.

NOTE: Realistically, the $datetime = Get-Date -Format yyyyMMddhhmm portion of the code should be inside the foreach loop with the call to the Invoke-SqlNotebook cmdlet. I placed it at the top of this example just to make it a litle more readable.

That’s about all I have time to show off today. Please let me know what you plan to do with the new SQL Notebook automation capabilities? 😃

Before I go any farther, shout out to Rohn Edwards ( blog | twitter ) for his session at PowerShell Summit which got me started with this. (And then being polite enough to DM back-n-forth with me for like three hours straight while I struggled to get my head around how to make them happen.)

The PowerShell cmdlets for Power BI are extremely useful, but they do have some quirks. I don’t mind the quirks for two reasons:

1. the cmdlets are open source so we can submit pull requests to fix the quirks.
2. back to the whole point of this blog, PowerShell lets you make it better, all by yourself!

If you’re trying to use the PowerShell cmdlets for Power BI you may have noticed that the Get-PowerBIReport cmdlet is a little ‘odd’. It allows you to pipe input in from the Get-PowerBIWorkspace cmdlet, which is great, we like that. However, if you to get a list of reports inside a workspace, and you only want to use the Get-PowerBIReport cmdlet, you have to know the Id (a GUID) or have a Workspace object. Neither of those are easy to type out, which is why I whipped up a few Argument Completers instead.

I have the code below in my PowerShell profile so that it’s ready every time I open a PowerShell session. (read more about how to do that here)

Once that’s loaded up, PowerShell will give me a list of Workspaces that I have access to. This makes finding and working with the reports much smoother.

I have 4 other argument completers for the Power BI PowerShell cmdlets in this gist that I think everyone will find handy. Hopefully we can get something like this added to the Power BI modules themselves so you do have to add this on your own. But until then, I hope this makes using the Power BI PowerShell cmdlets much easier for you

BTW, big shout-out to my friend and coworker Caroline Bell (twitter) for reading the Docs files to me about why I couldn’t get this to work the first time I tried

However, one detail they left out of that post is how to clone the users & groups, and their respective permissions from one Workspace to another. It’s a pretty simple thing to do, with one not-completely-obvious quirk to it.

Currently, you have to be a Power BI Admin in order to retrieve the list of users/groups & their permission levels. If you are one, the rest is pretty straight forward. Just make sure you have the MicrosoftPowerBIMgmt module installed, run Login-PowerBI, then run the script below.

There is one unfortunate kink to the script above. You’ll notice this line WHERE { $_.AccessRight -ne ‘Viewer‘ } in there because at this time, the Add-PowerBIWorkspaceUser cmdlet doesn’t handle adding a user and granting them the new Viewer role. I have already filed an issue in the GitHub repo for the Power BI PowerShell cmdlets. Please feel-free to comment & Up-Vote this issue if you need this fixed too.

That’s pretty much it for now, I hope this helps!

Update-Module SqlServer

The SQL Assessment cmdlets come with over 40 pre-configured Best Practices checks that can be run against both Windows & Linux SQL Server instances. These checks are based on the Open Source BPCheck project available in GitHub, and they automatically adjust and run only the checks which are relevant to the version of SQL Server you are running. Even better, you can build your own customized checks tailored to your specific environment, and include them in the SQL Assessment checks when they are run. Furthermore, you can even setup profiles of which checks you want to have run (you don’t have to accept all of the defaults). I highly recommend you checkout the official documentation on this new topic: https://docs.microsoft.com/sql/sql-assessment-api/sql-assessment-api-overview

My favorite check so far is the one that tells me if my SQL Servers are up to date with the latest available CU. Even better, I can leverage my Registered Servers file or Central Management Server to run that check against all the SQL Server Instances across my whole environments with just a few lines of PowerShell code!

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' |             
WHERE { $_.Mode -ne 'd'} |            
foreach {            
Get-SqlInstance -ServerInstance $_.Name |             
Invoke-SqlAssessment            
} | Out-GridView            

NOTE: Please keep in mind that the SQL PowerShell above is not really what you would call a ‘Best Practice‘. It’s just meant as a way to easily illustrate what is possible. This is not quite how the code would look if I were wanting to run this across my entire environment, but it gets the point across for this demonstration.

These two new cmdlets are being presented as a ‘preview’ which means they could still change a little based on real-world customer feedback from people like you. While I have been testing out the usability of these cmdlets for several months now, I didn’t have access to a real-world SQL Server environment to test them out against. With that said, I’m sure there are plenty more improvements to be made, and I hope you’ll speak up and let the team know what to do by submitting ideas via aka.ms/sqlfeedback. I can’t wait to see how these cmdlets evolve now that you’re able to use them!

Here’s the full text of what that Message highlighted in the screenshot above says: “Product version 13.0.1742 is not the latest available. We recommend keeping your SQL Server up to date and install Service Packs and Cumulative Updates as they are released.”

1. How do you use PowerShell to extract an .ISPAC from the SSIS Catalog?
2. How do you automate generating the .ISPAC file from an SSIS Project in the first place?

How do you use PowerShell to extract an .ISPAC from the SSIS Catalog?

For the first one, I wasn’t sure if it was possible, but I assumed it should be possible. The way to find out is to start grabbing objects and piping them to the Get-Member cmdlet. First, I tried the Packages themselves, then I tried the folders. No luck on either of them. Then I went poking around the Catalog in SSMS and right-clicking on the various nodes to see which options popped up. Finally, when I got to the Project itself I was given an option to Export.

You’ll notice with the code below, that we’re able to extract that Project-object into a variable named $Proj, but when we pipe that to the Get-Member cmdlet we do not see a method called Export.

<# This is the SSIS Project once it's deployed #>            
$Proj = Get-Item 'SQLSERVER:\SSIS\localhost\SQL2017\Catalogs\SSISDB\Folders\Provider Solution2\Projects\TestSSISProject'            
$Proj | Get-Member -MemberType Methods

As it turns out, it’s a good thing I wrote the importing article first. Otherwise, I probably wouldn’t have spotted the method we need. There is a method named GetBytes that I totally skipped over at first. When I couldn’t find anything I started thinking back through the steps I did to import the .ISPAC file. I remembered this piece of code [System.IO.File]::ReadAllBytes($ProjectFilePath) converted the .ISPAC file into a very long string of byte numbers.

<# Dont run this piece of code, you will hate me. #>

$Proj.GetProjectBytes()

So, I tried it. And I saw bytes! And about 9 minutes later, I was able to do something with my PowerShell window again

In PowerShell cmdlets are supposed to be in pairs:

• If there’s a Get there should be a Set
• If there’s an Add there should be a Remove
• If there’s a Read there should be a Write

Since we used ReadAllBytes
method to pull the file in off of the hard drive, it stands to reason that we would use a WriteAllBytes method to push the data back out to a file on the hard drive. I did a quick search and sure enough there was a method by that name. After a quick look at an example of how to use it, I came up with the following code.

<# This is the theory I have #>            
[byte[]] $ProjBytes = $Proj.GetProjectBytes()            
[System.IO.File]::WriteAllBytes('C:\temp\ASSISPrj.ispac',$ProjBytes)

This created the .ISPAC file on my hard drive in a flash, which was great! Of course, who knows if what’s in that file is usable? It could be a big jumbled mass in there for all I know? To find out what I had done, first I renamed the file to .zip and looked inside.

Looks good to me.

Next I used the code from the previous post to deploy the .ISPAC file to a different folder within the same SSIS Catalog. Success again! But still, I don’t know if it’s really usable or not, so I run the package. Great news! The package that is supposed to succeed, succeed. The package that is supposed to fail, failed. Now the neither package really did anything, it didn’t move any data or anything. But at this point I’m fairly convinced that I can now export an .ISPAC file with the SSIS PowerShell Provider.

Quick thing to note:

One this I figured out [the hard way] during this, is that you can deploy the .ISPAC to a folder with a different name, but the Project must still have the same name.  It’s embedded within the .ISPAC file, and apparently, it gets checked on deployment. You may have already known this, but I didn’t (or if I did know, I forgot)

How do you automate generating the .ISPAC file from an SSIS Project in the first place?

Now to answer the second question, I used the phone instant-message-a-friend option. I asked Mat the Mad M-Man Masson ( blog | twitter ) what to do? He told me to give this GitHub project a try. If you’re looking to automate building a Visual Studio SSIS project (dtproj) into an .ISPAC file, you should give this project a try.

The project includes New-SsisDeploymentPackage, “A PowerShell Cmdlet that builds a deployment package from a Visual Studio Project File”. If you do, try it out, please let me know how it works out for you?

The recording for this session has been posted to the PowerShell VG YouTube channel:

Install & Configure SQL Server with PowerShell DSC

Get on the cutting edge by implementing infrastructure as code for your SQL Server environment. Using PowerShell Desired State Configuration (DSC) we’ll look at how to take a newly built Windows Server and get it ready for prime time while discussing the benefits associated with infrastructure as code. – Setup Pre-Requisites – Install SQL Server – Configure SQL Server – Make SQL Server Application Ready

Jess Pomfret

Jess Pomfret is a SQL Server DBA at Westfield Group in Westfield, Ohio. She started working with SQL Server in 2011 and currently works as part of a team administering over 100 instances ranging from 2005 to 2017. She enjoys the problem-solving aspects of performance tuning and automating processes with PowerShell. She also enjoys contributing to dbatools and dbachecks, two open source PowerShell modules that aid DBAs with automating the management of SQL Server instances. She grew up in the South West of England and outside of her DBA life enjoys Crossfit, cycling and watching proper football.

Registration Links

If you’re already a member of PASS, use this link to register for the webinar with your PASS account: http://powershell.pass.org/Home.aspx?EventID=13325

If you’ve never heard of PASS and just want to learn how to “Install & Configure SQL Server with PowerShell DSC” without any commitment, use this link to register for the webinar: https://attendee.gotowebinar.com/register/3184438960190324493

Wondering what DSC is?

Check out the SqlServerDsc module on the PowerShell Gallery: https://www.powershellgallery.com/packages/SqlServerDsc/

Now, Matt ( blog | twitter ) is certainly a “smart guy”, there is no doubt about that, but the question I wonder is: Is Matt Masson a “lazy guy”?  Like me. 

You see Matt’s code example used 14 lines of code to deploy a single .ISPAC file to a single package catalog.  That seemed like a little too much for me.  I set out to see if I could streamline the steps a little and I was able to knock off ~4 lines of code.  This might not seem like much, but for me, it made the deployment process a lot more understandable. I accomplished this simply by letting the SSIS PowerShell Provider do some of the work.

I also think this streamlining is crucial to show how this technique could be very useful in a DevOps or Scale-Out scenario.

In today’s world, people expect to be able to commit one change, then have the entire project re-deployed and automated tests run against it.

Just like in the post on executing an SSIS package with the SSIS Provider, I’ll use SSMS to save me some work and capture the path I need to get started.

Once again I’m working with one of Andy’s demo files which has an .ISPAC file named TestSSISProject.ispac for this deployment.

I plan to do a Pull Request to add an SSIS Provider example to the “Deploy an SSIS project with PowerShell” Docs page later today.

What’s next?  The Docs pages show lots of capabilities that are already available to choose from. I think I want to figure out how to deploy a single SSIS package to an SSIS catalog using PowerShell.  What do you want to be able to do with SSIS & PowerShell?

(Please comment with what you want to do, or cool things you’ve already done with SSIS & PowerShell)

Small update for this post: I received several requests to show how to Export an .ISPAC file from the SSIS Catalog, so I wrote a post about that, you can find the link here: https://sqlvariant.com/2019/05/exporting-an-ispac-from-the-ssis-catalog-via-the-ssis-powershell-provider/

Here’s a list of the posts in Andy’s blog series:

• examined how to install Docker for Windows, pull a test image, search for a SQL Server on Windows image, install and interact with it (Part 0)
• shared why I want to do all this (Part 1)
• shared one way to execute SSIS on a container in Part 2
• shared a failed attempt to add an SSIS Catalog to a SQL Server-on-Windows container in Part 3
• shared a successful attempt to create an SSIS Catalog in a container (Part 4)
• shared how to deploy SSIS Projects to an SSIS Catalog in a container (Part 5)
• explored one option for Executing Packages in an SSIS Catalog in a Container (Part 6)

In Part 6 Andy showed that it is possible to execute a package that is within an SSIS Catalog inside a Docker container.  However, he used the old dtexec command to do it.  I don’t blame him for resorting to a tool that he already knows well, after already kicking down so many barriers to get SSIS deployed & running within a Docker container.

But I still felt that since Andy had already used PowerShell in so many steps throughout the blog series, the series really should include an example of how to execute an SSIS package with PowerShell.  The only problem here was that I didn’t have many SSIS packages lying around in Docker containers for me to just try this.

Thankfully, Andy was very generous with his time last Thursday morning.  We did a web meeting, Andy shared his screen and he let me tell him what commands I thought he should try.  In the end, Andy was the one who figured out how to execute the SSIS package with PowerShell, I just showed him how to get there, despite the error messages he encountered.

At the end of our session, we found that the easiest way to execute an SSIS package in PowerShell was to navigate to the location of the package within the SSIS catalog, and then use the Get-Item cmdlet to grab the package and execute it.  You can save some typing by navigating to he package you want in SSMS, and then right-click on it and choose “Start PowerShell”.

Note: The “.” tells the Get-Item cmdlet to grab the current item.  You could also navigate to the folder the package is in, and then execute it using it’s DisplayName property.

(Get-Item . ).execute($false, $null)

After grabbing the package with Get-Item, we then use the .execute() method and pass it two parameters to run it.

The “10031” number you’re seeing displayed there is the Execution ID of the package.  So when we see that, we know the package was successfully started.

Obviously, you have some easier options to do this in T-SQL or SSIS itself, but what if you’re already running some predecessor steps in PowerShell?

If you’re already doing work in PowerShell, and just happen to need to execute an SSIS package, this is a relatively easy option.

In addition, Andy and I figured out some other steps you could take to make executing SSIS in PowerShell even easier.  Since Andy is the expert in SSIS, not to mention a better story teller than me, I will let him show folks which pieces make the most sense.

In the meantime, I hope this post inspires you to try some things that you didn’t even know were possible! 👍

Big thanks to Tony Green ( t ) for bringing this to my attention.

I took this opportunity to build out a repo on GitHub with all of the scripts I used that day.  You can find that repo here.

Going forward, I will try to post all of my presentation scripts in this repo.  And please call me out if I forget to! 

I do work closely with software vendors from time to time, to help them get their data automation or user experience right better.  However, the thoughts expresses here do no represent those companies, no matter how many times I quote their official blogs or documentation.

Cheers!

I’m getting a new work laptop next week: Hooray!! 😃

I’m going to have to install A LOT of software: Opportunity! 👍

Problem: I have a lot of meetings next week, and I don’t want to be distracted by installing a bunch of different pieces throughout the day.

Solution: Chocolatey! 🎂

What is Chocolatey?

“Chocolatey is a package manager for Windows (like apt-get or yum but for Windows). It was designed to be a decentralized framework for quickly installing applications and tools that you need”
(excerpted from Chocolatey.org)

How does it work?

First you install Chocolatey, then you install the packages (very similar to installing a PowerShell module).

What the heck does this have to do with Business Intelligence?  Well, it takes a lot of different pieces of software for me to do my work as a Business Intelligence Developer.  Although in the future, with VS Code  & Azure Data Studio, some of these smaller pieces of software may turn into extensions for those editors.  Who knows?  But if you take a look at line #9 of the script below, that single line of code will install Power BI Desktop for me.

I took a look through the list of Chocolatey Packages and found a ton of stuff I use.  Unfortunately I didn’t find packages for any of the following tools:

• DAX Studio
• DAX Formatter
• Power BI Helper

What am I missing?

As I mentioned, I already found packages for a lot of software I already have installed.  My question to you is: What packages am I missing that a Business Intelligence Developer who is starting to work with Databricks, should have?

Please comment with a link to the package!

Want to give it a try?

If you have never tried Chocolatey before, the steps are pretty simple.

• 1) Open PowerShell as an Administrator.
• 2) Run the first line of this Gist I embedded (below).
• 3) pick any line of code below and install one of those packages.

I plan to keep this Gist public, and update it with more packages as I find /am told about them.

Cheers!

• 9:00am: SQL Server Memory Internals & Troubleshooting – by Amit Bansal
• 10:15am: Inside Kubernetes – An Architectural Deep Dive – by Anthony Nocentino
• 11:30am: How to write a kick a** anything – by Blythe Morrow
• 1:30pm: We need DataOps – NOW – by Steph Locke
• 2:45pm: Automate the Pain Away with SQL Server 2019 – by John Sterret
• 4:00pm: Creating a SQL Server Test Lab On Your Workstation – by David Fowler
• 5:15pm: PowerShell for the SQL DBA – by Ben Miller

*All Eastern Daylight Time (EDT = GMT -4)

Registration is free, and it’s open now. See you on April 11th!

The online platform we’re using is limited to 1,000 connections so that’s how many people who can register.  If you’re planning to attend I recommend you register before we max out.

Looking forward to chatting with you on the day of the event

Turns our, it’s extremely easy to do with the ImportExcel PowerShell module.

Obviously, you have to have the module installed, and a copy of AdventureWorksDW2017 db restored to a SQL Server.  After that,  all you have to do is loop through the tables, ‘query’ them with the Read-SqlTableData cmdlet, and pipe the results to the Export-Excel cmdlet.

I did some trial and error with this yesterday.  I settled on exporting all of the Dimension tables to separate Worksheets within the same Excel file, and exporting all of the Fact tables to their own individual files (since they tend to be much larger).

I also tried out all tables in one file, as well as all tables in individual file.  I created a gist with all of these options.

If you decide to try this out yourself, the most things to do are to:

• Install the SqlServer & ImportExcel modules
• Import both the SqlServer & ImportExcel modules into your PowerShell session
• Change the name of the SQL Server instance in both the SQL Provider, and in the Read-SqlTableData cmdlet

#Requires -Modules SqlServer             
#Requires -Modules ImportExcel             
<# The AdventureWorksDW2017 only has 29 tables and they're all under 1 million rows.#>            
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables            
            
<# Scenario #1 A) all Dimensions in a single file,
    and B) each Fact table in their own file. #>            
             
<# A) Every Dimension table in a worksheet named after the table, the same Excel file #>            
dir | WHERE { $_.name -like 'dim*' } |            
foreach {            
"$($_.Name)"            
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |            
Export-Excel -Path "c:\temp\AW\AdventureWorksDW2017_Dims.xlsx" -WorksheetName $_.Name -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors             
}            
            
<# B) Each Fact-table in it's own Excel file, named after the table. #>            
dir | WHERE { $_.name -like 'fact*' } |            
foreach {            
"$($_.Name)"            
Read-SqlTableData -ServerInstance LocalHost\SQL2017 -DatabaseName AdventureWorksDW2017 -SchemaName dbo -TableName $_.Name -OutputAs DataRows |            
Export-Excel -Path "c:\temp\AW\$($_.Name).xlsx" -WorksheetName $_.Name -ExcludeProperty IsReadOnly,IsFixedSize,IsSynchronized,SyncRoot,Count            
}

If you end up giving this a try, I would LOVE for you to comment and tell me how easy or hard it was for you to do?

Azure Data Studio is a great little cross-platform SQL editor and the team has been working hard to add some of the most requested features.  To make it easy for customers to request new features & report bugs they include a little smiley in the lower-right corner for you to provide feedback that can go directly into their GiuHub repo.  Although, it also give the opportunity to tweet about Azure Data Studio.  I think part of the problem is that when you click on the smiley in the lower-right corner it actually says “Tweet feedback” not ‘Send us feedback’.  I was confused by this myself and ended up tweeting to Buck Woody about it.  He pointed out the “Submit a bug” text.

Clicking on “Submit a Bug” will bring up this little window where you can give the bug a name, fill in some details, and it even let’s you switch back to your Azure Data Studio window so you can copy the error message you received.

After that, you click on the Preview in GiHub button which takes you to a page in the Azured Data Studio repo with everything you already filled out in the form.

Again, once you’ve done it once, this seems like a waste of a blog post.  But it seems like I’m not the only one who didn’t wanted to just tweet an error they were running into in Azure Data Studio.

Update-Module SqlServer -AllowPrerelease

But wait, there’s more.  In order to use the Invoke-Sqlcmd cmdlet on PSCore you’ll also need to download PSCore 6.2 which, as of this writing, is currently in Release Candidate status.  I guess that makes this sort of a double-preview

For information on how to “installing a PowerShell Core Preview release for Linux” check out the docs page.

If you’re on a Mac, you’ll currently need to run pwsh-preview to launch v6.2 of PSCore.  Once you have the module updated & PSCorev6.2 installed you’re ready to go.  Nothing else needed.

To install PSCore v6.2 on MacOS, after you have run ‘brew cask install powershell’ then run the following code to allow it to access the preview version:

brew tap homebrew/cask-versions

After that, run this to install the preview version of PSCore:

brew cask install powershell-preview

Finally, to launch the preview version of PSCore run the command below and off you go

pwsh-preview

If you’re still on an earlier version of PSCore and are unable to install PSCore 6.2 right now, you can still download preview of the SqlServer module to get the latest fixes and new features.  You just won’t be able to use the Invoke-Sqlcmd cmdlet.

Another quick thing to note is that this is like a v.0.0.1 of Invoke-SqlCmd on PSCore; it does not have all the bells & whistles of the version of Invoke-Sqlcmd for [full blown] Windows PowerShell.  Obviously, more features will be added over time, but the basic functionality was ready to for customers to start “kicking the tires”.

The functionality listed in the release notes notes are as follows:

“* Initial support for Invoke-Sqlcmd on .Net Core (-Variable, -DisableVariables, -DisableCommand,
 -QueryTimeout, -ConnectionTimeout, -Hostname not supported yet). ”

If you run into an issue with the cmdlet, please do comment on the PowerShell Gallery page for the SqlServer module, Matteo is really good about responding there.

I just pushed out a new (prerelease) version of the SQLServer PowerShell module at https://t.co/mnYCjyVouu.

Surprise, surprise! If you are on PowerShell 6.2.0 (also preview), you’ll be able to run Invoke-Sqlcmd (basic functionality).

Enjoy! pic.twitter.com/Sol1tASTjw

— Matteo Taveggia (@matteo_taveggia) March 5, 2019

My friend Reza Rad ( blog | twitter ) reached out to me this morning because he was running into some issues with a PowerShell cmdlet for gathering some log data. To make a long story short, the cmdlet in question had apparently been built with expectation that customers would want to gather results from the last day, or maybe even the last week, but not much more than that.

My friend was trying to grab 3 months work of data all at once and it just wasn’t working. The logs went back for months on end, but you just couldn’t pull that many days at once without running into problems. We quickly realized that just asking for the logs one day at a time was the easiest way to guarantee that we got everything. But who wants to sit there and change the to/from dates 90 times??

I joked that you could write that code in Excel if you wanted but I was sure that there was an easy way to accomplish this by combining a few simple PowerShell tricks:

· Number range 90..1

· Foreach foreach {}

· Date Add (Get-Date).AddDays(–1)

· -Append

PowerShell has a feature which allows you to emit all the numbers in a range. I admit it can sound like a useless trick until you have a reason to combine it with something else. (Like the simple random raffle number generator I still haven’t blogged about.) At which point you’re all like: OMG this is stupid-simple and yet perfect!

The number rage is normally shown as something 1..10, but the range could start anywhere, like 1433..1521. In our case, we can use this to count backwards from 90 [days] like this:

90..1

Again, that’s not much of a big deal, but since we can pass those numbers in to DateAdd via a foreach loop, it completely amplifies what you’re doing.

90..1 |            
foreach { (Get-Date).AddDays(-$_) }

Now that we have our list of 90 dates ready, all we have to do is combine it with the cmdlet that retrieves our data. In lieu of outing the product team who built the less-than-optimal cmdlet which started this problem, I’ll substitute the Get-SqlErrorLog cmdlet in its place.

90..1 |            
foreach {            
$Start=((Get-Date).Date).AddDays(-$_);            
Get-SqlErrorLog -ServerInstance localhost\sql2017 -After $Start -Before $Start.AddDays(1) |            
Out-File -Append -FilePath C:\temp\SQLErrors.txt            
}

3 quick things to point out here.

· I made sure that we were working with the date as of midnight instead of the current time by adding ().Date) around Get-Date.

· It’s probably obvious but just to be clear, the $Start= is setting the date as we’re looping through the code, and to give us a full day worth of logs I’m using .AddDays(1) to add a single day to date within the $Start variable to get the next day.

· Since the problem here is retrieving the logs, not storing their results, I’m using the -Append parameter to store them all in the same file.  Of course, we don’t even need the -Append parameter if we were piping the results to the Write-SqlTableData cmdlet we wouldn’t even need the -Append switch, but maybe a SQL Server table was overkill for this particular task.

Obviously, the cmdlet that will not [yet] be named needs to be fixed. In the meantime, a couple minutes & a couple lines of PowerShell code to save yourself from an hour or more of manual work downloading daily log files is a great thing to have in your back pocket.

You can download a .zip file with the scripts here.

If you’re looking for the slides or the video, those are available over on the Ignite site: https://myignite.microsoft.com/sessions/57084

(Note: It looks like you have to login in order to download the slides.)

Please comment if you have any questions about the scripts!

I’m going to automate some thing in SSRS & SSAS, but that’s not what I’m going to write about today. Instead, I’m just going to post some pointers to help you install/update to the latest version of the SqlServer module more easily;

I put together an article for MSSQLTips on Best Practices for working with the SqlServer module that is available in the PowerShell Gallery.  The article has some handy details in it but here are the highlights:

You need to run this command from an elevated PowerShell session to install the latest version:
Install-Module SqlServer

I didn’t mention in the article that you will probably need to add two additional parameters to upgrade your SqlServer module when the team releases new versions:

Install-Module SqlServer -AllowClobber -Force

If you want to download the module from the PowerShell Gallery so that you can copy it to a server that isn’t connected to the internet, use:

Save-Module SqlServer -Path c:\temp\

And then copy the folder to the C:\Program Files\WindowsPowerShell\Modules\ directory on the server you need it on.

Whatever you do, try to avoid using the -Scope CurrentUser option.  You may need to use it if you don’t have high enough permissions, in that situation go ahead and use -Scope CurrentUser.  However, if you can avoid using it, do so.  I have lost a lot of time to having used this option.

Install-Module SqlServer -Scope CurrentUser

Finally, the SSAS cmdlets and my improvements to the Provider have now been incorporated into the main SqlServer module.  I’m working on an article to walk people the benefits of using that.  I hope to share the draft next week or so.

If you run into any trouble with the SqlServer module, please don’t hesitate to reach out to me, I’m happy to help!  🙂

Here’s an excerpt from the Power BI blog post from this weekend:

“With this August 2017 preview, users can create Power BI reports in Power BI Desktop that connect to any data source, and publish their reports to Power BI Report Server. There’s no special configuration required to enable this functionality“

You can Download Power BI Report Server August 2017 Preview here.

You will also want to Download Power BI Desktop (August 2017) for Power BI Report Server.

Once you’ve done that, you can start taking your existing Power BI reports (.PBIX files) and saving them to your Power BI Report server, instead of having to upload them, and all the data they rely on, to the cloud. Which means that you can now build rich reports like this one below right off of your SQL Server database. Better yet, your coworkers interact with it from your internal Power BI Report Server.

Wait, I thought Power BI Report Server had already gone GA?  But this says it’s in Preview

Out of an abundance of clarity I want to mention that Power BI Report Server was made Generally Available (read: RTM) back in June of 2017. That version is still available and only able to connect to SSAS as a data source.

Back to the Power BI Report Server August 2017 Preview News:

What new features can you expect when you install Power BI Desktop (August 2017) for Power BI Report Server?

Support for new table and matrix viuals

The August 2017 preview of Power BI Report Server includes support for the new Power BI table and matrix visuals. To create reports with these visuals, you will need an updated Power BI Desktop release for the August 2017 preview. It cannot be installed side-by-side with Power BI Desktop (June 2017) release.

To me, this is massive news! If you think of the SQL Performance Dashboard Reports project which already allows you to publish reports about your SQL Server environment to a central SSRS server so that your coworkers can see what’s going on with the SQL Server anytime they want. Now, you can take that information further with the rich interactive visuals available in the Power BI toolset. Plus, Power BI is not only very easy to use, but it has tons of online resources available for it. The Guy in a Cube YouTube channel is a great place to start.

But I don’t think this is massive news because DBAs and other data professionals can now build pretty dashboards about how their SQL Server is running. Building Power BI reports about your SQL environment is just practice. Now, data professionals have access to a full ecosystem to be able to serve up rich visualizations about whatever their coworkers need to know about, post it internally using existing domain security, and they can put it all together in just minutes!

Community Visualizations

I think there will be a lot more interest in the dbareports project now that it can be published internally. I’m very excited to see what kind of data visualizations data professionals come up with to share with their coworkers and the community!

I have already started contributing small modifications / enhancements to the dbareports Power BI files and I hope to contribute several more over the coming days as time allows. I can’t wait to see what enhancements everyone else comes up with.

Any Issues?

I did run across a couple issues this weekend that I flagged to the team. They might be unique to my environment / setup, so I’ll share them here in case you run into them too:

1. Installing the August Preview blew away my Saved Data Source credentials I was using for each one of my reports.  I had to re-setup Data Source creds one-by-one (was early in the a.m. and for once didn’t think to use PowerShell first).  None of the previous versions of PBIRS, since January, had ever done this to me.
   1. Worse yet, and the reason I will have to rollback to GA: My saved credentials get lost Every.  Single. Time.  I update a report.
   2. NOTE: This has not affected any of my reports where I was using “Log into the data source – As the user viewing the report ” so you may be completely unaffected by this.
2. Could not get any reports to render using http://<ComputerName>:8081/PBIReports.  Was only able to get reports to render after trying http://LocalHost:8081/PBIReports
3. Power BI Desktop (August 2017) for Power BI Report Server doesn’t seem to want display a TreeMap visual to you.  However, as you can see from my screenshot above, the Power BI Report Server will render the TreeMap just fine.
4. As of right now, it seems that they maximum size your .PBIX file can be is 100 MB.  Keep that in mind if you’re working with imported models.

If any of you run into these issues too, please drop a comment!  🙂

Also keep in mind that with this preview version, while all data sources are now available, still not every single feature has been turned on just yet.  For a list of what has been turned on, please have a look at the official preview documentation.

I was at Ignite in Atlanta last year and it was an excellent experience. I’m really looking forward to meeting new people and running into old friends.

If you’ll be at Ignite, here are the details on my session:

Achieve more in less time using the new SQL PowerShell

• Aaron Nelson
  

  Microsoft’s official PowerShell module for SQL Server offers faster ways to manage your entire data-loving world. Learn about the best features in the new sqlserver module, and why every data professional will find it useful. Prior to SQL Server 2016, using SQL PowerShell was like installing training wheels on a Ducati. The SQL Tools team changed all that, by working with the community to prioritize improvements to SQL PowerShell. Additionally, they’ve started releasing regular updates. The SQL Server team has already delivered new cmdlets to help you manage SQL Agent Jobs, SQL Error Logs, and Add & Remove Logins. One of the best new features is the ability to now query multiple sources (SQL, csv files, etc.)
  

  Session Code
  

  THR3030
  

  Session Length
  

  20 mins
  

  Level
  Advanced (300)
  

I also mentioned that the tools can be kind of hard to find. With that in mind, I just wanted to write this quick blog post in the hopes of making it easier for customers to find these tools so they can try them out.

Here’s an excerpt from their GitHub page for mssql-scripter:

“We’re excited to introduce mssql-scripter, a multi-platform command line experience for scripting SQL Server databases.

mssql-scripter is the multiplatform command line equivalent of the widely used Generate Scripts Wizard experience in SSMS. You can use mssql-scripter on Linux, macOS, and Windows to generate data definition language (DDL) and data manipulation language (DML) T-SQL scripts for database objects in SQL Server running anywhere, Azure SQL Database, and Azure SQL Data Warehouse.”

Cheers!

SQL Server Dashboard Reports

I showed that you can download & deploy the SQL Server Dashboard Reports from the SQL Tiger team.

You can find a blog post explaining how those reports work here

You can find the MSSQL Tiger Team’s GitHub Repo here
An article I wrote explaining how to deploy those reports to your SSRS / Power BI Report Server with PowerShell here

As a quick reminder, the performance_dashboard_main report from that project is now included as part of SQL Management Studio in SSMS 17.2+ http://aka.ms/SSMS

SSRS Themes

I actually stole my SSRS theme from Guy in a Cube ( t ). Here is a modified version of the theme I was using. You can find out more about SSRS branding here.

Power BI Themes

You have plenty of options for Power BI Themes. You can find the Waveform theme here or the theme I was using here.

dbareports

You can find out more about the dbareports open-source project at dbareports.io

I hope that helps everyone and please leave a comment if I left out one of the resources I was using!

[contact-form]