Voice of the DBA

Category: Editorial

  • Get-DbaPowerShell

    When I first saw PowerShell, at TechEd 2006 (ish) and I was enamored. This was a much better environment than VBScript for working in a shell. Then I realized I needed -eq, -gt, etc. and was less excited. These language elements felt like a step back. Then I tried to build a restore script for SQL Server databases early on and was much less excited.

    Across the years, I’ve played with various tasks in PoSh as opposed to T-SQL or simple command scripts in various languages, and I find myself going back and forth. PoSh is very useful in some ways, more cumbersome in others, and I still am not sure exactly how I feel about it as a go-to tool. I am coming around to use more PoSh because of the dbatools project, and I find myself considering PoSh instead of T-SQL, especially when I think I may want to work across instances and string together commands.

    I was quite excited recently by T-SQL Tuesday #94, hosted by Rob Sewell. The topic was PoSh, and quite a few people participated, as you can see from the Get-PostRoundup. There are some creative uses of PoSh, such as importing Excel data, administering SSRS, turning Azure VMs off, cleaning up orphaned files, and more. Personally, once you start to use Azure more, PoSh makes the portal seem cumbersome and slow, and I find that for recurring tasks, it’s a much better tool.

    Recently I saw the DBAfromtheCold write a short piece on why you should learn PoSh. It’s a good look at some of the tasks that are easy through PowerShell. Some of these could be done in T-SQL, some with other scripts, but PoSh certainly is a possibility and an easy way to manage some of the tasks you might need to do at scale.

    I don’t think PoSh is the best way to do everything. In fact, I often still lean on T-SQL for many admin things, especially if I already have a connection open in SSMS. However, as I learn more about PoSh and how to use it, I can make those judgment calls about when it’s a better fit. That knowledge helps me become a more capable and stronger data professional, because I have choices about how to work with systems, and I have some knowledge or basis for the decisions I make.

    I advocate continuing to learn throughout your career, and certainly including PoSh as a topic if you work on the Microsoft platforms.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The New New World

    Star Trek Discovery premiered this past week as a new entry in the Star Trek series that has been a part of my life since I was a kid. I was enamored with the idea of exploring the stars, meeting new civilizations while maintaining a strong ethical stance about not interfering with their development. The idea of beaming up to a ship instead of dealing with long shuttle flights seemed like something I’d like, especially as I sat on a long bus ride to and from school for most of my youth.

    Since then I continue to dream about Star Trek technologies, which are still likely as far in the future as I thought as a kid. These days I wouldn’t want to go to space without artificial gravity, and I feel that the HoloLens, which interesting, is still miles away from any sort of Holodeck. Even the advances in speech technology, across many devices and slowly becoming more and more ubiquitous, don’t seem to be anywhere close to the fictional computers on board the starship Enterprise. I still can’t see how a general purpose speech device in a crowded area would work. Unless we all have our own moment to speak our lines.

    There are so many ideas about the future of technology and where we might go. I know that in some ways my imagination of the Jetsons, Dick Tracy, and Star Wars aren’t anything like what we have today, well, maybe Dick Tracy has come true. We are barely in space, we haven’t gotten anyone to another planet, much less explored other galaxies. We have computers that are way more reliable, but software that is maybe not quite as reliable, and certainly VR far behind what many science fiction writers imagined. Communications occur at a rate I’ve rarely seen in fiction, though with a darker overtone than I ever expected or like.

    There are still dreams out there, and plenty of thoughts and views of the future. Some sad ones, some scary ones, maybe some exciting ones. Most views are so far in the future, with plots that are too outlandish, but there are slices and glimpses of technology that could come true. Maybe instead of quick subspace orbital elevators we might get lots of hyperloop transport. Perhaps we won’t have warp drive, but is it possible to get people onto Red Mars?

    I don’t have any great predictions about the future. I tend to see more the downsides and problems that technology can bring us, especially with more data being captured. However, maybe you have some ideas. Can you come up with something that you think our children might experience?

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Problems with Gods

    When I was learning how to work with computers early in life, I ran across various documentation and writings that would liken the root account to being a god on the system. Over the years, I’ve seen other articles that note will describe “God mode” in various software systems. There have even been science fiction books describing the god-like abilities of a person that obtains a privileged account on a system.

    To me, this is one of those places where our industry is immature. Having an account that can perform any task on a system, with no limits, is indeed like a god. This account can do anything, which is a double edged sword. Someone can reconfigure, fix, patch, update anything to ensure the system runs well. Or they can “rm /rf” the system.

    Recently Verelox had their entire system wiped out by a former administrator. There are a variety of problems with this story, not the least of which is leaving old credentials active. Mike Walsh wrote about some of the issues from a backup perspective. I would add from a security perspective that this is why an individual’s credentials need to be disabled immediately, and any well known, long time passwords need to be changed. We do this in the physical world by changing locks. We need to do this in the digital work as well.

    However, I see a overreaching account with unlimited privileges as fundamentally a bad idea. Sure, this makes installing software or reconfiguring our system easier, but perhaps we should be required to use separate accounts for all sorts of options. This is especially true when we build a distributed system across multiple machines. As the number of services and systems increases, the value from having one account able to accomplish every task outweighs the potential issues.

    Humans make mistakes. We make inadvertent ones when we’re tired or distracted. We make malicious mistakes we regret; we make emotional mistakes by overreacting to a situation. We make mistakes based on incorrect information. If we have all the power over a shared system, then we may easily make mistakes that could cause an extraordinary amount of damage.

    Our modern systems should include the ability for a separation of all duties and more default accounts that we configure. At the very least we should separate administration from auditing, and perhaps security as well. A slight inconvenience during setup is worth accidental issues in the future. Having separate accounts for different functions will also help to slow down the potential problems in the future by ensuring no one user account can be used to perform every function on a platform if it’s compromised.

    Steve Jones

    The Voice of the DBA Podcast

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

  • A SQL Server Bug and Data Security

    This week I saw a post from Microsoft Tiger Team on the issues with backup compression and TDE databases. Apparently when they added compression, they didn’t test a few edge cases. Or a few regular cases, like WITH INIT. There is a possibility that your data could be corrupted, which is a major problem. I have found the SQL Server backup and restore capabilities to be very solid over the years, so this is disturbing. If you use TDE, apply SQL 2016 CU7 for RTM or SP1 CU4 ASAP. Test your restores, and be sure you know what you can recover and what you can’t. If Mr. Murphy has anything to say about it, you’ll have an issue soon, so test your restores.

    Overall, SQL Server security is very good, but there are certainly issues with applications and devices that connect to SQL Server. You never know when some item that queries or alters data in SQL Server will cause issues. This week there were a couple notes from Bruce Schneier on FaceID and Bluetooth security, the latter of which has a vulnerability issue. Be sure you are aware of issues here to actually help protect yourself with your devices, but I was amazed to see this piece on infrared camera hacking. A POC on using light to jump air gaps is truly frightening. It seems that truly anywhere that we are processing data, we need to be thinking about security.

    The last few weeks are especially scary for many people, particularly with the Equifax breach. I know they have released numbers on people’s data, but I’d assume that everyone who has ever had credit in the US has a potential issue. I know I am being careful with credit and watching for issues myself. You should, too, and demand better security from companies you do business with. We can improve systems, but it will take more pressure to get companies to put more emphasis on better software and security.

    Steve Jones