Refreshing Test Dev SQL Environments using NetApp Technologies

#!/usr/bin/perl
#===============================================================#
#
# $ID$
#
# RefreshSQL.pl	
#
# Copyright 2010, Rajeev Karamchedu, Ltd. All rights reserved. 
#
# This work is licensed under the Creative Commons 
# Attribution-Noncommercial-Share Alike 3.0 United States License. 
# To view a copy of this license, visit 
# http://creativecommons.org/licenses/by-nc-sa/3.0/us/ or send a 
# letter to Creative Commons, 171 Second Street, Suite 300, 
# San Francisco, California, 94105, USA.
#
#===============================================================#
 
require 5.6.1;
use lib './manage-ontap-sdk-3.5.1/lib/perl/NetApp/';
use lib './lib';
use POSIX qw/strftime/;
use NaServer;
use NaElement;
use NaErrno;
use Simple;
 
# Configuration
my $filer_hostname = "simtap2";
my $filer_username = "root";
my $filer_password = "netapp";
my $sqlserver_hostname = "win2k8";
my $db_name = "AdventureWorksDW";
my $db_datafile = "W:\\AdventureWorksDW_Data.mdf";
my $db_logfile = "X:\\AdventureWorksDW_Log.ldf";
my $db_datalun_filerpath = "/vol/vol1/sqlproddata";
my $db_loglun_filerpath = "/vol/vol2/sqlprodlogs";
my $clone_suffix = "clone";
my $snapshot_name = "clonesnap";
my $igroup = "viaRPC.iqn.1991-05.com.microsoft:win2k8";
 
main();
 
 
##############################
#
# Subroutines
#
##############################
 
 
 
sub main() {
 
	my $s = NaServer->new ($filer_hostname, 1, 3);
	my $resp = $s->set_style(LOGIN);
	if (ref ($resp) eq "NaElement" && $resp->results_errno != 0) {
		my $r = $resp->results_reason();
		print "Failed to set authentication style $r\n";
		exit 2;
	}
 
	$s->set_admin_user($filer_username, $filer_password);
 
	$resp = $s->set_transport_type(HTTP);
	if (ref ($resp) eq "NaElement" && $resp->results_errno != 0) {
		my $r = $resp->results_reason();
		print "Unable to set HTTP transport $r\n";
		exit 2;
	}
 
	# Begin 
	# begin_process($s);
 
	print "Detaching the database ($db_name)....";
	detach_database();
	print "Done\n";
 
	refresh_luns($s, "data");
	refresh_luns($s, "logs");
 
	print "Attaching the database ($db_name)....";
	attach_database($sqlserver_hostname,$db_name, $db_datafile, $db_logfile);
	print "Done\n";
	# End 
	# end_process($s);
 
	exit 0;	
}
 
sub detach_database {
	my $sqlcmd = "sqlcmd -d master -E -Q \"exec sp_detach_db $db_name\" ";
	print "Executing SQL Command: \"$sqlcmd\"\n";
	system($sqlcmd);
}
 
sub attach_database {
	my $sqlcmd = "sqlcmd -d master -E -Q \"exec sp_attach_db '$db_name', '$db_datafile', '$db_logfile'\" ";
	system($sqlcmd);
}
 
