Voice of the DBA

Author: way0utwest

  • Learning is Hard–March Goals Update

    This is the third update for my goals for 2018. This is about 3 weeks after the last update, and it’s too soon. Not in time, but because I haven’t done a great job.

    This was a busy month. March and April are prime volleyball season, so I’ve had some longer weekends and time off to coach and watch my daughter play. Add to that some ski time and I haven’t had a lot of work or free time. As a result, my days at work are busy with catching up and ensuring I’m getting things done for work.

    Learning has suffered, and like the Oct/Nov conference season, learning is suffering here.

    My two appointments, Tues afternoon just after work and Friday afternoon, have been busy with other events, or with the need to do more work. As a result, not enough learning. Here’s an update in my two areas.

    Extended Events

    Very little here. This is Tuesday learning, and I’ve gotten myself jammed up for time every week. I’m considering moving this to another day, but not sure which one is better. I’m tempting to try and get through ski season and see if Tues then lightens a bit at work.

    I did watch more of Jonathan Kehayias’ Pluralsight course while on the bike, but not practical work.

    Python

    I spent a couple of hours working through some basics on lists and dictionaries. I realized that I haven’t been doing this enough and I needed a refresher. That was good, and it helped me build a few Questions of the Day, as well as get some practice.

    The one upside is that I was struggling with a puzzle in T-SQL and decided to try Python. I solved it in about 10 minutes, and my solution worked the first time. I was proud of that, and spent another 10-15 minutes solving another problem. So, I know a few things.

    No real formal learning, but I feel like I should race through a Pluralsight course and try to be sure I have a good grounding. Plans for next month as I’m traveling a bit.

    Too Busy

    A few things with family and then volleyball affected me more than I thought, but that’s part of a busy life and it challenges my focus on learning. I’m also letting myself get distracted with the shiny things, reading a bit on various other technologies, which is part of my job. However, CosmosDB and other items aren’t what I planned for this year.

    I’m putting pressure on myself to get through some XE and Python learning and then move into other areas. We’ll see if I can pick things up next month.

  • Moving to AWS EC2

    SQLServerCentral has been stuck in the past for some time, but we’ve finally decided to move. I’ve been asking for this, and it’s usually a resource issue. However, earlier this year we started to debate and examine the costs, and decided to pursue a move to EC2. We have other infrastructure there, VPNs setup to connect, and this made the most sense.

    I was excited. The last time we upgraded, we left SQL Server 2000 for SQL Server 2008 R2. That was a long time ago, and at the time, I wasn’t sure that 2008 R2 was a big change. We used the core engine only, and it didn’t seem that this was that cost effective a move for us. Now I wish we’d delayed another year and moved to 2012.

    We’re moving into the current era, choosing to run on SQL Server 2017. I’ll have access to lots of the latest and greatest T-SQL, which will help with some of our complex reporting tasks.

    Cutover

    I didn’t do the work, but one of our engineers practiced a backup and restore, and then setup log shipping to limit the downtime. After a few days of testing, and a few of us running the new site through our own tests, we made the cutover.

    Total downtime, about 35 minutes to quiesce the old system, get the last logs copied over, and then move networking.

    And we’re live.

    2018-03-28 10_14_07-ssc-db-n1 - Remote Desktop Connection

    We have six Basic Availability Groups setup. We could potentially have issues with a failover in that it’s possible that a user does something in one BAG that doesn’t make it to the other side, but in practice, it’s not a concern. We don’t deal with sensitive information, and likely any data lots can be manually moved.

    This was the most cost effective move for us, roughly equivalent hardware, though we moved from physical db servers at Rackspace to VMs in EC2. However, we’re now on modern versions of Windows, SQL, and more importantly, .NET. That last item was slowing some development items, so we can now start to move forward with future development.

  • The Nightmare Letter

    I’m not sure if this imaginary GDPR letter is a nightmare, but I do know that in most of the organizations where I’ve worked, this type of request would result in a crash project for me. I’d be working long hours, contacting lots of people and trying to manage a complex spreadsheet of information about an individual. I’d like to think that I’d compile this information in a general sense to understand our data better and anticipate future requests, building a process that I could repeat, but I know that under pressure that might not always happen. I’m sure I’d grab some data without capturing and saving the metadata or query. I’d probably have to perform duplicate work when the next request came in.

    GDPR enforcement begins in a couple months, and organizations receiving this type of letter will have 30 days to respond. Companies can also charge a reasonable fee based on administrative costs for information requested. The fee that’s reasonable for getting a few of these letters a month might not be sufficient if hundreds or thousands of individuals start requesting this information, and I’m sure companies and authorities will be arguing about the rates.

    With the focus on privacy in the media, and the mishandling of data regularly by companies, I wouldn’t be surprised if there are going to be large numbers of requests by individuals. In fact, I’m wouldn’t be surprised if there are scripts or applications being built now to facilitate the ability for lots of individuals to ask for this information from companies about their data processing.

    Really all of this information should be documented and any decisions made about securing sensitive data should always be followed. Any organization should know how they handle data, where it’s stored, and how it’s secured. This is just practical and good administrative practice. The items about how data is processed and used are good business knowledge points. After all, should we be processing data without some justification for the resources involved? I think too often a company decides to implement some process without evaluating if it makes sense in the context of their mission. If it does, we should know why it does and be able to measure that. If it doesn’t, we ought to stop.

    If you do business in the EU or with EU citizens, you might wish to start ensuring you have a way to export the information requested in this letter. Being prepared for some of these items might make it much easier to respond to any or all of these requests.

    Whether you think this might happen to your organization or not, you might want to just save a copy of this letter. I know I will, with the idea that I might send this off to companies that store my data. Knowledge can help me protect myself by being aware of what’s being done with information related to me. If there are issues, having this information might help ensure my rights are protected. I’ll also be sure that I have a form letter to ask for removal of information. I’ve felt this wasn’t possible in the past, but at least in the EU, where I regularly travel, I can exert some control over my data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Small Changes and Big Impacts

    I’ve been doing some work with RLS lately (Row-Level Security). I think this is one of the most useful features added in SQL Server 2016, and I wish it had been available earlier in my career. I’ve had the need to secure multi-tenant databases, which is a chore. Updates and new functionality constantly brought stress on developers and DBAs that a small mistake would expose one client’s data to another.

    RLS isn’t perfect, and while it simplifies the way in which you can apply security to your tables, it does require strong T-SQL understanding and careful thought. While we often can link a user to a customer, we may decide that we also have administrators or super users that need to access all, or many pieces of data. What might seem like a small change, just adding a catch-all clause, might cause problems for performance. This article at MSDN shows the impact, which could be quite large for million (or billion) row tables.

    I  ran across a developer that was testing access and found performance to be much worse. They were querying AD with OR clauses to allow different groups access, and this ended up causing issues. There are a few ways that this could be solved, but everyone should understand that adding in functions to your code  requires more processing of data. This processing can dramatically impact your system, just like any other code, if you haven’t written efficient operations.

    That’s ultimately the litmus test for most code. Is you code written to work efficiently in your environment and with ever growing data sizes? There are certainly places where a trade off might make sense. If you have a system that will never pass many rows through the FORMAT() function, perhaps that’s acceptable. If you can’t be sure of the data size and it could be large, then you shouldn’t ever be using FORMAT().

    There are some great features in SQL Server, many of which can help you write better applications. You just need to use them appropriately and in the places where they will shine. RLS is one of these, but there can be a performance impact if you aren’t careful. Ensure that you learn to write better code, picking the patterns that work well. No matter what, also ensure that before you deploy your change, you run a full scale performance test to be sure your clients won’t be spending most of their time staring at a screen while some query runs in the background.

    Steve Jones

    The Voice of the DBA Podcast

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