1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Sync data across multiple cloud and on-premises databases with SQL Data Sync

Getting started with SQL Data Sync

By: Arshad Ali | Updated: 2013-11-12 | Comments (6) | Related: More > Azure
Problem

SQL Data Sync is a feature of Windows Azure SQL Database which synchronizes data across multiple SQL Server and Windows Azure SQL Database instances without writing any code. How do you get started with using this feature and how does it work?

Solution

SQL Data Sync is a feature of Windows Azure SQL Database built on Microsoft Sync Framework technologies. With the use of this feature, you can either setup single direction or bi-directional data synchronization and data management to allow easy data sharing across Windows Azure SQL Databases across multiple data centers or between on-premises SQL Server databases and Windows Azure SQL databases.

In my last tip, «Understanding SQL Data Sync» I talked in detail about this new capability, in this tip I am going to demonstrate how you get started using it.

In this demonstration, I am going to show how a Windows Azure SQL Database will be synched to a SQL Server database on-premises and vice versa.

Creating a Sync Agent on Azure

The first step in using SQL Data Sync is to create a New Sync Agent (this manages communication between Windows Azure SQL Database and the on-premises SQL Server database without compromising the firewall) on the Windows Azure SQL Database instance.

You need to go to the SQL DATABASES tab on the Windows Azure Management Portal, click on ADD SYNC and then click on New Sync Agent as shown below:

On the New Sync Agent wizard, specify the name of the sync agent and then region where it should be created. For best practices on selecting the right region, please check my earlier tip, «Understanding SQL Data Sync». Also, please note, SQL Data Sync is in Preview mode as of this writing and might have some changes in the final release of the product.

Once the Sync Agent is created, you can see it here on the Sync tab on the top right side of the screen (please note this tab will be available only after you create your first sync agent). As you can see, the status of the created sync agent is offline as we have just created it and not configured it.

I am going to demonstrate the bi-directional data synchronization between Windows Azure SQL Database and on-premises SQL Server database, so I need to setup the Sync Agent for the on-premises machine as well. But before that, I need to get the Sync Agent access key (which I just created), so that it can be contacted by the Sync Agent of the on-premises machine. To get this key, click on the Sync Agent on Windows Azure Management Portal and then click on MANAGE KEY as shown below:

Click on the Generate button on the Manage access key wizard, copy the Agent access key for use in the next section.

Installing and Setting up a Microsoft SQL Data Sync Agent On-Premises

In order to synchronize an on-premises SQL Server Database to a Windows Azure SQL Database or vice versa, you need to install and configure SQL Data Sync Agent on your local machine. You can download it from here for installation and once installed you should see a new Windows service (Microsoft SQL Data Sync) as shown below. Make sure this service is running.

Now go to All Programs and launch the Microsoft SQL Data Sync Agent application which should look like this. Click on the Submit Agent Key as shown below:

Copy or enter the Agent access key, which we copied in the first section of this tip, and click on OK.

Now you should see the Register icon has become enabled, click on this and specify the connection information to connect to the database from your on-premises SQL Server instance. You can click on the «Test Connection» to validate the information and save it as shown below:

Once you have the on-premises database registered in SQL Data Sync Agent, you can see it here as shown below:

Creating and configuring a Sync Group on Azure

Now you need to create a sync group, which is actually a logical grouping of Windows Azure SQL Databases and databases from the on-premises SQL Server instance (in case you want to sync an on-premises SQL Server database). To do this, go to the SQL DATABASES tab on the Windows Azure Management Portal, click on ADD SYNC and then on New Sync Group as shown below:

On the first screen of the Create Sync Group wizard, specify the name of the sync group and region where you want this sync group to be created. For best practices on selecting the right region, please check my earlier tip, «Understanding SQL Data Sync».

On the second screen of the Create Sync Group wizard, specify one of the databases from the Windows Azure SQL database instance as a hub database for this configuration. You need to specify the right credentials to connect to the specified database and the setting to use when there is conflict. In my case I chose Hub Wins in case of conflicts as shown below:

Читать еще:  Планшет не реагирует на прикосновения. Что делать?

On the last screen of the Create Sync Group wizard, you will notice the on-premises SQL Server database gets listed in the list of reference database (this is because we configured Sync Agent on the local machine using the agent access key of this Sync Agent and registered an on-premises database), choose the database you want to use as a reference database for synchronization; specify the credentials and sync direction as shown below:

