Voice of the DBA

Category: Blog

  • Off to Germany

    I’ve been in the UK for almost a week at the Redgate Software offices, meeting with product groups and catching up with the coworkers. It’s been a good visit, but I’m off to SQL Konferenz today. I fly early from London to Frankfurt for the event.

    I’ll be speaking tomorrow on DevOps and databases in the afternoon, so I’ll spend a little time visiting people and checking out pre-cons and a bunch of time practicing and ensuring the presentation is ready to go.

    It will be a fun day in Germany and I’m looking forward to seeing if I can goad a taxi driver to get up close to 200kph on the way from Frankfurt to Darmstadt.

  • New Solutions for Old Problems–T-SQL Tuesday #87

    tsql2sday-300x300This is an interesting T-SQL Tuesday this month. For #87, we have an invitation from Matt Gordon. The topic is using new tools to solve old problems. The “new” cutoff is SQL Server 2014, so we’d looking at a way that the last two versions of SQL Server have helped solve an old problem.

    This is the monthly blog party that picks a topic and has everyone with a blog writing on the topic. You can do that, too. Just add an entry on your blog on Tuesday, February 14. Or start a blog and join in.

    Old Problems

    I’ve got no shortage of those, from my current and past jobs. However, in my current job as editor of SQLServerCentral, we use SQL Server 2008. I’ve got a few problems, but they’d be solved by SQL Server 2012, so those don’t qualify. Perhaps there’s some improvement in SQL Server 2016 with AGs that might work well for us, but I haven’t really looked since the pressing items are SQL Server 2012+ ones.

    However, there is an issue I’ve had in a previous job that was a problem. SQL Server 2016 provides a great solution that I wish would have been available in SQL Server 2005+.

    I once worked for a financial securities company where we had multiple clients in a single database. Each of these clients managed a portfolio of their own, and we stored the data and provided an application that limited their access to sensitive data. We did this with a series of views and procedures designed to check the clientID against the logged in user. The original person designing this has limited database experience, and ended up putting the client ID in almost every table. While that worked OK, it limited flexibility and we had issues when there were two clients from the same company that needed to manage the same portfolio. They’d end up sharing a login because we couldn’t handle flexible security.

    Enter SQL Server 2016 Row Level Security. This would have been a perfect solution as we could have limited the access to data based on the client login, as well as a predicate function that we wrote. Because of the flexibility of writing this function and having it follow the user around without requiring joins to the table being queried, we could have more easily implementing flexible security to rows of data without drastic alterations of our database design.

    Actually, these days I wouldn’t have recommended SQL Server 2016, but rather Azure SQL Database, using small, separate databases for each client, with RLS implemented for the various employees that needed to manage separate portfolios. A simple join table referenced in our security predicate would allow us to limit access without burdening developers to build new views or checks in stored procedures that correctly enforced our security model.

    I think RLS is the best security feature in SQL Server 2016, and while I wish it had been implemented in previous versions, I’m glad it’s been added to SQL Server 2016.

  • The Foundry and Data Masking

    There’s a group at Redgate that investigates new ideas and products. They’re called the Foundry, and they do some cool things. One of those is work on data masking. They’ve got a whole section on the Redgate site. Check it out and see what you think.

    I’ve seen some of their early work in other areas over the years, and it can be interesting to think about future ideas. Some of our products have come out of research, so I’m always looking to see what they’re up to.

    I think data masking is a very useful idea, and you can check out some of what they’re thinking now. It will be interesting to see what comes out of this.

  • Opening the PowerShell ISE from Explorer

    This is a cool productivity trip, and one that I ran into by accident. I had heard that I could launch a command prompt by typing cmd in the address. That works, and it’s cool. It even works with ConEmu, which is my default command window.

    However, while I was looking for other hints, I found that Powershell_ISE also works. That will launch the Powershell_ISE editor with the current folder as the default one in the lower pane. The “open” dialog, is still a the previous location, but I’m not sure how easy that is to change.

    In any case, here’s how this works. Browse to a folder in Exporer, such as a GitHub repo I have:

    2017-01-06 12_13_47-Load-SQL-Saturday-Sessions

    Type “powershell_ise”

    2017-01-06 12_13_56-Load-SQL-Saturday-Sessions

    Hit Enter, and the ISE appears. Note the path in the lower pane.

    2017-01-06 12_14_18-Windows PowerShell ISE

    That’s it.

    Quick, easy, and if you want to get to the ISE quickly to do something, or write some code, this is one way to do from a folder.