Tag Archives: Powershell VC

2016 – That was a Year :-)

Posted on by

Its the time of year for reflection and I have had the most amazing 2016, I am blessed that I love what I do so much. I thoroughly enjoy writing and talking and sharing and commenting and supporting and cherishing all the SQL and PowerShell things. I wrote about using Power Bi to display my checkins. I only started this in June and this is where I have been 🙂

swarm

I learnt about Pester and ended the year incorporating it into dbatools and dbareports. I also started using GitHub It is quite surprising to me how much time I now spend using both. I also had to start learning DSC for the client I was working with because as ‘the PowerShell guy’ I was the one who could the easiest. I learnt things and then forgot them causing me to find this Pester post via google later in the year!! (That’s a big reason for blogging by the way)

Early in the year we organised with SQL Saturday Exeter

Helping to organise a SQL Saturday is a lot of fun, especially when you do it with good friends, but choosing sessions is by far the most challenging part of it for me. I could have chosen at least 60 of these sessions and I know people were disappointed not to have been chosen. I was also the first person many saw at SQL Bits in Liverpool manning the front of house and getting asked the best question ever

The Beard says

When you go to an event –  Say thank you to the organisers and volunteers

and a TERRIBLE thing happened – I broke my DBA Team mug

WP_20160223_07_51_03_Pro.jpg

Luckily the fine folk at redgate sorted me out with a replacement from deep in the stores somewhere and gave it to me at SQL Saturday Exeter 🙂 Thank you.

I spoke at the PowerShell Conference Europe and met and made some great friends which lead to me speaking at the PowerShell Monday in Munich and the Dutch PowerShell Usergroup. SQL Saturday Dublin was a blast, its a wonderful city, Manchester had a whole PowerShell Track 🙂 and Cambridge was memorable for the appalling journey as well as the chance to share a stage with Chrissy. PowerShell Conference Asia in the sovereign city-state of Singapore was such a good event and place. Lastly of course was Slovenia with its fantastic Christmas lights and awesome event organisation. I visited some user groups too. Southampton run by my good friends John Martin and Steph Middleton Congratulations to John on his first MVP award yesterday, Cardiff for the Return of the Battle of the Beards with Terry McCann and Tobiasz Koprowski where the projector threw its toys out of the pram and Birmingham in the school hall which was slightly chilly (theres a joke there for some people)

Amazing things happened

We created https://sqlps.io/vote and https://sqlps.io/ssms and https://sqlps.io/powerbi to enable anyone to influence Microsoft and help to improve the PowerShell SQL experience

and lo and behold there was a new sqlserver module 🙂

I was also invited by Aaron and Chrissy to become an officer for the PASS PowerShell Virtual Chapter oh and we made https://sqlps.io/slack to enable people to talk about all things Data Platform – Another addition to my life that I didn’t have at the beginning of the year. I spend a lot of time in there in the #dbatools and #dbareports channels and have made some fantastic friends. Chrissy and I created the SQL Community Collaborative GitHub team and added dbatools and dbareports and even more friendships were born

And that’s the biggest and bestest thing about this year. Some amazing new friends and spending time with all my other friends. I started writing out a list but was terrified I would have missed someone out, so to all my friends

THANK YOU for a brilliant 2016 and 2017 shall be just as good 🙂

Here are a few of my pics from the year with a lot of my friends

Terry waving hishands
John in Dublin
The PowerShell track in Manchester
Having a meal in Manchester
Big John doing indexes with cups in Exeter
Thank you Microsoft
Bearded Buddy Tobiasz
Munich with the scripting wife and Jeff
PowerShell Monday in Munich
Mr Snover joined us in Munich via Skype
Jeff giving out prizes in Munich
Drinks after Munich
Gael met me in London when I had an hour to spare.
Me and the smiliest PowerShell man in the world Irwin
Dutch PowerShell User Group
Dutch PowerShell User Group
Lunch at Dutch PowerShell User Group
Cambridge breakfast room!!
SQL Saturday Cambridge
Two Cajuns and an Englishman
Awesome Chrissy in Cambridge
Chrissy and Deep Fat Fryer in Cambridge
Two great beards its SQL WareWolf
We had a fabulous meal in Cambridge. Me, Chrissy,Lu and Dan
A great place to work
SQL relay Cardiff – there were people sitting on the floor
Not the session you were expecting by Richard and I in Cardiff at SQL Relay
SQL relay Reading
The first Sentry One Prize at SQL Relay in Reading
I met some apes in Singapore
A fabuilous evening natter
Matt and Milton – 2 of the PS Conf Asia organisers in the AWS building
Jason Yoder pre-con in Singapore
The Shy and Retiring Jason at Singapore User Group
Speakers on the metro in Singapore
PowerShell team in Singapore
Flynn talked containers in Singapore
Trevor Hess
An evening with friends in Singapore
Angel, June and Kenneth
A great buddy Mathias
The coolest man in Singapore Jaap
Aaron during the PowerShell 10th Anniversary
Even Jake the dog watched the PowerShell 10th Anniversary
Jonathan 🙂
Southampton User group in December
Alex was disappointed
The glitter beard in Slovenia. even the next day there was glitter on many speakers
A win for Andre and Alex
Slovenia Sessions
The Speakers in Ljubljana being ‘taught’ by Matija
Andre showing magic in Slovenia
ljubljana is beautiful
John is demonstrating indexes with cups in Exeter
Andrew the man who taught me
John in Dublin
My amazing wife made me this PowerShell rug
Manning the front of house at SQL Bits with armed protection
My buddy Mark
SQL Bits
Andre didnt put much thought into his costume. I try not to think about Kevins!!
Sensible times at SQL Bits
Terry goes old school
Errm!!! Buck being Buck
Inside the space bubbles at SQL Bits
Happy Birthday Alex
Yay its Andre
Steph of the Locke
Is it the new Karla? No its the original Catherine
Just a chat with a few friends
PSConfEU keynote
PSConfEU
I borrowed Christians Hat
Is he French? Is he English? He’s just Gael and I love him
Northern people are strange (I’m allowed to say that I’m northern too!!)
Jeff with the EU flag
The amazing opening for PSConfEU
Return of battle of the beards with Tobiasz and Terry
My PSConfEU shirt
Terry enjoying the SQL Sat Exeter food
The SQL South West Flag hanging proud
The SanDisk competition at SQL Sat Exeter was brilliant
Choosing sessions is tough
SQL Sat Exeter
The waizardry of Terry McCann
Speaking at Southampton in a shirt and tie
SQL Sat Exeter
Disaster! My DBA Team mug smashed
SQL Sat Exeter

 

PSConfAsia 2016

Posted on by

I have just got back to the UK from Singapore following the amazing PSConfAsia conference. I must say that Matt, Milton, Sebastian and Ben did a fantastic job organising this conference and were proud that there was a notable increase in attendees from last year.

sebastians-photo

 

The conference began (unofficially) with a PowerShell User group session in the Microsoft Offices on Wednesday where Ravi Chaganti spoke about DSC

WP_20161019_19_56_07_Pro (2).jpg

and then Desmond Lee lead a Q and A session. In the end we decided that all the answers were

It Depends and Test in your Environment

That evening, I even managed to jump on the PASS PowerShell Virtual Chapter session by Scott Sutherland Hacking SQL Servers on Scale using PowerShell the recording of which is here  A session organised and managed online in three different time zones by Aaron Chrissy and myself :-).

On Thursday the conference proper started with a pre-con day at the Amazon Web Services office. Yes, you read that right. This conference really highlighted the cross-platform direction and adoption of open-source that Microsoft is taking.  Jason Yoder spent all day teaching a group “PowerShell for Beginners” in one room

WP_20161020_09_24_43_Pro.jpg

while The Amazon Web Services Team showed DevOps on AWS with PowerShell in the morning and June Blender gave a SAPIEN Toolmaking Seminar.fter this we went back to the Microsoft Offices for another User Group where Jason Yoder gave a (nother) session with Jaap Brasser on PowerShell Tips and Tricks (Demo)

WP_20161020_19_26_24_Pro (2).jpg

Friday started with The PowerShell Team represented by Kenneth Hansen & Angel Calvo talking about PowerShell Past, Present and Future. It was really good that there was such great access to the product team at the conference and I saw lots of interaction around the conference as well, in addition to the sessions they provided.

Next up for me was another session from the PowerShell Team, this time Hemant Mahawar & Jason Shirk taking us on a Journey Through the Ages of PowerShell Security

Execution Policy is not a security feature

That took us to lunch, we were treated to excellent lunches at this conference

WP_20161020_12_07_14_Pro (2).jpg

After lunch I sat in the PowerShell Teams Ask Us Anything session although I was mainly preparing for my own session Powershell Profile Prepares Perfect Production Purlieu which followed. There were excellent sessions on JEA, Nano Server, Chef and DSC, Containers, ETS and securing PowerShell against malware whilst I attended Flynn Bundy’s session about Windows Containers and Building GUIs with XAML with David Das Neves

WP_20161021_15_58_12_Pro (2).jpg

That evening, organisers, speakers and attendees all went to the Penny Black pub on Marina Bay and enjoyed some food, refreshments and networking

Saturday started slowly after the rain (another impressive ‘feature’ of Singapore)  but the first session was a brilliant one with Hemant Mahawar & Jason Shirk talking Pragmatic PowerShell and answering questions. I am glad Jason used Carnac to show what he was typing so that people could (just about 🙂 ) keep up. I then attended the excellent session about contribution with Microsoft.

