Voice of the DBA

Author: way0utwest

  • Finding Tabs in SSMS–#SQLNewBlogger

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

    Someone posted this as a question and I thought it was worth noting. I use SQL Prompt for formatting, and never worry if there are tabs in code, but I know there are people without this amazing tool.

    I added some tabs to a script and want to replace them.

    2018-05-04 09_50_15-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (60))_ - Microsoft SQL Serv

    I hit CTRL+H for the search and replace toolbar. The tab character is represented by a \t in a regular expression. I enter that, and then enter 4 spaces in the replace text box. I do need to click the “Use Regular Expressions” button, which is the one my arrow is on in the image below. It’s a little box with an asterisk in it

    2018-05-04 09_51_39-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (60))_ - Microsoft SQL Serv

    When I do that, tabs are highlighted in SSMS.

    2018-05-04 09_53_32-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (60))_ - Microsoft SQL Serv

    If I click “replace all”, I see 4 replacements, and if I check, the tabs are gone and spaces live.

    2018-05-04 09_51_48-SQLQuery1.sql - (local)_SQL2016.sandbox (PLATO_Steve (60))_ - Microsoft SQL Serv

    SQLNewBlogger

    This is a quick post, an example of what you can do to show you’re building better work habits and learning about your tools.

  • Power BI Desktop on the Mac

    Power Bi is one of the neatest tools that Microsoft has built for data professionals. It allows anyone to build fantastic interactive visualizations that can help tell a story and help someone make decisions. There have been some amazing demo visualizations from Microsoft customers. I’ve seen a predictive maintenance visualization for airplanes, one for diabetes trends and a fun basketball visual. There are also a fantastic set of custom visual controls, quite a few of which are described in Devin Knights blog series.

    If you don’t know anything about Power BI, there are lots of resources, including some amazing tips from the Guy in a Cube. Adam Saxton has an amazing channel and puts together some impressive videos with Patrick LeBlanc. They might be one of the best sources for learning to work with the tools. You might want to spend some time playing with Power BI as I can almost guarantee that you’ll build better reports with this tool than you would otherwise. You’ll impress your customers, clients, and boss with your design work.

    Plenty of people have Mac laptops, especially business people, and for quite some time they’ve had to deal with a lack of native tooling. Certainly there are lots of software ports, and no shortage of web software, but there are always places where a lack of a native solution is problematic. I don’t know that we’ll get lots of software ported to a new OS, but there is a better solution. Containerization can help smooth the way.

    A few years back I met the Kenji Obata, founder of Turbo.net. He was doing some interesting work with containers, and I was fascinated by their work. Since then, they’ve grown and refined their technology, and now they have Power BI for Mac available. As soon as I saw their walk-through video, I wanted to share the experience. It’s pretty amazing, and if I were still running OSX, I’d be looking to use some of the Microsoft tools I want on the native desktop.

    This isn’t really native, but the experience feels native. The use of containerization technology might be debatable for databases, but for client applications, it’s amazing. The way that Turbo.net packages up applications is incredible. I like Turbo, and if you need to use Microsoft client applications on a Mac, you might give them a try. It’s not free, but it’s way better than virtualization and has some really cool features as well. I’m sure lots of non-technical people, especially management, that might build reports would love the experience of running Power Bi Desktop on their Mac.

    They even have SSMS available as a turbo container for the Mac, if you’re interested.

    Steve Jones

    The Voice of the DBA Podcast

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

  • First Wave of Sessions for PASSSummit Now Available

    The first wave of sessions for the #PASSSummit have been released. You can see the list at: http://www.pass.org/summit/2018/Learn/ConferenceSessions.aspx

    I am honored to have a session on the list. This year, I’ll be doing a session called Minimizing the Impact of Data Breaches in Dev and Test Databases. This is a partner session, and I’ll talk about some of the issues with data security in your development area and show some ways that you can mitigate the problems.

    There are some other really interesting sessions on the schedule, such as Ola’s  Analyzing Performance Problems Using XEvents, DMVs & Query Store and Faster SSIS from Andy Leonard.  Check out the complete list and start to find those sessions that will help you in your job. Use that information to help build a case for you to come to the Summit.

    I expect we’ll see releases of more sessions across the next few weeks or months. Keep an eye out for new sessions so you can update your schedule.

  • Anonymisation Confusion

    The GDPR starts getting enforced in a few weeks. It’s been law for a couple years, but the authorities have given companies time to comply. I know various entities are frantically working towards compliance as I keep getting updates to Terms of Service. My company is among them, and we are diligently ensuring we can prove that we aren’t violating any rules. That’s good because I’m sure fines will reduce any bonus we might earn this year.

    As I’ve been reading over the law and talking with customers, I’ve learned quite a bit. Redgate Software builds products to help with compliance and we’re updating guidance and information on how to work with data. As I’ve helped to update information and explain concepts I keep running into the term “pseudonymisation”. If you listen to the podcast, you’ll probably hear me struggle to pronounce it, but more importantly, I was initially confused about what this actually meant.

    The Data Protection site from Ireland has a great description of how this differs from anonymisation. You can read through the document, but anonymisation means that the data can’t be some how reverse engineered to find the original data. In terms of privacy, an anonymised set of my data wouldn’t allow anyone to determine the data is about me.

    If the data is pseudonumised, data about me would be replaces with a token, but there might be other means of discovering a data set is about me. As an example, in an eCommerce system, you might have an order key in a dev data set that’s copied directly from production. However, my name would be replaced with something else, like Bob Smith. It’s not apparent that it’s my data, but the protection is limited. If the data were anonymised, the order key would be replaced as well to prevent reverse engineering.

    Many of us have gotten used to being lax with dev and test data, often just restoring from production. It’s handy, convenient, and allows you to find problems in production or verify changes using known values. The downside of this is that we have poor data security. There are no shortage of data breaches from dev and test systems. Certainly plenty of data has been lost from developer laptops as well. Even if you had your laptop encrypted, there’s no real excuse for using real data in less secure environments.

    We need to learn to use anonymised data, and become comfortable with the idea of working on secure data sets. That also means we need the skills to ensure we build good, useful, valid datasets with production-like characteristics.

    Steve Jones

    The Voice of the DBA Podcast

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