For being able to upgrade, you should for sure rely on DNN business layer instead of accessing the database on your own. If you need transactional behaviour, you might need to implement rollback on your own, since afaik DNN objects don't implement the system.transaction.resource interface, i.e. if a createRole fails, you need to undo the previous steps or react appropriately, e.g. by choosing a different name, if a role with same name already exist. Another approach would be checking all prerequisites first and only start your transaction, if you are (mostly) safe, that it can be committed.

Supposing I decide to call the DNN native Stored Proceduress for creating a Role so that I can support transactions (calling DNN Role API methods would be hardly useful as you said they would not support transaction) , then from a best practices perspective, what is advisable?

1) I create methods in the DAL that call the SP to create the role. Then, have my BL class call the methods for creating department & then the Role one after the another. In that case, the BL would need to be aware of the underlying transaction in some way, and would need to request the DAL to commit, if everything is fine.

2) Have my DAL method to create the department perform 2 operations: i) Create department, and then ii) Create Role. In this case, the BL would be totally abstracted from the underlying trasanction, as the DAL can itself decide when to commit or abort.

To me, although the second approach seems more attractive from an implementation point, but logically, the method for creating department should only do that. It is the BL that knows, that creation of department should be followed by creation of a role!!!