DBA Fundamentals December 2017

Damien would like to show you the most interested features you can find in SQL Server 2017. He will briefly talk about SQL Server on Linux (Security), how to use Python scripts, Adaptive query processing and many more.
Very demo heavy.

Warner Chaves ( blog | twitter )

“Azure SQL Data Warehouse for the SQL Server DBA”

2017-08-08, 11:00 – 12:00.

The power of cloud storage and compute power has made data warehousing possible for businesses of all sizes. What was once a large capital expenditure and multi-year implementation can now be deployed and ready to use within minutes and allow any organization to collect, query and discover insights from their structured data sources. With a full T-SQL interface and compatibility with the rest of the Microsoft data stack, Azure Data Warehouse can fit transparently into your business data strategy and leverage already existing and familiar development and management skills. In this session we will look at the main concepts of the Azure SQL Data Warehouse service, how it’s different than SQL Server and the advantages it provides to an on-premises solution.

David Klee ( blog | twitter )

“Linux for the SQL Administrator”

2017-08-22 , 11:00 – 12:00

I bet never in your career you thought SQL Server would run on anything other than Windows. Times are changing, and are you ready? SQL Server on Linux is right around the corner! What is Linux – really? Why run your mission-critical databases on an unfamiliar operating system? This introductory session will help seasoned SQL Server DBA understand the basics of Linux and how it is different from Windows, all the way from basic management to performance monitoring. By the end of this session, you will be able to launch your own Linux-based SQL Server instance and get hands-on with this paradigm shift to see if this new platform is right for your organization.

Other Items

We’re continuing work on getting a list of past presenters up on our website. It’s a slow, deliberate progress but it’s ongoing.

You can check it out here: Past Presenters

Apart from that, anything you want to get in touch about, hit us up on Slack or Twitter!

DBA Fundamentals July 2017

What’s On?

July is a pretty busy month for the DBA Fundamentals Virtual Group with 3 seperate sessions being made available for the SQL Community. As well as giving away discount codes to the PASS Summit.

If you haven’t considered going before, now may be the time to do so. Nearly every review of the summit has people saying that they consider it to be the start of their careers, which is pretty high praise!

Use our discount code VC15GBQ6 for $150.00 off the cost of PASS Summit; currently $1895.00 until the 23rd of July. With the discount code now it will be $1745.00.
Also if you use our code, you will be entered in a drawing for one winner to get a $500.00 Amazon Gift Card.

The next big date for the PASS Summit price is the 23rd of July as the cost goes up another $300-400 after that!

Sessions.

DevOPs and the DBA

Hamish Watson ( blog | twitter ), 11th July, 12:30 – 13:30 Brisbane (10th July, 02:30 – 03:30 UTC)

Register: dbafun.org

You may have heard the word “DevOps” and wondered whether it is just another buzzword and/or what it can do for you.

In this session I will demystify the concepts of DevOps and we will look at two aspects of DevOps – Continuous Integration & Continuous Delivery.

Continuous Integration is the practice in which software developers frequently integrate their work with that of other members of the development team. It also involves automating tests around the integrated work.

Continuous Delivery is the next step after Continuous Integration in the deployment pipeline and is the process of automating the deployment of software to test, staging, and production environments.

Database migrations/changes are an area that may not be typically automated or utilise Continuous Delivery.

Through the use of a comprehensive live demo to a running production database the audience will learn the benefits and how to implement Continuous Delivery in their database systems deployment pipeline.

Hamish Watson is a Systems Management Specialist with a passion for efficient application deployment using DevOps methodologies.

He has 19 years IT experience in managing large scale databases on JADE & SQL Server technologies. He has been managing SQL Server since SQL Server 2000 and pragmatic architectural design is his main focus at Jade Software.

Educating and helping others learn is a driver for Hamish and he is a PASS Chapter Leader, International speaker and a repeat guest lecturer at a local university. Follow him at @TheHybridDBA or at https://hybriddbablog.com

