Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Use of CLR in SSMS

    Is this the next step to really increase your skills?

    I was watching a piece on how to ETL your data using the merge statement with CLR. Any of your experts utilize this strategy?

    Thanks for any follow ups.

  2. Best Answer
    Posted by Pat Phelan

    "
    Quote Originally Posted by VLOOKUP View Post
    Pat is that not why you test?
    I should have made that point clearer... Out of 1063 CLR based solutions (which all passed from developer sandbox, developer shared, QA, UAT, and then to production) a total of 16 have not had catastrophic failures within 24 months. I define a catastrophic failure as one where the SQL Server Service has an unplanned stop and business data is lost. Nearly all of these worked for 10 days. More than half of them worked for 45 days. Almost 100 of them worked for 500 days. 16 survived their second anniversary without loss of data.

    Many of the CLR related failures were exposed by Microsoft patching, so that when a new security or management feature was added to the SQL Server which hadn't existed before, the CLR code couldn't cope and the server itself failed. The design of the SQL Agent is flexible, and is regression tested whenever Microsoft releases patches. That prevents the kind of unexpected failure which isn't acceptable to me.

    There's a lot of money to be made training people to do unwise things. There's an arsenal and several large equipment manufacturers within 60 miles of my house so I see some spectacular things that make me ask "How did you expect that this was going to work???" when I see them! I also know this because people keep asking me to train them to do unwise things with databases, and I'm involved with many other trainers (see my previous post about SQLPASS and SQLSaturdays in this thread). I'm not saying that I never use the CLR, it is very useful for certain tasks and it is invaluable for triage work when a client implements a system that wasn't designed to scale securely and efficiently.

    If there wasn't a much better way to do what you're describing, I would be more open to doing it. By creating a SQL Agent Job, you can get all of the benefits that you've described and many more for less than 5 percent of the effort. I'm not opposed to using the CLR for fun or for learning, but I'm resistant to using the CLR in production... In my experience, using the CLR is nearly always a symptom of poor system design.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I only use the CLR in SQL Server as a "commando patch" for a really poorly designed application. The CLR can be a useful triage in an emergency, but I see that as packing parachutes on a commercial flight so that you don't need to make as many stops... A very poor way to do things.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Really?

    They were saying for certain task, C# code can offer a solution with 1 - 2 lines whereas the SQL could take dozens of lines of code if not more.

  5. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It's the classic "it depends" answer. It is true for certain workloads and/or applications. Mostly from what I have seen, these revolve around complex math functions and some heavy string operations. I briefly thought about creating an aggregate bitwise OR function, but the need for it has dropped a bit. (Actually, I dropped the project temporarily).

    CLR can carry with it some implications for server security, as you can set some assemblies to be able to access files, or other settings on the local server or even the network.

  6. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by VLOOKUP View Post
    They were saying for certain task, C# code can offer a solution with 1 - 2 lines whereas the SQL could take dozens of lines of code if not more.
    That might be true, but that kind of job should NEVER be done "in process" with SQL Server. That's why I prefer to use PowerShell in SQL Agent Jobs, which gives you a safe, supported, and standard way to handle that kind of tasks. SQL Agent also allows you to proxy credentials, which is MUCH better from a security standpoint.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #6
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Thanks all for the follow up.

    This IMO is a great opportunity to understand the engine the drive the .net framework. Most of the Business Analyst I work with know how to write simple selects but learning powershell and C# would help drive my value to the organization up, and it's interesting!

    I spent years on BA and PM certification, methodologies and IMO they never really helped me get ahead. Knowing how to deliver solutions is where it's at.

  8. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    All of your experience (such as getting BA and PM certification) contributes to your experience. That experience is what gives you value, and what differentiates you from everyone else.

    You are correct, knowing how to accomplish things is learned from experience and it is what makes people valuable. That's true in Boy Scouting, Girl Scouting, Social organizations, business, and just about everything else.

    If you haven't experienced http://www.sqlpass.org yet, you're getting to the point where I think that it will help you. There are quite literally thousands of people around the world who have collectively had millenia of experience and they are willing to share. Dive in, but be prepared... This is truly "drinking from the firehose" if you aren't careful! You don't have to consume the whole thing in a week/month/year/lifetime!

    Thinking about your original CLR question... My real objection comes from the fact that CLR code runs (for the most part) "in process" with SQL Server. If you have a bug in your code, that bug can hang SQL Server. I've seen code run for months or years then fail spectacularly under load, usually at the most critical time for the organization. I've been called in to triage poorly designed systems that failed spectacularly and have had to resort to CLR code to put the wheels back on the bus while we redesigned and rebuilt the offending systems so that they could scale, but that's triage and not good design. Database servers should do database things, application servers should do application/business logic things, browsers or thin apps should do presentation things... This ought to be natural, logical, and the only "correct" solution for a BA!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #8
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat is that not why you test?

    While I know you are serious, there are skilled instructors who spend a lot of time talking about utilizing CLR in SSMS, especially around the ETL space. This seems to be common place, not some rare one off. Just thought I would follow up.

    And yes in our production environment we have a app, web and database server, in dev our app and web are on the same VM, our database is on it's own vm.

  10. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by VLOOKUP View Post
    Pat is that not why you test?
    I should have made that point clearer... Out of 1063 CLR based solutions (which all passed from developer sandbox, developer shared, QA, UAT, and then to production) a total of 16 have not had catastrophic failures within 24 months. I define a catastrophic failure as one where the SQL Server Service has an unplanned stop and business data is lost. Nearly all of these worked for 10 days. More than half of them worked for 45 days. Almost 100 of them worked for 500 days. 16 survived their second anniversary without loss of data.

    Many of the CLR related failures were exposed by Microsoft patching, so that when a new security or management feature was added to the SQL Server which hadn't existed before, the CLR code couldn't cope and the server itself failed. The design of the SQL Agent is flexible, and is regression tested whenever Microsoft releases patches. That prevents the kind of unexpected failure which isn't acceptable to me.

    There's a lot of money to be made training people to do unwise things. There's an arsenal and several large equipment manufacturers within 60 miles of my house so I see some spectacular things that make me ask "How did you expect that this was going to work???" when I see them! I also know this because people keep asking me to train them to do unwise things with databases, and I'm involved with many other trainers (see my previous post about SQLPASS and SQLSaturdays in this thread). I'm not saying that I never use the CLR, it is very useful for certain tasks and it is invaluable for triage work when a client implements a system that wasn't designed to scale securely and efficiently.

    If there wasn't a much better way to do what you're describing, I would be more open to doing it. By creating a SQL Agent Job, you can get all of the benefits that you've described and many more for less than 5 percent of the effort. I'm not opposed to using the CLR for fun or for learning, but I'm resistant to using the CLR in production... In my experience, using the CLR is nearly always a symptom of poor system design.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #10
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Fair enough. And it a wrap!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •