Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- SQL Server Blogs -

SQL Server on the Cloud - It is not that Cloudy



Introduction

Till a few years back, we used to visualize the SQL Server database being hosted in a physical server inside client premises. The database could remain in a single server or could span to multiple servers. 

The advantage of an on-premise server is as follows.

  1. The total cost of ownership
  2. Complete control of hardware and software
  3. Internet connection is not required and the system can run on local networks.

But on-premise servers had a few drawbacks.

  1. The Maintenance cost is high.
  2. It takes more time to implement.

Cloud-based database systems are more flexible, reliable, and secured. The maintenance of the cloud-based system is less costly as most of the maintenance headache is taken by the vendor who is hosting the cloud. There are a few popular cloud hosting services for SQL.

  1. Microsoft SQL Server on AWS.
  2. Cloud SQL for SQL Server.
  3. Microsoft Azure SQL Server Service.

We will discuss all these services in the next few paragraphs of this write-up.

Advantages and disadvantages of Cloud Server

Advantages

Disadvantages

Easy Implementation: A business will be able to run all its existing application and business processes without having to think about the technical problems in the backend.

No longer in control: When we move the services to the cloud, we do not have any control over the data and application.

Accessibility: Data can be accessed from anywhere via the internet.

May not get all the features: There can be significant differences in the services each cloud service providers provide.

No hardware is required: Since everything is hosted in the cloud so no need to maintain on-premise hardware.

Still have to maintain servers: You still have to maintain server and IT staff for backup and recovery of data.

Cost Per head: Overhead cost of technology is minimum.

No Redundancy: A cloud server is not redundant nor it is backed up. There can be significant risk of loss of data if it crashes.

Efficient Recovery: Cloud computing delivers faster and accurate retrieval of data. 

Bandwidth issues: For ideal performance, faster internet connection is required.

We will now discuss the popular SQL Cloud hosting services one by one.

Microsoft SQL Server on AWS

Microsoft SQL Server on AWS is a cloud service provided by Amazon. Using Amazon RDS for SQL Server it is very easy to set up, operate, and scale SQL Server deployments in the cloud. 

It supports the “License Included” licensing model. It is not required to separately purchase Microsoft SQL Server licenses. "License Included" pricing is inclusive of software, underlying hardware resources, and Amazon RDS management capabilities.

Some of the features of Microsoft SQL Server on AWS are as follows.

  1. It supports multiple instances, thus supporting almost any form of workload.

  2. It is very easy to setup. It can be up and running with just a few clicks.

  3. It is highly scalable, durable, and available while also being very simple to administer.

  4. Supports a manual snapshot. This allows the user to take a backup of an entire database with a single snapshot.

How to create and connect to a Microsoft SQL Server Database using Microsoft SQL Server on AWS.

Step 1

We need to first log into AWS management console. Then click on RDS under Database to open the Amazon RDS console.

SQL Server on AWS

Step 2

Next, we will use Amazon RDS to create a Microsoft SQL Server DB instance. This will have a storage capacity of 20GB and automated backups enabled. The retention period of the backup is one day.

In the top left corner of the Amazon RDS console, we can change the region in which we want to create the database.

SQL Server on AWS

Step 3

In the create database section we need to choose to create a database.

SQL Server on AWS3

Step 4

Next, we need to select the database engine. We select SQL Server Express here.

SQL Server on AWS 4

Step 5

Next, we configure the connectivity section.

SQL Server on AWS5

Click the Create Database button to create the database.

Step 6

Once the database is created we can view the database by clicking on view your DB instance.

How to connect to the Database using SQL client

Step 1

Download and install SQL Client in the cloud following the below screenshot.

Database using SQL client

Step 2

Once the installation is complete, configure the client as below.

Click on the connect button.

Step 3

This will connect us to the database we have just created. We  can now start creating tables and run queries inside the database using SQL Server Management Studio.

Cloud SQL for SQL Server

