I would suggest taking a serious look at ORM solutions like entity framework and nhibernate.
Then I would suggest drawning up a vision of where you want the architecture to move to over time. stick that on the wall, and tell every developer about. Take their feedback and improve it. And make sure that every developer buys into the vision.
Then to move towards the vision I'd take an iterative and test-driven approach of bringing different parts of the system under test and improving it towards the vision incrementally. For much more on how to approach this part please read Michael Feahters' Working Effectively with Legacy Code.
As I could see from your questions is, that you first should think about what you need and what is NoSql made for.
NoSQL - made for huge amount of distributed data. Scales well from performance an amount of data. and depending in the Type of NoSQL system you can just put and get objects very fast, or also do long running jobs on the distributed data, it's not made for fast search/queries.
Search - it's just made for searching references to data fast. Depending on system it scales well with data and performance. its not made for query huge amount of data nor data relations.
RDBMS - it's made to store data which have relations to each other. System scales depending on system itself and based on your data design. means even the fastest rdbms solutions could perform out with wrong data/query design. It's not made for fast search on huge amount of data. And scaling is depending of rdmbs product, it's not a feature by default.
So if you want to search data from a huge amount, choose the system that is made for it: Search Engine.
If you want to store huge amount of data, where data needs to be distributed (because of amount) and performance of getting data should be independent from amount of data: choose a NoSQL System.
If you don't have that much data,but data needs to have relations to each other, then choose a RDBMS, and think well of you data design.
If you need a Search which stored the index distributed, combine a search product with distributed storage (filesystem).
If you need a distributed filesystem, have a look at Apache Hadoop.
If you need a NoSQL System like Google Big Table, which is somehow compareable to a RDBMS, have a look at Apache Hbase: hbase.apache.org or Hypertable.
Cassandra is more like Amazon Dynamo. It's distributed, but more far away from rdbms.
and MongoDB, is more close to LotusNotes. It's a good storage for Documents/Objects.
And MAIN POINT to solve the problem, think solution dependent, means: do NOT think like RDBMS when using a NoSQL System, you need to think in that specific NoSQL System (they are all very different from each other).
Best Answer
Here's a partial, but more practical and likely applicable, answer. If follow-on processes only consider files via their metadata entries, then the file upload and the update to the metadata need not be atomic. This means you can't have a process that processes every file in the SFTP directory. It would instead need to fetch the list of files from the metadata table and process each file in the resulting list. Similarly, you can't have a process that checks for a specific file in the directory; it would instead check for an entry in the metadata table.
In this context, you can simply upload the file and, once the upload has been verified, insert a metadata record. If the process fails at any point before the metadata insert commits, you just end up with a harmless orphan file. This assumes that file names are distinct; however, if you always go through the metadata table, the actual file names in the SFTP directory no longer matter, so you can just tack a UUID to the end of them to guarantee they are distinct.
You may want to clean out orphans (particularly as "overwriting" a file in this context simply means orphaning the old file). This can be done in a background process that deletes files older than some time horizon, which can likely be at least 24 hours and quite possibly on the order of months.
If the assumptions don't apply, then something like Christophe's approach becomes necessary.