Voice of the DBA

Author: way0utwest

  • Dropping a Database Now–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    One of the things I find myself doing often with demos is dropping databases. I may create and drop databases often to try some technique out or practice a skill. I’ve done this for years, going back to SQL Server 4.2, using small databases as quick lab spaces.

    A common problem for me is that often a database is in use, often because of another connection from ADS or SSMS. This is actually a common problem for many people in teams as well, when someone else might be holding an open connection. If you’ve tried to run a simple DROP DATABASE, you know this doesn’t work. In the SSMS GUI, we have a “Close existing connections” option, but nothing with the DROP DATABASE command.

    2020-04-21 11_24_00-Delete Object

    The best solution I’ve found is on StackOverflow. Set the db to single user, which allows you to use the WITH ROLLBACK IMMEDIATE option. This terminates any existing connections and rolls back their transactions.

    Here’s the syntax:

    ALTER DATABASE ClientDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    If I do that, I can then run this, without any issues.

    DROP DATABASE ClientDemo

    SQLNewBlogger

    This is a fairly simple task, and one I’ve done often. I’ve never written about it, mostly because I never took the time. Solving this issue was something that took about 5 minutes of Internet research. I had to check that the DROP command hadn’t added the option. I know this has been discussed, but apparently, never added.

    In any case, since I had to do this for work, I made a new notes, snapped a screenshot, and saved a few links. I do this with OpenLiveWriter. Later I took 10 minutes and typed this up, including this section on how and why.

    This is a quick way to show some knowledge, problem solving, cement learning, and give the next interviewer something to ask you about. Give it a try.

  • Encryption Gets Broken All the Time

    There are always researchers and hackers looking to break encryption algorithms. In fact, there are regularly a series of challenges against the very commonly used RSA algorithm. Recently, the RSA-250 challenge was completed with the algorithm being factored, albeit with a key length of only 829 bits. Most of us would use a 2048 or 4096 key length, so this isn’t that disconcerting. Especially given the amount of time this took.

    The effort took 2700 core-years, and in real time, thousands of cores used for a few months. This was a new record, and one that scientists regularly compete for. There are challenges that are being run to try and help determine just how strong our encryption algorithm are in today’s world. I don’t think many of us have anything to worry about, but if you are still using any lower length RSA key lengths (512 or 1024), you might think about replacing these keys with longer ones.

    In fact, all sorts of algorithms and key lengths have been shown to be insecure, meaning they can be cracked relatively easily. In SQL Server, there are a number of algorithms that have been deprecated for this reason. While most people don’t use the encryption features of SQL Server, some do, and some of you might not realize they are in use in your system. If older algorithms are being used, you should change them as soon as you can. Right now, only the AES algorithms are active, and using any older ones requires a compatibility level of 120 or lower.

    This isn’t to imply that encryption isn’t strong or useful or necessary. It does provide protection, but it isn’t perfect. In fact, just like many organizations don’t rely on just locks; they also use live human patrols to secure their assets. You shouldn’t rely only on encryption along. Audit and monitor your systems for unusual and unauthorized activity, and then take the appropriate action, including revoking access for compromised encryption keys.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Daily Coping 27 Apr 2020

    I’ve started to add a daily coping tip to the SQLServerCentral newsletter, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    Today’s tip was to connect with nature. Breathe and notice life continuing.

    I try to connect with nature often. I’m very lucky I live surrounded by large spaces, and lots of animals. I have learned to appreciate the quiet, the views, and the creatures around me.

    I often do try to stop and take a look around and be grateful when I’m walking around. If I’ve got a dog, it’s easy to get annoyed when they stop to smell something. Much nicer to stop with them, breathe, and smile at the world.

  • Is Now the Time to Learn?

    With many of us shut in our homes, it’s a stressful time. We’re often managing work, family, and the rest of our lives from an unchanging environment. If you’re like me, you may struggle to tell one day from the next. Thankfully I have an app the keeps the date in the corner of my desktop, since I have to check it regularly to remember if it’s Tuesday or Thursday.

    Some of us are busy, or busier, and some not as busy. Stress has changed, and I know quite a few people looking for some break in their routine. At Redgate, we’ve started the Community Circle, which has some technical resources if you’re trying to learn more or level up your skills. Some people are finding this to be a good time to tackle technical stuff, but not everyone feels that way.

    Personally, I am doing a little more, or was before I got really busy at work, but more I need a break. I have been trying to do things away from work, mostly to just change my routine. I also have been doing a bit less overall, mostly because I’m struggling to find more mental energy to focus after work.

    I saw this post from Kenneth Fisher, where he asked the same question and showed that he’s trying to learn to juggle. I have been doing a bit more guitar, which I find relaxing. As the weather improves, I’m looking forward to sitting outside with a drink in the evenings and just learning a new song or playing something old.

    The lack of movement for many of us does mean more time, but it doesn’t necessarily mean you want to focus on learning something. If you do, there are lots of resources, and we’ll keep providing information at SQLServerCentral. If you can’t focus, then maybe you just want to relax and consume something fun. My wife and I have gone through the Oscar Best Picture nominees, one every few days, which has been fun. However, you might try picking up a fun new skill, like juggling. A break trying something new, laughing at yourself, or just getting away from life, might help you cope better.

    Steve Jones