“The SQL Guy” Post # 13: Changes to Location of Startup Options in SQL Server 2012
Many users have hated the way SQL Server supported configuring startup parameters in previous versions. It was confusing and buried in a single text box under the Advanced Tab of SQL Server services in Configuration Manager. The only way you could separate different options is by using a semicolon(;) between the options.
With the introduction of SQL Server 2012, this is no longer an issue. The Startup Parameter option has been relocated to its own tab and can be easily accessed from SQL Server Configuration Manager.
CONFIGURING STARTUP PARAMETERS USING SQL SERVER 2012 |
1. Launch SQL Server Configuration Manager from Configuration Tools folder
2. Click on SQL Server Services from the Left Pane. You should now see all the services associated with SQL Server on the right pane.
3. From the right pane, right-click on the “SQL Server Instance” you wish to configure startup options for and then click “Properties”
4. You will notice a new tab called Startup Parameter. You can now specify the startup option by typing the parameter in the “Specify a Startup Parameter” box.
5. Once done, click “OK” button.
6. Restart SQL Server Database Engine for changes to take effect. |
CONFIGURING STARTUP PARAMETERS USING PRIOR SQL SERVER VERSIONS (PRE SQL SERVER 2012) |
1. In SQL Server Configuration Manager, click SQL Server Services.
2. In the right pane, right-click SQL Server (<instance_name>), and then click Properties.
3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;). |
BEST PRACTICES / THINGS TO KEEP IN MIND:
1. Always restart SQL Server services after making changes to startup options.
2. Never update/edit the SQL Server registry to add/edit any startup option. This could corrupt your SQL Server installation.
3. On a cluster, changes must be made on the active server when SQL Server is online, and will take effect when the Database Engine is restarted. The registry update of the startup options on the other node will occur upon the next failover.
