Is it possible to reproject spatial data using SQL Server?
No.
Transform - ability to transform from one spatial ref to another: No - need 3rd-party tools, Geometry can use any SRID between 0 and 999999. Spatial Tools free CLR add-on does provide limited transform support.
Source: http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare
Not by default, but check the SQL Server Spatial Tools developed by MSDN on GitHub. Specifically, the affine transformation functions.
Example for reprojection from EPSG:2193 to EPSG:3857
c:\OSGeo4W64\bin\ogr2ogr.exe ^
-f "MSSQLSpatial"^
"MSSQL:server=DestServerName;database=DestDbName;trusted_connection=yes"^
"MSSQL:server=SourceServerName;database=SourceDbName;trusted_connection=yes"^
-sql "SELECT [Id], [Shape].STAsText() Shape FROM [SourceDbName].[dbo].[SourceTableName]"^
-nln "DestTableName"^
-overwrite^
-s_srs EPSG:2193^
-t_srs EPSG:3857
After this execute SQL Query
update [DestDbName].[dbo].[DestTableName]
set [ogr_geometry] = geometry::STGeomFromText([shape], 3857)