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

October 2016 SQL Mastery Sessions

Everyone is really excited about PASS Summit being right around the corner and as usually happens, the folks at the Portland Oregon user group are putting on a SQL Saturday the weekend before.

I love getting to explore the city of Portland and eat food that came out of a converted camper or back yard shed, I love catching up with so many friends that make it out to the city that weekend, but this year I have something else to love.

My friend Mike Lawell ( b | t ) and I have teamed up to share some great SQL PowerShell content designed around the new features that have been coming out in the monthly releases of SSMS 2016. We’ll be doing this on Friday the 21st and there are two other great choices to pick from as well if you’re in town that day.

I hope to see you in Portland and if you’re a fan of all the SQL PowerShell goodness that has happened this year make sure to find me by Saturday morning for some cool SQL PowerShell stickers I have with me!

October 2016 SQL Mastery Sessions

An Oregon SQL Special Event
A Full-day of SQL Training offered the day before Oregon SQLSaturday 2016

Friday, October 21, 2016, 8:00 AM until 4:30 PM

University of Phoenix; 13221 SW 68th Pkwy, Tigard, OR 97223
Registration and Location Details

SQL PowerShell – Episode IV: A New Hope
Aaron Nelson | Mike Lawell

T-SQL For Performance and Accuracy
Vern Rabe

Mastering Power BI Solutions
Paul Turley, Rachel Dyer, Brian Grant

SQL PowerShell – Episode IV: A New Hope
Aaron Nelson

25 New SQL PowerShell cmdlets have already been delivered and more are on their way every month.  2016 has unleashed a new era in what can be done with data by leveraging PowerShell. PowerShell is for every Data Professional and this session is built from my own experience as a DBA, Database Developer, and now B/I Developer.

The day will start with a primer on the basics of PowerShell and it’s ecosystem. You will learn things like how to work with data, import it into SQL Server extremely quickly, back up your database schema, compare that schema with another database. We will go through techniques to scale your scripts across multiple instances as well as how to store the results where you want. Even if you’re not a not a B/I Developer you may have needed to deploy an SSRS report or two, you will learn easy ways to leverage PowerShell to deploy lots of reports to lots of machines.

While we’re at it, we’ll go through new features the community has asked Microsoft build for SQL PowerShell, how you can add your voice to that conversation, and how to build something to get around an issue until Microsoft gets around to building their own solution.

Finally, we will go through error handling and other things to make your scripts more durable.

All attendees will receive all of the code that is demoed, as well as handouts about the language and how to get things done..

Register for this session

Aaron Nelson ( blog | twitter ) is a Consultant with SolidQ with over 15 years of experience in Architecture, Business Intelligence, Development, and Performance Tuning of SQL Server.  He was recently awarded a 6th Microsoft MVP for SQL Server (Data Platform).  Aaron holds certifications for MCITP: Business Intelligence Developer, Database Administrator, & Database Developer.

Aaron leads the PowerShell Virtual Chapters of PASS, and volunteers for the local PASS Chapter AtlantaMDF, and helps organize SQL Saturday events in Atlanta.

This year, the PowerShell VC of PASS set out to “Make SQL PowerShell Great Finally” and it is well on its way.  Along with PowerShell MVP Chrissy LeMaire, Aaron helps organize what the community is asking for in the new SqlServer PowerShell module on the community Trello board (sqlps.io/vote).  Thanks to these efforts and the countless contributions from the SQL community around the world, we have already received 25 new PowerShell cmdlets and have been told to expect even more new cmdlets every month!

Mike Lawell is a 20 year veteran of SQL Server, working with all versions since 6.5. He specializes in performance tuning, infrastructure, and high availability.  He has been a consultant for SolidQ since 2015 specializing in health checks, performance optimization, & security and compliance.

He is also a certified Scuba instructor with a long bucket list of dive destinations. He resides in Atlanta, GA and you can catch him at many of the SQLSaturdays in the SE region of the United States.

T-SQL For Performance And Accuracy
Vern Rabe

It seems like there’s always a query or a stored procedure that takes way too long to execute. Don’t be quick to blame it on a missing index, or out of date statistics, or insufficient memory. Often a slow performing query can be drastically improved with a rewrite. This preconference will teach you how to write T-SQL with two goals in mind: Getting the results you expect (accuracy), and quickly (performance).

In this preconference we will start with some SQL Server internals, where you will learn how the optimizer determines the execution plan, which join algorithms should be used in which situations, and some of the right and wrong choices that the optimizer can make. We’ll discuss how statistics are key to the optimizer’s choices, how the plan cache is used (for good or bad), how to minimize blocking, and how the ubiquitous transaction log is a performance factor is just about everything.

After the internals you will learn how to use various tools to identify poorly performing queries and the reasons for their poor performance. We’ll dig deep into the execution plan, identifying some details of how the optimizer chose to execute a query. We’ll learn about tell-tale warnings, estimated vs. actual plans, and parallel execution threads. We’ll also learn where the graphical execution plan can mislead you.

Next we’ll get into specific of T-SQL queries, where we’ll discuss many of the query writing mistakes that can lead the optimizer down a bad path, and most importantly, how to rewrite those queries to give the optimizer the appropriate information to make good decisions. All while making sure the results are exactly what they should be.

