The world’s leading publication for data science, AI, and ML professionals.

Learn SQL Server Management Studio – Part 11 Intro to SQL Database & Server in the Cloud (Azure)

The skillset that will make you fun at parties! Step by step.

Photo by Michael Dziedzic on Unsplash
Photo by Michael Dziedzic on Unsplash

In the Last Episodes…

Hi there! Welcome to the 11th instalment of a series of tutorials on SQL and SQL Server Studio. There’s a simple goal: To make you familiar and comfortable with the tool, and the language. "Why does it even matter?" I see you asking. It turns out that curiosity and side projects are often key in getting picked on new projects or even getting hired for a new job. The mere fact you’ve already used an important tool such as SQL Server Studio and wrote some SQL queries, can and will give you a clear head start.

And don’t forget to come back 😉 .

What to Expect?

With a good foundation in creating databases, stored procedures and schedules on on-premise servers, understanding how to handle the equivalent in the Cloud is a natural next step. Cloud usage is ramping up, and with it, the need of having skilled individuals who can leverage it. Even a remote understanding of the Cloud and how to set up some environments can define whether you’d get on a project or land a job. Curiosity is key.

In this tutorial, we’ll introduce how to create a server and a database in the cloud. We will then connect our Cloud server to our SQL Management studio desktop app. By the end of this tutorial, you’ll feel empowered to leverage the power of Cloud Computing and hopefully dive into the deep end. Let’s jump right in!

Introduction

The Cloud computing space is filled with the usual suspects, the few famous households you’d expect: Azure (Microsoft), GCP (Google), AWS (Amazon), Alibaba, IBM Cloud. There are of course other players. The Cloud platforms aforementioned offer similar services e.g. compute, storage, backup, AI/ML, database, etc.

For this tutorial, we will be using Azure Portal. Without further ado, let’s get started.

  1. Log in to Azure Portal
  2. Within the top search bar, type Sql Server. You might be eligible to start with a free trial with a $200 credit.

Select +CREATE, you’re then taken to a new screen

  1. Pick your subscription name, the resource group (or create one), enter a server name and location, then define a server admin login ID and password.

For the sake of our Tutorial, the other tabs (Networking; Additional settings; Tags) can be skipped. We can hit ‘Review + create’. It will then take about a minute for the deployment to happen. You can safely browse other pages of the portal.

  1. Now that we have a server, we can create a database. Search SQL database and select the service, then click +CREATE.
  2. We’re presented with a similar screen. Define the database name and select the server we just created.
  1. Next, we will define our requirements for Compute + storage. Click ‘configure database’. It is configured as ‘General Purpose’, with the Computer tier set as ‘Provisioned’ by default.

The monthly estimated bill for this setup would be $159.82. Depending on your needs, an alternative could be to select ‘Serverless’. The difference between these options is that for ‘server less’ approaches, compute resources are auto-scaled, while for provisioned, they are pre-allocated.

With a serverless setup, our estimated bill is significantly reduced to a few cents per month:

  1. You can further drive down costs by keeping auto-pause enabled, although this might mean a slower service if you’re querying your database after a while, as it will need to be ‘awoken’. If a matter of a few seconds is not a game-changer, this option would make sense.
  1. We can then hit ‘Review + create’. As for the setup of our server, it will take about a minute to be deployed. Now that we have a server and a database, let’s connect to our SQL Server Studio.

Connect Azure Cloud Server to SSMS

  1. We can find our recent resources on the main screen. Let’s select our database.
  1. We get a list of this database’s essentials. On the right-hand side hover over the server name, it ends with ‘.database.windows.net’, and copy it.
  1. Open SSMS. Within the Connect to Server window, paste the server we just copied. Then enter the admin ID and password you provided when creating the server. From the Authentication dropdown, select ‘SQL Server Authentication.
  1. The Azure server will then appear on the right-hand side, with the database we create underneath it.
  2. Within this database, we can create a table the same way we did in Part 2 Databases, Tables & Naming Convention.
  3. The operations we’re doing on this new table are visible within our Azure Portal Database dashboard.

Final Words and What’s Coming Next

That’s it for now! I hope you now feel more confident creating and leveraging cloud resources such as SQL databases and servers to tinker around in SSMS. The Cloud offers an interesting alternative to on-prem services. Some of the benefits are:

  • Maintenance costs (hardware & software, including updates) are on the cloud provider,
  • Security,
  • Availability as per SLAs,
  • Resources and computing power can be scaled up and down,
  • Pay as you use options,
  • Backup (disaster recovery/business continuity, loss prevention)

I’ll admit there might still be some anxiety related to bills in the beginning. Options to auto-pause resources can help relieve this stress. Customer service is often helpful, I have had occasions when I obtained a refund from other cloud providers on services that were still up and running but which weren’t used (my bad 😅 ).

Thanks for reading, let me know what you think or if there’s a topic I should cover. In the meantime, feel free to subscribe and follow me. Until next time!

Happy Coding 🎉 !


Thanks for reading! Enjoyed this story? Join Medium for complete access to all my stories.


Related Articles