SQLvariations: SQL Server, a little PowerShell, maybe some Tableau Rotating Header Image

Next for the PoSh VC: PowerShell, SQL and Power BI

That’s right, PowerShell & Power BI.

How does this work you ask?

Well back in July, Rob Sewell ( b | t ) gave us a preview of this, you can catch a few minutes here: https://youtu.be/rc6lwiTE9GI

Here’s Rob’s abstract to give you more info on the session. We hope you’ll join us next Thursday! J

PowerShell, SQL and Power BI – Reducing your Context Switching

“Could you just tell me……….?” This may cost 10 seconds of time or it may take several hours but it will definitely stop the current task and impact its completion.

A SQL DBA will be required to provide a myriad of information in many different ways to many different types of people, answering questions from technical teams, technology teams, other parts of the business as well as directors and external parties.

In this session you will learn the why, what and how of automating the gathering, storing and displaying of information enabling self-service and reducing the interruptive calls on your time whilst ensuring that the data is correct and trustworthy. I will also show you how you can use this to enable consistency across your estate. This session will be of benefit to the “Accidental DBAs” as well as DBAs looking after large estates

The majority of the session will concentrate on the way I use PowerShell to gather the information and store it. I will also show you how to enable self-service with natural language query using Power BI.

You will leave the session with all of the tools you need to return to work and convince your boss this is a worthwhile use of your time and to implement this solution to provide a modern way of providing accurate information about your estate.

Details:
Thu, Aug 18, 2016 12-Noon – 1PM EDT (GMT -4)
Registration URL: https://attendee.gotowebinar.com/register/4422529712300802818

Quick Post: Scripts from PowerShell VC Meeting

I knew I was forgetting something, I just couldn’t remember what, now I know! J

Chrissy LeMaire ( b | t ), Rob Sewell ( b | t ) And I did a session for the PowerShell VC of PASS last week and in the session I demoed a few scripts on how to use the new cmdlets in the July 2016 update of SSMS. Someone just asked me when I was going to post the scripts from that session, so here they are.

Please give them a try and tell us if you have any ideas on how to make them even better!

Thank You for Helping Spread the News!

I just wanted to take a minute to thank everyone who helped me get the news out about the new PowerShell cmdlets last week. If you haven’t heard the news, read the release notes, download
this and run this:

Get-Command -Module SqlServer | Out-GridView

I realize that some people’s twitter streams reach a much broader audience than mine, but still it was so great to see so many people excited and helping share the news.

There’s more to come. Massive THANK YOU to everyone who helped make this news possible in the first place!

Seriously, look at all these votes. We couldn’t have this without so many people chiming in.

Source: http://powershell.sqlpass.org/Reporting.aspx

Did I mention Thank You?

Webinar on 25 New PowerShell cmdlets for SQL Server and more!

Next week Chrissy LeMaire ( b | t ), Laerte Junior ( b | t ), Rob Sewell ( b | t ) and I will be hosting a webinar covering the new PowerShell cmdlets that have just arrived in the end-of-June update of SSMS 2016.

You can register for the webinar here.

Until then, here is a quick summary of what’s available now if you want to get started playing around with things yourself.

CMDLET Description
Get-SqlAgentJobHistory Returns the JobHistory present in the target instance of SQL Agent.
5 More SQL Agent cmdlets SQL JobSchedules and SQL Agent Job Steps. Read more in Chrissy’s post
Get-SqlErrorLog Retrieves the SQL Server Logs.
Set-SqlErrorLog Sets or resets the maximum number of error log files before they are recycled.
Plus 17 more Encryption cmdlets For more on that check out the post over on the Data Platform Insider blog

Get-SqlErrorLog

I want to tell you a great story about Get-SqlErrorLog. This new cmdlet is possible thanks to some capabilities in the old SQLPSX module of PowerShell, as well as a contribution by Nic Cain ( b | t ).  Huge thanks to Nic for posting a version of the Get-SqlErrorLog he was already using, which helped give the SQL Tools team some ideas of what people may want from this cmdlet.

A couple weeks ago I ran into a memory issue with the Clustered Columnstore Indexes in SQL Server 2016. (The problem I’m talking about is already fixed and will be out in CU1 of SQL Server 2016.) To make a long story short the SQL team needed the exact sequence of events I went through to receive this error message.  The great thing about SQL Server 2016 is that this was easier than ever to do.  Since I already had turned on Query Store, the SQL team would easily be able to see which queries I had run which had generated an error.  If you’d like to do that yourself, just run this query.

SELECT qt.query_sql_text, rs.execution_type_desc
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
WHERE execution_type !=0

So I’ve got the queries, I’ve got the backups, now I just need to send them the sequence of events.  But wait, I can do even better than that!  Since I had recently been discussing different options for Get-SqlErrorLog I remembered that I could use that to help the SQL team even more.  I fired up PowerShell, pointed Nic’s Get-SqlErrorLog at my instance, and then piped the output to Out-GridView.

Get-SqlErrorLog-sqlserver localhost\sql2016|
Out-GridView