Some of the topics covered:

  • Graphical Execution Plans
  • Statistics
  • Cardinality estimator
  • Plan cache
  • Parallelism
  • Implicit conversion
  • UDFs

All attendees will receive a USB flash drive with a copy of the PowerPoint presentation and all of the demonstration SQL scripts. Although not necessary for the class, if you bring a laptop configured with a USB port and SQL Server 2016, you can execute the scripts along with the presentation.

Register for this session

Vern Rabe (LinkedIn | Twitter) an independent SQL Server consultant and contract trainer in Portland, OR. He has attained MCSE, MCITP (both Administration and Development), and MCT certifications, among others. Vern has been working with databases for 23 years, and has worked with SQL Server since version 4.21a. He provides broad technical SQL Server skills gained from the mixture of academic and practical experiences acquired from his classroom instructing and varied consulting contracts.

Mastering Power BI Solutions
Paul Turley, Rachel Dyer, Brian Grant

In this second annual installment of the Power BI Hands-on Workshop, you will master the “power” of Power BI.  Learn to use self-service and enterprise-scale Power BI capabilities; gain valuable skills to integrate, wrangle, shape and visualize data for analysis.  Beginning and intermediate level users will learn to address data and reporting challenges with advanced design techniques.  After several successful workshops last year, Paul has invited two respected experts in their respective disciplines to co-deliver this workshop and to go deeper with the maturing Power BI platform.

Bring your laptop with Power BI Desktop installed; 64-bit Windows & 8 GB of RAM (4 GB min) is recommended.  A Power BI subscription is recommended but not required.

Topics & Skills:

Data wrangling and transformations:

  • Source data from a variety of platforms & formats
  • Connect to live and on-premises data using DirectQuery & SSAS direct connect
  • Manage & schedule data refresh
  • Query design best practices & survival skills
  • Advanced query scripting techniques using “M”/Power Query formulas

Modelling and shaping data:

  • Data modelling basics & best practices
  • Modelling approaches with flat, normalized, star & snowflake schemas
  • DAX calculation basics & fundamentals
  • Need-to-know and good-to-know DAX functions & skills
  • Understanding challenging DAX concepts; row & filter context, relationship cardinality & filter flow
  • Advanced DAX calculations & business applications

Visualizing and analyzing business data:

  • Standard report design principles & patterns
  • Self-service end-user coaching
  • Proper applications of filters & slicers
  • Applying report visuals with cross filtering to answer business questions
  • Report & dashboard design showcase

Advanced concepts and solutions:

  • Working with on-prem, hybrid & published solutions
  • Managing published models, reports & dashboards in the Power BI service
  • Working with workspaces, content packs & security roles
  • Using gateways to manage live & on-premises data sources
  • Integrating Power BI with SSRS, Excel & SSAS
  • Using R for specialized reporting, statistical & predictive analytics
  • Using custom visuals for advanced & specialized reporting & analytics

Embedding reports & dashboards into sites & web solutions

Register for this session

Paul Turley  (Blog | LinkedIn | Twitter) is an independent BI consultant, owner of Intelligent Business LLC, and a Microsoft Data Platform MVP. He consults, writes, speaks, teaches & blogs about business intelligence and reporting solutions. He works with companies around the world to visualize and deliver critical information to make informed business decisions. Paul is a Director of the Oregon SQL PASS chapter & user group, the lead author of Professional SQL Server 2016 Reporting Services and other titles from Wrox & Microsoft Press.

Rachel Dyer (LinkedIn) has been working with Data Warehouse related projects for almost 15 years, primarily in the role of consultant. In the last two years she has specialized in Power BI with particular attention to DAX and the modeling aspects of the platform. Her passion is for understanding the reasonably tricky parts, avoiding the really tricky parts, and helping others understand what’s cool about columns and rows.

Brian Grant (LinkedIn)  Is an Analytics Consultant with CSG Pro, Brian has several years of experience training people in various data tools such as Power Query, and Excel both via workshops and online screencasts. He’s happiest when solving complex DAX problems and breaking down difficult concepts for people in ways that are easy to understand.

Webinar: Hacking SQL Servers on Scale using PowerShell

Next up for the PowerShell Virtual Chapter of PASS Scott Sutherland ( b | t ) will be presenting: Hacking SQL Servers on Scale using PowerShell. This session will be live on Wednesday Oct 19, 12-Noon EDT (GMT -4). As always, we will get the recording posted to you PoSh VC YouTube channel as quickly as we can.

You can register for this webinar right here: http://bit.ly/2dIK7K0

I’m really curious to how all this works. I’m certainly no penetration-tester but since Scott is making the PowerUpSQL module available I’m hoping I can learn some things that I can use to help harden SQL Server environments when my clients need help with that.

About the presentation:

This presentation will provide an overview of common SQL Server discovery, privilege escalation, and data targeting techniques. It will also cover how SQL Servers can be leveraged to escalate privileges in Active Directory domains. Finally, we’ll show how PowerShell automation can be used to execute the SQL Server attacks on scale. This should be useful to penetration testers and system administrators trying to gain a better understanding of their SQL Server’s attack surface and how it can be exploited.

