Tag Archives: #TSQL2sDay

#TSQL2sDay – NomNomNomNomNom

Posted on by

The topic for this months T-SQL Tuesday #112 hosted by Shane O’Neill (Blog / Twitter) is about “dipping into your cookie jar”. This reference means “when times get tough how do you dip into your reserves to keep going”. Shane asks the following:

That is what I want from the contributors of this T-SQL Tuesday, those memories that they can think back on for sustenance. Like the humble cookie, I want a humble brag.

Mmmm Cookies


Photo by Pille-Riin Priske on Unsplash

I’m not good at bragging, I’m generally convinced that all of you are better than me. Yes, I am aware that it is irrational. This has made writing this post really hard. Sure, I get immense pleasure and satisfaction from solving a problem, that’s a form of instant fulfillment. Certainly, I enjoy teaching people and passing over my knowledge for them to use.
I am not going to write about technical things that I have done because they don’t give me sustenance in that way.

So what does give me sustenance when times are hard?

People.

The things I am most proud of are the things other people do where I have played a small part. These are the things I look back at and help to energise me. Things like

  • A couple of people who I suggested started writing blogs and then speaking who are now seen as experts in their niche.
  • The people I mentored as new speakers who are now speaking all over the continent.

The most recent story was a DBA who sat in a full day pre-con at a SQL Saturday, took loads of notes and waited at the end to ask questions. We were looking at some code and she was telling me it wasn’t very good and apologising for it. It was good, it performed the required actions over a large estate and I told her so. I asked about her job and with a big sigh, she told a story of being stuck in a rut, dealing with a lot of legacy systems, not enjoying it and not being able to move on. We had a long talk.

Cut to this years SQL Bits and she came running up to me all energised. She has a new job, doing something “Cool in the cloud”, she said the things she had learned had helped her to land this role.

In all of these cases, it is the person involved who has done all of the hard work but it is these things that keep me going. The thank yous and the smiles I see on those peoples faces as they do the thing that they love and enjoy their success and progression 🙂

Cake !!!!!


Photo by Prince Abid on Unsplash

Hey, thats cake and not cookies Rob.

I know. The biggest thing that keeps me going when times are tough though is the security I am able to provide. Nearly 20 years ago my life was very different. Without a job, I’d had to give up a career, struggling dealing with my wife’s serious illnesses, suddenly responsible for the entire household without the means to provide, I was in a very bleak place and saw no way out.

So to have found a career that is my hobby, to be able to work and also to have fun, to have a social world that provides me with friends and entertainment in many countries and the opportunity to experience different cultures and still be able to live comfortably. Thats a blessing and what keeps me going.

Also being able to pay my dad back for turning up with sacks of potatoes by taking him to football matches and comedy shows 🙂

Acknowledge what you have got, tell your loved ones that you love them, enjoy life and use your cookies when you need them but don’t forget the cake 🙂

Hitting the Wall – #TSQL2sDay

Posted on by

farrel-nobel-97504-unsplashPhoto by Farrel Nobel on Unsplash

Welcome to another edition of T-SQL Tuesday!

This T-SQL Tuesday is hosted by Wayne Sheffield ( blog | twitter ) and he has asked us to talk about

[…] a time when you ran up against your own brick wall, and how you worked it out or dealt with it.

Hitting The Wall

When stuck in a problem in the past, I could often be found glued to a keyboard and screen for many hours. I would try this way and that way, I would Google and read Stack Overflow looking for ways around the particular issue I was seeing trying different things and finally I would get frustrated and fed up and stop.

Maybe I would go and walk the dog, maybe just sit somewhere else but I would often find that I had an idea how to solve my problem and quickly go back to the keyboard frustrated that I hadnt thought about this earlier and frequently rinse and repeat.

A Different Way

It took me many years to realise this and I wish I had done so sooner but once I made the connection that leaving the problem to one side for a little while meant that I often found a way to a solution for a problem I started setting a time limit.

30 minutes

If I have been stuck on a problem for 30 minutes, I (mostly, I still sometimes fail at this) stop, take a break, go for a walk or do something different and the number of times that I arrive if not at a solution then at a path to a solution is remarkable.

Ask

The other thing to do at this point in the troublesome problem solving is to ask. Twitter, Google, Slack, Stack Overflow. These are all excellent resources where you can quickly find people who are willing and capable of helping.

Don’t be like me and take years to work this out 🙂

#tsql2sday – Giving Back – Reprise

Posted on by

Itsql2sdayt’s TSQL Tuesday again! This month our host is Riley Major (b/t) and the subject is Giving Back. He’s given us two options here (as well as the side option of your favorite 2017 improvement). Pick a way that you’d like to give back to the community and talk about it, or if you already give back,  tell us how and why you started.

 

There will be a lot of excellent posts on this subject and one of the things that I like is that you can go to http://tsqltuesday.com and look up all of the entries which means that for a particular topic you can find a bunch of blog posts from different angles (I miss-typed that as angels first and yes they are all angels!) whether it is career improvement or technical like say Extended Events it is all there waiting for you 🙂

I wrote about giving back in 2014 when I was about to help organise SQL Saturday Exeter for the first time. Last year I blogged about a few of the wonderful people who made a difference to me. So this post is a little about how I give back and also hopefully some hints that can help you to do the same as well.

How

There are so many ways that you can give back to the community as Riley’s post shows.

Sharing your knowledge is a good way. I share my knowledge in blog posts and in sessions at user groups and at conferences. You can too.

From beginner, introductory posts and talks to expert level deep dives every single one of us has learned from the blog posts that other people have spent their time and effort creating.

YOU can share your knowledge, even if you have only been using a technology for a few months, you have knowledge of the things you have learned and the things that would have made it easier. Write a post about those things.

Answering questions is another way. You can do this on Stack Overflow, SQL Server Central, PowerShell.Org , Reddit, Facebook , on Twitter using the #sqlhelp or #PowerShellHelp or just in person. I try to answer questions when I see them on twitter or in the SQL Community Slack

Mentoring or just providing feedback to people. You can offer to proof read blog posts or abstract submissions or you can listen to peoples presentations. I do this and it is a lot of fun

