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

#SQLServer Team is Doing an AMA on Reddit TODAY!

111816_1635_SQLServerTe1.png

The SQL Server team is doing an AMA on Reddit today! The AMA is scheduled to run from Friday 11/18 at 9:30 PDT until 14:00 PDT but you can start asking your questions right now.

As you may know from reading this blog, Folks from the SQL Server team have made an effort this year to be more accessible public forums like twitter, the SQL Community Slack channel, Trello boards, and Reddit, just to name a few. This is a great opportunity to ask a question you’ve been wondering about, or just tune in and read what other people are asking. Don’t forget, on Reddit you can Up-Vote questions/answers that you think are important for others to see.

Here are the details straight from today’s AMA thread:

Hi Everyone!

We’re from the Microsoft SQL Server Engineering Team and we want you to ask us anything!

We’re posting this a bit early so you can start asking questions – feel free to start asking and we’ll start answering Friday 11/18 at 9:30 PDT until 14:00 PDT. We’ll have members from the Engineering teams participating in the AMA. This includes folks working on

  • SQL Server Features such as Columnstore, In-Memory OLTP, Row-Level Security, PolyBase, Stretch, or any of the features listed here.
  • Database Tools for Microsoft SQL Server and Azure SQL Database (SSMS, SSDT, SSRS, SSIS, SSAS, SSMA, SQLPS, CLI Tools)
  • Azure Portal for Azure SQL DB, Azure Elastic Database Pools or Azure Virtual Machines with SQL Server
  • R Services for predictive analytics, machine learning, and using scalable R packages with SQL Server technologies
  • Developing with SQL Server using the language of your choice (Examples. Node, Python, Java, etc.) or connecting your app to SQL Server using our drivers (ODBC, JDBC and open source drivers)
  • Migrating or Building Apps and Solutions with SQL Server and the resources we have available to help you get the most out of SQL Server.
  • SQL Server on Linux
  • SQL Server 2016 Service Pack 1

Here are some question ideas:

  • What’s new in SQL Server Service Pack 1 and SQL Server on Linux?
  • How does your team take the feedback from the community?
  • What tools would I use to migrate my database to SQL Server?
  • What’s a cool trick you don’t think most customers know about?

You can ask us anything about our public products or about the team. If there’s a topic that’s not listed above, ask it anyways! We’ll do our best to answer it. We cannot comment on unreleased features and future plans, though ūüôā Tweet at us with #SQLServer and follow @SQLServer to keep up to speed with what the SQL team is working on. After this AMA, you can also post questions here in reddit or tweet at us any time.

Analyzing SQL Server Buffer Pool with TreeMaps in PowerShell

analyzingsqlbufferwtreemap

On Monday Chrissy LeMaire & I Did a session called “SQL Server Cmdlets and Community¬†Involvement” for the PowerShell 10 Year¬†Anniversary all-day event on Channel9 on MSDN. If you jump to the 18 minutes 30 second mark of that video you’ll see me showing how to look at the Buffer Pool of your SQL Server instance, first with the Out-GridView cmdlet, then I used a function from PowerShell MVP Boe Prox ( b | t ) called Out-SquarifiedTreeMap like so:

You’ll want to get the latest version of from Boe’s GitHub, and here’s the PowerShell code that calls SQL Server to get the Buffer Pool information (Direct Link to Gist):

Please give the code a whirl and let me know what you think.  I will post a longer write-up of how this works soon.

Connect(); 2016 Event #MsftConnect

I received an invite to the Microsoft Connect event (yes I see the problem with name, maybe they forgot Connect isn’t always super popular with Data Professionals, I digress) and at first I didn’t think much of it, but then I noticed that Lara Rubbelke would be speaking at the event so I figured it might be worth checking out.

Here are the details in case you want to check it out yourself.

The Connect(); 2016 developer event starts tomorrow!

Join Microsoft’s Connect(); 2016 live stream to watch keynotes and deep dive technical sessions led by¬†Executive Vice President Scott Guthrie, Principal Program Manager Scott¬†Hanselman, and other industry innovators.

The live broadcast starts November 16th at 9:45am EST

Wednesday, November 16th

9:45am – 10:00am EST Pre-show with Channel 9 team
10:00am – 12:30pm EST Keynotes by Scott Guthrie and Scott Hanselman
12:30pm – 4:30pm EST Q&A with senior executives



Scott Guthrie
Executive Vice President, Cloud and Enterprise Group
Speaker Bio >


Scott Hanselman
Principal Program Manager, Open Source .NET
Speaker Bio >


Lara Rubbelke
Principal SDE Manager, Azure Developer Experience
Speaker Bio >


Nat Friedman
Corporate Vice President, Mobile Developer Tools
Speaker Bio >


Donovan Brown
Senior Program Manager, DevOps
Speaker Bio > 


Beth Massi
Senior Product Marketing Manager, .NET
Speaker Bio > 


Miguel de Icaza
Distinguished Engineer, Mobile Developer Tools
Speaker Bio > 


Maria Naggaga
Program Manager, .NET and Visual Studio
Speaker Bio > 

PowerShell 10th Anniversary Today!

PowerShell turns 10 years old today and they’re throwing an all-day PowerShell event over on Channel 9 to celebrate!

Why should you care? Well, two reasons.

  1. My partner in crime community Chrissy LeMaire & I were invited to contribute a half hour session on how much the SQL Community has gotten involved with helping design PowerShell cmdlets for SQL Server this year (12:15pm EST GMT-5).
  2. The session right before ours (11:45am EST GMT-5) is about how to “Manage Your Garden Sprinkler and Your Mood With¬†PowerShell“.

In the session Chrissy and I put together we go over some of the history & evolution of SQL PowerShell, the SQL PowerShell revolution & the community designing the new cmdlets that have come out this year, and we will close with me demoing a bunch of the new cmdlets that folks like yourself in the community helped define.

The final demo is one I’m hoping will help change the way a lot of people think of and approach PowerShell. It’s a nice representation of how I think of PowerShell, and it was only possible because of the new cmdlets available in SSMS 2016 and a strong dose of community help from PowerShell MVP Boe Prox ( t | b ).

Boe has already published a new version of his Out-SquarifiedTreemap function on his blog and I will post all the code I used in the demo tomorrow after I have the link to the video on Channel9 and everything.

PowerShell for SSRS is Here!

That’s right, you heard it here second. Today the SSRS team announced the availability of new SSRS commands in GitHub & the PowerShell Gallery.


To get this module just run this command in PowerShell:

Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)

After you have the module downloaded and unpacked you’ll probably want to run a command like this to find the list of SSRS commands that you will now have available at your fingertips:

Get-Command -Module ReportingServicesTools

I have been testing these commands for several weeks and so far my favorite command is Write-RsFolderContent because it will allows you to write the .RDL & .RSD files from a directory on your machine to your SSRS folder. Like the whole thing. You don’t have to throw it into a loop or anything. Try it out!

Here’s the full list of PowerShell commands for SSRS that are available today.

Backup-RsEncryptionKey
Get-RsCatalogItems
Get-RsDataSource
Get-RsItemReferences
Grant-AccessOnCatalogItem
Grant-AccessToRs
New-RsConfigurationSettingObject
New-RsDataSource
New-RsWebServiceProxy
Out-RsCatalogItem
Out-RsFolderContent
Register-PowerBI
Remove-RsCatalogItem
Restore-RSEncryptionKey
Revoke-AccessOnCatalogItem
Revoke-AccessToRs
Set-RsDataSet
Set-RsDataSource
Set-RsDataSourcePassword
Set-RsEmailSettingsAsBasicAuth
Set-RsEmailSettingsAsNoAuth
Set-RsEmailSettingsAsNTLMAuth
Set-RsSharedDataSource
Write-RsCatalogItem
Write-RsFolderContent

Give them a test and let the SSRS team know what you think!

SQL Server Management Tools on Windows and Other Platforms at #SQLSummit

If you are at the 2016 PASS Summit and you want to find out what’s going to happen next with SSMS on Windows and ‘Other Platforms’ then I highly recommend that you check out this session by Ken Van Hyning ( t ) and Sanjay Nagamangalam ( t ).

I can tell you that Ken is very passionate about giving us Data Professionals the best tools for us to use, so I really encourage you to check out his session and if you have more questions or ideas about SSMS, try to chat with him or Sanjay after the session if they have time. I hear they might even throw in a SQL PowerShell demo or two today J

In this session, we’ll walk you through our investments in SQL Server manageability tools for Windows and other platforms. We’ll present enhancements in our flagship SQL Server Management Studio (SSMS) and SQL client tools based on feedback from the community and discuss our plans for cross-platform database management tooling for
SQL Server and PowerShell. We will also answer questions about your favorite feature in SSMS and the roadmap for SQL client tools such as Profiler, Database Tuning Advisor and Distributed Replay.

Date: Oct 28

Time:
9:30AM – 10:45AM

Room:
TCC Tahoma 5

Session Code: DBA-213-M

Ken Van Hyning (left)
SQL Client Tools Engineering Manager at Microsoft


Sanjay Nagamangalam (right)
Principal Program Manager, Microsoft

Accompanying Materials:

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
  • SARGABILITY
  • Implicit conversion
  • OUTER APPLY
  • UDFs
  • NOLOCK

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.