1. Introduction

BlockchainSQL Server is a software system that builds and maintains an SQL Server database of the live Bitcoin blockchain (and other UTXO databases). BlockchainSQL Server runs as a Windows Service and Web Server and GUI-based applications. BlockchainSQL Server can be used as a stand-alone application and/or a background windows service. As an application, several utilities are available to the user to generate databases, scan block files and install the service/web server itself. The self-hosted web-application can also be installed that provides a user-interface into the blockchain data.

2. Hardware Requirements

Type Details
Server LENOVO ThinkSystem SR250
1/1x Xeon E-2236 6C/12T 3.4GHz
1/4x 16GB
8/8x SFF HS
Onboard SW RAID
2x 1GbE
1/2x 450W
3 Yr Ltd Wty
Memory/RAM LENOVO ThinkSystem 16GB TruDDR4 2666MHz (2Rx8, 1.2V) UDIMM for SR250/ST50/ST250
Storage/SSD - System Samsung 870 EVO 500GB 2.5" SATA III
Storage/SSD - Data Samsung 870 QVO 8TB 2.5" SSD SATA3
Operating System LENOVO Windows Server 2022 Essentials ROK (10 core)
Software Microsoft SQL Server 2019 Standard Edition
Microsoft SQL Server 2019 1 User CAL

3. Installation

This section discusses the installation process for BlockchainSQL Server.

3.1 Pre-Installation

Before installation, you will need to:

  • Install SQL Server 2012 or greater
  • Ensure target machine has a 6 - 8 TB volume that will house the databases.
  • Ensure SQL Server's default database paths are configured to the target volume.
ℹ️ For optimal performance it is recommended that:
  • Database volume comprised of SSD physical drives (these can be spanned together).
  • Recommend that SQL Server's temp database is moved to the database volume.
ℹ️ For optimal security ensure you have two usernames
  • service_username: used by the Windows Service to insert latest block data into blockchain database.
  • web_username: used by the Web Server to execute queries over the blockchain database and manage it's own web database.

See Database Security for permissions configuration.

3.2 Generating Databases

In order to track blockchain data in your database, you first need to create the database. BlockchainSQL Server allows you to generate a database directly for testing, development and production scenarios via the Generate Databases tool.

ℹ️ The username used in the generation process can be sa and these credentials are not stored anywhere.
ℹ️ Generation of the blockchain database can also be done within the Installation Wizard in the Web Settings screen.
ℹ️ Generation of the web database is done within the Installation Wizard in the Blockchain Database screen.

3.2.1 Database Security

After generating your databases it is important to secure them. For development scenarios where local database security is not pertinent, the user can simply use the system administrator account sa for both service and web-server database users. For production use cases, the optimal database security configuration is to create two separate database users (named uniquely for your deployment) which will be used by the windows service and web server respectively. In this document, these two users will be named:

  • service_username: used by the Windows Service to insert the latest blockchain data into blockchain database.
  • web_username: used by the Web Server to query the blockchain database and manage it's own web database.

After you've generated your databases as per the above section, create those two users (using your own naming convention) and set their memberships as follows:

service_username on the Blockchain Database

web_username on the Blockchain Database

web_username on the Web Database

3.3 Importing Block Files

After your database is generated and secure, BlockchainSQL Server allows you to import the raw Bitcoin Core data-files. This can be performed by using the Block File Scanner option in GUI.

⚠️ Disabling indexes will allow much faster import and they will be re-enabled after scanning finishes. Since the re-enabling of index's can take a very long time it is recommended this feature be used only when scanning into an empty (or near empty) database not for resuming scanning scenarios on an already 50% or greater scanned database.
ℹ️ Even though the import is much faster than building from the service, it can still take several days to import the full Bitcoin blockchain.
ℹ️ Attached to the header of this document is an example log output for a full successful block file import.

3.4 Installing Service

BlockchainSQL Server allows a background scanning of the latest blockchain into the database. To install this service follow these steps:

3.4.1 Launch Install Wizard

3.4.2 Set Install Location

3.4.3 Create/Select Blockchain Database

3.4.4 Set Node Settings

3.4.5 Set Scanner Settings

3.4.5 Set Web Settings

⚠️ Ensure your databases are secured appropriately as per the Database Security section.

3.5 Using Command Line Installer

BlockchainSQL Server permits installation and uninstallation of the service/web service via the command line as follows.

