A good achievement of mine in 2016 was convincing a Friend from Netherlands to use Microsoft Azure to deploy back-end for his mobile app. We had a very long conversation after office hours and he was arguing, instead of Azure, he can host his DB server locally and do the stuff, he had a point, all my arguments were about scaling and enterprise level security but most of the time DEVELOPERS always need to get hands dirty. Also earlier this month another friend of mine wanted me to develop an app for his idea. Developing an app and hosting the app services on Azure is not a big deal but this time I wanted to try things and get hands dirty. Even though my experiences with MySQL and Mongo, I didn’t try hosting a SQL Server DB before (out side my office work) since “money” but, I thought to try with the new SQL Server on Linux offering, for which I don’t need to care about “money”.
Didn’t wanted to risk my Acer Aspire P3 tab with Ubuntu 16.04 since many people use it on weekends. But that would be an ideal way of doing it. So decided to create a cheap Ubuntu 16.04 VM on Azure. I have chosen F2 type VM with 4Gigs of RAM residing on East US 2. Its probably the cheapest on since you need 3.5 Gigs of RAM for installing SQL Server on Ubuntu.
Installing SQL Server
Its just the matter of executing some commands from SSH. I used WebSSH. But most of the people ( and me also most of the time) use PuTTY. I didn’t install any desktop envs on this machine. Since, Ubuntu executed the following commands (also provided in the documentation)
Done with installation. Now we have to run the configuration script came with the installation where we need to configure SA account, its just about giving password for the account and set the SQL Server service to start on logon. So, execute. Give password for SA and say “Y” to start the service on startup.
After this, verify if things went smoothly by executing
To install SQL Server tools for Linux be executing the following set of commands
And to create symlinks to ‘SQLCMD’ and ‘BCP’ under /usr/bin/
Go to the ENDPOINTS tab on VM’s windows and click ADD to add MSSQL and leave the default settings (if Public Port is not set to 1433, set it and save).
Now you are OK to connect to the SQL Service service hosted on the VM remotely.
Accessing DB Remotely
Before this, we need to create a Database on our newly created service. So go back to SSH and execute the following
Now we have created a new database called quickcustinfo_db.
Go to Visual Studio -> Server Explorer -> Connect To Database
Note that I have selected SQL Server Authentication and gave SA and my password (this is kind of risky business, still for my POC I have decided to go like this, BUT MANIPULATING DATABASE AS “SA” FROM CLIENT APPLICATIONS IS NOT ADVISED). When I populate the combo box to select database name I will be able to see “quickcustinfo_db”. Select it and Press OK.
Go to the properties of this database and copy the connection string. It will look like
Copy and paste it in a notepad and replace *********** with actual password.
ASP Web API2 Application – Web.config
My idea is to use EF. So created a new WebAPI2 application. Here in the Web.config file, we need to give the above connection string by replacing the default one. So we can avoid data to be seeded and manipulated on (localdb) and put them to our remote database hosted on Linux. So I did the changes like below.
Now its all about creating models and seed the data.
For this POC, I created two models
Contollers and DataContext
Also create a contoller with option “WebAPI2 controller with actions using Entity Framework” (this is one of the coolest feature I love). Select Customer model first and give a new generic DataContext class and controller name. Do the same for Invoice model with the same DataContext class. Rebuild the project.
In the package manager console execute
Now expand the newly created Migrations folder in solution explorer and open Configuration.cs and modify Seed() like below.
Note the DbMigrationConfiguration is typed with the thing we gave earlier – QuickCustInfo.Models.QuickCustInfoContext (DataContext).
Again in the Package Manager Console
Thats it, DONE 🙂
Make Sure Things Went Well
Go to the SSH again, open SQLCMD and execute the below
and if everything went well,
Host the WebAPI project to a free tier Azure Web Service offering and call the APIs from your Clients to do the CRUD operation.
POC was successful and make this without any expenses is just the matter of hosting the SQL Server on a local Ubuntu machine.
Things are possible and happy coding.