I also share my knowledge via my GitHub. All of my presentations slides and code are available as well as other code that I use. I also contribute to open-source projects such as dbatools and dbachecks.  You can do this too. You could open an issue for an improvement or bug. You can contribute your code, even if you are not confident writing the code you can fix spelling mistakes or add documentation., everything helps

You can help with organisation of events. I have helped to organise the PowerShell Europe Conference, PSDay.UK, SQL Saturday Exeter, SQL SouthWest user group, PASS PowerShell Virtual Group. I have also helped with session choices for a number of other events like SQL Grillen and SQLGLA and I have volunteered at many events from SQL Saturdays to SQL Bits. Everything from setting up and tearing down (IE moving heavy things and cleaning up rubbish) to sitting on the information desk, giving out badges, making sure the speakers are on time in their sessions. You can do this too. Just ask the organisers of the events what they need. It is better to do this prior to the event than on the day but I am sure all help is welcomed. Richard Munn and I talked (waffled?) about this at SQL Bits this year

If you would like to be considered as a volunteer for next years SQL Bits please email helpers at sqlbits.com

Why

So why did I start giving back?

I wanted to be useful. I saw the amount of work that Jonathan and Annette were doing organising SQL Saturday Exeter and SQL South West. I hoped that I could help them with that.

Why do I carry on doing it?

Because it is fun 🙂 I enjoy speaking, I enjoy sharing my knowledge and talking to people

That is good but there is more to it as well

You learn so much by writing a presentation or a blog post because you will do research.

You will learn even more when people ask you questions in your sessions or leave comments on your blog posts and you have to go and find the answers

You learn new and useful skills and demonstrate your knowledge to potential employers

My blog is a scrapbook of knowledge that I go back to and use all the time (and sometimes I forget that I have written something and find my own post in the search results!)

But the most important reason is that I feel that it pays back some of the benefit that I have gained from all of those people who’s time and effort I made use of for free when I was learning and continue to do so to this day. All of those blog posts and videos and presentations that I consumed have helped to make me the technician I am today. I have skills and abilities that I would not have without them all and by giving back I hope that I am enabling others to develop and see the benefit of sharing so that they will continue to do so in the future and I can learn from them.

 

 

 

 

 

 

 

TSQL2sDay – Get-PostRoundup

Posted on by

First an apology, this round up is late!

The reason for that is an error in the PowerShell testing module Pester (That’s not completely true as you shall see!!)

I spoke in Stuttgart at the PowerShell Saturday last weekend and had intended to write this blog post whilst travelling, unfortunately I found a major error in Pester (again not strictly true but it makes a good story!!)

I explained it with this slide in my presentation

Yep, I forgot to pack my NUC with my VMs on it and had to re-write all my demos!!

But anyway, on to the TSQL2sDay posts

What a response. You wonderful people. I salute you with a Rimmer salute

There are 34 TSQL2sDay posts about dbatools, about starting with PowerShell, If you should learn PowerShell, SSAS, SSRS, Log Shipping, backups, restores, Pester, Default settings, best practices, migrations, Warnings in Agent Jobs, sqlpackage, VLFs, CMS, Disabling Named Pipes, Orphaned users, AG Status, AG Agent Jobs, logging, classes, auditing, copying files, ETL and more.

I am really pleased to see so many first timers to the TSQL2sDay blog monthly blog party. Please don’t let this be your only TSQL2sDay post. Come back next month and write a post on that topic.

Here they are below in the media of tweets, so that you can also go and follow these wonderful people who are so willing to share their knowledge. Say thank you to them, ask them questions, interact.

Learn, Share, Network

Volker wrote about testing best practices with dbatools

Dave explains why PowerShell is so useful to him in his ETL processes

Steve writes about the time he has saved using PowerShell to automate restores and audit SQL Server instances

https://twitter.com/Steve_TSQL/status/907713842943115264

Nate talks about copying large files like SQL Server backups using BITS with PowerShell

Warren talks about his experience as a beginner, the amount of things he automates and his DBReboot module

THANK YOU every single one and apologies if I have missed anyone!

 

 

#TSQL2sDay – Starting Out with PowerShell

Posted on by

tsql2sdayThis months TSQL2sday is hosted by me!

Surprise! – I chose PowerShell

I am really looking forward to seeing what other people post. Some advanced scripts that will help you and show you how you can use PowerShell to save you time and hopefully some beginner posts explaining experiences or showing you how to start with PowerShell and SQL Server. I decided to go with the latter.

First though a warning.

PowerShell is another language, you are not going to be as proficient in a new language as you are in the language you spend all day working with. You will have to go through the learning curve and you will have to understand how to interpret errors. There is a learning curve just like with any language. Undoubtedly you will get frustrated at times. Reach out for help. Use twitter, use the #powershellhelp in the SQL Server Community Slack channel. There are many other places and plenty of people who will be glad to help you.

Open the Editor

If you are using a Windows machine you will have PowerShell installed. You will find it in your start menu on Windows 7 under All Programs, Accessories, Windows PowerShell folder, and then click Windows PowerShell or Windows PowerShell ISE. On Windows 8 or 10 search for PowerShell.

PowerShell or PowerShell ISE? Use PowerShell instead of cmd.exe for command line usage and ISE for developing scripts, functions and modules. (However, read on before making your choice)

PowerShell on Other O/S’s (and Windows!)

Just as SQL Server is available on Linux PowerShell is also available cross-platform, you can run and edit PowerShell on Linux and on Mac natively. The best editor to use is VS Code which is also available cross-platform. In fact, as Microsofts David Wilson says in this blog post

The PowerShell ISE has been the official editor for PowerShell throughout most of the history of Windows PowerShell. Now with the advent of the cross-platform PowerShell Core, we need a new official editor that’s available across all supported OS platforms and versions. Visual Studio Code is now that editor and the majority of our effort will be focused there.

So my advice is, whichever operating system you are using, use VS Code to write, edit, debug and run your PowerShell as that is the editor where Microsoft are spending the majority of their effort in development. Follow the instructions here to install You can also use it with many other languages including T-SQL. I find it a very useful tool

Where to start?

