Boost your SQL skills by setting up your own SQL Server on the cloud
In order to sharpen our SQL skills it comes handy for any web-developer to have his own SQL server database playground. This can also be particularly interesting for OutSystems Developers taking their first steps into the OutSystems and SQL world.
Having your own SQL Server database will speed up your learning and help you to more easily understand the concepts behind ‘Aggregates’, as well as prepare you to start building more complex queries in a structured and performant way using the OutSystems ‘Advanced Queries’.
How?
You’ll be able to use SQL Server Management Studio (SSMS) or Oracle SQL Developer (the IDEs offered by Microsoft, and Oracle are supported by the OutSystems Platform) to connect and experiment with your database. These powerful tools are designed to configure, monitor, and administer SQL Server instances and databases. With them, you’ll be able to create tables, queries, scripts, track & tune the performance of your queries, and much more.
Why?
Another reason to consider getting your own playground database lies in the fact that you cannot use SSMS or Oracle SQL to connect to your OutSystems Personal Environment database. This option is limited to Enterprise Editions of the platform.
Setting it up, the quick way
You could install your playground SQL server on your local machine, or even on a virtual machine. However, this can be time-consuming and probably not worth the effort.
Fortunately, the recent IaaS (Infrastructure as a Service) hype has brought us, developers, very useful — and especially “free” — tier plans which give access to cloud databases without having to be an expert to set up a server on our own.
In this post, I’ve opted to go for instructions to create a Microsoft SQL Server cloud instance, instead of an Oracle one, because Microsoft SQL Server is the most common setup used by OutSystems customers and is also the one I’m most familiar with.
After we’re done you’ll be able to use SSMS to connect to it and start playing!
Let’s dive in…
Step 1 — Register for a free cloud database
We’ll be using Microsoft Azure to set up the cloud database server, but free tiers are also available on other SaaS providers such as AWS and Google Cloud. Feel free to give a try to other providers and let me know how it went.
Go to the Microsoft Azure Portal . Click ‘Start free’.
Fill in your personal information…
Note that in the process you will be required to provide a form of payment. Don’t worry, I wasn’t charged for it. You’ll have 12 months with free access to a different number of services, including database servers, and then opt to pay if you wish so. However since conditions might change, do read the conditions before doing any payment!
I created a virtual VISA card with only 20 euros on it with MBway, a virtual credit card service that is available in Portugal, but any other similar service (i.e. Revolut) should work.
Step 2 — Create a server
Once you’re logged in, search for ‘SQL server’.
‘Add’ a new Server:
Create a new resource:
Choose a name:
Fill in the server details:
Now, wait for the deployment to be completed:
Once finished, click on ‘Go to resource’:
Step 3— Create a new database
We’re now going to create a database on the SQL Server.
Add a ‘New database’:
Note: You can choose to load your database with the free Microsoft sample AdventureWorksLT database which can be useful for your playground tests. I recommend you to do this since you’ll already have data to play with existing data.
Optional: change the pricing tier to a basic tier (reducing the space used and performance). This will cost less in case you want to start paying and keep using the service after the 12 months trial period has ended.
The database will be created in the background. You can check the notifications area to see the progress:
Step 4 — Connect to your cloud SQL Server using SSMS
Now that you have your own SQL Server database, open Sequel Server Management Studio (SSMS) and provide the credentials defined in Step 2 to connect to the SQL server.
Note: If you don’t have SSMS installed, you can download it here.
Provide the credentials:
SSMS will detect that you are connecting to a Microsoft Azure cloud SQL server, and will prompt you to add your IP on Microsoft Azure to connect to the Server. Just click Sign-In and provide your credentials to continue.
Add the firewall rule:
Step 5 — Start using your SQL playground!
And… voilá! You now have access to your databases on Microsoft Azure:
You’re finally ready to create your first query! Have fun!
Final words…
I hope you enjoyed this article. Please feel free to let me know about your opinion, ideas, or suggestions that you might want to share. All feedback is appreciated.