The rest of the day had amazing sessions on Azure Automation, IoT, AWS Cloud Formation, Centralised Repository Server, Chef, Puppet, Professional Help, Nano Server, Docker, DSC, Release Pipeline and of course some bearded fella talking about Installing SQL Scripts and creating Pester Tests for them and combining PowerShell, SQL, SSRS, PowerBi and Cortana 🙂

Jason Yoder's photo.jpg

My takeaways from the conference were that Microsoft is very open to all members of the open source community, DevOps is a very important topic and also the following points from the PowerShell team

PowerShell Team want YOU to contribute.
Interact with them
File bugs
Feature Requests
Documentation
Tests
Code

and

Fixing is better than complaining 🙂 @HemanMahawar #psconfasia You can help fix the documentation. Use the contribute button on the doc

and

If you are thinking of starting or run a PowerShell usergroup Microsoft would like help. Tag 1 of the team such as @ANGELCALVOS #psconfasia

Special thanks and congratulations must go to Matt, Milton, Sebastian and Ben for their excellent organisation and for creating an awesome event. I am looking forward to seeing how they can better it next year and also hoping that seeing all the fabulous speakers and sessions will inspire some attendees from this years event to share their own knowledge and experience at local user groups and even next years conference.

PowerShell CMDLets added for SQL2016 Always Encrypted

Posted on by

The post on the SQLServer blog at TechNet by the SQL Server Tools Team today made me jump out of my seat.

The July update for SSMS includes the first substantial improvement in SQL PowerShell in many years. We owe a lot of thanks for this effort to the great collaboration with our community. We have several new CMDLETs to share with you

In one release there are twenty-five new CMDLets for the new sqlserver module

This means that if you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to be Import-Module SqlServer in order to take advantage of the new provider functionality and new CMDLETs. The new module will be installed to “%Program Files\WindowsPowerShell\Modules\SqlServer” and hence no update to $env:PSModulePath is required.

So SQLPS will still continue to work but will not be updated and will not contain the new CMDlets or the future new CMDlets.

So what new things do we have?

This month we introduce CMDLETs for the following areas:

  • Always Encrypted
  • SQL Agent
  • SQL Error Logs

Chrissy LeMaire has written about the new SQL Agent cmdlets

Aaron Nelson has written about the new Get-SqlErrorLog cmdlet

Laerte Junior has written about Invoke-SQLCmd

All four of us will be presenting a webinar on the new CMDlets via the PowerShell Virtual Chapter Wed, Jul 06 2016 12:00 Eastern Daylight Time If you cant make it a recording will be made available on YouTube on the VC Channel https://sqlps.io/video

Always Encrypted CMDlets

That leaves the Always Encrypted CMDLets and there are 17 of those!

Add-SqlColumnEncryptionKeyValue Adds a new encrypted value for an existing column encryption key object in the database.
Complete-SqlColumnMasterKeyRotation Completes the rotation of a column master key.
Get-SqlColumnEncryptionKey Returns all column encryption key objects defined in the database, or returns one column encryption key object with the specified name.
Get-SqlColumnMasterKey Returns the column master key objects defined in the database, or returns one column master key object with the specified name.
Invoke-SqlColumnMasterKeyRotation Initiates the rotation of a column master key.
New-SqlAzureKeyVaultColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in Azure Key Vault.
New-SqlCngColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store supporting the Cryptography Next Generation (CNG) API.
New-SqlColumnEncryptionKey Crates a new column encryption key object in the database.
New-SqlColumnEncryptionKeyEncryptedValue Produces an encrypted value of a column encryption key.
New-SqlColumnEncryptionSettings Creates a new SqlColumnEncryptionSettings object that encapsulates information about a single column’s encryption, including CEK and encryption type.
New-SqlColumnMasterKey Creates a new column master key object in the database.
New-SqlCspColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store with a Cryptography Service Provider (CSP) supporting Cryptography API (CAPI).
Remove-SqlColumnEncryptionKey Removes the column encryption key object from the database.
Remove-SqlColumnEncryptionKeyValue Removes an encrypted value from an existing column encryption key object in the database.
Remove-SqlColumnMasterKey Removes the column master key object from the database.
Set-SqlColumnEncryption Encrypts, decrypts or re-encrypts specified columns in the database.
 

 

That seems to cover setting up Always Encrypted with Powershell , removing it and getting information about it. When the new SSMS update is dropped you will be able to start using all of this new functionality.

Just remember Import-Module sqlserver

CALL TO ACTION

Microsoft are engaging with the community to improve the tools we all use in our day to day work. There is are two Trello boards set up for YOU to use to contribute

https://sqlps.io/vote  for SQLPS  sqlserver PowerShell module

https://sqlps.io/ssms for SSMS

Go and join them and upvote YOUR preferred choice of the next lot of CMDlets

trellocount

 

We have also set up a SQL Community Slack for anyone in the community to discuss all things related to SQL including the Trello board items and already it seems a good place for people to get help with 150+ members in a few days. You can get an invite here https://sqlps.io/slack

Come and join us