SQL Server 2005: a 3-tier purist's nightmare

SQL Server 2005: a 3-tier purist's nightmare

Summary: SQL Server 2005, now available as a "community technology preview,"  is an odd beast.  For those who believe their databases should be little more than simple data access routines with business logic placed at the "middle tier," the move towards application server technology integrated as part of a database is a Bad Thing.

TOPICS: Data Management

SQL Server 2005, now available as a "community technology preview,"  is an odd beast.  For those who believe their databases should be little more than simple data access routines with business logic placed at the "middle tier," the move towards application server technology integrated as part of a database is a Bad Thing.  The trend is clear.

Oracle integrated the Java VM a while back.  With .NET now fully integrated into SQL Server, the trend towards moving business logic into the database is enough to make the 3-tier purists wake up screaming in the dead of night.

I've never understood the purists.  I tend towards long stored procedures with lots of business logic.  I also tend towards transactions at the stored procedure level.  Databases are a single access point, and it makes sense (at least to me) to try to keep transactions within stored procedure boundaries.

Of course, that's not always possible.  When you are in an enterprise environment, databases are scattered like dandelions on a hillside, so transactions must be managed external to the database.  Likewise, business logic often has cross-database aspects, which means much business logic resides of necessity outside the database.  Lastly, business logic can stuff up high-volume databases, though this might be mitigated when the clustered database Holy Grail is finally reached. (Yes, implementations do exist -- among them Oracle's -- but in my experience, they have problems.)

That doesn't mean that there isn't a role for business logic close to the data.  For one, performance always will be higher when your data and business logic live in the same basic location (versus separated by network boundaries).  Likewise, 3-tier is more important in the fractious enterprise environment, and less important in small- to medium-sized environments.  For companies with the luxury of having smaller numbers of data sources, it's easier, and less error-prone, to manage everything in one place.

Microsoft warns people not to overdo usage of the new CLR features, as it can negatively affect performance.  That makes sense, as the CLR does come with some overhead.  It can speed up certain operations, however, provided careful thought is applied to what operations make most sense to move into the data tier.

From a productivity standpoint, you get a lot of nifty features not possible in traditional database procedural languages, such as custom aggregation functions, or columns that use custom CLR types (classes, in other words).  That's interesting, and serves as a nice middle ground between the flexibility of relational databases and the power of object technology.

Topic: Data Management

John Carroll

About John Carroll

John Carroll has delivered his opinion on ZDNet since the last millennium. Since May 2008, he is no longer a Microsoft employee. He is currently working at a unified messaging-related startup.

Kick off your day with ZDNet's daily email newsletter. It's the freshest tech news and opinion, served hot. Get it.


Log in or register to join the discussion
  • Comingling?

    Globbing everything together so that no one piece can be replicated outside the whole? Is this what's happening here? Sounds like vendor lock-in TIGHT to me!
    Roger Ramjet
    • Vendor lock-in

      Stored procedures are ALREADY proprietary, in Oracle, SQL Server and DB2. At least with .NET/Java IL/bytecodes, you have a chance of taking the code and running it as is in another environment, provided the proper runtime (.NET/Java) exists in the target database.

      You do make a good point, though. If you goal is to reduce dependence on any one database, then moving the business logic as much as possible outside the database makes sense. You WILL, however, pay and performance and developer productivity penalty for that, and bypass some of the interesting features to be foun in modern databases.
      John Carroll
  • SQL Server 2005 Preserves Separation of Code

    Placing the CLR inside SQL Server essentially allows you to leverage the Visual Studio IDE and many of its languages and tools, to create stored procedures, triggers, etc. It doesn?t really introduce an opportunity to place business logic in the SQL Server database. Basically, applications should be ?architected? the way have always been ? having business logic, presentation, data access, and data manipulation tiers. Each tier of an application can be considered a sphere of responsibility, in which code from various spheres employ the services of code from other spheres. Therefore code from an application?s project can elicit code from the presentation tier, to aid in its implementation of the application?s user interface. Similarly, code from an application?s business logic tier (which often operates as a virtual high level analyst) can make high level decisions about the functioning of the application, then solicit code/objects from the data access tier, to manipulate data a certain way. Data access code can then optionally solicit a stored procedure written in C# to implement the manipulation of the data requested by the business tier ? which could be very involved. In the above scenario, the business logic code which solicited the service of a stored procedure object via a data access object, has no idea how the stored procedure object implemented its methods, and it does not care. Therefore in the situation above, you do get good separation of code.

    Stored procedures and triggers and so forth are great in that they use database engines to manipulate data far faster than could be done outside the database. Placing the CLR inside SQL Server now allows (among other things) the creation of these types of code using OOP. Therefore you can now create highly sophisticated code within the database, and you can use ordinary programmers with their C#, VB.Net, and other .Net language skills to create code within the database ? reducing the number of skill sets required for an application project.
    P. Douglas
    • deployment

      I haven't run any betas/CTP's for SQL Server 2005, but to me it seems the desirability of moving SP's/triggers etc into SQL Server's CLR is going to be all about how easy it's going to be to DEPLOY MODS to that CLR code in a running (2005) SQL Server. Where do these DLL's reside exactly - are they free standing or are we talking about some kind of GAC? I just don't get this bit about the CLR residing inside SQL Server when queries could be compiled today into DLL's hence exist outside SQL Server and can reside on the same machine (not that I'd want to do that)! So one does smell a lockin.

      Having watched the latest .NET show, I think MS intends us to port SOME business logic into this CLR, albeit perhaps judiciously!
      • Re: deployment

        [I] Having watched the latest .NET show, I think MS intends us to port SOME business logic into this CLR, albeit perhaps judiciously![/I]

        In a sense, data access and data manipulation (stored procedures, triggers, etc.) tiers are low-level business logic tiers, because they are tiers that specialize in the access and manipulation of data, consistent with understood business logic principles. However, they are distinctively different from the business logic tier, which contains high-level logic concerning how a program should operate and manipulate data. Therefore if you heard something that sounded like accommodations are being made for code in the SQL Server CLR to contain some business logic, it could be a loose reference to the fact that code in the SQL Server CLR will allow you to specifically manipulate data consistent with some [b]low level[/b] business logic.

        [I] So one does smell a lockin.[/I]

        Not necessarily. Stored procedures written in SQL Server 2005 using VS .Net, could also be written in Oracle using whatever means are provided in that database, and an application could access both sets of stored procedures in approximately the same way ? just as they do today. What MS is trying to do, is provide a very compelling and rich tool set for the development of database code and other elements ? much more compelling than what is available from the competition. Of course, developers could develop their code in such a way, so as to meld their applications to SQL Server 2005, so that it is hard to use another database with their applications. I believe however if developers write their code properly, no greater vendor lock than what exists in products currently on the market, need occur.
        P. Douglas
        • procedural logic

          If it's basic data access you use TSQL. If you need to move up from TSQL's relative 'flatness' to provide an element of procedural logic, or you're not comfortable with TSQL, you move into SQL CLR.
  • Welcome to my blog

    This is my first blog post as part of ZDNet, something that I will be doing regularly.
    John Carroll
    • That's good. (NT)

      P. Douglas
  • Thank Goodness

    I'm glad they're moving to .NET CLR languages on SQL Server 2005. I have been tranisitioning skills from a non-MS world on a new job, and learning C# hasn't been bad at all. Then I started working on SQL Server 2000 and was forced to learn some old VB langauge with it's horrible syntax. I'll be happy when I can simply use C# everywhere.