Rendering PDFs Natively in SQL

  • I stand corrected. Please accept my apologies for my phrasing which now appears to me as ungracious. I truly wish your company great success!

    The point I really wish to make is that this architecture has only one scaling solution...scale up.

    Other options allow both scaling up and out. Other individuals have brought out other benefits for continuing to do the work outside of SQL Server, so I won't expand here.

    Again, please continue to post...you did a great job communicating the problem and the solution.

    I post daily at http://www.sswug.org and have found you need to have thick skin. I highly value comments from others. Not everyone is going to agree with you. In fact, your own opinion is likely to change over time and as you have different experiences.

    Cheers,

    Ben

  • I fail to see how this solution prevents tampering with the coupons. Surely if the only access that users have is to the web server this is no safer than building the PDF there?

  • Very good article mate.

    Thanks.

  • Wonderful.

    Share a website with you ,

    ( http://www.ccmalls.net/ )

    Believe you will love it.

    We accept any form of payment.

  • ccmalls26 (5/9/2013)


    Wonderful.

    Share a website with you ,

    ( http://www.ccmalls.net/ )

    Believe you will love it.

    We accept any form of payment.

    Reported as spam.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • As @johannconsult's points out, neither this approach nor any other can eliminate malicious tampering with the output. However, using a PDF does discourage tampering more than some other approaches. For example, serving a JPG and overlaying HTML text client-side would make it easier for a user to tamper with output.

    Comparing apples to apples (i.e. generating the PDF at the SQL Server vs. the web server): tamper-resistance is implementation specific. It would be possible to have a good web server-based implementation...but it would also be possible to have a poor implementation that accidentally exposed undesired data or functionality.

    Some aspects of architecture are subjective, and some decisions are made with an eye on factors such as separation of concerns. In this particular example, I'd prefer not to have to trust the web servers for the integrity of the coupon image. I don't want it to be possible for the web server to accidentally get the wrong serial number on the wrong background image, to serve the same coupon more than once, to expose a means for a user to overlay arbitrary text on a coupon image, etc.

    The approach in the article tries to minimize points of failure, minimize the surface area, and to minimize amount of code and number of platforms to debug. But this approach also simplifies life by not requiring the involvement of the web developer in development, support or maintenance of core business-critical functionality. There is good centralized visibility into exactly what was served, with no opportunity for the user to receive something other than what the database thought the user received. Change control is more easily enforced, yet changes are simpler to make when needed. These are a few of the benefits of this particular architecture.

    The article is clearly not saying that SQL-side PDF generation is best in every case. Yet in this specific example there is an elegance to having all business logic and coupon-generation functionality encapsulated in the database. Yes, this needs to be weighed against other factors such as scalability. There are no free lunches--but there are no one-size-fits-all solutions either. Good architects make appropriate decisions in view of many factors.

    Regarding "scalability": a good architect will understand requirements and make decisions accordingly. Do I need to serve up hundreds of coupons? Thousands? Millions? Am I providing a general service that needs to serve up buzillions of coupons from every manufacturer on earth, or am I serving up coupons for individual small vendors? Does everything need to live in one monster SQL instance, or should each vendor have their own SQL instance? Multi-tenant or single-tenant? Serving directly to consumers, or via API through affiliate partners? and so on...

    I'm always amazed at the number of people that blindly cry "scalability" as some sort of a trump card without understanding the requirements first. Scalability is only one of many factors--and it is not necessarily the most important. Not every application needs to be prepared for an infinite growth in usage. What is more, there are lots of ways to scale--even if the architect decides to centralize functionality in SQL.

    Consider just a few options in this example: farm of linked SQL servers for rendering PDFs (possibly even SQL Express as Ted Cooper-351490 suggested), separate SQL servers for each vendor, pre-render and store batches of unissued coupons, scale up hardware, and many more. This approach is not a "dead-end" street with respect to scalability.

    What's more, until you benchmark and profile the end-to-end solution, you really don't know how resources are consumed. It could be that rendering the PDF is nowhere close to being the limiting factor for performance and scalability. But more importantly, no one who naively cries "scalability" without knowing the requirements really has much to contribute. In the absence of understanding and addressing requirements, that cry is little more than a DBA trying to look out for himself without concern for the overall solution.

    I say this not to be defensive, but to try to be helpful and instructive. Yes, I understand that the role of the DBA is to protect the database...but the database is one element of an overall architecture that exists to serve a business purpose. The more that a DBA (or any individual) can understand and contribute to the ultimate business purpose, the greater value that person can offer.

  • I'm at risk of sounding repetitive I know - sorry but I couldn't resist trying again.

    Here is a link to the Wikipedia definition of 'rendering'.

    It relates to the generation and display of graphic output - not to the generation of files.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • @Phil: 🙂

    Not to belabor the point... but "generation and display of graphic output" sounds like it applies to a PDF...as well as JPG, PNG, HTML, etc. "Rendering" is not format-specific.

    In common usage, web developers talk about "rendering" web page output. Browsers have rendering engines. Sometimes developers even call the process of preparing server-side emitted HTML and Javascript as "rendering".

    I'm sorry you object to the use of the word "rendering" to refer to combining textual + graphical elements and outputting them as a new graphical image...but I think I have used the word appropriately.

    Not to nit-pick, but you say"...not to the generation of files". Nothing in this article has anything to do with files per-se...only a stream of "rendered" graphical data. (The sample test code does save this to a file for demonstration purposes, but otherwise files are not used.)

    I'm not being testy, and this isn't a hill I'd want to die on...but I stand by my usage of the word.

  • David Rueter (5/9/2013)


    @Phil: 🙂

    Not to belabor the point... but "generation and display of graphic output" sounds like it applies to a PDF...as well as JPG, PNG, HTML, etc. "Rendering" is not format-specific.

    In common usage, web developers talk about "rendering" web page output. Browsers have rendering engines. Sometimes developers even call the process of preparing server-side emitted HTML and Javascript as "rendering".

    I'm sorry you object to the use of the word "rendering" to refer to combining textual + graphical elements and outputting them as a new graphical image...but I think I have used the word appropriately.

    Not to nit-pick, but you say"...not to the generation of files". Nothing in this article has anything to do with files per-se...only a stream of "rendered" graphical data. (The sample test code does save this to a file for demonstration purposes, but otherwise files are not used.)

    I'm not being testy, and this isn't a hill I'd want to die on...but I stand by my usage of the word.

    Ah, me neither and I'm happy that you've been so constructive in your response. My sense of 'rendering' is that it must include the action of displaying - so, for example, a JPG is rendered only when it is displayed, not when it is created.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Wonderful.

    Share a website with you ,

    ( http://www.ccmalls.net/ )

    Believe you will love it.

    We accept any form of payment.

  • ccmalls29 (5/10/2013)


    Wonderful.

    Share a website with you ,

    ( **website removed** )

    Believe you will love it.

    We accept any form of payment.

    SPAM reported.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ccmalls32 (5/12/2013)


    Wonderful.

    Share a website with you ,

    ( http://www.ccmalls.net/ )

    Believe you will love it.

    We accept any form of payment.

    Spam reported again.

    Please go away ccmalls.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Design by code (i.e. place the cursor at x,y position, change font etc.) is just too difficult and frankly ancient.

    There are so many better options these days.

    For example, I wrote a freeware project for generating .DOCX, .PDF files etc. using Word templates.

    You can do the design in Word instead of in the code and it allows you to truly any sophisticated document you'd like.

    Also includes support for practically every 1D and 2D barcode in the world, 70+ chart types etc.

    The free project and tutorial can be found at docxfactory.com/tutorial

    Disclosure: I wrote the project.

  • How fast is it? I have to dump reports out from sql to PDF from a payments table, one report per client containing all of their payments.

    I am writing directly to a pdf from SQL Server.

    I get about 2.4 pdfs per second.

  • averykess (8/21/2015)


    How fast is it? I have to dump reports out from sql to PDF from a payments table, one report per client containing all of their payments.

    I am writing directly to a pdf from SQL Server.

    I get about 2.4 pdfs per second.

    I would check what part of this is your bottle neck.

    You could be missing an index that makes the select for the report run longer than it should.

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply