achieve more in less time by using the new sql...
TRANSCRIPT
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Achieve More in Less Time by Using the New SQL PowerShell
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Aaron NelsonData Architect
Paya
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Microsoft Data Platform MVP
Conference SpeakerPASS SummitTech EdIgniteSQL Saturdays
PASS PowerShell Virtual Group Leader
Aaron Nelson [email protected]
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Where to get the modules
You can see the modules on
PowerShellGallery.com but you install
them from your favorite PowerShell editor
SqlServer module for SQL Server, SSAS,
& SSIS
ReportingServicesTools Module for
SSRS & Power BI Report Server
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Install-Module -Name SqlServer
Update-Module -Name SqlServer -WhatIf
Download the SqlServer Module from the PowerShell Gallery
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
<# You may need to include some additional parameters #>Install-Module -Name SqlServer -Force
<# You need to check every once in a while to see if a new version is available. #>Update-Module -Name SqlServer;
Notes about installing & updating
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
<# This will help you find the commands available in each module #>
Get-Command -Module SqlServer -Noun *Job*
Get-Command -Module SqlServer | Out-GridView
Search for commands available inside the SqlServer Module
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Get-SqlAgentGet-SqlAgentJobGet-SqlAgentJobHistoryGet-SqlAgentJobScheduleGet-SqlAgentJobStepGet-SqlAgentSchedule
New cmdlets since 2016
Name Description
SQLRegistration SQL Server Registrations
SQL SQL Server Database Engine
SQLAS SQL Server Analysis Services
SSIS SQL Server Integration Services
Xevent SQL Server Extended Events
DatabaseXEvent SQL Server Extended Events
DAC SQL Server Data-Tier Application
DataCollection SQL Server Data Collection
SQLPolicy SQL Server Policy Management
Utility SQL Server Utility
•
•
•
•
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
What do they do?
• They’re like having access to Object Explorer, or even Object Explorer details, but at the command line!
• <Demo of scripting multiple object, sorted by size, then create date, in Object Explorer Details>
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
What is the benefit to the SQL Providers?
• SQL PowerShell doesn’t have as many cmdlets as it should, but these help to fill in the gaps a little.
• You can see lots of things, but you can also modify them too.
• In the case of SSAS, they can give you more actionable information that SSMS can.
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
How can I easily leverage these Providers?
• Dir = Get-ChildItem
• Running a Dir is almost like piping objects to SELECT-[Object] *• Except that it’s different!
• You can’t always rely on “column names” to be the actual “property name”• To confirm this you will need to pipe to Get-Member or SELECT-[Object] *
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
What’s else is new in the SqlServer module?
• Updated SQL Server provider (SQLRegistration) to display AS/IS/RS groups
• Updated Get-SqlInstance cmdlet. This cmdlet now utilizes SMO and supports all server instances exposed via SMO
• Fixed SqlServer Provider for SSIS • Added Get-SqlBackupHistory cmdlet • Ported PS Provider to .NET Core for PowerShell 6 support • Ported a subset of cmdlets to .NET Core for PowerShell 6
support • PowerShell 6 support on macOS and Linux in Preview.
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Little Fixes that make things a lot easier!
• Added support for PSCredential to Invoke-Sqlcmd• -Name parameter on Get-SqlDatabase has been aliased as Database• Added -ErrorLogSizeKb parameter to Set-SqlErrorLog cmdlet• Fixed the issue where Instance name was showing up as empty string
on directly creating a SMO.Server object after SqlServer module is imported
• Fixed issue where PowerShell was not able to find cmdlets in the module unless the user did an explicit ''Import-Module SQLServer''
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Little Fixes that make things a lot easier!
• Added support for PSCredential to Invoke-Sqlcmd• -Name parameter on Get-SqlDatabase has been aliased as Database• Added -ErrorLogSizeKb parameter to Set-SqlErrorLog cmdlet• Fixed the issue where Instance name was showing up as empty string
on directly creating a SMO.Server object after SqlServer module is imported
• Fixed issue where PowerShell was not able to find cmdlets in the module unless the user did an explicit ''Import-Module SQLServer''
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
Don’t believe me? Look at the comments
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
New Vulnerability Assessment cmdlets
• Export-SqlVulnerabilityAssessmentBaselineSet• Export-SqlVulnerabilityAssessmentScan• Import-SqlVulnerabilityAssessmentBaselineSet• Invoke-SqlVulnerabilityAssessmentScan• New-SqlVulnerabilityAssessmentBaseline• New-SqlVulnerabilityAssessmentBaselineSet
#ITDEVCONNECTIONS | ITDEVCONNECTIONS.COM
<# This is a module created & maintained by the community. It is available in the PowerShell Gallery #>
Install-Module ImportExcel
Want to be able to import or export Excel files?