So far we created a sync group which includes databases for synchronization, we still need to specify tables and columns for synchronization. To do that, click on the sync group you created, click on SYNC RULES then click on the DEFINE SYNC RULES link as shown below:

Now specify the tables and columns from each table to be synchronized as shown below and then save it:

In my case I have a Department table in Windows Azure SQL Database which has data and the same table in my on-premises SQL Server database which does not have data. So what I want to do, is to synchronize from Windows Azure SQL Database to the on-premises SQL Server database:

When you kick off data synchronization, you can see data is synchronized to the on-premises SQL Server database as shown below. Also, if you notice there are some objects that get created when we register the database in the sync agent to be used by the sync agent for synchronization.

As you might have noticed in the last step, we manually kicked off data synchronization but what if I want to set it up to be automated. To configure automatic synchronization, go to the CONFIGURE tab of the sync group you have created, specify automatic synchronization along with the synchronization frequency. Please refer my last tip, «Understanding SQL Data Sync» on best practices for synchronization and frequency.

You can go to the LOGS tab of the sync group to analyze the logs generated by the sync service during data synchronization as shown below.

Please note, SQL Data Sync is in Preview as of writing this tip and features might change in the final release of the SQL Data Sync product; for the latest updates on SQL Data Sync refer to this msdn documentation.

Sync data across multiple cloud and on-premises databases with SQL Data Sync

Azure SQL Data Sync has been ignored for quite some time, but has finally gotten an update (it’s in public preview). This release includes several major improvements to the service including new Azure portal support, PowerShell and REST API support, and enhancements to security and privacy.

SQL Azure Data Sync is a Microsoft Windows Azure web service that provides data synchronization capabilities for SQL databases. SQL Azure Data Sync allows data to be synchronized between on-premises SQL Server databases and Azure SQL databases; in addition, it can also keep multiple Azure SQL databases in sync (see Azure SQL Data Sync technical documentation).

SQL Data Sync targets the reference data replication scenario. Its key capabilities are:

Sync between SQL Server (2005 SP2 and later) and Azure SQL databases, or between Azure SQL databases:

  • One-way and bi-directional sync
  • One-to-one and hub-spoke
  • Table filter and column filter
  • Scheduled and on-demand
  • Eventual consistency

Active Geo-Replication, in contrast, targets the GeoDR scenario for Azure SQL Database by replicating the database to another region. It only supports one-way replication (secondaries are read-only), replication is at database granularity, there is no database or column/row filter support, and it is only available for Premium service tier.

Comparing Data Sync to transactional replication: if you only need to do one way replication, both solution work. Transactional replication can provide better latency and transactional consistency. But it requires more complex setup and maintenance. It doesn’t require the latest SQL Server as publisher as SQL Server 2012 or later will work. Data sync is an Azure Database feature which requires less maintenance and is easier to setup. But it has a minimum five minute latency and a more significant performance impact to the source database (trigger based change tracking). It is optimized for bi direction sync.

Data Sync is now available in the new Azure portal. If you would like to try Data Sync refer to this tutorial. Existing users will be migrated to the new service starting June 1, 2017. For more information on migration look at the blog post “Migrating to Azure SQL Data Sync 2.0.”

Sync data across multiple cloud and on-premises databases with SQL Data Sync

Новые темы необходимо создавать только в корневом разделе! В дальнейшем они будут обработаны модераторами.

Если Вы выложили новую версию программы, пожалуйста, сообщите об этом модератору нажав на вашем сообщении кнопку «Жалоба».

Последнее обновление программы в шапке: 14.01.2014

Для функционирования программы необходимы права root пользователя.

Краткое описание:
Синхронизация приложений и файлов между устройствами. Поддержка WiFi, Dropbox и FTP.

Описание:
DataSync позволяет вам синхронизировать данные между Android устройствами. Это означает что вы можете играть в игру на телефоне, синхронизировать данные и продолжить игру на планшете с того же места.

Особенности:
— Синхронизация файлов и папок, а также сохраненных игр и данных приложений
— Интуитивный интерфейс, совместимый с планшетами и ICS
— Быстрая, мультипоточная синхронизация
— Автоматическое определение устройства
— Поддержка Dropbox и FTP
— Ярлык быстрой синхронизации

