1

I have upgraded SQL Server 2008 R2 Express to SQL Server Standard Edition. After I have upgraded it, the SQL Server Agent was stopped.

I have tried to start but it fails.

Only when I have changed his built in account from "Network Service" to "Local System" it started.

Looking at MSDN How to: Upgrade to a Different Edition of SQL Server 2008 R2 Procedure 13 it says:

Provision the SQL Server Agent service account by using SQL Server Configuration Manager

  1. What does it mean provision it?
  2. What built in account should the SQL Server Agent run with?

Thank you

1

Let me answer your question point wise

1.SQL Server express has SQL server agent code built in but disabled. When you go to sp_configure you can see Agent XP enable/disable option this is so because if any point of time you are planning to upgrade SQL Server Express Microsoft upgrade process does not have to do much change but just a minimum to enable SQL server agent functionality. So before upgrading to Standard it wants you to use proper service account as a start up for SQL server agent so that after upgrade when database engine tries to bring agent services up it can do it eaisly. By default NT Authority/System account has admin privileges so it was able to bring Agent services up. Also please make sure that you always change SQL Server/Agent service account using SQL Server configuration manager it preserves the ACL's

2.As a good security practice SQL server should not run with Built in account instead a domain account should be created and granted least privileges to run it. Below Microsoft web resource will tell you what rights are required. The article is bit lengthy but beautifully written

http://msdn.microsoft.com/en-gb/library/ms143504.aspx

1

Provisioning the SQL Server Agent service account is simply referring to "configuring it". Which you stated you changed the service account from Network Services to Local System, that is provisioning the service to run as "Local System". It is just terminology used in their documentation.

Regarding the issue at hand, you need to review the system log within Event Viewer or the SQL Agent error log to determine what issue it is having. The link provides methods of viewing that log, you can also browser to your default log directory for the instance and find it there also. The name of the current error log file for SQL Agent is generally "SQLAGENT.out", any previous error log file is "SQLAGENT.x" (x = sequential number).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.