The Proliferation of Roles

The best practice guidance for SQL Server security is to use roles for permissions, instead of granting rights to users. I’ve always followed this guidance in my career. I’ve learned that if one person needs access, sooner or later someone else will. Even if it’s an automated process, I’ll use a role so that I can build a test login to ensure I’ve configured things correctly.

In small companies, or in relatively static applications, this makes perfect sense and few people argue with the rule. That is, until they get some requirement that only one account will ever be used. Then they want to just grant rights to a user. After all, why add the role for one person. See my thought above. Sooner or later, someone else will want access.

However I also have had people complain that if each new required position or process needs their own role, sooner or later we’ll have this proliferation of dozens of roles.

True.

I worked in a large Fortune 100 company and we had thousands of groups in our AD forest, and easily dozens of roles in many databases. While that might seem complex and confusing, it wasn’t bad. We named roles to match AD groups or job functions, and adding in new users was simple as we usually mapped them to the same roles as a previous user. If a new system or person needs access, usually their access is the same as some other account.

However, I know they can be complex, so I’m wondering if you have any tips, tricks, hints, or even gotchas for using roles. I’d also be curious if you think there are cases where roles don’t make sense.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

The New Global Dashboard

Redgate recently released SQL Monitor 5.2, which is the latest upgrade to our monitoring/alerting/troubleshooting product for DBAs. This was the big change that the team has spent a lot of time developing and refining. It’s been available for a few weeks as a hidden URL, but with 5.2, this becomes the default main screen for SQL Monitor.

I think it’s a good move forward. In general, I don’t like things moving around physically in applications, as I get used to them being in a certain spot, or I expect them. However in this case, it makes sense.

In a dashboard for monitoring and alerting, you want to know what’s broken. Having a list of 20 servers at the top, and 1 broken one potentially “beneath the fold” (in newspaper parlance) and requiring scrolling would be bad. As a DBA, I’d want to see those items that are problematic. When I look at the monitor.red-gate.com site, I see:

2016-05-12 08_39_11-Global Dashboard

The cluster has a long running query, which is an active, high priority alert. If I were to clear this, along with the other active alerts, this “card” would move to the end, and the sm-cluster2 item would take the top left spot.

Note there are options to configure what is a high or low priority, and even pin specific servers at the top, but the general behavior is to let you know what’s broken now.

There’s one other cool feature in this. If I have a high level alert, like a machine unreachable, and the alert clears itself (the machine reboots), I may see the machine as “green” on the dashboard when I login. This is because current alerts are shown, not historical ones. I can still get the historical data, but the intention is to make this a responsive tool for right now, not last night.

I think this is a great change, and I’m excited to see how well it works in practice as customers roll this out.

SQL Nexus

I was lucky enough to be accepted to speak at SQL Nexus in Copenhagen and I attended the event a few weeks ago. This is the new Nordic SQL conference that seems to be replacing the SQL Rally. I’d never been to Denmark, but the trip was easy, and I had no problem getting from the airport to the area downtown where the event was held. Surprisingly (for me) everyone I met spoke English, which was nice, considering I didn’t even know how to pronounce many words, including the train station I was trying to get to.

Being on the water, the speaker dinner was appropriate and quite enjoyable. I’ve had some good ones, but I think this was the best of them all.

Photo May 02, 12 16 50 PM

The event was held in a movie theater, which I think is very interesting. The spaces were large, theater style room with big screens. Our computers were hooked to the projectors, which worked well. You can see the keynote in the IMAX room below.

Photo May 03, 8 59 10 AM

The keynote was interesting, from Joseph Sirosh (Microsoft) and Troels Peterson (physicist at the Neils Bohr Institute). The same keynote from Joseph was at SQL Bits, but I don’t think Dr. Peterson went over to the UK. I want to write on Joseph’s a little later, so I’ll just take a few moments and show some highlights of how data is managed at the Cern Hadron collider.

Dr. Peterson works with the ATLAS detector. He had a few nice stats on the hardware.

Photo May 03, 10 24 59 AM

If you do some math here, you’ll see that when they run the detector and conduct an experiment they produce a lot of data. In fact, this was the next slide:

Photo May 03, 10 34 21 AM

That’s serious data. It was interesting that he said that’s an unmanageable amount of data. In fact, they need sensors to make decisions on the raw data because they can’t even use computers to analyze this level of information. However, they do have computers. In fact, they have:

  • 1127 racks
  • 10,070 servers
  • 17, 259 processors
  • 90, 948 cores
  • 75,718 disks
  • 113, 852 TiB raw disk
  • 312 TiB of memory capacity.
  • 120 tape drives
  • 52000 tape catridges
  • 75 PiB data on tape.

In all their analysis, searching for the secrets of the universe, they’ve learned a lot and gotten better at finding anomalies and problems with data. They know all their data is flawed, so they must use algorithms to try and find the data they can rely on in the entire lake of bits that is captured and stored. They use a lot of machine learning to comb through data.

