Monday, June 25, 2012

Passing Parameters to Remote PowerShell Session Inside a Script

Let’s take a look at the following script named md.ps1:

When I run using below syntax it will not work as expected on a remote computer.

PS> .\md.ps1 “computer1” “Release” “ReleaseBuildConfig”

It will replace $BuildDefinition variable but then it will not list all its child folders.  Further more it will not replace $configuration variable with the value, for example, “Release” or “Debug”.   As soon as I hard coded them the rest of the command worked just fine.  Ok so what was the issue.

It turns out that when you are trying to pass parameters to a script and then invoke a remote powershell session and access variables inside that PSsession then you have to declare them twice in your scripts. Yes Twice.  Check the final script.

It was painful to figure it out and my co-worker friend helped me figure it out.

Tuesday, June 19, 2012

Deploying Website and Windows Service using MSDeploy, Powershell and InstallUtil from TFS Build

We have a visual studio solution which has 4 projects into it.  One of them is a web project, another windows service project, class library and console application, all under source control inside Team Foundation Server 2010 in one team project.
   >Web Project
   >Windows Service Project
   >Class Library Project
   >Console Application Project

When we queue a new build using Team Foundation Build then all the content gets dumped into one single drops folder (a shared folder on your staging server).   It is a total mess and we need to have our structure as shown above on the destination drops folder.  We would like to have control over the structure of the destination files dropped by TFS Build.  I was able to achieve this by following this guideline posted on MSDN “Control Where the Build System Places Your Binaries”.  

First we will take a look into how to deploy Web Application using Web package in TFS Build using powershell.  Alright now when we look into the drop folder for our website on the destination folder we can see a _publishedpackages folder which has a .zip file.  We are interested in this folder.  We will grab the .zip file which is our package and deploy it using MSdeploy.

MSDeploy is a command line utility used to deploy webpackages to IIS.  All good but there is one problem and that is everytime a new build is queued a new folder is created with date and version number appended to it.  Our files are inside dropsfolder\builddefinition\latestfolder.6192012.1\.  Inside this folder if you have customized Process template then each project will have its own folder and inside that there will be Release\Debug folder.  Then we have to get the path to our .zip file for the website which we need to pass to MSDeploy to publish website to IIS.  This can get cumbersome and slow down our cycle. Why? Everytime a new build is dropped you will have to go through this pain.

1. Login into the server
2. Fire up command line
3. Locate the path to the latest build folder and find the path to the _PublishedPackages folder
4. Pass those parameters into MSDeploy and then run it.

What we want is combine steps 2 to 4 into one powershell script and store it inside TFS that will do the job for us.  You will have to modify process template to run a powershell script during build process. Check this excellent article from Ewald Hofman on how to execute powershell script from tfs. One might think that there are different ways of achieving same functionality and automating it directly from build definition itself but I learnt something in this process which I thought might be useful to somebody.

$latestbuildfolder = Get-ChildItem "C:\dropsfolder\BuildDefinition\" | Sort-Object LastWriteTime -Descending | SELECT-Object -First 1 $webpackagelocation = "\ProjectName\WebProject1\Release\_PublishedWebsites\WebProject1_Package\" $finalstring = $latestbuildfolder.FullName + $webpackagelocation & 'C:\Program Files\IIS\Microsoft Web Deploy v2\msdeploy.exe' -verb=sync -dest=auto "-source=package=$finalstring"

In the first three lines we try to grab the path to the web package and then pass the path to MSDeploy commandline utility to deploy to IIS web server.

Line1: First we list all the folders using Get-ChildItem and then sort them in descending order and get the first item.  This first item is our last build that was queued.  Everytime when you run the script it will make sure we get the latest folder.
Line2: This line is very simple because for all the future builds we know where our .Zip file will be sitting.  So it is direct path to the zip file.
Line3: Just concatenating two strings from line1 and 2 but be careful here the $latestbuildfolder variable holds the folder as the item so to get the path you need $latestbuildfolder.FullName.
Line4: If you want to run commandline utilities through powershell then you will have to first put “&” ampersand varialbel then include commandline utility in quotes and then provide other variables or arguments in quotes.  MSDeploy is little bit tricky here.  You provide all other variables in without quotes and include the –source=package-$finalstring in quotes.  To find out the exact syntax that worked, I played with lots of combinations and eventually a friend of mine in the office helped me achieve this.  You can also use Join-Path cmdlet of powershell to join paths.

Now you can do almost anything now with MSDeploy, TFSBuild and Powershell.  MSDeploy is a very powerfull tool and the one that is less exploited for deploying Websites guess.
Let’s try to install windows service using Powershell so that this wil happen automatically everytime.  We also have to take care of one more problem, and that is uninstalling windows service everytime we deploy a new version.  Below is my powershell script to uninstall windows service:

$service1 = Get-WmiObject -Class win32_service -Filter "Name='OurServiceName'" 
 if ($service1.State -eq 'Running') 
 Write-Host "Stopping $service1.Name service" stop-service OurServiceName & 'C:\Windows\Microsoft.Net\Framework\v4.0.30319\installUtil.exe' /u $service1.PathName