Well that depends what you want to do. Start by reading. Start by trying to accomplish something in PowerShell even if you know how to do it another way. This will also help you to realise when you are trying to use a hammer to put in a screw (using the wrong tool for the job)

What Command?

You don’t know where to start so how do you know the command to use? Here is a first command. You will use it often. I use it everyday still despite using PowerShell daily for many years. But don’t run it yet!

Get-Command

PowerShell is quite intuitive in it’s command naming. It uses a Verb-Noun syntax. You know without me explaining what this command will do. It will “Get” the “Command” (s). PowerShell uses singular nouns for its commands. I have 8000+ commands on my machine so it would have overloaded you with commands if you had run it without filters!

We use * as a wildcard in PowerShell so we can use that with our Get-Command command to find some commands to run. Type Get-Com and then hit the tab button. This will not only help you as it will auto-complete, it will reduce the number of errors as it will only auto-complete correct command names :-).

Get-Command *service

This will show all commands which end with service. The results will have some different headings. The screenshot below shows just one command to reduce confusion.

03 - get-service.png

The type of command, the name of the command, the version of the command and the module it is in. PowerShell uses modules to group commands together and to enable you to install the ones that you require from a repository like the PowerShell Gallery (more on this later).

You can find all of the commands in a module with the Get-Command command like this

Get-Command -Module microsoft.powershell.management

This will show you all of the commands in the management module which may be a good place to start exploring. In this module for example

  • Test-Connection is like ping but better
  • Test-Path tests the existence of files and file paths
  • *-Service works with services
  • *-Process works with processes
  • *-Computer for power options for a computer
  • Get-ComputerInfo which will give you computer information (surprisingly 🙂 )

This is also something to remember in the future when you want to know what commands a module has or what the precise name of the command is.

Maybe once you install the dbatools module, the sqlserver module or the SSRS module??

HOMEWORK – Examine the names of commands in other modules that you find in

Get-Module

or for more choices

Get-Module -ListAvailable

How do I use that command though?

You need to read! PowerShell comes with a lot of documentation, a lot of it is available on your machine. Lets introduce another command

Get-Help

As you can imagine, this gets the help for a command or returns help topics about PowerShell. Again, I use this every single day and you should too. Lets start with the first one. Lets get help for the Get-ChildItem command

Get-Help Get-ChildItem

Now, some of you may not get very much returned and a message at the bottom which says

Get-Help cannot find the Help files for this cmdlet on this computer. It is displaying only partial help.
— To download and install Help files for the module that includes this cmdlet, use Update-Help.
— To view the Help topic for this cmdlet online, type: “Get-Help Get-ChildItem -Online” or
go to https://go.microsoft.com/fwlink/?LinkID=113308.

When you have something else to do I would recommend running Update-Help as it suggests (It can take a few minutes to run) but for now make use of the -Online switch.

HOMEWORK – Run Update-Help

Get-Help Get-ChildItem -Online

This is available for all of the Microsoft modules and some of the better open source modules, dbatools is an example of a (brilliant) community module which has -Online URLs for it’s commands. Running Get-Help (with or without the -Online switch) will give you plenty of information about the command, the switches available, the inputs and outputs.

Maybe start by using the -examples switch

Get-Help Get-ChildItem -Examples

will show you some examples of how to use the command

05 gethelp examples

Now you can start to explore the file system with this command (also the SQL Server, Registry, Certificate Store and many more things)

I also like to use the ShowWindow switch

Get-Help Get-ChildItem -ShowWindow

which will open up the help in a separate window which can be useful when you are writing a script and it is searchable

04 - gethelp showwindo

But Chrissy prefers to use the -Detailed switch

Get-Help Get-ChildItem -Detailed

You can decide which way you like best.

Now you know how to find a command and hot to find out how to use it. If you are using VS Code (or ISE) you have access to Intellisense and snippets to help you with the syntax. You can run a command and get some results.

HOMEWORK – Use Get-Help to understand how to use commands. ( Extra merit marks for teachers pets who read the topics they find using Get-Help About_*) Also, instant demerit points throughout the course (your career) for students who do not use Get-Help when using any command for the first time.

Hopefully you are confident enough to have run some commands. If you are worried about breaking things only run the commands which start with Get- as these should only be returning information or objects and not changing anything.

This isn’t cmd 

This isn’t cmd. What you are getting back is not text.

It is an object.

Unless you choose to use one of the formatting commands or the command you are using outputs using a formatting command (pro-tip don’t do that) then what you will receive as the results of your command is an object.

This means that there is more that you can do with the results than just have them in text format. You want Text File,JSON, XML, CSV, Excel output or to write to a database? Look here for ideas

Objects,Objects,Objects

Objects are awesome. Lets start with a SQL Server Instance object to show you how to explore objects. You will need to install the most useful PowerShell module you will find if you are working with SQL Server – dbatools

Disclosure – I am a contributor to the open source module dbatools. I am presenting a full day workshop in Singapore for the PowerShell Conference Asia in October. I am also proctoring at Chrissy and CK’s PASS Summit in Seattle. I highly recommend that you come and join us at one of those events if you can to further learn how you can use PowerShell to administer SQL Server

You can install it from the PowerShell Gallery using

Install-Module dbatools -Scope CurrentUser

You will be asked if you want to trust the repository, say Yes. If this doesn’t work on your machine (you are on an older version of PowerShell) follow the instructions on this page.  Or ask for help in #powershellhelp in Slack

Now that the module is installed we can create a SQL Server Instance object by assigning the results of a command to a variable.

A variable in PowerShell is designated by a $.

I suggest that you get used to following this step as well. This is a great way of exploring the results of any command and understanding what you can do with it.

We will use the Get-Member command. Another one that I use every single day.

Warning – You do not have to use these methods to accomplish tasks in PowerShell against SQL Server as both Microsoft and the community have released modules which will enable you to general tasks without “coding”

This is an example to get you comfortable with PowerShell by using a target that you are comfortable with and can recognise.

## Create a smo object provide your host\instance after -SqlInstance 
$SMO = Connect-DbaSqlServer -SqlInstance ROB-XPS\sql2016

Now that we have an object we can explore it

$SMO

By just calling the variable it will display the default properties

06 - default properties.png

If you want to see what else is available you can use Get-Member You just pipe | to Get-Member

