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

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.

Next Fix for SQLPS module – Invoke-Sqlcmd – Object Output

The compelling thing about PowerShell is that you’re working with objects instead of just text. For SQL folks you’re already aware of the benefits of working with objects is better because you know it’s easier to add a week to a date that is stored as a DATE datatype instead of a date that is stored as a VARCHAR datatype.

The same benefits hold true when working with a datatable instead of an array. For one thing, when working with a DataTable you can very easily insert your result set into a table inside a SQL Server database using this very handy function that I’ve written about before. I’m not going to get into all of the benefits of DataTables today, because you can’t get them natively from the Invoke-Sqlcmd and that’s the problem!

Many years ago Chad Miller ( b | t ) improved his Invoke-Sqlcmd2 script to include outputting the results as either a “DataSet”, a “DataTable”, or a “DataRow”. For years I’ve hoped that the SQL Server team would notice this and add similar functionality to the Invoke-Sqlcmd cmdlet that comes with SQLPS. With the wonderful news that they’ve already fixed the first 3 issues we submitted (and y’all Up-Voted) on SQLPS it’s obvious that NOW is the time to ask for this.

So today I created a Connect Item asking the SQL Server team add this. Without this functionality, taking query results from SQL Server and trying to pass them to other objects/commands in PowerShell is downright archaic! Please Up-Vote this J

They Fixed All Three of Them!!!

I told you there was a chance!

Thank you so much to all the people who Up-Voted the 3 SQLPS Connect in my last post. It was great to see the community react to these items so quickly. Over the weekend Chrissy received word that the three things we pushed for and you Up-Voted had already been fixed and were available in the March update of SSMS 2016.

Luckily for me I had already kicked off the download of SQL Server 2016 RC2 earlier that morning. When I got home I spun up a fresh VM and installed everything as quick as I could. Here’s what it now looks like when you load the SQLPS module in PowerShell, sub-second load time!

This is just the tip of the ice berg.
There’s SO MUCH MORE TO DO.
SO MUCH MORE TO ASK FOR.
But if we don’t tell Microsoft what we need and what’s not working for us, it’s unlikely they’ll dedicate resources to fixing things. Thank you to everyone who made this one possible J

3 Little Things to Fix in the SQLPS PowerShell Module

I recently teamed up with PowerShell MVP Chrissy LeMaire to make a bigger difference in the SQL Server + PowerShell community. Last week we were talking about SQL + PoSh like always, and we started talking about what really annoys us about the SQLPS PowerShell module (besides the dearth of cmdlets) and we up with 3 main points that are pretty close to show-stoppers as far as us recommending to others that they should use the SQLPS PowerShell module.

(from Chrissy’s blog…)

“Reasons I don’t use SQLPS
  1. It takes 3-5 seconds to load
  2. It changes my directory
  3. It produces warnings upon load”

    So as we were coming up with this list of things that we feel really need to change we started digging into how hard it would be to change them. Right away (we’re talking line #7 of the code) Chrissy spotted something that shouldn’t be done. With a quick edit to what should be done Chrissy was able to drop the load time of the SQLPS module from 3-5 seconds down to sub-second.

    I was amazed. Dumfounded actually that for years such a simple fix has existed and none of us supposed ‘SQL+PoSh Experts’ ever caught it. I chimed in next with how much I hate the fact that when you load the SQLPS module it relocates you to the SQLSERVER Provider. If you just wanted to backup a database with the cmdlet don’t actually need to be within the provider. Plus when you’re within the SQLSERVER:\ Provider you can just forget about tab completion. Chrissy helped me put together a Connect Item to recommend that that functionality be removed. To make sure I wasn’t off-base in my dislike for this ‘feature’ I surfaced this Connect item with some people active in the PowerShell community. Some of these people are familiar with PowerShell capabilities of SQL Server and some are not; here are a couple of their reactions:

    So I basically found two allies on this issue instantly, on to the third thing. The SQLPS module uses unapproved PowerShell verbs and you get a message about this on load. Whether the verbs the SQL team used need to be added to the approved verbs list or not is not the point. Turns out, SQL Server and PowerShell are made by the same company so if the SQL team needs them that badly it would seem to me that they need to go make their case to the PowerShell team. This is going to be the 3rd major release with this warning message popping up every time you load the SQLPS module; this should have been resolved by now.

    Here are the 3 Connect items we ended up filing that night.

I realize with RC0 already being out that there is very little chance of these items getting fixed before RTM of SQL Server 2016, but there’s a chance. That’s why I’m asking you to Up-Vote these 3 items if PowerShell support in SQL Server is important to you. It’s slim, I know; but if you think about it, they probably have everyone they could possibly need assembled already. If the necessary people aren’t bogged down with bug fixes for RC0 then maybe they could start working on this. Or even if they don’t have the time to fix it right now, they might be able to at least start scoping out the fixes and see if they think it’s really as easy as we think they are.

Again, there are lots more things we dislike about the PowerShell experience for SQL Server and I’m sure you have plenty of things to add too! So maybe, just maybe, if we can get some visibility and <fingers crossed> some traction </fingers> on these 3 Connect Items, then we can get to work asking for more stuff.

Please join me in up-voting or just leave what you dislike most about the PowerShell experience for SQL Server in the comments below. J