I am looking for a process or a tool that would allow me to migrate data from an ESRI SDE Database to a SQL Spatial Database. This would include converting the ESRI geometry to SQL geometry types, as well as potentially having to convert ESRI domains to lookup tables. I have seen some tools out there that will take shapefiles and convert them to SQL spatial format and I have been successful in doing that but given the amount of data and how dynamic it has been, I would prefer to not have to convert my entire database into shapefiles first. Would it be easier to go from a File/Personal geodatabase?
If you have ArcGIS now, you should use the FME Workbench found in the Data Interoperability Extension to 'read' your SDE data and 'write' it as SQL Server Spatial. Or, get a trial version and migrate all your data over before the trial runs out.
In terms of the domains, If there is an opposite of 'table to domain', use that, and again use FME to load the table as SQL Server non-spatial data.
From there we use Python with pypyodbc to move data between MSSQL servers.
We use QGIS as our main SQL Server Spatial Editor.
Why not use GDAL/OGR? You should be able to convert directly from your sde database to sql server. Here are some links that might help:
This one should give you the syntax for your sql server connection: https://alastaira.wordpress.com/ogr2ogr-patterns-for-sql-server/
This helps with the syntax for your sde connection: How to migrate data from ArcSDE to PostGIS?