Month: October 2012

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


Dealing issues with Agile Process – Part 1

While digging deeper into agile process I came across few interesting articles. One of the articles describes some interesting factors which they call drawbacks. I’m not going to write any comments on these but after reading through the article I felt that the identified problems can be solved with proper management strategies.

One of the disadvantages which were discussed in detail was breaking down the project into 2 to 4 week sprints and the rapid delivery can vary the scope of the features dramatically based on the end user feedback. The ability for the end user to provide feedback is one of the key features of this practice to ensure the implementation of features that the end user wants and as this article says it could go out of control, where the end user may ask for more and more modifications or they could storm us with change requests. This will lead to features that could not be delivered by the end of the project and these simplest things could derail the project by putting extra stress on the team which will result in poor performance. This means we can’t get the required outcome from the sprint.

In this type of situations I think we should have a proper change control mechanism. That is both the development party and the end user should have a strong agreement on how to handle the change. If the key members of the team continue to work on the same feature for more than two or three sprints, to enhance or to implement new functionality based on the feedback, the product development may take more time and budget unnecessarily. I have faced situations where we work hard to implement some new features and after the demo, they want something else and some new functionality to the implemented feature which took the team to work extra hours to meet the requirements.

Good change control management could help by allocating couple of sprints in the development cycle to deal with the change. In situations where you get new features we could categorise them as Change Requests and implement them in a separate sprint. If we could get these type of additional strategies to keep the wheels rotating.

TFS build Get Workspace step fails with inter-method access error

I have been implementing TFS 2010 automated build for our company’ product and initially it was implemented in a workgroup environment. There were some custom Powershell scripts that has to be executed remotely and it took weeks for me to finally solve it.
I was so close to complete the whole automated process but due to the security factors we have to bring in a Domain Controller and had to add the servers to the domain. All our servers are in Amazon Cloud and during this process the computer names were changed and all the share point configurations and the TFS configurations were somewhat corrupted. So instead of battling with the trouble shooting this I decided to remove TFS and re install it.
So I did a basic installation as a standalone server and all the settings were fine. But after setting the build definition and queue a new build, I faced a whole new problem as follows. No matter what I did everything failed at the Get Workspace step as follows.

Attempt by method ‘Microsoft.TeamFoundation.Build.Workflow.Activities.TfUndo+TfUndoCore.RunCommand(Microsoft.TeamFoundation.Build.Workflow.Activities.VersionControlScope, Microsoft.TeamFoundation.VersionControl.Client.Workspace, System.String, System.String)’ to access method ‘Microsoft.TeamFoundation.VersionControl.Client.Workspace.Undo(Microsoft.TeamFoundation.VersionControl.Client.ItemSpec[], Boolean, Boolean, System.String[])’ failed.

I was trying many things and everything failed. But at the end my good old friend, Prabath helped me out by suggesting that I should install the TFS 2010 service pack 1. But I have already done that.
So I was out of options, and then I decided to re install the service packs. First I installed the Microsoft Team Foundation Server 2010, Service pack 1 using the following link: TFS 2010 Service Pack 1

After that I applied the Cumulative Update package 2 for Visual Studio Team foundation server 2010 Service Pack 1 from the following link: Cumulative Update package 2 for Visual Studio Team foundation server 2010 Service Pack 1

So this approach solved the issue for me which I was stuck in the above issue for 4 days and have to say that never give up. There is always an answer and always someone to guide you to get it fixed.