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

Being a dba

being a dba

This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.

SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple databases, both on-premises and in the cloud. – ms-docs

What is SQL Data Sync?

SQL Data Sync is the ‘eventual” syncing of two or more databases together. Eventual because the sync is not real-time or transactional like Transactional Replication but the changes from one database are moved to another database at some point, there won’t be any data loss but it’s just that any change made to one database won’t be implemented on the others the same time.

Taken from ms-docs

Components of SQL Data Sync

Hub database: This is the database which acts like repository for all the changes happening in all the spoke databases, and is responsible to sync with each member individually.

Member/Spoke Databases: These are the databases which we want to be in sync with some other database. It could be either Azure SQL Database or a database in an instance of SQL Server

Metadata Database: This database holds every metadata information of SQL Data Sync group and members.

Sync Group: This is the starting point and also the “box” where everything exist. To start with SQL Data Sync you need to create a Sync Group where you define all the settings and configure the Hub, Metadata and Member database.

Conflict Resolution: As all the hubs and spokes are writable, there could be a scenario when the changes are made to one which is conflicting with the data already existing at other end, in such case you have the option to choose which data will be discarded and which will be stored.
The options available are “Hub wins”, which is, during sync if the data in Spoke is conflicting with the one in Hub, Spoke’s data is disregarded and the values from Hub is copied into the Spoke db. The other option is “Member wins” in which data in Hub is overwritten by the one from member (Spoke db)

Automatic Sync: In SQL Data Sync you have the option to either set it for only manual sync or set it for Automatic Sync where you define the frequency when sync automatically triggers. It could be anything from 5 minutes to 30 days.

Sync Agent: When having a member which is a database from a SQL Server Instance you need to install Sync Agent, and connect it with your Sync Group using agent key (you will find this when adding On-Premise database)

This is used for building secure connection between Sync group and the On-Premise database sitting behind the firewall.

Creating SQL Data Sync

For having data sync between different database first we need to setup the group which will hold the whole Data Sync ecosystem.

When creating the New Sync Group you need to provide the name of your group, Name of the Metadata database, the option to either have Automatic Sync or Manual and the last is Conflict Resolution.

If you select Automatic Sync you will also need to provide the frequency in seconds, minutes, hours or days. It could only be in between 5 minute or 30 days.

Once the 1st step is processed you will need to provide the below details in the 2nd tab.

Читать еще:  Названы самые выгодные тарифные планы от всех российских сотовых операторов

a) The username and password to connect to the Hub database’s SQL Server.
b) Azure Database/On-Premise database details.

For adding an Azure SQL Database, select “Add an Azure Database”, select the database that you to be in sync and choose the direction for data movement which could be, ‘From the Hub”, “To the Hub” or ‘Bi-Directional Sync”. Username and password will be the existing credentials for the server.

If you need to have On-Premise database check this link, the important thing is you will require to install the Sync Agent on the On-Premise server.

In the next tab you will be given the option to select the tables that you want to sync, only the tables with Primary Key can be synced.

Once you are setup, to begin Sync for your database go to the Sync Group and click on “Sync”.

Working of SQL Data Sync

SQL Data Sync uses hub and spoke topology, spokes could be either Azure SQL Database or any SQL Server Instance but Hub can only be Azure SQL Database.

SQL Data Sync uses Triggers and Tables to track the changes happening to the tables part of the Sync. When a Sync is triggered the tables are checked for the data to be moved.

1. To track all the changes happening in the tables three triggers are created one each for Delete, Insert and Update in every table.

2. These trigger insert the values into the table created in the same database in DataSync schema to track the changes these tables are named as DataSync.tablename_dss_tracking .

3. There are some other tables created to keep record of the sync group and tables being synced.

PowerShell commands for SQL Data Sync

I tried configuring SQL Data Sync using just PowerShell but dropped the idea in between as the process which took me 15 minutes using GUI was taking more 1 hour using PowerShell.

The only command that I am going to use is Start-AzSqlSyncGroupSync .

So I’ll prefer configuring SQL Data Sync using GUI and then automating the Sync not based upon some frequency but my usage by using the Start-AzSqlSyncGroupSync cmdlet.

Conclusion

SQL Data Sync is a powerful feature, to know about, for providing easy sync between the mix of Azure SQL and On-premise databases.

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:

Читать еще:  MoneyWiz 2 – новое поколение популярного менеджера финансов

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

The following list of article might provide adequate information for you to put together an Azure SQL Data Sync:

SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

  • Set up SQL Data Sync (Preview)

(Emphasis mine)

In this tutorial, you learn how to set up Azure SQL Data Sync by creating a hybrid sync group that contains both Azure SQL Database and SQL Server instances. The new sync group is fully configured and synchronizes on the schedule you set.
[. ]

Add an Azure SQL Database

In the Member Database section, optionally add an Azure SQL Database to the sync group by selecting Add an Azure Database. The Configure Azure Database page opens.

On the Configure Azure Database page, do the following things:

