da Agency

Cloud SQL for PostgreSQL: Managed PostgreSQL for your mobile and geospatial applications in Google Cloud

By Brett Hesterberg, Product Manager, Google Cloud Platform

At Google Cloud Next ‘17, we announced support for PostgreSQL as part of Google Cloud SQL, our managed database service. With its extensibility, strong standards compliance and support from a vibrant open-source community, Postgres is the database of choice for many developers, especially for powering geospatial and mobile applications. Cloud SQL already supports MySQL, and now, PostgreSQL users can also let Google take care of mundane database administration tasks like applying patches and managing backups and storage capacity, and focus on developing great applications.
Feature highlightsStorage and data protection
Flexible backups: Schedule automatic daily backups or run them on-demand.
Automatic storage increase: Enable automatic storage increase and Cloud SQL will add storage capacity whenever you approach your limit.

Connections
Open standards: We embrace the PostgreSQL wire protocol (the standard connection protocol for PostgreSQL databases) and SSL, so you can access your database from nearly any application, running anywhere.
Security features: Our Cloud SQL Proxy creates a local socket and uses OAuth to help establish a secure connection with your application or PostgreSQL tool. It automatically creates the SSL certificate and makes more secure connections easier for both dynamic and static IP addresses.

Extensibility
Geospatial support: Easily enable the popular PostGIS extension for geospatial objects in Postgres.
Custom instance sizes: Create your Postgres instances with the optimal amount of CPU and memory for your workloads

Create Cloud SQL for PostgreSQL instances customized to your needs.

More features coming soonWe’re continuing to improve Cloud SQL for PostgreSQL during beta. Watch for the following:

Automatic failover for high availability
Read replicas
Additional extensions
Precise restores with point-in-time recovery
Compliance certification as part of Google’s Cloud Platform BAA

Case study: Descartes Labs delves into Earth’s resources with Cloud SQL for PostgreSQLUsing deep-learning to make sense of vast amounts of image data from Google Earth Engine, NASA, and other satellites, Descartes Labs delivers invaluable insights about natural resources and human population. They provide timely and accurate forecasts on such things as the growth and health of crops, urban development, the spread of forest fires and the availability of safe drinking water across the globe.

Cloud SQL for PostgreSQL integrates seamlessly with the open-source components that make up Descartes Labs’ environment. Google Earth Engine combines a multi-petabyte catalog of satellite imagery and geospatial datasets with planetary-scale analysis capabilities and makes it available for scientists, researchers and developers to detect changes, map trends and quantify differences on the Earth’s surface. With ready-to-use data sets and an API, Earth Engine data is core to Descartes Labs’ product. Combining this with NASA data and the popular OpenStreetMap data, Descartes Labs takes full advantage of the open source community.

Descartes Labs’ first application tracks corn crops based on a 13-year historical backtest. It predicts the U.S. corn yield faster and more accurately than the U.S. Department of Agriculture.

Descartes adopted Cloud SQL for PostgreSQL early on because it allowed them to focus on developing applications rather than on mundane database management tasks. “Cloud SQL gives us more time to work on products that provide value to our customers,” said Tim Kelton, Descartes Labs Co-founder and Cloud Architect. “Our individual teams, who are building micro services, can quickly provision a database on Cloud SQL. They don’t need to bother compiling Geos, Proj4, GDAL, and Lib2xml to leverage PostGIS. And when PostGIS isn’t needed, our teams use PostgreSQL without extensions or MySQL, also supported by Cloud SQL.”

According to Descartes Labs, Google Cloud Platform (GCP) is like having a virtual supercomputer on demand, without all the usual space, power, cooling and networking issues. Cloud SQL for PostgreSQL is a key piece of the architecture that backs the company’s satellite image analysis applications.
In developing their newest application, GeoVisual Search, the team benefited greatly from automatic storage increases in Cloud SQL for PostgreSQL. “Ever tried to estimate how a compressed 54GB XML file will expand in PostGIS?” Tim Kelton asked. “It’s not easy. We enabled Cloud SQL’s automatic storage increase, which allows the disk to start at 10GB and, in our case, automatically expanded to 387GB. With this feature, we don’t waste money or time by under- or over-allocating disk capacity as we would on a VM.”

Because the team was able to focus on data models rather than on database management, development of the GeoVisual Search application proceeded smoothly. Descartes’ customers can now find the geospatial equivalent of a needle in a haystack: specific objects of interest in map images.

The screenshot below shows a search through two billion map tiles to find wind turbines.
Tim’s parting advice for startups evaluating cloud solutions: “Make sure the solution you choose gives you the freedom to experiment, lets your team focus on product development rather than IT management and aligns with your company’s budget.”

See what GCP can do for you
Sign up for a $300 credit to try Cloud SQL and the rest of GCP. Start with inexpensive micro instances for testing and development. When you’re ready, you can easily scale them up to serve performance-intensive applications. As a bonus, everyone gets the 100% sustained use discount during beta, regardless of usage.

