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