Docker Operations For MSSQL Server Using Unix Shell

Context

I am currently working on a piece of inherited software from another organization and one of the technologies that’s been used is MSSQL Server 2017. Now we are working more on Unix based OSes and you can imagine that was a very close one given that for the longest time there was no SQL Server for any other OS apart from Windows OS. We now have SQL Server for Linux variations, starting from SQL Server 2017. The trick however is that we don’t have this for Mac OS. This means that if you are using a Mac OS one of ways to get the database service up and running is to use Docker, my reason for saying this is that all Non-Linux hosts use Linux VMs for your Docker containers. Ultimately this means that we can then set up the database service inside a Linux container that’s running in Docker.

That’s fine, we managed to work with this idea when we took over the project mentioned above and the set up works nicely, but one day our team was discussing the pain of backing up and restoring on a MS SQL Server service that’s running on Docker. Some of the challenges include:


Restoration Process

- Having to copy your database backup file over to the docker container.
- Using a client app to locate the file.
- Setting up the restoration options
- After restoration you must may be delete the file from your container to reserve space and avoid duplicate files.


Backup Process

- You backup to the default docker container directory
- Must copy the file from the docker container for safe keeping.
- After you copy you may want to remove it from the container to reserve space on your container.


These can a bunch of tiresome processes when you have to perform them on a daily basis. The idea of scripting also motivates configurations & automation of various processes and, as much as there are many ways of solving a problem, I will share one of the ways we tackled these problems to assist the development team. We will start with the basic configurations then the backup process and then move on to the restore process, also note that we will be using some plain old unix shell scripting. Hopefully you have basic understanding of that.

1.Basic Configurations & Commons

1.1 Variables / Costants

We are going to set a few variables to use later in our script as configuration values. Create a new shell script with the following code:

NB: You can your script anything you prefer, for example I named mine docker-mssql.sh 

DOCKER_MSSQL_SERVER_SHELL_SCRIPT_PATH=${0:a:h} 

# Change these for your environment
MSSQL_HOME_DIR="/var/opt/mssql/data"
MSSQL_CONTAINER_NAME="mssql-server-2017"
MSSQL_SERVER_IP="localhost"
MSSQL_SERVER_PORT="1433"
MSSQL_SA_USERNAME="SA"
MSSQL_SA_PASSWORD="exe1Hou!"

The variable DOCKER_MSSQL_SERVER_SHELL_SCRIPT_PATH stores the current path of the script. So that's the ZShell way of dynamically resolving the script's current path. If you are not using ZShell, but Bash then you can do the same as follows: 

NB: Make sure that you type all this in one line.

DOCKER_MSSQL_SERVER_SHELL_SCRIPT_PATH=$(cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd)

The next step is to try and see the possible common processes, for example when backing up or restoring the database backups, we need to copy to and from the Docker container, so I believe we need a way of copying. We can write two functions that can help us, one will copy To the container and one will copy From the container. We will tailor these functions strictly for this Docker container. These functions will also make use of the Docker copy command. 

1.2 Common Functions

Add / Implement this function in your script : dockerMSSQLCopyToVM()

function dockerMSSQLCopyToVM() {
    sourceFilePath=$1
    docker cp $sourceFilePath $MSSQL_CONTAINER_NAME:$MSSQL_HOME_DIR
}

This function takes one argument at $1. It's the standard shell way of getting parameters. Unlike Java, shell is not Zero based so the parameters start at "1". This argument should be the path to a file, in this case it will be your backup file. We then take this argument and assign it to a new variable $sourceFilePath which then gives it more meaning. Now that everything is in place, we call the "Docker copy" command to copy the given file path to the docker container. According to the Docker standards, when you copy to a docker container you need to specify the {container-name}:{path-inside-the-container} docker. You will notice that to achieve that we are now using our configured variable(s) which we have set up in section "1.1" 


Add / Implement this function in your script : dockerMSSQLCopyFromVM()

function dockerMSSQLCopyFromVM() {
    fileName=$1
    destinationFilePath=$2
    docker cp $MSSQL_CONTAINER_NAME:$MSSQL_HOME_DIR"/"$fileName $destinationFilePath
}

In this function we expect two arguments or parameters. The first one at $1 being the name of the file which you want to copy. This will be the file that's on your docker container, so you want to copy it from the container to your machine. The second parameter at $2 will be the destination path where you want to copy it. 


Add / Implement this function in your script : dockerRemoveFileFromContainer()

function dockerRemoveFileFromContainer() {
    pathOfFileToRemove=$1
    docker exec $MSSQL_CONTAINER_NAME rm -rf $pathOfFileToRemove
}

