For our application we chose to use the Microsoft SQL server engine. The reason? Well, Microsoft is very good at nudging developers using Visual Studio to choose MS SQL server. For small databases that is fine. The Express edition is free and has sufficient capacity.
As we have chosen an EC2 instance with limited memory, we have noticed some memory issues. The SQL engine is constantly using 10-20% CPU which seems to be related to insufficient memory. And after importing a database the instance is almost unusable while creating the indexes, which takes up 45 minutes to an hour.
To limit the issues as much as possible we have to run as little programs as possible. A better solution would be to move the database to a separate instance, but that will cost extra money. We will be moving to a MySQL/Aurora/MariaDB solution soon though, more on that in appendix E.
An easy way to manage the database is through SSMS (SQL Server Management Studio). It is installed by default in the AMI by AWS that we installed. It does however takes memory to run.
Luckily the SQL Server engine can also be accessed remotely, so we'll delete the SQL Server Management Studio on our EC2 instance and install it on a local machine instead.
Before we do that we are going to use it one last time to add a remote user.
Login to the EC2 instance through remote desktop. Open SSMS and connect to the Database Engine. The Server name should be
<computername>\SQLEXPRESS and Authentication
Right-click the server engine (server name\SQLEXPRESS (SQL...) and click Properties.
Click Security and select SQL Server and Windows Authentication mode. Press OK.
Now expand Security -> Logins, Right-click the sa user and click Properties.
In the General page enter a password and confirm it. Now go to the Status page and set Login: to Enabled. Click OK.
We already opened the firewall port
1433 during setup of the EC2 in part 5. There we also already attached a security group
sg-ms-sql, so we should be able to login to SQL server remotely from local machine.
Open SSMS on your local machine and enter your domain (awesome!) or the Public DNS as Server name. Select SQL Server Authentication and enter the login credentials: Login: sa, Password: as entered in step 5. You can only login from your office/home IP.
When successful, uninstall SSMS from the EC2 instance to safe disk space and memory.
A possible cause of the constant CPU use could be the high amount of memory utilization. It is above 90% most of the time so possibly the SQL server is trying to scrap together memory and over eagerly busy with garbage collection (or something, I have no idea what it is doing).
We can however limit the amount of (reserved) memory the SQL server is controlling. This should in theory stop the server from trying to clean up unnecessary amounts of memory. This kind of works, but obviously limits the performance when the database grows.
Right-click the database server and click Properties.
Got to the Memory page and set the Maximum server memory to
512 MB. This is by no means a well researched limit. It's my initial guess and it seems to work for our single database which is currently ~90 MB, which has a memory consumption of ~105 MB. Please do consult a database expert and/or Google and run your own tests.
Update May 2, 2020: All of this didn't solve our problems. We ended up migrating the SQL server database to our Ubuntu instance.
Alright, our database server is setup so we can finally import the actual database. I'll assume you already have a database running for development or in another production environment and that you're using a similar SQL server version. Let's first export a database and then import it.
Open SSMS and connect to your development database server. Now Right-click the database you want to export, go to Tasks and click Back up....
Check if the correct Database is selected
<database>. Select Backup type:
Full and under Destination select Back up to
Now click Add... and browse to the folder you want to "dump" the backup to (annoyingly you can't select all folders, we'll fix that in the next step). Select
All files(*) for Files of type and enter a filename like
<database>-export. Click OK.
Now click OK twice and the database will be exported.
If you get an error, try this solution:
Download the database export, the "dump", to your computer.
Copy the database dump to the EC2. Copy it and paste it in a folder through the remote desktop connection.
Right-click the Database folder in the Object Explorer and click Restore Database....
Select Device and click the "3 dots" ... In the next screen click Add.
Browse to the folder where you saved the database export, select All Files(*) and select the database file. Click OK twice.
Check if the database name is correct and if so click OK to import (restore) the database.
To allow the website (app pool) to connect to and manipulate the database, we have to add it as a database user.
In SSMS expand Security, Richt-click Logins and click New Login...
Type the application pool name
IIS APPPOOL\jodibooks-test in the Login name: field.
Note 1: You need to have inbound port 445 opened to allow searching for users. I added that in part 5. https://harvarinder.blogspot.com/2019/03/the-program-cannot-open-required-dialog.html
Note 2: to get this to work, I had to login with the Windows EC2 admin credentials first. Then I got a screen where I could search for users. Click the Object types button and select Groups. After that it works. http://www.mssqldestination.com/blog/manjunathcbhat/2014/object-user-group-or-built-security-proncipal-following-name-cannot-be-found
Now select the User Mapping page and select the database
<database>. Once selected add the roles
db_datawriter. Click the dots in the Default schema column ... and type
dbo in the user field. Now click Check Names and OK twice.
With the database configured, the application should be fully functional. However, we do want to offer secure connections to our website, so in the next part we'll generate our SSL certificate and configure it in IIS.