SUPER SQL – Make Database a Service

By May 30, 2017Uncategorized

Microsoft Azure – Database as a Service

What is a SQL Database?

Microsoft Azure SQL Database is a managed cloud database (SaaS) provided as part of Microsoft Azure.

A cloud database is a database that runs on a cloud computing platform, and access to it is provided as a service. Managed database services take care of scalability, backup, and high availability of the database. Microsoft Azure SQL Database includes built-in intelligence that learns app patterns and adapts to maximize performance, reliability, and data protection.

Key capabilities include:

  • Continuous learning of your unique app patterns, adaptive performance tuning, and automatic improvements to reliability and data protection
  • Scaling as needed, with virtually no app downtime
  • Management and monitoring of multitenant apps with isolation benefits of one-customer-per-database
  • Leverage open source tools like cheetah, sql-cli, VS Code and Microsoft tools like Visual Studio and SQL Server Management Studio, Azure Management Portal, PowerShell, and REST APIs
  • Data protection with encryption, authentication, limiting user access to the appropriate subset of the data, continuous monitoring and auditing to help detect potential threats and provide a record of critical events in case of a breach

Microsoft Azure SQL Database Details

Top 5 Benefits of Microsoft Azure SQL Database

The question of whether or not to put your business data in the cloud is getting easier and easier these days and a lot of that is thanks to Microsoft’s investment in Microsoft Azure. Their strategy to create an infrastructure, like Microsoft Azure SQL Database, that can manage big data (as big as petabyte-scale) and everything on down from that is working and it’s working well.

The advancements made in SQL Database are making it more and more possible businesses of all sizes to save money and scale up or down efficiently. Below are the top five benefits that companies are realizing with SQL Database as their database service:

1) Scalability and Beyond

First of all, flexible service plans for SQL Database meet the needs of both big and small business users. SQL is no longer way out of reach for smaller operations because the pricing structure allows users to pay as little as $4.99 per database per month with a maximum storage set at 150 GB per database. That’s a lot of space for very little cost.

2) High Speed and Minimal Down Time

High availability architectures mean high speed connectivity and data retrieval as well as low down time at your organization. There’s nothing worse than stopping business because your technology can’t keep up, but that is no longer a problem with SQL Database.

Secondly, companies can add application instances as needed through sharding, for example. Sharding is a type of database partitioning that separates very large databases into smaller, faster, more easily managed parts called data shards. Not only can you spin nodes up and down on demand, you can leverage a federation infrastructure to scale more easily without affecting other areas of the server. SQL Azure Federation Data Migration Wizard can even further automate this process, which impacts your organization and the employees much less.

Lastly, there are multiple levels of implementation that you can benefit from. If you just need a website and a database, you can hitch a SQL Azure instance to an Azure website and you’re done! If you need a full-blown virtual machine(VM) now or even down the road, you can get that as well. You can even use a locally deployed instance of SQL Server in the VM instead of SQL Azure. These implementation options help make your company more adaptable to the inevitable changes it undergoes on a regular basis. With SQL Azure, you’re not stuck. You’re on a foundation that encourages growth while working with it!

3) Improved Usability

SQL developers are familiar with all things SQL and SQL Database can be updated with SQLCMD or the SQL Server Management Studio. Better yet, there’s no coding required! Using standard SQL, it’s much easier to manage database systems without having to write or update a huge amount of code.

4) Time Is On Your Side

With no administrative duties on your physical location, employees can take time for strategic work to advance and grow all-around business success. When your database is hosted in the cloud, you don’t have to deal with setting up SQL Server, apportioning databases, and dealing with physical machine maintenance and upkeep. All of this results in better alignment of your organization and ultimately more time on your side.

5) Easy-to-Use Migration Tools

Ramp up time with SQL Database is now easier than ever and free! SQL Data Sync allows you to either sync your SQL Server-stored data or migrate the data without having to worry about the costs to migrate by syncing gigabyte-sized tables.

Pricing

Azure SQL Database is offered either as a Standalone database or Elastic database pool, and is priced in three tiers: Basic, Standard and Premium. Each tier offers different performance levels to accommodate a variety of workloads.
The resources available for Standalone databases are expressed in terms of Database Transaction Units (DTUs) and for elastic pools in terms of elastic DTUs or eDTUs. A DTU is defined as a blended measure of CPU, memory, and data I/O and transaction log I/O in a ratio determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads.

Databases are available as Standalone databases or in database pools which allow multiple databases to share storage and compute resources.

A managed cloud database for app developers

Make building and maintaining applications easier and more productive. With built-in intelligence that learns app patterns and adapts to maximize performance, reliability, and data protection, SQL Database is a cloud database built for developers.

