There are two new options for automating your SQL Notebooks with your SQL Servers. Earlier this month, the Insiders build of Azure Data Studio received the ability to add SQL Notebooks in SQL Agent. This past Friday (September 20th, 2019) a new version of the SqlServer PowerShell module was posted to the Gallery, with a new Invoke-SqlNotebook cmdlet.
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? ??
The post Automate Your SQL Notebooks with Two New Options first appeared on SQLvariations: SQL Server, a little PowerShell, maybe some Power BI.