Над чем сейчас ведется работа:
— Всплывающие подсказки
— Автоматическая синхронизация по расписанию

По всем найденным багам автор просит писать ему на e-mail, обязуется ответить всем.

Русский интерфейс: Неизвестно

Сообщение отредактировал Gridzilla — 14.01.14, 04:18

Версия в шапке сильно устарела. Самые последние изменения:

DataSync_2.2.5.apk ( 755,17 КБ )

DataSync Beta 3.0.3
Полностью переписанная версия, пока в beta:
Market

— Improved and simplified the UI:
— Properly implemented the (contextual) ActionBar
— Split manual and list-based syncing, default page can be set
— Consistent (holo-themed) style across all platforms and activities
— Improved tablet-UI, especially in portrait mode

— Improved syncing and scheduling:
— Added «Safe two-way» syncing for everyone
— Improved automatic two-way syncing with conflict/first sync checks
— Brand new status page and notifications
— Scheduled syncs only start after the previous sync completed
— Add multiple lists to a single scheduled sync
— Bluetooth syncing does not require pairing on newer devices

— Entirely new features:
— Edit and rename lists
— FTP syncing (yes, it’s back)
— NFC-powered bluetooth syncing
— Automatic device-to-device list syncing
— Support for background syncing (notification will show)

Читать еще:  Как изменить IP на телефоне с ОС Android: простые способы

DataSync Beta_3.0.3.apk ( 1,13 МБ )

Сообщение отредактировал Dragon Master — 10.09.12, 13:15

Automatic data enrichment

Connect your valuable databases with DocuWare for the seamless exchange of critical index and meta information. Enrich document index data in DocuWare with external data sources — or use DocuWare data to populate your other systems. Automatically index large volumes of documents at once to ensure all repositories are precisely consistent.

Synchronize and enrich data

DocuWare is your central repository for documents, but those documents require rich index and meta data for maximum value. Much of that index data is captured when documents arrive in DocuWare — but even more relevant data may reside in external databases.

DocuWare can retrieve information from a data source and uses that dataset to populate records within DocuWare. Large volumes of documents can be indexed quickly for complete consistency.

Conversely, index information from DocuWare can be written to records of external data sources.

DocuWare works with diverse data sources: a standalone database, a more limited database view, a DocuWare file cabinet, or even a simple CSV file.

Use data across system boundaries

DocuWare uses data already available within the company for indexing. This automatic data synchronization is useful in many areas. For example:

  • Invoice indexing. An invoice with sender, document number, date and total is entered in the posting system. To store the invoice, DocuWare accesses the posting system and brings in necessary information.
  • Creating a new contact. DocuWare records the delivery note of a new customer and then recreates the record for this customer in CRM.
  • Returned delivery notes . Delivery notes signed by the customer are archived, assigned to the process and indexed. In the event of a query, all documents can be viewed at a glance.
  • Contract alert . If the expiry date of a contract is less than a certain time ahead, the contract is flagged and displayed in the responsible employee’s task list.
  • Fast reorganization of file cabinets . Add a new index field to all documents or change the document type.

Universal connection

Communicate with databases such as SQL, MySQL, Oracle and OBDC.

Seamless integration

Effortlessly exchange data with systems such as ERP, CRM, HRM and many others.

Flexible time management

Start according to your business schedule or immediately after data modification.

Azure SQL Data Sync Limitations Pricing and Alternatives

Before we talk about the Azure SQL Data Sync Limitations, we will need to understand the prerequisites first. The Azure Data Sync has been around for a while (October 2011), and it got better over the years. At the time of writing this, it was still in preview, and we expect this to be released shortly.

The new version supports On-premises SQL Server instance to act as a subscriber, which we think is excellent. For starters, if you are from the SQL Server On-premises world, and have tickled with SQL Server replication services, you will feel right at home. The Azure SQL Data Sync feature is a hybrid of Merge & Transactional replication. At least, that is what we think.

Image Source: Microsoft

However, the setup complexity is less, and you should be up and running in a few minutes. This was not the case with the on-premises edition. Anyways, let us look at the system requirements first.

When to use SQL Data Sync?

Hybrid Data Synchronization – Data synchronized between your on-premises databases and Azure SQL Databases. For customers who would like to put some of their application in Azure

Distributed Applications – Separate different workloads across different databases. Run a reporting or analytics workload separate from the production workload

