Last week, Microsoft released the MapPoint 2009 Add-In for SQL Server 2008 Spatial. You can get it here.
This add in allows you to add layers to the map from queries against SQL Server spatial geography columns. It's more of an entry level mapping program that an IT-level (like the ESRI offerings) mapping program. You can save the map and use it against without SQL Server having to be available. There's also a number of "utility" type functions including shapefile import and queries and editing against your existing layers.
One thing I especially like about it is that it's usable sans-web, because the MapPoint data is local.
I was peripherally involved with the project from a point of view of identifying general spatial query constructs that would be most performant, usually this involved using query patterns (both in T-SQL and the underlying ADO.NET code) that would be most likely to use spatial indexes. There's a few different constructs (like CTEs, intermediate temp tables, and using parameterized queries in ADO.NET) that are a help in this regard. It was a learning experience.
Check it out.