Voice of the DBA

Author: way0utwest

  • T-SQL Tuesday #17 – APPLYing Yourself to T-SQL

    TSQL2sDay150x150It’s T-SQL Tuesday again, and this month Matt Velic is the host. His topic this month is the APPLY operator, after a challenge from Adam Machanic that you are not that proficient in T-SQL if you don’t know how to use this operator. I agree with Adam, and I think APPLY was an amazing addition to the T-SQL language.

    If you’re not sure what T-SQL Tuesday is all about, check out Adam’s initial T-SQL idea and post on the monthly blog party. T-SQL Tuesday is the second Tuesday of every month and the host rotates.

    You can also follow T-SQL Tuesday on Twitter with the #tsql2sday hashtag.

    APPLY

    The APPLY operator is one that I wished had been available in SQL 7/2000. There were many times when you were trying to apply a result set to a function and there was no easy way to do this. Most of the time this resulted in some type of cursor/temp table solution to make things work.

    One classic example was in trying to determine the SQL that someone had executed when they were blocking another user. The old sp_who2 gave limited information and often we were query a blocking tree and then start sending SPIDs through dbcc inputbuffer to get an idea of what SQL queries were being run.

    APPLY doesn’t help with DBCC, but it does help in other ways. In a modern twist to this problem, you can take a plan handle and run it through sys.dm_exec_sql_text to get the SQL that was executed

    If I did that for one of the connections I have locally, I could get something like this:

    SELECT *
 FROM sys.dm_exec_sql_text(0x010005003E60AD1C901E7D81000000000000000000000000)

    Which will give you this:

    tsqltues_code2

    Now, if you have a whole list of data, say perhaps a list of everyone connected from sys.dm_exec_connections, you can combine these two together.

    SELECT a.session_id
    , a.num_reads
    , a.num_writes
    , b.text
 FROM sys.dm_exec_connections a
   CROSS APPLY sys.dm_exec_sql_text(a.most_recent_sql_handle) b

    From this, you’ll get some result similar to this one:

    tsqltues_code1

    Note that you can’t join these two items together because this doesn’t work:

    SELECT *
 FROM sys.dm_exec_sql_text

    It returns an error:

    Msg 216, Level 16, State 1, Line 3

    Parameters were not supplied for the function ‘sys.dm_exec_sql_text’.

    You have to pass in a parameter, which means that either you create some cursor or loop to do this, or use the power of APPLY.

  • PAAS

    Will we get a real platform in the clouds or just virtual machines?

    When I hear people talk about the “cloud” I often find that I have a different view of what “cloud” means.  What is a cloud? How is it structured? There are all sorts of definitions, but in my mind I have thought of the cloud as a service I can use, just as I use the web. My browser works with any kind of hardware, and software, as long as it handles the http spec.

    In my mind, that’s what I expect with a cloud database service. It conforms to what I expect in a SQL Server database, allowing me to deploy my data and objects, and expecting that I don’t need to worry about a database name, or configuring Windows logins, or even worrying about scaling up from 4 to 8 CPUs. I’d expect that the cloud would handle this fairly invisibly.

    There’s an acronym called PAAS, which is Platform as a Service, and it’s analogous to SAAS (Software as a service) and IAAS (infrastructure as a service). The idea is that an application platform exists in the cloud and you essentially upload your code, allowing the provisioning, scaling, and other details to be handled by the platform is interesting. This article talks about PASS solving lots of developers’ problems, but I think it’ s a little premature to think that this is the best solution for many types of businesses.

    I do hope that we find a way to smooth the deployment and access for our applications and services for both companies and consumers. It does feel that we spend a lot of time covering up for the inefficiencies in our entire application development and deployment life cycle. The cloud has some promise here, but it has to be more than a Windows host that runs on someone else’s hardware. It has to truly handle much of the complexity and abstract  us away from those details, in a secure, safe way.

    Steve Jones

    The Voice of the DBA Podcasts

  • Close, but no MCM Cigar

    I failed.

    There’s no other way to say it, but I’m not really disappointed or discouraged. I’m actually fairly upbeat about it. I went into the Microsoft Certified Master test thinking that I’d fail because it aims to be a high end certification. I felt like I had a huge knowledge gap going through the study resources on the SQLskills MCM page.

    I plugged away, and learned a lot. I listened to content on long car trips and airplane rides. I read white papers at night and while waiting for kids at various events. I practiced working through some of the code examples, and came up with new ideas along the way. And I got a lot of notes, so many notes that I didn’t even review half of them as the test got closer.

    And I was surprised. As I wrote about my test experience, I think I was well prepared, and I knew more than I thought. Every question made me think and made me deduce some solution, but very few of them had me throwing up my hands and feeling like a complete idiot.

    My score was close to passing. It wasn’t the single digits I joked about on Twitter, nor was it the low 200-300 score I was thinking I might end up with. The passing score is 700, though it isn’t clear if that’s out of 1000 or 800 or 743. We don’t know how the exam is scored, and I’m not sure if that’s good or bad, but that’s a separate blog.

    In my case, I got a 620 with bar graphs that listed my relative strength in a few areas. I scored highest in Scalability and Availability, with my graphs about 8/10 of the way across. In Recoverability and Managability I had about 7/10 of the total distance. That’s not surprising since I think those administrative areas are my strongest skills.

    In security I was about 6/10, which is a little surprising. I thought I knew SQL Server security very well, but I’ve always been a “practical” security guy, and not necessarily conforming to what the MCM might see as the best way to implement security. Definitely need to work on this area a little more. In Performance, I was about halfway, which is probably true. Without spending a lot of time on this in a practical sense in recent years, I’m relying on my memory in studying and not using field-skills.

    Developer Support, which I assume means XML, Spatial, CLR, and other similar code oriented areas I was the lowest, about a 4/10. That’s not surprising since I don’t write a lot of code and really haven’t spent much time in those areas. They feel to be a little outside of the core of what I really enjoy in SQL Server, and I’m sure I didn’t focus here all that well. I know I listened to the SQL CLR prep four or five times and was still slightly confused as to what was covered.

    There were no areas that were incredibly weak, but a few that need improvement, and I know that I have a few things in the manageability to work on after the test. I won’t go into specific areas, but suffice it to say that one particular topic had 3 or 4 questions and I struggled with them talking about a certain implementation of the technology that I hadn’t spent much time on.

    Moving On

    What do I do from here? I was thinking this was a one-shot deal for me and I’d get an assessment of where I stood. I definitely got that, and the results seem to mirror what I would have thought were my strengths and weaknesses. My higher-than-expected score has me wondering a little about which of these is true:

    · I know more than I thought

    · The exam doesn’t test real world skills as much as I thought

    It’s hard to know which is true. I don’t do a lot of real world work, but I read a lot, and I talk with many people about what real world problems and solutions they use. So I have a wide set of knowledge, but not necessarily deep in places. The exam can only test me on the areas it tests me, and with a limited time, there is only so much it can test.

    I have the chance to take the exam again as there are still vouchers available and I think I will. I’d like to try again to pass it since I have a little competitiveness left in me and I thought I was close after taking the exam. If I can improve in a few areas, I’d think I would do better, though I expect different questions and would need to review all the material again.

    The lab? That’s a whole ‘nother story. The lack of practice in some areas might have me fumbling around more than I could afford in a limited time frame. I have less confidence I would pass that, though more than I would have had a few months ago. That’s if I could sit still for 6 hours and focus on SQL Server that long.

  • Why Use the Principle of Least Privilege?

    This SQL Injection could be a problem in data entry

    SQL Injection is not the fault of the SQL Server. Brian Kelley pointed that out, and reminded me that SQL Injection isn’t an case of malformed SQL. It’s legitimate code, including SQL commands that we might use from any query connection, especially an administrative one. We regularly issue update and delete commands from our applications, and SQL Injection takes advantage of this to issue an update the we might not be expecting.

    Would you expect this input handwritten injection from an application? Or this table guessing attempt? You wouldn’t, but they can come through data entry in your application if the input isn’t well sanitized. Someone setting all your prices to $0.01 or all of your customers to “W3 0wnz U!” isn’t what you want to happen. You can’t necessarily prevent all of these patterns  or check for every permutation, but you can prevent things like ‘shutdown’ or ‘drop table’ from being run by your application. Even adding a new user to the database system isn’t something I would want to allow.

    Education is the key here. As Andy Leonard (blog | @AndyLeonard) would say, design patterns are important. When developers have an understanding of the issue, many of these things will be avoided. Having standard ways to begin building an application, checking for bad input, and setting up database users and permissions easily, should make this easy for anyone that wants to code against a database. We still have work to do here to build better frameworks, and ORM tools that require elevated permissions to the database are not the answer. They might become the answer, but they aren’t a better solution right now.

    Grant Fritchey wrote a nice piece about developers and DBAS, noting the need that we both have the same goals, but need to learn to communicate better.  This is one area where we ought to make an effort to communicate better, pass along education about security issues, and work to make life easier for developers to work with a database.

    That also means teaching them to work with the minimum privileges needed in order to make an application work, just in case someone plans on submitting some input you didn’t expect.

    Steve Jones

    The Voice of the DBA Podcasts