Month: July 2017

How To Handle Schema Changes In Azure SQL Data Sync

In this post I will show you how to handle schema changes in Azure SQL Data Sync. It follows on my previous post which gave a quick overview of Azure SQL Data Sync.

How To Handle Schema Changes In Azure SQL Data Sync

Since schema changes are not handled automatically by Azure SQL Data Sync you have to perform some manual steps in the Azure Portal or automate the steps. I’m going to cover doing it manually but same sequence of steps can be automated using PowerShell cmdlets or direct calls to the REST API.

Refreshing The Schema

For Azure SQL Data Sync to pickup changes in the schema of Sync Group members you have to refresh the schema.

In the Azure Portal browse to your Sync Group.

How To Handle Schema Changes In Azure SQL Data Sync

 

Select properties and disable automatic sync to prevent errors while changes are taking place.

How To Handle Schema Changes In Azure SQL Data Sync

 

Select Tables and for each database in the Sync Group click on refresh schema to pickup any changes. Select the table and columns to sync and click on save.

How To Handle Schema Changes In Azure SQL Data Sync

Adding A New Column To Sync

There are two scenarios here:

  • Adding a new column that is empty or the same in all databases

In this case you create the column in all the databases with a null or default value that will result in the same rows across all the databases.

  • Adding an existing column with data that was not synced yet

In this case the column exists with possibly different data between databases. If the rows are different between databases your conflict resolution policy and client sync sequence will determine the end result. If the conflict resolution policy is set to “hub wins” the member databases will end up with the values from the hub. If the policy is set to “client wins” the last client to sync will set the values in the hub and those values will be synced to the other clients.

  1. Disable automatic sync.
  2. Add the column in all Sync Group databases.
  3. Refresh the schema for each database in the Sync Group.
  4. Enable automatic sync.

Changing the data type of a column

You can change the data type of a column if the change will not cause any data loss for instance changing int to bigint.

  1. Disable automatic sync.
  2. Change the column type in all Sync Group databases.
  3. Refresh the schema for each database in the Sync Group.
  4. Enable automatic sync.

If the change will lead to data loss you can treat it as removing and adding a new column to achieve the same outcome.

Changing the name of a column

Changing the name of column basically involves removing the existing column and adding a new one.

  1. Disable automatic sync.
  2. Change the column name in all Sync Group databases.
  3. Refresh the schema for each database in the Sync Group and select the new column.
  4. Enable automatic sync.

NOTE: You cannot change a column if it is used in a filter.

Deleting a column

  1. Disable automatic sync.
  2. Delete the column from all databases in the Sync Group.
  3. Refresh the schema for each database in the Sync Group and select the new column.
  4. Enable automatic sync.

NOTE: You cannot remove a column if it is used in a filter.

Francois Delport
Latest posts by Francois Delport (see all)

Azure SQL Data Sync

In this post I’m going to take a quick look at Azure SQL Data Sync. What is does, how it works, scenarios where you would use it and recent updates.

What Does It Do

As the name implies you use Azure SQL Data Sync to sync data between MS SQL databases via Azure. The databases can be on-premise MS SQL or Azure SQL databases. Take note that this is data only, not schema changes or other database objects, just data in tables. The data sync can be configured to run on a schedule with the smallest recurrence of 5 minutes or it can be a manual sync. Individual tables and columns can be selected for sync.

How Does It Do It

Azure SQL Data Sync uses a hub and spoke model. The hub in this case is an Azure SQL database, the spokes or member databases are connected to the hub. Changes will flow from a member database to the hub and then to other member databases. Azure SQL databases can connect directly to the hub but on-premise databases have to use Sync Agents which you install on-premise. When you connect a member database you can choose the sync direction:

  • bi-directional
  • to the hub
  • from the hub

To handle conflicts you select a conflict policy, the options are, hub wins or client wins.

Usage Scenarios

By choosing different sync directions you can utilise Azure SQL Data Sync for a variety of scenarios:

  • Bi-directional sync for all members can be used to keep all connected databases and the hub in sync
  • Syncing to the hub for all members can be used to collect all records from member database into the hub. For example creating a central aggregated database for reporting
  • Syncing from the hub for all members can be used to create a single source of truth in the hub. Member databases will have a local copy of the data
  • Setting one member to sync to the hub and another to sync from the hub will keep the read only member database in sync with the transactional member database. This is handy when moving a database that is constantly being updated into Azure SQL. You can continue to use the on-prem database and make the switch with very little down time and without restoring from a backup or bulk copy procedure. UPDATE: Transactional Replication is now available for Azure SQL and is the preferred method to sync database changes in a migration scenario link.

It is important to remember Azure SQL Data Sync will sync only data, schema changes are not handled automatically by the tool. In a future post I will dig deeper into handling schema changes.

Azure SQL Data Sync Update

Previously Azure SQL Data Sync was only available in the old Azure portal. After the update it is now available in the new Azure portal. It also received PowerShell cmdlets and Rest API enhancements, previously everything had to be done manually in the UI. The hub used to have a shared database maintained by the Azure SQL Data Sync service. Users will now have to provision their own database in their own subscription and use it as the hub.

Resources

The Azure SQL Data Sync team did a very good job providing comprehensive documentation which can be downloaded here link.

Francois Delport
Latest posts by Francois Delport (see all)

Create Custom Virtual Machine Image In New Azure Portal

In this post I’ll show you how to create custom virtual machine images in the new Azure Portal for ARM Virtual Machines and Dev Test Labs. Creating custom virtual machine images from your existing virtual machines is a bit different in the new Azure portal compared to the old one. There are lots of resources showing how to do it in PowerShell, Azure CLI and ASM VMs but not so much ARM VMs in the new Azure Portal. For some reason it is not so intuitive that I could just stumbled upon it by exploring the portal.

Create Custom Virtual Machine Image In The Portal

My first thought was to look for a capture image button on the VM blade but there isn’t one, it is now a separate resource on its own blade.

ImagesMenu

If you plan on creating multiple distinct VMs from this image you have to run sysprep before creating the image. If you really want to make clones of this instance you can skip sysprep. The images are created as Managed Disks and you can’t change it so keep the associated cost in mind.

Create Virtual Machine From Custom Image In The Portal

To create a VM from the custom image created earlier you have to go back to the Image blade.

CreateVMFromImage

Create Custom Virtual Machine Image In Dev Test Labs
Firstly Dev Test Labs are pretty awesome, have a look at it if you have to manage multiple VMs for development, testing or training labs. Creating custom images in Dev Test Labs are a bit easier, the “Create custom image” menu item is right on the VM blade.

CreateImageDevTest

You also have the option to run sysprep if you didn’t already or to skip it. Note that the VM will become unusable if you run sysprep.

RunSysPrep

To manage the existing custom images you have to open the “Configuration and policies” blade and you’ll see the “Custom Images” menu item.

ManageCustomImages

Create Virtual Machine From Custom Image In Dev Test Labs

To create a VM from your custom image is very intuitive in Dev Test Labs, when you click on the Add button to create a VM your custom images will be right there with the existing VM templates.

CreateFromCustomImageDevTest

The same applies when you create new Formulas, which is basically creating the new templates in Dev Test Labs.

Francois Delport
Latest posts by Francois Delport (see all)