SQL Server Performance Tuning Made Easy

Pinal Dave ( blog | twitter ), 11th July, 11:00 – 12:00 (11th July, 16:00 – 17:00 UTC)

Register: dbafun.org

:SQL Server Performance Tuning is still a mystery to many. Quite often even an experienced SQL Server DBA, often gets confused as to how to figure out where to start with this entire process. In this module we are going to learn about how to get started with SQL Server Performance Tuning. We will go over some very important scripts which will help us to get started with the SQL Server Performance Tuning exercise. At the end of the session the author will share his three important scripts which he uses at his customer sites all the time.

Pinal Dave has been a part of the industry for more than eleven years. During his career he has worked both in India and the US, mostly working with SQL Server Technology – right from version 6.5 to its latest form. Pinal has worked on many performance tuning and optimization projects for high transactional systems. He received his Master of Science from the University of Southern California and a Bachelors of Engineering from Gujarat University. Additionally, he holds many Microsoft certificates. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet and countless user groups.

Pinal writes frequently writes on his blog http://blog.sqlauthority.com on various subjects regarding SQL Server technology and Business Intelligence. His passion for the community drives him to share his training and knowledge. His previous experience includes Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his outstanding community service and evangelizing SQL Server technology. He was also awarded the Community Impact Award – Individual Contributor.

Extending DevOps to SQL Server

Grant Fritchey ( blog | twitter ), 18th July, 11:00 – 12:00 (18th July, 16:00 – 17:00 UTC)

Register: dbafun.org

Most organizations are under pressure to speed up the software delivery cycle, whether that’s to respond more quickly to the needs of the business, the needs of your customers or just to keep up with the competition. Unfortunately the database is commonly considered a bottleneck. Without the right processes in place, database change management can slow things down, adding risk, uncertainty, and getting in the way of development and operations working together to deliver. Any organization that wants to fully benefit from a DevOps approach is going to have to overcome some specific challenges presented by the database. This session will teach you how to take DevOps principles and practices and apply them to SQL Server so that you can speed up the database delivery cycle at the same time you protect the information contained within.

Grant Fritchey, Microsoft Data Platform MVP, has more than twenty years’ experience in IT. That time was spent in technical support, development and database administration. Grant currently works as a Product Evangelist at Red Gate Software. Grant writes articles for publication at SQL Server Central and Simple-Talk. He has published several books including, “SQL Server Execution Plans” and “SQL Server Query Performance Tuning.” Grant Fritchey currently serves on the Board of Directors of the PASS organization, the leading source of educational content and training on the Microsoft Data Platform, as the Executive Vice President in charge of governance and finance. Grant teaches and presents at events, large and small, all over the world.

Don’t Forget!

Any questions, hit us up on Slack (joining instructions here 🙂 ), or Twitter

Chaos Theory, Compound Effects, and Consequences.

Straight away I want to apologise for the Nicolas Cage memes!

User Groups are great, aren’t they?

I just got back from the Reading User Group and I’m still in that post “User Group Glow”, also known as “Long Day Lethargy”, or “Twelve Hour Tiredness”.

They are great though! A chance to talk to other people in the SQL Server community, – a slight reminder that even if you work alone, people are still experiencing some of the same problems that you are (apparently everyone has to deal with multiple nested views, who knew!) – a chance to hear presentations on different topics, and pizza if you’re lucky (we were).

I realised during the session that the two presentations given during the User Group had a connection with a small issue with a table change I had been given with a developer.

Here’s what did not happen to me so you can watch out for it.

The Chaos Theory

Raul Gonzalez ( blog | twitter ) was first up with this presentation “Database Design Matters, Seriously”, showing us the chaos that can occur from not giving some serious thought into how you design your database.

His session is not yet up on his blog as I’m writing this but it will be soon so keep an eye out for that!

