Step-to-migrate-from-microsft-SQl-to-PostgreSQL

To migrate the Veeam Backup & Replication configuration database from Microsoft SQL Server to PostgreSQL, create a new configuration backup and restore it on the PostgreSQL instance. This process enables continued use of the same Veeam Backup & Replication server, now connected to a configuration database on PostgreSQL instead of Microsoft SQL Server. In this article, we will learn how to Migrate Veeam Configuration Database to PostgreSQL Server. Please see how to Migrate Veeam MSSQL Database to a new MSSQL Server, and how to uninstall Veeam Backup and Replication from your server.

Default installations of Veeam Backup & Replication 12 use PostgreSQL. However, for existing Veeam Backup & Replication deployments upgraded from version 11a or older. Manual migration to PostgreSQL is required, as they continue to utilize Microsoft SQL Server.

Please see the Complete Guide on TestRail as a Test Management Tool, how to Install PostgreSQL on Windows server as Veeam Database Engine, and how to SSH into a VM created using Azure CLI or GUI.

Why switch to PostgreSQL?

Migrating Veeam Backup and Replication from Microsoft SQL Server (MSSQL) to PostgreSQL is driven by various reasons. Below are some common reasons to consider such a migration

Cost saving: If you prioritize cost savings, choosing PostgreSQL for your database needs is an excellent option. It is an open-source database engine that is completely free to use, even for commercial purposes, under the PostgreSQL license.

Mitigate Vendor Lockin: Migrating to PostgreSQL reduces dependency on Microsoft technologies, This is one reason some firms are utilizing open-source technologies. "I love working with Microsoft Technologies". If this is not the case for you, migrating to PostgreSQL can be advantageous for vendor independence or vendor lock-in.

Scalability: This is also a compelling reason to migrate to PostgreSQL. This database is recognized for its scalability and ability to handle large volumes of data and transactions. If your organization is anticipating significant growth in data volume, migrating to PostgreSQL could provide better scalability and performance.

Note: In SQL Server Express 2022, critical limitations such as utilizing only one socket or four cores, a 1.4GB per instance constraint, and a 10GB database limit, necessitated migration to paid SQL Server versions for larger backup environments exceeding 500 machines. Please take a look at this article for more information. Also, with PostgreSQL, you won’t have any restrictions on CPU and Memory use

Veeam, mindful of its customers, introduced the option to utilize PostgreSQL as a configuration database, enhancing flexibility in database choices.

Cross-Platform Compatibility: Lastly, PostgreSQL is a cross-platform database system that can run on various operating systems, including Windows and Linux. See how to install PostgreSQL on Ubuntu.

Create a Veeam Backup & Replication Configuration Backup

Note: Before performing this step, you are required to be on v12 before migrating to PostgreSQL. It is recommended that you use Veeam Backup & Replication tools to create configuration backups and migrate the configuration database.

Please learn how to upgrade Veeam Backup and Replication v11 to 12.1, how to upgrade Veeam ONE to v12.1, and Dockerizing a NodeJs Express Application.

1: Stop and disable backup jobs

Ensure that you do not initiate or activate any jobs until the migration of Veeam Backup & Replication is finalized. Starting a job before completing the migration process will cause Veeam Backup & Replication to generate a new restore point in the chain and update the chain metadata.

The configuration backup created will lack information about this new restore point. Subsequently, when migrating data from the configuration backup to the database and restarting the job. Veeam Backup & Replication will be unable to synchronise the metadata of the backup chain with the data in the database. Consequently, the job will fail.

2: Create a Configuration Backup

From the main menu, launch the Configuration Backup Settings as shown below

By default, the “Enable configuration backup to the following repository is selected” is selected. If this is not the case for you, please ensure it is selected.

Enable Backup file encryption by selecting the checkbox. Click “Backup Now” to create a new backup as shown below.

This is necessary so that user passwords for the Veeam infrastructure can be exported. Otherwise, you will need to enter all user passwords during the migration.

Let’s check for the backup in the repository if it is truly available. Click Ok to close the wizard.

According t the Veeam documentation, it is pretty safe to ignore the “Loss protection disabled” warning if you do not have Veeam Backup Enterprise Manager installed.

This is because, the backup server is not registered with Veeam Backup Enterprise Manager server. Or your system administrator chose not to enable loss protection functionality.

Migrate the Configuration Database to PostgreSQL

Also from the main menu of the Veeam Backup & Replication console, select Configuration Backup.

In the Restore section, click Restore.

Note: If you encounter an error that the Veeam Data Mover, or Veeam Installer Services aren’t running, please got to services.msc and start them.

In the Restore Mode window of the Veeam Backup & Replication Configuration Restore wizard, select Migrate, and click on Next.

Select the configuration backup file we created above.

Analyse the configuration file selected

Review the contents

On the Target backup page of the configuration restore wizard, select PostgreSQL as the database engine.

PostgreSQL selected as VBR database.

Next, select SQL authentication using the credentials. Then click Connect as shown below. To Learn about this user account, please see how to Install PostgreSQL on Windows server as Veeam Database Engine.

I am okay with the defaults as shown below

The configuration restoration completed successfully. Click next to proceed

Configuration-Restore-Completed-Successfully

Verfify and update restore credentials

Click Finish on the Summary page

Migration-to-postgresql-from-mssql-completed

Lastly, now that the restore process has completed. Ensure sure that PostgreSQL target instance is configured according to the recommended hardware resources values.

You can modify settings of PostgreSQL target instance using the following cmdlet below.

Set-VBRPSQLDatabaseServerLimits

Note: If you set PostgreSQL database as a configuration database when you install Veeam Backup & Replication, the necessary resource capacity will be configured during Veeam Backup & Replication installation.
- If the CPU or RAM resources are changed after Veeam Backup & Replication or Veeam Backup Enterprise Manager installation, you must run this cmdlet again to adjust hardware resources of the PostgreSQL instance.

As recommended, restart the PostgreSQL service to take effect. Below are the various ways to restart a service.