In fact, he said their research actually showed that there was a reason certain data was altered in line with the phase of the moon. In fact, Dr. Peterson said that they determined that the length of the collider tunnel actually lengthened by 1mm because of the moon.

There were lots of other interesting SQL Server 2016 talks, including the ones you’d expect on machine learning, R, one on IoT (a bit of a wreck of a talk) and a great one showing MitM and other attacks against a SQL Server from a Linux machine.

The event was two days long. I spoke on encryption and security changes in SQL Server 2016 that went well. I’ll do some writing on my demos, showing more Always Encrypted, RLS, Dynamic Data Masking, and more.

The theater was right next to the river, with a nice walkway. A few of us were able to run alongside in the morning. Plenty of people walked or biked along the river each day, and the weather was amazing.

Photo May 03, 10 04 04 AM

I think this was a really nice conference, at a good cost for those of you in Europe. If you can get to Copenhagen, it might be worth the two (or three days with a pre-con) to try SQL Nexus next year. I’m hoping they do the event again next year and I’d certainly like to go if I can.

Testing T-SQL Made Easy

Abstract:

Everyone tests that code, but most people run a query, execute a procedure and run another query. This ad hoc, non-repeatable testing isn’t reliable, and encourages regression bugs. In this session you will learn how to begin introducing testing into your development process using the proven tSQLt framework. You’ll run tests with a click of a button, using an ever growing test suite that improves code quality. You will see how to handle test data, exceptions, and edge cases.

Level: 200 (I am assuming you know something about unit testing and tSQLt)

Demos:

  • Creating exceptions to standards
  • Catching changes to large tables
  • Ensuring the join works correctly
  • Testing Getdate()
  • Isolating functions and stored procedures from other calls
  • Checking boundary conditions

Downloads:

Powerpoint: Testing T-SQL Made Easy.pptx

Code: .zip file or Github repo

Presentations

  • SQL Bits XV – May 7, 2016

Back Home, Despite a Scare

I’ve spent the last 11 days traveling in Europe, returning home late yesterday. It wasn’t a holiday or even corporate boondoggle as this was 3 cities, 2 events at which I spoke, travel in planes, trains, and automobiles, a minor sinus infection, and movement across 6 hotels. All with just this luggage:

13087787_10209272872407736_4048621762789380628_n

Packed too full, and heavy, but I made it.

I returned late afternoon and in between spending time with family, I booted up my desktop and was surprised to find that Dropbox gave me an error. As I dug in, one of my SSDs wasn’t visible in Windows, containing VMs, my local DropBox folders and a few other things. Not what I wanted to deal with the first night back.

I shut down the system and rechecked connections this morning, then rebooted to find things working, but it was a good reminder to double check my backups and ensure that I’ve got copies of data in case I do lose an SSD.

Solving Tempdb Issues

While reading Remus Rusanu’s piece on SQL Server 2016 development, there was an item that struck me. There’s a part of the piece where he notes that an engineer at Microsoft realized that there was a tempdb issue with Azure SQL Database. To solve it, a failover was needed, which could be problematic. The basic issue was that tempdb files were sized unequally, discovered after “months of investigation.”

Now, on one hand this seems silly. After all, we’ve known since SQL Server 2005 that the guidance was for all files to be sized the same. Shouldn’t engineers at Microsoft be following all the practices  known for optimizing SQL Server performance? I think all Microsoft people should follow this, especially those working on other products (*cough* Sharepoint *cough*), but at the very least SQL Server engineers should have a huge list, perhaps with PBM or unit tests, that warn about non-compliance.

On the other hand, since we’ve known this is an issue since SQL Server 2005, why does SQL Server allow this? I’d think some simple tooling and a few checks in code could eliminate this as an issue in any new install. Catch the issue during upgrades and grow (or shrink) files to be the same size, or at least warn the administrator. I know there are situations where you need to add a new file in an emergency, but shouldn’t this be an item we push administrators to correct quickly after the emergency is past? Or at least force them to consciously make an exception.

There are plenty of limitations and restrictions in SQL Server systems that Microsoft forces on us. We have limits on where new features can be used, various historical limits on function parameters, and more. Why not also just enforce limits that prevent issues like this? I’m sure people will complain, but wouldn’t those complaints be outweighed by less issues overall from all customers?

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.3MB) podcast or subscribe to the feed at iTunes and LibSyn.

Use Tools

This editorial was originally published on May 23, 2012. It is being re-run as Steve is out of town.

As someone that works with Information Technology, I usually work on applications designed to make work easier. For example, we have accounting systems that ease the handling of debits and credits, they detect mistakes in data entry, and in general require less people to handle the accounting needs of many firms. Do we have less accountants?

I suppose we might have less, but it seems that the finance departments, handling AP and AR in many companies is as large as I remember from my younger days. Perhaps the department is smaller than it might otherwise be, but it’s usually not small.

