Creating and connecting to multiple MonetDB databases in Windows

Question

I am completely new to MonetDB and trying to set it up on Windows. I didn't know how to create my own database, but this thread from 7 years ago helped. Now, I have follow-up questions.

At first, I thought the way to have multiple databases is to create a separate MSQLserver.bat and M5server.bat for each and to pick the right one depending on the db you want to connect to? And that if you want to have both up and connect to them simultaneously, all you had to do was keep both batches running.

Screenshot

Well creating and running separate batches did create separate db's, although I am coming to realize by looking at the content of these batches that all they do to achieve this is create separate directories under %MONETDB%, meaning this is probably something I can do manually (mkdir) and all I need to do in order to create a new database in my farm. Is that correct?

Screenshot

Then, I realized that no matter which copy of the batches I am running, I can connect to both databases via JDBC from DBeaver. (Running both simultaneously actually allows me to connect only to the last one I run.) Unfortunately, I haven't found a way to do this without creating separate connections. I am coming from the SQL Server world where you establish a single connection to a server and once connected, you can access all databases at the same time.

Screenshot

I did find this other post that says I should use different ports for the different databases, but I didn't have to do that. I am connected to both via port 5000 at the same time.

Are all my assumptions above correct? Is there a better way?

I do have a somewhat unrelated question... It seems every database automatically starts with these 7 schemas shown above (json, logging, profiler, sys, etc.) Do people typically create their objects in one of those? sys seems to be the default. Or do they create their own? I created one called "main", but I am assuming a lot here.

Thanks in advance!

Answer 1

I am copying the answer of a colleague who uses MonetDB on windows but is not on StackOverflow:

It is possible to start multiple database servers on Windows. For this it is easiest to make a copy of M5Server.bat file (e.g. M5Server_DBxyz.bat) and edit it (change the creation of a new database directory (from default value: demo) and usage of the new database directory when starting mserver5.exe) It is important to also change the port number, such that each database server process is listening to a different port. The default is 50000. Add the argument --set mapi_port=41000 to the mserver5.exe startup line to specify a different port number.

See https://www.monetdb.org/documentation/admin-guide/manpages/mserver5/ for more info on mserver5 program options.

Next you can start each database server process by starting the different M5Server_DBxyz.bat scripts. Each MonetDB server (mserver5 process) can access only 1 db, not multiple. By default you can also not query data from multiple dbs (aka distributed databases). One alternative is to create a remote table. See https://www.monetdb.org/documentation/user-guide/sql-manual/data-definition/table-definition/#remote-tables If you want to create multiple databases with each having their own tables and using queries which access and join tables from different databases, it much faster/better to create 1 db and in it create separate schemas to create distinct name spaces for the different tables. This way all tables are located in one 1 db, managed by 1 server and queries across tables in different schemas run very fast. FYI: MonetDB does not support a CREATE DATABASE command. Instead use CREATE SCHEMA command.

When you create tables, make sure you do it in the proper schema. With SET SCHEMA myschm; you can change the current schema. So with one connection (e.g. from DBeaver/JDBC/ODBC/pymonetdb) you can connect to only one MonetDB server and thus 1 db. If you want to access multiple databases, you need to create multiple connections, one for each db. This is possible when you write you own program/script and using JDBC/ODBC/pymonetdb API.