For several decades, before the rise of cloud computing upended the way we think about databases and applications, Oracle and Microsoft SQL Server databases were a mainstay of business application architectures. But today, as you map out your cloud journey, you’re probably reevaluating your technology choices in light of the cloud’s vast possibilities and current industry trends.

In the database realm, these trends include a shift to open source technologies (especially to MySQL, PostgreSQL, and their derivatives), adoption of non-relational databases, multi-cloud and hybrid-cloud strategies, and the need to support global, always-on applications. Each application may require a different cloud journey, whether it’s a quick lift-and-shift migration, a larger application modernization effort, or a complete transformation with a cloud-first database.

Google Cloud offers a suite of managed database services that support open source, third-party, and cloud-first database engines. At Next 2022, we published five new videos specifically for Oracle and SQL Server customers looking to either lift-and-shift to the cloud or fully free themselves from licensing and other restrictions. We hope you’ll find the videos useful in thinking through your options, whether you’re leaning towards a homogeneous migration (using the same database you have today) or a heterogeneous migration (switching to a different database engine).

Let’s dive into our five new videos.

#1 Running Oracle-based applications on Google Cloud

By Jagdeep Singh & Andy Colvin

Moving to the cloud may be difficult if your business depends on applications running on an Oracle database. Some applications may have dependencies on Oracle for reasons such as compatibility, licensing, and management. Learn about several solutions from Google Cloud, including Bare Metal Solution for Oracle, a hardware solution certified and optimized for Oracle workloads, and solutions from cloud partners such as VMware and Equinix. See how you can run legacy workloads on Oracle while adopting modern cloud technologies for newer workloads.

#2 Running SQL Server-based applications on Google Cloud

By Isabella Lubin

Microsoft SQL Server remains a popular commercial database engine. Learn how to run SQL Server reliably and securely with Cloud SQL, a fully-managed database service for running MySQL, PostgreSQL and SQL Server workloads. In fact, Cloud SQL is trusted by some of the world’s largest enterprises with more than 90% of the top 100 Google Cloud customers using Cloud SQL. We’ll explore how to select the right database instance, how to migrate your database, how to work with standard SQL Server tools, and how to monitor your database and keep it up to date.

#3 Choosing a PostgreSQL database on Google Cloud

By Mohsin Imam

PostgreSQL is an industry-leading relational database widely admired for its permissive open source licensing, rich functionality, proven track record in the enterprise, and strong community of developers and tools. Google Cloud offers three fully-managed databases for PostgreSQL users: Cloud SQL, an easy-to-use fully-managed database service for open source PostgreSQL; AlloyDB, a PostgreSQL-compatible database service for applications that require an additional level of scalability, availability, and performance; and Cloud Spanner, a cloud-first database with unlimited global scale, 99.999% availability and a PostgreSQL interface. Learn which one is right for your application, how to migrate your database to the cloud, and how to get started.

#4 How to migrate and modernize your applications with Google Cloud databases

By Sandeep Brahmarouthu

Migrating your applications and databases to the cloud isn’t always easy. While simple workloads may just require a simple database lift-and-shift, custom enterprise applications may benefit from more complete modernization and transformation efforts. Learn about the managed database services available from Google Cloud, our approach to phased modernization, the database migration framework and programs that we offer, and how we can help you get started with a risk-free assessment.

#5 Getting started with Database Migration Service

By Shachar Guz & Inna Weiner

Migrating your databases to the cloud becomes very attractive as the cost of maintaining legacy databases increases. Google Cloud can help with your journey whether it’s a simple lift-and-shift, a database modernization to a modern, open source-based alternative, or a complete application transformation. Learn how Database Migration Service simplifies your migration with a serverless, secure platform that utilizes native replication for higher fidelity and greater reliability. See how database migration can be less complex, time-consuming and risky, and how to start your migration often in less than an hour.

We can’t wait to partner with you