A reminder that after backing up and restoring, we also want to remove the backup files from our docker container in order to reserve some space on the container. There's no use in keeping it there if you already have it on your host machine. So now we are expecting one argument which is the path to the file you want to remove. To execute a command against a running docker container we use the docker exec command. In this case we want to run a unix rm command to "remove" the file.

 

 

2. Backing Up Your Database

 

2.1 Function Skeleton

In this section we can now look into the core functionality, this is about the backup  process. We will build this up step by step then revise the complete function later. for now, create an empty function, I created mine as follows :

function dockerMSSQLBackupDatabase() {
    
}

2.2 Gathering Backup Process Information

Thinking about this more, we need to take in some information in the form of parameters / arguments. I imagine we will need to know about the "database' name that you want to backup and also may be something like a result "backup file name" prefix so that you can identify the file quickly when you are done. 

So let's fay for our example, to make this more verbose than just a file name and extention we would also want to see the date we created this backup file and achieve a format like FileNamePrefix_2020-03-02.bak. Keep this in mind because we will be using that format. Go to your new empty function and add the following code.

databaseName=$1                                   
backupDate=$(date +%F)                             
fileName=$2"_"$backupDate".bak" 
completeBackupFilePath=$MSSQL_HOME_DIR"/"$fileName

• By now you should be able to follow on our parameter positions, so, in the first argument we expect the name of the existing database on the service. This will be the database you want to back up. We then assign that to a meaningful name, databaseName assign. 
  

• We then use the "Bash Date" to get the current date in a spefic format, so the +%F will format our date as YYYY-MM-DD, which is what we are looking for to make up our desired file name.

• From there we put everything together, including argument 2, which is where we expect the file name prefix. Ultimately we join / put together / concatenate all this to make up our file name format, which is then assigned to a variable named, fileName.
• Finally, we are ready and we build up our full destination file path. This is where the file will be backedup. Using our variable / config at $MSSQL_HOME_DIR, joined up with the fileName variable.

AT this stage we have our information and we can now start the actual work, connecting to our container and loging into the SQL Service so that we can run the SQL Backup statement. 

2.3 Docker & SQL Login To Execute Backup Command

It's now time to revisit our "docker exec" to login into our container, so that we can login into the SQL Service and then run something against the service to backup. This is the core function of our backup process.

docker exec -it $MSSQL_CONTAINER_NAME /opt/mssql-tools/bin/sqlcmd \
    -S $MSSQL_SERVER_IP \
    -U $MSSQL_SA_USERNAME \
    -P $MSSQL_SA_PASSWORD \
    -Q '
BACKUP DATABASE '${databaseName}'
TO DISK = '\'$completeBackupFilePath\''
WITH FORMAT, INIT,
MEDIANAME = '\'$databaseName\'' ,
NAME = '\'$databaseName\'',
SKIP, REWIND, NOUNLOAD,  STATS = 10'

• There we go with the exec command to login into our container. We are also using our constant $MSSQL_CONTAINER_NAME, to specify our container name. 
• Then we have a command /opt/mssql-tools/bin/sqlcmd that's used to to execute the actual SQL Service followed by parameters with options of what we are tying to do, in this case we want to login so that we can execute a SQL command to run the backup process. -S $MSSQL_SERVER_IP, to specifcy the IP of the SQL Service we want to log into; -U $MSSQL_SA_USERNAME, just a username for the database; -P $MSSQL_SERVER_PASSWORD, password for the user and finally the -Q '...', which will be used to carry our SQL Queries & Statment. In this case you will notice that we are using the SQL Backup Command , joined up by our local function variables to make up a complete backup command.
• One small thing you are probably wondering about is the backslashes in our command. That's just the unix shell way of breaking a command that's in a single line, to multiple lines.

2.4 Cleanup After Backing Up

What happens when the backup is done? Remember that we have some utility functions we can use. So we can now clean up nicely, this way we get you manage our backup files much easier and we also clear out the container and researve some space. To do that, first we copy that file from the container to our desired paht. In my case I am copying the file to my downloads folder, as follows :  

dockerMSSQLCopyFromVM $fileName $HOME/Downloads

The next step is to remove the file, as follows :

dockerRemoveFileFromContainer $MSSQL_CONTAINER_NAME $completeBackupFilePath

2.5 Backup Process Conlusion 

We are going to do some touch ups on backing up. This will give us an idea of the whole picture.

