[object Object]

Hosting ASP.NET apps on AWS Part 11 - Database in MS SQL server

How-to written and screenshots taken on 2020 January 31

1. SQL server

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.

2. SSMS

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.

  1. 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 Windows Authentication.

    connect to the database engine through remote desktop

  2. Right-click the server engine (server name\SQLEXPRESS (SQL...) and click Properties.

    go to properties of database engine

  3. Click Security and select SQL Server and Windows Authentication mode. Press OK.

    select server authentication mode

  4. Now expand Security -> Logins, Right-click the sa user and click Properties.

    open sa user properties

  5. In the General page enter a password and confirm it. Now go to the Status page and set Login: to Enabled. Click OK.

    system admin user settings

  6. 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.

  7. 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.

    login to remote SQL server database

  8. When successful, uninstall SSMS from the EC2 instance to safe disk space and memory.

3. 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.

  1. Right-click the database server and click Properties.

    go to SQL server properties

  2. 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.

    set maximum server memory

  3. Update May 2, 2020: All of this didn't solve our problems. We ended up migrating the SQL server database to our Ubuntu instance.

4. Transfer the database

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.

4.1 Export

  1. 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....

    find back up task in databasemenu

  2. Check if the correct Database is selected <database>. Select Backup type: Full and under Destination select Back up to Disk.

    select database export parameters

  3. 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.

    select base folder and filename for export

  4. Now click OK twice and the database will be exported.

    backup destination selected

  5. If you get an error, try this solution:

    https://stackoverflow.com/questions/3960257/cannot-open-backup-device-operating-system-error-5

    database export error 5
    Operating system error 5.

    database backup successful
    Error resolved by granting permission to backup/export folder to MSSQL$SQLEXPRESS user

  6. Download the database export, the "dump", to your computer.

4.2 Import

  1. Copy the database dump to the EC2. Copy it and paste it in a folder through the remote desktop connection.

  2. Right-click the Database folder in the Object Explorer and click Restore Database....

    find restore database in menu

  3. Select Device and click the "3 dots" ... In the next screen click Add.

    open a database file on your device

  4. Browse to the folder where you saved the database export, select All Files(*) and select the database file. Click OK twice.

    browse to the database file

  5. Check if the database name is correct and if so click OK to import (restore) the database.

    check database name and restore

5. App pool credentials

To allow the website (app pool) to connect to and manipulate the database, we have to add it as a database user.

  1. In SSMS expand Security, Richt-click Logins and click New Login...

    add new database login

  2. 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

    enter the application name

  3. Now select the User Mapping page and select the database <database>. Once selected add the roles db_datareader and db_datawriter. Click the dots in the Default schema column ... and type dbo in the user field. Now click Check Names and OK twice.

    configure user mappings

6. Next: SSL

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.