Now he had a lot of good points but, for brevity’s sake, the main chaos theory points here are what happens if you don’t take advantage of CHECK CONSTRAINTS, FOREIGN KEY CONSTRAINTS, and not specifying a columns NULLABILITY (yes, that’s a word!). SQL Server is a powerful program with many performance optimizations provided for you, but it’s not omniscient; it can only use the information that you give it!

His points on NULLABILITY (I mean, I think it’s a word) tied in nicely with the next presentation…

Compound Effects

David Morrison ( blog | twitter ) followed up with his presentation on “Query Plan Deep Dives” (I had seen this at SQL Bits, but it’s a great session so I had no problems watching it again) and, as an aside, through his presentation he showed us the compound effects that can happen from not specifying a columns NULLABILITY (it’s got letters so it’s word-like…)

Now his slides and scripts are up on his blog and they do a great job of walking you through them so check them out and you’ll see the compound effects they create!

Here’s a little teaser…


-- now I want all people who's email isn't in the email table
SELECT /*C.FirstName ,
    C.LastName ,*/
    C.EmailAddress
FROM dbo.Contact AS C
WHERE C.EmailAddress NOT IN (SELECT E.EmailAddress
                             FROM dbo.Emails AS E)

GO

Consequences

Which brings us back around to consequences or as I like to put it “How I Pissed Off A Dev By Refusing A Simple Request”.

To be quite honest, it was a simple request. A requirement came in to expand a column datatype up to varchar(100), so one of devs wrote up a simple script and passed it onto the DBAs to check as part of the change control procedure.

ALTER TABLE tablename
ALTER COLUMN columnname varchar(100)

And I said no.

“WHY???!!!“, you may shout at me (he certainly did), but I’m going to say to you what I said to him. “Give me a chance to explain before you take my head off, alright?”

While there is nothing wrong with the above code syntactically (is that a word?) but I couldn’t approve it since that column was originally NOT NULL and the above script would have stripped the column of that attribute! Business requirements dictated that it should not allow NULLS, and hey, who are we to argue with that 😐

Double checking to see if the column is NULL or NOT NULL allowed me to see a problem with that code, one that many people would consider simple enough to just allow it through at a quick glance. Which could have opened up problems further down the line if it had run…

Thanks to the User Group, I now know that it could have a knock on effect with our query plans as well!

ALTER TABLE tablename
ALTER COLUMN columnname varchar(100) NOT NULL

There, that’s better!

DBAs deal with databases and consequences

DBAs get a lot of stick sometime, the “Default Blame Acceptors” or the “Don’t Bother Asking” but a lot of the time, it’s not that we want to say no, it’s just that we have to take into consideration a thousand little things that could snowball into 1 giant problem.

With the rise of DevOps, check out the latest T-SQL Tuesday, DBAs have gone from going

“No”

to somewhere along the lines of

“Not this second, let me check it out and see what we can do”

If pressed further, we may rely on the good, old “it depends” though. Hey, clichés are there for a reason; they work!

It just goes to show that, like the IT profession, DBAs are constantly evolving.
Continuosly learning, checking out new helping technologies, and going to User Groups are going to help us to deal with it.

Just remember, in the end,

P.S. I should probably mention that the Nicolas Cage memes are because of this blog post by Nate Johnson ( blog ) that I enjoyed so much that I had to do something in response. I’m not normally this crazy, I swear!

Why I try to help with dbatools?

Can I get a couple more hours in each day please?

This post started after I created a function for dbatools, was resurrected when talking to Chrissy LeMaire ( blog | twitter ) then died down again afterwards. At this stage, I figure I publish it now or I’ll never finish it.

2 fricking hours…

I’m a Junior DBA, and as one, I get given the graft work.

For me that meant manually checking the backups. Every single file of every single database of every single server, every single day…plus whatever other jobs and alerts had come in overnight.

As you can imagine, it took a while (OVER 2 HOURS!!!) and since my youth, I had leveled-up from ‘laziness’ to ‘efficiency’, so I wanted a better option.

