A Fine Slice Of SQL Server
Dave Mason builds a Powershell script to parse Extended Events information:
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.
Comments closedThe 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.
Comments closedJakub Szymaszek shows how to configure Always Encrypted support from Powershell:
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.
Comments closedJana Sattainathan has a cmdlet to retrieve instance information with Powershell:
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.
Comments closedChrissy LeMaire shows how to load SMO with full names when you don’t know which version is installed:
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.
Comments closedTo download the latest PowerShell Open Source just go to the link below:
https://github.com/PowerShell/PowerShell
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:
SQL Server next version Community Technology Preview 1.1 now available
Max has additional links and resources in that post as well.
Comments closedFirst, 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.
Comments closedRestore-SqlBackupFromDirectory is super useful in a pinch, too, but it’s not quite fleshed out to our standards, so it doesn’t have a corresponding webpage. We expect this will be renamed by the next release.
Again, I usually have all the docs for all of our newly released commands, but I was trynna make it for #tsql2sday.
Check out her post, and then check out dbatools.
Comments closedKevin Hill gets into Powershell:
What follows is a step by step of how I expanded on the most basic command to get more and more out of it. I spent a lot of time on MSDN looking up cmdlets, and on Stack Overflow twice with basic questions. Even went to my “DBA” Twitter feed early in the process using #sqlhelp since a lot of my colleagues are veteran PoSH users.
Warning…its very possible I’ve gained a syntax error here and there when copying and formatting this into a post…run at your own peril 😉
One bit I’d recommend is using Out-GridView for occasional display and potentially getting rid of the Excel requirement.
1 CommentAaron Nelson is advocating improvements to Powershell cmdlets around Analysis Services:
Frequently when developing updates to an SSAS cube I want to deploy my schema and process the dimension. Sometimes several of dimensions process successfully and then fails on one. At this point I go and correct the error, deploy the new schema, and then I only want to process all of my dimensions except the dimensions which did process successfully the first time. Sometimes this is really easy, but if you have a large number of dimensions this can become cumbersome since the only way to know which dimensions had been processed successfully or to right-click each dimension one at a time and find out, or to have memorized which dimensions had processed successfully on the earlier attempt. There can be a better way, and of course, PowerShell is one of those options. J The only problem is that as things currently stand, PowerShell is not as easy as it could be; the Invoke-ProcessDimension cmdlet doesn’t accept [direct] pipeline input. What is one to do when PowerShell isn’t as easy as it could be? File a Connect item of course!
Check out the Trello board. It’s been instrumental in helping Microsoft developers get the leverage they need to dedicate time to improving particular aspects of the product.
Comments closed