Accidental DBA

T-SQL Tuesday: Speaking & Presenting

November 8, 2016 by Kevin3NF 3 Comments

#TSQL2SDAY is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Adam Machanic on his blog.

This month’s topic is going to be about Speaking & Presenting with a focus on Helping New Speakers.

I have presented “publicly” exactly one time, probably 12 years ago. It was to the North Texas SQL Server User Group, during the “Pizza and Networking” portion of the evening. I was the warmup act for the main presenter, which was perfectly fine with me. I was simply doing a walk through of Red Gate Software‘s SQL Compare and SQL Data Compare tools, which weren’t as well known as they are now.

I’ve done a boatload of teaching since then, but almost exclusively to people I work with. I’ve had dozens of one hour ad-hoc sessions to teach level 1 engineers SQL Server basics such as Backups, Log Shipping concepts, etc. so that they could close more tickets without having to escalate. These were limited to 2-3 people, and no slide decks. All whiteboard with circles, squares and arrows. I’m also very experienced in teaching Bible stories and concepts to teens and pre-teens. Lastly, I’ve been a cycling coach to teenagers for almost 10 years now…teaching training techniques and race tactics. Multiple Texas State Championships from those I’ve coached.

All that to say I’m comfortable teaching to kids and those with open minds.

Teaching to SQL Server professionals is quite a bit more intimidating.

My ideal first presentation has to be things I know very, very well to a group of people that rarely use the info, and don’t know they need it:

“DBA basics for non-DBAs”

Target Audience: developers, junior and accidental DBAs, managers, Sys/Storage Admins, Data Warehouse and BI folks. DBA-101.

I don’t know how well this would fit into a User group meeting, since most of the attendees are fairly experienced with SQL Server. I think it would go over really well at a SQL Saturday, where there are multiple concurrent sessions.

Topics I think would fit really well into this talk (not necessarily all):

Backup/restore – What they are, what your DBA is doing, and what to ask for when you need a restore
How to find things in SQL Server Management Studio
What exactly is in the ERRORLOG?
Installation/Setup best practices
Concepts and differences in Log Shipping, Replication, Clustering and AGs (no tech, just descriptions…possibly its own presentation)

The key point of all this is take the massive enterprise product that is SQL Server and boil the basics down to the new users in terms they understand without drowning them. A fine line for sure…

I would LOVE to get your feedback in the comments!

Thanks,

Kevin3NF

The OnPurpose DBA

T-SQL Tuesday: Still dealing with the same problems

October 11, 2016 by Kevin3NF 2 Comments

t-sqltuesday

This post is my contribution to the monthly blog party known as “T-SQL Tuesday.” This month is being hosted by Andy Mallon, and covers the topic We’re still dealing with the same problems.

The topic was inspired by a Tweet from Allan Hirt. Allan is exactly right.

I entered the IT field officially ( meaning full-time) in 1998 as an Access Developer. I’ve done desktop support, database dev, network, phone systems and for the last 17 years I’ve been working exclusively with SQL Server, v. 6.5 – 2016.

In all these years, we still have users that don’t know to reboot a frozen laptop, store passwords anywhere but a sticky note and call me to “fix the internet” (pro tip…its broken. Go read comments on any news article.)

Narrowing this down to my slice of the IT world (SQL) and then more to Admin, and even further took some time. The thing that I see and deal with the most came down to “blindly clicking OK and accepting all the defaults, all the time.”

I get it.

On my first SQL installation, I clicked OK and yes right through the install wizard and had a functional SQL 2000 install on my desktop. On the C drive, which was almost full already.

Installation defaults that are going to bite you (not version specific, and the installer is getting better):

Files all on the C drive
One TempDB data file (improved in SQL 2016)
Backups on C drive
No automated backups
Allow SQL to use ALL the memory
Allow SQL to use ALL the CPUs
Builtin\Administrators group not default*
Compressed backup set to OFF

If you have any of the above, please research each and sort out why I listed it…you will learn more along the way than I can teach you here. Each of these is documented extremely well by both Microsoft and the SQL Community.

* let the religious debate start in 3…2…1…

Another issue that is not done at install time, but shortly thereafter when you create your first database is the VERY common failure of setting up a new Database using the default FULL Recovery model, without a Transaction Log backup plan in place.

There are hundreds, maybe thousands of forum posts asking the same question: “My database is 1GB, but my T-log is 500GB and filled the drive.”

I had this question brought to me just 2 weeks ago at SQL Saturday Dallas by developer turned accidental DBA. We spent 30 minutes discussing his database restore failures and why it took so long. At the very end he mentioned that the .LDF file was HUGE compared to the .MDF file, and then walked off to the next session. That HUGE .LDF is taking most of the restore time due to writing zeros into it well after the data is written.

Long story short: Make sure you understand the defaults before you install, and implement a proper backup plan. You can learn these easily and I’m expensive if I need to come fix them for you 🙂

Kevin3NF

SQL 101 – Recovery models in simple terms

January 10, 2008 by Kevin3NF Leave a Comment

A recovery model is simply a choice made on each database that determines how much data you can recover if your db goes “toes up”.

If you do not backup your database, you have chosen recovery model “terminated” or “update resume”

The 3 that are offered by Microsoft are:

Bulk-logged (rarely seen, and generally more advanced, so I’m skipping it)
Simple
Full

Simple vs. Full is very simply a matter of how much data can you afford to lose if the database is lost and you are forced to restore from your backups.

Simple recovery model: does not allow backing up the transaction logs, therefore you cannot restore any of the data in them. You can only restore to the point of the Full backup and any one Differential you may have.

Full recovery model: You can restore from t-log backups (assuming you have them), right up to a specific point in time. Reduced data loss, assuming the backup files are valid.

When to use:

Simple: When you do not care about the data, such as in a Development or Test environment where you can regenerate from another source. Also useful in a static, read-only database (assuming you have a full backup).

Full: Pretty much any live production database that has Inserts, Updates and Deletes happening.

Switching from one to the other:

Simple to Full: Immediately take a Full or Differential backup

Full to Simple: No specific action required, other than verifying regular data backups are in place and working.

Maintenance plan considerations:

If you have both Simple and Full recovery model databases in your SQL instance, and you create a Maintenance Plan to back up data and logs, you may run into an issue (at least in SQL 2000) where the automated deletion of old t-log backups is failing. Make two plans: one for Full and one for Simple. I have no idea if this issue still presents in SQL 2005.

I hope this is clear…please feel free to comment.

Kevin3NF