In short, I would agree with your CTO. You’ve probably gained some performance at the expense of scalability (if those terms are confusing, I’ll clarify below). My two biggest worries would be maintainability and lack of options to scale horizontally (assuming you are going to need that).

Proximity to data: Let’s take a step back. There are some good reasons for pushing code into a DB. I would argue that the biggest one would be proximity to the data – for example, if you are expecting a calculation to return a handful of values, but these are aggregations of millions of records, sending the millions of records (on-demand) over the network to be aggregated elsewhere is hugely wasteful, and could kill easily your system. Having said this, you could achieve this proximity of data in other ways, essentially using caches or analysis DBs where some of the aggregation is done upfront.

Performance of code in the DB: Secondary performance effects, such as “caching of execution plans” are more difficult to argue. Sometimes, cached execution plans can be a very negative thing, if the wrong execution plan was cached. Depending on your RDBMS, you may get the most out of these, but you won’t get much over parametrised SQL, in most cases (those plans typically get cached, too). I would also argue that most compiled or JIT’ed languages typically perform better than their SQL equivalents (such as T-SQL or PL/SQL) for basic operations and non-relational programming (string manipulation, loops, etc), so you wouldn’t be losing anything there, if you used something like Java or C# to do the number crunching. Fine-grained optimisation is also pretty difficult – on the DB, you’re often stuck with a generic B-tree (index) as your only data structure. To be fair, a full analysis, including things like having longer-running transactions, lock escalation, etc, could fill books.

Maintainability: SQL is a wonderful language for what it was designed to do. I’m not sure it’s a great fit for application logic. Most of the tooling and practices that make our lives bearable (TDD, refactoring, etc) are difficult to apply to database programming.

Performance versus scalability: To clarify these terms, I mean this: performance is how quick you’d expect a single request to go through your system (and back to the user), for the moment assuming low load. This will often be limited by things like the number of physical layers it goes through, how well optimised those layers are, etc. Scalability is how performance changes with increasing number of users / load. You may have medium / low performance (say, 5 seconds+ for a request), but awesome scalability (able to support millions of users). In your case, you will probably experience good performance, but your scalability will be bounded by how big a server your can physically build. At some point, you will hit that limit, and be forced to turn to things like sharding, which may not be feasible depending on the nature of the application.

Premature Optimisation: Ultimately, I think you’ve made the mistake of optimising prematurely. As others have pointed out, you don’t really have measurements showing how the other approaches would work. Well, we can’t always build full-scale prototypes to prove or disprove a theory… But in general, I’d always be hesitant to chose an approach which trades maintainability (probably the most important quality of an application) for performance.

EDIT: On a positive note, vertical scaling can stretch quite far in some cases. As far as I know, SO ran on a single server for quite some time. I’m not sure how it matches up to your 10 000 users (I guess it would depend on the nature of what they are doing in your system), but it gives you an idea of what can be done (actually, there are far more impressive examples, this just happens to be a popular one people can easily understand).

EDIT 2: To clarify and comment on a few things raised elsewhere:

• Re: Atomic consistency – ACID consistency may well be a requirement of the system. The above doesn’t really argue against that, and you should realise that ACID consistency doesn’t require you to run all your business logic inside the DB. By moving code which does not need to be there into the DB, you’re constraining it to run in the physical environment of the rest of the DB – it’s competing for the same hardware resources as the actual data management portion of your DB. As for scaling only the code out to other DB servers (but not the actual data) – sure, this may be possible, but what exactly are you gaining here, apart from additional licensing costs in most cases? Keep things that don’t need to be on the DB, off the DB.
• Re: SQL / C# performance – since this seems to be a topic of interest, let’s add a bit to the discussion. You can certainly run native / Java / C# code inside DBs, but as far as I know, that’s not what was being discussed here – we’re comparing implementing typical application code in something like T-SQL versus something like C#. There a number of problems which have been difficult to solve with relational code in the past – e.g. consider the “maximum concurrent logins” problem, where you have records indicating a login or logout, and the time, and you need to work out what the maximum number of users logged in at any one time was. The simplest possible solution is to iterate through the records and keep incrementing / decrementing a counter as you encounter logins / logouts, and keeping track of the maximum of this value. It turns out that unless your DB supports a certain sliding window aggregation (which SQL 2008 didn’t, 2012 may, I don’t know), the best you can do is a CURSOR (the purely relational solutions are all on different orders of complexity, and attempting to solve it using a while loop results in worse performance). In this case, yes, the C# solution is actually faster than what you can achieve in T-SQL, period. That may seem far-fetched, but this problem can easily manifest itself in financial systems, if you are working with rows representing relative changes, and need to calculate windowed aggregations on those. Stored proc invocations also tend to be more expensive – invoke a trivial SP a million times and see how that compares to calling a C# function. I hinted at a few other examples above – I haven’t yet encountered anyone implement a proper hash table in T-SQL (one which actually gives some benefits), while it is pretty easy to do in C#. Again, there are things that DBs are awesome at, and things that they’re not so awesome at. Just like I wouldn’t want to be doing JOINs, SUMs and GROUP BYs in C#, I don’t want to be writing anything particularly CPU intensive in T-SQL.

Scalability has nothing to do with where the data sits or how the computation happens. Scalability is all about how you manage global state and data inter-dependence. If your architecture is convoluted with all sorts of data inter-dependencies then it doesn’t matter where you put the code for transforming that data. The inter-dependencies are going to force your hand and reduce any potential for scaling things. If on the other hand your data is loosely coupled and there is very little to no global state then once again it doesn’t matter where the computation happens. Scaling things is going to be much easier.

I’m not sure where your CTO is getting his information about scalability issues but from what you have said it doesn’t sound like he has any real reasons for questioning the current architectural decision other than software fashion trends. Basing architectural decisions on such trends is usually a bad idea.

And we actually did get a huge performance boost.

I think you need to set a performance benchmark and start building your prototype first. Keeping all logic in DB is an old school (imho, i don’t have anything against it) of dealing with client-server architecture. Although, it has its advantages, there are number of drawback that needs to be considered.

Usual approach for this type of salable applications is done through SOA. Because in long run, this is the easiest way to add new client applications to your project.

You have also mentioned triggers. The trigger usage might turn to be a big gotchas later in the application’s support life-cycle, i would be double careful with it, and even try to skip its usage.

Your CTO is 100% Wrong.

Your Financial numbers MUST add up at all times. That means you need ACID and relational DB are the best place to insure that. The Performance gains of NoSql DB are usually at the Expense of ACID and that is OK for Google and Facebook BUT NOT for a system containing financials.

To say that C# performs better than SQL code is also idiocy…

Anytime anyone mentions scalability and Google/Facebook/Twitter/etc, it’s a red herring. Unless you’re providing essentially the same service, what works for them may not be appropriate for you. In general, if you can scale from a single machine to an eight-machine cluster, you’ve probably covered all your bases. Unless you have a hard business requirement to serve 20M page views a day, don’t worry about hyper-scaling. Do what makes sense for your application’s real requirements, and worry about scaling up when it becomes obvious you need to. And don’t forget, most database servers can be clustered too, so just because it’s all in one database doesn’t mean it’s on one server.