Accidental DBA

Top 10 SQL Server functions every DBA should know by heart

February 17, 2017 by Kevin3NF 4 Comments

I suck at syntax. A lot.

Basic Selects and Joins I’ve got. Insert, Update or Delete a row? No problem. Beyond that I have to look it up more than I probably should. But, since I was last a SQL developer in 2001, I function just fine. I am very familiar with pretty much all of the T-SQL keywords, what they do, etc. But I just don’t use them often enough to commit them to memory.

If you are a DBA, there are many functions built into SQL Server that you MUST commit to memory. If you don’t…they will creep in there and take up residence anyway. Like Borg…they will assimilate parts of your brain. Just play along and all will be ok.

My top 10 list:

1. @@servername – I use ‘Select @@Servername’ literally every morning. I open SSMS, go to registered servers, Select my ‘Prod’ local group and run this in a query…just to see if all of my little Production server children respond. Takes 5 seconds to make sure everyone is online that should be.

2. @@version – Depending on the client I may use this every day if their servers are everything from 2005 RTM to 2014 SP-whatever. Currently, all of my servers are exactly the same. Its a little weird. But in a good way.

3. DBCC Opentran – log file filling up? Processes blocked and timing out? See if some doofus (hopefully not you) left a transaction open and went to lunch. If you are lucky you can kill it. Unlucky and you find an undistributed replicated transaction.

4. Getdate() – Not sure why, but this winds up in probably 80% of the scripts I write. Not surprisingly, it often brings along it’s little brother, DateDiff.

5. sp_Configure – If you don’t know this one, run it by itself against your test instance…you get back a ton of information about the instance. Min/Max memory, Backup Compression, etc. Basically a bunch of the stuff you see if you right-click the server instance and browse the GUI. And you can make changes here too.

6. DB_Name()/DB_Id() – When the view you are using give you the DBName and you need the ID, or vice versa…use this to flip it. Go ahead:

Select DB_Name(dbid), * from sysprocesses

6.5 Object_Name/Object_ID – same as above but for tables, indexes, etc.

7. Set NoCount On – Don’t need (53 row(s) affected) in the messages pane? Use this at the top of your query

8. Set Statistics IO On/Off – want to see a bunch of info about scan counts, logical and physical reads, etc. from your query? Use Set Statistics IO ON. For duration info, use TIME ON. Generally speaking, bigger is not better on this.

9. Min/Max/Count/Sum – “Help! I need a quick query to give us the most recent sale to each client in TX!” MAX(DateSold) is your friend. All of these count as aggregate functions, and you will need to understand GROUP BY as well. Select Count(*) from Table….google up a better way 😉

10. Cast/Convert – Need to Concatenate an INT field into a string? CAST it into a varchar():

Print 'Today is day ' + 1 + 'of the rest of your life'
--Conversion failed when converting the varchar value 'Today is day ' to data type int.

Print 'Today is day ' + cast(1 as varchar(1)) + ' of the rest of your life'
--Today is day 1 of the rest of your life

Convert is used when working with dates in one format and you need them in another. Read the linked doc and save in your favorites

Honorable mention: sp_whoisactive by Adam Machanic (b|t).

This would be top 5 on the list if Microsoft had written it. Step one in troubleshooting a performance issue, right after checking Task Manager to see if it really IS SQL Server taking up all the CPU, or some other program gone wild.

You, my dear rookie DBA need to get very comfortable with all of these. Look them up, test them, and memorize. Don’t just bookmark…memorize them. Give your brain some muscle memory.

Did I leave out something really obvious? Should it have been in my top 10? Tell me about it in the comments! Or harass me on Twitter 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

T-SQL Tuesday: Shiny New Toys

February 14, 2017 by Kevin3NF 1 Comment

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) over five years ago. The first Tuesday of each month a blogger hosts the party and suggests a topic. Anyone who is interested blogs on that topic on the second Tuesday. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick.

