Problem
My company wants me to look into Azure and how it can help, especially with our relational database – Microsoft SQL Server. What is an Azure SQL database? What can and can’t be done with Azure SQL workload? Why do companies choose a Microsoft Azure SQL Database? And how do I get started?
Solution
In this tutorial, we will answer several FAQs related to Azure SQL databases.
What is a Microsoft Azure SQL Database?
Azure SQL is a paid database service to store a SQL database in the Microsoft Cloud, known as Azure. Once paid for and configured, you can connect your applications to that single database. For more information on service tiers (General Purpose, Business Critical, etc.), compute levels (CPU, Memory, IO), pricing, etc. – click here.
For more information about Azure SQL, refer to this SQL Azure Create Database Tutorial.
It is different and easier to work with compared to an Azure Virtual Machine with SQL Server installed on it or an Azure SQL Managed Instance because you only have to deal with the database, not the server. All of the other management, setup, and configuration is performed by Azure.
What can I do with Azure SQL?
In basic terms, it is the regular SQL Server database engine as a cloud service. You can create tables, views, stored procedures, and insert, select, update, and delete data. Scalability is easy along with automatic backups, patching and apply high-availability features included to meet your Service Level Agreements (SLA) with clients and business continuity for your management. If you have an application in Azure or on-premises, you can connect to it. It is easy to connect a website or application to a database using Azure SQL.
In Azure SQL, you can also have active geo-replication for disaster recovery, auto-failover groups, auto-scale, automatic tuning indexes, backup retention for seven days (by default) and a maximum of 35 days, elastic jobs, geo-restore, and hyperscale architecture.
What cannot be done with Azure SQL?
Azure SQL is only a cloud database; it is not a complete SQL Server instance you maybe familiar with for an on-premises installation. If you need to apply cross-database queries or use SQL Server Agent, you can use an Azure SQL Database Managed Instance as another deployment option.
Here are some additional things that you cannot do with Azure SQL that you maybe accustomed to with your SQL Server databases:
- Backup using the backup command (you have automatic backups)
- Configure collation of the instance
- Create CLR assemblies
- Apply database mail features
- Create distributed partition views
- Create custom Files and filegroups
- Create linked servers
- Modify system data
- Apply OPENQUERY
- Apply query notifications
- Use Machine Learning Services
- Use the Resource governor
- RESTORE data using the T-SQL RESTORE statement
- Use the Service Broker
- Apply server configuration settings
- Configure and use the SQL Server Agent
- Apply SQL Server Auditing
- Select the time zone
- Apply trace flags
- Apply Windows authentication
Additionally, there are some restrictions, such as fewer built-in functions, limits in triggers and DMVs views, functions, language elements, operators, set statements, system stored procedures, system functions, and system catalog views.
What is in Azure SQL?
Behind the scenes, there are several Azure SQL nodes, and the user connects using gateways. The clients connect to the gateway with a public IP address using port 1433. The gateway redirects or proxies the traffic to the right Azure SQL Database cluster. Basically, Azure SQL is a virtual database in the cloud that you can access.
Why do companies choose Azure SQL Database?
Generally, it depends on the company’s requirements and the application needs. Companies have multiple needs, requiring several technologies to accomplish various goals and applications. In my experience, big companies are handling servers and databases on-premises or in the cloud, like Azure (some Azure SQL, some managed instances, and some Virtual Machines with SQL Server), AWS, Oracle in cloud and on-premises, and Google Cloud as well.
After interviewing some customers, they generally prefer Azure SQL for applications with non-critical information. They do not trust having critical information in the cloud yet. They still prefer on-premises servers for that purpose. So, they usually use Azure SQL databases for applications on the web. However, this may change in the future. Many companies are hybrid right now, meaning some information is still on-premises, and some is migrating to the cloud.
Customers who prefer Azure SQL are users who love SQL Server on-premises and have found it easy to migrate and work from SQL Server on-premises into Azure SQL. Most companies that choose Azure SQL have worked with Microsoft technologies for a long time, testing this new technology and trying to migrate all the information to the cloud in the long run.
What concepts do I need to know to start?
Learning Azure SQL is a straightforward process. If you have experience with SQL, it will not be hard to learn. If you have experience in SQL Server, it will be extremely easy because you can connect using SSMS.
You may notice fewer features than SQL Server on-premises, but creating tables and inserting data will be almost the same as using your local SQL Server. It will not be hard if you do not have experience in SQL Server but have experience using MySQL, PostgreSQL, or similar tools, depending on your requirements. The process will be slightly longer than for an experienced SQL Server user.
What are the best practices?
- Use Azure Security Center to monitor your database.
- Only grant the required permissions to the users. Do not grant too many privileges if it is not necessary.
- Configure the Advanced Threat protection to detect anomalous activities.
- Track and monitor the activity with Azure Monitor Activity Logs.
- Apply multifactor authentication for security.
How do I get started?
You only need a machine with internet access and an Azure account to begin. Microsoft provides a free 12-month account with a credit of $200 USD.
To start, use the following link to sign up: Create Your Azure Free Account Today.
Once your account is established, you can follow this tutorial to create your own Azure SQL database: SQL Azure Create Database Tutorial.
Good luck!
Next Steps
For more information, refer to these links:
