Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Current »

1. Backing up

In this section you will find the process for backing up the MSSQL DB that is running inside a docker container for the Lakehouse Monitor.

1. Stop the containers

First and foremost you’ll need to stop the app and the database:

$ docker compose down

You should see a message similar to

[+] Running 3/3
 ⠿ Container lakehouse      Removed                                                                                                                                       1.1s
 ⠿ Container bplm           Removed                                                                                                                                       0.4s
 ⠿ Network centos_bplm_net  Removed

This tells you that the Lakehouse monitor and the DB are down.

2. Locate the MSSQL data

Now you will need to identify the path on the host system where your MSSQL data resides. Do this by using the following approach:

$ sudo -i
# docker volume ls
DRIVER    VOLUME NAME
local     centos_mssql_data
local     centos_spring_session
# docker volume inspect centos_mssql_data
[
    {
        "CreatedAt": "2023-03-15T07:27:39Z",
        "Driver": "local",
        "Labels": {
            "com.docker.compose.project": "centos",
            "com.docker.compose.version": "2.16.0",
            "com.docker.compose.volume": "mssql_data"
        },
        "Mountpoint": "/var/lib/docker/volumes/centos_mssql_data/_data",
        "Name": "centos_mssql_data",
        "Options": null,
        "Scope": "local"
    }
]
# cd /var/lib/docker/volumes/centos_mssql_data/_data
# ls -la
total 8
drwxrwx---. 7 root  root   73 Apr  5 12:36 .
drwx-----x. 3 root  root   19 Mar 15 07:27 ..
drwxr-xr-x. 5 10001 root   74 Mar 15 07:27 .system
drwxr-xr-x. 2 10001 root   24 Apr  5 12:36 backup
drwxr-xr-x. 2 10001 root 4096 Mar 15 07:27 data
drwxr-xr-x. 2 10001 root 4096 Apr  6 10:10 log
drwxr-xr-x. 2 10001 root   25 Mar 15 07:27 secrets

3. Archive the data

Once in this step, you can create a backup archive of this entire directory using the tar command

# tar zcvf ~centos/mssql-backup.tgz .
./
./.system/
./.system/system/
./.system/system/Temp/
./.system/system/lsasetup.log
....

Note: in my script above I have saved the tgz file in the centos home folder, as such: ~centos/mssql-backup.tgz

Now you need to change the owner of the file so that your user, in my case centos can access the file.

# cd ~centos
# chown centos mssql-backup.tgz

4. Start the containers

You can now leave the privileged session using the exit command and restart the Lakehouse containers

# exit
$ docker compose up -d # make sure you're in the same directory where your docker-compose.yml file is located

The output should look similar to this:

[+] Running 3/3
 ⠿ Network centos_bplm_net  Created                                                                                                                                       0.1s
 ⠿ Container bplm           Started                                                                                                                                       0.9s
 ⠿ Container lakehouse      Started

You can check that the containers are up and running with the following command:

$ docker ps
CONTAINER ID   IMAGE                                                      COMMAND                  CREATED         STATUS         PORTS                                                                                        NAMES
240a0312e897   blueprint.azurecr.io/bpcs/lakehouse-optimizer:20230406.1   "./start-app.sh"         4 seconds ago   Up 3 seconds   2222/tcp, 0.0.0.0:4040->4040/tcp, :::4040->4040/tcp, 0.0.0.0:80->8080/tcp, :::80->8080/tcp   bplm
0287182a2d92   mcr.microsoft.com/mssql/server:2019-latest                 "/opt/mssql/bin/perm…"   4 seconds ago   Up 3 seconds   0.0.0.0:1433->1433/tcp, :::1433->1433/tcp                                                    lakehouse

5. Use scp to download the data

You can use scp to copy the tgz backup to your local machine and from there upload it to S3 or any other cloud storage solution you use.

### On your local computer
$ scp <your username>@<your server host>:<path to mssql-backup.tgz> ./

For example, in my case:

$ scp centos@1.2.3.4:/home/centos/mssql-backup.tgz ./

This will copy the archive to my local computer from which point I can upload it to S3.

2. Restore

In this section you will find the process for using the backup created in the first section to restore the MSSQL DB that’s running inside a docker container for the Lakehouse Monitor.

Assumptions

I’m assuming that you have the mssql-backup.tgz file on your server under the centos user home folder, if not, you need to use scp to copy it from your local machine, so on your local machine you run this:

$ scp <path to tar>/mssql-backup.tgz <your username>@<your server host>:

1. Stop the containers

First and foremost you’ll need to stop the app and the database:

$ docker compose down

You should see a message similar to

[+] Running 3/3
 ⠿ Container lakehouse      Removed                                                                                                                                       1.1s
 ⠿ Container bplm           Removed                                                                                                                                       0.4s
 ⠿ Network centos_bplm_net  Removed

This tells you that the Lakehouse monitor and the DB are down.

2. Locate the MSSQL data