function dockerMSSQLBackupDatabase() {

    if [[ -n $1 || -n $2 ]]; then
        databaseName=$1                                    
        backupDate=$(date +%F)                             
        fileName=$2"_"$backupDate".bak"                    
        completeBackupFilePath=$MSSQL_HOME_DIR"/"$fileName

        echo "[ Docker Backup ] Backing up your file to $completeBackupFilePath ..."
        docker exec -it $MSSQL_CONTAINER_NAME /opt/mssql-tools/bin/sqlcmd \
            -S $MSSQL_SERVER_IP \
            -U $MSSQL_SA_USERNAME \
            -P $MSSQL_SA_PASSWORD \
            -Q '
        BACKUP DATABASE '${databaseName}'
        TO DISK = '\'$completeBackupFilePath\''
        WITH FORMAT, INIT,
        MEDIANAME = '\'$databaseName\'' ,
        NAME = '\'$databaseName\'',
        SKIP, REWIND, NOUNLOAD,  STATS = 10'

        echo "[ Docker Backup ] Downloading your $fileName ... "
        dockerMSSQLCopyFromVM $fileName $HOME/Downloads

        dockerRemoveFileFromContainer $MSSQL_CONTAINER_NAME $completeBackupFilePath
        echo "[ Docker Backup ] Backup complete!"
    else
        echo "[ERROR] Please pass parameters (arg1) = [DatabaseName] and also the (arg 2) = [FileNamePrefix]"
    fi
}

Here we go. Your function should look like something like this, except the "if" and "printing or echo" part of the code, which I will explain in shortly. The "if" part is all about just a small validation to see if we do get the parameters we expect and that's it. So we know that we expect two arguments, therefore, we are just checking if they are supplied by the caller. You can just wrap up your code in the "if" statement. The other thing with the "echo" is that we are just printing out the progress as we go.

Open up your terminal window and import your script that has all this implementation as follows.

source ~/workspace/informing-techies/docker-mssql-operations/docker-mssql.sh

After that you can try and run your new docker backup function, type something like: 

dockerMSSQLBackupDatabase AlimdaadSystem MyOwnPrefix

Sample : These are the results of an expected successful run of you new function click on the image to zoom in.

Take note of my backup file name after typing that command MyOwnPrefix_2020-03-12.bak, so finally, we see it an action and it make more sense. So that's it for backing up the Docker MSSQL Server Databases. You can play more with it the logic and add some error handling and all.

 

 

3. Restoring Your Database

 

3.1 Restore Challenge

By now you have an idea of what's going on. And there will be a lot of familiar code so instead of breaking it up in pieces like we did previously I am just going to leave the rest of the code for you to have a little fun challenge and see if you ca understand what's going on.

function dockerMSSQLRestoreDatabase() {

    if [[ -n $1 || -n $2 ]]; then
        databaseName=$1     
        backupFilePath=$2   
        backupFileName=$2:t 

        echo "[ Docker Restore ] Copying your file $backupFileName over to your container ..."
        dockerMSSQLCopyToVM $backupFilePath

        dockerBackupFilePath=$MSSQL_HOME_DIR"/"$backupFileName
        echo "[ Docker Restore ] Now restoring from $dockerBackupFilePath ..."

        docker exec -it $MSSQL_CONTAINER_NAME /opt/mssql-tools/bin/sqlcmd \
            -S $MSSQL_SERVER_IP \
            -U $MSSQL_SA_USERNAME \
            -P $MSSQL_SA_PASSWORD \
            -Q '
            RESTORE DATABASE '${databaseName}'
            FROM DISK = '\'$dockerBackupFilePath\''
            WITH REPLACE,
            NOUNLOAD,
            STATS = 5'

        echo "[ Docker Restore ] Removing your backup from the container "
        dockerRemoveFileFromContainer $MSSQL_CONTAINER_NAME $dockerBackupFilePath
        echo "[ Docker Restore ] Restore complete!"
    else
        echo "[ERROR] Please pass parameters (arg1) = [DatabaseName] and also the (arg 2) = [/backup/file/fle/path.bak]"
    fi
}

And that's it! The idea is to get the actual file we want to restore, we just copy it over to the docker container. From there we look into executing the SQL Restore command, using the copied file. When the restore process is complete we then just remove the file like we previously did with out backup process. 

A little new trick in this section, as you would have noticed, is the line with backupFilenae=$2:t. This command takes the supplied full path and just extracts the filename and its extension. That's all. Now let's test the restore process and then we can wrap up this excersice.

dockerMSSQLRestoreDatabase AlimdaadSystem MyOwnPrefix

Sample : These are the results of an expected successful run of you new function click on the image to zoom in.

