Time to read: ~ 4 minutes
I pity people who have to write acceptance speeches…who do you leave out?
I pity people who have to write acceptance speeches…who do you leave out?
Time to read: ~ 4 minutes
I’ll be the one trying not to avoid eye contact and wanting to talk to everyone!
Time to read: ~ 1 minute
DBA Fundamentals VG has 3 different sessions ready for you all this August, with 2 sessions from our American Group and 1 from our down-under group.
If any of these sessions catch your eye, sign up here: DBA Fundamentals
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.
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.
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.
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!
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!
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 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.
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.
Straight away I want to apologise for the Nicolas Cage memes!
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.
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
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…
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
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 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
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!
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.
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.
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.
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.
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.
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!
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!
SQL Server 2017 has this lovely function called
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…
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
USE tempdb; -- Test table SELECT dt.comments FROM ( VALUES ( 'XML is the bomb!'), ( '& JSON is cool too...') ) AS dt (comments);
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!'), ( '& JSON is cool too...') ) AS dt (comments) FOR XML PATH('') ), 1, 1, '') AS CommentsEnXML;
So let’s try it out.
SELECT STUFF((SELECT ', ' + dt.comments FROM ( VALUES ( 'XML is the bomb!'), ( '& JSON is cool too...') ) AS dt (comments) FOR XML PATH(''), TYPE).value('.', 'varchar(max)' ), 1, 1, '') AS CommentsEnXML;
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…