Our partner ecosystem can help you get started with Cloud SQL for PostgreSQL. To streamline data transfer, reach out to Alooma, Informatica, Segment, Stitch, Talend and Xplenty. For help with visualizing analytics data, try ChartIO, iCharts, Looker, Metabase and Zoomdata.
“PostgreSQL is one of Segment’s most popular database targets for our Warehouses product. Analysts and administrators appreciate its rich set of OLAP features and the portability they’re ensured by it being open source. In an increasingly “serverless” world, Google’s Cloud SQL for PostgreSQL offering allows our customers to eschew costly management and operations of their PostgreSQL instance in favor of effortless setup, and the NoOps cost and scaling model that GCP is known for across their product line.”  — Chris Sperandio, Product Lead, Segment”At Xplenty, we see steady growth of prospects and customers seeking to establish their data and analytics infrastructure on Google Cloud Platform. Data integration is always a key challenge, and we’re excited to support both Google Cloud Spanner and Cloud SQL for PostgreSQL both as data sources as well as targets, to continue helping companies integrate and prepare their data for analytics. With the robustness of Cloud Spanner and the popularity of PostgreSQL, Google continues to innovate and prove it is a world leader in #cloud computing.”  — Saggi Neumann, CTO, Xplenty
No matter how far we take Cloud SQL, we still feel like we’re just getting started. We hope you’ll come along for the ride.

Quelle: Google Cloud Platform

da Agency

Azure SQL hybrid data movement

As #Cloud computing is getting more and more popular today, many companies are choosing to deploy a hybrid environment using a mix of on-premises data center and public cloud. It gives the businesses greater flexibility and more data deployment options. For example, a company can host business critical or sensitive data in on-premises data centers and deploy less-critical data or test and development environment in the public cloud. A hybrid cloud environment will also help large companies to migrate on-premises data center to cloud in multiple stages without interfering with the business.

Moving data around efficiently in a hybrid cloud environment is critical and challenging. In this blog, we are going to introduce options in different data movement scenarios built on top of on-premises SQL Server, Azure SQL VMs and Azure SQL Databases:

Migrate data from on-premises SQL Server to Azure
Replicate data for business continuity
Replicate data to scale out read-only workload
Replicate data to refresh dev-test environment
Distribute referencing data or multi-master
Backup and restore data
Migrate cold data from on-premises SQL Server to Azure
Move data into data warehouse
Move data into big data platform
Move data from other data platforms

We are going to mention the following technologies and tools in this blog:

Export and import .bacpac files
bcp
Transactional replication, including peer-to-peer transaction replication
Merge replication
SQL Server backup and restore, including managed backup and file snapshot backup
Always On availability groups
Data Migration Assistant (DMA)
Azure SQL Data Sync
SQL Server Integration Services (SSIS)
Azure SQL Database copy
Azure Data Factory (ADF)
SQL Server Migration Assistant (SSMA)
Attunity CDC for SSIS
SQL Server Stretch Database

The goal of this blog is to help you to choose the right technologies and tools to implement different scenarios. Implementation details and step by step instructions will not be covered in this blog, however we will provide links to related resources.

Migrate data from on-premises SQL Server to Azure

When you are migrating existing data from on-premises SQL Server databases to Azure, there are a few key facts you should measure and consider:

Azure SQL Databases (PaaS) or Azure SQL VM (IaaS), which is the better option? This is out of scope of our topic today. Please see “Choose a cloud SQL Server option: Azure SQL (PaaS) Database or SQL Server on Azure VMs (IaaS)” for more details.
How many databases are you going to migrate? How large are they?
How much downtime can your service or application afford without significant business impact?

Azure SQL Databases

If you can afford some downtime, or if you are performing a test migration, you can use bacpac to migrate your databases to Azure SQL Databases. See the blog of “Migrating from SQL Server to Azure SQL Database using Bacpac Files” for detailed instructions.

When you migrate databases, especially large databases using bacpac, plan for a long enough application downtime. Depending on the database size, the downtime can be hours.

When you cannot afford to remove your databases from production during the migration, you can consider using transaction replication as the migration solution (SQL Azure Database as push subscriber). See the “Migration from SQL Server to Azure SQL Database Using Transactional Replication” and “Replication to SQL Database” for details, including the limitations of transaction replication.

See “SQL Server database migration to SQL Database in the cloud” for more about migration to Azure SQL Databases.

Azure SQL VM

If you decide to migrate and host your data in Azure SQL VM, you will have several more options, including creating a Always On replica, backup/restore, etc. See “Migrate a SQL Server database to SQL Server in an Azure VM” for more details.

