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

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

Export-SqlDatabase | Import-SqlDatabase; Please Comment on Connect!

Microsoft is listening.

We have a chance to make awesome things.

Microsoft needs your feedback.

Earlier this week I submitted a Connect Item proposing that Microsoft basically replace /augment /succeed the SQLPackage.exe functionality with at least three PowerShell cmdlets but possibly more. It should be fairly easy since SQLPackage.exe is just a wrapper around DacFx. The problem is that only 3 people have commented so far. Trust me, you don’t want what I jotted down to become what the SQL Tools team produces; I skipped over a few areas because I have other things going on. Heck I even misspelled SSDT, now how hard is that for get right?!?

Please Click the Link and Comment!

SQLPackage.exe – Needs to be made into at least 3 cmdlets

SQLPackage.exe – Needs to be made into at least 3 cmdlets (and possibly more; we have added ideas for additional cmdlets below). The first 3 cmdlets that need to be made into are:

  • Export-SqlDatabase
  • Import-SqlDatabase
  • Compare-SqlDatabase

Export-SqlDatabase

At a minimum Export-SqlDatabase should take parameters such as -ServerInstance -Database -TargetFile -IncludeData (ExtractAllTableData).

It would also be nice if these cmdlets would work ‘smartly’ from within the provider. For instance, if you were within the provider inside a database (PS SQLSERVER:\sql\localhost\default\databases\AdventureWorks> ) and wanted to run Export-SqlDatabase you should only have to supply a filename for your export; the cmdlet should infer that you wanted to export that db on that instance and supply those parameters for you, so that you don’t have to.

(Below Chrissy proposes another cmdlet called “Export-SqlTable” and not to steal from her thunder, but… alternatively there could be an optional parameter call -Table that accepted an array of tables to be exported. However, the problem with that is obvious, if you do that for tables you will need to add a parameter for stored procedures, views, etc…)

Import-SqlDatabase

Import-SqlDatabase should obviously import a database from a .DACPAC file, but it should also be able to import the structure of a database from an existing SQL Server database, and maybe even a SSDS Project. If Importing directly from an existing SQL Server database you should probably have to pipe output of Export-SqlDatabase to Import-SqlDatabase. This cmdlet should include parameters such as -ServerInstance -Database -SourceFile -IncludeData.

Compare-SqlDatabase

Comparing databases is extremely important and should be supported from a cmdlet named something like Compare-SqlDatabase. When using this cmdlet there will need to be a ton of optional parameters. You need to be able to compare your database to another database, possibly on another SQL Server (obviously) but you will also need to be able to compare a database to a .DACPAC file and vice-versa. This cmdlet should be able to generate a drift report, which should output to the screen by default, but have an optional parameter -DriftFile that would allow you to save the results to disk. It should be possible to push the changes to the target database if a parameter is supplied (and the user doing so has the requisite permissions). If the user does deploy changes, there should be a report of what was changed.

Here are some additional Ideas that PowerShell MVP Chrissy LeMaire ( b | t ) has contributed to this Connect item from her experience in the MySQL world:

Make it super similar to mysqldump. Especially now with the SQL Server on Linux thing, it’s gonna be a great idea to have a tool similar to mysqldump. As a MySQL user, I’ve wanted this functionality from SQL Server for years.

For cmdlet names, the SQLPS module uses Verb-SqlNoun, so the cmdlet names should be: Export-SqlDatabase, Export-SqlTable, Import-SqlDatabase, Import-SqlTable

1. Create an alias for Export-SqlDatabase and call it mssqldump

Set-Alias -Name mssqldump -Value Export-SqlDatabase

2. Get syntax close to mysqldump, so if no server specified, default to local. If no username specified, default to local (like sqlcmd)

mysqldump test > dump.sql

mysqldump –databases db1 db2 db3 > dump.sql

3. The data should be output by default, like mysqldump. If people only want the schema, they could do Export-SqlDatabaseSchema OR add a -SchemaOnly switch to Export-SqlDatabase / Table. This is the behavior of mysqldump. You’ll start to like it; I guarantee 😉

Again, this is just a starting point and I’m sure the community YOU can contribute a lot more ideas than the few that I have laid out here.