Voice of the DBA

Category: Editorial

  • 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.

  • The Evolution of AI

    I saw a study recently where an AI system was used to analyze code and trying to decide if authors were good. The conclusions were things we’d expect, and quite a few people laughed about this on Twitter. After all, if AI comes to the obvious answer, is it useful? Perhaps, but it’s also a little disappointing.

    I’ve felt that way about AI for some time. Years ago I went through an AI demo for the Titantic data set, coming to the conclusion that the lower you were in the ship, or poorer, the more likely you were to have died. I went through a tutorial with Microsoft on flight data, trying to determine why flights were late. It turned out the later they left, the more likely they were to be late arriving.

    While this might seem silly, and perhaps obvious, I think that’s OK. We don’t expect these early AI systems to be better than humans, at least I don’t. However, what I do expect is that they can do at least as well as humans, and having them return results that we could figure out is a good step. To me, this also gives me confidence that a computer can be used to analyze a complex problem or situation.

    There is more evolution coming in the AI and ML worlds. I have no doubt that these systems will improve, and likely find niches where they are valuable and very helpful in making decisions. The world is gathering more and more data, and it’s becoming hard for humans to analyze it, and maybe more importantly, react quickly. The rate at which data can change is overwhelming, especially for humans. I think that we will need computing systems to help process the vast amounts of data we continue to accrue.

    While I don’t know that I want AI/ML systems making decisions for me, I do think they can help reduce the burden of looking over data and help humans to better focus their analysis in certain areas. A symbiosis of computing software using AI, data, and a human to closely watch it is what I think will really help us become even more efficient and focused in our particular organization.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.