In the Sync Member Name field, provide a name for the new sync member. This name is distinct from the name of the database itself.

In the Subscription field, select the associated Azure subscription for billing purposes.

In the Azure SQL Server field, select the existing SQL database server.

In the Azure SQL Database field, select the existing SQL database.

In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.

In the Username and Password fields, enter the existing credentials for the SQL Database server on which the member database is located. Don’t enter new credentials in this section.

Select OK and wait for the new sync member to be created and deployed.

Add an on-premises SQL Server database

In the Member Database section, optionally add an on-premises SQL Server to the sync group by selecting Add an On-Premises Database. The Configure On-Premises page opens.

On the Configure On-Premises page, do the following things:

Select Choose the Sync Agent Gateway. The Select Sync Agent page opens.

On the Choose the Sync Agent Gateway page, choose whether to use an existing agent or create a new agent.

If you chose Existing agents, select the existing agent from the list.

If you chose Create a new agent, do the following things:

a. Download the client sync agent software from the link provided and install it on the computer where the SQL Server is located.

Important You have to open outbound TCP port 1433 in the firewall to let the client agent communicate with the server.

b. Enter a name for the agent.

c. Select Create and Generate Key.

d. Copy the agent key to the clipboard.

e. Select OK to close the Select Sync Agent page.

f. On the SQL Server computer, locate and run the Client Sync Agent app.

g. In the sync agent app, select Submit Agent Key. The Sync Metadata Database Configuration dialog box opens.

h. In the Sync Metadata Database Configuration dialog box, paste in the agent key copied from the Azure portal. Also provide the existing credentials for the Azure SQL Database server on which the metadata database is located. (If you created a new metadata database, this database is on the same server as the hub database.) Select OK and wait for the configuration to finish.

Note If you get a firewall error at this point, you have to create a firewall rule on Azure to allow incoming traffic from the SQL Server computer. You can create the rule manually in the portal, but you may find it easier to create it in SQL Server Management Studio (SSMS). In SSMS, try to connect to the hub database on Azure. Enter its name as .database.windows.net . To configure the Azure firewall rule, follow the steps in the dialog box. Then return to the Client Sync Agent app.

i. In the Client Sync Agent app, click Register to register a SQL Server database with the agent. The SQL Server Configuration dialog box opens. j. In the SQL Server Configuration dialog box, choose whether to connect by using SQL Server authentication or Windows authentication. If you chose SQL Server authentication, enter the existing credentials. Provide the SQL Server name and the name of the database that you want to sync. Select Test connection to test your settings. Then select Save. The registered database appears in the list.

Читать еще:  Как очистить память телефона Андроид (5 простых и эффективных способов)

k. You can now close the Client Sync Agent app.

l. In the portal, on the Configure On-Premises page, select Select the Database. The Select Database page opens.

m. On the Select Database page, in the Sync Member Name field, provide a name for the new sync member. This name is distinct from the name of the database itself. Select the database from the list. In the Sync Directions field, select Bi-directional Sync, To the Hub, or From the Hub.

n. Select OK to close the Select Database page. Then select OK to close the Configure On-Premises page and wait for the new sync member to be created and deployed. Finally, click OK to close the Select sync members page.

To connect to SQL Data Sync and the local agent, add your user name to the role DataSync_Executor. Data Sync creates this role on the SQL Server instance.

Step 3 — Configure sync group

After the new sync group members are created and deployed, Step 3, Configure sync group, is highlighted in the New sync group page.

On the Tables page, select a database from the list of sync group members, and then select Refresh schema.

From the list of available tables, select the tables that you want to sync.

By default, all columns in the table are selected. If you don’t want to sync all the columns, disable the checkbox for the columns that you don’t want to sync. Be sure to leave the primary key column selected.

Finally, select Save.

  • Sync data across multiple cloud and on-premises databases with SQL Data Sync (Preview)

SQL Data Sync is a service built on Azure SQL Database that lets you synchronize the data you select bi-directionally across multiple SQL databases and SQL Server instances.

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

What happens if the same file is modified on different devices at the same time?

For example, let’s say you create a picture called image.jpg on your computer while it isn’t connected to the internet, then you upload a picture with the same name to Yandex.Disk from your smartphone.

Yandex.Disk starts syncing as soon as you connect your computer to the internet. Having detected two files with the same name, Disk automatically renames one as follows: image.jpg and image(2).jpg .

Automated Continuous Data Replication

Keep SaaS/Cloud data in sync with any Database or Data Warehouse

CData Sync provides users with a straightforward way to synchronize data between on-premise and cloud data sources with a wide range of traditional and emerging databases. Replicate data to facilitate operational reporting, connect data to analytics for BI and decision support, archive data for disaster recovery, and much more.

  • Synchronize data with a wide range of traditional and emerging databases.
  • Replicate Cloud data to RDBMS systems to facilitate operational reporting, BI, and analytics.
  • Archive data for disaster recovery.
Ссылка на основную публикацию
Статьи c упоминанием слов:
Adblock
detector