Data Platform Tips 6 - Always Encrypted

Always Encrypted feature in SQL Server and Azure SQL Database allows to protect sensitive data like credit card numbers, social security numbers etc. It allows client applications to encrypt the data and the SQL Engine won't know anything about the encrypted keys. Like encryption, decryption also happens through the client application.

Always Encrypted supports 2 types of Encryption.

Deterministic encryption - Always generate the same encrypted value for any plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. Use deterministic encryption where columns will be used in search, join and group by parameters.

Randomized encryption - Encrypts data in a less predictable manner. More secure than deterministic but prevents using the columns in search, join, indexing and group by parameters. Note: With the release of SQL Server 2019 "Always Encrypted with secure enclaves" feature, now pattern matching, comparison operators and indexing are enabled on columns encrypted with Randomized encryption.

Also with SQL Server 2019 "Always Encrypted with secure enclaves", now you can use T-SQL to encrypt existing data without the need to move the data outside for encryption operations.

Data Platform Tips 5 - Dynamic Data Masking

Dynamic Data Masking allows to limit the exposure of sensitive data to the users. Really easy and handy to secure data in your SQL Database, Azure SQL Database or Azure Synapse Analytics (Azure SQL DW).

Different types of masking

• Default - Completely masks the entire column according to the designated data types.
• Email - Masks everything instead of first letter of the email address, @ symbol and the constant suffix e.g. .com, .net etc.
• Random - Use on any numeric type to mask the original value with a random value within a specified range.
• Custom using Partial function - Masking method that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix. Masks everything in between the prefix and suffix.

Download the Dynamic Data Masking Notebook.

Data Platform Tips 4 - Geo-replication vs. Failover groups

Active geo-replication allows to create readable replicas and manually failover to any replica in case of a data center outage.

Auto-failover group allows the application to automatically recovery in case of a data center outage.

Features	Geo-replication	Failover Groups
Automatic Failover	No	Yes
Failover multiple databases simultaneously	No	Yes
Update Connection string after failover	Yes	No
Manage Instance supported	No	Yes
Can be in the same region as primary	Yes	No
Multiple Replicas	Yes	No
Supports Read on secondaries	Yes	Yes

Data Platform Tips 3 - Auto-failover groups

Failover group is a group of databases managed by a single Database server or within a single managed instance that can failover as a group to another region in disaster situations. Supports both  Automatic and Manual failover,

Note: Name of the failover group must be globally unique. (.database.windows.net)

Adding a single database to failover groups

a) Create new resource group named "failover-group"

b) Create a new SQL Database named "failover-group-db1" and a SQL Server named "failover-group-server1" in 'Australia East" region.

c) Navigate to the "failover-group-db1" and click on Failover Groups and create a failover group name as "fog-failover-group"

d) Create a secondary server named "failover-group-db-server2" in "Southeast Asia" region and add "failover-group-db1" database to the failover group.

Initiating a Manual Failover using Azure Portal

a) Navigate to the "fog-failover-group" under "fileover-group-server-1" and notice that the "failover-group-db-server1" has the primary role and "failover-group-db-server2" has the secondary role.

b) Initiate the failover by clicking the "Failover" button and once the failover is complete, "failover-group-db-server2" has the primary role and "failover-group-db-server1" has the primary role.

Data Platform Tips 2 - Active geo-replication for Azure SQL Database

Active Geo-replication is a business continuity feature on Azure SQL Database for quick disaster recovery in case of regional disasters. With geo-replication enabled, it creates readable secondary in the same or different region data center. It supports up to 4 secondaries in the same or different region and can be used for read only query access.

Supports only manual failover.

Active Geo-replication uses Always-On technology to asynchronously replicate committed transactions on the primary database to a secondary database using snapshot isolation.

To guarantee that the changes in primary is replicated in the secondary before initiating a failover, the application can call the stored procedure "sp_wait_for_database_copy_sync" to force the synchronisation from Primary to Secondaries. Also use the stored procedure "sys.dm_geo_replication_link_status" to check the replication status.

Initiate geo-replication using Azure Portal

a) Create a new resource group named "active-geo-replication".





b) Create a new Azure SQL Database




c) Select the newly created database "active-geo-replication-db1" and click on geo-replication.

d) As you can see below the secondary database for active geo-replication hasn't been configured and let us configure it.



e) Select the secondary database region as "Southeast Asia" and configure the secondary database and server

f) Once the secondary replica database is created, you can see the replica database created in the "Southeast Asia" region.

i) The secondary replica is a read only replica as shown below.

Initiating Failover using Azure Portal

a) Initiate a failover by right clicking on the secondary replica and clicking on "Forced failover" and click ok to continue. Note: With forced failover, you may encounter data loss. To avoid data loss, the data synchronisation needs to be completed before initiating the failover using "sp_wait_for_database_copy_sync"

b) Once the failover the complete, you can notice the database in "Southeast Asia" region as primary and secondaries in "Australia East" region becomes readonly.

c) The failover is now complete.