Getting Started with Ansible and Windows Automation Part 2


Now that we have out basic WinRM setup configured and Ansible it able to communicate with our Windows server, it's time to start automation. In this example we will use Ansible to install the SSMS ( SQL SERVER MANAGEMENT STUDIO ver 18.4) on our windows host
  • First thing to do is to download the SSMS.exe from Microsoft on our Ansible server




Now we can start to work on our Ansible playbook, which will do the following:

  1. Copy the SSMS application to the Windows host to test that Ansible  and windows are playing together
  2. Add the refresh task to the playbook which will call the powershell script to refresh the DR SQL database
  • Create the ssms.yaml file and start by coping the SSMS Application to the Windows host


  • Run the Ansible playbook


  • Check to make sure the file has been copied across




Now Lets configure Ansible to call the powershell script which will automatically refresh a Target SQL Server database from a snapshot running on a Source SQL server database.

In this example I have both the Source and target running on the same Virtual Machine on ESX, the database being refreshed is called testdb



Lets have a look at the Ansible "ssms.yaml" playbook and the powershell script.

 ssms.yaml

 ---
- hosts: winsql
  gather_facts: no
  tasks:
  - name: Copy SSMS Application
    win_copy:
        src: SSMS-Setup-ENU.exe
         dest: c:\\Temp\\

    - name: Run Database Refresh Powershell

     win_command: powershell.exe -ExecutionPolicy ByPass C:/Users/Administrator/Desktop/snap.ps1

 The task called "Run Database Refresh Powershell" uses the win_command Ansible module to call the remote Powershell script called C:/Users/Administrator/Desktop/snap.ps1

 The following file called win.yaml is used to setup the Ansible groups variable for WinRm to connect to the windows server

 ---
ansible_user: Administrator
ansible_password: P@ssw0rd!
ansible_connection: winrm
ansible_winrm_transport: basic

ansible_winrm_port: 5985
The basic powershell script below is used to refresh the DR database called MSSQLDR from the source database MSSQLPROD

So Lets see how easy it is to fresh the DR database!!
  • First we add some new data to the MSSQLPROD database, lets create a new table called "cbt10" 
  • Right Click on the table and select "New Query" type in the SQL quere and click on "Execute"
    • create table cbt10 (cbt10 varchar(20))


We can see the new table called dbo.cbt10 created successfully



Lets confirm that the MSQLDR database does not have the new table


































 Now lets run the Ansible ssms.yaml playbook


[oracle@ansible-server ansible]$ ansible-playbook ssms.yaml






Now we can see the new Table on the MSQLDR database after running the ansible refresh playbook































You can also run the playbook in debug mode to get more verbosity from the output

[oracle@ansible-server ansible]$ ansible-playbook -vvv ssms.yaml

Comments

Post a Comment

Popular posts from this blog

SQL Server container with Pure Storage Docker volumes

Image
SQL Server on Docker with Pure Storage Persistent Volumes Docker and containers have become an integral part of virtualisation. Having the ability to spin up containers has almost instantly transformed the way we build, develop and manage applications. Many of us have been using some sort of virtualisation for decades as this is not a new concept. Due to their speed, simplicity and automation, container usage has garnered substantial popularity and are becoming ubiquitous in the field of virtualisations. So why use containers ? Decouple application runtime from infrastructure. Runs instantly. Process and user isolation for application multi-tenancy. Key building blocks for microservices. But what happens when you delete the container ? The main issue in deleting a container is that you delete the writeable layer and any data that was there, hence no data persistence. Docker volumes provide us a way to decouple your application from its state, to the point where you c...
Read more

Cloning SQL Server Containers With Pure Storage Docker Volumes

Image
In my first blog  https://bannaych.blogspot.com/2020/04/mssql-server-on-docker-with-pure.html  I showed you how we can create SQL Server containers using the Pure Storage Docker plugin. In this blog I will show you how we can leverage the Pure Storage Docker plugin to instantly create a clone volume from the source volume, then use Pure Storage crash consistent snapshots to refresh the clone volume. Crash consistent snapshots provide us with a point in time copy of the volume/database, because we use metadata pointers to the original blocks on disk, the snapshots are created instantly with no initial space consumption, then those snapshots can be used to refresh volumes or create new ones. The big advantage of using snapshots is that we no longer need to backup and restore large databases to provision and refresh TEST/DEV/UAT environments. In the world of DevOps being agile is imperative and Pure Storage space efficient snapshots are key to maximising productivity of database ...
Read more

Oracle Containers on Kubernetes and Pure Service Orchestrator

Image
Pure Service Orchestrator allows for smart provisioning of storage to one or many arrays, this provides administrators and developers the ability to  provision storage on demand with cloud like functionality, ease and flexibility. In this blog I will use Pure Service Orchestrator ( PSO ) to automate the provisioning of volumes to Kubernetes which will then be used to create Oracle containers using persistent storage.   My next blog will demonstrate the  power of using  Pure Storage snapshots and cloning to rapidly create instant copies of the source Oracle database. Environment NOTE:  In this blog I will not be going through the Kubernetes cluster install Role FQDN IP OS RAM CPU K8 Ver Master master.localdomain.com 192.168.111.231 Ubuntu 18.04 5G 3 1.18 Worker1 worker1.localdomain.com 192.168.111.232 Ubuntu 18.04 8G 4 1.18 # kubectl get nodes -o wide NAME STATUS ROLES AGE VERSION INTERNAL-IP EXTERNAL-IP OS-IMAGE KERNEL-VER...
Read more