This month’s topic is presented by Matt Gordon (b/t), who has challenged us to write about “Fixing Old Problems with Shiny New Toys”, specifically new items introduced in SQL 2014 and up.

True to my typical post style which focuses on small shops, accidental DBAs, and junior DBAs I went looking for something that could very easily benefit people that are using the basic SQL Server features. In this case they may not even realize how limited they were.

I chose to write about Index Maintenance in the built-in Maintenance Plan portion of SQL Server.

A brief summary of the built-in Maintenance Plans is that they allow you to drag-and-drop your way to basic SQL Server maintenance items such as Backups, Index maintenance, CheckDB, Statistics updating, etc. This a tool that has been around since at least version 7 that I know of. It wasn’t always very good, and it gets a bad rap from a lot of DBAs. It has been dramatically improved over the years in flexibility and reliability.

In SQL 2014 and before, you had a choice to rebuild or reorganize indexes, and it was pretty much an all or none proposition. There were a few options, but still limited. If you wanted to get granular, you needed to roll your own code, buy a 3rd party utility, or use something like Ola Hallengren’s free scripts (known everywhere as the “Ola scripts“).

2014 Rebuild task…a few choices, but not a lot.

2014 Reorganize Index dialog…even fewer choices:

Don’t misunderstand me…its great that these options are available, especially for the SQL Server customer that doesn’t have a DBA or the budget for a 3rd party product. The biggest downside is that they will rebuild EVERY index on the selected database(s)…whether it needs it or not. This can cause significant blocking, resource usage and transaction log files that can grow way beyond what is expected.

In SQL 2016, Microsoft introduced the ability to Rebuild or Reorganize based on Fragmentation %, Size of the Index (page count) and usage:

These options allow you to rebuild at whatever levels make sense to you. Taking a hint from the Ola Scripts, I set mine to rebuild everything over 30% fragmented, and over 500 pages in size. “Used in the last xx days” is a nice feature as well. The stats sampling option is something I didn’t know was there until I started writing this!

Similarly, Index Reorganize is also now allowing you to get more granular:

These options give the small SQL shop the ability to “set it and forget it” in ways that are more intelligent than previously available.

Pro tip….do the rebuild first, so you don’t accidentally Reorganize everything over 5%, including the ones over 30%…

There are dozens of articles and blogs on Rebuild vs. Reorganize, so I won’t re-hash that here…but I will Google it for you.

Many DBAs will scoff at this and turn up their nose at SQL Maintenance Plans…but they definitely have their place. Not every shop can use a “free” product such as the Ola Scripts, or have budget for MinionWare. An additional great aspect of this is that since Microsoft wrote it, they support it!

Large shops with multiple instances, and a DBA staff…go get ’em! If you are a small shop looking to upgrade from 2008, R2, 2012…this is only available in 2016…which just runs faster, and has lots of Shiny New Toys!

Thanks for reading,

Kevin3NF

SQL 101: The SQL Server Security Model

February 1, 2017 by Kevin3NF 5 Comments

The SQL Server Security model has a lot of moving parts to it. Some friends of mine are so expert in it that it is their primary function on the DBA team they work for. My friend (and recently minted Data Platform MVP) Kenneth Fisher (b|t) regularly presents on it.

Fear not! There are a couple of key concepts that you have to get down early in order to not drown in all the other stuff.

As with most of my other 101 posts, this occurred to me while riding my bike. Road bike…the only thing that occurs to me when mountain biking is “don’t hit that tree.”

Imagine if you will, that you own a house.

Because you are the owner, you have a key to the house. You can do anything you want in any room of the house. You decide who else gets keys, and what they can do inside. We’ll assume your significant other also has a key and equal rights to all rooms.

Now just for kicks, lets assume you have 2 kids. Suzie and Little Bobby Tables (Bobby for short). Since mom and dad work, Suzie and Bobby also both have keys so they can get inside after school. However, they are not allowed into each other’s rooms, nor are they allowed to change the locks, paint the walls or use the stove. Because reasons.