3.5.1 Command Line Arguments

  install                    Installs the BlockchainSQL Server Windows Service
    --path <value>           Path where to install the BlockchainSQL Server Windows Service
    --start                  Path to install windows service
    --dbms <value>           DBMS that will host the BlockchainSQL database. Options are: SQLServer
    --db <value>             Database connection string for BlockchainSQL database
    --ip <value>             IP address of the Bitcoin Core node
    --port <value>           Port of the Bitcoin Core network protocol (default 8333)
    --poll <value>           Number of seconds between polling node for new blocks (default 10)
    --store_scripts          Scanner should store scripts resulting in very large databaes (default true). Options: True, False
    --maxmem <value>         Maximum megabytes of memory to consume during scanning (default 500)
    --web                    Whether or not to install web application explorer
    --web_port <value>       Whether or not to install web application explorer (default 5000)
    --web_dbms <value>       DBMS that will host the web database. Options are: SQLServer, Sqlite, Firebird, FirebirdFile
    --web_db <value>         Database connection string for web database
    --web_bsql_dbms <value>  DBMS of blockchain database used by web-server (will default to `dbms` argument if omitted)
    --web_bsql_db <value>    Database connection string to blockchain database that web-server will use (will default to `db` argument if omitted)

  uninstall               Uninstalls BlockchainSQL Server Windows Service
    --path <value>        Path where to install the BlockchainSQL Server Windows Service

  service                 Run in service mode

3.5.2 Example: Installing Service and Web Server

Perform a full installation BlockchainSQL Server:

> bsql install --path="C:\Program Files\BlockchainSQL" --dbms=SQLServer --db="Data Source=localhost;Initial Catalog=blockchain_btc;Integrated Security=False;User ID=service_username;Password=service_username_password" --ip= --port=8333 --poll=10 --maxmem=500 --store_scripts --start --web --web_port=5000 --web_dbms=SQLServer --web_db="Data Source=localhost;Initial Catalog=bsql_web;Integrated Security=False;User ID=web_username;Password=web_username_password" --web_bsql_dbms=SQLServer --web_bsql_db="Data Source=localhost;Initial Catalog=blockchain_btc;Integrated Security=False;User ID=web_username;Password=web_username_password"

3.5.3 Example: Uninstalling Service and Web Server

Uninstalling an installation of BlockchainSQL Server:

> bsql uninstall --path="C:\Program Files\BlockchainSQL"

4. Web Interface

If you've installed the web server, a web interface will be available for you to query the blockchain data. To access the web interface, use an address of the form http://localhost:5000 from the local machine on which it is installed. To access the web interface from a remote machine, you will need to configure a reverse-proxy via IIS/IIS express. See Configuring Remote Web Access section for details..

4.1 Query

The BlockchainSQL Server web interface provides users the ability to query the network.

4.1.1 Running an SQL query

4.1.2 Viewing Query Results

ℹ️ After running a query, the results are displayed in the results pane.

4.1.2 Viewing the Database Schema

4.1.3 Saving a Query

ℹ️ After saving your query, the resulting URL can be bookmarked/saved for future reference.

4.1.4 Loading a Template Query

4.1.5 Searching Address/Transaction

4.2 Explorer

BlockchainSQL Server also provides a graphical explorer into the blockchain that allows users to browse blocks, transactions, inputs/outputs and their scripts.

4.2.1 Block Explorer

4.2.2 Transaction Explorer

4.2.2 Transaction Input/Output Script Explorer

5. Configuration

BlockchainSQL Server web interface provides a configuration page available only to administrators.

5.1 Logging on as Administrator

To access the Configpage, you must log on as an adminstrator via the logon form accessible from the top-right Admin link. The default admin username is BSQL_Admin and password BSQL0000.

5.2 Accessing Config page

The configuration page is accessible to logged on administrators via the Config link on the top-menu.

5.3 Changing Administrator Credentials

The admin username and password are configured in the appsettings.json file found in the web sub-directory where the service was installed (e.g. C:\Program Files\BlockchainSQL\web\appesttings.json). They can be changed by updating the appsettings.json file.

5.4 Configuring Remote Web Access

5.4.1 Installing IIS

Run the following commands in a PowerShell (Admin) window:

Install-WindowsFeature -name Web-Server -IncludeManagementTools
cd d:\temp

5.4.2 Downloading And Install URL Rewrite

Run the following commands in a PowerShell (Admin) window:

curl -OutFile rewrite_amd64_en-US.msi
msiexec /qb /i rewrite_amd64_en-US.msi
rm rewrite_amd64_en-US.msi

