PosgreSQL + SQL Server = WiltonDB – Part 1 – Installation

I know,  few years ago, it was uncredible to associate SQL Server & PostgreSQL or see Microsoft in the Open source world. 😉 The world changes and we follow it…

My colleague Daniel Westermann will enjoy that I write a blog about PostgreSQL but also SQL Server…

My first one in 10 years by dbi services but it’s good to begin new challenges!

I will also thanks Daniel to share with me the information and he knows my curiosity… 😉

I write this article to introduce this new Open Source RDBMS  based on PostgreSQL (I don’t believe what I writing) and can accept T-SQL queries…

Ok more seriously, for these first steps, I test the installation, configuration  and how to connect.

WiltonDB uses the extension Babelfish to understand T-SQL queries the query language for Microsoft SQL Server. More information about it here.

You can find more explanations on the dedicated website: https://wiltondb.com/

Let start’s!

The Installation

To install WiltonDB, I download the msi file for Windows here.

I do the installation on a Windows Server 2022, I’m still a Microsoft guy but it’s possible to do it on Linux.

I run the msi file and follow the only 2 steps for the installation:

• Accept the License terms as usual

• Choose the installation folder and I take the default C:\Program Files\WiltonDB Software\witondb3.3

Few seconds later, the installation is finish and you can run the WiltonDB Configuration Tool directly from the installer

The Configuration

The WiltonDB Configuration tool has a lot of Settings that we can configure as you can see in this screenshot.

You can choose also to have only a part of the settings with a already group of settings like Memory only or Networking only

I choose to select the Network Only and it’s more easy to read the settings and change it if needed

You can notice the babelfishpg_tds.port is set to 1433, a known-port in the SQL Server world…

If you change something, push the button reload settings to apply your changes

Tips

if you search in the windows menu, you will not find the WiltonDB Configuration tool. You need to go to the folder C:\Program Files\WiltonDB Software\witondb3.3\bin and run wdb_config.exe

When you run wdb_config.exe, the WiltonDB Configuration tool asks you the connection credential and after you can easily change again the settings.

The default login and password are wilton and test the connection first and load the settings after…

The Queries Test

Now go to test through SSMS (SQL Server Management Studio)

The connection will work only with the 127.0.0.1 and port 1433. Localhost or the computer name does not work…

As you can see, the SQL Server version see by SSMS is 12.0.2000.8

Let’s go with the first configuration scalar function well known in the SQL Server World:

SELECT @@VERSION

Nice result: Babelfish for PosgreSQL with SQL Server Compatibilty – 12.0.2000.8 ….

I run a select of 3 configuration scalar function:

SELECT @@VERSION, @@SERVERNAME, @@LANGUAGE 

and run it on the wiltonDB and to compare to SQL Server, I have installed a named instance inst01

You can see that the servername is BABELFISH and not the real name of the server… ☹

I continue with Metadata scalar function for database object on both instance

I don’t try all but just these two on 2 system databases (master and msdb):

SELECT DB_NAME(), DB_ID()

I continue with the System Catalog Views on the WiltonDB with

SELECT * from Sys.databases 
SELECT * from Sys.configurations
SELECT * from Sys.Logins

We can see that we have 3 system databases (master,msdb and tempdb) like SQL Server. The model is missing…

On the configuration, we have only 7 parameters and as login only the “SQL Login” wilton

Conclusion

I was very curious about it and begin the first steps (installation, configurations & first queries)

I need to say that WiltonDB is easy to install and very fast.

The default configuration is a good start  but need to be more studying to optimize the settings.

I’m very interested to continue to test and see the possibilities of WiltonDB…

See you soon for the next steps of my tests like the security, database creation, restore an existing database,….!