$SMO | Get-Member

This will show you all of the events, methods and properties available on the object.  Methods are really useful, enabling you to do things like start agent jobs or enumerate permissions or members

If you wish to see all of the processes that are running on the instance

$SMO.EnumProcesses() | Out-GridView

Out-GridView is a useful command allowing you to see the results of your command in a graphical format and easily filter using the search bar at the top.

07 - ogv.png

Other things you can do include reading the errorlog

$SMO.ReadErrorLog() | Out-GridView

08 - errorlog.png

Get the connections to a database

$smo.GetActiveDBConnectionCount('master')

Get the current Traceflags

$smo.EnumActiveCurrentSessionTraceFlags() 
$smo.EnumActiveGlobalTraceFlags()

The properties that are available at this level are similar to the view that you see in SSMS Object Explorer for the instance. You can see a databases folder in SSMS and you can expand it. You can do the same in PowerShell

$SMO.Databases.Name

That is going to just show you the names of the databases on that instance. You can do the same thing using the pipe | again and Select-Object

$Smo.Databases | Select-Object Name

This will pass the Databases array of objects “along the pipeline” and then you can use Select-Object to choose them. Multiple properties can be chosen with commas.

$smo.Databases|Select-Object Name, Collation, CreateDate, Owner, version

09 - databases.png

PowerShell will let you select things that do not exist so for example

$smo.Databases | Select Name, NumberofBeards, Owner, MoustacheLength,one

does not return an error but some of the columns are empty 🙂 You need to use the names of the properties returned from Get-Member in your Select statement (Hmm, sounds a bit like T-SQL)

18 select.png

You can look at a single database default properties using

$SMO.Database['NAMEOFDATABASE']

You can then use Get-Member to explore deeper and deeper.

You can also look at the Agent from the instance level object

$SMO.JobServer

HOMEWORK – Using the code above explore the $SMO object, look at the databases, tables, the columns and the indexes. Explore other properties and select them. (Extra merit points for exploring the SQL Server like a file system using the SQLServer drive. Start with cd SQLSERVER:\ and use Get-ChildItem or its alias dir or ls)

Warning Reminder – You do not have to use these methods to accomplish tasks in PowerShell against SQL Server as both Microsoft and the community have released modules which will enable you to do general tasks without “coding”

But this is a very useful way of understanding and exploring using PowerShell with a type of object that you understand. You will use these methods all of the time and when you need to accomplish the next thing you have the tools. Already. Now.

Recap

You should take from this for the future

  • Get-Command *search* – Find a Command
  • Get-Help NameOfCommand – How do I use the Command ?
  • $var = Some Command here – Set the output of a command to a variable
  • $var | Get-Member – What Events, Methods, Properties do I have on this object
  • $var | Select Property1, Property2, Property3

That’s All Code – Where’s The Easy Buttons ?

There are two answers to this.

Firstly, yes it’s all code, but you will find that code is the future. Every time you run the same piece of code it will do the same thing, every time a junior DBA follows the steps to manually do something in a GUI like SSMS they may make a mistake and a different mistake each time. As DBAs we use stored procedures, views, functions and more, they are all code. Using code is vital for automation, for making things easy, for reducing the risk of mistakes, for getting rid of the mundane.

It is also required for DevopsAgileScrumContinuousIntegrationDeliveryWhatsTheNextBuzzWord which is all the rage (and also a lot of fun with interesting challenges)

As estates get bigger and bigger the GUI becomes less useful to you.

Secondly, remember my warning at the top of this post? There is no easy button. You will have to learn new things you will make mistakes and get errors, you need to ask for help

I Have An ERROR !

Don’t be disheartened, errors happen. The red text looks scary and frightening but its not.

Read the Error

Lets use Microsofts sqlserver module. There are all sorts of ways to get it. If you have SSMS you probably have it already. Lets check

Get-Module sqlserver

A result like this means that you have it

10 - get-module.png

If not

Install-Module sqlserver -Scope CurrentUser

Again you will be asked if you want to trust the repository, say yes!

If I run

Get-SqlLogin -ServerInstance rob-xps

I get

11 - error.png

That error is fairly easy to understand. I can’t connect to an instance that isn’t running

If I run

Get-SqlLogin -ServerInstance rob-xps\sql2016 -LoginName rob

I get a different error

12 sql login error.png

The Get-SqlLogin from Microsofts sqlserver module returns an error if it cannot find the specified login name. The important part of this error message is ObjectNotFound. That tells us what has happened

If we look at the logins

Get-SqlLogin -ServerInstance rob-xps\sql2016 | Select Name

13 - Logins.png

and choose one that exists everything is good

14 one login.png

HOMEWORK – Here is another object to explore with Get-Member. Explore the Login object

For example

15 - login script.png

But back to errors. Sometimes it isn’t quite so obvious. I have altered my Create-Database function so that it shows an error instead of a helpful message.

16 - db error.png

This is an example of the sort of error message that you might not be able to decipher so easily. Even if you read the message it says Exception calling .ctor with 2 arguments. How can you, as a new PowerShell user work out what has happened?

Objects

Remember, PowerShell is great because of objects? The errors are objects too. Lets have a look at the error in full. This is another piece of code that I still use every single day

$error[0] | fl -force

The $error variable holds an array of error objects for that session. The [0] targets the 1st object in the array which is the latest. We pipe that to fl which is an alias for Format-List. Normally I am very clear about not using aliases in my blog posts as I believe that you should not use aliases in your scripts and functions (This is due to a bad experience trying to google for % to find out what it was in PowerShell many years ago). However, you are only going to use this at the command line so its ok.

The results are

17 error.png

Once you read the error, you will see that there is a generic connection failed error message which you will be used to seeing and now you can go about fixing it.

Errors aren’t scary. You just have to read them. Sometimes you have to expand them to read them. Once you have read them don’t forget that there is also a lot of help out there on the internet. I recommend the #powershellhelp channel in the slack but with the full error message you will be able to get some useful results from whatever your choice of helpful solutions is.

That’s exactly 3000 words – which is slightly (!) more than I intended to write and a lot to read, so if you have got all the way down here. Congratulations, when you see me at Summit or at a SQL Saturday come up and tell me and I will give you a special sticker 🙂

Hopefully this has given you some guidance to starting to use PowerShell with SQL and how you can help yourself.

Good luck.

TSQL2sday #94 Lets get all Posh!

Posted on by 
Write-Output "What are you going to automate today?"

 

Welcome to T-SQL Tuesday for September 2017!

tsql2sdayT-SQL Tuesday is a chance for you to join in the SQL Server community and write a blog post on a suggested topic. It makes for a great way to find a bunch of blog posts showing the same subject from many different viewpoints. Please join in and write a blog post, maybe it’s your first ever, maybe you haven’t blogged for a while but even if you blog every day come and join the party and share your knowledge.

To participate:

  1. Write a post on the topic below
  2. Schedule the post to go live on Tuesday, September 12th (between zero am and midnight, UTC)
  3. Include the TSQL Tuesday logo in the top of your post
  4. Link the post back to this one (it’s easier if you comment on this post and link it)
  5. Optional: Tweet a link to your post using the #tsql2sday hash tag on Twitter

Extra credit: if you’d like to host your own TSQL Tuesday in the future, read the full rules for info on how to sign up. Just like I did but don’t forget its your month!!

This month’s topic: Let’s get all Posh – What are you going to automate today?

PowerShellIt is no surprise to those that know me that I will choose PowerShell as the topic for this month. I am passionate about PowerShell because it has enabled me to have the career I have today and to visit numerous countries all around the world, meet people and talk about PowerShell. By my reckoning searching the TSQL Tuesday website it has been over 3 years since we had a topic specific to PowerShell. So I would like you to blog about PowerShell and SQL Server (or other interesting data platform products)

If you don’t know or use PowerShell GREAT! That’s awesome.

Please spend an hour or so with it and tell us how you got on and what and how you learned. Just like Erik and Brent did. You could install one of the community modules like dbatools, dbareports , SQLDiagAPI  or the Microsoft ones sqlserver or SSRS and try them out and tell us what you learned.

If you want help whilst doing this please make use of the #powershellhelp channel in the SQL Server Community Slack

This will be of so much benefit to all people who don’t use PowerShell and want to start to learn about it.

If you do use PowerShell and SQL then either tell the tale of the best thing you have automated or a beginners post to show people how to start using PowerShell. I have heard many stories and am looking forward to tales of

  • testing backups
  • doing migrations
  • resetting log shipping
  • creating things in the cloud and on premises
  • SQL on Linux with PowerShell on Linux
  • using Pester for testing
  • automating manual tasks
  • automating incident knowledge gathering
  • continuous integration and delivery

and many more. I will read all of them and do a write up of them later next week.

Invoke-Coffee

Start-BlogWriting -Title 'Cool PowerShell Post'

Get-BlogProofRead

Post-Blog -Date ‘September 12th 2017’ -Title 'Cool PowerShell Post'

Write-Tweet -Hashtag ‘TSQL2sday’ -Message 'This is my cool blogpost'

 

keep calm and powershell.jpg

I Hate Interviews – TSQL2sDay

Posted on by

tsql2sday

This month’s T-SQL Tuesday is hosted by Kendra Little and is on the topic of interviews 

I hate interviews as an interviewee. I have had many memorable experiences with them. Even writing this blog post has been challenging as I relive some of them. I haven’t shared a lot of the worst ones!

When I was a lad

My first interview was for a waiter/washer up at a local country pub aged 15 or so. I stumbled and stammered and stuttered my way through and I think it was only because they needed someone that evening that I got the job.

When I was 17 I wanted a car and whilst doing my A-Levels I got a job at a local private school to achieve this. It was about a 10 minute cycle from my college to the school and I was so nervous that about half-way I was incapable of riding my bike and had to walk. I then was faced with two people interviewing me which I was not expecting or prepared for. I remember nothing of the interview other than leaving it soaking in sweat.

Nerves

As a young man I interviewed for numerous jobs and things got progressively worse for me. I would get so incredibly nervous. I could not sleep or eat before an interview. This meant I often was feeling very nauseous and tired during an interview. I would arrive incredibly early and have to waste time wandering around, giving me more time to think about how nervous I was and, of course, making it worse. Obviously I wouldn’t give a good impression and didn’t get the jobs which meant more interviews and more nerves.

I tried many things, I sought advice and information from many sources and approached the situation in a number of different ways without much change to my internal responses.

Preparation, Practice, Knowledge and Distraction

To this day I hate interviews even after 20 years of having to do them. I can still get so overcome by nerves that I forget even the simplest and most obvious things such as what the N in DNS stands for or what the question is that I should be answering.

To minimise this, I try my best to prepare as well as I possibly can. I learn and revise the things I think I will need to show that I know by reading the job descriptions and adverts carefully.

I also split the whole process into separate boxes. Revising and researching a company and a job was one part. Getting ready and travelling and arriving was another and the actual interview was then just the last part consisting of talking to some people. This definitely reduced the overall stress and improved my performance in interviews.

A previous shop provided interviewer training and needed volunteers to be interviewed for those courses. I volunteered as often as I was able to and treated them as realistically as was feasible. This helped me a lot and also enabled me (sometimes) to view an interview as just a chat. If you suffer with nerves and this is available I would recommend doing so. If not, ask someone who interviews for some practice interviews and treat them as realistically as you feel is necessary.

A wise person told me to remember that interviewers are people too and also that good interviewers will recognise nerves and assist the interviewee. After all, they are trying to find the right person to fulfil their needs and want to know if you meet their requirements for that position.

Another wise person told me, during an interview, that it was ok to ask for clarification about a question. A decade or more of interviews before I knew that. It enables me to pull back from a spiral of nerves making me gabble and to be able to return to the question required. When I find that I am rambling in my answer or that the answer has disappeared from my mind I ask the interviewer for clarification and get some much needed breathing space.

To reduce the impact of nervousness before the interview I learnt to distract myself in the couple of hours prior to an interview. I have been known to go and see a film if a cinema is close to the interview or do the weekly shopping. Anything that can occupy my mind without risking me being late. This may be of no use to many people but it works for me.

The other side of the table

