Things we should be Aware of in SQL Server DENALI Configuration Manager, A Small simple change which might make a huge difference!
Before proceeding any further, let me make it very clear….for any changes to me made on any of the SQL Server Services you’ve to make use of SQL Server Configuration Manager, not our typical windows services from administrative tools.
I’ve seen in the past(still seeing few folks doing the same even now!!) where people going to “RUN” and entering “services.msc” for changing SQL Server Service accounts, Start Up type etc….Hope you are not doing the same!
Config Manager in SQL Server 2008R2:
Okay, Let’s jump into Config Manager in SQL Server 2008R2, when you choose properties of SQL Server Engine we’ll be getting what is shown in the screenshot below.
This is the place where we’ve to change the Service accounts and pwd’s. You can change the Start Mode(Automatic/Manual/Disabled) in the Service Tab. For adding any start up parameter, you’ve to go to your advanced Tab as shown below.
Configuration Manager in DENALI:
See the screenshot below and if you see carefully, you’ll notice a new tab for startup parameters!
Basically, DENALI has 2 new tabs, one for Brand new fabulous feature “AlwaysOn HA” and the other for “Starup Parameters”. Let’s see what’s present in my AlwaysON tab…
Well, I’ve Installed this Instance of DENALI on my WINDOWS 7 x64 Client OS which is a stand alone, hence unfortunately, I can’t show you much about this setting here 😦 (I’m planning to Install DENALI on my LAB Cluster pretty soon to explore and learn this brand new HADR feature of DENALI…yayyyyyy)
Let’s move on to Starup Parameters Tab, You can see the brand new interface as shown below.
IMHO, this makes all of our(DBA’s) lives much easy dealing with those annoying semi-colons “;” and Spaces where we should be very very cautious upto SQL Server 2008R2. A single space will change the entire behavior of your startup parameter(s).
Let me show you how we can add a trace flag as a starup parameter in DENALI. Let’s try to enable Trace Flag 3226 as a start up parameter to disable all the successful backup events being logged in our error logs.( See here if you want to know how you can enable a trace flag globally instead of making it a startup parameter). All you need to do is Just typing “-T 3226” and click Add ->Apply->Ok (Service restart is required to reflect the changes) as shown in the below screenshot.
If you want to Update any parameter, all you need to do is Just click on the parameter which you want to change and make changes and click on Update Button. ( The Add button will change to Update button once you select any parameter as shown below).
In the same manner you can remove any Startup parameters which you’ve added.
This greatly reduces pain in our Butt dealing with Spaces and Semicolons( I’m sure that almost everybody agree with me in this matter, especially who’ve already faced an issue and wasted time(like me when i was a Novice in SQL) figuring out what’s wrong with the parameter provided and banging head to your desk, OMG! why this SQL Server is not starting up as expected 😦 )
Hope this post helps!
MSSQLTREK 