Contents

---

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 XCC Ent 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.

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.

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:

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.

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.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

Commands:
  install                    Installs the BlockchainSQL Server Windows Service
    --path            Path where to install the BlockchainSQL Server Windows Service
    --start                  Path to install windows service
    --dbms            DBMS that will host the BlockchainSQL database. Options are: SQLServer
    --db              Database connection string for BlockchainSQL database
    --ip              IP address of the Bitcoin Core node
    --port            Port of the Bitcoin Core network protocol (default 8333)
    --poll            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          Maximum megabytes of memory to consume during scanning (default 500)
    --web                    Whether or not to install web application explorer
    --web_port        Whether or not to install web application explorer (default 5000)
    --web_dbms        DBMS that will host the web database. Options are: SQLServer, Sqlite, Firebird, FirebirdFile
    --web_db          Database connection string for web database
    --web_bsql_dbms   DBMS of blockchain database used by web-server (will default to `dbms` argument if omitted)
    --web_bsql_db     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         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=127.0.0.1 --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.2 Explorer

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

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 https://download.microsoft.com/download/1/2/8/128E2E22-C1B9-44A4-BE2A-5859ED1D4592/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 https://go.microsoft.com/fwlink/?LinkID=615136
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 127.0.0.1:5000 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.

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.]
Data:
	HelpLink.ProdName:Microsoft SQL Server
	HelpLink.ProdVer:15.00.2000
	HelpLink.EvtSrc:MSSQLServer
	HelpLink.EvtID:8649
	HelpLink.BaseHelpUrl:https://go.microsoft.com/fwlink
	HelpLink.LinkId:20476
StackTrace:
   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)
Source:
Core .Net SqlClient Data Provider
TargetSite:
Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])