As an interviewer, I hope that I recognise when people are nervous and am able to assist them and also coax out the information that I need to be able to make the best decision about the candidate for the position.

Interviewing is tiring.

When I worked in secure units we would sometimes spend 2 continuous days interviewing. It is hard work. You need to look after yourself in these situations. It is important to drink plenty of water, to ensure that you eat and at least get up and stretch in between interviews. The very last person you interview might be the perfect candidate don’t miss that because you have switched off.

You are being interviewed too

The person that you are interviewing is also interviewing you. They are considering if they want to come and work for your company with the people they meet. That might only be the people in the interview so it is important I think to ensure that you make a good impression as well. During a day of interviewing many candidates try to reset before each person.

Being courteous, attentive and professional is important during the interview even if the interviewers recognise that the person is not suitable for that role within 2 minutes. They may be ideal for another position or you may come across them later in your career. Leave a good impression.

Preparation

A shop I worked at employed a new DBA who had impressed the manager in interview with their knowledge as they had passed a lot of exams. The manager was very pleased and looking forward to the new arrival. This changed quite quickly when it became obvious that the new DBA was missing some basic knowledge about installing SQL Server and creating new databases which was a significant part of their role. A lot of time was wasted by the other DBAs in the team re-doing and re-checking the work that this person had done and team dynamics went downhill very quickly (although I did learn a lot about Policy Based Management from this experience!)

When I was working in secure units focusing on people with Autism we knew that communication skills both verbal and none-verbal were vital to all members of our team. We had an excellent set of questions and scenarios early in the interview to establish peoples capabilities in these areas and this allowed us to close off interviews early when we could see that the candidate did not meet our requirements as well as ensuring we employed people with the right skills for a very challenging workplace.

Before the interviews for a replacement DBA the manager asked how to avoid a repeat of that situation. I described the situation above and as a team we identified the basic skills, knowledge and approaches that we wanted in our future team members and designed a set of questions and scenarios so that candidates could demonstrate them. This was excellent for ensuring the entire team felt that they had some input into the recruiting process and also added confidence in the new team member. I think it was an excellent piece of team management as well.

The biggest take away from this post, I hope, is preparation. For both sides of the table preparation is a vital part of any interview process. Also if you see me all dressed up and in the queue for a film I am probably very nervous and won’t want to chat!!

Make sure that you go and visit the round-up post that Kendra posts on her blog to read further posts on the interviewing process from others in the SQL Community. You can also find all the archives at http://tsqltuesday.com/

 

 

 

Any resemblance to any living people in this post apart from myself is complete co-incidence

 

 

 

 

Speaking? You? Go on. #tsql2sday #84

Posted on by

This is a blog post for this month’s T-SQL Tuesday post, hosted by Andy Yun (b|t). T-SQL Tuesday is a monthly blog event started by Adam Machanic (b|t). The T-SQL Tuesday topic this month was about advice for new speakers. Thanks Andy for hosting. I have created a channel in the SQL Server Community Slack for presenting which everyone can make use of to ask and to answer questions

I think you should share what you know with others.

You will be amazing.

I will give you some great advice I learnt from a fantastic person’s blog post

  1. Start speaking
  2. Keep going
  3. Listen to feedback
  4. That’s it.

Kendra has said it all, you don’t need to read any further 😉

 

 

 

 

However..

Not all plain sailing

I love giving sessions but I never knew or thought that I would. My journey to speaking started at my SQL user group in Exeter and two fabulous people Jonathan and Annette Allen who encouraged me to share some PowerShell with the group. I was terrified, didn’t think I was worthy, my HDMI output wasn’t strong enough to power the projector, I had to transfer my slides and demo to Jonathans laptop. It was a fraught and frustrating experience.

My second presentation was done on Stuart Moores MacBook Pro using Office Online for presentations and Azure for demos. Again a change right at the last minute and using a machine I didn’t know (and a different keyboard set-up).

Stuff will go wrong. Murphy’s Law will always show his head somewhere and no matter how often you test and re-test your demos, sometimes an odd thing will make them stop working

There will be problems and issues, you can mitigate some of them by following the 6 P’s

Proper Preparation Prevents Pretty Poor Performance.

You can read some great blog posts in this T-SQL Tuesday Series and also this one from Steve Jones or any of these But also accept that these things happen and you must be prepared to shine on through the darkness if the power runs out or use pen and paper or even plastic cups like John Martin 🙂

You never know you might enjoy it

I found I enjoyed it and wanted to do more and since then I have presented sessions in a wide variety of places. It was very strange to have been sat watching and listening to all of these fantastic presenters thinking I could never do that and then find out that actually it is something that I enjoy doing and find fun. You can do that too.

Equally, it’s ok to not enjoy it, think its not worth the stress and hassle and support the community in a different way but at least give it a go

You will be nervous

quote-joan-jett-you-want-to-have-butterflies-in-your-185899

I shared a train across Germany with someone who had attended the PSMonday conference in Munich and they were astonished when I said that I get very nervous before speaking. It’s ok to be nervous, the trick is to make use of that nervous energy and turn it into something positive.

I get very nervous before presentations. My hands shake, I sweat, I either babble or loose my voice. I fret and fidget and check everything a thousandillion times. I find it is better for me if I am sat in the room during the previous presentation as that generally helps me to feel more relaxed as I can listen to their talk and also out of respect for the presenter and the organisation it forces me to sit quietly.

You will find your own way to deal with this, maybe listening to music on headphones or just sitting quietly somewhere. Don’t worry if it is not immediately obvious, try some different things, talk with others and believe me, it will be ok.

Don’t try to numb it with alcohol

Once I get up and its ‘my’ turn I take a few deep breaths and suddenly presenter turns on and I forget all about being nervous.

173101-everything-you-want-is-on-the-other-side-of-fear

Something to talk about

I have nothing to talk about.

Or everyone else knows more than I do.

Or X Y and Z talk about this much better than I do.

I’m scared

Richard Munn and I gave an impromptu session at SQL Relay in Cardiff where we talked about and hopefully encouraged people to start speaking and these statements came up.

wp_20161004_13_59_32_pro

Heres (a little of) what we said

