Peter showed off three mechanisms for streaming data to a real time dashboard:
- The Power BI Rest API
- Azure Stream Analytics
- Streaming Datasets
We’ve done a fair bit at Adatis with the first two and whilst I was aware of the August 2016 feature, Streaming Datasets I’d never got round to looking at them in depth. Now, having seen them in action I wish I had – they are much quicker to set up than the other two options and require little to no development effort to get going – pretty good for demo scenarios or when you want to get something streaming pretty quickly at low cost.
Click through for more details and a sample script.
2 very solid reasons (there are others) that every DBA should be learning and using PowerShell:
1 – Its very useful for admin at the O/S level.
At my current client I am team lead of System and SQL Admins, along with doing any of the work that comes our way. This means we need to be able to manage the modest server farm we have. Its big enough that we can’t log onto every server every day, but small enough nobody wants to buy a proper monitoring toolset. So…PS to the rescue!
Read on for the other reason. I think the relatively poor Powershell tooling with SQL Server (with respect to other groups like Exchange) limited general acceptance, but they’ve made some big improvements over the past year and there are some sharp minds in the community working to make Powershell even more important for DBAs.
I decided to change this around so that it no longer uses string formatting, but instead a
SqlConnectionStringBuilder. I had a couple reasons for this:
It will eliminate redundant code. There are several common elements in each of the
ConnectionStringsabove. If more complex logic is needed, there are potentially more copies of this ConnectionString kicking around.
It’s prone to copy/paste and other editing errors. If there’s a change that affects both versions of the ConnectionString and the developer just copies the line from one branch of the if statement to the other, code will be lost or invalid values will be substituted because of positioning.
This is something I’d like to see make it to the main cmdlet.
Powershell takes center stage for this post. Previously, I showed how to handle a SQL Server Extended Event in C# by accessing the event_stream target. We can do the same thing in PowerShell. The code translates mostly line-for-line from C#. Check out the last post if you want the full back story. Otherwise, continue on for the script and some Posh-specific notes.
Read on for the code.
The sample script below shows how this is done. The sequence command is used to delete multiple partitions in a single transaction. This is similar to the batch command in XMLA. In this example we’re only performing delete operations, but many different operations can be performed in sequence (And some in parallel).
Click through for a description of the process as well as a script to do the job.
Note: In a production environment, you should always run tools (such as PowerShell or SSMS) provisioning and using Always Encrypted keys on a machine that is different than the machine hosting your database. The primary purpose of Always Encrypted is to protect your data, in case the environment hosting your database gets compromised. If your keys are revealed to the machine hosting the database, an attacker can get them and the benefit of Always Encrypted will be defeated.
That’s a good warning.
There is not much to say except that this supports pipeline input. Take a close look. PowerShell folks don’t read text, they just focus on the code…So, here it is.
Click through for this code.
In a recent version of PowerShell, Publish-Module, which publishes modules to the Gallery began requiring fully qualified Assembly names such as “Microsoft.SqlServer.Smo, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91”.
Previously, it was sufficient just to use short names such as Microsoft.SqlServer.Smo. This had similar behavior to LoadWithPartialName.
I get that LoadWithPartialName is sketchy, but the solution that Chrissy has to do seems overly complicated to me. Nonetheless, those are the rules of the game now, I suppose.
To download the latest PowerShell Open Source just go to the link below:
Just remember to remove the previous version, and any existing folders as this will be resolved later.
To download the latest SQL Server vNext just check the following Microsoft blog post as the new CTP 1.1 includes version both Windows and Linux:
Max has additional links and resources in that post as well.
First, an annoyance. To be able to deploy Data Science virtual machines in Azure programmatically you first have to login to the portal and click some buttons.
In the Portal click new and then marketplace and then search for data science. Choose the Windows Data Science Machine and under the blue Create button you will see a link which says “Want to deploy programmatically? Get started” Clicking this will lead to the following blade.
Click through for a screenshot-laden explanation which leaves you with a working VM in Azure.