SQLvariations: SQL Server, a little PowerShell, maybe some Tableau Rotating Header 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

Sander Stad is Up-Next for the PowerShell Virtual Chapter of PASS

There’s a lot of news about what all is happening at the PowerShell Virtual Chapter of PASS to share with you but I’m going to save a lot of that for another post. Right now I just want to share 4 things with you really quick.

  • Chrissy LeMaire Joining as Co-Leader
  • Sander Stad is presenting next week
  • Check out the YouTube Channel

PowerShell MVP Chrissy LeMaire ( b | t ) has joined the PowerShell VC of PASS as my Co-Leader. She is doing amazing things with our website right now and I will share more about that soon; in the meantime please help me in welcoming her to the PoSh VC of PASS!


[fairly] New Speaker Sander Stad ( b | t ) will be presenting for the PoSh VC of PASS next Wednesday March 16th at 12-Noon Eastern Daylight Time (GMT-4). So don’t forget to Spring forward this weekend, or adjust accordingly if they don’t do that where you live.

Documenting SQL Server with PowerShell

Documentation is mostly overlooked and only comes up when a problem arises. What if you’d have a tool or method to generate documentation for all your database servers? In this session, Sander Stad will show you show how easy it is to use PowerShell to retrieve information from your servers. He’ll detail what can be used to document your servers, how to retrieve the information and what should be documented. In the end you no longer have an excuse not to document your servers.

Register here for the webcast.


Finally, I wanted to point out that Jeff Wouters ( b | t ) did a great session “PowerShell Unbound – Tips & Tricks” for the PoSh VC last month and the recording for it is already up on our YouTube channel.

OK, That’s it for now, lots more to come soon! J

And We’re Back

Wow, sorry for the long delay between blog posts there, I’ve been a tick busy lately. What have you been up to that has kept you away, you say? Oh not much, just: Got married (yes, still surprises me too some days), my wife gave us a baby boy, and my oldest child is off to College in a couple months. Outside of that not much really, just slackin’ really.

So yeah, I can’t believe she went through with it either, but a wonderful woman named Karen agreed to marry me in the fall of 2014. Here’s us at our wedding on 12-13-14 (That’s MM-DD-YY for your international folk). Karen has many talents but among them is that she has a Masters Degree in Child Psychology. And yes, you guessed it, I never win an argument and I now frequently find myself doing tasks I didn’t know I wanted to do. The Force is strong with this one!

In other news: We had a BOY!!!! That’s crazy in and of itself. I thought I was done having children honestly but SURPRISE. In fact, the week before Karen found out she was pregnant she told me: “I don’t need any kids, I have you”. His name is David and he still keeps us (read: Karen) up at night.

Here’s a picture of both my children at Dorothy’s 18th birthday celebration dinner last week. You may have met Dorothy at a SQL Saturday, she’s about to graduate high school in a few weeks and has already been accepted to a couple of colleges. Makes you feel old, huh? ME TOO!

More to come about SQL + PowerShell and all kinds of stuff very soon; but I wanted to get this update out to you right now.