It seems like you use a SQL Server database as a dumb storage for the application data.
all the business logic is in the application, the data stored in the format used by application (JSON in you case) and only thing which is required from the SQL Server is to take the data, save it on disk and return it back on request.
iIt was all so smart and brilliant at the beginning, but now, when the data base he's grown, it started to hurt.
the are 2 ways out of it.
1. Hire a SQL Server developer. I mean - a proper one. Knowing the syntax of basic SQL command would not be sufficient.
Let him/her develop a proper relational database, with all the so much hated normalisation in place, implement the business logic on the database level, so there would not need to pump whole the database from the database server to the application server in order to find 10 latest transactions for some account.
Build interfaces between app and db layers, usually in form of stored procedure calls, which gives you a flexibility of implementing changes in different parts of app or db without the necessity of redeploying the whole system.
2. Ditch SQL Server and move to file storage system.
SQL Server is insanely expensive platform for storing JSON files, and it's so much ineffective in terms of overheads involved in the process. For the cost of a license you can buy so much cloud file storage, with all its bells and whistles.
It will also improve performance. Nothing to compare with performance of properly designed relational database, but still much better comparing to what you have now.