I had heard about PowerShell as a language before and wanted to check out if it was possible to use it to help me out.

So I opened up my PowerShell ISE, rested my fingers on the keyboard and…nothing.

So I checked out solutions online, and it was there that I found dbatools.io!

They had everything – or what I thought was everything since they have an issues page in github with over 100 items – so one Friday night I downloaded their tools at home and started getting familiar with them. (I know, rock star lifestyle that I have).

Monday morning, bright-eyed, bushy-tailed and filled with coffee, I sit down at my computer, open up PowerShell to start my graft work, and in 2 minutes I was finished.

2 fricking minutes…

With that, I was hooked; Twitter account, followed, Slack channel, signed in, anything and everything I could do to learn more about this wonderful life-saving (I figure time is life ergo this was life-saving) tool I was in!

But it wasn’t enough… they had given to me and I had no method to repay them.

Then one day, I asked a question on PowerShell help and one of their members Constantine Kokkinos ( blog | twitter ) helped me, and we got chatting.

He gave me an enhancement request to look at and I spent 3 days looking, poking and prodding it until finally I gave up and did a replace to fix it.

I then proceeded to try and push my entire computer into their Git repository but CK laughed and help me fix that too.

Then, from nowhere from my point of view, Chrissy LeMaire said she like it and, like that, it was in!

I’ve done more stuff since then, one more enhancement that wasn’t accepted (no worries) and a command that was accepted (that I am ashamed about since I think it’s not good enough), but I am constantly thankful for the work that they do and the knowledge that they impart.

2 commits later…

I know, I know, here I am trying to wax lyrical about dbatools when I’ve only done 2 commits.

What can I say, I’ve slowly gotten busier and busier to the point that I’m trying to schedule my days to fit everything in (if you had told me earlier that I would become a “not enough hours in the day” guy…).

Does this mean that my love for dbatools has weaned? Not in the slightest! I’m still impressed every single time that I look in (seeing as that is every day, I’m spending a lot of my time being impressed) and I’m still trying to get back to it.

2 things left to say…

To anyone hesitant about getting started with dbatools, whether that is helping out or using them, I urge you not to be.
They are welcoming, warm, and inviting people who are happy to receive help from anyone willing to give it.
I’ll eventually get around to fixing that issue Chrissy, I swear 🙁

Why You May Need More Than FOR XML PATH(”)

XML – both easy, easy, lemon easy and difficult, difficult, lemon difficult…

Working on a blog post and I came up against a problem that I had heard of before but did not spend much brain-CPU power against.

I know I’m going to run into this again so let’s document this for future me. Oh he’s going to appreciate this so much!

Commas are all the rage nowadays:

There are a fair number of questions nowadays about returning data from a database in a comma separated string. Sure the application should probably do that but hey, database servers are expensive, why not get some bang for your bucks!

Done!-ish…

SQL Server 2017 has this lovely function called STRING_AGG()

Pop in your column and your separator and it takes care of it for you!

…wait not everyone has SQL Server 2017 yet?

…wait I don’t have SQL Server 2017 yet? Oh, I should really fix that…

Pre-SQL Server 2017:

So what can we do if we are not on SQL Server 2017? Take the advice that I was given for most of my life and STUFF it!

The STUFFing:

Our playground:

USE tempdb;

-- Test table
SELECT dt.comments
FROM ( VALUES ( 'XML is the bomb!'),
              ( '&amp; JSON is cool too...')
     ) AS dt (comments);

Plain Old STUFFing:

I’m not the biggest fan of stuffing if I’m honest…tastes like dirt to me but hey, it works in 99% of situations…

SELECT STUFF((SELECT ', ' + dt.comments
              FROM ( VALUES ( 'XML is the bomb!'),
                            ( '&amp; JSON is cool too...')
                   ) AS dt (comments)
              FOR XML PATH('')
             ), 1, 1, '') AS CommentsEnXML;

