Two Users Are Trying To Update The Sql Database At The Same Time. What Will Happen?

title	description	services	ms.service	ms.subservice	ms.custom	ms.devlang	ms.topic	author	ms.author	ms.reviewer	ms.date
What is SQL Data Sync for Azure?	This overview introduces SQL Data Sync for Azure, which allows you to sync information across multiple deject and on-premises databases.	sql-database	sql-database	sql-data-sync	data sync, sqldbrb=1, fasttrack-edit		conceptual	MaraSteiu	masteiu	kendralittle, mathoma	2/2/2022

What is SQL Data Sync for Azure?

SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data y'all select bi-directionally across multiple databases, both on-bounds and in the cloud.

[!IMPORTANT] Azure SQL Data Sync does not support Azure SQL Managed Instance at this time.

Overview

Data Sync is based around the concept of a sync group. A sync group is a grouping of databases that you want to synchronize.

Data Sync uses a hub and spoke topology to synchronize data. You define one of the databases in the sync group as the hub database. The rest of the databases are member databases. Sync occurs but between the hub and individual members.

• The Hub Database must exist an Azure SQL Database.
• The member databases can be either databases in Azure SQL Database or in instances of SQL Server.
• The Sync Metadata Database contains the metadata and log for Data Sync. The Sync Metadata Database has to be an Azure SQL Database located in the same region every bit the Hub Database. The Sync Metadata Database is customer created and customer endemic. You can only have one Sync Metadata Database per region and subscription. Sync Metadata Database cannot be deleted or renamed while sync groups or sync agents exist. Microsoft recommends to create a new, empty database for employ as the Sync Metadata Database. Data Sync creates tables in this database and runs a frequent workload.

[!NOTE] If you're using an on bounds database as a member database, you lot have to install and configure a local sync amanuensis.

A sync group has the following properties:

• The Sync Schema describes which data is existence synchronized.
• The Sync Direction tin can be bi-directional or can flow in only one management. That is, the Sync Direction tin be Hub to Member, or Member to Hub, or both.
• The Sync Interval describes how frequently synchronization occurs.
• The Conflict Resolution Policy is a group level policy, which tin be Hub wins or Member wins.

When to utilize

Data Sync is useful in cases where information needs to be kept updated across several databases in Azure SQL Database or SQL Server. Here are the main apply cases for Data Sync:

• Hybrid Data Synchronization: With Information Sync, yous tin can keep information synchronized betwixt your databases in SQL Server and Azure SQL Database to enable hybrid applications. This capability may appeal to customers who are because moving to the cloud and would like to put some of their application in Azure.
• Distributed Applications: In many cases, it'southward beneficial to separate unlike workloads across unlike databases. For example, if you have a large production database, but yous also need to run a reporting or analytics workload on this data, it's helpful to have a second database for this boosted workload. This approach minimizes the operation bear on on your production workload. You lot can use Data Sync to go along these ii databases synchronized.
• Globally Distributed Applications: Many businesses span several regions and even several countries/regions. To minimize network latency, it'due south best to have your information in a region shut to y'all. With Information Sync, you tin can hands go along databases in regions around the world synchronized.

Data Sync isn't the preferred solution for the following scenarios:

Scenario	Some recommended solutions
Disaster Recovery	Azure geo-redundant backups
Read Scale	Apply read-only replicas to load residual read-only query workloads
ETL (OLTP to OLAP)	Azure Data Factory or SQL Server Integration Services
Migration from SQL Server to Azure SQL Database. Still, SQL Information Sync can be used after the migration is completed, to ensure that the source and target are kept in sync.	Azure Database Migration Service

How it works

• Tracking data changes: Data Sync tracks changes using insert, update, and delete triggers. The changes are recorded in a side tabular array in the user database. Note that BULK INSERT doesn't fire triggers past default. If FIRE_TRIGGERS isn't specified, no insert triggers execute. Add together the FIRE_TRIGGERS choice and so Data Sync tin can track those inserts.
• Synchronizing data: Data Sync is designed in a hub and spoke model. The hub syncs with each fellow member individually. Changes from the hub are downloaded to the member and so changes from the member are uploaded to the hub.
• Resolving conflicts: Information Sync provides two options for conflict resolution, Hub wins or Member wins.
  • If you select Hub wins, the changes in the hub always overwrite changes in the fellow member.
  • If you select Member wins, the changes in the member overwrite changes in the hub. If at that place's more than one member, the final value depends on which member syncs beginning.

Compare with Transactional Replication

	Information Sync	Transactional Replication
Advantages	- Agile-agile support - Bi-directional between on-bounds and Azure SQL Database	- Lower latency - Transactional consistency - Reuse existing topology afterward migration -Azure SQL Managed Instance support
Disadvantages	- No transactional consistency - College performance impact	- Can't publish from Azure SQL Database - Loftier maintenance cost

Private link for Data Sync

[!NOTE] The SQL Data Sync private link is different from the Azure Private Link.

The new individual link feature allows y'all to choose a service managed private endpoint to establish a secure connection between the sync service and your member/hub databases during the data synchronization process. A service managed private endpoint is a private IP address within a specific virtual network and subnet. Within Data Sync, the service managed private endpoint is created by Microsoft and is exclusively used past the Data Sync service for a given sync operation. Before setting upwards the private link, read the general requirements for the feature.

[!NOTE] You must manually approve the service managed private endpoint in the Private endpoint connections page of the Azure portal during the sync grouping deployment or past using PowerShell.

Become started

Set up Data Sync in the Azure portal

• Fix Azure SQL Data Sync
• Data Sync Agent - Data Sync Amanuensis for Azure SQL Data Sync

Set upwardly Information Sync with PowerShell

• Employ PowerShell to sync betwixt multiple databases in Azure SQL Database
• Use PowerShell to sync between a database in Azure SQL Database and a databases in a SQL Server example

Set up Information Sync with Rest API

• Use Rest API to sync betwixt multiple databases in Azure SQL Database

Review the all-time practices for Data Sync

• Best practices for Azure SQL Data Sync

Did something get wrong

• Troubleshoot issues with Azure SQL Data Sync

Consistency and performance

Eventual consistency

Since Information Sync is trigger-based, transactional consistency isn't guaranteed. Microsoft guarantees that all changes are made eventually and that Data Sync doesn't cause data loss.

Functioning bear on

Information Sync uses insert, update, and delete triggers to rail changes. Information technology creates side tables in the user database for modify tracking. These change tracking activities have an impact on your database workload. Appraise your service tier and upgrade if needed.

Provisioning and deprovisioning during sync group cosmos, update, and deletion may also affect the database performance.

Requirements and limitations

Full general requirements

• Each table must have a master central. Don't change the value of the primary key in any row. If you have to modify a chief key value, delete the row and recreate information technology with the new principal key value.

[!Important] Changing the value of an existing primary key will result in the following faulty behavior:

• Information between hub and member can be lost even though sync does non report whatsoever issue.
• Sync tin can fail considering the tracking tabular array has a non-existing row from source due to the primary key change.

• Snapshot isolation must be enabled for both Sync members and hub. For more info, come across Snapshot Isolation in SQL Server.

• In order to utilise Data Sync private link, both the member and hub databases must be hosted in Azure (aforementioned or different regions), in the same cloud type (e.grand. both in public cloud or both in government cloud). Additionally, to utilise individual link, Microsoft.Network resources providers must exist Registered for the subscriptions that host the hub and member servers. Lastly, you must manually approve the private link for Information Sync during the sync configuration, within the "Private endpoint connections" department in the Azure portal or through PowerShell. For more than details on how to approve the private link, see Set up SQL Information Sync. One time you corroborate the service managed private endpoint, all communication between the sync service and the member/hub databases will happen over the individual link. Existing sync groups can be updated to have this characteristic enabled.