You can also use DMA (Data Migration Assistant) to migrate on-premises SQL Server databases to Azure SQL VM. DMA can migrate not only data, but also other server objects like logins, users and roles. DMA can also be used to detect compatibility issues before the migration. See the document of “Data Migration Assistant (DMA)” for more details.

Replicate data for business continuity

Disruptive events can happen in any data platform and cause data loss or your databases and application to become unavailable. Capability of fast recovery from data loss or database downtime is important for business continuity, especially for business-critical databases and applications.

Azure SQL Databases

Azure SQL Databases automatically have more than one copy created to ensure the high availability (99.99% availability SLA). To prevent or mitigate business discontinuity in event of a data center outage, you can either create Active Geo-replication or restore database from geo-redundant backup.

Active geo-replication will provide minimum downtime and data loss during the data center outage. It can also be used to scale out read-only workloads (will discuss in next scenario); however, this will introduce extra cost to have active geo-replication replicas. Consider this option for business-critical databases. See “Overview: SQL Database Active Geo-Replication” for more details about active geo-replication.

You can also recover your database from geo-redundant backup only when necessary. It will introduce longer recovery time and more data loss. It provides business continuity during data center outage with lower cost. See “Recover an Azure SQL database using automated database backups” for more details:

You can find more details about SQL Azure Databases business continuity in “Overview of business continuity with Azure SQL Database”.

Azure SQL VM

For SQL VM, you can setup Always On availability group or failover cluster instance to manage the downtime during VM reboot or outage.

See “High availability and disaster recovery for SQL Server in Azure Virtual Machines” for more details.

SQL VM as DR solution for on-premises SQL Server

To create a DR site without building a data center in another region, you can extend on-premises Availability Groups to Azure by provisioning one or more Azure SQL VMs and then adding them as replicas to your on-premises Availability Group. See section “Hybrid IT: Disaster recovery solution” in “High availability and disaster recovery for SQL Server in Azure Virtual Machines” for more details.

Replicate data to scale out for read-only workload

In many systems, certain applications only need to read the information from the databases. For example, in the information publishing service, only the publisher need to update the data, and all subscribers only need to read the data. To offload the primary database, you can replicate data and redirect read-only workload to other replicas.

Azure SQL Databases

In addition of providing business continuity in event of disaster, Active Geo-replication also can be used to offload read-only workloads such as reporting jobs to the secondary databases. If you only intend to use the secondary databases for load balancing, you can create the secondary databases in the same region.

See “Overview: SQL Database Active Geo-Replication” for more details about Active Geo-replication.

SQL VMs and on-premises SQL Server

To scale out SQL VM or on-premises SQL Server, you can build readable Always On replicas. Consider to build the replica in the same region unless the read traffic is going to happen in a different region.

Replicate data to refresh dev-test environment

Before system upgrade or deployment of a new system, you may want to test it using a copy of the production data. Building a separate dev-test environment will help you to run the test without impact on your production environment.

Azure SQL Databases

To create a replication of the live production environment in Azure DB for dev-test environment, you can use database copy.

If you want to replicate a snapshot of production environment with old data within retention period (35 days for Standard and Premium; 7 days of Basic), you can restore the database to the point in time you want.

Azure SQL VM and on-premises SQL Server

To replicate data from Azure Databases to on-premises or Azure SQL VM, you can export the database into bacpac and import into SQL Server running in Azure VM or on-premises. If you only want to replicate specific tables instead of the whole database, you can run SqlPackage in Azure VM. See “Export an Azure SQL database or a SQL Server database to a BACPAC file” for more details.

Distributing referencing data/Multi-master

International ISVs and corporates usually have clients or branches in different countries or regions. To reduce the performance impact from network latency, they want some business referencing data, like SKUs, user information, etc., distributed to Azure SQL or SQL Server databases. In a typical scenario, a central database will host all reference data and distribute it to different clients or branches. The clients or branches can also update the reference data locally and push the change back to the central database.

Azure SQL Data Sync can be used to implement the data distribution between on-premises SQL Server, Azure SQL VM and Azure SQL databases, in uni-direction or bi-direction. See “Getting Started with Azure SQL Data Sync (Preview)” to learn more about Azure Data Sync.

Azure Data Sync is now only available in old Azure portal. It will be available in new Azure portal very soon. See the blog of “Azure Data Sync Update” for more details.

On-premises or Azure SQL VMs to Azure SQL Databases

When you are design and implement such a distributed multi-master system, especially cross internet, you should consider to shard the database and only sync data when necessary to reduce the latency.

If the central database is hosted on-premises or in Azure SQL VM, you can use transactional replication to distribute data. Azure SQL databases can be configured as push subscribers and replicate data from the publisher, the central database. Transaction replication can only replicate data in one-way.

Between on-premises or Azure SQL VMs

If all your data in hosted in on-premises SQL Server or Azure SQL VM, and you need to sync data in bi-direction, except using Azure SQL Data Sync, you can setup either Peer-to-peer replication or Merge replication.