Globally Distributed Applications – To minimize network latency and have your data in a region close to where is primary used

However, you must remember that “Data Sync” is not a recommended solution for Disaster Recovery, Read Scale, ETL (OLTP to OLAP) or migration scenarios.

Azure SQL Data Sync Limitations

If you want an uneventful deployment, ensure that the setup has met the minimum prerequisites first. Let us look at the system requirements first.

System Requirements for Azure Data Sync

The Data Sync is a cloud feature, and there is not much to set up. However, for the On-premises SQL Server, here are the system requirements:

  • A minimum of SQL Server 2005 SP2. However, it works best with SQL Server 2008 (R2 and later) as support for SQL Database was added to SQL Server Management Studio in SQL Server 2008 R2. Download a free trial from SQL Server Evaluations if you don’t have one.
  • SQL Data Sync (Preview) only supports full editions of SQL Server and Azure SQL Database. Express versions of SQL Server are not supported.
  • A Microsoft Azure account and subscription.
  • A SQL Database subscription.
  • You need at least one SQL Database deployed. If you need to host SQL Database instances in multiple data centers, you need a SQL Database implemented for each data center.
  • Windows Server 2008 or later or Windows Vista or later versions of Windows OS.
  • An x86 or x64 computer.
  • The latest version of the Sync Agent.
  • .NET Framework 4.5 or above

The table must have a primary key. It must not be changed, and it cannot be: sql_variant, binary, varbinary, image, XML

Snapshot isolation must be enabled

An identity column must be a primary key

Supported precision is only to the second when used as PK: time, DateTime, datetime2, datetimeoffset

Names of objects cannot contain “.”, “[,“ “]”

Azure Active Directory authentication is not supported

Unsupported data types: FileStream, SQL/CLR UDT, XMLSchemaCollection, Cursor, RowVersion, Timestamp, Hierarchyid

Data Sync can’t sync read-only or system-generated columns

Now, that we have the system requirements clarified, let us look at some of the limitations.

  • The maximum number of sync groups support is five (5)
  • Maximum number of endpoints supported is thirty (30)
  • Maximum On-premises endpoints supported is five (5)
  • Maximum 12 filters per table being replicated. This is for performance reasons.

Dimension Limit

  • The database, table, schema, & column names: 50 characters per name
  • Tables in a sync group: 500
  • Columns in a table in a sync group: 1000
  • Data row size on a table: 24Mb

The SQL Server Agent best practices:

  • Install the client agent using the least privilege account with network service access.
  • It is best if the client agent is installed on a computer separate from your on-premises SQL
    Server computer.
  • Do not register an on-premises DB with more than one agent.
  • Even if syncing different tables for different sync groups.
  • Registering an on-premises database

Best Practices before Data-Sync

When you create a sync group, be sure that only one of the databases contains data before the first synchronization. If multiple databases include data, each current row is treated as a data conflict, even if the data is identical. Data conflicts significantly slow down a synchronization. Depending on the size of your DB, if multiple databases are populated with data, the first sync can take days or weeks. Conflict resolution also requires numerous round trips between the databases. If your databases are in different data centers, these round trips can add significantly to your ingress and egress charges.

Sync Group-Update-Limitations

The subsequent changes to a database schema cannot be applied to a sync group. If you want to consolidate any of these changes to your sync group, you need to delete and re-create the sync association.

  • Changing a column’s data-type.
  • Add, remove or modify a column’s filter.
  • Remove a column that has a filter

Sync data across multiple cloud and on-premises databases with SQL Data Sync

  • Forums
  • Podcasts Toggle children
    • Enterprise Dish
    • M365 Knowledge
    • MJF Chat
    • Petri Dish
  • Resources Toggle children
    • Webinars
  • Newsletters
  • Windows Toggle children
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows Server
  • Cloud Toggle children
    • Microsoft Azure
    • Amazon Web Services
    • Google Cloud
  • Office 365 Toggle children
    • Exchange Online
    • Teams
  • Microsoft 365
  • Servers Toggle children
    • Exchange Server
    • SQL Server
  • Backup & Storage
  • SharePoint
  • Security
  • PowerShell

