I think this topic is more appropriate when it comes to deciding the database system for your application. Vertical scaling refers to scale the database system by providing more hardware resources to the host machine. In the other hand, horizontal scaling refers to scale out the database systems by adding more nodes. Either way the objective is to have better performing database system so that it can serve users fast.
I opened up a discussion with my fellow DBAs and DB enthusiasts and below are some of the views they expressed about the topic.
"I think Horizontal scaling (by adding node/machines) is less challenging. Any way in vertical scaling you will hit a ceiling with CPU memory etc depending on the capacity of the machines used." [Bhanu]
"Vertical scaling is less challenging to implement as it is just to increase the resources of a given machine.in a virtual environment VS becomes more and more easier to implement. However, as Bhanu pointed out, there is a limit where u can reach with VS.
Horizontal Scaling will require more expertise (hence costly), additional software and in some cases, even additional hardware features. Therefore, HS will be more challenging to implement" [Shamil]
"Horizontal scaling is much more align with NoSQL Technologies where as in Relational aren't.
Relational does this through partitioning and there should be some routing mechanism implemented in some where to cater.
My guess is, it is based on the technologies we are trying to implement." [Abi]
"I would say, irrespective of the technology still Vertical Scaling is less challenging to implement. for example, adding more RAM and processor power to a mongoDB box is much easier than implementing mongo sharding.
In the other hand, even relational DB technologies support horizontal scaling. SQL server has AlwaysOn and Oracle has Real Application Clusters. both can do Horizontal Scaling." [Shamil]
Regardless of the DB system, the core concept is to process small chunks of data to achieve better performance. If you consider SQL Server we always try to deal with small chunks of data, when scanning, deleting, updating, inserting, etc. That is because no matter you increase the processing power, the underneath algorithms are not salable. As a result you're forced to deal with small chunks of data to achieve better performance. By adding multi-cores does not resolve this mathematical limitation of algorithms. Then there is additional overhead when it comes to threads management.
In relational db systems, they are designed to handle large monolith databases in a single server. That is the reason we've many TBs of databases in relational systems and we're facing many limitations and challenges with those databases.
When it comes to SQL Server, it has some horizontal scaling solutions like table partitioning and replicas. If you consider table partitioning, it is still within a single machine and you can scale out IO operations within a single machine. (There is an exception to this with the newly released version of SQL Server 2016. A feature called stretched database allows you to partition a table to keep frequently working data set in on-prem and less frequently used data set in cloud.) You can not distribute partitions among many nodes. So still it has a limitation.
If you consider replicas in SQL Server, those can be distributed in many nodes. However replicas are read only so that you can scale out applications reads but still writes are not scalable. In AlwaysOn, writes can be accepted by the primary node only. Consequently SQL Server does not provides true horizontal scaling capability just because of the limitations that I've mentioned above.
In true horizontal scaling, data should be able to partitions and assign to different nodes/machines. This technique is called sharding. Database systems like, MongoDB, Cassandra and DynamoDB all provides this capability. In these database systems, the data can be partitioned and also these partitions can be placed in many nodes which gives the horizontal scaling capability backed by additional CPU and memory power. In other words, CPU and memory can be scale out to other nodes.
The specialty of Cassandra and DynamoDB is, they have masterless architecture which means any nodes of the cluster can accepts reads as well as writes. So there is no single hot spot when it comes to writes as in SQL Server. However, in this architecture has numerous challenges since data is fully distributed among many nodes by using some kind of hashing technique. You can imagine how complex is to handle the concurrency related issues when the your data is distributed and the complexity becomes increase when any nodes can accept the write. These challenges are simply because those DB systems are designed for horizontal scaling.
So in my opinion horizontal scaling is more challenging than vertical scaling. Actually in vertical scaling there is no distinctive challenge.
Hope this is interesting discussion.