Streaming replication with PostgreSQL 9.6, 10 and 11 - PostgreSQL Standby Databases
Implementing a PostgreSQL 9.6 streaming replication on Linux Ubuntu 18.04 servers is very easy.
In the streaming replication mode, the standby connects to the primary, which streams WAL records (Write Ahead Log) to the standby as they're generated, without waiting for the WAL file to be filled. Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping.
The standby server can be in read only mode for reporting purposes for example.
A replication slot ensures that the needed WAL files for the standby are not removed in the primary server before the standby server processes them. Multiple replication slots can be defined depending on the number of the standby servers.
PostgreSQL 9.6 - Quick setup and installation on Ubuntu 18.04
A PostgreSQL 9.6 instance is installed and customized (port, database and configuration files…) on Ubuntu 18.04.
A quick guide for creating a PostgreSQL database and managing this database with pg_ctl (start, stop, restart…)
Microsoft SQL Server 2016, using dbcc clonedatabase
A new feature has been introduced in SQL Server 2012 : dbcc clonedatabase. This feature has been enhanced in SQL Server 2016 (supported objects).
How does it work and what is the purpose of this new feature ?
dbcc clonedatabase is very useful to diagnose compatibility level impact on queries in a production environment with real time statistics without modifying directly and immediately the compatibility level of the source database, modification that can generate further issues and must be conducted cautiously.
SQL Server 2019 on Linux, configuring SSL connections
Modifying the port of SQL Server installed on a WAN/DMZ area is not enough from a security point of view, it decreases potential attacks but does not prevent some sniffer tools from reading TCP packets.
How to encrypt the packets with SSL / TLS 1.2 for SQL Server on Linux ? Steps are easy with openssl and mssql-conf utility.
The encryption is initiated and forced by the SQL Server engine using a self-signed certificate.
Changing the SQL Server 2019 port on Linux and using SQL Server aliases on the client hosts
Microsoft SQL Server located in a WAN/DMZ area and running on its default port 1433 is vulnerable to attacks.
In this article the port of SQL Server 2019, installed on Linux Ubuntu, is modified with the utility mssql-conf.
The (automated) deployment of SQL Server aliases on client hosts when changing the server port drastically reduces negative impacts.
Troubleshooting a startup issue of SQL Server 2019 on Ubuntu linked to user root action error
ALWAYS start the SQL Server engine on Linux with the mssql account. NEVER run the sqlservr binary directly as root.
Indeed, the engine can not then be restarted normally with the account mssql.
The diagnosis is not as simple as usual in this kind of problem, permissions are altered for hidden binary system files of SQL Server (security.hiv…).
Managing SQL Server 2019 service on Ubuntu with systemctl
In the default architecture, SQL Server 2019 runs with the account mssql.
When trying to stop/start/restart the service mssql-server with systemctl, the password is prompted. We may want to allow mssql to manage its own service without password prompts.
2 solutions are available : defining mssql-server as a user service with the account mssql or granting rights with no password on the system service mssql-server to the user mssql through sudo.
Microsoft SQL Server 2019 on Ubuntu - SQL Server Agent
And the SQL Server Agent on Linux? Yes, BUT…
If the configuration and activation of the SQL Server Agent are very simple with mssql-conf, controlling it is much less. On Linux, the SQL Server Agent is not associated with a dedicated service like on Windows with significant consequences.
Microsoft SQL Server 2019 on Linux Ubuntu 18.04, installation and configuration
Who would have believe it in 2009, 10 years ago, Microsoft SQL Server 2019 on Linux platforms !
On Ubuntu 18.04, a SQL Server 2019 instance is installed and configured with mssql-conf. Since the majority of companies implement firewalls to prevent servers from communicating with the outside world, installations are performed in offline mode with the *.deb packages provided by Microsoft.
Database files are not created in the default directory.
After the installation, 2 tests are first carried out: restoration on SQL Server 2019/Linux of a database from its SQL Server 2016/Windows backup and a table export/import using bcp in native mode from SQL Server 2016/Windows to SQL Server 2019/Linux.
These 2 tests will show whether or not it is easy, in this cross-platform context, to migrate databases from SQL Server 2016 on Windows to SQL Server 2019 on Linux.
Ubuntu, securing SSH accesses with the listening port and ufw (Uncomplicated Firewall)
The first three steps to secure a new Ubuntu server, server located in the cloud or not :
- Change the default port for SSH connections.
- Disable direct SSH access using the root account.
- Enable ufw (Ubuntu Uncomplicated Firewall) by allowing incoming SSH connections.
Installing and using Komodo Edit on Ubuntu 18.04
For writing code, vim is an excellent tool on Linux systems, but when you are used to the Active State Komodo Edit tool on Windows platforms, in particular for its project definition features, workspaces… you may want to run Komodo Edit on Ubuntu and transfer the GUI on its Windows 10 client.
Active State Komodo Edit can be installed and run on an Ubuntu system and the GUI transferred to a Windows 10 client using "X11 forwarding". The prerequisites and installation are fairly straightforward.
Ubuntu, how to display server windows on Windows 10 client hosts with Putty and XMing Server
When graphical desktops are not installed on an Ubuntu server, sometimes graphical interfaces are required and must be transmitted and displayed on client machines.
As examples :
- Complex installation programs (Oracle…), the graphical version is often preferred to the console mode and/or silent installations.
- Running Android studio on an Ubuntu server, more powerful, rather than on the client machine which does not meet the hardware prerequisites (memory, CPU, etc.).