Google Cloud’s relational database offering is called Cloud SQL. It is a fully managed service designed to work with SQL Server. Cloud SQL is very similar to RDS, although it offers fewer database engines. It is highly available, scalable, durable, and very secure. Cloud SQL comes with automated backups for point-in-time recovery, and it integrates natively with other Google Cloud Services.

Some of the features of Cloud SQL are as follows

  1. Fully managed SQL Server databases in the cloud
  2. The data of the customer is encrypted on Google's internal networks and in database tables, temporary files, and backups.
  3. Support for secure external connections with the Cloud SQL Proxy or with the SSL/TLS protocol.
  4. Database can be imported using BAK and SQL files.
  5. Database can be exported using BAK files.
  6. Automated and on-demand backups.

Create an instance of SQL Server in Cloud SQL

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.
  2. Select your project and click Continue.
  3. Click Create Instance.
  4. Click Choose SQL Server.
  5. Enter myinstance for Instance ID.
  6. Enter a password for the sqlserver user.
  7. Use the default values for the other fields.
  8. Click Create.

Connecting to the instance using SQL Server Management Studio

Step 1

In SSMS select connect to object explorer from the file menu.

Step 2

Put the necessary configuration values and click on connect.

sql

Create a database and input data

Step 1

In the SQL Server management Studio right-click on the database tab and click on new database.

SQL Server management

Step 2

We will name the new database as testdb. Click ok after that.

SQL Server management

Step 3

Once the database is created select the testdb database. Expand and right-click on the tables tab and then new table. This would create a new table.

SQL Server management

Step 4

Once the table is created, data can be inserted into the table using SQL insert statement.

Microsoft Azure SQL Server Service

Microsoft’s Azure is, at the moment, the second most commonly used public cloud provider.

Azure’s database services are similar to AWS’ RDS and Google Cloud’s Cloud SQL in that they are products for which maintenance is handled by the public cloud provider.

Some of the features of Microsoft Azure SQL Server Service are as follows:

  1. Relational data storage for cloud-based applications and web sites
  2. Business and consumer web and mobile apps
  3. Manage databases for multi-tenant apps (software-as-a-service)
  4. Quickly create development and test databases to speed up development cycles
  5. Scale production business services quickly and at a known cost
  6. Containerize data in the cloud for isolation and security
  7. Outsource database management to focus on value-added services.

Create an instance managed instance in SQL in Azure Database Service

Step 1

Sign in to the Azure portal.

SQL in Azure Database Service1

Step 2

Select Azure SQL on the left menu of the Azure portal. If Azure SQL is not in the list, select All services, and then enter Azure SQL in the search box.

SQL in Azure Database Service

Step 3

Select +Add to open the Select SQL deployment option page. You can view additional information about Azure SQL Managed Instance by selecting Show details on the SQL managed instances tile.

SQL in Azure Database Service

Step 4

Select Create Database.

Step 5

Fill in the minimum set of information under Basic tab.

Create Database

Click Review + Create

Step 6

The following screen appears.

Create Database

To view the Resources created click on Go To Resource.

Step 7

Click on the Resource Group name and the following screen appears.

Microsoft SQL Server

Connect to the Microsoft Azure database

Step 1

Open Microsoft SQL Server Management Studio and provide the necessary credentials.

Click on Connect.

Step 2

The system connects to the Azure database.

Database

Create a table in Microsoft Azure SQL Database

Step 1

Right-click on the Table tab under the database and click on new.

Microsoft Azure SQL Database

Summary

The above write-up gave an introduction to the three most popular SQL Services available in the market at this moment. As discussed above all these three services has some advantages and disadvantages over others. We had also discussed in the beginning the advantages and disadvantages of Cloud services as a whole at the very beginning of the write-up. Each of these services is suitable for a particular scenario. It is up to the users to decide which one of these services are best suited for their business.


    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

5 days 31 Oct 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

28 days 23 Nov 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

2 days 28 Oct 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

4 days 30 Oct 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

-0 day 26 Oct 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

15 days 10 Nov 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

4 days 30 Oct 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

4 days 30 Oct 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

-0 day 26 Oct 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

-0 day 26 Oct 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

13 days 08 Nov 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

4 days 30 Oct 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews