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”.

Advertisements

3 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s