If you can design your application or service to ensure certain rows will be modified only in one node, peer-to-peer replication is recommended. If the application requires sophisticated conflict detection and resolution capacities, use merge replication. Merge replication introduces more complex topology and higher maintenance cost comparing to other sync and replication technologies. Only use Merge replication when other technologies can’t solve your problem.

Peer-to-peer replication and Merge replication will not work on Azure SQL Database, as neither publisher nor subscriber.

See Peer to peer transaction replication and Merge replication for more details.

Backup and restore data

Backing up your database is essential for protecting your data. It allows you to recover data from accidental corruption or deletion. We always recommend to store backup files separately from the databases. The hybrid environment will allow you to implement this easily.

Azure SQL Databases

Azure SQL Databases automatically backup the databases at no additional charge. You can restore the database to any point in time during the retention (7 days for Basic and 35 days for Standard and Premium). All backup files are replicated to multiple copies also to a different region. See “Learn about SQL Database backups” for more details.

If your business requires longer retention for backup, you can configure long term retention backup to keep the backup up to 10 years. See “Storing Azure SQL Database Backups for up to 10 years” for more details.

If you want to restore the database to on-premises SQL Server or store the backup in your own local file system or other cloud platforms, you can export the database into a bacpac file. You can use Azure automation to schedule the backup periodically. The sample script is provided in the following document: “Export an Azure SQL database or a SQL Server database to a BACPAC file”.

Azure SQL VMs

If you are running SQL Server on Azure VM, we recommend you to backup your databases to Azure storage (Backup to URL). This feature is supported in SQL Server 2012 SP1 CU2 or later version.

You can also configure the Managed Backup to enable automated backup management. The feature is available in SQL Server 2014 or later version. In SQL Server 2016, it supports custom schedule. See “Backup and Restore for SQL Server in Azure Virtual Machines” for more details.

To ensure the availability of backup files in event of disaster or data center outage, we recommend you use GRS or RA-GRS storage to store the backup files. It will automatically replicate your backup files to a different region. See “Azure Storage replication” for more details.

If you are running SQL Server 2016 and host data files in Azure storage, another option is to use file snapshot backup. It provides near instantaneous backup and rapid restores for database files stored in Azure storage. See “File-Snapshot Backups for Database Files in Azure” for more details.

On-premises SQL Server

We always recommend users to store backup files in a different location from database files. If you are running SQL Server 2014 or later version on-premises, you can use the Backup to URL feature to back up your database or transaction log directly to Azure storage. However, due to the network latency, backing up to and restoring from Azure storage may introduce lower throughput than the same operation against local disk. See “SQL Server Backup and Restore with Windows Azure Blob Storage Service” for more details.

Migrate cold data from on-premises SQL Server to Azure

As the database size is fast growing, managing and storing historical or cold data efficiently becomes a big problem. You can use Stretch Database to migrate the cold data from on-premises SQL Server to Azure and keep the data online. See “Introduction to Stretch Database” for more details about Stretch Database.

Move data into data warehouse

Companies periodically move data from OLTP system to OLAP system/data warehouse for data analysis and reporting. The process usually includes extracting data from data source, transformation and loading data into the target data warehouse (ETL).

The data warehouse and OLAP system can be built on top of Azure SQL Databases, Azure SQL VMs or Azure SQL Data Warehouse.

Most on-premises SQL Server customers use SSIS (SQL Server Integration Services) to load data into data warehouse. It extracts data from OLTP system, transforms it, and loads it into data warehouses. SSIS can also be used in the hybrid environment. For exiting SSIS customers, it will reduce the cost to build a new ETL process. If you are loading data into SQL VM or SQL Databases, we recommend running SSIS on Azure VM. See the blog of “Running SSIS on Azure VM (IaaS) – Do more with less money” for more information.

If you are moving data into Azure Data Warehouse, you can also use ADF (Azure Data Factory) or bcp as the loading tools. See “SQL Data Warehouse Migrate Your Data” and “Use Azure Data Factory with SQL Data Warehouse” for details.

Move data into Azure Big Data Stores for Advanced Analytics

To build big data advanced analytics solution in Azure, users need to load online transactional data and other reference data from various data sources into a modernized multi-platform data warehouse, which usually consists of Azure Blob/Azure Data Lake as the staging area, perform transformation activities such as HIVE/PIG/Spark, and finally load “cooked” data into Azure Data Warehouse for BI and reporting.

If you want to move your data into Azure and build advanced analytics solution on top of it, you can use ADF (Azure Data Factory), which is a fully managed data integration service that orchestrates the movement and transformation of data.  Please see “Introduction to Azure Data Factory Service, a data integration service in the cloud” for more details about ADF.

If you are an existing SSIS user, SSIS is another option you can use to load data into big data stores such as Azure Storage Blob and Azure Data Lake Store.  You will need the Azure feature pack to load data into Azure.

Move data from other data platforms

If you want to migrate databases from other DBMSs (Oracle, MySQL, etc…) to Azure SQL Databases or Azure SQL VM, you can use SSMA (SQL Server Migration Assistant) as a helpful migration tool. See “SQL Server Migration Assistant” for more details.

If you want to continuously move data from other DBMSs or other data format like flat files to Azure SQL Databases or Azure SQL VM, or you need to do some transformation before loading data into Azure, you can use SSIS. Attunity CDC for SSIS or SQL Server CDC for Oracle by Attunity provides end to end operational data replication solution. For more information, see “Attunity CDC for SSIS” and “SQL Server 2012 CDC for Oracle – a Review of One Implementation”.

Summary

In this blog, we discussed how to choose the right technologies and tools for different hybrid data movement scenarios. It is just a starting point and general guidance for these use cases. You will still need to evaluate different solutions based on your business needs.

If you have any further question, please post it in MSDN forum.

If you have any feedback to Azure SQL Databases or Azure SQL VMs, please submit it in https://feedback.azure.com.
Quelle: Azure

da Agency

Azure SQL hybrid data movement

As #Cloud computing is getting more and more popular today, many companies are choosing to deploy a hybrid environment using a mix of on-premises data center and public cloud. It gives the businesses greater flexibility and more data deployment options. For example, a company can host business critical or sensitive data in on-premises data centers and deploy less-critical data or test and development environment in the public cloud. A hybrid cloud environment will also help large companies to migrate on-premises data center to cloud in multiple stages without interfering with the business.

Moving data around efficiently in a hybrid cloud environment is critical and challenging. In this blog, we are going to introduce options in different data movement scenarios built on top of on-premises SQL Server, Azure SQL VMs and Azure SQL Databases:

Migrate data from on-premises SQL Server to Azure
Replicate data for business continuity
Replicate data to scale out read-only workload
Replicate data to refresh dev-test environment
Distribute referencing data or multi-master
Backup and restore data
Migrate cold data from on-premises SQL Server to Azure
Move data into data warehouse
Move data into big data platform
Move data from other data platforms

We are going to mention the following technologies and tools in this blog:

Export and import .bacpac files
bcp
Transactional replication, including peer-to-peer transaction replication
Merge replication
SQL Server backup and restore, including managed backup and file snapshot backup
Always On availability groups
Data Migration Assistant (DMA)
Azure SQL Data Sync
SQL Server Integration Services (SSIS)
Azure SQL Database copy
Azure Data Factory (ADF)
SQL Server Migration Assistant (SSMA)
Attunity CDC for SSIS
SQL Server Stretch Database

The goal of this blog is to help you to choose the right technologies and tools to implement different scenarios. Implementation details and step by step instructions will not be covered in this blog, however we will provide links to related resources.

Migrate data from on-premises SQL Server to Azure

When you are migrating existing data from on-premises SQL Server databases to Azure, there are a few key facts you should measure and consider:

Azure SQL Databases (PaaS) or Azure SQL VM (IaaS), which is the better option? This is out of scope of our topic today. Please see “Choose a cloud SQL Server option: Azure SQL (PaaS) Database or SQL Server on Azure VMs (IaaS)” for more details.
How many databases are you going to migrate? How large are they?
How much downtime can your service or application afford without significant business impact?

Azure SQL Databases

If you can afford some downtime, or if you are performing a test migration, you can use bacpac to migrate your databases to Azure SQL Databases. See the blog of “Migrating from SQL Server to Azure SQL Database using Bacpac Files” for detailed instructions.

When you migrate databases, especially large databases using bacpac, plan for a long enough application downtime. Depending on the database size, the downtime can be hours.

When you cannot afford to remove your databases from production during the migration, you can consider using transaction replication as the migration solution (SQL Azure Database as push subscriber). See the “Migration from SQL Server to Azure SQL Database Using Transactional Replication” and “Replication to SQL Database” for details, including the limitations of transaction replication.

See “SQL Server database migration to SQL Database in the cloud” for more about migration to Azure SQL Databases.

Azure SQL VM

If you decide to migrate and host your data in Azure SQL VM, you will have several more options, including creating a Always On replica, backup/restore, etc. See “Migrate a SQL Server database to SQL Server in an Azure VM” for more details.

You can also use DMA (Data Migration Assistant) to migrate on-premises SQL Server databases to Azure SQL VM. DMA can migrate not only data, but also other server objects like logins, users and roles. DMA can also be used to detect compatibility issues before the migration. See the document of “Data Migration Assistant (DMA)” for more details.

Replicate data for business continuity

Disruptive events can happen in any data platform and cause data loss or your databases and application to become unavailable. Capability of fast recovery from data loss or database downtime is important for business continuity, especially for business-critical databases and applications.

