I’ve recently been thinking about the possibility of saving data from the Internet Computer blockchain into Microsoft SQL Server on Windows Server. It may not be anything groundbreaking, but I’ve been working on it for a few days and managed to connect MS SQL with a simple canister written in Motoko on ICP.Ninja for testing, using the ICP.Net library and PowerShell as the execution layer for a SQL Agent job.
Based on the ICP.Net library, I created a simple .NET program (DappQuery) that enables fairly easy and straightforward communication between backend canister code and SQL, with PowerShell assisting in inserting results into the database.
It’s a fairly simple workaround-style communication, since it just queries a function for a value via a SQL job process, but I wanted to share the solution anyway.
It could probably be done better :D, but this is just a demo of how it can be implemented.
I included a PDF in the repository description explaining how to set it up with MS SQL Server here:
and Powershell script here:
I’m curious how blockchain could be used to integrate with MS SQL functions in an interesting way. This is just a small demo, but who knows maybe it will be useful to someone else who was also thinking about it
Hi, thanks. MS SQL doesn’t run on the canister, only locally on the server not on blockchain. I work with MS SQL quite a lot, and I wanted to check whether I could use ICP to store data and query a canister for that data, so I could pull it from the blockchain into a traditional MS SQL Server. ASP.net Core and dotnet 10 running on ICP That sounds really interesting
This is the kind of thing that would be really interesting for canisters that had discipline around public data interfaces and truly being open internet utilities. It is not practical to serve and index all possible info streams from robust and semi complex canister, so you need off chain indexers. SQL is great for that as indexes are much cheaper off chain than off. These pairs of on chain /off chain combos can become more powerful when you have real witnesses that prove that off chain data matches the onchain verification. DFINITY has been so lax about this approach that it never really developed. I attribute this to their “wave open with one hand while acting closed with the other” culture that was perpetuated when convenient, and it is up to the community and individual devs to reach for the higher commitment to a truly open and transparent culture. Tools that make it easy to harvest all possible data streams and serve them from elsewhere are key. Great work. Some way to systematize what is available and how to retrieve it would be helpful.
Yes I fully agree with the on-chain ↔ off-chain idea.
MSSQL would run locally and store the actual data while a canister would be used only to store history and logs for example database maintenance logs, backup history, trigger alerts, and objects captured by extended events.
It is not meant for storing heavy data itself, because even compressed database backups can be quite large.
Instead, the canister would store auxiliary data for MSSQL and act as a kind of “proof of integrity” layer.
For selected data in the database, we could compute a hash ( using BLAKE3) and store that hash both in the canister and in SQL. For data that must not be changed after insert, this seems like a good approach, especially when SQL is integrated with other systems that depend on this data.
