It has extension (rather than community module) status, so there is at least some support. However there are better database options (those in core distribution are likely better supported than extensions, which in turn are better supported than community modules, broadly speaking). I'd generally suggest PostGIS first.
Whether SQL Server and the geoserver (mostly GeoTools) module suit your needs depends on your situation (which you haven't really described) and to some extent, opinion. I can only suggest trying it with representative workloads.
I'd still suggest PostGIS except that this would really be an opinion and we try to avoid that.