This is more like an infra-structure problem than JBoss AS related. Let me tell you why I understand it this way.
What I think you really need here is a Database Cluster with Replication. Think of it this way. Imagine instead of two servers you wanted to have a whole farm of Database Servers, let's say 50.
Would you really want to set up your JBoss AS 7 to know every database server? I don't think this is the best choice. What we usually do here is to set up a replication cluster. There are several topologies for that: Master-Slave (Selects are split and Inserts only to master), Master-Master (Selects and Inserts are split but you have a huge problem with duplicate IDs for different data beware!) and an algorithm which is my personal choice which is called GaleraDB where all your nodes can act as a master and replicate changes safely to other nodes. In this case, all you have to do is set up a Load Balancer node using either Apache, HAProxy, even a hardware balancer or switch to point the connections to the node you want to redirect. This way your JBoss installation will only know ONE ip address or hostname, the address of your database balancer.
I am not sure whether this GaleraDB cluster will work for MS-SQL but you sure can refer to this technet link to see whether you can build the same topology around your servers.
Thanks for your inputs/suggestions.
I have no idea about SQL/databases level. So please advice me how to proceed further.
Even though i have distributed load thru Apache load-balancer/hardware balancer, it would be using/pointing multiple nodes of Application servers but at the back-end would be pointing to single DB(Please correct me if am wrong).
So advice me what would be better way to achieve to avoid any downtime(database level and application level as well)