Friday, October 14, 2016

Vertical scaling vs Horizontal scaling: Which one is more challenging?

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 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]

My view

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. 


Wednesday, August 17, 2016

DBA role transformation

Traditionally, being a database administrator (DBA) meant minding the store. In an on-premises environment, a lot of a DBA's time is spent attending to mundane administrative, maintenance, and performance optimization tasks, aimed at keeping systems running properly. Such tasks include keeping servers up to date, adding new disk space when capacity is exceeded, and tuning disk I/O. At Microsoft, our DBAs spent about 50 percent of their time on maintenance work.
In Azure, the DBA role has fundamentally changed. Routine administrative, maintenance, and performance management tasks are automatically managed by Azure. This means that our DBA team has free time to deliver more strategic value to the company. The team can invest in more high-value and time-intensive activities, such as data modeling, design, architecture, and code reviews. For example, we have been able to reconstruct our NoSQL data model, and perform more graph modeling.

Thursday, August 4, 2016

How To Create a MongoDB Replica Set

Recently I did setup a MongoDB replica set in a VM hosted in AWS cloud environment. Since I'm totally into SQL Server and Windows, setting up MongoDB replica set was quite challenging and at the same time it was interesting. The biggest challenge was to use the terminal for scripting work. It looks to me, writing commands in terminal was bit cumbersome. Writing some commands in mongo shell with JSON format adds more complexity to the work. I'm not a fan of JSON format. JSON use to be the developer friendly format and not definitely a friend of DBAs.

The concept of MongoDB replica set is quite interesting. You can use replica set configuration to achieve both High Availability (HA) and Disaster Recovery (DR) objectives. It has automatic failover feature too. In replica set, one node is capable to perform write and read operations and it is called a primary node. The secondary nodes can be used for multiple purposes such as DR, analytics, reads, backups, etc. There is a third type of node known as Arbiter. This node is optional and it is used as a tie breaker in election process. Election process is carried out in events which needs to select a new primary. Arbiter is just to participate for the election and it does not hold any data.

Replica set architecture

Replica set has many different architectures depending on the business need. The replica set I created has three nodes, 1 primary and 2 secondaries. Node is also knows as a member. The simplicity of the replica set architecture in MongoDB is, it can be created in a single machine as well. In the below example, I created all three members in the same VM. Each node would reside in separate VMs in typical production deployment. Below diagram illustrate three members replica set in a single VM.


Below are the steps that I carried out to setup the replica set;

Node Provisioning

1. Spin up a VM in AWS cloud. This is quite simple and its free. You can sign up for 1 year free subscription in AWS so that you can mock around it. Use this URL to sign up to AWS cloud.

Connect to the VM

2. Use the public IP to connect to the VM you just created in step #1. You can SSH to the node using below command in Terminal.

> sudo ssh -i "aws/test.pem" ubuntu@ip

The test.pem file contains the private key that is used to authenticate into the cloud VM. You can create this key pair in AWS console and then download the file. In my experiment, I placed the pem file in folder called, aws.

Furthermore, you need to check the pem file permissions. The pem file needs be given the minimum permission (read-only) in order to authenticate with the cloud VM. Follow the below mentioned commands to manage the pem file permissions.

> ls -l test.pem # check the permission of the file.
> chmod 400 test.pem # set the file permission to read only.

Install MongoDB

4. Since this is a  brand new VM, I had to install the MongoDB using the below command.

> sudo apt-get install -y mongodb

Please note the command given in mongodb site does not work. (apt-get install -y mongodb-org)

Configure Replica Set

3. Create three separate folders to maintain replica set files for each node.

> mkdir -p /srv/mongodb/rs0-0 /srv/mongodb/rs0-1 /srv/mongodb/rs0-2 /srv/mongodb/rs0-3

rs0-0, rs0-1, and rs0-2 are the folder names. You can use -p switch with the above command to create these three folders in a single statement. Its awesome, huh!

4. Create three separate mongod instances.

> sudo mongod --port 27017 --dbpath /srv/mongodb/rs0-0 --replSet rs0 --smallfiles --oplogSize 128

> sudo mongod --port 27018 --dbpath /srv/mongodb/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

> sudo mongod --port 27019 --dbpath /srv/mongodb/rs0-2 --replSet rs0 --smallfiles --oplogSize 128