The image above should give you an idea of the expected positive results of your process. And that's it you may now log into your database server to double check your data and see if it's been restored to the previous state. 

Conclusion

If you take the time on this you can improve this big time. More error handling and code refactoring. And this is not the only way you can achieve this but it's one of the ways and it works very well for local development.


Leave some comments below and you may checkout the GitHub Source Code to validate the steps.

My Transition To UNIX From DOS: Working With Aliases

Background

It's been fun playing on a Windows OS machine as a Software Engineer. I always find it interesting and fun to explore other things. Funny enough though, I have always been scared of using commands because I always felt that I would mess up something on my machine and have to do a factory reset of some sort. It's always a drag when one has to do that on their work machine. Finally I landed at a place where the recommended OSes were the unix based ones, preferably, Linux Ubuntu or Macintosh OS. Ultimately I went for the Macintosh OS, just because... 

From my first day there I had to learn some basic and common unix commands from the likes of: tail -f /file/path/here up to, ssh username@server.ip.address.here. At some point I got a bit fed up with typing a long and same old command everyday and to a point, every hour even. Even though it was great practice, something had to be done and that's when I learned about Unix aliases. Aliases are pretty much abbreviations of FUCs "Frequently-Used Commands" 

For example one can shorten a command like: defaults write com.apple.finder AppleShowAllFiles NO, which toggles hidden files & folders off, in Mac OS's finder, to something like show-hidden-files which will essentially be the name "Alias" of that command. 

Now to the "Nuts & Bolts", we will look at the following:

1. Creating / Adding a new alias
2. Referencing an external file for aliases.

We will also need some "shell scripting skills", nothing major, very few lines of code so that's all fun and well

1. Creating / Adding a new alias

Perhaps this may be slightly different on other unix platforms, so this is more pertinent to Mac OS. Let's go...!

• Open finder and head to your home directory. (Unless you are in your home directory by default) 
• Look for a file named, ".bash_profile". Notice the dot before the file name. If you cannot see hidden files then use the command (in your terminal)  from the example above and just change that "NO" to "YES". Then hold down the "alt" button and then "alt-click (right-click)"  on the finder icon and then "left click"  the option "Relaunch".
• Go back to looking for our file and then open it in any of your favourite text editor... (Sublime Text, Atom, even VI on your terminal etc ...)
• Let's add a new line, this will be a new alias for any command you want to add, for example:  

  alias show_files_no="defaults write com.apple.finder AppleShowAllFiles NO"
  

• To delineate things a bit more, "alias" is a reserved key word for shell, so you are letting shell know that you want to add a new alias, the next word "show_files_no" can be anything you want, this is the actual name or alias of your command. Then last part "defaults write com.apple.finder AppleShowAllFiles NO" is the actual shell command that you would normally type and execute on the terminal. We are almost done. 
• If you were editing the file using the terminal with VI, VIM, Nano etc... The next thing is to reload the ".bash_profile"  by either closing and opening the terminal again or typing the command "bash -l". I believe there may be more commands out there that one can use. 
• Finally open terminal if you closed it. If you reloaded the ".bach_profile"  using the command then just type the alias you added recently, in our case just type "show_files_no". This will execute the command associated with that alias. To see if this made an effect just follow the steps about relaunching finder above. That's it!

2. Referencing an external file for aliases.

I have recently been playing around with externalising some bash profile stuff because I have a lot of aliases and my bash profile artifact was just getting too congested. So main things to note here is that

... you don't have to create your new external file inside the same directory as your bash profile and the name of your file does not have to start with the word "bash"...

I just named it that way for the sake of naming it that way! So let's get to it:

• Create a new file as follows, "~/bashes/.whatever_file_name", of which in my case, is inside a new folder that I created, "bashes", in my home directory and named it ".bash_aliases". Keep in mind that your folder can be named anything you want.

• Now go back to your main file, ".bash_profile" and then replace your alias with the following shell scrip code.

  #Referencing path to file containing the aliases
  aliasesPath=~/bashes/.bash_aliases 
  if [ -f $aliasesPath ]; then 
     source $aliasesPath
  fi  
  

• Something along those lines should help you out. It's basically a shell if statement that checks if that file path exists and if so we then reference it from the main ".bash_profile" so next thing you should try is to now reload like we did earlier if you did all this using terminal or just close and then open terminal.  

So now you have externalised the aliases and you can try with other stuff like your environment variable profiles can also be externalised and so forth. Like I said this is the first main thing I learned when moving to UNIX, I hope it helps someone out there. I would really love to learn from you on how I can improve this post and some feedback, Cheers!