Ways of Dynamic Data Masking SQL Server Fields
Dynamic data masking feature in SQL Server provides the possibilities of hiding sensitive information in the database.
Always on Availability group is one of the high availability and disaster recovery solution in SQL Server. Always on Feature have introduced in SQL Server 2012 Version.
SQL Server 2012
Only 4 secondary replicas can be available in SQL server 2012 including one primary replica.
Only two automatic failovers will takes place in In SQL Server 2012.
Three synchronous mode replicas available in SQL server 2012.
SQL Server 2014
8 Secondary Replicas are there in SQL Server 2014 including Primary.
Two automatic failovers will takes place in SQL server 2014.
Three synchronous replicas are there in SQL server 2014.
Read intent was introduced in SQL Server 2014.
Read intent routing command
ALTER AVAILABILITY GROUP [AG2]
MODIFY REPLICA ON N’WIN2012R2-VM1\GeoPITS_PR’
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(‘WIN2012R2-VM2\GeoPITS_SR1’,’WIN2012R2-VM3\GeoPITS_SR2',’WIN2012R2-VM1\GeoPITS_PR’)));
In this read intent routing the read request will direct based on the order, which you have given in above command. Based on the above routing list all read request will direct to the ‘WIN2012R2-VM2\GeoPITS_SR1’ which is give in first in above routing list in case if these secondary replica is next available the read requests will direst to next replica which is second(WIN2012R2-VM3\GeoPITS_SR2) in routing list. In both first and second secondary replicas not available read requests will go next secondary replica based on routing list.
SQL server 2016
The Enhancements of ALWAYS ON in SQL server 2016 given below
->Round-robin Load-balanced readable secondary’s.
-> Direct seeding of new DB replicas.
-> supports Azure integration.
-> Always on Supports TDE.
->Support for Distributed Transactions (DTC).
-> SQL Server Standard Edition support always on Availability group(Basic Availability Group).
->‘ALWAYSON’ renamed as ‘ALWAYS ON’.
Round-robin load balancing in readable secondary’s:
In SQL server 2016 round-robin load balancing concept was introduced. Before SQL server 2016 there is no round-robin load balancing. Main use of the round-robin read requests will distributes equality to all secondary replicas as show in below figure.
Prior of SQL server 2016 the read request will connect to the secondary replica which will responds quickly for listener.
Automatic Seeding:
Automatic seeding is new enhancement of alwayson in SQL server 2016.
SQL Server performs a full database backup using Microsoft SQL Server Virtual Device Interface (VDI) full database.
This VDI database backup is streamed through the network to all available secondary replicas.
Secondary replica restores this streamed backup.
Once the database restoration is complete, it is added into the availability group.
GRANT CREATE ANY DATABASE permission needed.
Supports Azure:
In SQL Server 2016 Always on support AZURE for Secondary replica.
Recommended is secondary replica in Asynchronous mode.
Always on Supports TDE:
Now always on supports for Encrypted Databases.
However authentication requires when adding encrypted database to Availability Group.
Support for Distributed Transactions (DTC)
Always on supports the Distributed Transaction in SQL server 2016, now we register a resource manager per availability database. The resource manager works with DTC services to track distributed Transactions. Because of this now we can guarantee for integrity of a distributed Transaction.
To use DTC with Always on we require Windows Server 2012 with KB3090973.
We need to create Availability Group with the WITH DTC_SUPPORT =PER_DB.
Database level health detection failover:
In SQL server 2016 always on one of the new enhancement is DATABSE level Health detection failover was introduced.in prior SQL server 2016 only Availability group failover takes place when the instance fails. From SQL server 2016 availability group failover takes place even on database fails or not available for long time.
We can enable this feature using below commands.
ALTER AVAILABILITY GROUP [AG1] SET (DB_FAILOVER = ON);
ALTER AVAILABILITY GROUP [AG2] SET (DB_FAILOVER = OFF);
We can see how to enable Database level Health detection for Availability group in below pictures for SQL server 2016 when creating new group.
SQL Server 2017
Read-scale Availability Group in SQL server 2017 and later we can create the availability group without windows failover cluster.
But it will not provides the high availability.
Basic Availability Group
Basic Availability group feature was introduced in SQL server 2016, which was supports SQL Server 2016 standard edition. Basic availability behaves like mirroring feature.
In below picture shows how to enable Basic availability in SQL Server 2016 and higher SQL server Standard edition.
Limitations of Basic Availability Group:
Secondary replica not allows read operations
No backup in secondary replica
Basic availability group supports only in standard editions in 2016 and later
Basic availability group supports failover environment for a single database.
In Basic availability group we have only on secondary replica.
