MS-SQL HA database backup using Powershell and Ansible, Step by Step - 3 -

Installing & Setting up Ansible on Linux server (ubuntu 18.04)

Ansible installation on Linux:

1. Get a Linux machine (ubuntu 18.4 server) from your environment. (Here we used Azure's VM.)

2. Login to the deployed Linux server using SSH and run the following commands to install Ansible.

Update apt-get and install python

 sudo apt-get update    

 sudo apt-get upgrade -y    

 sudo apt-get install python -y  
  

Update Ansible repo & install Ansible

sudo apt-add-repository ppa:ansible/ansible  

sudo apt-get update  

sudo  apt-get install ansible -y  

3. Configure SSH (optional for Linux managed nodes)

For this instruction, this is an out of scope but in case you need a Linux managed node that is running automation, you have to do these steps.

Configure SSH

ssh-keygen

f:id:htr1036:20200115055713p:plain
Creating an SSH Key

Key file's path and an ssh passphrase will be asked like above. Store them in a secure place.

Copy SSH public key

cat <your ssh key file's path>.pub

e.g.

f:id:htr1036:20200115060147p:plain
Copying SSH public key

Copy the public key shown above, and store it somewhere.

Login to your Linux managed node.
Issue the command

sudo -s  

Open the authorized_key file with the following command

sudo vi ~/.ssh/authorized_keys  (Past the public key that you got previous step, and write & quit.)  

The authorized_keys should be same as the publick key you have created previously.

Now you can test SSH between them.

sudo ssh <ansible host username>@<ansible managed host server name>

4. Create an Ansible inventory hosts file on the Ansible server

sudo vi /etc/ansible/hosts

Edit hosts like below format:

[winserver]
<your windows server FQDN>

[winserver:vars]
snaible_winrm_port=5986
ansible_connection=winrm
ansible_winrm_transport=credssp
ansible_winrm_server_cert_validation=ignore


5. Create an Ansible play-book yml

sudo vi sqlAutobackup.yml  

Edit sqlAutobackup.yml like below:

- name: Powershell for ms-sql database backup
  hosts : winserver
  gather_facts: false

  vars:
   ansible_user: "<your winrm username for ansible server>"
   ansible_password: "<password>"

  tasks:
   - win_command: powershell.exe
     args:
      stdin: <your powershell script path in the remote windows host>


6. Test run

Try to run the sql database backup playbook from Ansible server:

sudo ansible-playbook ~/sqlAutobackup.yml -vvvv

Note: -vvvv is a debug mode switch.

You will the outputs something like this:

f:id:htr1036:20200115081717p:plain
Backup result by Ansible-playbook's debug mode.

In debug mode outputs, you should see all databases like red box above, and "PLAY RECAP" should show "ok=1".


7. Set up a Cron entry for an schedule backup

To set up an schedule backup, add an entry to Ansible user's crontab.

sudo crontab -u <your winrm username for ansible server> -e

The below entry will backup sql databases in every 6 hours:

#Run ansible playbook under the user "ansible-winrm user".
0 */6 * * * sudo ansible-playbook /home/<ansible-winrm user>/sqlAutobackup.yml

"wq!" to save the crontab.

Wait and see if this schedule yml will backup as expected.

--END

References:

How to install Ansible on Ubuntu Server 18.04 - TechRepublic
Setting up a Windows Host — Ansible Documentation