If you’re managing a crucial application that has to be fully fault-tolerant, you need your system to be able to handle every fault, no matter the type and scope of failure, with minimal downtime and data loss. Protecting against these faults means juggling numerous variables that can impact performance as well as recovery time and cost.

Today’s managed database services take over the operational complexity that used to exist for database administrators. Growing your organization’s tolerance required adding machines, compute, and storage, plus the operational costs of IT management: performing backups, writing scripts, creating dashboards, and carrying out testing to make sure your platform is ready when problems arise–all in a secure way.

At Google, our Cloud SQL managed database service offers three fault tolerance mechanisms —backup, high availability, and replication—and there are three major factors to consider for each of them:

• RTO (recovery time objective): When a failure happens, how much time can be lost before significant harm occurs?
• RPO (recovery point objective): When a failure happens, how much data can be lost before significant harm occurs?
• Cost: How cost-effective is this solution?  

We’ve heard from customers like Major League Baseball, HSBC, and Equifax that they have strict data-protection needs and require highly fault-tolerant multi-region applications—and they’ve all chosen Cloud SQL to meet those needs. 

Let’s take a closer look at how the decision-making process plays out for each recovery solution.  

High availability (HA)

If your application is business critical, you require minimum RTO and zero RPO— a high availability configuration ensures that you and your customers are protected. If the primary instance fails, there’s another standby instance ready to take over with no data loss. There’s an additional cost here, but doing this manually brings a great operational cost, since you have to detect and verify the fault, do the failover, and make sure it’s correct—you can’t have two primary instances or you risk data corruption—then finally connect the application to the new database.

Cloud SQL removes all that complexity. Choose high availability for a given instance and we’ll replicate the data across multiple zones, synchronously, to each zone’s persistent disk. If an HA instance has a failure, you don’t have to think about when to fail over because Cloud SQL detects the failure and automatically initiates failover, for a full recovery and no data loss within minutes. Cloud SQL also moves the IP address during failover so your application can easily reconnect. MLB, for example, uses Cloud SQL high availability to serve prediction data to live games with minimal downtime. Dev/test instances don’t need those same guarantees, but can use local backups to recover from any potential failure.

Cross-region replica

If a whole Google Cloud region goes down you still need your business to continue to run. That’s where cross-region replication comes in, a hot standby replica in another Google Cloud region provides RTO of minutes and RPO typically less than a minute . If you create a read replica in a region separate from your primary instance and you get hit with a regional outage, your application and database can start serving customers from another region within minutes. But this solution can be complex and enabling it yourself can be difficult and time-consuming. Securing cross-geography traffic demands end-to-end encryption and can bring connectivity issues too. 

This is where the fully managed Cloud SQL solution shines. We offer MySQL, PostgreSQL and SQL Server database engines as a cross-region replication solution that’s easily configured and bolstered by Google’s interconnected global network. Just say, “I’m in U.S. East, I want to create a replica in U.S. West,” and it’s done, reliably and securely.

Backup

When you suffer data loss because of an operations error (for example, a bug in a script dropped your tables) or human error (for example, someone dropped the wrong table by accident), backups help you restore lost data to your Cloud SQL instance. Our low cost backup mechanism features point-in-time, granular recovery, meaning that if you accidentally delete data or something else goes wrong, you can ask for recovery of, for example, the state of that database down to the millisecond, such as Monday at 12:53pm. Your valuable data is replicated multiple times in multiple geographic locations automatically. This enables the automatic handling of failover in cases of major failure. You can always rest assured that your database is available and data is secure, even in the times of major failure crises. 

Cloud SQL provides automated and on-demand backups. With automated backups, Google manages the backups so that you can easily restore them when required. Also, the scheduled backing is automatically taken by default. With on-demand backup, you can create a backup at any time. This could be useful if you are about to perform a risky operation on your database, as Cloud SQL lets you select a custom location for your backup data. When the backup is stored in multiple regions, and there’s an outage in the region that contains the source instance, you can restore a backup to a new or existing instance in a different region. This is also useful if your organization needs to comply with data residency regulations that require you to keep your backups within a specific geographic boundary.

Putting it all together

For critical workloads, MLB configures their Cloud SQL instances with backups, high availability, and cross-region replication. Doing so ensures they can recover from many failure types.

• To recover from human error (“Oops, I didn’t mean to delete that”), MLB uses backups and point-in-time recovery to recovery to a millisecond or specific database transaction
• To automatically recover from primary instance failures and zonal outages, MLB uses Cloud SQL’s high availability configuration
• To protect against regional outages, MLB uses cross-region replication

Creating a robust configuration, like MLB did, takes just a few minutes. Get started in our Console or review documentation.

Do you want to fire up a bunch of techies? Talk about multicloud! There is no shortage of opinions. I figured we should tackle this hot topic head-on, so I recently talked to four smart folks—Corey Quinn of Duckbill Group, Armon Dadgar of Hashicorp, Tammy Bryant Butow of Gremlin, and James Watters of VMware—about what multicloud is all about, key considerations, and why you should (or shouldn’t!) do it.

Five important insights came out of these discussions. If you’re on a multicloud journey or considering one, keep reading.

Do: Choose to do multicloud for the right reasons

Don’t do multicloud because Gartner says so, implores Corey Quinn. Before embarking on a multicloud, define a “why” focused on business value journey, says Armon Dadger. For example, you might want to use services from each public cloud because of their differentiated services, according to Tammy Bryant Butow. Armon also calls out regulatory reasons, existing business relationships, and accommodating mergers and acquisitions. On the topic of M&A, Corey points out that if you acquire a company that uses another cloud, it’s usually expensive and difficult to consolidate. It can be smarter to stay put.

Don’t: Over-engineer for workload or data portability

Thinking that you’ll build a system that moves seamlessly among the various cloud providers? Hold up, says our group of experts. Armon points out that aspects of your toolchain or architecture may be multicloud—think of some of your workflows or global network routing—but that shifting workloads or data is far from simple. Corey says that trying to engineer for “write once, run anywhere” can slow you down, and ignores the inherent uniqueness that’s part of each platform. Specifically, Corey calls out the per-cloud stickiness of identity management, security features, and even network functionality. And data gravity is still a thing, says James, that causes some to dismiss multicloud outright.

If you’re using multiple public clouds, you take advantage of the distinct value each offers, Armon says. Use native cloud services where possible so that you see the benefits from useful innovations, built-in resilience, and baked-in best practices. The value from that cloud-infused workload may outweigh the benefits of seamless portability.

Do: Recognize different stakeholder interests and needs

James smartly points out that many multicloud debates happen because people are arguing from different perspectives. Context matters. If you’re an infrastructure engineer who invests heavily in a given cloud’s identity and access management model, multicloud looks tricky. Or if you’re a data engineer with petabytes of data homed in a particular cloud, multicloud may look unrealistic. James highlights that many developers default to multicloud because their local tools—where all the work happens—are multicloud. A developer’s IDE and preferred code framework(s) aren’t tied to any given cloud. Be aware that groups within your organization will come at multicloud from distinct directions. And this may impact your approach!

Don’t: Go it alone

Corey talks about the importance of asking others what worked, and what didn’t. Tammy offers her best practices around sharing results from experiments. It’s about sharing knowledge and tapping into it for community benefit. Others have probably tried what you’re trying, and can help you avoid common pitfalls. If you’ve just made an architectural choice that didn’t work out, share it, and help others avoid the pain. 

Read research from analysts, go to conferences or watch videos to observe case studies, and join online communities that offer a safe place to share mistakes and learn from others.

Do: Experiment first using techniques like multi-region deployments

If you think you can operate systems across clouds, how about you first try doing it across regions in a specific cloud, suggests Corey. Getting a system to properly work across cloud regions isn’t trivial, he says, and that experience can help you uncover where you have architectural or operational constraints that will be even worse across cloud providers.

This is great guidance if your multicloud aspirations involve using multiple clouds to power one application—versus the more standard definition of multicloud where you use different clouds for different applications—but can also surface issues in your support process or toolchain that fail when faced with distributed systems. Start with muti-region deployments and chaos engineering experiments before aggressively jumping into multicloud architectures.

The Google Cloud take

Do the things above. It’s great advice. I’ll add three more things that we’ve learned from our customers.

1. Don’t fear multicloud. You’re already doing it. You don’t single-source everything. As Corey mentioned, you probably already have one cloud for productivity tools, another for source code, another for cloud infrastructure. You’ll use software and application services from a mix of providers for a single app. You have that experience in your team and have been doing that for decades. What people do rightly worry about is using more than one infrastructure service beneath an application, as that can introduce latency, security, and logistical hurdles. Make sure you know which model your team is considering.
2. Embrace the right foundational components, including Kubernetes. Will everything run on Kubernetes? Of course not. Don’t try to do that. But it also represents the closest thing we have to a multicloud API. Companies are using Kubernetes to stripe a consistent experience across clouds. And this isn’t just to orchestrate containers, but also to manage infrastructure and cloud-native services. Also, consider where you need other fundamental consistency across clouds, including areas like provisioning and identity federation.
3. Use Google Cloud as your anchor. Here’s a fundamental question you have to decide for yourself: Are you going to bring your on-premises technology and practices to the cloud, or bring cloud technology and practices on-prem? We sincerely believe in the latter. Anchor to where you’re trying to get to. We offer Anthos as a way to build and run distributed Kubernetes fleets in Google Cloud and across clouds. By using a cloud-based backplane instead of an on-prem one, you’re offloading toil, leveraging managed services for scale and security, and introducing modern practices to the rest of your team.

We learned a lot about multicloud through these discussions, and it seems like others did too. That’s why we’re going to do a second round of interviews with a new crop of experts so that we can keep digging deeper into this topic. Stay tuned!

Over the past few years, many organizations have experienced the benefits of migrating their SAP solutions to Google Cloud. But this migration can do more than reduce IT maintenance costs and make data more secure. By leveraging BigQuery, SAP customers can complement their SAP investments and gain fresh insights by consolidating enterprise data and easily extending it with powerful datasets and machine learning from Google. 

BigQuery is a leading cloud data warehouse, fully managed and serverless, and allows for massive scale, supporting petabyte-scale queries at super-fast speeds. It can easily combine SAP data with additional data sources, such as Google Analytics or Salesforce, and its built-in machine learning lets users operationalize machine learning models using standard SQL — all at a comparatively low cost. 

If your SAP-powered organization is looking to supercharge its analytics with the strength of BigQuery, read on for considerations and recommendations for modeling with SAP data. These guidelines are based on our real-world implementation experience with customers and can serve as a roadmap to the analytics capabilities your business needs.

Considerations for data replication 

Like most technology journeys, this one should start with a business objective. Keeping your intended business value and goals in mind is critical to making the right decisions in the early steps of the design process.

When it comes to replicating the data from an SAP system into BigQuery, there are multiple ways to do it successfully. Decide which method will work best for your organization by answering these questions:

• Does your business need real-time data? Will you need to time travel into past data?
• Which external datasets will you need to join with the replicated data?
• Are the source structures or business logic likely to change? Will you be migrating the SAP source systems any time soon? For instance, will you be moving from SAP ECC to SAP S/4HANA?

You’ll also need to determine whether replication should be done on a table-by-table basis or whether your team can source from pre-built logic. This decision, along with other considerations such as licensing, will influence which replication tool you should use.

Replicating on a table-by-table basis
Replicating tables, especially standard tables in their raw form, allows sources to be reused and ensures more stability of the source structure and functional output. For example, the SAP table for sales order headers (VBAK) is very unlikely to change its structure across different versions of SAP, and the logic that writes to it is also unlikely to change in a way that affects a replicated table. 

Something else to consider: Reconciliation between the source system and the landing table in BigQuery is linear when comparing raw tables, which helps avoid issues in consolidation exercises during critical business processes, such as period-end closing. Since replicated tables aren’t aggregated or subject to process-specific data transformation, the same replicated columns can be reused in different BigQuery views. You can, for instance, replicate the MARA table (the material master) once and use it in as many models as needed. 

Replicating pre-built logic
If you replicate pre-built models, such as those from SAP extractors or CDS views, you don’t need to build the logic in BigQuery, since you’re using existing logic. Some of these extraction objects have embedded delta mechanisms, which may complement a replication tool that can’t handle deltas. This will save initial development time, but it can also lead to challenges if you create new columns, or if customizations or upgrades change the logic behind the extraction. 

It’s also important to note that different extraction processes may transform and load the same source columns multiple times, which creates redundancy in BigQuery and can lead to higher maintenance needs and costs. However, replicating pre-built models may still be a good choice, since doing so can be especially useful for logic that tends to be immutable, such as flattening a hierarchy, or logic that is highly complex.

How you approach replication will also depend on your long-term plans and other key factors — for example, the availability (and curiosity) of your developers, and the time or effort they can put into applying their SQL knowledge to a new data warehouse. 

With either replication approach, bear in mind when designing your replication process that BigQuery is meant to be an append-always database — so post-processing of data and changes will be required in both cases. 

Processing data changes

The replication tool you choose will also determine how data changes are captured (known as CDC – change data capture). If the replication tool allows for it (for example as SAP SLT does) the same patterns described in the CDC with BigQuery documentation also apply to SAP data. 

Because some data, like transactions, are known to be less static than others (e.g., master data), you need to decide what should be scanned in real time, what will require immediate consistency, and what can be processed in batches to manage costs. This decision will be based on the reporting needs from the business.

Consider the SAP table BUT000, containing our example master data for business partners, where we have replicated changes from an SAP ERP system:

In an append-always replication in BigQuery, all updates are received as new records. For example, deleting a record in the source will be represented as a new record in BigQuery with a deletion flag. This applies to whether the records are coming from raw tables like BUT000 itself or pre-aggregated data, as from a BW extractor or a CDS view.

Let’s take a closer look at data coming particularly from the partners “LUCIA” and “RIZ”. The operation flag tells us whether the new record in BigQuery is an insert (I), update (U) or deletion (D), while the timestamps help us identify the latest version of our business partner.

If we want to find the latest updated record for the partners LUCIA and RIZ, this is what the query would look like:

  SELECT partner,
        ARRAY_AGG(i1 ORDER BY i1.recordstamp DESC LIMIT 1) AS row
FROM SAP_ECC.but000 i1 
WHERE partner in ('LUCIA','RIZ')
    GROUP BY partner

With the following result:

After identifying stale records for “LUCIA” and “RIZ” business partners, we can proceed to deleting all stale records for “LUCIA” if we do not want to retain the history. In this example, we are using a different table to which the same replication has been done, for the purpose of comparison and to check that all stale records have been deleted for the selection made and that we only kept last updated records. For example:

  DELETE SAP_HANA.but000 i1
WHERE
i1.recordstamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR) AND
i1.recordstamp < (SELECT MAX(recordstamp) FROM SAP_HANA.but000 i2
WHERE
i1.partner = i2.partner
and partner="LUCIA")

You can also use the following query to retrieve stale records for “LUCIA” partner before moving forward with deletion

  SELECT partner, operation_flag, recordstamp  FROM SAP_HANA.but000 i1
WHERE
i1.recordstamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR) 
AND
i1.recordstamp < (SELECT MAX(recordstamp) FROM SAP_HANA.but000 i2 
WHERE 
i1.partner = i2.partner
and partner="LUCIA")

Which produces all of the records, except the latest update:

Partitioning and clustering

To limit the number of records scanned in a query, save on cost and achieve the best performance possible, you’ll need to take two important steps: determine partitions and create clusters. 

Partitioning
A partitioned table is one that’s divided into segments, called partitions, which make it easier to manage and query your data. Dividing a large table into smaller partitions improves query performance and controls costs because it reduces the number of bytes read by a query.

You can partition BigQuery tables by:

• Time-unit column: Tables are partitioned based on a “timestamp,” “date,” or “datetime” column in the table.
• Ingestion time: Tables are partitioned based on the timestamp recorded when BigQuery ingested the data.
• Integer range: Tables are partitioned based on an integer column.

Partitions are enabled when the table is created, as in the example below.  A great tip is to always include the partition filter as shown on the left-hand side of the query.

Clustering
Clustering can be created on top of partitioned tables by applying the fields that are likely to be used for filtering. When you create a clustered table in BigQuery, the table data is automatically organized based on the contents of one or more of the columns in the table’s schema. The columns you specify are then used to colocate related data.

Clustering can improve the performance of certain query types — for example, queries that use filter clauses or that aggregate data. It makes a lot of sense to use them for large tables such as ACDOCA, the table for accounting documents in SAP S/4HANA. In this case, the timestamp could be used for partitioning, and common filtering fields such as the ledger, company code, and fiscal year could be used to define the clusters.

A great feature is that BigQuery will also periodically recluster the data automatically.

Materialized views

In BigQuery, materialized views are precomputed views that periodically cache the results of a query for better performance and efficiency. BigQuery uses precomputed results from materialized views and, whenever possible, reads only the delta changes from the base table to compute up-to-date results quickly. Materialized views can be queried directly or can be used by the BigQuery optimizer to process queries to the base table.

Queries that use materialized views are generally completed faster and consume fewer resources than queries that retrieve the same data only from the base table. If workload performance is an issue, materialized views can significantly improve the performance of workloads that have common and repeated queries. While materialized views currently only support single tables, they are very useful common and frequent aggregations like stock levels or order fulfillment.

Further tips on performance optimization while creating select statements can be found in the documentation for optimizing query computation.

Deployment pipeline and security

For most of the work you’ll do in BigQuery, you’ll normally have at least two delivery pipelines running — one for the actual objects in BigQuery and the other to keep the data staging, transforming, and updated as intended within the change-data-capture flows. Note that you can use most existing tools for your Continuous Integration / Continuous Deployment (CI/CD) pipeline — one of the benefits of using an open system like BigQuery. But, if your organization is new to CI/CD pipelines, this is a great opportunity to gradually gain experience. A good place to start is to read our guide for setting up a CI/CD pipeline for your data-processing workflow.  

When it comes to access and security, most end-users will only have access to the final version of the BigQuery views. While row and column-level security can be applied, as in the SAP source system, separation of concerns can be taken to the next level by splitting your data across different Google Cloud projects and BigQuery datasets. While it’s easy to replicate data and structures across your datasets, it’s a good idea to define the requirements and naming conventions early in the design process so you set it up properly from the start. 

Start driving faster and more insightful analytics

The best piece of advice we can give you is this: Try it yourself. Anyone with SQL knowledge can get started using the free BigQuery tier. New customers get $300 in free credits to spend on Google Cloud during the first 90 days. All customers get 10 GB storage and up to 1 TB queries/month, completely free of charge. In addition to discovering the massive processing capabilities, embedded machine learning, multiple integration tools, and cost benefits, you’ll soon discover how BigQuery can simplify your analytics tasks. 

If you need additional assistance, our Google Cloud Professional Services Organization (PSO) and Customer Engineers will be happy to help show you the best path forward for your organization. For anything else, contact us at cloud.google.com/contact.

Road construction is booming in India. A recent report prepared by the India Brand Equity Foundation—a trust established by the Department of Commerce—pointed out that in FY 2019 alone, the country added 10,855 kilometers of highways to a road network that spans 5.89 million kilometers. This network is the second largest in the world and transports 90% of passenger traffic and 64.5% of all goods in the country.

The Indian Government has also earmarked road construction as key to plans to increase the nation’s GDP to $5 trillion in coming years, targeting road construction worth $212.8 billion in the two years from April 2020.

G R Infraprojects Limited is well positioned to support the government’s program. The business, which started as a contractor building roads in rural villages in India, now specializes in road engineering, procurement, and construction (EPC), a model whereby private construction firms build roads funded by the government.

The business now undertakes the processing of bitumen, manufacture of thermoplastic road-marking paint and road signage, and fabrication and galvanizing road-crash barriers. Its in-house integration model includes a design and engineering team, as well as manufacturing facilities in Rajasthan, Assam, and Gujarat.

The business recently expanded into rail—another area expected to benefit from extensive government investment—with its competencies including earthworks, materials supply, track lining, and bridge construction.

In this environment—and despite the economic impact of the coronavirus pandemic—G R Infraprojects Limited aims to substantially increase turnover and manpower over the next five years.

Best-in-class infrastructure key to success

Digital transformation is key to enabling growth while best-in-class IT infrastructure is one of the foundations on which the business seeks to build success.