Azure SQL Databases

Azure SQL Databases automatically have more than one copy created to ensure the high availability (99.99% availability SLA). To prevent or mitigate business discontinuity in event of a data center outage, you can either create Active Geo-replication or restore database from geo-redundant backup.

Active geo-replication will provide minimum downtime and data loss during the data center outage. It can also be used to scale out read-only workloads (will discuss in next scenario); however, this will introduce extra cost to have active geo-replication replicas. Consider this option for business-critical databases. See “Overview: SQL Database Active Geo-Replication” for more details about active geo-replication.

You can also recover your database from geo-redundant backup only when necessary. It will introduce longer recovery time and more data loss. It provides business continuity during data center outage with lower cost. See “Recover an Azure SQL database using automated database backups” for more details:

You can find more details about SQL Azure Databases business continuity in “Overview of business continuity with Azure SQL Database”.

Azure SQL VM

For SQL VM, you can setup Always On availability group or failover cluster instance to manage the downtime during VM reboot or outage.

See “High availability and disaster recovery for SQL Server in Azure Virtual Machines” for more details.

SQL VM as DR solution for on-premises SQL Server

To create a DR site without building a data center in another region, you can extend on-premises Availability Groups to Azure by provisioning one or more Azure SQL VMs and then adding them as replicas to your on-premises Availability Group. See section “Hybrid IT: Disaster recovery solution” in “High availability and disaster recovery for SQL Server in Azure Virtual Machines” for more details.

Replicate data to scale out for read-only workload

In many systems, certain applications only need to read the information from the databases. For example, in the information publishing service, only the publisher need to update the data, and all subscribers only need to read the data. To offload the primary database, you can replicate data and redirect read-only workload to other replicas.

Azure SQL Databases

In addition of providing business continuity in event of disaster, Active Geo-replication also can be used to offload read-only workloads such as reporting jobs to the secondary databases. If you only intend to use the secondary databases for load balancing, you can create the secondary databases in the same region.

See “Overview: SQL Database Active Geo-Replication” for more details about Active Geo-replication.

SQL VMs and on-premises SQL Server

To scale out SQL VM or on-premises SQL Server, you can build readable Always On replicas. Consider to build the replica in the same region unless the read traffic is going to happen in a different region.

Replicate data to refresh dev-test environment

Before system upgrade or deployment of a new system, you may want to test it using a copy of the production data. Building a separate dev-test environment will help you to run the test without impact on your production environment.

Azure SQL Databases

To create a replication of the live production environment in Azure DB for dev-test environment, you can use database copy.

If you want to replicate a snapshot of production environment with old data within retention period (35 days for Standard and Premium; 7 days of Basic), you can restore the database to the point in time you want.

Azure SQL VM and on-premises SQL Server

To replicate data from Azure Databases to on-premises or Azure SQL VM, you can export the database into bacpac and import into SQL Server running in Azure VM or on-premises. If you only want to replicate specific tables instead of the whole database, you can run SqlPackage in Azure VM. See “Export an Azure SQL database or a SQL Server database to a BACPAC file” for more details.

Distributing referencing data/Multi-master

International ISVs and corporates usually have clients or branches in different countries or regions. To reduce the performance impact from network latency, they want some business referencing data, like SKUs, user information, etc., distributed to Azure SQL or SQL Server databases. In a typical scenario, a central database will host all reference data and distribute it to different clients or branches. The clients or branches can also update the reference data locally and push the change back to the central database.

Azure SQL Data Sync can be used to implement the data distribution between on-premises SQL Server, Azure SQL VM and Azure SQL databases, in uni-direction or bi-direction. See “Getting Started with Azure SQL Data Sync (Preview)” to learn more about Azure Data Sync.

Azure Data Sync is now only available in old Azure portal. It will be available in new Azure portal very soon. See the blog of “Azure Data Sync Update” for more details.

On-premises or Azure SQL VMs to Azure SQL Databases

When you are design and implement such a distributed multi-master system, especially cross internet, you should consider to shard the database and only sync data when necessary to reduce the latency.

If the central database is hosted on-premises or in Azure SQL VM, you can use transactional replication to distribute data. Azure SQL databases can be configured as push subscribers and replicate data from the publisher, the central database. Transaction replication can only replicate data in one-way.

Between on-premises or Azure SQL VMs

If all your data in hosted in on-premises SQL Server or Azure SQL VM, and you need to sync data in bi-direction, except using Azure SQL Data Sync, you can setup either Peer-to-peer replication or Merge replication.

If you can design your application or service to ensure certain rows will be modified only in one node, peer-to-peer replication is recommended. If the application requires sophisticated conflict detection and resolution capacities, use merge replication. Merge replication introduces more complex topology and higher maintenance cost comparing to other sync and replication technologies. Only use Merge replication when other technologies can’t solve your problem.

Peer-to-peer replication and Merge replication will not work on Azure SQL Database, as neither publisher nor subscriber.

See Peer to peer transaction replication and Merge replication for more details.

Backup and restore data

Backing up your database is essential for protecting your data. It allows you to recover data from accidental corruption or deletion. We always recommend to store backup files separately from the databases. The hybrid environment will allow you to implement this easily.

Azure SQL Databases

Azure SQL Databases automatically backup the databases at no additional charge. You can restore the database to any point in time during the retention (7 days for Basic and 35 days for Standard and Premium). All backup files are replicated to multiple copies also to a different region. See “Learn about SQL Database backups” for more details.

If your business requires longer retention for backup, you can configure long term retention backup to keep the backup up to 10 years. See “Storing Azure SQL Database Backups for up to 10 years” for more details.

If you want to restore the database to on-premises SQL Server or store the backup in your own local file system or other cloud platforms, you can export the database into a bacpac file. You can use Azure automation to schedule the backup periodically. The sample script is provided in the following document: “Export an Azure SQL database or a SQL Server database to a BACPAC file”.

Azure SQL VMs

If you are running SQL Server on Azure VM, we recommend you to backup your databases to Azure storage (Backup to URL). This feature is supported in SQL Server 2012 SP1 CU2 or later version.

You can also configure the Managed Backup to enable automated backup management. The feature is available in SQL Server 2014 or later version. In SQL Server 2016, it supports custom schedule. See “Backup and Restore for SQL Server in Azure Virtual Machines” for more details.

To ensure the availability of backup files in event of disaster or data center outage, we recommend you use GRS or RA-GRS storage to store the backup files. It will automatically replicate your backup files to a different region. See “Azure Storage replication” for more details.

If you are running SQL Server 2016 and host data files in Azure storage, another option is to use file snapshot backup. It provides near instantaneous backup and rapid restores for database files stored in Azure storage. See “File-Snapshot Backups for Database Files in Azure” for more details.

On-premises SQL Server

We always recommend users to store backup files in a different location from database files. If you are running SQL Server 2014 or later version on-premises, you can use the Backup to URL feature to back up your database or transaction log directly to Azure storage. However, due to the network latency, backing up to and restoring from Azure storage may introduce lower throughput than the same operation against local disk. See “SQL Server Backup and Restore with Windows Azure Blob Storage Service” for more details.

Migrate cold data from on-premises SQL Server to Azure

As the database size is fast growing, managing and storing historical or cold data efficiently becomes a big problem. You can use Stretch Database to migrate the cold data from on-premises SQL Server to Azure and keep the data online. See “Introduction to Stretch Database” for more details about Stretch Database.

Move data into data warehouse

Companies periodically move data from OLTP system to OLAP system/data warehouse for data analysis and reporting. The process usually includes extracting data from data source, transformation and loading data into the target data warehouse (ETL).

The data warehouse and OLAP system can be built on top of Azure SQL Databases, Azure SQL VMs or Azure SQL Data Warehouse.

Most on-premises SQL Server customers use SSIS (SQL Server Integration Services) to load data into data warehouse. It extracts data from OLTP system, transforms it, and loads it into data warehouses. SSIS can also be used in the hybrid environment. For exiting SSIS customers, it will reduce the cost to build a new ETL process. If you are loading data into SQL VM or SQL Databases, we recommend running SSIS on Azure VM. See the blog of “Running SSIS on Azure VM (IaaS) – Do more with less money” for more information.

If you are moving data into Azure Data Warehouse, you can also use ADF (Azure Data Factory) or bcp as the loading tools. See “SQL Data Warehouse Migrate Your Data” and “Use Azure Data Factory with SQL Data Warehouse” for details.

Move data into Azure Big Data Stores for Advanced Analytics

To build big data advanced analytics solution in Azure, users need to load online transactional data and other reference data from various data sources into a modernized multi-platform data warehouse, which usually consists of Azure Blob/Azure Data Lake as the staging area, perform transformation activities such as HIVE/PIG/Spark, and finally load “cooked” data into Azure Data Warehouse for BI and reporting.

If you want to move your data into Azure and build advanced analytics solution on top of it, you can use ADF (Azure Data Factory), which is a fully managed data integration service that orchestrates the movement and transformation of data.  Please see “Introduction to Azure Data Factory Service, a data integration service in the cloud” for more details about ADF.

If you are an existing SSIS user, SSIS is another option you can use to load data into big data stores such as Azure Storage Blob and Azure Data Lake Store.  You will need the Azure feature pack to load data into Azure.

Move data from other data platforms

If you want to migrate databases from other DBMSs (Oracle, MySQL, etc…) to Azure SQL Databases or Azure SQL VM, you can use SSMA (SQL Server Migration Assistant) as a helpful migration tool. See “SQL Server Migration Assistant” for more details.

