Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein

Thursday 29 June 2017

AWS Summit 2017 London

Yesterday was my first AWS Summit at ICC London, ExCeL. The event was much larger than I thought. On arrival I had to queue for over 25 minutes to get into ICC London. Then had to queue again to collect the registration badge, then had to go to another room to be security checked. 45 minutes later managed to get a seat in the keynote which had already started.

There was a great demo showing robots working in a distribution centre at @OcadoTechnology using AI. The keynote raised the question of old world relational databases. Do we still need old world relational databases? They say not and MySQL, PostgreSQL and MariaDB are why customer are moving to Open database engines.

The Forces and market trends that are causing adoption of new services

Amazon has various database offerings.
  • Amazon Aurora (a MySQL-compatible relation database engine that combines the speed and availability of high-end commercial databases)
  • Amazon Relational Database Service (covers MySQL, Oracle, SQL Server, or PostgreSQL database in the cloud)
  • Amazon DynamoDB (a highly scalable, fully managed NoSQL database service)
  • Amazon Redshift (a fully managed, easily scalable petabyte-scale data warehouse service that works with your existing business intelligence tools)
  • Amazon ElastiCache (a web service that makes it easy to deploy, operate, and scale an in-memory cache in the cloud.)
  • AWS Database Migration Service

Migrating Data to and from AWS

When migrating databases to AWS you can backup your SQLServer database and restore the backup file to AWS RDS or EC2. The migration steps

There were various things to learn from the migration.

The Amazon Relational Database Service (Amazon RDS) covers

Amazon Aurora keeps 6 copies of the data, two copies in each availability zone to protect against availability zone failure.

There are various Analytics services such as
  • Amazon Athena (is an interactive query service to analyze data in Amazon S3 using standard SQL. Athena is serverless)
  • AWS Data Pipeline (is a web service that helps you reliably process and move data between different AWS compute and storage services, as well as on-premise data sources, at specified intervals)
  • AWS Glue (is a fully managed ETL service that makes it easy to move data between your data stores)
  • Amazon Kinesis (to collect, process, and analyse real-time streaming data) 

Artificial Intelligence service offerings are: 
  • Amazon Lex
  • Amazon Polly
  • Amazon Rekognition
  • Amazon Machine Learning
  • AWS Deep Learning AMIs
  • Apache MXNet on AWS

AWS was called the centre of gravity for AI.

Machine Learning capabilities explained

Friday 2 June 2017

Azure Application Architecture Guide

I came across this useful guide. It presents a structured approach for designing applications on Azure that can scale, are resilient, and highly available. It is based on proven practices that Microsoft have learned from customer engagements.

Thursday 1 June 2017

Continual Growth of DTU

Database Transaction Unit (DTU) 

Database Transaction Unit (DTU) is a blended measure of CPU, memory, I/O (data and transaction log I/O). For a single Azure SQL database at a specific performance level within a service tier, Microsoft guarantees a certain level of resources.  This was originally calculated by OLTP benchmarks.

Azure SQL Database Advisor enables deeper insight into the DTU resource consumption.

A useful tool to help calculate the DTU size required for the service in Azure SQL Database is the DTU Calculator.

Measure Resource Utilization

To measure resource utilization for your existing database server, you'll need to capture several windows performance metrics on your SQL Server. It is best to run the performance counters for a while to obtain a representative workload pattern. This should be no less than an hour to provide the best recommendation. The counters to use are:

  • Processor - % Processor Time
  • Logical Disk - Disk Reads/sec
  • Logical Disk - Disk Writes/sec
  • Database - Log Bytes Flushed/sec

Elastic Database Transaction Units (eDTUs)

There is another type of DTU and that is elastic DTU. Rather than providing a dedicated set of resources (DTUs) to a SQL Database you can place the databases into an elastic pool. This is where a SQL Database server shares a pool of resources among databases. Elastic pools provide a simple cost effective solution to manage the performance for multiple databases that have widely varying and unpredictable usage patterns.

A pool is given a set number of eDTUs for a set price.

Change in Elastic Pool Sizes

There has been a change now with the availability of higher database eDTU limits for standard elastic pools in Azure SQL Database. The standard elastic pool was limited to 100 eDTUs but, it is now increased up to 3000 eDTUs.