Now you will need to identify the path on the host system where your MSSQL data resides. Do this by using the following approach:

$ sudo -i
# docker volume ls
DRIVER    VOLUME NAME
local     centos_mssql_data
local     centos_spring_session
# docker volume inspect centos_mssql_data
[
    {
        "CreatedAt": "2023-03-15T07:27:39Z",
        "Driver": "local",
        "Labels": {
            "com.docker.compose.project": "centos",
            "com.docker.compose.version": "2.16.0",
            "com.docker.compose.volume": "mssql_data"
        },
        "Mountpoint": "/var/lib/docker/volumes/centos_mssql_data/_data",
        "Name": "centos_mssql_data",
        "Options": null,
        "Scope": "local"
    }
]
# cd /var/lib/docker/volumes/centos_mssql_data/_data
# ls -la
total 8
drwxrwx---. 7 root  root   73 Apr  5 12:36 .
drwx-----x. 3 root  root   19 Mar 15 07:27 ..
drwxr-xr-x. 5 10001 root   74 Mar 15 07:27 .system
drwxr-xr-x. 2 10001 root   24 Apr  5 12:36 backup
drwxr-xr-x. 2 10001 root 4096 Mar 15 07:27 data
drwxr-xr-x. 2 10001 root 4096 Apr  6 10:10 log
drwxr-xr-x. 2 10001 root   25 Mar 15 07:27 secrets

3. Clean the _data dir

You can either remove all of the data or, for extra-precations move it into another directory of your choosing.

3.1 Deleting the data

# rm -rfv * .system

3.2 Moving the data

# mkdir bak
# mv * .system bak
mv: cannot move 'bak' to a subdirectory of itself, 'bak/bak'
# ls -la
total 0
drwxrwx---. 3 root root 17 Apr  6 10:19 .
drwx-----x. 3 root root 19 Mar 15 07:27 ..
drwxr-xr-x. 7 root root 73 Apr  6 10:19 bak

4. Restoring the data

With the destination directory empty, it is now time to bring in the backup data.

# cd ~centos
# tar zxvf mssql-backup.tgz -C /var/lib/docker/volumes/centos_mssql_data/_data/
./
./.system/
./.system/system/
./.system/system/Temp/
./.system/system/lsasetup.log
./.system/system/debug/
./.system/system/debug/PASSWD.LOG
./.system/system/system32/
......

As you can see I used the tar command to extract the contents of mssql-backup.tgz into the _data directory (using the -C flag).

Now you can check that the data was correctly restored using the following approach:

# cd -
/var/lib/docker/volumes/centos_mssql_data/_data
# ls -la
total 8
drwxrwx---. 8 root  root   84 Apr  5 12:36 .
drwx-----x. 3 root  root   19 Mar 15 07:27 ..
drwxr-xr-x. 5 10001 root   74 Mar 15 07:27 .system
drwxr-xr-x. 2 10001 root   24 Apr  5 12:36 backup
drwxr-xr-x. 7 root  root   73 Apr  6 10:19 bak
drwxr-xr-x. 2 10001 root 4096 Mar 15 07:27 data
drwxr-xr-x. 2 10001 root 4096 Apr  6 10:10 log
drwxr-xr-x. 2 10001 root   25 Mar 15 07:27 secrets

5. Starting the containers

You can now exit the privileged bash session and start the containers.

# exit
$ docker compose up -d
[+] Running 3/3
 ⠿ Network centos_bplm_net  Created                                                                                                                                       0.1s
 ⠿ Container lakehouse      Started                                                                                                                                       0.8s
 ⠿ Container bplm           Started

Give it 2 minutes during which time you can check to see that no restarts happen using docker ps

After 2 minutes without restarts you can check the app logs to see that the app started

$ docker logs bplm | grep Started
.....
2023-04-06 10:21:08.024  INFO 1 --- [           main]   scala.App                                                    : Started App in 34.412 seconds (JVM running for 36.438)

6. Clean up of the bak folder

If in step 3 you chose to create the bak folder, it is now safe to delete it.

$ sudo -i
# cd /var/lib/docker/volumes/centos_mssql_data/_data/
# ls -la
total 8
drwxrwx---. 8 root  root   84 Apr  5 12:36 .
drwx-----x. 3 root  root   19 Mar 15 07:27 ..
drwxr-xr-x. 5 10001 root   74 Mar 15 07:27 .system
drwxr-xr-x. 2 10001 root   24 Apr  5 12:36 backup
drwxr-xr-x. 7 root  root   73 Apr  6 10:19 bak
drwxr-xr-x. 2 10001 root 4096 Mar 15 07:27 data
drwxr-xr-x. 2 10001 root 4096 Apr  6 10:10 log
drwxr-xr-x. 2 10001 root   25 Mar 15 07:27 secrets
# rm -rfv bak

  • No labels

0 Comments

You are not logged in. Any changes you make will be marked as anonymous. You may want to Log In if you already have an account.