Once the GridView popped up, I typed “memory” into the box at the top, that instantly filtered down the entire results of my SQL Error Log down to only the messages which contained the word “memory” in them.  I then clicked in the grid of results, did Ctrl+A then Ctrl+C.  Next I pasted all 970 of those error messages into an Excel spreadsheet and included it in what I sent off to the SQL team with my backup file.

I don’t know how much that helped the SQL team track down the problem I was running into, but I do know that it took me less than 30 seconds to gather that information for them.

In the future, hopefully we will have a Write-DataTable cmdlet to allow us to save this kind of data straight into a SQL Server table.  Until then, this is still a great step up over what we’ve had.

Not impressed?

Don’t forget that by throwing this in a quick foreach loop around your Registered Servers or Central Management Server, you can grab the error logs from all your SQL Servers, and then filter the results in just seconds.

Join the Conversation

We’re all really excited to finally get our hands on these cmdlets, but we’re also excited that we’ll be getting even more cmdlets when SSMS 2016 updates again.  That next round of cmdlets is still being defined right now and we again invite you Up-Vote to join the conversations we’re having with the SQL Tools team on the Trello board we’ve setup.  If you want to converse more about these topics, we’ve even setup a Slack Channel to do just that. Everyone in the SQL Community is welcome to join the Slack channel, that’s why we named it SQLCommunity.Slack.com! J

Read More

My fellow PowerShell VC leaders have also posted about these new capabilities and you can read their thoughts here:

  • To read the full announcement from Microsoft check out this post on the Data Platform Insider blog.
  • To read more about the new SQL Agent cmdlets read this post from Chrissy LeMaire.
  • To read more about the new Always Encrypted cmdlets read this post from Rob Sewell
  • To read more about the new -OutputAs parameter for Invoke-SqlCmd read this post from Laerte Junior.


Automating Your SQL Server Best Practices Word Document

Just a quick update on PowerShell things for today but I hope to have some cool SQL Server 2016 features to share soon.

Laerte Junior will be presenting a session for the PowerShell VC of PASS tomorrow at noon EDT (GMT-4) titled “Automating Your SQL Server Best Practices Word Document”.  Laerte has done articles on this topic for Simple-Talk and tomorrow is your chance to see him do this live and ask questions you may have.

Automating Your SQL Server Best Practices Word Document
You are a consultant that needs to create a word document for your client with the best practices on SQL Server. You cannot install any kind of third party software so you will ended up doing a lot of queries and copying and paste to a word document. PowerShell MVP and SQL Server Pro Laerte Junior bets that you will spent a lot of time doing this. What if he told you could do all this work, including color-coded alerts in the document where there are problems or best practices aren’t being followed, with 2 clicks of the mouse?  In this session we will see ..well.. come with us and check this out!

Registration URL: https://attendee.gotowebinar.com/register/1538783389677221380


There are some great PowerShell suggestions coming out of the Trello board!  Here is the latest batch if you’d help make the SQL+PoSh world a little better by Up-Voting.

I know the anticipation is killing you (it’s killing me at least), but we’ve been told to expect to see any new SQLPS cmdlets to show up at the end of June when the update for SSMS is released.  Hopefully they’ll let us know about a few more of the cmdlets they have in the pipeline sometime soon.

Link Problem / Suggestion
http://bit.ly/SQLDSC Merge of xSQLServer & xSQLPS into 1 module and gracefully replace with SQLServerDSC
http://bit.ly/AsPS1 Script Action to New Window AS PowerShell
http://bit.ly/WriteTable SQLPS lacks a simple way to write rows from a .Net DataTable into a SQL Server table
http://bit.ly/OutDt Useful for converting any Powershell object into a DataTable which then can be bulk imported into a SQL Server table

Connect is Working! Get in While the Getting is Good!!!

TL;DR: Go Vote / File on Connect NOW!

For years I’ve avoided Connect, it’s true.

imageIn an ironic twist fit for a recursive-CTE I think I quit using Connect over responses to items like this one “Improvements to Keyboard Shortcuts in SSMS” when it was “Closed as Won’t Fix”.  Well I didn’t quit right that second, I had to let my feelings be know first, so I left this comment
”Posted by SQLvariant on 6/1/2011 at 4:14 AM

