SQL Server Express edition is a fantastic product as an entry into using SQL Server for your applications. The main benefit this edition of SQL Server brings to the table is it is free. While this does allow you to get started and have your applications connect to SQL Server and get all of the benefits that SQL Server provides, because this is a free edition it does have some limitations :
- 10 GB database size
- Lesser of 1 socket or 4 cores
- Maximum Memory – 1410 MB
- No SQL Server Agent
With the above limitations, this product can provide all of the functionality for your application to operate as required. Because there is no SQL Agent service in this edition this makes it more difficult for you to implement scheduled database maintenance. We are not going to discuss ways to implement database maintenance on an Express edition of SQL Server in this post.
But at some point in time you may find yourself in the position that you need to perform an upgrade of editions. This may or may not include and upgrade of versions. An upgrade to the version can be achieved and can bring benefits of its own. Normally, I would not be writing about and recommending performing in-place upgrades. However, sometimes due to restrictions of vendor applications it is a requirement to perform an in-place upgrade. With that in mind, we are performing the following:
- Version Upgrade (2014 to 2019)
- Edition Upgrade (Express to Standard Edition)
- In-place upgrade
With performing this upgrade we now get the benefit of having access to more resources as well as the SQL Agent service to more easily schedule jobs and that all important database maintenance. After the upgrade, if you previously had a connection open in SSMS to the instance, you will need to disconnect and reconnect, for the Agent Service to now be visible for you to access.
Now by default, because Express edition does not have the SQL Agent service, after the upgrade we need to enable the new Agent service. We can achieve this as follows:
- Open SQL Server Configuration Manager
- Right Click on the SQL Server Agent Service (Currently Stopped) | Select Properties
- Click on the Service Tab and change the Start Mode to Automatic
- Click Apply
- Click Ok
The above change means that in the future when a reboot is undertaken, the Agent Service will start for us automatically. This does not start the service after making the change, so we have to do that manually.
If you right click on the Agent Service and hit Start, this will actually fail if you do not make some changes first.
If you are running in a domain attached environment, ideally you will have configured domain service accounts for both the SQL Service and SQL Agent services and will need to change the services to start under these accounts. Or to be far more secure, you will have configured gMSA accounts.
If you are running in a stand-alone environment, you may want to create a local account and like the domain service accounts, we change services to run under the new accounts. For this post we are showing the use of the built-in security account which is the default for the Agent Service to be configured and run under.
Because Express edition does not have an agent service, the initial setup does not grant the appropriate permissions for the agent to have access to the instance of SQL Server and allow for the Agent to start.
In the instance of SQL Server, we need to add the account that the Agent Service is running under and grant it a member of the ‘Sysadmin’ server role group.
In SQL Server Configuration Manager we are now able to start the agent and we are good to go.
Leave a Reply