Please note, each mongod service/process has given unique port numbers. These port numbers are later use to connect to each mongod instance. Functional point of view, these mongod instances are somewhat analogous to SQL Server instances because it represent individual server instance which can hold databases. In this example, all three server instances are to be run on a single VM. However in a typical production environment these instances run in separate VMs.

You can also notice --dbpath parameter is given the specific folder name for the each instance. These are the folders that we created in step #3. Additionally it has the replica set name, rs0 which is the same in all three instances because these three instance are for one replica set. The replica set structure can be considered as a cluster environment in SQL Server.

Oplog is the transaction log in MongoDB. So you can specify the oplog size when creating the mongod instances. Creating replica set has lot more parameters other than the ones mentioned above. (Please refer the links given in references section for more detail)

Connecting to mongod instance

5. Once the step #4 complete successfully, you now have three member MongoDB replica set. Next thing is to connect to an instance. For example, we want to connect to 27107 instance.

> mongo --port 27017

Then you will get a prompt specific to the instance you connected. With the prompt itself you can identify whether it is Primary or Secondary. Something like below;


But as of now, we just created mongod processes. We have not yet completed the replica set creation.

Add members to the Replica Set

6. After connecting to the 27017 mongod instance, you can run the below command in mongo shell to add one member to the Replica set.

> rsconf = {
           _id: "rs0",
           members: [
                       _id: 0,
                       host: "localhost:27017"

The preceding JSON document has the minimum information required to add a member to the replica set. You can specify all the members to the same document in members array. What I'm going to do is, to create one member and then add the other two members later.

Below JSON document shows how you add all members at once with some additional parameters.

> conf = {

I had some issues when copy and paste the above JSON style document to the mongo shell. This is the reason I said typing these scripts in mongo shell is very challenging, at least for me. Alternatively you can create a file with above document and then execute the file. Probably that is the easy route to take.

7. Initiate the Replica set

> rs.initiate( rsconf )

This will initiate the Replica set and primary node will be created.

8. Add other two members to the replica set, rs0.

> rs.add("localhost:27018")
> rs.add("localhost:27019")

Note that the port numbers are different for each member.
Now we've completed of adding all three members to the replica set, rs0. You can notice the mongo shell that is connected, indicates as PRIMARY. At this point, the replica set we created is up and running.


9. Below are some useful commands that you can use to manage the Replica set. Returns a basic help for all of the replication related shell functions
rs.conf() Returns a document that contains the current replica set configuration
rs.status() Returns a document with current status information of the replica set
rs.slaveOk() Allows the current connection to allow read operations to run on secondary members


10. Connect to the primary node and insert a record.

> db.people.insert({'name':'Andrew'})
> db.people.find()

You can see the new record exists in primary. Now connect to a secondary node and issue the following command. If your querying the secondary at very first time, then by default you can't view records. You need to run rs.slaveOk() to make it readable.

> db.people.find()


Hope this helps for a beginner who is willing to learn MongoDB.


Sunday, November 25, 2012

Future versions of SQL Server?

It is publicly known fact that RDBMSs have well known limitations when it comes to unstructured data management. This is the reason to emerged new database technologies like NoSQL. It is true that NoSQL is not a replacement for RDBMS. However the time has come to include NoSQL features into RDBMS products (then it can’t be called it as RDBMS) or introduce brand new NoSQL product from RDBMS vendors. We need to wait and see how Microsoft react to these new technology trends and how SQL Server change accordingly. Oracle has already announced their NoSQL version called Oracle NoSQL Database. Will Microsoft come up with new NoSQL product?

Saturday, November 17, 2012

24x7: A pipe dream or reality? And at what cost? Can a business afford not to be 24/7?

Executive Summary

What is 24x7? In simple terms, it refers to “Always On” or “100 % availability”. Meaning the information system is available to users, at any time, without interruption. No matter what the day is. No matter from where users access the system is.
Web definition of 24x7 is stated below;

“24/7 is an abbreviation which stands for "24 hours a day, 7 days a week", usually referring to a business or service available at all times without interruption.”  [1]

The most popular and the technical term used for the same concept is “availability”. As we learn in our software engineering subject at graduate studies, “availability” is one of the non-functional requirements of an information system. To achieve 24x7 availability, an enterprise has to spend money on it. It does not come free, as many end users may think; in long run enterprises get the return on 24x7. [1]

Tuesday, October 11, 2011

Database Sharding

Database sharding is a concept of dividing large databases to small data sets to scale out applications. Following article gives the in-out detail about sharding.