5.4.3 Downloading And Install Application Request Routing (ARR)

Run the following commands in a PowerShell (Admin) window:

curl -OutFile requestRouter_amd64.msi
msiexec /qb /i requestRouter_amd64.msi
rm requestRouter_amd64.msi

5.4.4 Installing IIS IP And Domain Security

Run the following command in a PowerShell (Admin) window:

Enable-WindowsOptionalFeature -Online -FeatureName IIS-IPSecurity

5.4.5 Setting Up IIS Reverse Proxy

  • Launch Internet Information Services (IIS) Manager, found under Windows Administrative Tools, from the Start Menu (Picture 79).
  • Click the > arrow next to the server in the left-hand column.
  • Click the > arrow next to "Sites" in the left-hand column.
  • Click "Default Web Site" in the left-hand column (Picture 80).
  • Double-click "URL Rewrite" (Picture 81).
  • Click "Add Rule(s)..." in the right-hand column (Picture 82).
  • Click "Reverse Proxy" then click "OK".
  • Click "OK" in the dialog box to enable Application Request Routing (ARR) (Picture 83).
  • Under "Inbound Rules" enter as the server to be forwarded to (Picture 84).
  • Under "Outbound Rules" (Picture 84):
    • Tick the "Rewrite the domain names of the links in HTTP responses" box.
    • Enter the server name or fully qualified domain name (FQDN) if available under "To:".
  • Click "OK" .

6. Troubleshooting

BlockchainSQL Server provides various ways to troubleshoot and diagnose issues.

6.1 Accessing the Windows Service

The BlockchainSQL Windows service can be accessed via the Services management console.

6.2 Log Files

Log files are kept in %PROGRAMDATA%/BlockchainSQL Server/Logs/ directory. Log files are capped to 1MB size and only 10 per application (service/web).

6.2.1 Event Log

In addition to log files, errors are logged in the Windows Event Log from BlockchainSQL Server source.

ℹ️ Events from .NET Runtime may be related to the BlockchainSQL, in particular errors relating to the Kestrel web server used host the Web Interface.

6.3 Query Governor

If Query Governor is enabled on your SQL Server instance then the BlockchainSQL Server may throw a resource exhaustion error. You can configure the Query Governor in the Server properties page, under Connections. BlockchainSQL Server will attempt to by-pass the query governor for most queries except user queries from the web. However, if you are experiencing difficulty you can try raising the query governor limit or disabling it altogether.

[SqlException: The query has been canceled because the estimated cost of this query (11621) exceeds the configured threshold of 10000. Contact the system administrator.]
	HelpLink.ProdName:Microsoft SQL Server
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Sphere10.Framework.Data.DACBase.ExecuteNonQuery(String query) in D:\Development\Sphere10\Framework-v3\src\Sphere10.Framework.Data\DAC\DACBase.cs:line 86
   at BlockchainSQL.DataAccess.ApplicationDAC.ConnectOutpoints(Int64 fromTransactionInputID, Int64 toTransactionInputID) in D:\Development\Sphere10\BlockchainSQL\BlockchainSQL.DataAccess\DAC\PostProcessorDAC.cs:line 90
   at Sphere10.Framework.IEnumerableExtensions.ForEach[T](IEnumerable`1 source, Action`1 action) in D:\Development\Sphere10\Framework-v3\src\Sphere10.Framework\Extensions\IEnumerableExtensions.cs:line 391
   at BlockchainSQL.Processing.PostProcessor.PostProcessPartial(PersistResult newPersistSet) in D:\Development\Sphere10\BlockchainSQL\BlockchainSQL.Processing\PostProcessor\StandardPostProcessor.cs:line 29
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at BlockchainSQL.Processing.SequentialBlockStreamParser.Parse(CancellationToken cancellationToken, Action`1 progressCallback, Boolean deferPostProcessing, Nullable`1 pollSleepDuration) in D:\Development\Sphere10\BlockchainSQL\BlockchainSQL.Processing\BlockStreamParser\SequentialBlockStreamParser.cs:line 79
   at BlockchainSQL.Server.BlockchainSQLService.StartScanning(DBReference database, ILogger logger, CancellationToken cancelToken)
   at BlockchainSQL.Server.BlockchainSQLService.OnStart(String[] args)
Core .Net SqlClient Data Provider
Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])

Built with our Notion CMS magic. Reach out to recreate and experience this website excellence!