Naked PowerShell

Sunday, June 16, 2019

dbatools 1.0 and how to make yourself a SuperHero

dbatools 1.0

https://dbatools.io/

Have you ever needed to migrate a SQL Server database from one version to another and dreaded all the T-SQL you will have to write and debug?

What about migrating 30 SQL Server machines with 100's of database on each machine?

I feel your pain.

How about using Log Shipping to migrate the data over time, then cut over with little downtime.

# Also supports multiple destinations!
# Oh, and has a ton of params, so use a PowerShell splat
$params = @{
Source = 'sql2008'
Destination = 'sql2016', 'sql2017'
Database = 'shipped'
BackupNetworkPath= '\\backups\sql'
PrimaryMonitorServer = 'sql2012'
SecondaryMonitorServer = 'sql2012'
BackupScheduleFrequencyType = 'Daily'
BackupScheduleFrequencyInterval = 1
CompressBackup = $true
CopyScheduleFrequencyType = 'Daily'
CopyScheduleFrequencyInterval = 1
GenerateFullBackup = $true
Force = $true
}

# pass the splat
Invoke-DbaLogShipping @params

# And now, failover to secondary
Invoke-DbaLogShippingRecovery -SqlInstance localhost\sql2017 -Database shipped

dbatools is an expansive and extremely flexible PowerShell toolkit that leverages the scripting abilities within PowerShell to manipulate SQL Server objects.

Do NOT let the 1.0 fool you. This code base has been building incrementally over the past few years, with many contributors writing code and creating Pester tests.

There are modules for every common operation that a SQL Server DBA will have to perform.

This toolkit contains over 500 modules to help you get your work automated and projects completed.

My favorite modules are the discovery modules, to help you find details about your SQL Server instance.

https://docs.dbatools.io/

Get-dba* in the Search box will show you all the cool discovery modules. The naming convention of the module will give you a hint of what it will do.

There is good documentation in the code and on the dbatools blog.

https://dbatools.io/blog/

Support is the backbone of this tool, where you can chat or email directly to the developers, contributors, and power users that will give you tips, code samples, and help you debug any roadblock you may encounter.

To get started, try this Getting Started web page.

https://dbatools.io/getting-started/

On Slack you can connect with the dbatools family and just soak in all the great chats and posting that relate to the tool, PowerShell, and SQL Server. The Slack channel is #dbatools and it is a great place to hang out and meet really awesome DBA's and developers from around the world.

https://dbatools.io/slack

The code is community driven, open source, and free.

What environments does dbatools support?

SQL Server 2000 – 2017
Express – Datacenter Edition
Clustered and stand-alone instances
Windows and SQL authentication
Default and named instances
Multiple instances on one server

We love new contributions of code, so please join up!

If you want to file an issue with the code or have a suggestion, go to http://dbatools.io/issues

Chrissy LeMaire (t) @cl is the ring leader of this awesome group, and she can always point you in the right direction for help, support or encouragement.

Thursday, July 12, 2018

Script to generate all the exploration I normally do when I want to install a new PowerShell module

#PowerShell Script to generate all the exploration I normally do when I want to install a new PowerShell module, generates code in a new .PS1 for exploring each function in the module including a formatted splat

I find this a time saver for

me.

https://gist.github.com/NakedPowerShell/9711e57e18fa4c4cd0fed148497f900e>

Thursday, July 5, 2018

New PowerShell Module: Get-SQLSatDL

# Blog Post for Get-SQLSatDL

Blog: Get-SQLSat

Get-SQLSatDL : Release Date - 07/05/2018 Version 1.0

In February 2018 I went to my third SQL Saturday in Redmond WA, held on the campus of Microsoft.

I had an awesome time and got to reconnect with many SQL friends and met plenty of new ones.

I always learn so much from the session speakers and I look forward to seeing what kind of content they post on the SQL Saturday website a few days later.

A few days after the event, I went to the schedule web page:

 <http://www.sqlsaturday.com/696/Sessions/Schedule.aspx>

And then proceeded to click on each download icon under the sessions that did have content.

I had to click the download button 27 times.

I could hear Jeffery Snover’s (Microsoft Fellow and Inventor of PowerShell) voice in my head, “don’t be a click next person, automate”.

I was also looking at a few other past events from around the country that I was interested in downloading.

That would be a lot of clicking download.

I created a PowerShell function to automate the downloading of all the content posted to the schedule page of the SQL Saturday event.

I call it Get-SQLSatDL that has only 3 parameters, the first is the local drive and path where you want the downloads to be stored, second is the SQL Saturday event # and then lastly if you want any zip files to be automatically expanded.

For example:

Get-SQLSatDL "K:\SQLSat_DL" 696 -Uz $true

`Get-SQLSatDL [-Tpath] <String> [-Evt] <Int32> [-Uz] <bool> [<CommonParameters>]`

    First parameter [TPath]: Local target path string where to download content to

    Second parameter[Evt]: SQL Saturday event # (an integer between 500-1000)

    Third parameter [Uz]: $true/$false to unzip zip files

You can find my code on GitHub:

<https://github.com/NakedPowerShell/SQLSatDL>

## Installing SQLSatDL

Download the code from <https://github.com/NakedPowerShell/SQLSatDL>

and put it in a local folder like C:\SQLSatDL

## Run PowerShell command line or PowerShell ISE

From within PowerShell type this:

Import-Module C:\SQLSatDL\SQLSatDL.psm1

`# To see a few examples for this module run this`

Get-Help Get-SQLSatDL -Examples

## To download SQL Saturday Event #696 content Redmond WA

`# Decide where you want to store the content downloaded like K:\SQLSat_DL`

Get-SQLSatDL "K:\SQLSat_DL" 696 -Uz $true

## To download SQL Saturday Event #696 Redmond WA content with Verbose

Get-SQLSatDL "K:\SQLSat_DL" 696 -Uz $true -verbose

`# To see all the comment based help`

Get-Help Get-SQLSatDL -All

If you have any questions, you can find me on Twitter @NakedPowerShell

 or send me an email at `NakedPowerShell at gmail.com`

## Future Ideas