Whichever path you take in your cloud journey, you’ll find that Google Cloud databases are scalable, reliable, secure and open. We’re looking forward to creating a new home for your Oracle- and SQL Server-based applications.

Start your journey with a Cloud SQL or Spanner free trial, and accelerate your move to Google Cloud with the Database Migration Program.

Intro

One of the time consuming tasks for DBAs is to maximize query performance. To optimize slow-running queries, they often have to do detailed analysis, understand optimizer plans, and go through a lot of trial and error before getting to the best set of indexes that help with improved query performance. However, even with expert knowledge of a database’s internals, it is challenging to choose an effective set of indexes, especially when workloads change over time. The complexity of queries including several joins, filters, and subqueries make the process of identifying the right set of indexes very challenging for DBAs. Also, the effect of an index on the query plan needs to be taken into account when considering further indexes. Creating an index may cause the query optimizer to select completely different join orders and join/scan methods. This effect is hard to predict and quantify for DBAs. 

Imagine if the database itself is intelligent enough to identify such queries, and recommends creating specific B-tree indexes? 

Google Cloud’s AlloyDB for PostgreSQL is a fully-managed and fully PostgreSQL-compatible database for demanding transactional and analytical workloads that provides enterprise-grade performance and availability.  AlloyDB offers Index Advisor, a built-in feature that helps alleviate the guesswork of tuning query performance with deep analysis of the different parts of a query including subqueries, joins, and filters. It periodically analyzes the database workload, identifies queries that can benefit from indexes, and recommends new indexes that can increase query performance. 

How the AlloyDB Index Advisor works

First, let’s review a few AlloyDB terms relevant to the Index Advisor’s work.

• PostgreSQL’s system catalog has schema metadata, such as information about tables and columns, and internal bookkeeping information.
• HypoPG is an open source PostgreSQL extension that helps with hypothetical indexes without actually creating them to see if the index helps with query execution.
• Query Optimizer generates optimal execution plan. 

1. AlloyDB’s Index Advisor tracks the user query workload and analyzes it using statistics from the system catalog; it then identifies queries that could be improved significantly and potential candidate indexes for those cases. Note that there could be a large number of candidate indexes.

2. The Index Advisor evaluates each of these queries using hypothetical indexes based on the HypoPG, an open source extension and AlloyDB’s Query Optimizer. 

3. The results of this evaluation are used to intelligently select the best set of indexes for the workload and to generate recommendations. 

You can then simply copy and run the suggested index creation SQL commands. The Index Advisor consumes minimal resources and analyzes queries at a frequency that you define. It can also be constrained to have a user-specified maximum storage budget for the new indexes it recommends.

How to use the Index Advisor

1. Index Advisor is enabled by default. Its recommendation engine analyzes your workload at the specified frequency (every 24 hours by default) to capture any potential new index recommendations. 

2. Run your queries that are representative of your workload to the instance. Index advisor tracks these queries automatically. 

3. To request an index recommendation immediately, you can use the google_db_advisor_recommend_indexes() function. This function performs on-demand analysis and recommends indexes for the top 100 queries. SELECT * FROM google_db_advisor_recommend_indexes();

4. To view the recommended indexes and the queries based on periodic analysis, use the following query (also see the Usage Example section). 
SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r 
JOIN google_db_advisor_workload_statements s
ON r.query_id = s.query_id;

Note that Index advisor analyzes queries issued by the connected user. For the user with the pg_read_all_stats role, it analyzes all tracked queries. 

Currently, Index advisor only recommends new indexes. In future, the Index Advisor could be used to report unused indexes as well; dropping these can reduce index maintenance overhead for your transactional workload.

Evaluation

We will discuss two cases in this section.

1. Decision support benchmark: We used a sample internal benchmark that modeled a decision support system. The benchmark dataset contained 450+ columns across 24 tables. The result quantified the performance of 100+ complex analytical queries. The benchmark initially had a minimal number of indexes and the normalized total query execution time was around 200+ minutes. We then enabled the AlloyDB Index Advisor and it recommended an additional 17 indexes. Creating those indexes and re-running the queries reduced the total execution time by half to ~100 minutes (1.8x).