No-one knows everything. Many people know a hell of a lot but not everything. You know a lot more than you realise and you also know things that no-one else does.

If you are stuck for things to talk about think about the you of 6 months or a year ago and something that you have learnt in that time and write the session that you wish you could have seen then. There will be other people at a similar stage who will appreciate it.

Don’t be scared, they are only people.

Practice

My dog is the one person who has been present at my presentations the most. He has listened (sometimes intently) to me practicing.

You need to practice speaking out loud.

You need to understand the timings

You need to be comfortable with hearing yourself speaking out aloud

You need to practice speaking out loud

A double reminder because I think it is important. You should practice and practice and practice with an eye on your timings and if you have a good friend who is technical or a small group at work for a lunchtime maybe then ask them if they will listen and give feedback.

Wanna chat?

I am very passionate about community involvement and lucky enough to be involved in two fantastic communities – the SQL community and the PowerShell community and have made some great friends along the way. I was amazed and proud when very soon after my second presentation someone told me that I had inspired them to start to present.

Since then I have gone out of my way to encourage other people to speak and to blog and am really enjoying watching them blossom. If you want to have a chat via email or via slack about speaking or blogging or getting involved in the community please feel free to contact me and I promise you I will get back to you. Better still go to the SQL Community Slack and ask questions in #presentingorspeaking

Go find out more

We are good at sharing and learning technical content but we can share and learn about so much more, about all aspects of our life. Go and read all of the other posts in this T-SQL Tuesday for starters 🙂 and develop

Enterprise Strategies – A #TSQL2sDay post

Posted on by

This months TSQL2sDay blog post party is hosted by Jen McCown and is about Enterprise Strategy.

Adam Mechanic started TSQL Tuesdays over 5 years ago and you will find many brilliant posts under that heading if you search for them

Managing SQL servers at enterprise scale is not a straightforward task. Your aim as a DBA should be to simplify it as much as possible and to automate everything that you possibly can. This post by John Sansom could have been written for this months party and I recommend that you read it.

