-2

What is the maintenance required from an organization when deploying an Azure SQL Database in the long term?

My current organization is hoping to do as little database management as possible, and have looked for products that fully manage our databases without much intervention needed from our end. Some products that are being considered includes Snowflake (for their automated partitioning of tables) and Domo (for their data warehousing, connectors, and BI tool offerings).

I'm leaning towards using Azure SQL DB for multiple reasons (products offered, transparent pricing, integration ease, available documentation, SSO, etc.), but want to first understand the skills needed and ease in maintaining it in the long run.

Will we have to manually rebuild indexes and partition out tables as we scale up? Or is Azure intelligent enough that it'll do most of the heavy lifting of performance optimization itself?

Does Azure or other vendors provide services to optimize a DB?

Sorry for the vague prompts, but any additional considerations in choosing DB vendors would be great. Thanks!

Yaga
  • 115
  • 3
  • 17

2 Answers2

1

Actually for your questions, you should know what is Azure SQL database and it's capabilities.

  1. I'm leaning towards using Azure SQL DB for multiple reasons (products offered, transparent pricing, integration ease, available documentation, SSO, etc.), but want to first understand the skills needed and ease in maintaining it in the long run.

This document What is Azure SQL Database service introduced almost all message you want to know. SQL Database is a general-purpose relational database managed service in Microsoft Azure that supports structures such as relational data, JSON, spatial, and XML. SQL Database delivers dynamically scalable performance within two different purchasing models: a vCore-based purchasing model and a DTU-based purchasing model. SQL Database also provides options such as columnstore indexes for extreme analytic analysis and reporting, and in-memory OLTP for extreme transactional processing. Microsoft handles all patching and updating of the SQL code base seamlessly and abstracts away all management of the underlying infrastructure.

  1. Will we have to manually rebuild indexes and partition out tables as we scale up? Or is Azure intelligent enough that it'll do most of the heavy lifting of performance optimization itself?

No, you don't. Scalability is one of the most important characteristics of PaaS that enables you to dynamically add more resources to your service when needed. Azure SQL Database enables you to easily change resources (CPU power, memory, IO throughput, and storage) allocated to your databases.

You can mitigate performance issues due to increased usage of your application that cannot be fixed using indexing or query rewrite methods. Adding more resources enables you to quickly react when your database hits the current resource limits and needs more power to handle the incoming workload. Azure SQL Database also enables you to scale-down the resources when they are not needed to lower the cost.

For more details, please reference: Scale Up/Down.

  1. Does Azure or other vendors provide services to optimize a DB?

As Woblli said, Azure SQL database provides the Azure SQL database Monitoring and tuning for you.

As a complement, you also can use Azure SQL Database Automatic tuning to help you optimize the database automatically.

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Appreciate the confirmation and links! It's tough when I have little to no experience in DB management and have to work with people who have decades of MSSS experience. It's always hard to be sure about technical nuances. Hopefully that will change in a few years :) – Yaga Jul 03 '19 at 14:44
0

Azure SQL DB offers the services you're asking.

You can enable automatic tuning, which will create and drop indexes based on performance gains. Force good query plans again based on performance. It will roll back changes if the specific change has decreased the overall database performance level.

It will not partition or shard your database for you however.

Official documentation: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning

Wobli
  • 192
  • 14