Introducing Azure Database for PostgreSQL and Azure Database for MySQL

Introducing Azure Database for PostgreSQL and Azure Database for MySQL


(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)

Danny Hutson

Leave a Reply

Your email address will not be published. Required fields are marked *