Most of the DBAs, Data Scientists, BI and Database developers are working on SQL Server on-premises. Is it time to migrate to the Cloud?
In this new article, we will talk about numbers, statistics, Azure technologies like Virtual Machines with SQL Server, Azure SQL, Azure Data Factory, Azure Machine learning and other technologies.
If you are not familiar with the Azure terms and technologies, this article is for you. We will give you an introduction to Azure technologies.
This article will answer the following questions:
- What is Azure?
- Is Azure Popular compared with Amazon Web Services (AWS) or other cloud services?
- How can I migrate SQL Server on-premises to Azure?
- What databases are available in Azure?
- What is the difference between Azure SQL and Azure SQL Data Warehouse?
- What is an elastic database pool?
- What is Redis Cache?
- I am using Linux. What are my options in Azure?
- Can I setup an Oracle or DB2 database in Azure?
- Is it possible to handle Big Data in Azure?
- What is Azure Storage, Azure Data Lake and what are the differences?
- What is Azure Machine Learning?
- What is the difference between SQL Server Data Mining and Azure Machine learning?
- What is Azure Data Factory?
- What is the difference between Azure Data Factory (ADF) and SSIS?
What is Azure?
Azure is a Cloud Service Platform where you can create Virtual Machines, SQL Server Databases, MySQL Databases, reports, Websites, Ethereum blockchain applications as well as store files and handle Big Data. It was announced in 2008 and released in 2010.
Is Azure Popular compared with Amazon Web Services (AWS) or other cloud services?
According to the Synergy Research Group, AWS has approximately 33% of the share revenue, Azure is in second place with 11% and then is IBM with 8%, Google 5%. There are hundreds of cloud providers. Here you have a list of some of them:
AWS is winning the race (by far), but Microsoft is growing faster. According to the same reference, the Market Share Gain in the last 4 quarters was 1% by AWS and Azure 3%!
According to the Microsoft Shareholder Letter, the cloud revenue per year was $12 billion and 70 million people use Office 365. Revenue from Azure grew triple digits and database usage doubled each year.
How can I migrate SQL Server on-premises to Azure?
You can migrate your data to Azure Virtual Machines where you can install SQL Server or use Azure Templates or use Azure SQL. Azure SQL is a service where you have a Database to connect and you do not need to worry about the operative system. You can have a Database in the Cloud in few minutes with automatic backups.
What are some Virtual Machines in Azure (note that you can have Windows or Linux machines):
There are several options to migrate the Data to Azure. You can use SSIS, the Azure Portal, BCP, Linked Servers, PowerShell, The command line, DMA, SSMS, etc.
What databases are available in Azure?
By the moment you can have the following Databases as services:
- Azure SQL
- Azure SQL Datawarehouse
- Azure Elastic Database pool
- Azure Database for MySQL
- Azure Database for PostgreSQL
- Azure Cosmos DB
- Database as a service for MongoDB
- Redis Cache
List of options in Azure:
In a virtual machine in Azure, you can install any database supported by the OS.
What is the difference between Azure SQL and Azure SQL Datawarehouse?
Azure SQL is a simple database (with some replications depending on the configuration specified). Azure SQL Data Warehouse is a MPP (Massive Parallel Processing) that allows to process huge queries using multiple SQL DB Nones. That is why an Azure SQL Data Warehouse is more expensive.
What is an elastic database pool?
An elastic database pool is used to group several databases and pay an optimized amount for all the databases with variable usage.
What is Redis Cache?
Redis (Remote Dictionary Server) is a NoSQL in-memory database. It is an open-source and supports string, lists, maps, sets and more types of data.
I am using Linux. What are my options in Azure?
Azure SQL is in the Cloud, it can be administered using a browser in any operative system. You can access to the Azure Portal from your tablet, smartphone, your Linux machine or Windows machine. Approximately 33% of the virtual machines in Azure are using Linux.
The following browsers are supported:
- Internet Explorer 11.
- Microsoft Edge (latest version).
- Chrome (latest version).
- Firefox (latest version).
- Safari (in Mac only).
You can always have a virtual machine with Ubuntu, RedHat, SUSE, CoreOS and more.
Some VMs supported:
You can also use MySQL or PostgreSQL as cloud services and create your WebSite or another language of your preference in Azure and connect to the Azure Databases.
Azure supports PowerShell for Linux, Visual Studio for Linux, PHP, Java and more languages.
Can I setup an Oracle or DB2 database in Azure?
There are some nice VM templates to setup Oracle or DB2.
The DB2 template for VM:
Some templates for Oracle VMs:
Is it possible to handle Big Data in Azure?
Yes, Azure includes HDInsight. You can have your Hadoop, Spark, R Server, HBase, Storm , Kafka in Azure.
You can store HDInsight in an Azure Storage or Azure Data Lake and Analyze the Data using Azure Machine Learning.
What is Azure Storage, Azure Data Lake and what are the differences?
Azure Storage is a scalable, redundant, secure storage in the Cloud. Azure Data Lake is a hyper-scale repository optimized for big data and parallel processing.
What is Azure Machine Learning?
Microsoft offers this technology in the cloud to process and forecast data. You can use machine learning for churn prediction, text translation, facial recognition, heart desease detection, breast cancer prediction, fraud detection.
What is the difference between SQL Server Data Mining and Azure Machine learning?
If you want to kill a mosquito, you can use a fly swatter or a bazooka. Azure Machine Learning is the bazooka. It is very powerful and it can be integrated with R, Python that are very popular in Machine Learning and other purposes.
SQL Server Analysis Services (SSAS) comes with Data Mining. It is included with a SQL Server license and it is very simple. You can use a Wizard and easily create some prediction analysis. You can create your own algorightms, however, it is not an easy task and they are less samples than Azure.
For languages required, you can create your Analysis in SSAS Data Mining without any programming knowledge (nor statistical knowledge) easily. If you want to customize your data, you can use DMX (Data Mining Extensions) for custom queries or automate administrative tasks. You can also use XMLA as a Data Definition Language (DDL). Data Mining can be integrated with SSIS which can allow more versatile applicability. It is also possible to automate Data Mining Tasks in PowerShell.
On the other hand, we have Azure Machine Learning. As we said in can be integrated with R, Python. The Machine learning results can be displayed in a Web Service then it is easy to use the data in any programming language.
Azure Machine Learning (AML) is very popular and SSAS Data Mining is not as much so. There are more articles, samples, code, videos about AML. It looks like it may supersede SSAS Data Mining in the future.
About the interface, AML looks like SSIS. You have to drag and drop tasks and algorithms. Data Mining is a Wizard. Is less flexible than AML where you can create more sophisticated and customized models.
What is Azure Data Factory?
It is an extract-transform-load (ETL), extract-load-transform (ELT) tool in Azure.
What is the difference between Azure Data Factory (ADF) and SSIS?
They can be used for the same purposes, but they are very different in design and architecture. Currently, SSIS is more popular and well known. ADF is new and few people know the tool. ADF is designed to handle big data. Is it designed for a big amount of data? It is also designed to work specially with Azure Data and it can handle relational and NoSQL data. You can handle NoSQL data in SSIS, but SSIS is specialized in relational data.
If you are migrating data from servers on-premises, it is better to use SSIS. If you are working with Azure migrating to Azure from a server on-premises or from other Azure Data Center, ADF can be a good choice. Anyway, what we notice is that ADF is evolving a lot and the usage, features, sources, and destinations supported are growing each day.
Conclusion
In this article, we analyzed that Azure is growing a lot. SSMS is improving the integration to Azure and some Azure features are easier to use and learn.
It is hard to say if Azure will be the leader and surpass AWS. By the moment, we can say that the Cloud usage is increasing and it is a good idea to learn cloud technologies in general, but particularly Azure because it is growing in popularity.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023