So, 4 people can enter the house. 2 can do whatever they want, 2 have some restrictions. Simple enough?

In the SQL Server security model the above translates as follows:

House – an instance of SQL Server
Keys to the house – Logins
Mom and Dad – Logins with sysadmin role (God rights…)
Suzie and Bobby – Logins without sysadmin
Rooms – databases

You have to have a key/Login, to even open the door/connect to the instance.

You need specific permissions to access a database/room. Bobby is not allowed in Suzie’s room and vice-versa. Neither kid is allowed in dad’s man-cave, but mom is.

Suzie and Bobby need to be granted access to use a database/room, so they are created as USERS in the database. Mom and Dad do not explicitly need to be granted as USERS in any database, because they own the place and can do whatever they want at the server/house or database/room level.

If Uncle Joe is coming for a visit, he would also get a key/LOGIN and access to the rooms/databases he will be using. When he leaves, he gives his key back (USER/LOGIN removed).

If you want, imagine Bobby has a brother and they share a room. In their room there are two beds. They don’t share and they don’t switch. In this example the beds are database objects such as tables. You can grant or deny access down to that level as well.

So, LOGIN is created first (can’t go into a room until you get in the house). USER access for non sysadmins is created next at the database level. Inside the database, you GRANT or DENY permissions to objects in the room (if you choose). You can get very, very granular if your application/employer requires it.

This is high level overview, with a lot of specifics left out. Such as, give LOGINs to Groups (preferred, instead of individuals), server and database roles, etc. Just knowing the difference between LOGIN and USER puts you ahead of non-DBAs who use the terms interchangeably. Login can be either Windows based or created within SQL Server depending on how the instance is configured and your security teams requirements.

Hope this makes your life easier! Comments are welcomed.

Update: My Apprentice astutely made the observation that a physical or virtual server with more than one SQL Server Instance installed would be a “neighborhood” in this example. Would that make a Failover Cluster a duplex? 🙂 Would a multi-tenant database be an apartment building?

Thanks for reading,

Kevin3NF

Installing SQL 2016 Developer – Extreme Basics

December 29, 2016 by Kevin3NF Leave a Comment

If you are a student, a developer or a non-dba wanting to get started with learning more about SQL Server and you need an install to play with, you have a couple of free choices:

SQL Server Express – free, but has some size and performance restrictions. Also, no SQL Agent, so no job scheduling.
SQL Server Developer – also free, full code of the Enterprise Edition, but cannot be used legally in production. Because its a dev edition.

If you’ve never installed SQL server before and are not sure what to do, this video is for you. It is targeted to new SQL users, installing on their laptops/workstations. I take all the defaults and describe why. I install just the database engine (and the SQL Agent that comes along with that)…no Reporting services, no Analysis Services, etc.

This is just so you can see it before you do it. Enjoy!

Thanks for watching!

Kevin3NF

TSQL2SDay: Backup Basics

December 13, 2016 by Kevin3NF 1 Comment

T-SQL Tuesday is a blog party started by Adam Machanic (b/t) just over five years ago. The first Tuesday of each month a blogger hosts the party and suggests a topic. Then anyone who is interested blogs on that topic. It can be a lot of fun and quite a challenge to blog on a topic you didn’t pick.

This month’s topic is presented by my friend Kenneth Fisher (b/t), who has challenged us to go #EntryLevel and talk about backups. So I did…in a video just posted to YouTube:

You’ll want to go full screen to see what’s going on. This video is targeted at non-DBAs, but give it a watch anyway…I can take the heckling!

Back to Basics: Backups

November 10, 2016 by Kevin3NF 1 Comment

A database backup intro for: Accidental DBAs, Juniors, Developers, Managers, etc.

Backing up SQL Server databases has been documented in thousands of books, Microsoft documentation and blog posts since the product was released in the 90s.

I’m not about to try and tell you HOW to backup your SQL databases. Rather, I want to take you through the basic considerations of deciding what to back up, when, how often, etc. in clear language that non-SQL folks can work through.