sub refresh_luns {
 
	my ($s, $lun_type) = @_;
	if ($lun_type eq "data") {
		@filer_volpath_splits = split("/", $db_datalun_filerpath);
		$file_path = $db_datafile;
		my @drive_splits = split(":", $db_datafile);
		$sd_lun_mount_point = $drive_splits[0];
 
	}
	elsif ($lun_type eq "logs") {
		@filer_volpath_splits = split("/", $db_loglun_filerpath);
		$file_path = $db_logfile;
		my @drive_splits = split(":", $db_logfile);
		$sd_lun_mount_point = $drive_splits[0];
	}
	else {
		print "Functionality not yet implemented\n";
		return (-1);
	}
 
	my $parent_vol_name = $filer_volpath_splits[2];
	$clone_vol_name = "${parent_vol_name}_${clone_suffix}";
	$cloned_lun_path = "/vol/$clone_vol_name/$filer_volpath_splits[3]";
	my $sd_lun_path = "$filer_hostname:$cloned_lun_path";
 
	print "Disconnecting LUN: $sd_lun_mount_point....";
	my $sys_cmd = "sdcli disk disconnect -m $sqlserver_hostname -d $sd_lun_mount_point";
 
	system($sys_cmd);
	print "Done\n";
 
	# Offline and Destroy the old clone volume
	prep_volumes($s, $clone_vol_name);
 
	# create a flexclone of that volume
	create_flexclone2($s, $parent_vol_name, $clone_vol_name);
 
	# Modify LUN and online
	#online_lun($s, $cloned_lun_path, $old_map, $igroup);
 
	# Connect the LUN
	print "Connecting LUN: $sd_lun_path\n";
	$sys_cmd = "sdcli  disk connect -p $sd_lun_path -d $sd_lun_mount_point -IG $sqlserver_hostname $igroup -dtype dedicated";
	print $sys_cmd . "\n";
	system ($sys_cmd);
}
 
 
sub prep_volumes {
 
	#4. set the serial number (fancy stuff)
	#5. map the luns and mount the luns from this vol
 
	my ($s, $clone_vol_name) = @_;
 
	# offline the volume
	print "Offlining previous clone volume $clone_vol_name...";
	my $result = $s->invoke("volume-offline", "name", $clone_vol_name);
	if (($result->results_errno != 0) && ($result->results_errno != 13042) ) {
		print "Error Code: " . $result->results_errno . " - " . $result->results_reason() . "\n";
	}
	else {
		print "Done\n";
		print "Destroying previous clone volume $clone_vol_name...";
		# Volume offlined - Now destroy it
		my $result = $s->invoke("volume-destroy", 
					"name", $clone_vol_name); 
		if ($result->results_errno != 0) {
			print "Error Code: " . $result->results_errno . " - " . $result->results_reason() . "\n";
		}
		else {
			print "Done\n";
			print "Volume $clone_vol_name has been offlined and destroyed\n";
		}
	}
}
 
sub create_flexclone2 {
	my ($s, $parent_vol_name, $clone_vol_name) = @_;
	print "Finding the latest snapshot in the volume '$parent_vol_name'\n";
	my $result = $s->invoke("snapshot-list-info", "volume", $parent_vol_name);
	if ($result->results_errno != 0) {
		print "\nError Code: " . $result->results_errno . " - " . $result->results_reason() . "\n";
		return (-1);
	}
	my $snapshotlist = $result->child_get("snapshots");
	if (!defined($snapshotlist) || ($snapshotlist eq "")) {
		# no snapshots to report
		printf("No snapshots on volume %s\n\n", $parent_vol_name);
		return (-1);
	}
	my @snapshots = $snapshotlist->children_get();
	my $tmp = 0;
	my $snapshot;
	my $snap;
	foreach my $ss (@snapshots) {
		if ($ss->child_get_int("access-time", 0) > $tmp) {
			$tmp = $ss->child_get_int("access-time", 0);
			$snapshot =  $ss->child_get_string("name");
			$snap = $ss;
		}
	}
	printf("Latest snapshot name is %s\n", $snap->child_get_string("name"));
	print "Creating a FlexClone volume....";
	my $result = $s->invoke("volume-clone-create", 
				"parent-volume", $parent_vol_name,
				"parent-snapshot", $snap->child_get_string("name"),
				"volume", $clone_vol_name);
	if ($result->results_errno != 0 ) {
		print "\nError Code: " . $result->results_errno . " - " . $result->results_reason() . "\n";
		return (-1);
	}
	print "Done\n";
}