[MUSIC PLAYING] JOLENE TEO: Ever wonder how
growing startups are leveraging Google Cloud database services? Stay tuned to find out more. Hi, everyone. I'm Jolene Teo, and
I'm a customer engineer at Google Cloud. And welcome back to the
Google Cloud Technical Guides for Startups, where we
are creating a series of videos for technical enablement to
help startups to start, build, and grow their businesses
successfully and sustainably on Google Cloud. In our previous
episode, we covered networking configurations. And moving on to
today's episode, episode seven of
the series, we will be going through databases,
with a focus on Cloud SQL. For today, we're
going to first go through the big
picture on databases and when to choose which. We'll then deep
dive into Cloud SQL and also run through
a demo on Cloud SQL, as well as how it interacts
with some of the other services in Google Cloud. I'll then share with you some
tips and tricks around cost as well as performance
optimization. We'll then go through a sample
architecture, as well as a customer success story. And then we'll finally wrap
up with some links you can go through with after this video. All right, so let's get started. Let me give you an overview on
how Google Cloud can help you with your database needs. As you can see,
there is a full suite of database services
available to meet your different requirements. Over here, you can see
highlights of each type, from in-memory, to
relational, to non-relational, as well as what each
service is good for. Not only do we have
our Google-built, Cloud-native database
services where you can use MySQL or PostgreSQL
for ease of migration and compatibility, we also do
support traditional databases such as SQL Server. And finally, to
complete the portfolio, we also work very closely
with our partners, such as MongoDB,
Confluent, and many more, to provide you with
their integrated support. At this point, you
must be wondering, how do you go about choosing
which database service, then? Well, I've got your back. Here is a flowchart
to help you out with your startup's database
service decision making. Depending on your
application, you might need one or several
of these services available. Now, because many
startups leverage SQL databases for the
transactional processing in their applications,
which Cloud SQL supports, today we're going to zoom
into database configurations with Cloud SQL. Building, running, and
maintaining infrastructure means less time creating
value for your app. Run the same
relational databases that you know without the
hassle of self-management. This frees up time
for you to focus on building great
applications for your startup. Cloud SQL is a fully
managed database service where Google takes care of the
administrative tasks required to run a database,
making it easy for you and simple to operate. Cloud SQL also has full
compatibility with MySQL, PostgreSQL, and SQL Server. With Cloud SQL, you can
easily create read replicas and backups, and there is
also automatic failover, ensuring that your
database will always be available when you need it. Cloud SQL also offers
easy integration with just about any application, from
Google Kubernetes Engine to Compute Engine,
and many more. Open up analytics
possibilities with Cloud SQL's native integration
with BigQuery, where BigQuery can directly
query from your Cloud SQL databases, running
federated queries, enabling you to not
need to move your data. Cloud SQL resides in secure
Google data centers, ensuring business continuity, with
reliable and secure services backed by a 24/7 SRE
team with a 99.95% SLA. There is also automatic
data encryption at rest and in transit. You can also easily migrate
your databases to Cloud SQL with minimal downtime using
the Database Migration Service. Lastly, monitor and troubleshoot
with Cloud SQL Insights, which is a simple and open tool
that can help developers to understand database
performance issues on Cloud SQL. Maintenance is also very
easy with automated patching. To dive a little deeper
with Cloud SQL databases, Cloud SQL supports read replicas
and cross-region read replicas. You can leverage replication
to horizontally scale your application needs. To highlight, you can leverage
cross-region read replicas for disaster recovery. Cloud SQL also has a built-in
high availability option, which helps you
to reduce downtime when a zone or instance
becomes unavailable. With high
availability, your data will still continue to
be available to client applications. Cloud SQL also provides you with
both automated and on-demand backups with
point-in-time recovery. Backups help to protect your
data from loss or damage. You can create a backup any
time you want on demand, and this could come
in really handy when you're may be about to
perform a risky operation on your database. You can also enable
automatic backups. Where possible,
schedule your backups where your instance
has the least activity. Right, it's about
time to give you a tour on how it looks like. Now on to the demo. We are going to continue on with
the guestbook application demo. In this demo, I'm
going to show you how you can create your
Cloud SQL instances and then populate
them with data being imported from Cloud Storage. We'll then explore some SQL
statements within Cloud Shell, and then we'll connect
to BigQuery as well as Data Studio. And last but not
least, we'll wrap up with a connection to
Google Compute Engine. All right, let's get started. For the first part
of the demo, we'll be going through how to
create Cloud SQL instances. Now moving into the
Console, first, we're going to head over to
Cloud SQL and click on the Create Instance button
to create a new instance. You can easily configure
your new instance according to exactly how
you want it to be like. First, you're going
to be prompted to select your database engine. Over here you can see the
different options available, and for this demo, I'm going
to just select PostgreSQL. Next, give your
instance an instance ID, and just remember that it has
to be unique within the project. And then following
that, put in a password. And following that, you
can also choose a region to be either single
zone or multi zone. By default, it's multi zone. And you can select the zone
for the primary and failover instance. If you do not have
specific requirements, feel free to leave it as Any. And on the right-hand
column, you can see a summary of
the different options and configurations
you have selected. At this point, you can already
create your Cloud SQL instance. If not, you can continue on
to configure the advanced configurations. You can select
your machine type, be it whether is it standard,
lightweight, shared cores, or high memory. You can also select
the Custom option to uniquely make selections for
your memory as well as cost. As far the storage type,
the typical selection is SSD for lower latency
and higher throughput. However, you can also
make the selection and switch over to HDD. You can choose your
storage capacity-- I'll select Custom-- if you
intend to go beyond 200 GB. At the same time, you can check
to Enable Automatic Storage Increases, where if
you run out of space, Google will allocate
additional storage to you. As for connections,
you have options for public IP or private IP. In this case, I'm just going
to go ahead to select both. For automated backups, you
can select a window of time for this to occur. It would be best to
select the non-peak hours of your business, where there
is lower traffic to your SQL instances. Similarly, for patching,
upgrades, and maintenance, you can also select
the non-peak hours. And lastly, you can also
set up database flags as well as make use
of labels, which are really good for billing
as well as tracking where your costs are coming from. All right, and now
we're good to go. I'm just going to hit
Create, and we're just going to speed up this
part until the instance is being created. All right, our Cloud SQL
instance is up and running. Moving on to the next
section in this demo, I'm going to show you how you
can import data into your Cloud SQL instance, and
we're also going to connect to it
to read the data. In this case, we will be using
guest data for this demo. So first, we're going to
create a database by going over to the Database tab to
create a new database called guestbook-db. Over here, I have my
Cloud SQL instance. And first, I'm going
to select Import, which is the button right at the top. For source, I'm going to select
the .sql file that have already imported into Cloud Storage, and
this file is what's going to be creating the tables. For the format, I'm going to
select SQL and also include the destination. And following that, I'm
going to repeat this process to import the data to
populate those tables. In this part of the
demo, we're going to be exploring the
imported data using SQL statements in Cloud Shell. Now we're going to connect
to the SQL instance via Cloud Shell simply by
selecting the button Connect Using Cloud Shell. Enter the command to
connect and authorize. And we're going to see this
pop up a little bit as well. And then following
that, also enter in your password to connect. From here, we can now
run a couple of queries. So first we can
list the databases, and then we can select one. And finally, we can then
list, perhaps, the guests. All right, and we are done. Now I'm going to show you how
to connect to BigQuery and Data Studio. Right, so first, move
to the BigQuery page, add external data
source, and then select your connection type. In this case, I'm just going to
go ahead and select Postgres, and then give it a name. Select your location. Over here, I'm just going to
go ahead with us-central1. And then following
that, copy and paste your Cloud SQL instance ID. Enter in the password as well. And then click to
create the connection. You should also see the
connection being created in the left navigation menu. All right. And from here, you can
just start querying. In this example
query itself, you can see the external
query function as well as the connection ID. And this makes it very easy
for you to query directly from Cloud SQL within
BigQuery for running federated queries easily. On top of data
analysis in BigQuery, you can also easily
create a connection to Data Studio for
data visualization. There are many
connectors available, as you can see over here. And for this demo, we're
going to select the Postgres connector. And then we're going
to create a new report. From here, you can
easily add charts. And I'm just going
to speed up this part and show you the end
result of how visualization can look like for you. All right, and we're done. Here's a simple
dashboard to show you how you can use data
visualization in connection to Cloud SQL with Data Studio. And finally, in
this demo section, I'm going to show you how you
can connect Cloud SQL back with Google Compute Engine. All right, so now we're
going to head over to Google Compute Engine. And then we're going to SSH in
to set up the Cloud SQL proxy. So first, we're going
to make sure that there are permissions by uploading
the service account key that has both the Cloud SQL client as
well as edit [? rules. ?] Next, we'll then download the
Cloud SQL auth proxy, and also make the Cloud
SQL auth proxy executable. We will then install
the PostgreSQL client onto the Compute
Engine instance, and then start the
PostgreSQL auth proxy. And finally, you can
connect to the PSQL client. And following that,
you can already begin to start querying. We're going to run
a couple of queries similar to earlier before
to show you the connection. All right, it looks awesome. And we're done. Now that you know how to
configure your Cloud SQL database, I'll be
sharing with you some of the best practices
for cost, as well as for performance optimization. We will be going through
some of the top tips, and I'll also put some
links in the description box below for your further reading. For performance
optimization, first of all, do consider a microservices
architecture, sometimes known as vertical sharing. This refers to having
multiple smaller databases for each microservice
you may have, as compared to having one
monolith database for all of your services, which can help
you to increase and optimize your performance. Remember that this is a
general suggestion, and perhaps in specific architectures,
a monolithic architecture may work for you. Secondly, for your
read-heavy workloads, do remember to add read
replicas to offload traffic from your primary instance. Optionally, you can also
make use of a load balancer like HAProxy to manage
traffic to your replicas. Lastly, use good connection
management practices, such as exponential backup
and connection pooling. Using these techniques
will help you to improve your application's
use of resources and also to stay within the
Cloud SQL connection limits. As for costs, the Cloud SQL
idle instance recommender helps to detect instances
that might be idle. It then provides you with
recommendations and insights on how you can reduce costs. Next, the Cloud SQL over
provisioned instance recommender helps
to detect instances that are unnecessarily
large for a given workload. It then provides you with
some recommendations on how you can resize these instances. Lastly, do leverage commit
use discounts, which are ideal for
predictable workloads on a one-year or three-year
basis, which can give you significant cost savings. Now let's explore some of the
use cases involving Cloud SQL. As you can see over
here, Cloud SQL works across many use cases,
from gaming to inventory management, and many more. Next, let's zoom
into one example, a sample fictitious gaming
backend database architecture. Here, we can see all of the
different databases in action. First, we have Cloud Firestore,
a NoSQL document database, which can be used for
authentication as well as the player database. Cloud Memorystore is
then used for storing regional, in-memory caches,
which speeds up the access to frequently viewed data. Cloud SQL or Cloud
Spanner then helps do the hosting of the game data. You can also log events to
Cloud Bigtable, Google Cloud's NoSQL columnar database. And finally, you can
also connect to BigQuery for data warehousing
and gameplay analytics. And if we wanted to take a
look at a real life example, we have Krikey. Krikey is a mobile
AR gaming startup that leverages on Google
Cloud to bring diversity into the mobile AR gaming space. Krikey migrated to
Cloud SQL and also uses some of the other Google
Cloud services, which has helped them to reduce their
rendering time for customers and also increase the
speed of Krikey games. Using Cloud SQL
has helped Krikey to decrease the
load time of videos, improve the speed
of games, resulting in higher app engagement times. All in all, I hope that
this video has given you a good understanding of
Google Cloud database services and how you can get
started on Cloud SQL. And after this video, if you
are still keen to find out more and cannot get enough of
databases and Cloud SQL, do check out the links in
the description box below. In our next episode, you will
be learning all about Firebase. We'll first kick off with
an overview of what Firebase is, how you can build
better applications, improve application quality, as
well as drive user engagement. We'll then go through a demo
as well as a customer success story. All right, and that's a wrap. See you in the next video. [MUSIC PLAYING]
