<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Shashank Agarwal on Medium]]></title>
        <description><![CDATA[Stories by Shashank Agarwal on Medium]]></description>
        <link>https://medium.com/@agarwalsh?source=rss-e4d65879b270------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*9wN1t0z5fF26Iyfz.jpg</url>
            <title>Stories by Shashank Agarwal on Medium</title>
            <link>https://medium.com/@agarwalsh?source=rss-e4d65879b270------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 03:32:35 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@agarwalsh/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Fast export large database tables — using GCP Serverless Dataproc (Spark)]]></title>
            <link>https://medium.com/google-cloud/fast-export-large-database-tables-using-gcp-serverless-dataproc-spark-bb32b1260268?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/bb32b1260268</guid>
            <category><![CDATA[spanner]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[import-table]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[table-export]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Thu, 26 May 2022 19:24:06 GMT</pubDate>
            <atom:updated>2022-09-21T21:57:06.184Z</atom:updated>
            <content:encoded><![CDATA[<h3>Fast export large database tables — using GCP Serverless Dataproc</h3><p>Import large tables from MySQL (any JDBC) to Cloud Spanner (or GCS, BigQuery)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/0*zg60riCuBk9HSgGc" /><figcaption>Spark to export large tables</figcaption></figure><h3>Introduction</h3><p>Are you looking to export/import large tables with 100s of GBs-TBs fast?</p><p>Approach to export/import data out in multiple threads parallelly ?</p><p>Prefer to preserve the schema of the export the data ?</p><p>Using a robust, proven, open source and hardened mechanism ?</p><p>A. The <a href="https://github.com/GoogleCloudPlatform/dataproc-templates/tree/main/java/src/main/java/com/google/cloud/dataproc/templates/jdbc#2-jdbc-to-gcs"><strong>JDBCToGCS</strong></a> template which uses GCP’s Serverless Dataproc can help you in exporting the tables in a fast, efficient and multi threaded fashion.</p><p>B. Use <a href="https://github.com/GoogleCloudPlatform/dataproc-templates/blob/main/java/src/main/java/com/google/cloud/dataproc/templates/gcs/README.md"><strong>GCSToSpanner</strong></a> template to import the data from GCS to Cloud Spanner.</p><h3><strong>Key Benefits</strong></h3><ol><li>Dataproc Serverless is fully managed, serverless and autoscaling.</li><li>Both the templates are open source, fully customizable and ready to use for simple jobs.</li><li>Spark has built in jdbc dialects for MySQL, Postgresql, DB2, Derby, H2, SQL Server, Oracle, Postgresql and Teradata databases.</li><li>Ephemeral, gcp resources are release once job has ended.</li><li>You can export data in <strong>avro, parquet, csv and orc </strong>formats.</li><li>Allows table partitions, using which you can read the data in chunks parallelly.</li><li>GCSToSpanner template can read multiple files in parallel (1 file per thread at a time) and load huge volumes of data with blazing fast speed into Cloud Spanner.</li></ol><h3>Simple Usage</h3><p>This usage works well for small tables (&lt;1 GB in size) as it will not be multi-threaded.</p><ol><li>Ensure you have enabled the subnet with Private Google Access, if you are going to use “default” VPC Network generated by GCP. Still you will need to enable private access as below. (<a href="https://cloud.google.com/dataproc-serverless/docs/concepts/network">Refer here</a> for details)</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/285/0*2OKjt1b9tkrYIcYp.png" /></figure><pre>gcloud compute networks subnets update default --region=us-central1 --enable-private-ip-google-access</pre><p>2. [Preferred] Pause writes to the source database for consistency purposes. This can be done by creating read replica and pausing replication on it. Alternatively you can clone the active instance.</p><p>3. Ensure your source database is reachable from the VPC network. <br><strong>Note</strong>: Cloud SQL instances are reachable using Private IP when peered with the VPC Network.</p><p>4. Create a GCS bucket and staging location for jar files. <br>Also download the JDBC Driver jar for the respective source database and upload into the GCS Bucket.</p><p>5. Clone git repo in a cloud shell which is pre-installed with <a href="https://cloud.google.com/shell/docs/how-cloud-shell-works">various tools</a>. Alternatively use any machine pre-installed with JDK 8+, Maven3+ and Git.</p><pre>git clone <a href="https://github.com/GoogleCloudPlatform/dataproc-templates.git">https://github.com/GoogleCloudPlatform/dataproc-templates.git</a><br>cd dataproc-templates/java</pre><p>6. Obtain authentication credentials (to submit the job).</p><pre>gcloud auth application-default login</pre><p>7. Execute the template, refer <a href="https://github.com/GoogleCloudPlatform/dataproc-templates/tree/main/java/src/main/java/com/google/cloud/dataproc/templates/jdbc#2-jdbc-to-gcs">JDBCToGCS</a> documentation for more details. <br>Replace environment specific values (gcp project, region, jdbc url, path of driver jar etc).<br>Eg:</p><pre>export GCP_PROJECT=my-gcp-proj \<br>export REGION=us-central1  \<br>export SUBNET=projects/my-gcp-proj/regions/us-central1/subnetworks/default   \<br>export GCS_STAGING_LOCATION=gs://my-gcp-proj/mysql-export/staging \<br>export JARS=gs://my-gcp-proj/mysql-export/mysql-connector-java-8.0.17.jar<br><br>bin/start.sh \<br>-- --template JDBCTOGCS \<br>--templateProperty &#39;jdbctogcs.jdbc.url=jdbc:mysql://192.168.16.3:3306/MyCloudSQLDB?user=root&amp;password=root&#39; \<br>--templateProperty jdbctogcs.jdbc.driver.class.name=com.mysql.cj.jdbc.Driver \<br>--templateProperty jdbctogcs.output.location=gs://my-gcp-proj/mysql-export/export/table1_export \<br>--templateProperty jdbctogcs.output.format=parquet \<br>--templateProperty jdbctogcs.write.mode=Overwrite \<br>--templateProperty &#39;jdbctogcs.sql=SELECT * FROM MyDB.employee&#39;</pre><p><strong>NOTE</strong>: It will ask you to enable Dataproc Api, if not enabled already.</p><p>8. Import the data into Cloud Spanner using <a href="https://github.com/GoogleCloudPlatform/dataproc-templates/blob/main/java/src/main/java/com/google/cloud/dataproc/templates/gcs/README.md#2-gcs-to-spanner"><strong>GCSToSpanner</strong></a> template. Each executor/worker loads 1 file at a time, therefore having multiple files is key to parallelism. Below configuration uses wildcard “table1_export/<strong>part*.</strong>parquet” which can with one or more files.<br>Eg:</p><pre>export GCP_PROJECT=my-gcp-proj \<br>export REGION=us-central1  \<br>export SUBNET=projects/my-gcp-proj/regions/us-central1/subnetworks/default   \<br>export GCS_STAGING_LOCATION=gs://my-gcp-proj/mysql-export/staging</pre><pre>bin/start.sh \<br>-- --template GCSTOSPANNER \<br>--templateProperty project.id=my-gcp-proj \<br>--templateProperty gcs.spanner.input.format=parquet \<br>--templateProperty gcs.spanner.input.location=gs://my-gcp-proj/mysql-export/export/table1_export/part*.parquet \<br>--templateProperty gcs.spanner.output.instance=spanner-inst \<br>--templateProperty gcs.spanner.output.database=spanner-db \<br>--templateProperty gcs.spanner.output.table=employee \<br>--templateProperty gcs.spanner.output.saveMode=Overwrite \<br>--templateProperty gcs.spanner.output.primaryKey=id</pre><h3>Advance Usage (multi threaded export/import)</h3><p>Assuming you have a table schema of Employee in mysql database as below:</p><pre>CREATE TABLE `employee` (<br>  `id` bigint(20) unsigned NOT NULL,<br>  `name` varchar(100) NOT NULL,<br>  `email` varchar(100) NOT NULL,<br>  `current_salary` int unsigned DEFAULT NULL,<br>  `account_id` bigint(20) unsigned NOT NULL,<br>  `department` varchar(100) DEFAULT NULL,<br>  `created_at` datetime NOT NULL,<br>  `updated_at` datetime NOT NULL,<br>  PRIMARY KEY (`id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;</pre><p>Assuming the max employee id is 100 million (used for upperBound parameter).</p><p>Perform steps 1–4 as described in previous section.<br>Change step 5 by specifying the partition properties.</p><p>Execute spark job along with partition parameters, example below:</p><pre>export GCP_PROJECT=my-gcp-proj \<br>export REGION=us-central1  \<br>export SUBNET=projects/my-gcp-proj/regions/us-central1/subnetworks/default   \<br>export GCS_STAGING_LOCATION=gs://my-gcp-proj/mysql-export/staging \<br>export JARS=gs://my-gcp-proj/mysql-export/mysql-connector-java-8.0.17.jar<br><br>bin/start.sh \<br>-- --template JDBCTOGCS \<br>--templateProperty &#39;jdbctogcs.jdbc.url=jdbc:mysql://192.168.16.3:3306/MyCloudSQLDB?user=root&amp;password=root&#39; \<br>--templateProperty jdbctogcs.jdbc.driver.class.name=com.mysql.cj.jdbc.Driver \<br>--templateProperty jdbctogcs.output.location=gs://my-gcp-proj/mysql-export/export/table1_export \<br>--templateProperty jdbctogcs.output.format=parquet \<br>--templateProperty jdbctogcs.write.mode=OVERWRITE \<br>--templateProperty &#39;jdbctogcs.sql=select * FROM employee&#39; \<br><strong>--templateProperty jdbctogcs.sql.partitionColumn=id \<br>--templateProperty jdbctogcs.sql.lowerBound=0 \<br>--templateProperty jdbctogcs.sql.upperBound=100000000 \<br>--templateProperty jdbctogcs.sql.numPartitions=400</strong></pre><p>Continue step 6 same as simple usage.</p><h3>Gotchas !</h3><ol><li><strong>Deciding on #partitions</strong></li></ol><p>Spark SQL executes query and loads entire result-set in memory. Therefore each partition should only fetch data small enough to be held in memory. For Dataproc Serverless by default each worker gets 8 GB of memory. Hence fetching 500K rows with each row size of 1KB leads to 500MB memory usage is likely to work alright.</p><p>Having more partitions along with higher number of executors can further improve speed by increasing parallelism.<br>You can specify executors using below configuration:</p><pre>export SPARK_PROPERTIES=spark.executor.instances=50,spark.dynamicAllocation.maxExecutors=200</pre><p>2. <strong>Handling of “unsigned bigint” data types</strong></p><p>In above example, some columns are unsigned bigint like “id”, “account id”. It is common practice for DBAs to create unsigned bigint columns. However, <a href="https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JdbcUtils.scala#L180">Spark SQL by default</a> when reads “unsigned bigint” it converts them into “decimal” datatype. Therefore when parquet files are written on gcs, generated schema will contain “decimal(20,0)” data types correspondingly.</p><p>Often this is not desirable when importing data into Spanner as Numeric datatype which it will automatically infers.</p><p>If the data in such columns is lower than signed limit, then simply casting can solve the issue. One side effect of casting is, existing indexes will not work and make the sql query very slow. To mitigate is, we should also add the current column as and then partition on it. Later on when data is imported into Cloud Spanner, drop this extra column.</p><p>Eg:</p><pre>export GCP_PROJECT=my-gcp-proj \<br>export REGION=us-central1  \<br>export SUBNET=projects/my-gcp-proj/regions/us-central1/subnetworks/default   \<br>export GCS_STAGING_LOCATION=gs://my-gcp-proj/mysql-export/staging \<br>export JARS=gs://my-gcp-proj/mysql-export/mysql-connector-java-8.0.17.jar<br><br>bin/start.sh \<br>-- --template JDBCTOGCS \<br>--templateProperty &#39;jdbctogcs.jdbc.url=jdbc:mysql://192.168.16.3:3306/MyCloudSQLDB?user=root&amp;password=root&#39; \<br>--templateProperty jdbctogcs.jdbc.driver.class.name=com.mysql.cj.jdbc.Driver \<br>--templateProperty jdbctogcs.output.location=gs://my-gcp-proj/mysql-export/export/table1_export \<br>--templateProperty jdbctogcs.output.format=parquet \<br>--templateProperty jdbctogcs.write.mode=OVERWRITE \<br>--templateProperty <strong>&#39;jdbctogcs.sql=select CAST(id as SIGNED) id, name, email, current_salary, CAST(account_id as SIGNED) account_id, department, created_at, updated_at, id as tbl_id FROM employee&#39;</strong> \<br>--templateProperty jdbctogcs.sql.partitionColumn=tbl_id \<br>--templateProperty jdbctogcs.sql.lowerBound=0 \<br>--templateProperty jdbctogcs.sql.upperBound=100000000 \<br>--templateProperty jdbctogcs.sql.numPartitions=400</pre><p>3. <strong>Insert batch size<br></strong>You can fine tune the insert batch size for GCSToSpanner job by specifying the “gcs.spanner.output.batchInsertSize” property as below. Please note that Cloud Spanner limits 20,000 mutations per write request (as of today).</p><p>Eg:</p><pre>export GCP_PROJECT=my-gcp-proj \<br>export REGION=us-central1  \<br>export SUBNET=projects/my-gcp-proj/regions/us-central1/subnetworks/default   \<br>export GCS_STAGING_LOCATION=gs://my-gcp-proj/mysql-export/staging</pre><pre>bin/start.sh \<br>-- --template GCSTOSPANNER \<br>--templateProperty project.id=my-gcp-proj \<br>--templateProperty gcs.spanner.input.format=parquet \<br>--templateProperty gcs.spanner.input.location=gs://my-gcp-proj/mysql-export/export/table1_export/part*.parquet \<br>--templateProperty gcs.spanner.output.instance=spanner-inst \<br>--templateProperty gcs.spanner.output.database=spanner-db \<br>--templateProperty gcs.spanner.output.table=employee \<br>--templateProperty gcs.spanner.output.saveMode=Overwrite \<br>--templateProperty gcs.spanner.output.primaryKey=id \<br><strong>--templateProperty gcs.spanner.output.batchInsertSize=2000</strong></pre><p>4. <strong>No Lock Reads</strong><br>You may choose to instruct mysql (source db)to read uncommitted to make select faster. However, dirty read might not be acceptable in many cases, so not ideal approach.<br>Better approach is to use stationary clone instead.</p><p>5. <strong>Order of Table Import<br></strong>Ideally you should not create constraints, indexes, foreign key references on target database. Hence order of import should not cause any issue. Else, you will need to manually take care relationships when importing table data.</p><h3>Alternative Targets</h3><ol><li><strong>BigQuery<br></strong>Since database table is already exported into GCS, you can ingest data into BigQuery using <a href="https://github.com/GoogleCloudPlatform/dataproc-templates/blob/main/java/src/main/java/com/google/cloud/dataproc/templates/gcs/README.md"><strong>GCSToBigQuery</strong></a> template.</li><li><strong>Another database</strong> <br>Spark JDBC natively supports following databases MySQL / MariaDB, Postgresql, DB2 and Oracle. Using <a href="https://github.com/GoogleCloudPlatform/dataproc-templates/blob/main/java/src/main/java/com/google/cloud/dataproc/templates/gcs/README.md#3-gcs-to-jdbc"><strong>GCSToJDBC</strong></a> template (<a href="https://medium.com/google-cloud/importing-data-from-gcs-to-databases-via-jdbc-using-dataproc-serverless-7ed75eab93ba">blogpost</a>)you can ingest data into any of them.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bb32b1260268" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/fast-export-large-database-tables-using-gcp-serverless-dataproc-spark-bb32b1260268">Fast export large database tables — using GCP Serverless Dataproc (Spark)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cloud Spanner export query results using Dataproc Serverless]]></title>
            <link>https://medium.com/google-cloud/cloud-spanner-export-query-results-using-dataproc-serverless-6f2f65b583a4?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/6f2f65b583a4</guid>
            <category><![CDATA[dataproc]]></category>
            <category><![CDATA[serverless]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[cloud-spanner]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Thu, 31 Mar 2022 20:11:02 GMT</pubDate>
            <atom:updated>2022-09-21T21:57:53.976Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/771/1*OhDE3jKutut9gWzeXsq8_g.jpeg" /></figure><p>Cloud Spanner is fully managed relational database with unlimited scale, strong consistency, and up to 99.999% availability. In this post we will explore exporting data for a Spanner Table or SQL Query using Dataproc Serverless.</p><h3>Key Benefits</h3><ol><li>Dataproc Serverless is fully managed, serverless and autoscaling.</li><li><a href="https://github.com/GoogleCloudPlatform/dataproc-templates/tree/main/java/src/main/java/com/google/cloud/dataproc/templates/databases#executing-spanner-to-gcs-template"><strong>SpannerToGCS</strong></a> template is open source, fully customizable and ready to use for simple jobs.</li><li>Ephemeral, spark resources are release once job has ended.</li><li>You can export data in <strong>avro, parquet, csv and orc </strong>formats.</li></ol><h3>Simple Usage</h3><ol><li>Ensure you have enabled the subnet with Private Google Access, if you are going to use “default” VPC Network generated by GCP. Still you will need to enable private access as below. (<a href="https://cloud.google.com/dataproc-serverless/docs/concepts/network">Refer here</a> for details)</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/285/0*2OKjt1b9tkrYIcYp.png" /></figure><pre>gcloud compute networks subnets update default --region=us-central1 --enable-private-ip-google-access</pre><p>2. Create a GCS bucket and staging location for jar files.</p><p>3. Clone git repo in a cloud shell which is pre-installed with <a href="https://cloud.google.com/shell/docs/how-cloud-shell-works">various tools</a>. Alternatively use any machine pre-installed with JDK 8+, Maven and Git.</p><pre>git clone <a href="https://github.com/GoogleCloudPlatform/dataproc-templates.git">https://github.com/GoogleCloudPlatform/dataproc-templates.git</a></pre><pre>cd dataproc-templates/java</pre><p>4. Obtain authentication credentials (to submit the job).</p><pre>gcloud auth application-default login</pre><p>5. Execute SpannerToGCS template. <br>Eg:</p><pre>export GCP_PROJECT=my-test-gcp-proj1<br>export REGION=us-central1<br>export GCS_STAGING_LOCATION=gs://my-test-gcp-proj1/staging</pre><pre>bin/start.sh \<br>-- --template SPANNERTOGCS \<br>--templateProperty project.id=my-test-gcp-proj1 \<br>--templateProperty spanner.gcs.input.spanner.id=spann \<br>--templateProperty spanner.gcs.input.database.id=testdb \<br>--templateProperty &quot;spanner.gcs.input.table.id=(select trip_id, bikeid, duration_minutes from bikeshare)&quot; \<br>--templateProperty spanner.gcs.output.gcs.path=gs://my-test-gcp-proj1/bikeshare/spark_export/ \<br>--templateProperty spanner.gcs.output.gcs.saveMode=Overwrite</pre><p><strong>NOTE</strong>: It will ask you to enable Dataproc Api, if not enabled already.</p><h3>Setting up daily incremental data export</h3><p>End of the day/week/month exports are sometimes needed. On top of it, you may want to extract only incremental changes as exporting whole data at every iteration might be overkill.</p><p><strong>NOTE</strong>: Serverless Dataproc does not support realtime jobs. Hence, if the goal is to replicate changes realtime from Spanner to GCS, then you will need to look into Change Data Capture (CDC) alternatives.</p><ol><li>Using Cloud Spanner’s <a href="https://cloud.google.com/spanner/docs/commit-timestamp">commit timestamp</a> feature you can keep track rows inserted / updated (deletes cannot be captured).</li><li>Re-write sql query, submitted to spark, to capture changes since last execution. Eg:</li></ol><pre>&quot;spanner.gcs.input.table.id=(select trip_id, bikeid, duration_minutes from bikeshare where LastUpdateTime &gt;= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);&quot;</pre><p>3. Schedule the batch job.<br>GCP natively provides Cloud Scheduler + Cloud Function which can be used to submit spark batch jobs. Alternatively self managed softwares like linux cron tab, jenkins etc can be used as well.</p><h3>Setting additional spark properties</h3><p>In case you need to <a href="https://cloud.google.com/dataproc-serverless/docs/concepts/properties">specify spark properties</a> supported by Dataproc Serverless like adjust the number of drivers, cores, executors etc.</p><p>You can edit the <a href="https://github.com/GoogleCloudPlatform/dataproc-templates/blob/main/java/bin/start.sh#L50">OPT_PROPERTIES</a> values in start.sh file.</p><h3>Other alternatives</h3><ol><li>GUI based tool <a href="https://dbeaver.io/">DBeaver</a> to export sql query results into csv format.</li><li><a href="https://cloud.google.com/bigquery/docs/cloud-spanner-federated-queries">Cloud Spanner federated queries</a> to export data using BigQuery into Avro, CSV and other supported formats.</li><li>Dataflow template for <a href="https://cloud.google.com/dataflow/docs/guides/templates/provided-batch#cloud-spanner-to-cloud-storage-text">Spanner to Text</a> to export as csv files or <a href="https://cloud.google.com/spanner/docs/export#exporting_a_subset_of_tables">Spanner to Avro</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6f2f65b583a4" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/cloud-spanner-export-query-results-using-dataproc-serverless-6f2f65b583a4">Cloud Spanner export query results using Dataproc Serverless</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GCP — SQL Server AO-AG with Single Subnet]]></title>
            <link>https://medium.com/google-cloud/gcp-sql-server-ao-ag-with-single-subnet-30f9b98816ce?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/30f9b98816ce</guid>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[high-availability]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[always-on]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Fri, 09 Apr 2021 20:26:37 GMT</pubDate>
            <atom:updated>2021-04-13T07:50:53.068Z</atom:updated>
            <content:encoded><![CDATA[<h3>GCP — SQL Server AO-AG with Single Subnet</h3><h3>Introduction</h3><p>SQL Server Always On Availability Groups (AOAG) allows users to deploy highly available and automated failover with SQL Server databases. It is commonly deployed using multi-subnets in Google Cloud.</p><p>However, sometimes it might be desirable to deploy it in a single subnet configuration. Which could be because network design was planned only to have one subnet per region and adding new subnets might be difficult.</p><p><strong>Note:</strong> The <a href="https://cloud.google.com/solutions/deploy-multi-subnet-sql-server">multi-subnet deployment configuration</a> is simpler (less components) than single subnet configuration. Hence, prefer to use multi-subnet if you can.</p><h3>Understanding issue with single subnet</h3><p>Typically, availability groups within a single subnet needs to have floating ip. In GCP true <a href="https://cloud.google.com/community/tutorials/sql-server-ao-single-subnet">floating IPs is not available</a>. Therefore below solution is a workaround to mimic floating ip by using internal load balancer (ILB) component of GCP.</p><h3>Architecture</h3><p>The SQL Server nodes will be part of Windows Server Failover Cluster (WSFC) group, with always on enabled. AG-Listener’s IP coinciding with ILB (internal load balancer’s) IP. This means, any traffic sent to ILB will get routed to AG-Listener. This creates an illusion (for SQL Server) that Listener IP is floating between nodes. In reality that IP (eg. 10.128.0.20 in diagram below) is only attached to ILB and NOT to the SQL Server nodes. A health check is created on WSFC layer using which ILB is able to route traffic to the current primary.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*VQ2VSnhhjl1nnfKGElhwTg.png" /><figcaption>SQL Server Availability Group in single subnet</figcaption></figure><p>Sequence of resource deployment is important here, AG-Listener should be created first and ILB should be created after that only. When AG-Listener is created windows/sql server automatically checks if that IP is already in use or not. Hence, if ILB is created first AG-Listener creation will fail.</p><p>Health check on WSFC is created via below powershell configuration on WSFC Cluster. Example health check code as below. It enables port 59997 to accept connections on whichever sql server node is primary.</p><pre>$cluster_network_name = &#39;Cluster Network 1&#39;<br>$ip_resource_name = &#39;sql-ag_10.128.0.20&#39;<br>$load_balancer_ip = &#39;10.128.0.20&#39;<br>[int]$health_check_port = 59997<br>Get-ClusterResource $ip_resource_name |<br>  Set-ClusterParameter -Multiple @{ &#39;Address&#39;=$load_balancer_ip;<br>                                    &#39;ProbePort&#39;=$health_check_port;<br>                                    &#39;SubnetMask&#39;=&#39;255.255.240.0&#39;;<br>                                    &#39;Network&#39;=$cluster_network_name;<br>                                    &#39;EnableDhcp&#39;=0; }</pre><p>Corresponding to above below is health check configuration need on ILB layer to probe 59997 port and determine the primary node.</p><pre>gcloud compute health-checks create tcp sql-healthcheck \<br>    --check-interval=&quot;2s&quot; \<br>    --healthy-threshold=1 \<br>    --unhealthy-threshold=2 \<br>    --port=59997 \<br>    --request=10.128.0.20 \<br>    --timeout=&quot;1s&quot;</pre><h3>Detailed implementation steps</h3><p><a href="https://cloud.google.com/community/tutorials/sql-server-ao-single-subnet">https://cloud.google.com/community/tutorials/sql-server-ao-single-subnet</a> <br>I have published detailed step by step instructions as above.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=30f9b98816ce" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/gcp-sql-server-ao-ag-with-single-subnet-30f9b98816ce">GCP — SQL Server AO-AG with Single Subnet</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GCP — Deploying highly available databases with GKE]]></title>
            <link>https://medium.com/google-cloud/gcp-deploying-highly-available-databases-with-gke-d4979c7bb6a2?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/d4979c7bb6a2</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[gke]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[kubernetes]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Wed, 07 Apr 2021 18:37:42 GMT</pubDate>
            <atom:updated>2021-04-13T07:51:47.383Z</atom:updated>
            <content:encoded><![CDATA[<h3>GCP — Deploying highly available databases with GKE</h3><h4>GKE for Databases</h4><h3>Introduction</h3><p>Containerization of software applications has transformed ease of packaging software applications. On top of it Google Kubernetes Engine (GKE) has made management of those containers far more easier. In this post we will look at how stateful applications such as database(s) can take advantage of the same technology.</p><h3>Architecture Diagram</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9p2wSYw8MMyFrMk688KBCg.png" /><figcaption>Databases in GKE</figcaption></figure><p><strong>Identify database binary</strong><br>Above diagram depicts an highly available deployment for any* relational database (like MySQL, Postgresql, MSSQL-Linux) on GKE. You will be able to find ready to use container images for all of these in <a href="https://hub.docker.com/">Docker Hub</a>.</p><p><strong>Creating GKE Cluster<br></strong>GKE cluster hosting the database should have a bi-zonal node pool in a single region. This is to ensure that HA setup which is going to be based on <a href="https://cloud.google.com/compute/docs/disks/high-availability-regional-persistent-disk">Regional Persistent Disks</a> works. <br>GKE cluster should have nodes with spare capacity so that upon failure all the POD(s) could move to HA zone safely.</p><p><strong>Provision storage for database<br></strong>GKE offers Persistent Volume Claim (PVC) to store data. PVC can be created using either PD or PD SSD. Further, PVC can be either zonal or regional (bi-zonal). In this setup you should be using <a href="https://cloud.google.com/compute/docs/disks/high-availability-regional-persistent-disk">Regional PD/PD-SSD for HA</a> purposes. Regional PD/PD-SSD automatically maintains two copies of data (disk 01 and disk02 in diagram).</p><p><strong>GKE Service</strong><br>This is a facade which allows client applications to communicate to the database. While there can be various low level implementations, commonly an Internal Load Balancer (ILB) is generated behind the scenes for a GKE Service. Upon failover, gke service automatically re-configures to send the traffic on new gke pod.</p><h3>Reference Implementation</h3><p><a href="https://cloud.google.com/solutions/deploying-highly-available-postgresql-with-gke">Deploying highly available PostgreSQL with GKE</a><br>Read above article (written by me) for detailed instructions on this setup using postgresql. However, same can be adopted to use any* common relational database.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d4979c7bb6a2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/gcp-deploying-highly-available-databases-with-gke-d4979c7bb6a2">GCP — Deploying highly available databases with GKE</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GCP SQL Server using Stateful MIGs for HA and DR]]></title>
            <link>https://agarwalsh.medium.com/gcp-sql-server-using-stateful-migs-for-ha-and-dr-f053142cbc4?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/f053142cbc4</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[managed]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[gcp]]></category>
            <category><![CDATA[stateful]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Sun, 29 Nov 2020 19:49:45 GMT</pubDate>
            <atom:updated>2022-02-18T19:57:11.692Z</atom:updated>
            <content:encoded><![CDATA[<h3>[Deprecated] GCP SQL Server using Stateful MIGs for HA and DR</h3><h3><strong>Update: Stateful MIG does not failover automatically to another Availability Zone (AZ).</strong></h3><p><a href="https://cloud.google.com/compute/docs/instance-groups/configuring-stateful-migs#limitations">Review limitations page</a>.</p><h3>TLDR;</h3><p>Deploying traditional database(s) like SQL Server, MySQL, Postgresql etc in a Highly Available setup with minimal cost using GCP.</p><h3>Introduction</h3><p>GCP offers <a href="https://cloud.google.com/compute/docs/instance-groups/stateful-migs">stateful managed instance groups</a> which provide an easy and convenient way to deploy a highly available “stateful” service such as database(s). It can be useful when you got to deploy database on a VM and other managed services (like Cloud SQL) does not meet requirements.</p><p><strong>NOTE:</strong> The same strategy can be applied to other database(s) like Postgresql, MySQL, Oracle, DB2 etc too.</p><p><strong>Pros</strong></p><ul><li><strong>Fully automated</strong> node repairs</li><li>Manual regional HA (across two zones).</li><li><strong>In-expensive</strong>, no standby instances</li><li>Data <strong>replication at GCP Level</strong> (via Regional PD), no lag</li></ul><p><strong>Cons</strong></p><ul><li>Longer RTO. For windows ≥~8 mins and ≥~3 mins for linux. Due to time it take for first boot and prepare database instance</li><li>OS Disk cannot be stateful, hence its recreated in event of failover</li><li>Startup scripts needed to configure database (attach database, create users, log shipping etc)</li><li>DR replication is based on log-shipping, which will have lag and potential for data loss of transactions occurred after most recent log backup.</li></ul><h3>Architecture</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/636/1*S44S1dYlImLBdRLWHA0hbQ.jpeg" /><figcaption>Stateful-MIG Setup with HA and DR</figcaption></figure><p>As above diagram shows complete solution with HA and DR needs to have any two regions. Primary region can replicate to secondary region via log shipping (other options like db replication, AO/AG can work but they are complex to deploy).</p><p>Each region should have a Stateful MIG (S-MIG)setup with regional persistent disk(s) being maintained as stateful device. At any given point in time, there will be exactly one VM in this S-MIG.</p><p>In the event of failure, S-MIG’s health check would start failing which will trigger automatic node repair, unless availability zone has outage. S-MIG will delete existing node and create a new node using the os boot disk as supplied with instance template. It will also attach the stateful disk(s) to the newly created vm. Custom startup scripts will need to be create, such that it will prepare database for serving traffic. This could include:</p><ol><li>Join active directory (if applicable)</li><li><a href="https://docs.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15">Attach database</a> residing on stateful disks</li><li>Create user logins, sql agent jobs (or restore previously backed up system databases).</li></ol><h3>Deployment</h3><p>It is important to note that Stateful MIG using <strong>Single Zone PD</strong> can be created via using GCP Cloud Console. However, at time of writing (Dec 2020), Dual Zone PD (aka Regional PD) based setup cannot be done using the gui. You can use below sequence of gcloud commands to deploy the same.</p><p>a. <a href="https://cloud.google.com/compute/docs/disks/regional-persistent-disk#repd_create_disk">Create regional pd</a></p><pre>gcloud compute disks create sql-data-repd \<br>--type=pd-standard \<br>--size=200GB \<br>--region=us-central1 \<br>--replica-zones=us-central1-c,us-central1-a</pre><p>b. <a href="https://cloud.google.com/compute/docs/instance-templates/create-instance-templates">Create a instance template</a></p><pre>gcloud compute instance-templates create sql-smig-template-1 \<br>--machine-type=e2-standard-4 \<br>--scopes=<a href="https://www.googleapis.com/auth/cloud-platform">https://www.googleapis.com/auth/cloud-platform</a> \<br>--image=sql-2017-standard-windows-2016-dc-v20201110 \<br>--image-project=windows-sql-cloud \<br>--boot-disk-size=50GB \<br>--boot-disk-type=pd-standard</pre><p>c. <a href="https://cloud.google.com/load-balancing/docs/health-checks">Create health check</a> also respective <a href="https://cloud.google.com/load-balancing/docs/health-checks#fw-rule">firewall rules</a> must be created (if not already).</p><pre>gcloud compute health-checks create tcp sql-port-hc-1 \<br>--port=1433 \<br>--proxy-header=NONE \<br>--check-interval=2 \<br>--timeout=2 \<br>--unhealthy-threshold=2 \<br>--healthy-threshold=2</pre><p>If needed create firewall rule:</p><pre>gcloud compute firewall-rules create fw-allow-health-checks \<br>    --action=ALLOW \<br>    --direction=INGRESS \<br>    --source-ranges=35.191.0.0/16,130.211.0.0/22 \<br>    --rules=tcp</pre><p>d. Create S-MIG with 0 instances. Also, using zones exactly matching Regional PD and Health Check create previously.</p><pre>gcloud compute instance-groups managed create sql-smig-1 \<br>--base-instance-name=sql-smig-1 \<br>--template=sql-smig-template-1 \<br>--size=0 \<br>--zones=us-central1-c,us-central1-a \<br>--instance-redistribution-type=NONE \<br>--health-check=sql-port-hc-1 \<br>--initial-delay=400</pre><p>e. Create an instance inside S-MIG. <br><strong>Note</strong>: This step cannot be done via GUI.</p><pre>export projectid=$(gcloud config get-value project)</pre><pre>gcloud compute instance-groups managed create-instance sql-smig-1 \<br>--instance=smig-sql-inst-1 \<br>--stateful-disk device-name=sql-data-repd,auto-delete=never,source=projects/$projectid/regions/us-central1/disks/sql-data-repd,mode=rw \<br>--region=us-central1</pre><p>This will create an instance inside S-MIG. Now you can RDP into this VM to format the attached stateful disk and give it drive letter. Formatting needs to be done only first time. <br>Next, create database with data location on this attached disk.</p><p>You will also need to create a startup script with commands to attach database upon automatic node repair. Then add this startup script to an instance template as well.</p><p>f. Update the S-MIG once to reconfigure and start automatic node repairs.</p><pre>gcloud compute instance-groups managed update sql-smig-1 \<br>--region us-central1</pre><p>g. Ideally, you should create an internal load balancer (ILB) to communicate with SQL Server. This is to avoid IP changes upon node repairs.</p><h3>Testing Failover</h3><p>Simply stop the SQL Server service and in a few seconds you would observe existing instance is replaced by a new one.</p><h3>Summary</h3><p>This setup is great if you wish to save on your infrastructure (and maybe licensing) costs while still having a multi zone data center setup. This is because you are not running any standby machines. Also, there is no data loss as data is being written simultaneously in two zone. <br>Only drawback is, you would need to tolerate recovery time of few minutes.</p><p>You can add similar DR too if you want to further increase availability.</p><h3>Thanks</h3><p>Thanks for reading my article. I would love to know your thoughts. Please let me know your suggestions and how can I improve my writing.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f053142cbc4" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GCP Solving MySQL HA Replication Lag]]></title>
            <link>https://medium.com/google-cloud/gcp-solving-mysql-ha-replication-lag-4f005779a32c?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/4f005779a32c</guid>
            <category><![CDATA[cloud-computing]]></category>
            <category><![CDATA[lag]]></category>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[gcp]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Mon, 30 Dec 2019 23:38:33 GMT</pubDate>
            <atom:updated>2020-01-02T17:08:00.414Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>TL;DR</strong> : This solutions focuses on usage of multi zonal disks (called regional disks) for achieving durable MySQL HA Replication. This is GCE based implementation of <a href="https://cloud.google.com/sql/docs/mysql/high-availability">CloudSQL’s replication technique</a>.</p><h3>Problem Statement</h3><p>Public clouds such as GCP start showing replication lag for MySQL Replica instances when durability is ON (i.e. sync_binlog = 1) and average load is &gt;~300 tps (transactions per second). However, bare metal hardware does not shows the lag at ~300 tps limits.<br><strong>NOTE</strong>: 300 TPS limit can vary based on transaction sizes and types, single or multi-threaded replication etc.</p><p>Secondary goal — Achieve failover within 30 secs.</p><h3>Assumptions</h3><p>Mysql Version : 5.7.10+<br>Replication type : Asynchronous <br>Durability : Required (i.e. sync_binlog = 1)<br>Failover Time : Under 30 secs</p><h3>Understanding the Problem</h3><p><strong>Why is durability needed ?</strong></p><p>DBA’s need to have durability while replicating to ensure consistency and robustness of data in replica. In addition, it makes failover faster and easier as no changes needs to be made on replica, except telling it to stop replicating.</p><p><strong>What causes lag in durable replication ?</strong></p><p>Root cause of the replication lag is due to latency added with virtualization.</p><p>In addition it is further highlighted due to ineffectiveness of mysql replicating when multi-threaded. MySQL is not able to use multiple threads well enough to distribute replication load between threads and often resorts to one single thread doing most of the work.</p><p><strong>Why is MASTER not lagging or high latency ?</strong></p><p>Master is receiving traffic via multiple connections (threads) and are written to database organically. In other words MySQL working as master is effective using multiple threads, which is not the case while its replicating.</p><p>Unless Master is in semi-sync replication setup, it will likely not show high latency. Even semi-sync mode also only ensure that transaction is committed to at-least just 1 replica. Therefore, if you are replicating from on-prem master to (1) on-prem replica. (2) gcp replica. Master will not show additional latency.</p><h3>Solution to mysql replication lag</h3><p>In the above section, we have established that replication lag in MySQL happens due to (1) latency due to virtualization (2) in-effectiveness of MySQL Software while replicating</p><p>To solve this, we can eliminate (2) by using GCP’s <a href="https://cloud.google.com/compute/docs/disks/#repds">regional persistent disks</a>. These disks are bi-zonal in a given region. So anything written on it is synchronously written in both zones simultaneously.</p><p><strong>Deployment diagram</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/815/1*WQTThK2znE9UtCkxOJbygg.png" /><figcaption>MySQL Master (HA) with regional ssd</figcaption></figure><ol><li>GCP Internal Load Balancer is created to send all the traffic to umig1 (unmanaged instance group 1) and only in failover send to umig2.</li><li>VMs DB zn1 and DB zn2 are installed with mysql using same boot image. (more details in next section).</li><li>Each of VM is added into separate unmanaged instance group (umig).</li><li>Regional SSD is disk used for data and logs writing by mysql. Only one VM can attach it in Read/Write mode at a given time.</li></ol><h3>Details of setting up regional disks based approach</h3><ol><li>Create golden boot disk.<br>a. Create a new VM and ssh into VM<br>b. Install the software(s) mysql (and any monitoring softwares, if needed). c. Configure mysql in my.cnf file to use mounted disk path.<br>datadir = /mnt/attached-disk/msql/data<br>log-bin = /mnt/attached-disk/msql/log/repl<br>d. Disable mysql service autostart<br>sudo systemctl disable mysqld<br>e. Shutdown VM<br>f. Create image of boot disk</li><li>Create regional pd-ssd.</li><li>Create the primary and standby VMs with metadata “attached_disk_name=&lt;name of regional pd-ssd&gt;”. <br>Note: Allow all the compute engine API permissions to the VM.</li><li>Login into both the above VMs and copy failover.sh script (below).</li><li>Execute above failover.sh in primary instance to start serving mysql.</li></ol><p><strong>failover.sh</strong></p><pre>#### Variables<br>export other_vm_name=&quot;&lt;other instance name primary/standby&gt;&quot;<br>export other_vm_zone=&quot;us-central1-a&quot;<br>export mysql_root_password=&quot;&lt;password&gt;&quot;<br>#### Variables END</pre><pre>echo $(date) &quot; starting failover #############&quot;<br>gcloud compute ssh $other_vm_name --zone $other_vm_zone --quiet -- -o ConnectTimeout=10 -t &quot;sudo systemctl stop mysqld&quot;<br>echo $(date) &quot; stopped current master ##########&quot;</pre><pre>export inst_zone=$(curl &quot;http://metadata.google.internal/computeMetadata/v1/instance/zone&quot; -H &quot;Metadata-Flavor: Google&quot; |  awk &#39;{split($0,a,&quot;/&quot;); print a[4]}&#39;)<br>export inst_name=$(curl &quot;http://metadata.google.internal/computeMetadata/v1/instance/name&quot; -H &quot;Metadata-Flavor: Google&quot;)<br>export attached_disk_name=$(curl &quot;http://metadata.google.internal/computeMetadata/v1/instance/attributes/attached_disk_name&quot; -H &quot;Metadata-Flavor: Google&quot;)</pre><pre>gcloud compute instances attach-disk $inst_name --zone &quot;$inst_zone&quot; --disk &quot;$attached_disk_name&quot; --device-name &quot;$attached_disk_name&quot; --mode rw --force-attach --disk-scope &quot;regional&quot;</pre><pre>echo $(date) &quot; attached regional disks ##########&quot;</pre><pre>sudo mount -o discard,defaults /dev/sdb /mnt/attached-disk</pre><pre>echo $(date) &quot; mounted regional disks ##########&quot;</pre><pre>sudo systemctl start mysqld</pre><pre>echo $(date) &quot; started mysql ##########&quot;<br>echo $(date) &quot; failover complete ##########&quot;</pre><pre>mysql -uroot -p$mysql_root_password -e &quot;show master status&quot;</pre><h3>Executing failover</h3><p>Execute failover.sh in standby vm. Load balancer will auto detect and switch over traffic.</p><h3>Executing fail-back</h3><p>Execute failover.sh from primary vm. Load balancer will auto detect and switch over traffic.</p><h3>Multi Region Deployment</h3><p>Similarly, multi region replication can be deployed so that both DC and DR are Highly Available. Only difference being, replication from DC to DR has to be non-durable to catch up replication lag.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/872/1*YYaVOxgUF-6QasTylwpZBg.png" /></figure><p>A few points to consider in this architecture:</p><ol><li>Migrating MySQL and failover of instances is the focus of this document. Hence the diagram shows all other aspects in rather simple terms.</li><li>Each database vm is in a different zone i.e. ZN[1–4]</li><li>The picture clearly shows Primary and Standby in both the regions. There is no replication between Primary DB and Standby.</li><li>Standby will have mysqld service stopped. Standby will not be attached with regional ssd(s), until failure of primary node (in respective region) happens.</li><li>Each Load Balancer should send traffic to standby “Only in case of failover”.</li><li>DR Primary (zn3) node has non-durable replication from DC Primary (zn1).</li><li>Unidirectional replication from Region1 — — to — → Region2. Since region2’s mysql is non-durable.</li><li>[Optional] Application traffic is routed using a dns service.</li><li>[Optional] Additional read replicas if configured, will have non-durable replication with zonal ssd.</li></ol><h3>Zonal Failover &amp; Failback with Regional PD-SSD</h3><p>In the event primary master (DB zn1 Primary), the topology will be updated as below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*sm22Ofqmwcfyg6jh1VKjJg.png" /></figure><p>Once failure is detected a script (failover.sh as above) will need to perform following steps:</p><ol><li>Attempt for graceful failure. Try stopping primary database (with timeout of 10 secs).</li><li>Force attach the regional pd-ssd to standby vm.</li><li>Start the mysql service on standby.</li><li>Change Master on DR Primary, to continue the replication in DR.</li></ol><p>Once steps 1,2 &amp; 3 are done. Ideally, Load Balancer will auto detect that standby instance is healthy and it will start sending all the traffic to standby.</p><h3>Adding Multiple Read Replicas</h3><p>Multiple read replicas (for mysql performance) can be further added to same setup. By adding independent mysql nodes replicating non-durably.</p><h3>Alternative Solutions</h3><p>While on our journey to solve this we found following more ways to solve it. But all of them are achieved via tweaking mysql (application layer) replication settings and therefore each of it have some short comings.</p><ol><li><strong>Semi Synchronous replication:</strong> It might solve replication lag at cost of increasing latency of master. Which is often not desirable.</li><li><strong>Turn off </strong><a href="https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_log_slave_updates"><strong>log_slave_updates</strong></a><strong>:</strong> Often replication lag is elevated when replicas also generate bin logs. Getting rid of bin logs and fasten replication until 700 tps limit (nearly double). This can be turned off using (log_slave_updates = OFF) in my.cnf. Downside being, chain replication cannot be done. Therefore say any read replicas in DR will also have to replicate from primary DC master.</li><li><strong>Multi threaded replication:</strong> We tried and it showed great results with load generated via sysbench. However, with real world load it did not performed better enough to completely catch up replication lag.</li></ol><h3>Conclusion</h3><p>Using regional persistent disks is great option for creating high availability database infrastructure. Giving a failover time of &lt; 30secs.</p><p>Thanks for reading. Please reply to this post and let me know, if you would like to see terraform or shell scripts to spin up whole of this infrastructure. I can write a part-2 giving complete end to end code for this.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4f005779a32c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/gcp-solving-mysql-ha-replication-lag-4f005779a32c">GCP Solving MySQL HA Replication Lag</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GCP  PostgreSQL — Compute Engine Snapshot Based Replication (Eg: Production to Development)]]></title>
            <link>https://medium.com/google-cloud/gcp-postgresql-compute-engine-snapshot-based-replication-eg-production-to-development-d90eb0477e90?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/d90eb0477e90</guid>
            <category><![CDATA[gce]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[gce-db-replication]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[replication]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Thu, 18 Jul 2019 16:39:14 GMT</pubDate>
            <atom:updated>2019-07-19T16:34:05.385Z</atom:updated>
            <content:encoded><![CDATA[<h3>GCP PostgreSQL — Compute Engine Snapshot Based Replication (Eg: Production to Development)</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d8WezOUjUDt5lwAVA0FhDA.jpeg" /></figure><h3>Quick Summary (TL;DR)</h3><p>In this post, will cover a real life walkthrough and recommendations of GCE Snapshot based replication on Google Cloud Platform. This solution is relevant for database administers and application teams, with reliance on databases hosted on GCE VMs. This solution leverages cloning disks via snapshot. Same concept can be applicable for other databases too.</p><ol><li>Create snapshot of production database disk</li><li>Create disk image from snapshot</li><li>Create instance template</li><li>Make Rolling Update to managed instance group (in development project) with new instance template.</li></ol><h3>Introduction</h3><p>Sometimes due to security and compliance requirements, development teams are not allowed to have direct access to production database. However, in order to debug specific production issues or create adhoc reports development teams may need to be able to access data.</p><p>This sometimes results in raising tickets to operations for taking database backups and restores in development environment. The process is often slow and frustrating. Also often taking a logical backup often requires downtime.</p><p>Experience and productivity can be greatly improved if scheduled script or pipeline is created which refreshes data every few hours. While there can be other solutions like streaming replication, logical backup &amp; restore, this is an alternative solution.</p><h4><strong>Advantages</strong></h4><ol><li>Improved security by giving developers a separate copy of production data</li><li>Time saved by automation, no more waiting for tickets</li><li>No production database downtime required</li><li>Faster bug fixes and development cycles</li></ol><h3>Deployment Layout</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2W92T4KKoiE84Xk4F97Upg.jpeg" /></figure><p>The above deployment diagram shows a typical setup, which is lay out in following way:</p><ol><li>Production database VMs are in separate prod project.</li><li>May or may not have separate VPC networks for prod and non-prod.</li><li>Production PostgreSql’s data disk is periodically snapshotted.</li><li>On non-prod environment, Managed Instance Group (MIG) is created. And new data rollout happens via rolling update feature.</li></ol><h3>Example Implementation</h3><p>For simplicity, I will be using:</p><ol><li>One single project containing both source database (assume prod db) and replica (for non-production use).</li><li>The postgres database vm will have only one disk for boot, data and logs. In a real world scenario it is better to replicate only data disk to non-prod so that database users, log and configurations (pg_hba.conf and postgres.conf etc) are not brought to non production environment.</li><li>Managed Instance Group to do a rolling update with new database snapshot.</li><li>Create load balancer for Managed instance group created in above step.</li><li>An orchestrator VM with scheduled cron job to trigger a pipeline. It will execute in following sequence of commands:<br>a. Create source disk snapshot<br>b. Create a new disk image from snapshot.<br>c. Create new instance template with new disk image.<br>d. Do a rolling update with new snapshot on MIG.</li></ol><h4>Let’s get started</h4><p>If you have not used Google Cloud, you can head on over to <a href="https://console.cloud.google.com/">https://console.cloud.google.com</a> and register for a free account starting with $300 in credit.</p><h4>Setup Source Database and Managed Instance Group</h4><ol><li>Open cloud shell</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/0*W0CDGYhKx_k4tNtW.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/0*VcNGvqDrnbfxcH4s.png" /></figure><p>2. Create Firewall rule for postgres instance and create a new VM instance.</p><p>Create Firewall (optional with default VPC Network)<br>Execute below command to create firewall rule which allows incoming connections on port 5432</p><pre>gcloud compute firewall-rules create pg-firewall-open --direction=INGRESS --priority=1000 --network=default --action=ALLOW --rules=tcp:5432 --source-ranges=0.0.0.0/0</pre><p>Create VM<br>VM instance can be created either by UI or as gcloud command as below. <br>Alternatively execute below command from cloud shell.</p><pre>gcloud compute instances create postgres-prod --zone=us-west1-b --machine-type=n1-standard-1 --subnet=default  --scopes=<a href="https://www.googleapis.com/auth/cloud-platform">https://www.googleapis.com/auth/cloud-platform</a> --image-family=ubuntu-1604-lts --image-project=ubuntu-os-cloud --boot-disk-size=100GB --boot-disk-type=pd-standard --boot-disk-device-name=postgres-prod</pre><p>3. SSH to the VM and install and configure PostgreSQL database.</p><p>Install PostgreSQL</p><pre>gcloud compute ssh postgres-prod --zone=us-west1-b<br>sudo apt update<br>sudo apt install postgresql postgresql-contrib</pre><p>Configure PostgreSQL to allow remote connection</p><p>By default PostgreSQL is configured to be bound to “localhost”.<br>Configuring postgresql.conf, I usually do a search to find it.</p><pre>$ find \ -name &quot;postgresql.conf&quot; /etc/postgresql/9.5/main/postgresql.conf</pre><p>Open postgresql.conf file and replace line</p><pre>listen_addresses = &#39;localhost&#39;</pre><p>with</p><pre>listen_addresses = &#39;*&#39;</pre><p>Configuring pg_hba.conf</p><p>Similarly find and open pg_hba.conf and add following entry at the very end.</p><pre>host    all             all      0.0.0.0/0     md5<br>host    all             all      ::/0          md5</pre><p>4. Create sample database.</p><pre>sudo -u postgres psql</pre><p>Once you are in the psql shell, create a new database.</p><pre>create database prod_test_1;</pre><p>Exit psql shell</p><pre>\q</pre><p>Exit postgres-prod VM</p><pre>exit</pre><p>5. Create disk snapshot.</p><p>Name snapshot as: postgres-prod-initial-snapshot</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uiX27S5bQHjCI5bAGyZw7A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*RCWgR059bS6XcwEuVuQCtQ.png" /></figure><p>6. Create disk image from snapshot as below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/728/1*bGLYe65RLxF2fl46NzQv1A.png" /></figure><p>7. Create a instance template with name postgres-prd-replica-initial-template as below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/731/1*i5wqISQhNIYSJeavcZhPrQ.png" /></figure><p>8. Create a managed instance group using above template with name postgres-prd-replica-mig. Compute Engine -&gt; Instance Groups -&gt; Create Instance Group.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/852/1*Af53vmvX5e2Y3piCxC5bPQ.png" /></figure><p>9. Now we shall create a load balancer. So that it provides one non changing IP which could be used to connect to the database. Network Services -&gt; Load Balancing -&gt; Create Load Balancer -&gt; TCP Load Balacing -&gt; Start Configuration</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/704/1*2gEOiwKAkdV70_Pr4i2-eQ.png" /></figure><p>Here we will configure load balancer. Name it as postgres-prod-replica-lb and configure as shown in screen shot below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/865/1*bbvBSXOSs7mKr83r_OKMZw.png" /></figure><p>Configure Health Check as below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/466/1*fkpPGmMcFIl6ssj9-xAvuA.png" /></figure><p>Configure front end as below and click Create button.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/861/1*I9SKQViDYHy_T26-sObMtA.png" /></figure><p>This should create a load balancer with an internal ip which can be used by developers/applications to connect.</p><p>Also you will need to create a health check firewall rule to enable the same.</p><pre>gcloud compute firewall-rules create allow-hc --direction=INGRESS --priority=1000 --network=default --action=ALLOW --rules=tcp --source-ranges=130.211.0.0/22,35.191.0.0/16</pre><p>Users shall connect to this load balancer to access prod replica database.</p><h3>Manually updating database snapshot replica</h3><p>To update latest copy of data, all we need to do is as below. <br>disk snapshot -&gt; image -&gt; instance template -&gt; rolling update</p><ol><li>Create disk snapshot (using UI as before or gcloud as below)</li></ol><pre>gcloud compute disks snapshot postgres-prod --snapshot-names=postgres-prod-manual1-snapshot --zone=us-west1-b</pre><p>2. Create disk image (using UI as before or gcloud as below)</p><pre>gcloud compute images create postgres-prod-manual1-image --family=postgres-prod --source-snapshot=postgres-prod-manual1-snapshot</pre><p>3. Create instance template (using UI as before or gcloud as below)</p><pre>gcloud compute instance-templates create postgres-prd-replica-manual1-template --machine-type=n1-standard-1 --scopes=https://www.googleapis.com/auth/cloud-platform --image=postgres-prod-manual1-image --boot-disk-size=100GB --boot-disk-type=pd-standard</pre><p>4. Rolling update to managed instance group</p><p>Go to Compute Engine -&gt; Instance Groups -&gt; postgres-prd-replica-mig -&gt; Rolling Update</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dgP1rS4AVyMEJyE2JBir9w.png" /></figure><p>Then select the new template, change the maximum unavailable to 0 and click update. This will create a new replica instance first and then remove the old one.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/716/1*m9tvYCNFZEx2h3VO1LafWA.png" /></figure><p>Alternatively this can be achieved with following gcloud command.</p><pre>gcloud beta compute instance-groups managed rolling-action start-update postgres-prd-replica-mig --version template=postgres-prd-replica-manual1-template --max-unavailable 0 --zone us-west1-b</pre><h3>Orchestrating periodic automatic updates</h3><p>It can be easily done using any of various third party deployment tools (like jenkins). However, here I will simply create a shell script and schedule it as cron tab.</p><p>I will be generating a uuid (using uuidgen command) to supply unique name to all resources. Also I have shortened name of resources as they were exceeding gcp character limits.</p><p>Create a vm with name prd-replica-updater</p><pre>gcloud compute instances create prd-replica-updater --zone=us-west1-b --machine-type=n1-standard-1 --subnet=default  --scopes=<a href="https://www.googleapis.com/auth/cloud-platform">https://www.googleapis.com/auth/cloud-platform</a> --image-family=ubuntu-1604-lts --image-project=ubuntu-os-cloud --boot-disk-size=10GB --boot-disk-type=pd-standard --boot-disk-device-name=prd-replica-updater</pre><p>SSH into VM and create shell script with name prd-replica-update.sh as below</p><pre># uuid string<br>rnd=$(uuidgen)</pre><pre># create snapshot<br>gcloud compute disks snapshot postgres-prod --snapshot-names=pg-prd-auto-$rnd-snap --zone=us-west1-b</pre><pre># create image<br>gcloud compute images create pg-prd-auto-$rnd-image --family=postgres-prod --source-snapshot=pg-prd-auto-$rnd-snap</pre><pre># create instance template<br>gcloud compute instance-templates create pg-prd-auto-$rnd-template --machine-type=n1-standard-1 --scopes=https://www.googleapis.com/auth/cloud-platform --image=pg-prd-auto-$rnd-image --boot-disk-size=100GB --boot-disk-type=pd-standard</pre><pre># do rolling update<br>gcloud beta compute instance-groups managed rolling-action start-update postgres-prd-replica-mig --version template=pg-prd-auto-$rnd-template --max-unavailable 0 --zone us-west1-b</pre><p>Above script can be scheduled using crontab and thus automating database updates.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d90eb0477e90" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/gcp-postgresql-compute-engine-snapshot-based-replication-eg-production-to-development-d90eb0477e90">GCP  PostgreSQL — Compute Engine Snapshot Based Replication (Eg: Production to Development)</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GCP VPC SC with Shared VPC Network]]></title>
            <link>https://medium.com/google-cloud/gcp-vpc-sc-with-shared-vpc-network-526f85377cdd?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/526f85377cdd</guid>
            <category><![CDATA[secure-gcs]]></category>
            <category><![CDATA[cloud-computing]]></category>
            <category><![CDATA[secure-bq]]></category>
            <category><![CDATA[data-exfiltration]]></category>
            <category><![CDATA[shared-vpc]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Wed, 26 Jun 2019 17:18:18 GMT</pubDate>
            <atom:updated>2019-06-27T19:08:55.843Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/301/1*7MpDhFKjH0jWt9LD9EhodA.png" /></figure><h3>Quick Summary (TL;DR)</h3><p>This article describes the limitation and workaround solution of using VPC Service Controls (VPC-SC) with Shared VPC in Google Cloud Platform.</p><p><strong>Issue</strong> arises from the fact that VPC-SC observes service project VMs’ as resources of Shared VPC (instead of service project resources). On one hand if VPC-SC is enabled using only service project, then its own VMs are treated as resources outside perimeter. On other hand, if Shared VPC is included to same VPC-SC along with service project then VMs of other service projects using that Shared VPC are also included in same perimeter. Thus, other service project VMs are not able to connect to resources. These issues are explained in more detail in sections below.</p><p><strong>Workaround</strong> solution is by creating VPC-SC only on service project and then stitch GCP VMs’ access by adding public IPs to Access Context Manager. More details in sections below.</p><h3>What is VPC-SC (VPC Service Control)</h3><p>In layman language, it is firewall for GCS, BigQuery, Bigtable and other supported services. It gives information security teams peace of mind that no-one can access data contained in these services from unauthorized networks. In other words it disables data exfiltration. Google Cloud documentation <a href="https://cloud.google.com/vpc-service-controls/">here</a>.</p><h3>What is Shared VPC Network</h3><p>Shared VPC as its name suggests is a VPC Network created within a GCP project and is shared to multiple other projects. The project containing Shared VPC is called Shared VPC Host and project using its network are called Service Project. GCP documentation <a href="https://cloud.google.com/vpc/docs/shared-vpc">here</a>.</p><h3>Issue in implementing VPC-SC with Shared VPC</h3><p>Often with a Shared VPC, multiple independent workloads/applications used same Shared VPC. While one of these teams require VPC-SC but other teams don’t.</p><p>Below diagram describes how VPC-SC observes the current setup.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*49z87biRvEcrApoSPrdGZA.jpeg" /></figure><p>Now in above diagram, let us suppose only Project A has sensitive data in GCS and BQ and therefore VPC-SC is required. This can be achieved in following ways:</p><ol><li>VPC SC perimeter with Shared VPC Host project + Service Project A + Access Context Manager (ACM) with public IPs of external network. Drawback is Service Project B VMs will not be able to talk to GCS or BQ in its own project.</li><li>VPC-SC with Shared VPC Host + All Service Projects + ACM of external network. Drawback is some products are not compatible with VPC-SC (documented <a href="https://cloud.google.com/vpc-service-controls/docs/supported-products">here</a>) like AppEngine, Cloud Functions, Dataflow etc. This can restrict other teams who do not require VPC-SC but still want to use serverless services. Second drawback is, lack of multiple VPC-SC for each team. This increases attack surface as breach in perimeter can cause more damage.</li><li>VPC-SC with Service Project A + ACM of external network + ACM of shared network VMs’ public IPs. While this fits most of the use cases, drawback is maintaining list of VM’s public IPs in ACM as new VMs can spun up anytime. (we will fix this drawback in solution).</li></ol><h3>Solution for using VPC-SC with Shared VPC</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lZuWA_UGYHcWET1GPSCWpQ.jpeg" /></figure><p>One of the simple solution is to create VPC-SC with Service Project A + ACM with IPs of external network + ACM with public IPs of shared network VMs. However to make it practically useful we need to mitigate its drawback which is need to maintain list of VM’s public IPs in ACM as new VMs can spun up anytime. Workaround for above drawback is use NAT Gateways.</p><p>Using ACM we can whitelist public IPs which are allowed to access VPC-SC perimeter. Thus, we create an ACM list containing public IPs of NAT. Thus any VM hosted on shared network will be allowed communicate with resources inside VPC-SC.</p><p>Broad steps to implement this solution are:</p><ol><li>Create NAT Gateways in Shared VPC project using compute engine VMs. Assign static external IPs to NAT VMs. Create required routes. Step by step implementation for NAT Gateway is described <a href="https://cloud.google.com/vpc/docs/special-configurations#natgateway">here</a>. <br><strong>NOTE</strong>: Cloud NAT does not works along with this workaround yet. (authoring date: June 25, 2019).</li><li>None of the service project VMs should have external IPs instead they should be using NAT Gateway only.</li><li>Create Access Context Manger (ACM) for whitelisting NAT IPs of shared network (plus public IPs of any other authorized network).</li><li>Create VPC-SC only with Service Project and link it to ACM above.</li></ol><h4>What is NAT Gateway</h4><p>It allows us to use one external IP address to send traffic from multiple virtual machine instances but only expose a single virtual machine to the Internet. Other benefits of NAT Gateway is ability to easily monitor traffic escaping to internet.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=526f85377cdd" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/gcp-vpc-sc-with-shared-vpc-network-526f85377cdd">GCP VPC SC with Shared VPC Network</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Scheduled Mirror/Sync SFTP to GCS]]></title>
            <link>https://medium.com/google-cloud/scheduled-mirror-sync-sftp-to-gcs-b167d0eb487a?source=rss-e4d65879b270------2</link>
            <guid isPermaLink="false">https://medium.com/p/b167d0eb487a</guid>
            <category><![CDATA[gcp]]></category>
            <category><![CDATA[ubuntu]]></category>
            <category><![CDATA[sftp]]></category>
            <dc:creator><![CDATA[Shashank Agarwal]]></dc:creator>
            <pubDate>Mon, 25 Mar 2019 18:22:29 GMT</pubDate>
            <atom:updated>2019-04-02T15:11:41.315Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Context</strong></h3><p>Applications often require loosely coupled file sharing systems. Sometimes data files need to be shared across companies or teams within company which do not share storage infrastructure. SFTP has been common interface in such cases.</p><h3><strong>Why Sync files to Google Cloud Storage ?</strong></h3><p>While applications on GCP may directly read from SFTP server. But in several cases syncing the files to GCS could be preferred approach. Reasons why you would consider setting up sync :</p><ul><li>Consistent files source for application</li><li>Fine grained access control list (ACL)</li><li>Easy life cycle management of files</li><li>Mitigate network flakiness</li></ul><h3>Solution overview</h3><ul><li>gcsfuse to mount bucket on VM</li><li>schedule lftp command to mirror files periodically</li><li>No Coding needed, using only tools.</li></ul><h3>Let’s get started</h3><p>If you have not used Google Cloud, you can head on over to <a href="https://console.cloud.google.com/">https://console.cloud.google.com</a> and register for a free account starting with $300 in credit.</p><ol><li>Open cloud shell.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*W0CDGYhKx_k4tNtW.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VcNGvqDrnbfxcH4s.png" /></figure><p>2. Create a new bucket with unique name.</p><pre>gcs_bucket=”sftp_bucket_”$(python -c “import uuid; print str(uuid.uuid4())”)<br>gsutil mb gs://$gcs_bucket</pre><p>3. Create a new VM instance</p><p>VM instance can be created either by UI or as gcloud command as below. <br>Incase of UI, change access scope to Access scopes -&gt; Storage -&gt; Full<br>Alternatively execute below command from cloud shell.</p><pre>gcloud compute instances create sftp-test --zone=us-central1-c --machine-type=n1-standard-1 --subnet=default  --scopes=<a href="https://www.googleapis.com/auth/cloud-platform">https://www.googleapis.com/auth/cloud-platform</a> --image-family=ubuntu-1604-lts --image-project=ubuntu-os-cloud --boot-disk-size=10GB --boot-disk-type=pd-standard --boot-disk-device-name=sftp-test</pre><p>Above command will create VM in default network and subnet.</p><p>4. SSH into VM</p><p>Type below command in cloud shell to SSH</p><pre>gcloud compute ssh sftp-test</pre><p>5. Install lftp on VM</p><p>Lftp utility is swiss army knife of file downloading, it works with lot of protocols including sftp.</p><pre>sudo apt-get install lftp</pre><p>6. Install gcsfuse on VM</p><pre>export GCSFUSE_REPO=gcsfuse-`lsb_release -c -s`<br>echo &quot;deb http://packages.cloud.google.com/apt $GCSFUSE_REPO main&quot; | sudo tee /etc/apt/sources.list.d/gcsfuse.list<br>curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo apt-key add -</pre><pre>sudo apt-get update<br>sudo apt-get install gcsfuse</pre><p>7. Mount bucket on VM</p><pre>mkdir ~<em>/gcsBucket</em><br>gcsfuse <em>&lt;replace-by-bucket-name&gt;</em> ~<em>/gcsBucket</em></pre><p>Replace &lt;replace-by-bucket-name&gt; by bucket name (created in step 2). Do not use gs://</p><p>8. Schedule lftp command</p><p>Assuming sftp authenticates with username and password. Create script as below.</p><pre>crontab -e</pre><p>This will open up a file to schedule cron job.</p><pre>0 0 * * * lftp sftp://<em>&lt;username&gt;</em>:<em>&lt;password&gt;</em>@<em>&lt;sftp-server-ip/domain&gt;</em>  -e &quot;set sftp:auto-confirm yes;  mirror --verbose <em>/path/on/sftp</em> ~/gcsBucket ;  bye&quot;</pre><p>Above job is scheduled at 00:00 hrs everyday (based on VM timezone, usually UTC).<br>Replace &lt;username&gt; with sftp username<br>Replace &lt;password&gt; with sftp password<br>Replace &lt;sftp-server-ip/domain&gt; with sftp url/ip<br>Replace /path/on/sftp with path on sftp server</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b167d0eb487a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/scheduled-mirror-sync-sftp-to-gcs-b167d0eb487a">Scheduled Mirror/Sync SFTP to GCS</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>