(machine whirring)
- Coming up, if you develop with open source databases,
today we're gonna show you the new community based
PostgreSQL and MySQL databases for Azure,
including dynamic scale to meet the performance needs
of your apps in seconds, high availability that eradicates
the need to set up replicas, Azure Service Integration
to add intelligence to your apps, as well as how to
get up and running while using your existing
developer tools and frameworks.
(electronic music)
So now we're joined by Sunil Kamath
from the Azure database engineering team, welcome.
- Thanks for having me on the show.
- So Azure's really offered database service options
for a while now, but now you can actually use
your preferred relational, community based,
open source database engines in Azure as an added service.
- That's right, and it makes a lot of sense.
The goal with Azure has always been
to support the need of the developers.
As you can see, it's an open platform
and we support any app, platform, or language.
We are seeing more and more open source
applications leveraging Azure now.
Bringing the open source community database engines
of PostgreSQL and MySQL to Azure allows us to now continue
to support your application development needs, but now
as a secure and a fully managed service in the cloud.
- And it's really the same platform that's been powering
Microsoft SQL Database as a service for many years.
- You got it.
These services are built on the same proven Microsoft
SQL Database services platform that has been powering
the Azure SQL Database service for many years.
We have expanded this platform and brought
the same innovations and operational experience
to support MySQL and PostgreSQL.
Now, it's important to highlight
that one of the core priorities in bringing
these services was familiarity.
You can continue to use your existing tools and frameworks
and use any programming languages you're familiar with
that supports these database engines.
All of this, without the need to worry about
managing infrastructure or patching
or designing for security or high availability.
It's all built into the service,
backed by an industry leading 99.99% of availability.
You can also flexibly scale compute or storage
and scale resources independently.
- Plus, you can get more geographic reach of your apps
across the entire global footprint of Azure.
- That's correct; and your apps can leverage
other services in Azure, for example,
to build more intelligence into your apps
or use the web app services, containers, and more.
You can also add intelligence to data.
- So how does this work in practice?
Is it easy to get started?
- Yes, let me show you how you publish
a database from scratch.
Let's search for MySQL and you find
Azure Database for MySQL.
Or by searching PostgreSQL I find
Azure Database for PostgreSQL.
Now, to create the database, you then simply
have to click on create the server.
Now here I am on the portal blade for creating
your first Azure PostgreSQL Database.
Here in this blade, you enter the information
such as your name, user credentials, select a region,
a pricing tier, and hit create.
So here in the connection security,
you need to configure the IP addresses for the clients
that need to talk to the database.
Typically this is your client IP address.
As you can also see, the SSL is already enabled by default.
There's an option available also for you to have
any Azure Services talk to this database
without explicitly configuring the IP address.
You do this by configuring on
for the allow access to Azure services.
- And if I'm using a workflow, for example,
a web app plus SQL right now, does this also apply
if I'm using PostgreSQL or MySQL?
- Yes, we have also done the integration
on MySQL and PostgreSQL with web apps,
so you can deploy these databases as part
of your deployment of the app.
Here, I have searched for the wep app in the Azure Portal,
and you see the Web App plus MySQL
and the Web App plus PostgreSQL.
So here you see the Web App plus MySQL deployment workflow,
where you can choose the Azure Database
for MySQL as the database provider.
- [Jeremy Chapman] Right, and if I wanted to automate
my infrastructure provisioning via things like
resource manager templates, has that also been built
into Azure, is that using that actually?
- Absolutely, this is a common way
to build your apps and make the build repeatable.
In fact, let me just pull up the template for you.
Web App plus PostgreSQL or MySQL uses these templates
and you can look at the JSON file if you want to customize
or use this as a basis to build your own template.
And let me also tell you that this same template can also
be used to automate third party frameworks like Terraform.
- Very cool, so can I use Cloud Shell or the Azure CLI then
to talk to PostgreSQL and MySQL?
- So, as you'd expect from SQL running on Azure,
you can manage PostgreSQL or MySQL
directly via Cloud Shell or Azure CLI.
From the Azure Portal, you click
on the Cloud Shell icon on the top.
MySQL and PostgreSQL client tools are already
being integrated with the Cloud Shell.
Here, I'm using a psql to connect
to my pre-existing PostgreSQL server.
And I can also interact with the database
using any PostgreSQL or SQL commands.
- Alright, so what about things like backups
or georedudancy in your database?
- The service has built-in backups with retention
of up to 35 days for point in time restore.
Backups can also be geographically stored
in another region to recover from disasters.
In this scenario, you create a server, you select
the source from backup, select the georedudant backup.
- And if I'm already using an open source database
on premises, what do I need think through and think about,
I guess, as I move from that on premises database
to the database as a service model?
- [Sunil Kamath] The main consideration is
the database engine versions we support.
Make sure we support the version you are using.
The next consideration is the database size,
so you know how much storage you need.
Then choose the appropriate service tier,
and the compute you need.
It's okay to get this wrong; you can later
flexibly scale compute or store it independently.
- So how do you go about migrating then an existing database
already running on premises into Azure?
- You can continue to use your existing tools
such as pg_dump or mysqldump, to restore
or to bring data from anywhere, because we are using
the same community version of MySQL and PostgreSQL.
In addition, we have also partnered
with Attunity Replicate Tool to help customers
migrate data to the service with minimal downtime.
And this is a free service.
Let me tell you how you can do this.
I am in the Attunity Designer and looking to migrate
my bookstore to a managed service,
connected to a local host, yapsql.
The first thing I need to do is
to set up the migration task.
That is, define the source and the target.
Let me review the connection properties.
I previously entered the connection details
for the source, local postgres,
which is my on premise server.
Let us test the connection.
Similarly, let us review the connection properties
for the target, an Azure Database for PostgreSQL.
You can see the server name and the connection details.
Let us test the connection.
We are looking at migrating the bookstore table.
Let's look at the source and the target
database yapsql shell now.
I want to review how many books titles
are in my local database.
So for that, I'm executing a query,
to know how many books I have.
So you see here I have about 101 book titles
that I want to now migrate to the destination.
And here I'm connected to the Azure PostgreSQL Service
and obviously you see that I do not
have any of these book titles migrated
to the Azure Database for PostgreSQL.
Now we wanna move that data with minimal downtime
over to the Azure PostgreSQL Server.
Now let's run our migration task.
For now, depending on the size of the data,
this could take longer, but most importantly is I do not
need to take an outage of my app
to move my data to the cloud.
Now, if I review the book titles on the target,
I can see that all the 101 book titles have now
migrated successfully to the Azure PostgreSQL Service.
In summary, I can migrate my existing MySQL or PostgreSQL
database to managed PostgreSQL on Azure with minimal downtime.
And all it would take now, is to change your application
connection string and point it
to the Azure MySQL or PostgreSQL Service.
- So now you're in Azure and this now opens a number
of new opportunities also as a developer.
- Yes, and the first thing that I wanna show
is the dynamic scaling.
Scale performance, typically you have to
think ahead of resources that you need,
especially planning for peak periods.
Imagine now that our bookstore is becoming very popular
and is enjoying strong business success,
and you want to add more book titles,
you likely want to increase more storage or add storage.
Or there are more users logging
into your bookstore during a busy sales season,
you may want to increase the compute.
Now you can independently scale storage
and compute in seconds, allowing you to respond quickly
to the changing business needs
and therefore optimize your costs.
- Right, and beyond scale, really one of the big benefits
here is that you can generate insights from your data
using things like machine learning or add intelligence
to your application via Cognitive Services.
- Yes, on azure.com you can see all the Cognitive Services.
I am interested in adding search capabilities to my app.
Specifically, the Bing Web Search API,
which gives your app the ability
to traverse billions of documents.
For a bookstore, what I wanna know is, what does demand
for the books in my inventory look like around the world?
The API will help me to approximate an answer
by getting the number of hits per country for each book.
But now looking at the bookstore app code,
where I'm going to add intelligence to this app.
In my code, I'm making a call to the Bing Web Search API
and storing the results in my PostgreSQL database.
Once that insights is available, I can easily visualize
via Power BI; let me show you how.
Here I have a Power BI Desktop.
You click on Get Data, select PostgreSQL database,
click connect, enter the server name,
and the database name, click okay.
Select the searchinfo table and click Load.
The searchinfo table has the insights
from the Bing Search API.
For this demo, I have created a view which represents
the hits on each book title across the world.
With this information, I can make my app localized
and pop up promotional sales in regions
where a book is very popular.
I could also let the supply chain know
that we should purchase more inventory
for certain books in a particular region.
This is intelligence that I can take action on.
- These are all really great insights added to your app.
So how can people watching learn more
and start trying this for themselves?
- You can sign up for the free Azure account today
and try the service for yourself.
You can also explore more at the links shown below.
- So great stuff, thanks for joining us today, Sunil.
And also thank you for watching us on Microsoft Mechanics.
Of course, you can stay up to date
with all the latest updates across Microsoft
by subscribing to our channel.
Thanks for watching, I'll see you next time.
(electronic music)