Learn and adapt dynamically with your app

As your app runs, SQL Database continuously learns your unique app patterns, adaptively tunes performance, and automatically improves reliability and data protection—freeing you to focus on your app.

Scale performance on the fly, without app downtime

When demand for your app grows from a handful of devices and customers to millions, SQL Database is a database as a service that can scale along with you, on the fly, with virtually no app downtime.

Build multitenant apps with customer isolation and efficiency

If you’re a SaaS app developer writing a multitenant app servicing many customers, you often make trade-offs in customer performance, management, and security. With SQL Database as your SaaS platform, you no longer have to make that compromise. SQL Database helps you manage and monitor multitenant apps and gain isolation benefits of one-customer-per-database.

Work within your preferred development environment

SQL Database allows you to focus on what you do best: building great apps. Develop your app and connect to SQL Database with the tools and platforms you prefer, and deliver with the speed and efficiency your business demands.

Help protect and secure app data

SQL Database helps you build security-enhanced apps in the cloud by providing built-in protection and security features. With physical and operational security, Azure SQL Database helps you meet the most stringent regulatory compliances.

Frequently asked questions

What is the current version of SQL Database?
The current version of SQL Database is V12. Version V11 has been retired. (subject to change with new releases)

What is the SLA for SQL Database?
We guarantee at least 99.99% of the time customers will have connectivity between their single or elastic Basic, Standard, or Premium Microsoft Azure SQL Database and our Internet gateway. For more information, see SLA.

How do I reset the password for the server admin?
In the Azure portal click SQL Servers, select the server from the list, and then click Reset Password.

How do I manage databases and logins?
See Managing databases and logins.

How do I make sure only authorized IP addresses are allowed to access a server?
See How to: Configure firewall settings on SQL Database.

How does the usage of SQL Database show up on my bill?
SQL Database bills on a predictable hourly rate based on both the service tier + performance level for single databases or eDTUs per elastic pool. Actual usage is computed and pro-rated hourly, so your bill might show fractions of an hour. For example, if a database exists for 12 hours in a month, your bill shows usage of 0.5 days. Additionally, service tiers + performance level and eDTUs per pool are broken out in the bill to make it easier to see the number of database days you used for each in a single month.

What if a single database is active for less than an hour or uses a higher service tier for less than an hour?
You are billed for each hour a database exists using the highest service tier + performance level that applied during that hour, regardless of usage or whether the database was active for less than an hour. For example, if you create a single database and delete it five minutes later your bill reflects a charge for one database hour.

Examples

  • If you create a Basic database and then immediately upgrade it to Standard S1, you are charged at the Standard S1 rate for the first hour.
  • If you upgrade a database from Basic to Premium at 10:00 p.m. and upgrade completes at 1:35 a.m. on the following day, you are charged at the Premium rate starting at 1:00 a.m.
  • If you downgrade a database from Premium to Basic at 11:00 a.m. and it completes at 2:15 p.m., then the database is charged at the Premium rate until 3:00 p.m., after which it is charged at the Basic rates.

How does elastic pool usage show up on my bill and what happens when I change eDTUs per pool?
Elastic pool charges show up on your bill as Elastic DTUs (eDTUs) in the increments shown under eDTUs per pool on the pricing page. There is no per-database charge for elastic pools. You are billed for each hour a pool exists at the highest eDTU, regardless of usage or whether the pool was active for less than an hour.

Examples

  • If you create a Standard elastic pool with 200 eDTUs at 11:18 a.m., adding five databases to the pool, you are charged for 200 eDTUs for the whole hour, beginning at 11 a.m. through the remainder of the day.
  • On Day 2, at 5:05 a.m., Database 1 begins consuming 50 eDTUs and holds steady through the day. Databases 2-5 fluctuate between 0 and 80 eDTUs. During the day, you add five other databases that consume varying eDTUs throughout the day. Day 2 is a full day billed at 200 eDTU.
  • On Day 3, at 5 a.m. you add another 15 databases. Database usage increases throughout the day to the point where you decide to increase eDTUs for the pool from 200 to 400 at 8:05 p.m. Charges at the 200 eDTU level were in effect until 8 pm and increases to 400 eDTUs for the remaining four hours.

Elastic pool billing and pricing information

Elastic pools are billed per the following characteristics:

  • An elastic pool is billed upon its creation, even when there are no databases in the pool.
  • An elastic pool is billed hourly. This is the same metering frequency as for performance levels of single databases.
  • If an elastic pool is resized to a new number of eDTUs, then the pool is not billed according to the new amount of eDTUS until the resizing operation completes. This follows the same pattern as changing the performance level of single databases.
  • The price of an elastic pool is based on the number of eDTUs of the pool. The price of an elastic pool is independent of the number and utilization of the elastic databases within it.
  • Price is computed by (number of pool eDTUs)x(unit price per eDTU).