All slides and the PowerUpSQL module will be made available online.

Scott is currently responsible for the development, and execution of penetration testing at NetSPI. His role includes researching and developing tools, techniques, and methodologies used during network and application penetration tests. Scott has been providing IT security services to medium sized to Fortune 50 companies for over 10 years. His goal is to help them identify the risks that exist in their environment, and develop prioritized remediation plans that take into account their business constraints and requirements. As an active participant in the information security community, Scott also contributes technical security blog posts, whitepapers, and presentations on a regular basis through NetSPI.

Searching SQL Servers & Saving Results, with Write-SqlTableData

Just a quick blog today to show you something which is possible with a handful of lines of code. I am writing a more in-depth article for SQL Server Central on Write-SqlTableData, in the meantime hopefully this might inspire or help you with something you need to do.

The other day I was chatting with my friend Tom Roush ( B | T ) and he mentioned needing to scan every database on every SQL Server instance in his environment and look for a particular word in the stored procs. Tom and I had discussed basics of his environment once before so I knew he already had Registered Servers filled out with all the instances he was going to need to search.

Here’s the query Tom needed to run:

DB_NAME() AS ‘Database’,
so.Name AS ‘ProcName’,
FROM syscomments sc
JOIN sysobjects so
ON sc.id = so.id
WHERE sc.text LIKE ‘%baconbaconbacon%’

Setting up the table for the search results

Write-SqlTableData came out last month in SSMS 16.4, it’s V1 so it’s not perfect [yet] but boy is it ever magic when you’re in a hurry! In this case, in order to be able to save the results for Tom, we need to find what we’re looking for once and then create a table in SQL Server using those results. To do this we use our very much improved friend Invoke-Sqlcmd and we leverage the new -OutputAs DataTables parameter which came out earlier this year. That give us a .Net Data Table. Next we send the results of that query to Write-SqlTableData, which accepts .Net data tables like Buck Woody ( B | T ) accepts coffee, via pipeline! You will need to give it a name schema & table name to use, oh and which database you want this in. Since this table doesn’t exist yet, we use the -Force parameter and like magic Write-SqlTableData is going to create the table for us. We do all of this here, because when we loop through we don’t want to create a new table each time.

Invoke-Sqlcmd -ServerInstance localhost -Database AdventureWorks2014 -OutputAs DataTables -Query
DB_NAME() AS ‘Database’,
so.Name AS ‘ProcName’,
FROM syscomments sc
JOIN sysobjects so
ON sc.id = so.id
WHERE sc.text LIKE ‘%baconbaconbacon%'”|Write-SqlTableData -ServerInstance localhost -DatabaseName sandbox -SchemaName dbo -TableName SysCommentSearchResults -Force

I <3 Registered Servers

I’ve shown how to leverage Registered Servers in PowerShell a couple times before (Exhibit A | Exhibit B). I love Registered Servers, so much that I have a snippet setup so that I can quickly run any PowerShell (or even just SQL) script against every SQL Server instance in my environment. I guess I should blog about that sometime. Until then, here is what I use, it should work for you too if you have any Registered Servers.

foreach ($RegisteredSQLs IN dir -recurse SQLSERVER:\SQLRegistration\’Database Engine Server Group’\ | where {$_.Mode -ne ‘d’} )

The Final Product

To search all of the SQL Server instances in Tom’s environment all we need to do is marry up the two above pieces of code. I have highlighted in yellow the two pieces of code that needed to be changed to go from a single searching a single instance to searching multiple. It was also necessary to add a call to Get-SqlDatabase in order to get a list of databases on each instance, which is highlighted in blue.

Loops through Registered Server to get a list of instances,
then uses Get-SQLDatabase to find each database on those instances,
then uses Invoke-SQLCmd to search syscomments in each of those databases.
Finally, it writes the resulting rows it found to a table in SQL Server using Write-SqlTableData

foreach ($RegisteredSQLs IN dir -Recurse SQLSERVER:\SQLRegistration\’Database Engine Server Group’\ | where {$_.Mode -ne ‘d’} )
Get-SqlDatabase -ServerInstance $RegisteredSQLs.Name | %{
Invoke-Sqlcmd -ServerInstance $RegisteredSQLs.Name -Database $_.Name -OutputAs DataTables -Query
select @@SERVERNAME AS ‘Instance’, DB_NAME() AS ‘Database’, so.ProcName, sc.text
from syscomments sc
inner join sysobjects so
on sc.id = so.id
where sc.text like ‘%baconbaconbacon%'”|Write-SqlTableData -ServerInstance localhost -DatabaseName sandbox -SchemaName dbo -TableName SysCommentSearchResults

So there you have it. Is this code perfect? No. Did it take me less than 10 minutes to write and test this code? Yes. Will it get the job done so that Tom can back to what he was doing before he ran into this roadblock that he had to solve? Yes.

Maybe you don’t need precisely this, but hopefully it will inspire to play around with Write-SqlTableData for something that you do need to do!


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.

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


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|

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