Follow us

  • Forums
  • Podcasts Toggle children Toggle children
    • Enterprise Dish
    • M365 Knowledge
    • MJF Chat
    • Petri Dish
  • Resources Toggle children Toggle children
    • Webinars
  • Newsletters
  • Windows Toggle children
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows Server
  • Cloud Toggle children
    • Microsoft Azure
    • Amazon Web Services
    • Google Cloud
  • Office 365 Toggle children
    • Exchange Online
    • Teams
  • Microsoft 365
  • Servers Toggle children
    • Exchange Server
    • SQL Server
  • Backup & Storage
  • SharePoint
  • Security
  • PowerShell

Follow us

  • Windows Toggle children Toggle children
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows Server
  • Cloud Toggle children Toggle children
    • Microsoft Azure
    • Amazon Web Services
    • Google Cloud
  • Office 365 Toggle children Toggle children
    • Exchange Online
    • Teams
  • Microsoft 365
  • Servers Toggle children Toggle children
    • Exchange Server
    • SQL Server
  • Backup & Storage
  • SharePoint
  • Security
  • PowerShell

Follow us

We noticed you are not a member yet!

Please Sign up/Sign In here in order to add this article to your favorites.

Cloud Computing, Hybrid Cloud, and Microsoft Azure

Synchronizing Hybrid Cloud Data with Azure SQL Data Sync

Paul Thurrott’s Short Takes: September 7

Paul Thurrott’s Short Takes: September 14

Synchronizing Hybrid Cloud Data with Azure SQL Data Sync

Cloud Computing, Hybrid Cloud, and Microsoft Azure

SQL Server includes a number hybrid cloud technologies including Backup to URL that lets you backup on-premise SQL Server databases to Azure and the Stretch Database feature that enables you to seamlessly extend a local database to the cloud. However, what if you want to synchronize the contents of two databases that are on separate servers where one is in the cloud and the other one is local? SQL Server replication is able to replicate database changes but it is not designed to work with the cloud. To address this problem of hybrid cloud database synchronization Microsoft provides their Azure SQL Data Sync service.

Azure SQL Data Sync is useful where data needs to be unidirectionally or bidirectionally synchronized across several Azure SQL Databases or SQL Server databases. It enables hybrid SQL deployment where you can split different workloads across multiple servers. For example, if you have a large production database in the cloud you can use Azure SQL Data Sync to copy the data to another SQL Server instance where you might run reporting or analytics workloads on the data. You can also use it to facilitate global data access where you might have multiple databases in different regions around the world synchronized with each other enabling each region to use its own local data minimizing network latency and possible connection failures.

How SQL Azure Data Sync Works

The core of Azure SQL Data Sync is the Sync Group. A Sync Group is essentially a group of databases that you want to synchronize with another SQL Server instance. Azure SQL Data Sync tracks changes using insert, update, and delete triggers. The changes are recorded in a table in the user database. Azure SQL Data Sync uses a hub and spoke model to synchronize data between different servers. The hub and spoke model consists of the following elements:

  • Hub Database – The Hub syncs with each member in the group individually. Changes from the Hub are downloaded to each member and changes from each Member are uploaded to the Hub. The Hub must be an Azure SQL Database.
  • Member databases – The Member databases can be Azure SQL Databases, on-premises SQL Server instances, or SQL Server instances on Azure VMs. On-premises database require a local sync agent.
  • Sync Database – The Sync database contains the metadata and log that controls and tracks the synchronization process. The Sync Database must be an Azure SQL Database located in the same Azure region as the Hub Database.

Azure SQL Data sync requires that each table must have a primary key and that snapshot isolation must be enabled. Data is synchronized according to the interval that you configure. One of the thorny issues with bidirectional data synchronization is what to do when you have data conflicts. Azure SQL Data Sync offer two options for conflict resolution: Hub wins or Member wins. You can configure Azure SQL Data Sync using either the Azure Portal or PowerShell.

Although it is a service, Microsoft doesn’t charge for the Azure SQL Data Sync service. However, there are charges for data storage and data transfer. You can learn more about how Azure SQL Data Sync can help support your hybrid cloud scenarios at Sync data across multiple cloud and on-premises databases with SQL Data Sync.

Subscribe to Petri Newsletters Office 365 Insider

Our Petri Office 365 Insider is dedicated to sharing detailed knowledge from top Office 365 experts. Delivered once a month to your inbox.

Ссылка на основную публикацию
Статьи c упоминанием слов:
Adblock
detector