G R Infraprojects Limited’s digital initiatives include deployment of a new document management system and corporate systems that enable remote monitoring, live tracking, effective real-time communication, and efficient data management.

Providing a scalable, reliable cost-effective infrastructure

But most important of all is providing a scalable, reliable, and cost-effective infrastructure to support a business-critical SAP enterprise resource planning system. Over the last few years, versions of SAP have enabled the organization to digitize processes and seamlessly run business-critical functions such as inventory management and finance.

G R Infraprojects Limited initially went live with SAP ECC6.0, with a few hundred team members using the system for business-critical tasks such as tracking stock level and movement and generating financial statements and reports for review and action.

“Google Cloud is a very big brand, so we were easily able to secure the trust from our executive and business teams to run an important system such as SAP S/4HANA on the platform.”—Sachin Kumar Agarwal, Head, Transformation, G R Infraprojects Limited

Lowering maintenance costs

However, as G R Infraprojects Limited grew, projects proliferated, and new markets emerged, the business elected to move to the cloud from an on-premises infrastructure. “We wanted to move because there were so many maintenance costs in on-premises solutions and cloud provided convenience to IT and the broader organization,” explains Sachin Kumar Agarwal, Head, Transformation at G R Infraprojects Limited.

The business also wanted to move to SAP S/4HANA to take advantage of features such as AI, advanced analytics, and machine learning to transform business processes. The system runs on the HANA database, an in-memory database with fast processing speeds and a simplified data model.

G R Infraprojects Limited selected Google Cloud to run SAP S/4HANA because, Sachin says, it is “much better than any other platform,” incorporates a wide range of features, and meets uptime requirements. The cloud service could also scale to support forecast growth without a sharp increase in cost.

Furthermore, Sachin adds, “Google Cloud is a very big brand, so we were easily able to secure the trust from our executive and business teams to run an important system such as SAP S/4HANA on the platform.”

“With Google Cloud, our speed and availability are controlled and optimized day by day. With such a scalable and dynamic platform, we are very happy with the performance.”—Sachin Kumar Agarwal, Head, Transformation, G R Infraprojects Limited

Successful transition with Infrabeat

G R Infraprojects Limited completed the project with assistance from partner InfraBeat over three months and SAP S/4HANA on Google Cloud went live mid-2019. “Our dedicated SAP team worked closely with Infrabeat to deliver the project successfully,” says Sachin. “We needed an experienced partner to assist with the implementation process and Infrabeat performed that role admirably. Both teams supported each other and worked to plan to deliver a great result.”

SAP S/4HANA runs on an infrastructure comprising virtual machine instances delivered through Compute Engine, Cloud Storage, and Cloud NAT to enable the secure transmission and receipt of packets to and from the internet.

G R Infraprojects Limited estimates the cost of running SAP S/4 HANA in Google Cloud is significantly lower than on alternative infrastructure options—freeing up budget for other business priorities.

In addition, moving SAP S/4 HANA to infrastructure as a service through Google Cloud has eliminated the need to assign internal team members to infrastructure management, allowing them instead to focus on higher-value activities.

Google Cloud also incorporates the security needed to protect the data and processes of SAP S/4 HANA from intrusion or disruption and ensure the uptime and continuity required of a business-critical system.

Optimized speed and availability

G R Infraprojects Limited’s decision to run SAP S/4 HANA on Google Cloud is delivering benefits on a daily basis. “With Google Cloud, our speed and availability are controlled and optimized day by day,” says Sachin. “We are very happy with the performance.”

Success with SAP S/4HANA has helped the business decide to move its remaining apps and data to the cloud when its on-premises servers and other equipment reach end of life. G R Infraprojects Limited is already running Active Directory in Google Cloud and Sachin says the cloud platform’s “fast and excellent services” made the decision easy.

“There are definitely instances of various upgrades of our systems and within the organization, and as we talk about our application and mobility requirements, we see Google Cloud playing a crucial role.”—Sachin Kumar Agarwal, Head, Transformation, G R Infraprojects Limited

Adding value

As G R Infraprojects Limited grows, Sachin adds, the business expects Google Cloud to continue to add value. “There are definitely instances of various upgrades of our systems and within the organization, and as we talk about our application and mobility requirements, we see Google Cloud playing a crucial role.”