In IT, we have all kinds of tools available to us that can help with our jobs. What’s more, we can create new tools as needed to do our jobs. Powershell or Perl scripting, Codeplex projects, and more are available to most of us to help us better manage systems. In SQL Server, we even have a built in framework, Policy Based Management, that helps us prevent changes or problematic configurations. If you find a third party tool that can help, you can make a case for its purchase, using the time savings translated to actual costs. One of those tools might really make your job more enjoyable..

In theory, we ought to be able to manage many more systems per person than we used to. Does that many that we need less people? Perhaps, but it seems we grow systems fast enough that we still need to hire more staff in many environments.

I so often see people working in IT fail to take advantage of all the tools we have to automate much of their jobs. They often tell me if they automate too much of their jobs, they won’t be needed and may get let go. Personally I think that’s an excuse not to exercise your skills, challenge your mind, and get rid of tedious work There’s no shortage of work to be done in most companies. If that’s true, why not use your tools to get rid of the tedious work and spend your time on something more interesting?

Steve Jones

A Lifetime of Software

This editorial was originally published on April 27, 2012. It is being re-run as Steve is out of town.

I’ve been working with computers and software for most of my life, but it’s been a career for a couple of decades now. I don’t do as much technical work as I used to, mostly testing and experimenting, but my job is related to SQL Server and software, and I anticipate that’s what I’ll be doing for the next two decades.

However that’s not necessarily the plan for everyone that works in the technology business. I know plenty of people that would like to move into management, or even move into some other career field if they can afford to do so. In the responses to many editorials in the past, I read that quite a few people think the technology business isn’t a great choice and wouldn’t encourage their children to enter this field.

I disagree, and think that this business has been very good to me, overall I’ve enjoyed it, and I think it would make a good career  for any of my kids. However this Friday I wanted to ask the rest of you what you think.

Do you expect to work in the technology field until you retire?

I know many of you will change your focus in technology, perhaps moving to develop from DBA, vice versa, or moving into another field. I know most of you will change companies before you retire, but I’m curious about whether you think you will remain a technology worker for the rest of the time you work.

Steve Jones

Work To Live

This editorial was originally published on May 2, 2012. It is being re-run as Steve is at SQL Bits.

I’m halfway through my career, having spent about 20 years since college working in technology with another twenty to go. The rest of the community is probably spread out around me, some older, some younger. It doesn’t matter what part of your life you are in, there are a few things about work that I think always apply. The first is that we work to live, and don’t live to work. The second is I like to hope for the best, but plan for the worst.

It’s all too common for people in this business to overwork themselves, work very long hours, and neglect the rest of their life. I have rarely seen someone that is retired or late in life complain that they didn’t work enough over their lifetime, and I’d caution you to keep that in mind as you go through life. There will always be more work, more things to do, and more requests from clients and customers. Keep a balance in your life, and remember to enjoy it along the way.

The second part of my advice can apply in many parts of our career, but it ought to apply to our finances and our retirement. Unfortunately, I’ve seen a few articles like this one that notes many people have not funded their retirements well. We all have expenses, and it’s easy to delay the investments in our life until later, but that is a bad idea. Whether it’s your IRA/401K or your health, you’d be better off sacrificing a little today for a little more security later.

In the US, many employers match contributions and you ought to take advantage of that. There are all sorts of options for retirement, like ROTH IRAs, that it seems many people aren’t aware of. I also meet many people that don’t realize they can borrow against their 401K as well, taking a loan whose interest goes back to your own account. There are few excuses to not plan for the future. Enjoy today, but plan to live a long time and to enjoy your retirement.

How much money you might need depends on your lifestyle, and that’s something you should spend time thinking about and talking with your family about. Many of us may not be able to retire at 65, or count on social security, but I’d hate to have to work longer than I want to because I cannot afford to retire.

Steve Jones

 

Pensacola in June–SQL Saturday #491

One June 5, I’ll be back in Pensacola, FL for SQL Saturday #491. It’s been a few years since there was a conference in the area, and I’m excited to go back. I really like the area, and am looking forward to visiting for a few days.

I’ll be presenting my dive into Always Encrypted in SQL Server 2016. I presented this in Phoenix recently and the session was well received. I like this feature, but there are some caveats and gotchas, as well as various items you should consider before making the decision to implement it. Hopefully I’ll see a few of you there and you’ll enjoy the talk.

However if you’re nearby, there are pre-conference sessions on both Thursday and Friday that you can attend. Both are inexpensive training and the chance for you to quickly improve some data skills.

There are lots of great sessions from other speakers, and Pensacola is a great place to spend a weekend. Lovely beaches, not too large, not too expensive, and a beautiful area in June. If you’re anywhere from Houston to Florida to Tennessee, consider combining a little learning with a vacation on the gulf coast.