Previous Configure SQL Service Recovery Manual Configuration Configure Errorlog Retention Next

Configure SQL Server Surface Area

FineBuild can configure SQL Server Surface Area to optimise it for security and manageability.

SQL Server surface area configuration is performed by running a series of queries in SQL Server Management Studio.

FineBuild SQL Server Surface Area configuration

The SQL Server Surface Area configuration relates to Process Id 5BA and is controlled by the parameters below:

Parameter Build SQL2005 SQL2008 SQL2008 R2 SQL2012 SQL2014 SQL2016 SQL2017
SetupSQLServer FULL Yes Yes Yes Yes Yes Yes Yes
SetupSQLServer WORKSTATION Yes Yes Yes Yes Yes Yes Yes
SetupSQLServer CLIENT N/A N/A N/A N/A N/A N/A N/A

In order to maintain compatibility with older versions of SQL FineBuild, the parameter ConfigSQLServer can also be used.

FineBuild also uses the following parameters to help configure SQL Server Surface Area:

Parameter Default Value Description
spConfigureCLREnabled 1 Used to enable use of the CLR
spConfigureRemoteAdminConnections 1 Used to enable Dedicated Administrator Connection
spConfigureRemoteProcTrans 0 Used to force use of MSDTC for Distributed Transactions
spConfigurexpCmdshell 1 Used to enable use of xp_cmdshell by non-sysadmin users
spConfigureOptimizeForAdHocWorkloads 1 Used to enable optimisation of ad-hoc workloads. For more details see http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/should-i-optimize-my-sql-server-instances-for-ad-hoc-workloads-137132

Top

Manual SQL Server Surface Area configuration

The following steps show what you would have to do for manual SQL Server Surface Area configuration. FineBuild does all of this work for you automatically.

1) Start SQL Server Management Studio and open a query window.

Allow the display of advanced options by running the following commands:
sp_configure 'show advanced options', 1
RECONFIGURE 

2) Common configuration

This configuration is done for all SQL Server editions. If a Named Instance is being configured then complete the rest of this section using that instance, otherwise use the default instance.

Enable the following options using the sp_configure options shown below.
Function SP_Configure Option
SQL Agent full functionality 'Agent XPs', '1'
CLR status 'clr enabled', 'spConfigureCLREnabled'
Disallow results from triggers 'Disallow results from triggers:', '1'
OLE Automation procedures 'Ole Automation Procedures', '1'
Remote use of the Dedicated Administrator Connection (DAC) feature 'remote admin connections', 'spConfigureRemoteAdminConnections'
Protect server to server transactions by using MSDTC 'remote proc trans', 'spConfigureRemoteProcTrans'
SQL Management procedures 'SMO and DMO XPs', '1'
Use of xp_cmdshell 'xp_cmdshell', 'spConfigurexpCmdshell'

3) Standard Edition additional configuration

If Standard Edition for SQL 2008 R2 or above has been installed, the following options should also be set:
Enable backup compression 'backup compression default', '1'

4) Business Intelligence Edition additional configuration

If Business Intelligence Edition for SQL 2012 and above has been installed, the following options should also be set:
Enable backup compression 'backup compression default', '1'
Ad-Hoc workload optimisation 'optimize for ad hoc workloads', 'spConfigureOptimizeForAdHocWorkloads'

5) Enterprise Edition additional configuration

If Enterprise Edition for SQL 2005 and above has been installed, the following options should also be set:
Enable backup compression 'backup compression default', '1'
Enable extended key management 'EKM provider enabled', '1'
Ad-Hoc workload optimisation 'optimize for ad hoc workloads', 'spConfigureOptimizeForAdHocWorkloads'

6) After all options have been set, run the following command:
RECONFIGURE WITH OVERRIDE 

Copyright FineBuild Team © 2012 - 2017. License and Acknowledgements
Previous Configure SQL Service Recovery Top Configure Errorlog Retention Next

Last edited Jan 6 at 2:59 PM by EdVassie, version 3