What is the equivalent of MS Access InStr function in SQL Server 2008?
InStr is pretty similar to CHARINDEX. So, your query can be written as:

SELECT  PartID, 
        [Description], 
        [Service], 
        CHARINDEX(' ',[Description]) Expr1,
        LEFT([Description],CHARINDEX(' ',[Description])) Part
FROM TI
WHERE PartID IS NOT NULL 
AND [Service] = 'JTP';