I admire the array of learning materials that they have for different topics. I find it reassuring seeing the names of Pluralsight authors and recognising them independently as knowledge leaders in their field.
I even have a personal subscription since I was devastated when I moved jobs and was removed from the old jobs plan!
So, even though it’s been out for a while now, when I saw that they now have an extension for VS Code it was enough to make me install it on my machine.
I’m currently going through some C# Fundamentals by Scott Allen ( twitter | blog ) on Pluralsight with an aim to work my way into Entity Framework.
With the Pluralsight extension on VS Code, I can open up some sample code file and Pluralsight will recommend some clips based on the file
Clicking on one of those clips will open a new tab in VS Code like below:
Clicking the “Watch Clip” button will bring up a dialog from VS Code asking if you meant to go to an external website.
Opening or copying & going to the link will open up the Pluralsight video segment from the recommendations.
Insert Dad joke response here
Give it a go and see how it works for you. I use Azure Data Studio for my SQL code but I’m tempted to open up the .sql files in VS Code and see what Pluralsight recommends for them.
What do I think it will say? Who wrote this? Urgh! Kill it, kill it with fire!
If there was ever a phrase that brings about the dreaded Writer’s Block it would be Imposter Syndrome. I’m going to fall back to what I know I can talk about while the back part of my brain ruminates on what to write.
T-SQL Tuesday is the brainchild of Adam Machanic ( Twitter | Blog ). It is a monthly blogging call by the Data community. Each month a topic for the community is offered up and a blog post is written by all wishing to take part.
This month we have John Shaulis ( Twitter | Blog ) as the host and John has given the topic of Imposter Syndrome.
I want to read your stories about when you’ve experienced, seen, or overcome imposter syndrome!
I’ve gone through 2 cups of coffee staring at a blank page while waiting for my brain to dredge up some relatable Imposter Syndrome moment. It’s taken this long for me to realise that it’s a futile exercise. Imposter Syndrome is, by the definition that John has kindly given us, a personal experience.
One’s success […] result of one’s […] efforts or skills.
So, with a third cup of coffee in hand, and a deep refusal to believe that I have a caffeine dependency, allow me to tell you about the times that I feel the Imposter Syndrome the most.
Normal working days are not the days that I feel Imposter Syndrome.
Fielding questions at work doesn’t bother me.
There’s a sense of investigation, an aim to teach, and a feeling of joy that people are taking an active interest in SQL Server.
Investigating issues and researching requests don’t trigger it either.
There are set goals here; either fix the issue or can the request be done? Plus, there is an amazing community out there that can be called upon on Twitter, Slack, etc. for answers or guidance.
Unsurprisingly the days when I am the sole person interested in SQL Server are not the days when I feel Imposter Syndrome.
Surrounded but Isolated
Imposter Syndrome hits like a brick when I’m not alone. Conferences, User Groups, and meetups are the main culprits.
Probably stemming from being a Junior DBA going to my first User Group and realising the extent of the topics I didn’t even know that I didn’t know!
Trying to learn the basics of administrating databases and going to a meeting where people are arguing about single-thread CPU performance and NUMA nodes…
Talking to people who are dealing with columnstore partitioning problems and massive memory grants because they’re bulk-loading 3 trillion rows at once.
All the while I’m there thinking my main problem was telling developers “set-based = good, row-by-row = bad”.
The same is true for conferences.
Can’t Stop, Won’t Stop
Please don’t get me wrong. I love going to conferences, I do!
There are few other places where you can get curated knowledge to that extent! Where you can talk to people who get it when you say “Can you believe that they wanted sysadmin privileges just to take backups!?!”.
I love seeing what people are doing, what new technologies are coming up, and catching up with people who experience the same problems that I do.
And there’s no way I’m going to stop, the “fors” don’t just outweigh the “againsts”, they completely smother them!
But it fires off my Imposter Syndrome like nothing else does!
There are many items that I consider gifts. More than I can reasonably write down in a blog post. So, in the interest of brevity, I’m going to break it down into gifts of SQL past, the gifts of SQL present, and the gifts of SQL future.
Gifts of SQL Past
I have been gifted with being mentored by DBAs that were knowledgeable and passionate about the Data Community.
I have been gifted with mentors who encouraged me to go to User Groups, volunteer at conferences and soak up as much data learnings as I could.
I have been gifted with mistakes that I have made in the past that have allowed me to learn from them. (I have definitely been gifted with the knowledge of how important test labs are!)
Gifts of SQL Present
I am gifted with a job that seems to actively repel me away from any sort of comfort zone.
I am gifted with an active Data Community on Twitter, Slack, and in person for any issues that I run into.
I am gifted with a PowerShell community on Discord and Slack who seem to be constantly battling for the right to call themselves the “most welcoming community”.
I am gifted with a shrinking of borders so that anyone anywhere can be considered a helpful resource to a data question.
Gifts of SQL Future
I’m being gifted with new technologies to help out with old and new problems.
I’m being gifted with a field that is ever-changing. Fluid, dynamic, and will never become stagnant nor boring.
I’m being gifted with putting names to welcome faces and meeting up with new Data Community friends.
Mostly though, I’m being gifted with the opportunity to help others out in the future. Whether that be with blog posts, tweets, talks, examples, or just encouragement.
As good as all the previous gifts are, I believe the best gift is the gifts that you can give back. So, here’s hoping that the next year sees us all help more than hinder, learn more than laze, and teach more than troll.
The first exploration of a system leaves a lasting impact. When you first get a chance to delve into the database, you capture a shot of what the coding standards are like. You gleam the past experiences of the developers.
I’m looking for instances of NOLOCK if I’m being honest.
…there are impressions.
This impression was a What were you thinking? experience.
DEADLOCK_PRIORITY LOW on most procedures.
A lot of hierarchial data types.
VARCHAR(MAX) on most columns
Variables at the start of procedures used in equality WHERE clauses. e.g. DECLARE @Success int; Set @Success = 4; ... WHERE StatusId = @Success.
Functions that return a single, deterministic value.
Multi-statement Table-Valued Functions with WHILE statements.
A plethora of indexes on the tables, all single-column indexes.
I’ve said enough.
If you had seen my face at that moment, you would have laughed. Imagine me staring, horrified, eyes darting around the screen mouthing What the…
A little thinking saves a lot of shouting
Granted it took getting a coffee and staring in disbelief at the code before I recovered. It took getting another coffee after the first before I could rationalise what I was seeing.
I took what I knew, which was these developers were smart. I tried to match that with what I was seeing. And there was an answer.
Theoretical, not Physical
The codebase read like developers who were not used to interacting with a database. Developers who thought of the database as a “place to shove data” and that’s all.
It was clear they had tried to follow the DRY (Don’t Repeat Yourself) approach (#4, #5).
They had read the documentation on hierarchical data types and Microsoft’s saying:…
The built-in hierarchyid data type makes it easier to store and query hierarchical data
…instead of a parent/child relationship tables. (#2)
They had tried to translate the .Net data type [string] into the database. Deciding that varchar(max) was its closest match. (#3)
They had tried to query the data in a row-by-row approach, instead of a set-based method (#6).
And, they had tried to deal with the consequences of these and other decisions. (#1, #7)
Understanding, not blame
It’s hard to stay annoyed at people when you can understand their motives. Their mindset is the most effective deterrent to anger I can think of. There’s no blame but understanding. You want to help them improve. And that’s where this on-going process is now.
To move away from multi-statement Table-Valued functions with WHILE statements. Here’s inline Table-Valued functions with a recursive CTE (Common Table Expression) instead.
To use variables when you have to but be aware of the change in statistics that it brings.
The difference it can make to a query and a database when the data types are apt. How memory grants, logical page reads, and more are affecting by blobs.
How DEADLOCK_PRIORITY LOW is not an option if every procedure has it! How indexes can be of more than a single column. That there is such a thing as an INCLUDES!
Seeing now that the driving force they have is to create features. But the pain force they feel is database performance. I can grok their choices and actions at the time.
Still, it didn’t stop me going What were you thinking? at the outset.
I’m no better
I’m trying to learn different languages and frameworks at the moment. If someone more knowledgeable was to come along and see my interactions with Linux. If they were to critique my Python files. Or attempt to suppress a groan at my PromQL. I’d appreciate an air of understanding, not blame at that time.
So well done to the people who dived in and attempted the work even if they didn’t know how at the time. To paraphrase; those whos face is marred by dust and sweat and blood deserve the credit.
But don’t think I didn’t see those TRANSACTION LEVEL READ UNCOMMITTED that you’re using as NOLOCKS!
On the 13th of September 2019, I spoke at DataScotland; my first time talking at a data conference.
My quasi-clickbait title was Feel Validated with dbachecks. If you guessed that I was talking about dbachecks then you’re right.
This is a brief retrospective of that time. Thinking back on that time still makes me relive the emotions that I felt. Nervousness, excitement, and panic.
This was my first time speaking at a conference as well as my first time attending Data Scotland.
I recommend that you check it out. It’s an amazing conference created by passionate people and staffed by dedicated volunteers.
What didn’t count as the good is the way to put this!
Amazing fellow speakers.
Getting to meet other first-time speakers.
Talking with the volunteers.
Speaking with attendees.
Seeing people who I hadn’t seen in a long time.
Thank you to Craig Porteous, Louise Paterson, Paul Broadwith, and Robert French for all your work and encouragement.
Thank you as well to Brent Miller, Andrew Pruski, David Alcock, and John McCormack for help with the presentation.
I don’t put this down to DataScotland though. You may not have heard from me for the last month. I felt drained and took time off from public exposure.
A full year of constant working on the day job and personal work. 2 conferences a month on average for the last year. Spreading myself out on projects about SQL, PowerShell, Containers, Python, AWS, and Azure without rest. It’s not something I could sustain without factoring in sharpening the axe time.
I’m easing myself back into things again with the caveat of planning ahead and making sure I don’t overwhelm myself. First thing on my list, planning for DataScotland next year.
Whether it’s speaking, volunteering, or attending, I’ll be there.
On your test instance, we’re going to create a SQL Authentication Login that shall be our test subject.
We are testing dbcreator so we’re going to add it to that role after we create it. We’ll also need a database that we can test this on.
CREATE LOGIN [RestoreNoBackup] WITH PASSWORD = 'WhyOneAndNotOther?';
ALTER SERVER ROLE dbcreator ADD MEMBER RestoreNoBackup;
IF DB_ID(N'TestBackup') IS NOT NULL BEGIN
ALTER DATABASE TestBackup SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestBackup;
CREATE DATABASE TestBackup;
ALTER DATABASE TestBackup SET RECOVERY SIMPLE;
CREATE TABLE dbo.TestData (
TestDataId int IDENTITY(1, 1) NOT NULL
CONSTRAINT pk_TestData PRIMARY KEY,
TestDate date NOT NULL
CONSTRAINT df_TestDate_getdate DEFAULT (GETDATE()),
TestValue varchar(25) NOT NULL
CREATE USER RestoreNoBackup FROM LOGIN RestoreNoBackup;
GRANT SELECT ON dbo.TestData TO RestoreNoBackup;
INSERT INTO dbo.TestData (TestValue)
('This is important!'),
('So is this.');
SELECT * FROM dbo.TestData;
So now we have:
A database: TestBackup
A SQL Login: RestoreNoBackup
Assigned to dbcreator
A User on the database linked to the login: RestoreNoBackup
Granted SELECT on the table.
Are we missing something? Let’s take a backup as well.
BACKUP DATABASE TestBackup
TO DISK = N'E:\BACKUP\TestBackup_20190530_01.bak'
Now, let’s do our actual test.
We’re going to connect to the instance as our login; RestoreNoBackup.
Now, let’s test that we can create a database…
CREATE DATABASE Empty01;
DbOwner = SUSER_SNAME(owner_sid),
DbName = [name]
Lovely, can we restore the backup that we took?…
RESTORE DATABASE RestoreTest01
FROM DISK = N'E:\BACKUP\TestBackup_20190530_01.bak'
MOVE N'TestBackup' TO N'/var/opt/mssql/data/RestoreTest01.mdf',
MOVE N'TestBackup_log' TO N'/var/opt/mssql/data/RestoreTest01_log.ldf',
STATS = 10;
SELECT * FROM [RestoreTest01].dbo.TestData;
DbOwner = SUSER_SNAME(owner_sid),
DbName = [name]
Great! But can we backup the database that we created?…
BACKUP DATABASE RestoreTest01
TO DISK = N'E:\BACKUP\RestoreTest01_20190530_01.bak'
Okay! So if backup permissions are needed, I need to add the user to the db_backupoperator database role.
Good to know!
I now have confirmation that dbcreator can create and restore (I’m wasn’t testing alter or drop) databases.
This is a lovely case of #SQLNewBlogger, it took me 15 minutes for this blog post and most of that was taking the screenshots and cleaning the code.