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
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.
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>
User name and password are the ones that you have created in this step:
3. Create an service account for Ansible
MS-SQL HA database backup using Powershell and Ansible, Step by Step - 2 - - Gonzablogstdin's powershell script should be the one you have created in this step:
Run the below script to back up all sql database on this host.
MS-SQL HA database backup using Powershell and Ansible, Step by Step - 1 - - GonzablogIn this example, a plain text is used for the password and the username, which is, of course, not secure (although only root user can see it). This should be encrypted by the ansilbe vault or other key vault services. I will post how to do it at a later date!
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:
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