Ideas for extending the output of this function:

    - Merge all the CSV log_SQLSAT.txt files in each sub-folder into a SQL table for searching and sorting

    - Use a search tool to scan all the Schedule_[Event#].html files in each sub-folder for keywords / authors

07/05/2018 <https://nakedpowershell.blogspot.com/>

Wednesday, May 30, 2018

Robert Herjavec's Dos and Don'ts: From Cryptocurrency to #Cyber Hygiene

Robert Herjavec's Dos and Don'ts: From Cryptocurrency to #Cyber Hygiene #SharkTank https://www.entrepreneur.com/article/310606

Announcing the May 2018 Git Security Vulnerability

Announcing the May 2018 #Git #Security Vulnerability https://blogs.msdn.microsoft.com/devops/2018/05/29/announcing-the-may-2018-git-security-vulnerability/

ICYMI - dbachecks – Dark Mode Historical Validation #PowerBi

ICYMI - dbachecks – Dark Mode Historical Validation #PowerBi RT @sqldbawithbeard "ICYMI - dbachecks – Dark Mode Historical Validation #PowerBi - " http://ht.ly/Gbsr30kfAHL

Create Function from Variable Value I

Create Function from Variable Value I #PowerShell http://tommymaynard.com/create-function-from-variable-value-i-2018/

YouTube content to Twitter posts with PowerShell & Time Triggered Azure Function

YouTube content to Twitter posts with PowerShell & Time Triggered Azure Function https://ridicurious.com/2018/05/29/youtube-to-twitter-posts-using-azure-functions/

Batch file to create backups of files to remote network paths using date and time stamps

Robocopy example rem batch file to create backups of files to remote network paths using date and time stamps FOR /f "tokens=1-5 delims=/- " %%a in ('date /t') DO SET XDate=%%d%%b%%c FOR /f "tokens=1-5 delims=/- " %%a in ('date /t') DO SET XSDate=%%d%%b FOR /f "tokens=1-2 delims=: " %%a in ('time /t') DO SET XTime=%%a%%b Echo "%XSDate%\%XDate%\%XDate%_%XTime%" set PW=Somepass1@@ set USR=Install rem set SOUPATH=Internal set SOUPATH=Dev_Local rem set LAB=DSCLIVE set LAB=DSCLAB set ROLE=DC1 net use M: \\%Lab%%Role%\c$\%SOUPATH% %PW% /user:%usr% Robocopy C:\Dev_Local M:\Backup\Code_Backup\%Lab%_%XDate%_%XTime%\%Lab%%Role% *.* /S /Z /XF *.EXE *.MSI *.ISO net use m: /delete

How to Create Encrypted Zip or 7z Archives on Any Operating System

How to Create Encrypted Zip or 7z Archives on Any Operating System https://www.howtogeek.com/203590/how-to-create-secure-encrypted-zip-or-7z-archives-on-any-operating-system/

Monday, October 31, 2016

Tweaks to Write-LogEntry

Tweaks to Write-LogEntry that was posted to Twitter by Trevor Sullivan @pcgeek86

His original PowerShell code:

function Write-LogEntry {
[CmdletBinding()]
param (
[string] $Message,
[string] $Severity
)
$CallStack = Get-PSCallStack
$LogMessage = '{0} {1}: {2}: {3}' -f (Get-Date -Format u), $CallStack[1].FunctionName, $Severity, $Message
Add-Content -Path c:\temp\logfile.log -Value $LogMessage
}
function LogTester {
[CmdletBinding()]
param (
)
Write-LogEntry -Message Testing1 -Severity Information
Write-LogEntry -Message Testing2 -Severity Warning
Write-LogEntry -Message Testing3 -Severity Error
}
LogTester

My tweaks to the original PowerShell code:

Function Write-LogEntry {
[CmdletBinding()]
param (
[string] $Message,
[string] $Severity,
[string] $Step
)
$CallStack = Get-PSCallStack
$LogMessage = '{0},{1},{2},{3},{4},{5}' -f $env:computername,((get-date).ToUniversalTime()), $CallStack[1].FunctionName, $Severity, $Message, $Step
Add-Content -Path c:\temp\logfile.csv -Value $LogMessage
}
function LogTester {
[CmdletBinding()]
param (
)
Write-LogEntry -Message Testing1 -Severity Information -Step 10
Write-LogEntry -Message Testing2 -Severity Warning -Step 20
Write-LogEntry -Message Testing3 -Severity Error -Step 55
}
LogTester

I did the following:

Changed the format of the date to UTC that was readable by Excel
Changed the output to a .CSV format and adding commas to the -f
Added the computername as the first entry in the text log
Added a parameter Step to the log

When I am consuming logs, lots of logs, I like tools like Excel to slice, dice, sort, and filter the data for me in a visual way.

When I write logging functions, I like to know what step tripped the log entry. So in my applications I set $Step to a number that is incremental from the last step I used this variable.

For example I set my step to 10 for the variable initially at the beginning of the application. Then at the start of each major piece of logic where I could trip the logging I increment the step by 10.

The steps then help me narrow down where in code the logging got tripped.

A future version of this could be to load the log into a SQL Table and also account for multiple applications writing to the same log.

Thank Trevor !!

Friday, April 1, 2016

Scaling DSC - Desired State Configuration in PowerShell

How to Scale DSC - Desired State Configuration in PowerShell

This series of posts will cover my ideas of how to scale DSC beyond just a few servers or VM's.

DSC is a fantastic tool for deploying consistent servers / VM's

Make the servers cattle, not pets.

But when you have to scale this DevOps process to 10's, 100's, or 1000's of servers, writing code for each server and configuration does not scale.

Data Driven

PowerShell can help generate the DSC code necessary to scale and make DSC data driven.

Why Data Driven?

A common deployment scenario is you want to create a small scale SharePoint farm of one SQL Server, one front end web server, and one App server.

The server for SQL has to be created and configured first, then web and App.

Each server type has different requirement for creation and configuration.

Using DSC manually, you have to remember the order of deployment and many attributes for each server type.

DSCDB can treat this farm as a collection (example Collection_SPsmall). Multiple collections can be created or cloned into a collection library.

It can know in what order the servers have to be deployed, and almost all the attributes that need to be passed to DSC to create MOFs including credentials.

In order to deploy using DSCDB, you only need the name of the server set (example SPsmall_1) and optionally a few additional parameters. The server set uses the collection (Collection_SPsmall) to know the farm configuration.

The deployment can then run either sequentially or in parallel.

The Scaling DSC Series

#1 Creating a DSCDB

A DSCDB is a simple set of tables in a database that is used to capture attributes about each server that is needed for deployment.

#2 Populating a DSCDB

There are four ways to populate the database:

Importing an Excel file of server names and attributes
Importing a text file of server names
Importing server names from AD - Active Directory
Manually update the SQL tables

#3 Deploying using DSCDB

This post will talk about how to deploy servers in a automated fashion using the DSCDB.

#4 Future Features for DSCDB

This post will discuss potential features for future releases.

Conclusion

This database is not a CMDB but a database specifically designed for deployment using DSC.

All the source code and documentation for this series will be in Github.