2. Real-world AlloyDB customer workload: This is a real-world workload from a large financial customer. They have a portfolio of complex analytical queries and business insights demand fast response times. Before using Index Advisor, DBAs had already created a few indexes that they considered to be useful to speed up these queries. Many of their queries joined 30+ tables, had 10+ filters and  multiple subqueries. Those made it complex for DBAs  to correctly identify the most beneficial indexes to create. AlloyDB Index Advisor analyzed these complex structures and identified four additional indexes. By adding the suggested indexes, performance of 17 queries were up by 5x –  73x. The response time of these queries dropped from seconds to milliseconds.

Usage Example

An example: We use the following example to demonstrate the use of Index Advisor. The example simulates a retail application performing analytics on its orders table. 

1. Create a sample orders table.

CREATE TABLE orders (
    o_orderkey bigint NOT NULL,
    o_custkey int NOT NULL,
    o_orderstatus "char" NOT NULL,
    o_totalprice numeric(13,2) NOT NULL,
    o_orderdate date NOT NULL,
    o_orderpriority character varying(15) NOT NULL,
    o_clerk character varying(15) NOT NULL,
    o_shippriority int NOT NULL,
    o_comment character varying(79) NOT NULL
);

2. Insert 100K random orders into the table.

INSERT INTO orders 
SELECT col, col, substr(md5(random()::text), 1, 1), random(), date '2023-01-01' + col * interval '1 hour', substr(md5(random()::text), 1, 1), concat('clerk', col), col%10, concat('comment', col)
 FROM generate_series(1,1000000) g(col);

3. Analyze the table to populate statistics.

ANALYZE orders;

4. Run a query that counts the number of orders in the second week of Jan. You can run the query with timing on so that you can see how long it takes to run the query before and after the index is created.

\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';

Time: 42.196 ms

5. Manually request an index recommendation.

SELECT * FROM google_db_advisor_recommend_indexes();
                      index                       | estimated_storage_size_in_mb 
--------------------------------------------------+------------------------------
 CREATE INDEX ON "public"."orders"("o_orderdate") |                           25
(1 row)

6. View recommended indexes for tracked queries.

SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != '';
               recommended_indexes                |                                             query                                              
--------------------------------------------------+------------------------------------------------------------------------------------------------
 CREATE INDEX ON "public"."orders"("o_orderdate") | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
(1 row)

6. View recommended indexes for tracked queries.

SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != ”;
recommended_indexes | query
————————————————–+————————————————————————————————
CREATE INDEX ON “public”.”orders”(“o_orderdate”) | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= ‘2023-01-09’ and o_orderdate <= ‘2023-01-15’;
(1 row)

7. Add the recommended index.

CREATE INDEX ON "public"."orders"("o_orderdate");

8. Re-run the query.

\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';

Time: 1.403 ms  (compared to 42.196ms before the index)

Learn more

• To learn more about AlloyDB’s Index Advisor, see Enable and use the index advisor | AlloyDB for PostgreSQL | Google Cloud

• To learn about AlloyDB, read AlloyDB for PostgreSQL intelligent scalable storage | Google Cloud Blog
• Start building on Google Cloud with $300 in free credits and 20+ always free products. https://cloud.google.com/free

If your organization is one of the 52% of SAP customers whose top analytics pain point is data integration¹, Google Cloud has got you covered. By working with partners like Qlik, we are expanding our integration options and bringing real-time replication capability for SAP to BigQuery.

Integrated data for accelerated insights

BigQuery—our fully managed, enterprise data warehouse that can scale up to petabytes on demand and execute queries in seconds—allows SAP customers to consolidate enterprise data silos and confidently derive more use and value from their data.  Customers can accelerate and simplify the delivery of SAP data on BigQuery with the latest Qlik Data Integration platform offering for Google Cloud which allows data integration using an automated, near real-time data pipeline through Qlik Replicate, whether data originates from legacy SAP environments, SAP HANA, or SAP application servers. Additionally, Qlik Compose for Data Warehouses can be used to easily generate and automate logical data models from SAP directly in BigQuery freeing up more time for data analysts to leverage advanced built-in capabilities such as BigQuery ML to derive greater value and insights using standard SQL without the need for advanced programming expertise.

Delivering faster results with real-time 

Traditional extract-transform-load (ETL) solutions operate on a batch basis, pulling data sets from SAP daily, hourly, or every minute. These tools often require manual mapping of multiple data fields so that data flows accurately. Given SAP’s highly complex table relationships, in which a single transaction can result in multiple changes, this can be a time consuming and tedious process. ETL can also increase the burden on your SAP systems.

Qlik Replicate simplifies this with its intuitive user interface where you can set up real-time data replication between SAP and BigQuery, eliminating the need for manual coding. And, to prevent system overhead, as soon as a new transaction is entered into SAP, the resulting data is replicated into BigQuery in a process known as change data capture (CDC) from SAP’s log layer. This means that data transfer can benefit from high performance with minimal impact on the source system’s resources. Read our latest white paper to learn how to extract SAP data into BigQuery leveraging Qlik Replicate. 

Solution expertise for all core SAP workloads

It doesn’t matter what database your SAP system runs on, Qlik Replicate supports all core SAP systems. It automates real-time data replication and decodes SAP’s complex, application-specific data structures into formats that flow smoothly into BigQuery. This ensures that anyone who depends on data analytics has the most current and relevant SAP data they need. For its robust solution capabilities Qlik has received a new “SAP on Google Cloud Expertise” designation for supporting:

• Fast onboarding and accelerated replication of SAP data into Google Cloud
• Real-time and continuous data replication from SAP applications to BigQuery
• Support for all core SAP modules and a broad set of data sources
• Automated data integration, which cuts resource requirements for initial delivery and ongoing maintenance

Proven customer results

Many SAP customers have experienced the benefits of leveraging Qlik alongside BigQuery for data analytics and AI at scale, including German luxury department store chain Breuninger. In order to meet its customers’ growing and changing expectations,Breuninger needed to accelerate its time to insight from data sources across a highly dispersed landscape of on-premises databases and systems, including SAP. The company uses Qlik Replicate to feed corporate data from modules in its SAP system into BigQuery and integrate its varying on-premises databases with the Google Cloud environment. This has yielded game-changing, real-time customer insights for the retailer.

What could your business do with faster, more integrated insights? Learn more about BigQuery for SAP customers and also how Qlik and Google Cloud can help you modernize and automate data integration and analytics.

The ability to gain real-time insights from IoT data can redefine competitiveness for businesses. Intelligence allows connected devices and assets to interact efficiently with applications and with human beings in an intuitive and non-disruptive way. After your IoT project is up and running, many devices will be producing lots of data. You need an efficient, scalable, affordable way to both manage those devices and handle all that information. 

IoT Core is a fully managed service for managing IoT devices. It supports registration, authentication, and authorization inside the Google Cloud resource hierarchy as well as device metadata stored in the cloud, and the ability to send device configuration from other GCP or third-party services to devices. 

Main components

The main components of Cloud IoT Core are the device manager and the protocol bridges:

• The device manager  registers devices with the service, so you can then monitor and configure them. It provides:
  • Device identity management 
  • Support for configuring, updating, and controlling individual devices
  • Role-level access control
  • Console and APIs for device deployment and monitoring
• Two protocol bridges (MQTT and HTTP) can be used by devices to connect to Google Cloud Platform for:
  • Bi-directional messaging
  • Automatic load balancing
  • Global data access with Pub/Sub

How does Cloud IoT Core work?

Device telemetry data is forwarded to a Cloud Pub/Sub topic, which can then be used to trigger Cloud Functions as well as other third-party apps to consume the data. You can also perform streaming analysis with Dataflow or custom analysis with your own subscribers.

Cloud IoT Core supports direct device connections as well as gateway-based architectures. In both cases the real time state of the device and the operational data is ingested into Cloud IoT Core and the key and certificates at the edge are also managed by Cloud IoT Core. From Pub/Sub the raw input is fed into Dataflow for transformation, and the cleaned output is populated in Cloud Bigtable for real-time monitoring or BigQuery for warehousing and machine learning. From BigQuery the data can be used for visualization in Looker or Data Studio and it can be used in Vertex AI for creating machine learning models. The models created can be deployed at the edge using Edge Manager (in experimental phase). Device configuration updates or device commands can be triggered by Cloud Functions or Dataflow to Cloud IoT Core, which then updates the device.  

Design principles of Cloud IoT Core

As a managed service to securely connect, manage, and ingest data from global device fleets, Cloud IoT COre is designed to be:

• Flexible, providing easy provisioning of device identities and enabling devices to access most of Google Cloud
• IThe industry leader in IoT scalability and performance
•  Interoperable, with supports for the most common industry-standard IoT protocols

Use cases

IoT use cases range across numerous industries. Some typical examples include:

• Asset tracking, visual inspection, and quality control in retail, automotive, industrial, supply chain and logistics
• Remote monitoring and predictive maintenance in oil & gas, utilities, manufacturing, and transportation
• Connected homes and consumer technologies.
• Vision intelligence in retail, security, manufacturing, and industrial sectors
• Smart living in commercial, residential, and smart spaces 
• Smart factories with predictive maintenance and real-time plant floor analytics

 For a more in-depth look into Cloud IoT Core check out the documentation.  

https://youtube.com/watch?v=76v16P-Wqe4%3Fenablejsapi%3D1%26

For more #GCPSketchnote, follow the GitHub repo. For similar cloud content follow me on Twitter @pvergadia and keep an eye out on thecloudgirl.dev.

As Google Cloud has become a choice for more startups, I’ve experienced an increase in founders asking how they should think about cloud services. Though each startup is different and requirements may vary across industries and regions, I’ve seen a few core best practices that help startups to succeed—as well as several traps to avoid. 

For example, If you go with a public cloud provider, you’re ideally not starting from ground zero like you would running your own data center, but it’s important not to introduce similar complexity in a virtualized environment. Just because you’re using public cloud infrastructure doesn’t mean you want to manage it.

Instead, you want to leverage platforms that abstract away complexity so your team can focus on delivering value to customers. That’s where serverless comes in. Serverless platforms are fully managed by the provider, offering automatic scaling for workloads, as well as provisioning, configuring, patching, and management of servers and clusters. Freed from these resource-intensive tasks, your technical talent can focus on the things that differentiate your business, not on IT curation. 

This applies to not only running stateless applications, but also managing and analyzing data. You should be collecting data points about which features are most popular on your platform, what people are buying, the types of activities that help your customers get their jobs done, and so on. This information is essential to building and executing on a product roadmap that will serve your customers. However, it’s not enough to collect this data, you need to make your data accessible, secure, and easy for your team to analyze—so where do you run and host it? 

On Google Cloud, this is where options like Spanner and Cloud SQL can play a large role, as can BigQuery for analysis. You won’t have to worry about standing up infrastructure or patching servers—you can just stream your data to our data management platforms, where it’s available whenever someone needs to run a query. By leveraging a serverless architecture, your startup can be data-driven without having to invest in the traditional complexity of database administration and management—and that can significantly change your playing field. 

Serverless is just one of the factors you should consider as you build out your tech stack. To hear my thoughts on a range of other topics relevant to startups — such as security, cloud credits, and the differences among managed services — check out the below video or visit our Build and Grow page.

https://www.youtube.com/embed/gn4RjLbs8Hc?enablejsapi=1&