Skip to content

Migrating Data from PostgreSQL Database to MS-SQL Database

Securden comes with PostgreSQL as the backend database by default. However, if you have an MS-SQL server instance, you can switch to MS-SQL database at any time.

If you have imported accounts or users into Securden while still having PostgreSQL server as the backend database, you are likely to have data stored in the PostgreSQL database. you can migrate all your data to an MS SQL database seamlessly.

Prerequisite: Before proceeding with the steps to migrate the database to an MS-SQL server, take a fresh backup of the PostgreSQL database along with a copy of the active encryption key and store it in a secure location.

Steps to Migrate Backend Database to MS-SQL Server

To change or migrate your database from PostgreSQL to MSSQL, navigate to the /bin and open Change Database.exe as an Administrator.

Specify the Host Name

You can choose to force SSL while securden communicates with the database. If enabled, connections will only be established if a valid certificate exists.

If you want to enable windows authentication, select the checkbox against Windows Authentication. If the user account used to login into Windows has the role db_creator, you can directly authenticate with the SQL server for migration.

If you don’t want to use Windows Authentication, you need to specify the username and password according to the criteria specified in the note section below for authenticating with the MS SQL server.

Click Save if you don’t have any data stored in the PostgreSQL database.

Click Migrate if you want to migrate existing data in the PostgreSQL database to MS SQL database.

Note

When specifying the credentials in the previous step, please follow the below criteria: - If the specified MS SQL server already exists, you need to specify the credentials of a user with ‘db_owner' role in MS SQL.
- If it is a fresh MS SQL server instance, you need to specify the credentials of a user with ‘db_creator’ role in MS SQL.

Important

  1. Once the database is migrated, the key with which the database is encrypted will be renewed. The new encryption key will be found under the name securden.key and the old key will be renamed to securden.key-date. The old database will no longer be accessible with the new encryption key.
  2. Store the old encryption key along with the latest PostgreSQL database backup. In case the old database needs to be restored, you would need a usable backup.
  3. You should change the backup path to a secure location where MS SQL server backup can be saved. Navigate to Admin >> Database Backup to specify the backup location.
  4. You should take a fresh backup of the database and store it in a secure location. The old backups will no longer work due to encryption key mismatch. Verify whether the backup is working fine.
  5. You need to reconfigure your distributed deployment and high availability setups. Since MS-SQL doesn't support Standby server configuration, you need to remove the configured Standby server and add it again as an Application server. Once added, you have to download the application server package, stop the Securden service on the secondary servers and deploy the package again.
  6. Similarly, all your other secondary application servers have to be reconfigured.