Auto-Mounting of most recent snapshot for SQL Server Reporting

[DinoBob]

I am running OnTap 9.5 and SQL Server 2016 on Windows 2012.

We have a server which we wish to use solely for reporting purposes. We would use FlexClones generated from the latest snapshot of our production SQL Server, and stand them up on the reporting server. We would like to refresh the data 1-2x daily.

Has anyone here had experience with automating this? As I see it, we'd have to:

Stop SQL Server.

Offline the Data and Log LUNs.

Offline the Data and Log volumes.

Rescan from the Windows host.

Destroy the Flex Clones.

Create new FlexClones of the latest Data and Log snapshots.

Rescan from the Windows host.

Restart SQL Server.

That's oversimplifying as I know there are more substeps but I think that would do it. As long as the LUN IDs are the same I think it would be rather easy to get the Windows Host to pick the LUNs up and assign the same path to them.

What would you use to accompish this? PowerShell? Ansible? If it swings the decision I do not have Ansible in my environment and would balance out standing that up against the amount of work that would be involved. If Ansible is worlds easier than PowerShell in terms of scripting this it might be easier to go that way.

[donny_lang]

Personally, I would use PowerShell to accomplish this. I have found Ansible to be quite lacking in general when needing to manage/automate Windows-based systems, it seems MUCH more suited to Linux and other non-Windows things. 

We do something very simliar with Oracle database refreshes in our environment, so feel free to post back if you get stuck and I'd be happy to give you a hand with your code! 

[DinoBob]

Thanks for the quick reply. Was the PoSh pretty easy to create? There is also the option to do it at the application layer with CommVault but I always prefer to do things as far down in the stack as possible. I think that with management of the actual snaps this will go well. Additionally, they are snapmirrors which are offsite so there will be no performance impact on the prod server at all. 

[donny_lang]

Honestly, I think defining the actual repeat-able process for what needed to be automated was more difficult than writing the code. Once you've condensed it down to a list of steps that need to be performed the same way each time, the code you need to write to meet each of those requirements isn't too bad in comparison.