The unit eDTU price for an elastic pool is higher than the unit DTU price for a single database in the same service tier. For details, see SQL Database pricing.

To understand the eDTUs and service tiers, see SQL Database options and performance.

How does the use of active geo-replication in an elastic pool show up on my bill?
Unlike single databases, using active geo-replication with elastic databases doesn’t have a direct billing impact. You are only charged for the eDTUs provisioned for each of the pools (primary pool and secondary pool)

 How does the use of the auditing feature impact my bill?
Auditing is built into the SQL Database service at no extra cost and is available to Basic, Standard, Premium, and Premium RS databases. However, to store the audit logs, the auditing feature uses an Azure Storage account, and rates for tables and queues in Azure Storage apply based on the size of your audit log.

How do I find the right service tier and performance level for single databases and elastic pools?
There are a few tools available to you.

How often can I change the service tier or performance level of a single database?
You can change the service tier (between Basic, Standard, Premium, and Premium RS) or the performance level within a service tier (for example, S1 to S2) as often as you want. For earlier version databases, you can change the service tier or performance level a total of four times in a 24-hour period.

How often can I adjust the eDTUs per pool?
As often as you want.

How long does it take to change the service tier or performance level of a single database or move a database in and out of an elastic pool?
Changing the service tier of a database and moving in and out of a pool requires the database to be copied on the platform as a background operation. Changing the service tier can take from a few minutes to several hours depending on the size of the databases. In both cases, the databases remain online and available during the move. For details on changing single databases, see Change the service tier of a database.

 When should I use a single database vs. elastic databases?
In general, elastic pools are designed for a typical software-as-a-service (SaaS) application pattern, where there is one database per customer or tenant. Purchasing individual databases and overprovisioning to meet the variable and peak demand for each database is often not cost efficient. With pools, you manage the collective performance of the pool, and the databases scale up and down automatically. Azure’s intelligent engine recommends a pool for databases when a usage pattern warrants it. For details, see Elastic pool guidance.

What does it mean to have up to 200% of your maximum provisioned database storage for backup storage?
Backup storage is the storage associated with your automated database backups that are used for Point-In-Time-Restore and geo-restore. Microsoft Azure SQL Database provides up to 200% of your maximum provisioned database storage of backup storage at no additional cost. For example, if you have a Standard DB instance with a provisioned DB size of 250 GB, you are provided with 500 GB of backup storage at no additional charge. If your database exceeds the provided backup storage, you can choose to reduce the retention period by contacting Azure Support or pay for the extra backup storage billed at standard Read-Access Geographically Redundant Storage (RA-GRS) rate. For more information on RA-GRS billing, see Storage Pricing Details.

I’m moving from Web/Business to the new service tiers, what do I need to know?
Azure SQL Web and Business databases are now retired. The Basic, Standard, Premium, Premium RS, and Elastic tiers replace the retiring Web and Business databases.

What is an expected replication lag when geo-replicating a database between two regions within the same Azure geography?
We are currently supporting an RPO of five seconds and the replication lag has been less than that when the geo-secondary is hosted in the Azure recommended paired region and at the same service tier.

What is an expected replication lag when geo-secondary is created in the same region as the primary database?
Based on empirical data, there is not too much difference between intra-region and inter-region replication lag when the Azure recommended paired region is used.

 If there is a network failure between two regions, how does the retry logic work when geo-replication is set up?
If there is a disconnect, we retry every 10 seconds to re-establish connections.

What can I do to guarantee that a critical change on the primary database is replicated?
The geo-secondary is an async replica and we do not try to keep it in full sync with the primary. But we provide a method to force synchronization to ensure the replication of critical changes (for example, password updates). Forced synchronization impacts performance because it blocks the calling thread until all committed transactions are replicated. For details, see sp_wait_for_database_copy_sync.

What tools are available to monitor the replication lag between the primary database and geo-secondary?
We expose the real-time replication lag between the primary database and geo-secondary through a DMV. For details, see sys.dm_geo_replication_link_status.

To move a database to a different server in the same subscription
In the Azure portal, click SQL databases, select a database from the list, and then click Copy. See Copy an Azure SQL database for more detail.

To move a database between subscriptions
In the Azure portal, click SQL servers and then select the server that hosts your database from the list. Click Move, and then pick the resources to move and the subscription to move to.

For more information on Microsoft Cloud Solution Provider Partner Program, please contact us on Microsoft.csp@firstdistribution.co.za

Leave a Reply