Some basic terminology:

Database backup – sending all of the data, structure, objects, etc. into a different location (file/tape/virtual device). This is at the database level. Not Server or Instance wide.
Device – formerly called a dump device, this is the target for your backup.
Full backup – all of the contents, data and code.
Differential – Everything that has changed since the last Full
Transaction Log backup – All new entries in the .ldf file since the last t-log backup
Recovery model – 3 choices, depending on how much data loss you can tolerate (Full, Bulk-Logged, Simple)
RPO – Recovery Point Objective – the goal of what point you need to be able to recover to.
RTO – Recovery Time Objective – how fast you need to be able to restore the data.

Decisions you really need to make…simplified:

Do I need to back up this database?
How often?
What is my tolerance for data loss?
How long can we be down if we have to restore?
Where should I store my backups?
How long should I keep them, based on business and regulatory requirements?
Should I test my backups?

One by one:

Do I need to back up this database?

Most likely. Unless this is a completely stale copy of read-only data and you can re-generate it with a few clicks, then yes…back it up. Restoring from backup is generally much faster than re-generating. System databases as well, in case you have to build up a new server.

How Often?

A very common standard in the industry to start with is to do a Full backup weekly, Differentials daily and T-logs throughout the day. This speaks directly to your tolerance for data loss. Adjust based on your specific business needs. Slowly changing data can tolerate fewer backups than a highly active system such as order fulfillment.

What is my tolerance for data loss?

The more “nines” you want, the more expensive your backup solution will be. Be realistic, and sort out if you can handle a minute, a day or a month. I’ve seen all of the above as the appropriate answer. There are not ANY generic backup solutions within SQL Server that will guarantee zero data loss. That is a whole different conversation.

How long can we be down if we have to restore?

Commonly referred to by management in terms of how much money are we losing, due to lost productivity, lost sales, etc. If you can be down for an hour and one person is affected, that is wildly different than your online store database being down for an hour when you are the size of an Amazon or eBay. Be realistic. Test restore your databases to determine the restore time.

Where should I store my backups?

Somewhere else! Industry standard is to have “hot” backup file(s) local to the server (different drives than the database files), and copies of them somewhere else…tape, SAN, etc. Again, the more resilient the solution needs to be, the more expensive. Cloud storage is getting cheaper and cheaper all the time.

How long should I keep them, based on business and regulatory requirements?

I’ve had customers trash backups after a week, and one that had to keep 30 years. True story. Ask your auditors and legal…you don’t want to be the one that has to explain why your backups only go back a year when 3 were required by law.

Should I test my backups?

I forget who, but someone well respected in the SQL Server world said this years ago (paraphrasing): “If you don’t test your DR solution, you don’t have a DR solution. You have a DR Hope.” Do test restores on another server so you know the files are valid, and so you know how long the restores take.

Other considerations:

You do not need a 3rd party solution or expensive toolset to implement basic backups. SQL Server has a Maintenance Plan wizard that will walk you through getting a basic plan in place. The more complex you needs, the less likely this is to be sufficient.

Setting up backups is a bare minimum starting point for any organization. Depending on industry, organizational and regulatory requirements you may have to go above and beyond. You may need to introduce compression, encryption, off-site storage, etc.

If your database/application/website uptime requirements have a lot of nines…you will be looking into HA (High Availability) solutions, of which there are a variety. DR (Disaster Recovery) starts with backups at the database level. Depending on the level of disaster you are trying to be able to recover from, you may need to protect more and more. I start this conversation from small to large: Database deleted, server dead, server room fire, building collapse, city offline.

This was not meant to be an exhaustive list of every possible backup and restore scenario in the SQL Server world. But I do hope that if you are not a DBA you now how a basic understanding. If you have a DBA, go ask her about your current strategy. If you don’t have anyone doing this for you, it may be time to bring someone in to get you set up…and now you have a starting point for that conversation.

Please leave me any follow up questions in the comments!

Kevin3NF