General limitations

• A table can't have an identity cavalcade that isn't the primary key.
• A primary key tin't have the following data types: sql_variant, binary, varbinary, image, xml.
• Be cautious when you lot use the following data types equally a primary key, because the supported precision is just to the 2d: time, datetime, datetime2, datetimeoffset.
• The names of objects (databases, tables, and columns) tin can't incorporate the printable characters period (.), left foursquare bracket ([), or right square subclass (]).
• A table proper name tin't contain printable characters: ! " # $ % ' ( ) * + - space
• Azure Active Directory authentication isn't supported.
• If at that place are tables with the same proper name but different schema (for instance, dbo.customers and sales.customers) but ane of the tables can be added into sync.
• Columns with User-Divers Data Types aren't supported
• Moving servers betwixt different subscriptions isn't supported.
• If two chief keys are only unlike in case (e.g. Foo and foo), Data Sync won't support this scenario.
• Truncating tables is not an operation supported by Information Sync (changes won't be tracked).
• Using a Hyperscale database as a Hub or Sync Metadata database is not supported. Even so, a Hyperscale database tin exist a member database in a Data Sync topology.
• Memory-optimized tables are not supported.

Unsupported data types

• FileStream
• SQL/CLR UDT
• XMLSchemaCollection (XML supported)
• Cursor, RowVersion, Timestamp, Hierarchyid

Unsupported column types

Information Sync can't sync read-only or organisation-generated columns. For example:

• Computed columns.
• System-generated columns for temporal tables.

Limitations on service and database dimensions

Dimensions	Limit	Workaround
Maximum number of sync groups any database can belong to.	5
Maximum number of endpoints in a unmarried sync grouping	30
Maximum number of on-premises endpoints in a single sync group.	5	Create multiple sync groups
Database, table, schema, and column names	fifty characters per proper noun
Tables in a sync group	500	Create multiple sync groups
Columns in a tabular array in a sync group	thousand
Data row size on a table	24 Mb

[!NOTE] There may be up to thirty endpoints in a single sync group if there is only one sync group. If in that location is more than i sync group, the total number of endpoints across all sync groups cannot exceed 30. If a database belongs to multiple sync groups, it is counted as multiple endpoints, not one.

Network requirements

[!Annotation] If you lot utilize Sync private link, these network requirements practise not employ.

When the sync grouping is established, the Data Sync service needs to connect to the hub database. At the time when y'all establish the sync group, the Azure SQL server must accept the following configuration in its Firewalls and virtual networks settings:

• Deny public network admission must exist fix to Off.
• Permit Azure services and resources to access this server must be fix to Yes, or you must create IP rules for the IP addresses used past Data Sync service.

Once the sync group is created and provisioned, y'all tin can so disable these settings. The sync agent will connect directly to the hub database, and yous can utilise the server's firewall IP rules or individual endpoints to allow the agent to access the hub server.

[!NOTE] If you modify the sync group'southward schema settings, yous will need to let the Information Sync service to access the server once again then that the hub database tin exist re-provisioned.

Region data residency

If yous synchronize data within the same region, SQL Data Sync doesn't shop/process customer data outside that region in which the service case is deployed. If y'all synchronize data across different regions, SQL Data Sync will replicate customer information to the paired regions.

FAQ about SQL Information Sync

How much does the SQL Data Sync service cost

There's no charge for the SQL Information Sync service itself. However, y'all yet collect information transfer charges for data motility in and out of your SQL Database instance. For more information, come across data transfer charges.

What regions support Data Sync

SQL Data Sync is available in all regions.

Is a SQL Database account required

Yes. You must accept a SQL Database account to host the hub database.

Can I utilize Information Sync to sync betwixt SQL Server databases only

Not direct. You lot tin sync between SQL Server databases indirectly, however, by creating a Hub database in Azure, and so adding the on-premises databases to the sync group.

Can I configure Data Sync to sync between databases in Azure SQL Database that vest to different subscriptions

Yes. You can configure sync between databases that belong to resource groups owned past different subscriptions, fifty-fifty if the subscriptions belong to different tenants.

• If the subscriptions belong to the same tenant and you take permission to all subscriptions, you can configure the sync group in the Azure portal.
• Otherwise, yous have to utilize PowerShell to add the sync members.

Tin can I setup Data Sync to sync betwixt databases in SQL Database that vest to different clouds (similar Azure Public Cloud and Azure China 21Vianet)

Yes. You can setup sync between databases that belong to different clouds. You take to use PowerShell to add the sync members that belong to the dissimilar subscriptions.

Tin I apply Data Sync to seed data from my production database to an empty database, and then sync them

Yep. Create the schema manually in the new database by scripting it from the original. After yous create the schema, add the tables to a sync group to copy the data and keep it synced.

Should I use SQL Data Sync to dorsum upward and restore my databases

It isn't recommended to use SQL Data Sync to create a backup of your data. You can't support and restore to a specific indicate in time considering SQL Data Sync synchronizations aren't versioned. Furthermore, SQL Information Sync doesn't back up other SQL objects, such as stored procedures, and doesn't exercise the equivalent of a restore operation quickly.

For one recommended backup technique, see Re-create a database in Azure SQL Database.

Can Data Sync sync encrypted tables and columns

• If a database uses Always Encrypted, yous can sync only the tables and columns that are not encrypted. You can't sync the encrypted columns, because Data Sync tin't decrypt the information.
• If a cavalcade uses Column-Level Encryption (CLE), you lot can sync the cavalcade, equally long as the row size is less than the maximum size of 24 Mb. Data Sync treats the column encrypted by key (CLE) as normal binary data. To decrypt the data on other sync members, you demand to take the same certificate.

Is collation supported in SQL Data Sync

Yes. SQL Data Sync supports collation in the post-obit scenarios:

• If the selected sync schema tables aren't already in your hub or member databases, then when yous deploy the sync group, the service automatically creates the corresponding tables and columns with the collation settings selected in the empty destination databases.
• If the tables to be synced already exist in both your hub and member databases, SQL Data Sync requires that the primary key columns have the same collation between hub and fellow member databases to successfully deploy the sync group. There are no collation restrictions on columns other than the primary key columns.

Is federation supported in SQL Information Sync

Federation Root Database can be used in the SQL Information Sync Service without any limitation. You can't add together the Federated Database endpoint to the current version of SQL Data Sync.

Can I use Data Sync to sync data exported from Dynamics 365 using bring your own database (BYOD) characteristic?

The Dynamics 365 bring your ain database feature lets administrators export data entities from the application into their own Microsoft Azure SQL database. Information Sync can exist used to sync this data into other databases if data is exported using incremental push (total button is non supported) and enable triggers in target database is set to yep.

Next steps

Update the schema of a synced database

Do you accept to update the schema of a database in a sync grouping? Schema changes aren't automatically replicated. For some solutions, see the following articles:

• Automate the replication of schema changes with SQL Data Sync in Azure
• Employ PowerShell to update the sync schema in an existing sync group

Monitor and troubleshoot

Is SQL Information Sync doing every bit expected? To monitor activity and troubleshoot problems, meet the following manufactures:

• Monitor SQL Data Sync with Azure Monitor logs
• Troubleshoot issues with Azure SQL Data Sync

Larn more nearly Azure SQL Database

For more than info well-nigh Azure SQL Database, see the following articles:

• SQL Database Overview
• Database Lifecycle Direction

Source: https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/azure-sql/database/sql-data-sync-data-sql-server-sql-database.md

Posted by: langfordcounce.blogspot.com

Share this post