If you want to continuously move data from other DBMSs or other data format like flat files to Azure SQL Databases or Azure SQL VM, or you need to do some transformation before loading data into Azure, you can use SSIS. Attunity CDC for SSIS or SQL Server CDC for Oracle by Attunity provides end to end operational data replication solution. For more information, see “Attunity CDC for SSIS” and “SQL Server 2012 CDC for Oracle – a Review of One Implementation”.

Summary

In this blog, we discussed how to choose the right technologies and tools for different hybrid data movement scenarios. It is just a starting point and general guidance for these use cases. You will still need to evaluate different solutions based on your business needs.

If you have any further question, please post it in MSDN forum.

If you have any feedback to Azure SQL Databases or Azure SQL VMs, please submit it in https://feedback.azure.com.
Quelle: Azure

da Agency

Webinar Q&A: Introducing Docker Enterprise Edition (EE)

A few weeks ago we announced Docker Enterprise Edition (EE), the trusted, certified and supported container platform. Docker EE enables IT teams to establish a Containers as a Service (CaaS) environment to converge legacy, ISV and microservices apps into a single software supply chain that is flexible, secure and infrastructure independent. With a built in orchestration architecture (swarm mode) Docker EE allows app teams to compose and schedule simple to complex apps to drive their digital transformation initiatives.

On March 14th we hosted a live webinar to provide an overview and demonstration of Docker EE. View the recorded session below and read through some of the most popular questions.

Frequently Asked Questions
Q: How is Docker EE licensed?
A: Docker EE is licensed per node. A node is an instance running on a bare metal or virtual server. For more details visit www.docker.com/pricing
Q: Is Google Cloud also one of your certified infrastructure partners?
A: Docker EE is available today for both Azure and AWS. Google Cloud is currently offered as a private beta with Docker Community Edition. Learn more in this blog post and sign up at https://beta.docker.com 
Q: What technology is used for the security scanning and vulnerability features of Docker EE? Does security scanning have a separate license?
A: Docker Security Scanning is the technology that conducts binary level scanning of Docker images and continuous vulnerability monitoring. This capability is included in the Docker EE Advanced subscription tier.  A free 30 day trial is available for you to try security scanning.
Q: Will signing and scanning images and the vulnerabilities for that image work on any image that is internally developed or only images that are downloaded from the Docker Store?
A: Yes, signing and scanning works for any image that is pushed to the on-premises registry (DTR) that is part of Docker EE.
Q: Where can I see the key features included in each of the different Docker EE tiers?
A: There are three tiers: Basic, Standard and Advanced. A comparison table is available at www.docker.com/pricing. 

Q: Can we use the container management layer (UCP) with Docker Community Edition?
A: No. The container management (UCP) and image registry (DTR) are tested, validated and supported for the Docker EE certified infrastructure only.
Q: Can you run Docker Certified Containers over Docker CE Engine?
A: No. Certified Containers and Plugins are tested, validated and supported to the Docker EE certified infrastructure only.
Q: How does the licensing work for Certified Containers and Plugins downloaded from Docker Store?
A: Similar to many other software marketplaces, Docker Store provides an interface for the publisher to provide a pay as you go or BYOL style of container for the Docker Store. Entitlement and upgrades are managed through the Docker Store by the publisher. The publisher can determine the subscription price for the end user.
Q: What is the difference between Docker CE and Docker EE?
A: The Docker product page provides a comparison between Docker CE and EE. https://www.docker.com/get-docker. Docker CE provides a free Docker platform available for many community infrastructure. Docker EE is an integrated container management and security platform with certification and capabilities like role based access control, LDAP/AD integration, deployment policies and more.
Q: Can Docker EE be run within my enterprise or is it only run externally?
A: Docker EE can be deployed on-premises or in your VPC.
Q 12: Does EE Basic use use normal swarm since UCP is with the other versions?
A: Both Docker CE and EE have built in orchestration capabilities of swarm mode. Each node is a fully functioning building block to be a manager or worker node in the cluster.  With Docker Enterprise Edition, the integrated management UI builds on top of the built in swarm mode orchestration and integrates with the private registry and Role Based Access Controls to provide a robust platform for end to end container application management.
Q 13: What is the migration path from Docker Community Edition to Enterprise Edition?
A: The apps built on Docker CE will also run on Docker EE. However there is no in place migration of the cluster itself.  To migrate apps to a Docker EE environment, a new cluster will need to be set up and the same Compose files and images can be deployed as services to the new Docker EE environment.
For More Information:

Learn more about Docker CE and EE
Try Docker EE for free
Register for DockerCon 2017 and Federal Summit

The post Webinar Q&A: Introducing Docker Enterprise Edition (EE) appeared first on Docker Blog.
Quelle: https://blog.docker.com/feed/