Execute remote SQL commands via Powershell on Amazon RDS

As I have been working with TFS2010 build automation for past few days, again I came across a dead end. The issue was all our application servers are on Amazon EC2 and the backend is on Amazon RDS. This was new to me and I had to include the script which contains the database changes to the Build Automation. i.e. The Build will execute the script from the Build server against the RDS instance. When it comes to RDS there are no servers or you can RDP to it. It is nothing but a standard database instance. So I thought of going through a step by step approach on solving this.
First followed a fundamental video tutorial on Pluralsite and got the basic understanding of the Amazon RDS for SQL Server. After that I have posted the question on AWS RDS forum. In the mean time we have also contacted the AWS support and according to the support person I could execute scripts remotely against the RDS instance using the “Sqlcmd”. So my preference was to do it via Powershell. So I did some R&D and found this excellent article which helped me to solve my issue.
The equivalent of the “SqlCmd” in powershell is “Invoke-Sqlcmd” command. This is not installed on your system by default. If you are using sql 2012, this will be installed with SQLPS modules. Follow up the document bellow on the Simple-Talk, it explains everything in detail.
Practical PowerShell for SQL Server Developers and DBAs

After doing the configuration I have used the following command to connect to the RDS instance remotely. Following example just uses a select statement.

PS C:\Users\Administrator> Invoke-Sqlcmd –ServerInstance -Database EmployeeData –Query “Select * from HoursWorked” –Username -Password

Please follow the link for more detail on “Invoke-Command”.


Visual Studio 2010 Powershell Extension and PowerGUI

I was using Powershell scripting to create automated deployment scripts for SSIS packages and believe me even though Powershell is a great scripting language it was quite difficult get a script syntactically correct and error free for first time. To overcome this difficulty I used the Powershell ISE (Integrated Scripting Environment). It gives some form of aid to my scripting needs, but I have to say not much. As my self and the team were battling with tight sprint dead lines, these automated scripts writing took considerable amount of time. This was affecting the completion of sprints and one reason for increasing the remaining work at the end of sprints. It was a nightmare to manage the scripts scattered with the team.

When I was reading through my emails I got this email from Tech Republic, of course I’m tech republic fan, regarding this Powershell extension for Visual Studio 2010. So I read it and got the components installed. First you need to install the PowerGUI, otherwise the Visual Studio extension will not work.