Part 1: Administering SQL Server Express on AWS EC2

In the past few years Diagram has hosted clients running small to midsize Episerver websites using local SQL Server Express edition instances on virtual servers. Having the databases on the same server as the website is nothing new and is common with Linux based CMSs. In the last few versions of SQL Server Microsoft loosened many of the resource limitations placed on the Express edition. This makes running a local Express edition instance for .NET CMS databases a more feasible option.

This blog will be the first of two related to administering SQL Server Express edition instances. I will go over setting up remote access and then working with the SQL Server tempdb data files to improve performance. In the second part, I will look at setting up an automated database backup and retention system.

SQL Server Express edition is a free scaled down version of Microsoft’s flag ship Relational DataBase Management System (RDBMS) SQL Server. Microsoft allows you to use SQL Server Express in a production environment, but restricts the resources the RDBMS engine can utilize as well as capping the maximum user database size at 10GB. The Express edition also does not come when many of the management tools, like SQL Server Agent, that make running SQL Server easier. You can find more information on SQL Server Editions and supported features on Microsoft’s website. (More info: Editions and supported features of SQL Server 2017)

We cannot get around the Express edition RDBMS engine’s resource limitation, these are built into the system and cannot be changed. With a few changes to the default configuration and some stored procedures, what we can do is make managing the Express edition simpler and more automated. 

Allow Remote Connections to SQL Server Express edition

When Express edition is first installed it does not allow for remote connections, connections from machines besides the one it is installed on. This makes it harder to administer because you need to log into the server to access the database engine with tools like SQL Server Management Studio (SSMS). It also stops other remote systems from being able to access the databases in the Express edition instance. An example of this would be a remote search server that needs to index the data in a website’s database.

To allow remote access on Express edition you first need to access the instance with a local copy of SSMS. Once connected to the database engine open the instance properties by right clicking on the instance name in SSMS and selecting "Properties." When the Properties window opens, click on the Connections page and check the “Allow remote connections to this server” box. Click OK to close the properties window and apply the change.

You can also do this with TSQL statements. Once connected to the database engine open a new query window and execute the following TSQL statements. These will enable the option to allow remote access.

   EXEC sp_configure 'remote access', 1 ;
   GO
   RECONFIGURE ;
   GO
   

Next open SQL Server Configuration Manger using the Windows Start Menu. Select SQL Server Network Configuration and then Protocols for SQLEXPRESS. In the Protocols window pane select TCP/IP. A new window with the SQL Server instances TCP/IP properties will open. Under the Protocol tab make sure Enabled is set as “Yes” and then select the IP Addresses tab. Once in the IP Addresses tab scroll to the IPALL section at the bottom. Make sure that TCP Dynamic Ports is blank and that TCP Port is set to the port number that you want to use to connect remotely to the instance. The default SQL Server port is 1433. In the following example, I am going to use port 4600. I have check my system and confirmed this port is not being used, if you use a port other than the default SQL Server port, you will need to do the same. Click OK to close the TCP/IP properties window. After making any change to the SQL Server Network Configuration of your instance you must restart the instance before they will take effect.

Once everything is setup to allow remoted connections with the SQL Server instance, you will need to make sure the AWS security group your EC2 instance is in will allow you to access the server on the port you have selected. For this demo, I have setup my security group to allow access from my home IP address to the EC2 server that I have my instance of SQL Server Express edition on. Both the Windows RDP port, 3389 and the port I selected for the SQL Server instance, 4600, are allowed.

You should now be able to connect to your instance with a remote copy of SSMS using the server’s IP address, the instance name and the port. If your EC2 instance is not running in a Windows domain, you will need to enabled SQL Server authentication and setup a SQL Server level account to allow you to login. In this example, I have setup a SQL Server account named, Test, to allow access.

Configuring SQL Server TempDB Data and Log Files

The SQL Server tempdb system database is a global resource that is available to all connections to the SQL Server instance. It holds temporary objects and data such as global or local temporary tables, temporary stored procedures, table variables, or cursors. If the data files for the tempdb are not correctly configured than accessing objects and data in the tempdb can become a bottleneck that reduces the overall performance of SQL Server.

The rule of thumb for the number of tempdb data files a SQL Server instance should have is one for each CPU core or virtual processor the instance as access. This means that for a EC2 Windows server that has two virtual processors, there should be two tempdb data files available for SQL Server to use. If the server has four virtual processors, then there should be four tempdb data files. The current version of SQL Server Express edition has a built-in limit of the lesser of one CPU socket or four cores/virtual processors. This means you can have more then four tempdb data files for an Express edition instance, but having more than four is unlikely to improve performance.

A common AWS EC2 instance type for running smaller website using a .NET CMS like Episerver is the t2.medium. The t2.medium has access to two virtual processors and 4 GB of RAM. The SQL Server Express edition installer by default sets up only one tempdb data file. If you were running Express edition on the same t2.medium EC2 instance as your Episerver site you would need to add another tempdb data file. Having two tempdb data files will enable SQL Server to get the most performance from the two virtual processors.

To add another tempdb data file you need to connect to the SQL Server instance using SSMS. Once you have done this expand the Databases folder and then the System Databases folder. Right click on the tempdb database and select Properties. In the tempdb Database Properties windows, select the Files page.  Add another data file by clinking the Add button at the bottom right. For Express edition, I recommend using an initial data file and log file size 100MB. Make sure auto growth is enabled and the growth steps are set to a size around 64MB. Adding more than one tempdb log file will not help performance because of the way SQL Server writes to log files.

Often SQL Server’s will need to read from and write to the tempdb files at the same time it is reading from and writing to the user database files. If your EC2 instance has access to more than one EBO volume, I recommend placing the tempdb files on a different volume then the data files. This will help to reduce IO bottlenecks.

With remote access configured and the correct number of tempdb data files setup in your SQL Server Express instance you are almost ready for prime time. The only thing you need now is an automated backup plan to insure the data in your databases can be recovered if there is an issue. In the second part of my Administering SQL Server Express on AWS EC2 blog, I will go over setting up automated database backups.