Voice of the DBA

Author: way0utwest

  • The Developer Arguments for Stored Procedures

    In all of the decades that I’ve been working with SQL Server, many people have been preaching the benefits of using stored procedures, In all of that time, the vast majority of developers that I’ve worked with have not wanted to actually write, or even call them. In one job, I offered to write all procedures within a day of being asked for them for one development team, and they still didn’t really want to use them. I made them do this by revoking permissions on many tables, but they still grumbled about the overhead of using procedures.

    I was having a discussion with a friend recently about using procedure, and he was having a similar battle. There was a group of developers that wanted to embed SQL  or use LINQ->SQL in their applications instead of calling stored procedures. It was frustrating, but he managed to secure a meeting to discuss the concerns with the developers.

    As we talked, we tried to frame the problem from the perspective of a developer. Why would or wouldn’t you want to use stored procedures? We had a few ideas, but really, I’m curious what you think today. Do you have any debate points that make sense from a developer point of view?

    I certainly do understand the ease of just writing SQL in whatever IDE you use for C#, ASP.NET, Java, etc. I get not wanting to open up SSMS, which can be slow. I also get that it’s nice to see all the code in one place, and not have some split between the application and the database.

    On the other hand, the idea of encapsulating code in the database feels a lot like the reason developers refactor code into new classes and methods to provide a clean interface between different types of functionality. Many times developers work with applications and code split among different components. On top of this, when you use procedures you push a lot of the performance work down to the database and on the DBAs.

    If you think stored procedures do or don’t make sense from a developer point of view, let me know today. Leave a comment in the discussion and explain your position.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Is 100% Security Possible?

    Microsoft has spent a lot of resources working to ensure their software can be automated, audited, and configured securely. After the SQL Slammer worm, there was an effort made by the organization to code more securely. Secure by design and by default was the goal, and they’ve continued in the years since to try and ensure we can better secure our systems. There’s even a Zero Trust methodology that they push these days.

    Microsoft is notoriously strict with security, disallowing networking to potential threats, and they encourage you to do this as well. They have SAWs and PAWs and auditing recommendations and advanced threat protection. They have an incredible security operations center, which I’d like to think they use for their internal resources.

    And yet, we have Microsoft support mis-configuring a database and exposing customer PII. How does this happen when security is a big part of the Microsoft business, and something that many of us rely on them doing well? This was human error, an issue with network security rules, and likely due to the complexity of these rules, because managing these and understanding the end result of a large set of rules is difficult for a human.

    Still, it’s disappointing and daunting. As I talk with people about DevOps and ensuring we automate our best practices, I find many people embracing the idea of using software to ensure our systems are set up consistently and securely, in the same way in all environments. I find plenty of people that prefer having the computer make the changes for them and removing the chance they make a mistake.

    However, if all of the staff doesn’t buy into the new process, it opens up potential places for problems. One human and cause problems by circumventing the system. In this case, Microsoft admits they have solutions to prevent and detect this, but they were not enabled. Again, this was likely a human mistake, but this isn’t clear from their post.

    Perhaps the most disappointing thing about this data breech is the lack of details. I’d like to know what rules failed, and maybe more importantly, what systems are in place to protect against this and how are they configured. This is a great opportunity for Microsoft to share some knowledge and educate their customers, but they didn’t take it. Specifics on the failure would help many of us ensure we don’t fall victim to the same issue.

    Security is hard. It’s a constant, ongoing battle to ensure we are following best practices, as well as learning and updating our knowledge all the time. If Microsoft can’t do it, can we hope to? Maybe more importantly, if Microsoft doesn’t help us understand how do secure systems well, with their experts and specialists, can we hope to do better?

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • The COVID-19 Impact

    I didn’t think much of the COVID-19 virus when I first heard of it. It’s another dangerous virus, but like SARS and MERS, it originated on the other side of the world from me and I didn’t expect to see a large impact. These were epidemics and my heart goes out to those affected by them. I thought the current COVID-19 impact would be similar, but that’s not correct.

    The MVP Summit was cancelled by Microsoft this week, as have quite a few large events around the world. SQL Konferez is still taking place and SQL Bits is still scheduled to go on. I travel to the UK next week, and I’m not concerned, but I will be careful. I think avoiding large scale gatherings is a good idea, and certainly, I hope anyone feeling sick will avoid going out as much as possible, especially to places with a concentration of people. Many of us have events and commitments we hate to miss (exams, weddings, etc.), but if we’re ill, this seems like the time to skip or reschedule something.

    This epidemic, or pandemic, is going to affect lots of the world. Already travel is curtailed, some countries have banned large events, others are being cancelled. Whether necessary or not, these are the realities we are dealing with. I hope most employers adapt and adjust to this, limiting the chance of infection and exposure.

    Cisco is offering Webex to customers and others to help limit contact with those that might be infected, as a way to prevent the spread. At Redgate, we are planning for disruptions and having teams practice working remotely while still getting work done. They are trying to run their stand-ups and other meetings from home. This is easier for some groups than others, but it’s a skill most teams need to develop. We already ask people to remain home when ill, but in this case, we’re just running our DR test early in the event that we need to ask most employees to work remotely.

    I’m not a doctor or scientist, and I don’t quite know what to make of all the information being spread online through social media. There are recommendations from the CDC, and most of the advice I’ve seen is similar. Wash your hands, disinfect common surfaces, and cover your mouth if you have any symptoms.

    I hope and pray no more people die, but I also know that likely isn’t reality. Take care of yourself and your family, and be smart in how to live and work in public. I hope the world gets control of this soon and we start to see a decline in cases, and I hope most of us find ways to help that come true.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Women’s History Month and Being Better

    This month is Women’s History Month, and it’s a chance to stop and think about the impact that women have had in history. After reading Monica Rathbun’s post on being a women in the SQL Server community, I wanted to drop a few notes.

    Personally, I think this month is a good chance for me to think about the impact women have had in history. For example, after seeing Hidden Figures, I stopped and did a little research to see just how impactful women were in the space program, and how little my early education taught me that.

    The more I learn across time and in different places, the more I realize how much we are shaped by the history we learn, and the more I realize how incomplete what we learn can be. We certainly can’t learn everything, but I find more and more that various minorities, genders, and even other cultures (non-US for me) have had an impact I never realized.

    This isn’t to complain or chastise anyone, but rather just an opportunity to stop and take a little time this month to learn more. The Women’s History Month site might be a good place to start.

    Being a Better Community Member

    I’ve had the honor of knowing and working with some amazing women in this business. I hope that I have treated them as peers and the highly knowledgeable and talented professionals that they are. Kalen Delaney, specifically, was one of my heroes and inspirations early in my career, and I’ve been honored to not only meet her, but be able to call her a friend.

    I’ve also seen some poor behavior, with men treating women at events like they are entertainment or potential mates, without understanding these are our peers. While being attracted to others is natural and will happen in any environment, I do think that some people, especially men, forget that they should be careful not to make others uncomfortable, and certainly not unsafe.

    In trying to be better, I know that others of all genders may interact and express themselves differently than I do. I try to ensure I don’t talk over or dismiss the opinions of anyone, but certainly women.

    I’ve been on panels and at events where few, if any, woman are speaking, and I do try to find ways to support others when I can. I also try to be aware of situations where others may feel unsafe and offer to help.

    Mostly, I think the best way to be a better community member to women or minorities is to just be professional. We would assume a new employee at our organization was competent and deserved to be there until they prove otherwise. Just do the same thing for anyone you meet at an event and try to let go of biases and preconceived notions.