Today I’d like to talk about the Azure Relational Data Services Platform. This is an important foundational component for many things that are being built on Azure Platform as a Service related to databases.
One of the key PaaS offerings when Microsoft started with Azure was Azure SQL Database. Moving forward, changes were made to this and Azure SQL DW was released. Recently, Microsoft released a preview of the Azure SQL Database Managed Instance option. This is significant as it is a v-core plus storage option and intended to have parity with the on premises version of SQL Server, plus is a key step to separating compute and storage for Azure SQL Databases as well.
See you at Azure Data Week in a few days!
This is important since it allows Microsoft to standardize their relational database support pattern for other databases as well. This has existed for Azure DW for some time and was also improved in Gen 2. Check out more about this in some previous posts in this series.
Azure’s Relational Database platform supports Azure DW’s MPP platform, Azure SQL Database or SQL Server as PaaS, Azure Database for MySQL and PostgreSQL. So, open source databases are supported on the same relational data services platform. Azure Database for MariaDB is coming by the end of 2018.
You may be thinking, why is all this important and what does a common platform include?
- First, Azure storage services as a foundation for all databases and all the data on the Azure platform. All data stored here, as well as Azure Databases, whether open source or SQL, are encrypted at rest.
- Manages high availability of a solution by keeping free copies of data available for the platform at all times. So high availability built in and encryption at rest—secure and available.
- Azure compute is the VMs supporting the compute needs of the databases. This is where you pick the cores that you want to provide scale up function. However, you’re not managing VMs, you’re managing capacity. Microsoft has taken on the task of understanding what you need from a capacity standpoint, like how do you want to scale up or down or how many v-cores do you want to set aside.
- A key component of many things in Azure is that we can scale compute separate from storage. The database services platform sits on top of Azure storage and compute, so its strength is that the core of the solution lives in those 2 platforms. It allows support of MPP, open source and SQL databases with PaaS.
- Databases services is where the next tier happens (or all the cool stuff). On top of the foundation, Microsoft adds a set of common components that are used across all these databases.
- It’s a trusted platform with things like backup and restore, security, audit and isolation all managed in this service. This allows you to trust the platform and build databases with confidence in the security.
- It’s flexible, enabling scalability and resource management within the platform. This includes features like scaling up or down on demand and adding storage as you need, giving flexibility to the platform. This is hard to do if you build this for yourself or use an IaaS solution.
- It’s intelligent. We see big benefits in the fact that it provides monitoring, automated tuning and advisors to the platform. These are built in to make your databases better, so you can rely on good performance and know what is happening in your database when you need to.
- Think of the third tier (after storage/compute and database services) as each unique database platform and the features each brings to your application. Whether you’re using an open source product that’s using MySQL or a SQL Server, their feature sets come forward in PaaS.
Another advantage to mention is by supporting standard SQL and managed instances, and MySQL and PostgreSQL Community Edition, it makes moving to the cloud so much easier. This open opportunities for you to migrate in clean fashion using all the capabilities of a system you’re familiar with.
Microsoft conducted a live event called Microsoft Data Amp to announce a number of key features and releases for SQL Server on premises and data platforms in Azure (such as Azure SQL DB and Azure Data Lake). Some of these include features that I have been waiting to see. Here are some of announcements that I am excited about.
Microsoft Data Platforms
Intelligent – Trusted – Flexible
On-premises & Cloud
SQL Server 2017
Yes. Microsoft has officially announced that SQL Server vNext is SQL Server 2017. The marquee feature being released in SQL Server running on Linux. But this also shows Microsoft is increasing its innovation efforts with SQL Server with an even shorter time between releases.
CTP 2 of SQL Server 2017 has been released today and includes an number of analytics features such as support for graph processing and graph queries. It will be the first commercial database with built in support for AI and deep learning database applications using R and Python scripts. Check out all the database engine improvements.
Azure SQL Database
Microsoft is bringing even more symmetry between the on-premises product and the PaaS product. The goal is to support upgrades or migrations to Azure SQL DB with minimal effort and no changes. Here are some of the features that are coming to Azure SQL DB soon:
- Support for SQL Agent
- 3-part names
- Service Broker
- Cross-Database and Cross-Instance querying
- CLR & R Services
- SQL Profiler
- Native backup-restore
- Log shipping
- Transactional Replication
These features will definitely bring more parity to the platforms. A number of these features are key for some of my clients to move to Azure SQL DB.
Migration Project for Azure SQL DB
Whether you have SQL Server, Oracle, or MySQL, you should be able to migrate your database to Azure SQL DB in “five simple steps”. While a great tool, I am interested in exploring this more with Oracle in particular. You can create a project in Azure that let’s you choose the source database and platform and target a Azure SQL DB then move the schema and load the database. While I am skeptical on the full capability of this solution, I look forward to exploring it more.
Azure Analysis Services is GA
The last topic I am going to bring up is Azure Analysis Services. This service is now GA which brings a great service to the PaaS space in Azure. Check out the capabilities here.
Microsoft announced much more than I highlight here including tighter AI integration into the data engine, R Server 9.1, and planet scale Document DB. Check out the Microsoft Data Amp site for more videos on what’s coming to Microsoft’s data platforms.
Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.
The presentation can be found here: A Window into Your Data
The code was put into a Word document that you can get here: TSQL Window Function Code
This session is also backed by an existing blog series I have written.
T-SQL Window Functions – Part 1- The OVER() Clause
T-SQL Window Functions – Part 2- Ranking Functions
T-SQL Window Functions – Part 3: Aggregate Functions
T-SQL Window Functions – Part 4- Analytic Functions
My Session: Using Azure SQL Database for Enterprise Needs
On 10/6/2015, I presented on Azure SQL Database at TechFuse Minnesota. Some of the highlights from the session included a comparison of the various SQL Server offerings and how Azure SQL Database fits into the overall picture. During the session we also discussed the importance of V12 and elastic databases (still in preview) for the enterprise environment. I was also raised and discussed the cloudy concerns including data safety, data compliance, data privacy and business continuity. With the ongoing changes Microsoft makes in this space, expect more changes to come. However, even today you can be successful using Azure SQL Database for your enterprise needs.
The presentation can be found in PDF format here.
Microsoft has a wealth of documentation which can be found here: https://azure.microsoft.com/en-us/documentation/services/sql-database/.
Questions from the Session
How are upgrades handled in Azure SQL Database?
Most upgrades to Azure SQL Database are seamless to us. However, they have made changes over the years which have required some intervention. One such change was the move from Web and Business Service Tiers to Basic, Standard, and Premium. In this case Microsoft provided a lot of guidance around the process and it was very easy to do in most cases. The most recent instance was the move to V12. Once again Microsoft provided the upgrade path in documentation. Both upgrades could be handled in the portal. However, V12 was a significant change so groups needed to do more testing prior to an upgrade.
Microsoft’s Azure upgrades for Azure SQL Database over the past couple of years: https://azure.microsoft.com/en-us/updates/?service=sql-database.
Can we use Point in Time Restore to recover a table or to recover to a new database?
Point in Time Restore allows us to recover databases to specific points in time. Each service tier has different amounts they retain: Basic-7 days, Standard-14 days; Premium-35 days. Azure SQL Database always recovers the database to a new database. This means you can either “swap” the database once it is recovered or move the items you need to recover from the new database. One word of caution, you need to have the DTU capability to run both databases on the server to support the restore capability. Be sure to plan the recovery process and clean up when you have completed your recovery.
Thanks again to everyone who could attend this year.
Until the next session …