In the above script, I am using Get-WMIObject win32_service to get our windows service because Get-Service was not giving me the path to the executable that was used to install the service.  The executable path to the service is revealed only through win32_service instance of the class.   This way we don’t have to cycle through our drops folder and find previous versions of windows service.
Let’s take a look at our installation powershell script code:

$latestbuildfolder = Get-ChildItem "C:\dropsfolder\BuildDefinition\" | Sort-Object LastWriteTime -Descending | SELECT-Object -First 1
$windowsServiceFolder = "\ProjectName\WindowsService\Release\WindowsService.exe"
$exepath = $latestbuildfolder.FullName + $windowsServiceFolder
& 'C:\Windows\Microsoft.Net\Framework\v4.0.30319\installUtil.exe' /username=companyname\srviceaccount /password=password1 $exepath
Start-Service -Name OurServiceName
$getlatestservice = Get-Service -Name OurServiceName

In the above code pay close attention to the installutil.exe code where we specify /username and /password because we don’t want to have that user prompt annoy us everytime we try to install our windows service.  For this to work you have to make some changes to your windows service installer code.  I have used code to make it work.  There is an appropriate BeforeInstall event where you need to add this code because there are BeforeInstall events for ServiceProcessInstaller and ServiceInstaller objects.

Now putting it all together we want to put our powershell script into TFS as we don’t want to have it on the debug or production machine.  We want it under source control.

$session = New-PSSession -ComputerName Server20 
Invoke-Command -Session $session -ScriptBlock { $service1 = Get-WmiObject -Class win32_service -Filter "Name='OurServiceName'" 
 if ($service1.State -eq 'Running') { 
 Write-Host "Stopping $service1.Name service" stop-service OurServiceName 
& 'C:\Windows\Microsoft.Net\Framework\v4.0.30319\installUtil.exe' /u $service1.PathName 
$latestbuildfolder = Get-ChildItem "C:\dropsfolder\BuildDefinition\" | Sort-Object LastWriteTime -Descending | SELECT-Object -First 1 
$webpackagelocation = "\ProjectName\WebProject1\Release\_PublishedWebsites\WebProject1_Package\" 
$finalstring = $latestbuildfolder.FullName + $webpackagelocation 
& 'C:\Program Files\IIS\Microsoft Web Deploy v2\msdeploy.exe' -verb=sync -dest=auto "-source=package=$finalstring" 
$windowsServiceFolder = "\ProjectName\WindowsService\Release\WindowsService.exe" $exepath = $latestbuildfolder.FullName + $windowsServiceFolder 
& 'C:\Windows\Microsoft.Net\Framework\v4.0.30319\installUtil.exe' /username=companyname\srviceaccount /password=password1 $exepath 
Start-Service -Name OurServiceName $getlatestservice = Get-Service -Name OurServiceName $getlatestservice 

The first two lines are of importance we are running these command using powershell remoting (Powershell remoting should be enabled on the remote server).  We are create a new session specify computer name and run our powershell commands.  These commands will run on the remote machine just like they would run on a client machine.  We are all set.  Queue a new build and see this magic happen.

One more step closer to continuous integration!!!

Friday, June 8, 2012

Linked Servers Inside SQL Server

I am trying to connect to a sql database on a different server say server2 and run a sql query on my server1. It will not run even though I specified tables like this [server2].[databasename].[dbo].[tablename] in my sql query. It gave me the following error:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'server2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

As the error specifies we have to add my server2 to a list of linked servers inside sql server.  So what is a linked server in SQL Server?  It allows us to execute remote queries on a remote data store [Sql server, oracle, file system, local Dsn, excel spreadsheet, active directory] by linking it to our server.  When you connect to your sql server inside management studio you will see a node named Linked Servers and there you can see a list of Providers for different types of data stores.  In the image you will see a list of linked servers if any and in my case I have added my server.

After adding a linked server I can query it by specifying entire name of your tables like I mentioned above.  You can also add Active Directory as a linked server and query Active Directory just like SQL Query.  Check this MSDN link to see which all data sources you can add as linked servers. 

The best way to add a linked server is through stored procedure specified in the error itself.  After adding a linked server you will also have to add a linked server login to connect to the remote database.  If you don’t specify security login to your linked server then it will try to connect with the credentials you are currently logged into your sql server through SSMS.  You can map a local user to the linked server to connect if only that user has local login access to that server.  For more check this msdn link on security of linked servers.
Syntax for adding a linked server and linked server login:

EXEC sp_addlinkedserver @server = 'server2', @srvproduct='', @provider='SQLNCLI', @datasrc = 'server2', @catalog='databasename'

EXEC sp_addlinkedsrvlogin @rmtsrvname='server2',@useself='FALSE', @rmtuser='remoteuserlogin',@rmtpassword='somecomplicatedpassword'

If you are using SQL server then you can leave @srvproduct as null.  

You can also connect to Oracle from SQL Server and here is this nice post  which details exact steps to do that.