Mass import into MS SQL database script with Powershell

While I've been testing failover cluster of Microsoft SQL Server 2012 (more details: Step-by-step deploying AlwaysOn on MS SQL 2012) I was needed to generate mass import of some data into database.

As soon as I'm very bad in T-SQL, I decided to write script on Powershell for this task.

We will need:

• Database itself
• Table in it for testing purposes

You should already have database. It's better to create new empty database, now we need to create a table. I created it manually in Designer in SQL Server Management Studio.

• GUID - key field for data
• TIMESTAMP - timestamp :)

Here is the script. It accept two mandatory parameters: total working time and number of INSERT queries per second.

param(	[Parameter(Mandatory=$true)][int] $Duration, 
		[Parameter(Mandatory=$true)][int] $StringsPerSecond
	 );


$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=ae1-tst-listene.domain.local; Initial Catalog=DAD_TST; Integrated Security=SSPI");
$conn.open();
$cmd = $conn.createcommand();

$start_time = Get-Date;
$end_time = $start_time.AddMinutes($Duration);
$current_time = Get-Date;
$counter = 0;

while ($end_time -gt $iteration_end_time) {
	$iteration_start_time = Get-Date;
	$guid = [guid]::NewGuid();
	
	if ($iteration_start_time.DateTime -gt $current_time.DateTime) {
		$counter = 0;
		echo $current_time.DateTime;
	}
	
	if ($counter -lt $StringsPerSecond -and $iteration_end_time.DateTime -eq $iteration_start_time.DateTime) {
		
		if ($conn.State -eq "Closed") {
			echo "Re-Opening Connection !!!";
			try {
				$conn.open();
			} catch {
				echo "Re-connection error !!!";
			}
		}
		$current_time_string = Get-Date -Format "yyyyMMddHHmmss";
		$current_time = Get-Date;
		$cmd.commandtext = "INSERT [dad_tst] (GUID, TIMESTAMP) VALUES ('$($guid)', $current_time_string)";
		try {
			$result = $cmd.executenonquery();
		} catch {
			echo "Error sending a query !!!";
		}
		$counter++;
	}
	
	$iteration_end_time = Get-Date;
}

$conn.close();

It's better to run the script from computer which is closer to the server, as network latency will slow down performance greatly.

As the result you will get something like this: