Voice of the DBA

Tag: T-SQL Tuesday

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

  • Please Fix This–T-SQL Tuesday #86

    tsqltuesdayIt’s T-SQL Tuesday time, and this month is an interesting topic. The invite comes from Brent Ozar, and it’s designed to get you to look at SQL Server. I spent some time thinking about this one and getting a post together.

    You can participate as well. Just write a post on your blog, the second Tuesday of the month, link to the invite or leave a comment on their post. If you want to host, contact Adam Machanic.

    Better Checks

    This Connect item was submitted by Erland Sommarskog, and I think it’s one that is worth spending development time on. The title is Add optional checks for more robust development, and I think it’s a great idea. I’m a big fan of testing and ensuring that you meet standards, that silly mistakes aren’t being made in code, and in general, finding ways to improve developer productivity.

    I know that getting errors or failing tests are a pain, but these should drive you to change behavior. If you constantly code “select *”, then you want to get out of the habit. Tests, standards, or other checks are one way to do this.

    This suggestion from Erland, which has quite a few votes, is a good one. Getting some sort of warning from deferred name resolution, is a good thing. Having the ability to prevent, or ignore some of the issues, is important. I’d really like to see all of these checks implemented, with the ability to turn them on/off as needed.

    These don’t take the place of unit tests with something like tSQLt or automated testing, but they are a good start to helping us produce better software.

    If you agree, go vote , and perhaps we can get this implemented.

  • T-SQL Tuesday #85–Cross Version Restores

    tsqltuesdayIt’s T-SQL Tuesday time, and this month Ken Fisher hosts. His topic is backups, and while I’m sure there will be plenty of views on backups, I wanted to touch base on a relatively simple item.

    This is the monthly blog party started by Adam Machanic. Whether you want to write today or at some point in the future, this is a good chance to showcase your knowledge and test yourself on a topic.

    Cross Version Restores

    I would hope that most people know that a SQL Server database backup has a version. This version corresponds to a version of SQL Server, and for the most part, we can’t restore a database backup to an earlier version of SQL Server. Some exceptions might be a similar CU version there the database format hasn’t changed, but certainly not to any prior Service Pack.

    However, can you restore to a later version? Can I take a SQL Server 2012 database backup and restore it to a SQL Server 2016 instance? Sure I can. In fact, lots of people upgrade their systems this way. Install a new SQL Server instance, take a backup on the old version and bring it forward. In fact, you can restore (or attach) a SQL Server 2005 database backup on SQL Server 2016.

    There is a caveat, however. You cannot restore a backup of master, model, or msdb on a newer version. This is called out in the RESTORE command ( https://msdn.microsoft.com/en-us/library/ms186858.aspx).

    That’s interesting, and it means a few things. First, if you really need to upgrade msdb, then you need to upgrade the instance. Does this mean you can’t do a side by side upgrade? No, because you could do this (for 2012 to 2016):

    • Install a new SQL Server 2012 instance.
    • Restore master and msdb.
    • Upgrade to SQL Server 2016.
    • Now attach or restore your SQL Server 2012 databases.

    I wasn’t aware of this fact until browsing BOL. I had assumed I would be able to move msdb and model, though perhaps not master, to a new version.

    That’s a handy piece of information to keep around, and it means that you should always be aware of those objects outside of user databases when you upgrade. Keep scripts handy in a VCS in case you need to create them on a newer version.

  • Growing Speakers for T-SQL Tuesday #84

    tsql2sday150x150It’s T-SQL Tuesday again, and for $#84, we have a non-technical topic. Hopefully this will then let us produce more technical content by growing new speakers. That’s the topic from Andy Yun, and it’s a good one.

    This is the monthly blog party, and you should feel to participate today, or any day in the future. Read Andy’s invitation for the rules.

    No Shortage of Speakers

    When we started SQL Saturday, Andy Warren and I worried about finding speakers for events. In the last 10 years, we’ve seen many, many people begin speaking and enjoy it. I don’t worry about finding speakers for SQL Saturdays anymore.

    However, I do think we could do more to encourage even more people to speak and share knowledge. One of the problems we have in this industry is that we don’t always get information spread out quickly to a wide audience and evolve our practices. Too many of us use our habitual practices long after there are better ways to build software. One of the ways we can work to improve the industry is with more speakers.

    I’ve given a talk on creating your first technical speaking opportunity. I can say that it worked since a couple of people that were in the audience have actually given presentations at the user group. One of the items that I suggest in there is picking something you’ve learned/solved, and then teach it to others in your company/department/group.

    This is often an easier way to get started, since it will be a smaller group, one you know, and it’s safer. It could even be more of a discussion you lead than a presentation.

    Certainly this can be hard as you need to be open to debate, accept you might get some things wrong, and that you might learn something. There is stress in trying to come across as an expert, but I’d rather you try to teach something, with the idea that this is your understanding of a topic, but you are open to admitting you might be wrong if someone else knows more.

    If that’s the case, then perhaps have that individual present next time.

    I’d really encourage more groups and departments to get a weekly presentation together from someone. This can be at lunch, or a standing meeting. This can be a group learning style like the SDTIG, or a quick 20 minute presentation on some topic.

    Just try it.

    Learn, speak, share.

    Get some buy in from your management to support this, and if you need a good reason, use this.

    Corporate Dilemma