I have to confess, these days I use Postgres more than SQL Server. But that said I do a LOT of GIS work, and weather you like it or not SQL Server just does not follow the spatial SQL simple features standard, well not correctly anyway, as I said in my presentation in spatial SQL at Last years DDD, "it's the most compatible, incompatible spatial SQL implementation I've seen."

Postgres also does a ton of stuff the SQL Server can only dream of and it runs fantastic on a Windows based OS too.