So here are a few points that I think you should consider if you look after SQL in an Enterprise environment.

  • Enterprise Strategy will undoubtedly garner a whole host of excellent posts and Jen will provide a round up post which will I am certain will be an excellent resource. Take a look here
  • Know where your instances are and have a single place that you can reference them from. Some people recommend a Central Management Server but I find this too restrictive for my needs. I use an InstanceList table in my DBA Database with the following columns [ServerName], [InstanceName] , [Port] , [AG] , [Inactive] , [Environment] and [Location]. This enables me to target instances not just by name but by environment (Dev, Test, Pre-Prod, Live etc), by location or by joining the InstanceList table with another table I can target by the application or any number of other factors. I also capture information about the servers at windows and SQL level to this database so I can target the SQL 2012 servers specifically if need be or any other metric. This is very powerful and enables far greater flexibility than the CMS in my opinion.
  • Use PowerShell (no surprise I would mention this!) PowerShell is a brilliant tool for automation and I use it all of the time
  • Get used to using this piece of Powershell code
	 $Query = @"
	 SELECT [ServerName],[InstanceName],[Port]
	  FROM [DBADatabase].[dbo].[InstanceList]
	  Where Inactive = 0 AND NotContactable = 0
	"@
	try{
	$AlltheServers= Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $query
	$ServerNames = $AlltheServers| Select ServerName,InstanceName,Port
	}
	foreach ($ServerName in $ServerNames)
	{
	## $ServerName
	 $InstanceName =  $ServerName|Select InstanceName -ExpandProperty InstanceName
	 $Port = $ServerName| Select Port -ExpandProperty Port
	$ServerName = $ServerName|Select ServerName -ExpandProperty ServerName 
	 $Connection = $ServerName + '\' + $InstanceName + ',' + $Port
	
	 try
	 {
	 $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Connection

Notice the query variable above, this is where the power lies as it enables you to gather all the instances that you need for your task as described in the bullet post above. Once you get used to doing this you can do things like this identify all the instances with Remote DAC disabled using a query against the DBA Database and then enable it on all servers by adding this code to the loop above

$srv.RemoteDacEnabled = $true
$srv.alter()

Very quick very simple and very very powerful. You can also use this to run TSQL scripts against the instances you target but there are some added complications with Invoke-SQLCmd that you need to be aware of

  • BE CAREFUL. Test and understand and test before you run any script on a live system especially using a script like this which enables you to target ALL of your servers. You must definitely check that your $ServerNames array contains only the instances you need before you make any changes. You need to be ultra-cautious when it is possible to do great damage
  • Write scripts that are robust and handle errors gracefully. I use Jason Wasser @wasserja Write-Log function to write to a text file and wrap my commands in a try catch block.
  • Include comments in your scripts to assist either the future you or the folks in your position in 5 years time. I would also add one of my bug bears – Use the description block in Agent Jobs. The first place any DBA is going to go to when that job fails is to open the properties of the job. Please fill in that block so that anyone troubleshooting knows some information about what the job does or at the very least a link to some documentation about it
  • Finally in my list, don’t overdo the alerts. Alerting is vital for any DBA it is a brilliant way to ensure that you quickly know about any issues affecting your estate but all alerts should be actionable and in some cases you can automate the action that you can take but the message here is don’t send messages to the DBA team email for every single tiny thing or they will get swamped and ignore the vital one. This holds for whichever alerting or monitoring system that you use

This is but a small sub-section of things that you need to consider when responsible for a large SQL estate but if you need help and advice or just moral support and you don’t already interact with the SQL community then make today the day you start. Maybe this post by Thomas La Rock is a good place to start or your nearest User Group/Chapter or the #sqlfamily hashtag or give me a shout and I will gladly help.

Giving Back – #TSQL2sday

Posted on by

T-SQL Tuesday, which was started by Adam Machanic (blog|twitter) and is now starting its 6th year, is hosted by a different person each month. The host selects the theme, and then the blogging begins. Worldwide, on the second Tuesday of the month (all day, based on GMT time), bloggers attend this party by blogging about the theme. This month it is hosted by Wayne Sheffield blog|twitter and in the spirit of the holiday season it is about giving. This is my post on giving back, in the little ways as well as the bigger ones, how you can give back and why it not only benefits others but also yourself

What’s the SQL Family?

The SQL Family (or SQL community call it what you will) is a fabulous place to be. Full of many wonderful, talented, passionate and generous people. Every method of interaction that you wish for can be found if you look. Ever wanted to know how others do it? or Does this happen in other shops? or I wish I had access to someone who knows about ‘insert new shiny thing here’?

I guess that that is how I joined. I had no peers in my shop. I had no one to turn to to ask questions or get advice. I had no support and I turned to the internet. Now we all know that the internet lies. It is full of accidental and deliberate mistruths, of part information and downright bad advice. You have to be careful where you go and who you trust. I gradually found myself going back to the same resources and from those I found out about PASS and user groups

I am in the UK. I found the list of UK SQL User Groups

You can find more here both local and virtual

User Groups

I found a user group near me and went along to a meeting not knowing what to expect. I found a group of people like me willing to give up their time to learn and share knowledge. A wide range of people from DBAs, Developers, BI Professionals and SysAdmins. Working for International multi regional companies looking after many hundreds of SQL Servers to single sysadmins looking after the whole kit and caboodle and everything in between. A wealth and breadth of knowledge to tap into. You will learn so much not only from the sessions but also the conversation with all these other talented people

Come along.

Simply coming along will bring benefit. Other people will be interested in what you have to say even if you are in week 0 of your first ever job. Your view will still be valued. Everyone can learn from everybody and NO-ONE knows it all.

There will come a point where you will pass on a piece of knowledge or an idea or a way of working and someone will say thank you I didn’t know that. You just gave back. It may even be someone you look up to, someone whose knowledge and experience far outweighs yours whose word you hang on to. That feels good.

You may ask the questions that others thought but didnt ask and boy are they glad you asked the question. You just gave back. It’s something I do often. I ask questions and sometimes I ask questions I know the answer to so that they will be of benefit to the group.

What will you get? More than you can ever put in. Free training, often free pizza, knowledge, advice,guidance, contacts, support, a network of people in your field, notice of job openings, swag, fun, friends, more social events and more and more

The user groups are run by volunteers in their own time out of the goodness of their hearts. They will always need your help. Turn up 5 minutes earlier and help set out the chairs or put out the handouts or assist with the tech. You just gave back. Afterwards before going to the pub clear the tables, help carry the boxes, put the pizza evidence in the bin. You just gave back

SQL Saturdays and other community events

SQL Saturdays are held all over the world most every Saturday You can find more about them here https://www.sqlsaturday.com/ There are also other larger events such as SQL Bits and SQL Relay here in the UK. Everything I wrote about User groups counts here just in a slightly larger scale. You will be able to attend several sessions across many different areas for free on a Saturday

These events are also run by volunteers and they will also need your help. If you can spare some time to help on a registration desk you just gave back. A room monitor to ensure the speaker and delegates have everything they need, the room is tidy and the session runs to time. You just gave back. Putting things out and tidying them away again. You just gave back.

You can become a volunteer by asking the people organising the events if they would like your help. These events will all have twitter feeds and emails and facebook pages and many methods of getting in touch. Contact them and offer your help if you can. You just gave back.

If you fancy taking the next step then you can get involved in organising the events. This is hard work, great fun, a good thing to add to your CV and you just gave back. There are so many areas to get involved organising an event. Premises and technology, speakers and printers, volunteers and sponsors all need co-ordination. Websites,twitter feeds, feedback forms, posters, marketing materials all need designing and producing. There are so many ways in which you will be able to provide value to the event and you just gave back

Oh and whilst I am at it, when you attend an event

Say Thank You to the volunteers. You just gave back.

 Speaking and Blogging

All the events named above need speakers. The bigger events like the SQL Saturdays and the large events like SQL Bits will generally have more established speakers but every user group will need speakers and they will be more likely to accept new speakers and will be very supportive if you say that you are a new speaker. Every speaker had to make their first presentation at some point and they all know how it feels and can provide guidance and advice. You will feel that you don’t have anything to speak about that others will want to hear about. You do. Your experience and your knowledge or how you solved something or created something will be of interest to people. Of course, you need to check with the user group leaders and members if your idea for a presentation is suitable. Like anything you do that is new, researching it and taking advice from people with more experience is always useful. Maybe you can start with a lightning talk. Give it a go. You just gave back.

What do you get back from Speaking and Blogging?

I’ll tell you a secret. The vast majority of my posts ( This is an exception) are written for the benefit of one person. Me.

I write posts to record my learning for myself. To document my knowledge. I use my posts to help me to do my job. I know that I wrote the method of doing something somewhere and this is part of my store of knowledge. I write posts to answer peoples questions so that I have a place to point them to. Occasionally people will email me asking a question and if it requires a detailed response I will write a post and email them to tell them that this is the answer. I often point my work colleagues at my blog when they ask me questions about Azure or Powershell. You could also see your blog as an extension of your CV and use it when job hunting and develop it in that way

I also write posts to expand my knowledge and this is the same for speaking. When I am writing a blog post or a presentation I will read and watch videos and ensure I know more about it. The process of creating that content will improve my own knowledge and work practices and you will find that, as you write your blog posts you will have a deeper knowledge also. When you give your presentations you will learn as you answer questions or find the answer to the question afterwards (It’s ok to do that) that you are improving yourself and your knowledge.You will also be giving back.

Putting your information online will enable people to find it. Sure you can worry about SEO and getting to the top of search pages but you know that sometimes the answer is on the ninth page. What you write will be of benefit to others and by taking the time to post you will be giving back to the community

You can do one, many or all of those and you will be giving back. I hope you do

I will be giving back. You will find me at SQL Bits where I shall be room monitoring and volunteering.

You will find me at SQL Saturday Exeter. I am again one of the fabulous SQL South West team

who are again organising a SQL Saturday in Exeter in the UK on April 24th/25th 2015 You can find out more here http://sqlsouthwest.co.uk/sql-saturday-372/ 

You still have time, if you are quick, to submit a session to speak or present a pre-con at Exeter. Submissions close on 15th December and we would love to have yours

I shall carry on blogging and hopefully present at some user groups again this year. If you see me any where, come up and say hi to me. You just gave back