StandardWay — …that’s…that’s not what I said!

Bacon Sausage STUFFing however:

So…SQL Server is trying to be too helpful. What do we do? I normally turn to the SQL community and people like Rob Farley ( blog | twitter ), who has a lovely post about this.

So let’s try it out.

SELECT STUFF((SELECT ', ' + dt.comments
              FROM ( VALUES ( 'XML is the bomb!'),
                            ( '&amp; JSON is cool too...')
                   ) AS dt (comments)
              FOR XML PATH(''),
              TYPE).value('.', 'varchar(max)'
             ), 1, 1, '') AS CommentsEnXML;

BetterWay — That exclamation mark is annoying me >:(

I just eat the turkey around the STUFFing:

Little hiccup in preparing for my next post. Thankfully I learn from my mistakes and failures (there’d be no help for me otherwise!).

I’ll leave this post with a quote from the blog of the main man himself:

It’s a habit I need to use more often.

Yeah, me too Rob, me too…

T-SQL Tuesday #89 – The times they are a-changing: Inline Out-Sourcing.

It’s T-SQL Tuesday time! T-SQL Tuesday time! T-SQL Tuesday, T-SQL Tuesday, T-SQL Tuesday and a baseball bat!
Ahem…sorry about that…

Koen Verbeeck ( blog | twitter ) is hosting this month’s T-SQL Tuesday experience, and wow does he really ask us to search deep within ourselves and ponder our future…

Technology has changed a lot in the past years, especially with cloud/globalization/automation. What an impact has this had on your job? Do you feel endangered? Or do you have more exciting features/toys to work with? Do you embrace the change and learn new skills? Or do you hide in your cubicle and fear the robot uprising?

Now my knowledge of these subjects are somewhat limited; I have yet to research the cloud beyond a rudimentary “poking it with a stick” so I leave that topic to people like Arun Sirpal ( blog | twitter ) and others.

Globalization is another new topic for me. I consider it to be along the lines of being able to fill in positions with people all around the globe (but I’m probably wrong with this). There is a veritable plethora of people online that are excelling at this, so I leave it up to you to seek them out.

Automation…well I have some dealings of this, so I was considering this to be my topic. Then I realised that I could write about something along the same lines. Out-sourcing, but not in the conventional sense. More like…

Inline Out-sourcing.

Start-Transcript.

There’s never enough hours in the day for everything I need to do!

How many times have we heard a complaint similar to that? Especially now-a-days when DBAs are tasked to look after more and more servers and instances. I cannot remember the last time I heard of a DBA taking care of servers in the single digits.

The work of the DBA keeps increasing but the amount of time that we have remains the same. How do we combat this? How do we make it so we are not sprinting just to keep up?

The only answer I have to this problem is this.

Don’t try to re-invent the wheel…let someone else do it.

SQL Community.

The SQL community has been hard at work creating awesome content for everyone to use.

A short list of the top of my head include:

This doesn’t include the tools available from companies like

And have you seen some of the scripts created by individual members of the SQL community?

Similar Tables? No problem for Jana Sattainathan ( blog | twitter )!
Check FreeSpace, Mike Fal ( blog | twitter ) has you covered!
Heck, even the MS SQL Tiger Team ( blog | twitter ) has tools out for you now!

Hmm, maybe this won’t be as short a list as I thought…

Brent Ozar Unlimited ( blog | twitter ) has got the First Responders kit,
Glenn Berry ( blog | twitter ) has his DMV Queries (with video), and
Paul Randal ( blog | twitter ) has his Wait Library, in conjunction to his Wait Script!

You know what this blog post needs? More links!

Andy Yun ( blog | twitter ) has got sp_helpExpandView.
Kimberly Tripp ( blog | twitter ) has got sp_helpIndex, and
Adam Machanic ( blog | twitter ) has got sp_WhoIsActive!

Can I stop now? NO!!! MOAR LINKS!!!

Kenneth Fisher ( blog | twitter ) has got SrvPermissions & DBPermissions.
Raul Gonzalez ( blog | twitter ) has got sqlg_securityAuditServerLogins, and
Ola Hallengren ( blog | twitter ) has got, what the community calls, the “Ola Hallengren scripts“!

And with Microsoft’s advancements with SQL Server and PowerShell, porting it to work with Linux and with Azure, it is like Microsoft are actively trying to get you to automate; actively trying to make your life easier!

Stop-Transcript.

So yes, technology has changed but we, as a SQL community, have worked so that you can use these tools, automate away the drudgery, and embrace these changes.

As long as you embrace this “inline out-sourcing” that we have.

Now I could wax lyrical about all the different aspects of the community and how, with these resources, you shouldn’t need to re-invent the wheel but I’m not going to.

These people have poured hours of effort into these scripts all for you to use. They have poured their heart, souls, and RAM into these scripts just so that they can help out the community.

I would ask a favour of people reading this post though. For all the time, effort, and sacrifice that these people have put in…

Thank them.

It means more than you know.

P.S. Andy Mallon ( blog | twitter ) has a beard…he may win this blog post…

DBA Fundamentals Social Media

This is going to be a short message but since it counts as my first SQL Family post, rather than a SQL or PowerShell one, I felt it still deserved a little post on its own.

DBA Fundamentals.

I’m helping out with the DBA Fundamentals Virtual Group’s social media presence (I told you it was going to be short 🙂 ).

What’s the Full Story Smartass?

What happened?

‘”What Happened” with Query Store’, actually?

At the start of February, I was watching a YouTube recording of that webinar by the DBA Fundamentals Virtual Group on their YouTube channel. Since my normal commute times and the normal times that webinars are on clash so often, it’s how I normally have to watch them.
During the webinar Steve Cantrell ( t ), the host and Group Leader, mentioned that they were looking for someone to volunteer with helping them out with their social media.

Why?

Well, I had steadily become more and more…addicted, for want of a better word, to the various social media outlets out there, like Slack, Twitter, YouTube, and LinkedIn. From these, I have gained so much knowledge, insight and joy from the different ways to connect to the SQL Family.

How?

I’ve been wanting a way to give back for all that I have learned so far so I took a chance and emailed Steve about the volunteer work, fully realising the very real possibility that someone who had watched the webinar in real time may have already offered and been accepted.

As you’ll find out with nearly all members of the SQL Community, there was no instant rejection. We emailed back and forth, Steve detailing who they are, what they currently do, and what they think they need to do better.

I replied with who I am, what I currently do, and what I thought I could help out with.
I think my suggestions were as long as that last line.

February 10th, expecting an apologetic yet negative reply to my last email, I got an email from Steve. He said he had talked it over with the Co-Group Leaders Mike Brumley ( t ) and Niraj Mehta, and then proceded to write to most eloquent piece of literature I have so far read.

You are in.

Why now?

I’m writing this now because I’ve had a month to try my hand in that arena, to give it a go, and see what it’s like.

I like it!

The Group Leaders have put a tremendous amount of effort into the webinars and secured well known names like Kimberly Tripp ( b | t ), Paul Randal ( b | t ), and more giving their expertise free of charge to all that attend.

Plus the cat was already out of the bag at this stage since it’s been mentioned in the pre-webinar slides.

What now?

From yourselves?

Enjoy the great content, hit us up with any questions or suggestions, and please forgive me if I ever come across as annoying on social media. I promise to try and not do that.

As for ourselves, it’s going to be pretty damn busy.

The Group Leaders are continuing to procure great talents for the webinars (believe me I’ve seen a sneak of what’s to come), we’re going to be updating the Virtual Group page to go with PASS’ new branding, and also try and get all avenues of social media to a standard where anyone can take pride in what they see.

How now brown cow?

Yeah so…a longer post than I expected to write…my bad.