Please reopen this bug ASAP!
At the very least you need to fix Highlight+Alt+F1. I hate having to put brackets around the name just to get its definition.
It probably sounds like nothing to the team because you just do [sales.salesorder] to get it to work but when you open up someone else’s stored proc and the code says [sales].[salesorder] and you have to strip out the inner “]~[“ and leave just the “.” …Do that 15 times and you get really ticked! ”

Well, turns out.  They fixed that.  But unfortunately, they didn’t share the good news so by the end of 2011 I had enough and I stayed away for years.  Fast-forward 4 years later Chrissy LeMaire & I went on our campaign to Fix Just 3 Things and I was staring at my old nemesis again.  I had swore to never touch that thing again, and I hate going back on a promise like that; but this was for a friend.  So I did it.  I Up-Voted a Connect item for the first time in nearly over 4 years.  Next I did the un-thinkable.  I actually filed a new Connect Item.

Next thing you know, this rapid-release-cycle seems to be working out because we see this tweet:

So we file some more Connect items.  A bunch more connect items.  Heck I even filed one Connect this past Sunday afternoon and they replied to it Monday afternoon telling me they had already spotted the problematic piece of code and were pondering a fix! That’s 30 hours 18 minutes on that reply.

imageThat’s not the only thing that’s been fixed.  Remember that first item that was “Closed as Won’t Fix”.  Well check out this comment from last week.

imageAnd now another comment on that same item yesterday:

So here I am, once again, telling you there’s a chance.

Now get out there and Up-Vote a great idea like this one:

image

 

 

Or these:

Link Problem / Suggestion
http://bit.ly/AsPS1 Script Action to New Window AS PowerShell
http://bit.ly/WriteTable SQLPS lacks a simple way to write rows from a .Net DataTable into a SQL Server table
http://bit.ly/OutDt Useful for converting any Powershell object into a DataTable which then can be bulk imported into a SQL Server table
http://bit.ly/GetLogin Basic Login management functionality is missing from SQLPS
http://bit.ly/GetSQLAgent Essential Job support is missing from the SQLPS module; Get-SQLAgentJob & Get-SQLAgentJobHistory etc…
http://bit.ly/SSMSPS Add PowerShell Editor into SSMS
http://bit.ly/GetSQLTable SQLPS Lacks cmdlets to “Get” Common SQL Server Object, Like Get-SqlTable
http://bit.ly/1SwqTtA SQL Server SQLPS PowerShell module fails connection to SQL 2012 instance
http://bit.ly/23KqWXZ SQL 2014 SMO can’t query SQL 2012 instances: “SQL Server WMI provider is not available”
http://bit.ly/1QnmscT Need Documentation for the limitations of a PowerShell step in a SQL Agent Job

Or file your own Connect item and tell me about it so I can share. Or even better, join us on Trello to collaborate about ways to improve SSMS & SQLPS

image

image

Want to get something fixed/improved/added in SQL Server Management Studio? Join us on Trello!

Do you have something you want to get fixed, improved, or added in SQL Server Management Studio?  If so, join us on Trello!

image

A couple weeks ago I mentioned that we are using Trello to help the community collaborate about what we want next in SQLPS before we submit Connect items to Microsoft.

That effort is going very well.  It’s going so well in fact that when the topic of getting some new improvements into SSMS was brought up, the SQL Tools team suggested that a Trello board to collaborate and prioritize what people want improved in SSMS would be very helpful to them.  Ultimately Microsoft needs Connect items filed but using Trello helps folks to debate and combine ideas.

We fired up the new Trello board for SSMS Thursday evening and there’s already a lot of ideas on there.  So far, “Warn for DELETE or UPDATE without WHERE” is out leading vote-getter.

image

I can’t wait to see all the great ideas that people come up with!

Performance Tweaking Your SQL Server via PowerShell and Other News

Wow, there’s so much PowerShell news and goings-on that I barely have time to update the blog.  Here is a very terse update of some of what’s going on.

Thank you to all of you who joined Amit Banerjee’s presentation yesterday on “You, SQL Server and PowerShell”.  You helped us set the all-time attendance record for a PowerShell VC session and that’s the second time we’ve done that this year!  We’ve already uploaded the video to YouTube and you can re-watch it here https://youtu.be/3tC3FCvAnNI

I feel the the session should have been called something more like: “Performance Tweaking Your SQL Server via PowerShell” but that’s just me. In any event, Amit has made the scripts available on GitHub.

There’s been a lot of great news about the future of PowerShell+SQL Server lately.  Chrissy and I recently interviewed Ken Van Hyning, Engineering Manager for SQL Server Client Tools at Microsoft.  Read more here: http://sqlps.io/future

We’ve got more great sessions lined up for you already:

Advanced PowerShell Development by Bryan Cafferky
Registration Link

Automating Your SQL Server Best Practices Word Document by Laerte Junior, PowerShell MVP
Registration Link

And please don’t forget to help us Up-Vote Connect items so that everyone has a better PowerShell+SQL Server experience!  Smile

Link Problem / Suggestion
http://bit.ly/GetLogin Basic Login management functionality is missing from SQLPS
http://bit.ly/GetSQLAgent Essential Job support is missing from the SQLPS module; Get-SQLAgentJob & Get-SQLAgentJobHistory etc…
http://bit.ly/SSMSPS Add PowerShell Editor into SSMS
http://bit.ly/GetSQLTable SQLPS Lacks cmdlets to “Get” Common SQL Server Object, Like Get-SqlTable
http://bit.ly/1SwqTtA SQL Server SQLPS PowerShell module fails connection to SQL 2012 instance
http://bit.ly/23KqWXZ SQL 2014 SMO can’t query SQL 2012 instances: “SQL Server WMI provider is not available”
http://bit.ly/1QnmscT Need Documentation for the limitations of a PowerShell step in a SQL Agent Job

And finally, if you want to collaborate on Connect items and prioritize when they are submitted, please join us on Trello.  http://sqlps.io/vote

image