The pain and agony the two sisters went through (Mary and Martha) in the death of Lazarus, does it mean sometimes God is happy to see us in pain just for His name to be glorified? I want more exposure on how to handle adversities of such nature.

By nature, we see all pain and suffering as bad, even evil. It is always good to rid ourselves of pain or adversities and it is always bad to experience any pain or suffering. However, in the teaching of the Bible, this is not the case at all. The Bible sees the end result as the most important aspect of any experience. Where does it lead us to? That is its value. Its value is not in the experience itself, but in the results that come from the experience. In fact, one of the powers of God is His ability to continually bring good out of what seems to be bad. Listen to the words of Solomon.

Ecclesiastes 7:2-3 [2] It is better to go to the house of mourning, than to go to the house of feasting: for that is the end of all men; and the living will lay it to his heart. [3] Sorrow is better than laughter: for by the sadness of the countenance the heart is made better.
See All... It is better to go to the house of mourning, than to go to the house of feasting: for that is the end of all men; and the living will lay it to his heart. Sorrow is better than laughter: for by the sadness of the countenance the heart is made better.

He sees greater value in sadness for by it the heart is made better. Such a value is not found in happiness and feasting. The Apostle Paul points to the same value in the following passage.

Romans 5:3-5 [3] And not only so, but we glory in tribulations also: knowing that tribulation worketh patience; [4] And patience, experience; and experience, hope: [5] And hope maketh not ashamed; because the love of God is shed abroad in our hearts by the Holy Ghost which is given unto us.
See All... And not only so, but we glory in tribulations also: knowing that tribulation worketh patience; And patience, experience; and experience, hope: And hope maketh not ashamed; because the love of God is shed abroad in our hearts by the Holy Ghost which is given unto us.

He had learned to glory in tribulations because of the value they brought to him. Although this may seem strange to us on the surface, we do understand it in a way. We know that a man who prepares to be a soldier must be pushed through a hard time of training in order to be a good soldier. And we understand that this training may make the difference as to whether or not he survives in a time of war. So, we know that good can come out of painful times.

This principle was put to the test in the life of Paul when he prayed for the removal of the "thorn in the flesh." I do not, as others, try to guess what the thorn in the flesh was. I believe it was left undefined so that we could see its application in many things that we face. But God refused to remove this thorn and told Paul that it was given to enable a greater working of Christ in him. Paul's conclusion is wonderful as you can read below.

2 Corinthians 12:9-10 [9] And he said unto me, My grace is sufficient for thee: for my strength is made perfect in weakness. Most gladly therefore will I rather glory in my infirmities, that the power of Christ may rest upon me. [10] Therefore I take pleasure in infirmities, in reproaches, in necessities, in persecutions, in distresses for Christ's sake: for when I am weak, then am I strong.
See All... And he said unto me, My grace is sufficient for thee: for my strength is made perfect in weakness. Most gladly therefore will I rather glory in my infirmities, that the power of Christ may rest upon me. Therefore I take pleasure in infirmities, in reproaches, in necessities, in persecutions, in distresses for Christ's sake: for when I am weak, then am I strong.

Your particular question refers to Mary and Martha and the death of Lazarus. Jesus said that his death was "for the glory of God, that the Son of God might be glorified thereby" (John 11:4When Jesus heard that, he said, This sickness is not unto death, but for the glory of God, that the Son of God might be glorified thereby.
See All...). So, was Christ unfeeling toward the pain they suffered on this occasion? Not at all. When "Jesus wept" in verse 35, He did not do so for Himself or for Lazarus. He knew that He was getting ready to raise Lazarus from the dead. It is when Jesus saw Mary weeping and the other Jews weeping with her that he "groaned in the spirit, and was troubled" (John 11:33When Jesus therefore saw her weeping, and the Jews also weeping which came with her, he groaned in the spirit, and was troubled.
See All...). And this was the cause of His weeping. He had compassion for their suffering while at the same time He saw a greater purpose to it--it would bring glory to the Lord.

This basic principle is found in many other scriptures. I only hope that this introduction can begin to show you the Father's love even in times of pain and suffering. If you will let Him, He will teach you the precious purpose of pain. I am not saying that we will enjoy the pain or even look forward to it as Paul seemed to do. However, we can at least accept by faith that God has a greater reason for it and will fully repay us for all suffering made for His sake.