MSSQL Database Migration - bcgov/PIMS GitHub Wiki

Migrating MSSQL Database between Namespaces by restoring from backup container

Step 1: Set Up the Target Database

Create the new database in the target namespace using the database deployment config.
Setup an image stream for the official Microsoft SQL Server image.
Create a deployment configuration for MSSQL within your target namespace. This configuration defines how the MSSQL container should run.
Attach a persistent volume to the MSSQL pod, ensuring data persistence even if the pod restarts or moves to a different node.
Specify the mount path as /var/opt/mssql in the deployment configuration, ensuring that the database files are stored persistently.
Add Env variables to Deployment config:
- SA_PASSWORD: Set a secure password for the SA (System Administrator) account.
- DB_NAME: Specify the name of the database you want to create.
- DB_PASSWORD: Define the password for the database user.
- DB_USER: Set the username for accessing the database.

Step 2: Prepare the Source Database A. Navigate to the Source Database Pod in OpenShift

Open Pod Terminal:
- Use the OpenShift Console or local terminal (by using openshift login) and navigate to the namespace containing the backup MSSQL database.
- oc project sourceNamespace
- oc exec -it source-database-pod -- /bin/bash
Check for Daily Backups:
- Navigate to the directory where daily backups are stored and confirm their existence.
- Example:
```
cd backups/daily
ls
```

B. Download the Backup Files

Use oc rsync to Download Backups:
- Utilize the oc rsync command to download the backup files from the source pod to your local machine.
- Example command: Replace "pims-backup-11-2h4qb" with your actual source pod name
```
oc rsync pims-backup-11-2h4qb:/backups/daily/ ./daily
```

C. Upload the Backup to the Target Pod

Switch to Target Namespace:
- Move to your local terminal and switch to the source database namespace.
- oc project targetNamespace
Use oc rsync to Upload Backups to Target Pod:
- Employ the oc rsync command to upload the downloaded backup files to the target pod.
- Example command: Replace "pims-database-1-7f84t" with your actual target pod name
```
oc rsync ./daily/2023-10-26 pims-database-1-7f84t:/var/opt/mssql
```

Step 3: Restore the Backup A. Navigate to the New Database Pod Terminal

Access Pod Terminal:
- Use the OpenShift Console or CLI to navigate to the namespace containing the new MSSQL database pod.
- Example: Assuming your pod is named "new-database-pod" in the "targetNamespace"
```
oc exec -it new-database-pod -- /bin/bash
```
Check the Upload:
- Navigate to the directory where the backup files were uploaded and confirm their presence.
- ```
cd /var/opt/mssql
ls
```

B. Login to the Database Server Using SA Credentials

Use SQLCMD to Login:
- Login to the MSSQL database server in the pod terminal using the SA credentials provided in the deployment config.
- Example:
```
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Pass123!'
```

C. Restore the Backup

Use RESTORE DATABASE:
- Restore the database from the backup file. Modify the path to the backup file according to your directory structure and file names.
- Example: assuming the backup file name to be "2023-10-26/backupFile"
```
RESTORE DATABASE YourNewDatabaseName
FROM DISK = '/var/opt/mssql/2023-10-26/backupFile'
WITH REPLACE; #if the database already existed
```

Troubleshooting Access Issues

A. Login Issues with DB_USER Login

Attempt DB_USER Login:
- Try logging in using the DB_USER login created during the deployment configuration.

Create DB_USER User if Login Fails

CREATE LOGIN DB_USER # replace db_user with the username
WITH PASSWORD = 'abcd'; # use the password and username provided in the deployment config
-- Connect to the database
USE YourNewDatabaseName;

Check Access with Admin Login:
- Attempt to access the database using the newly created admin login.
```
-- Connect to the database
USE YourNewDatabaseName;
```

Error on Access Rights:

If access rights errors occur, log in with the SA account and provide permissions to the “DB_USER“

-- Connect to the database using SA
USE YourNewDatabaseName;
-- Grant necessary access rights to the DB_USER user
EXEC sp_addrolemember 'db_datareader', 'DB_USER';
EXEC sp_addrolemember 'db_datawriter', 'DB_USER';