December 01, 2021

Data Engineering Analogy

Data is the new oil. data is the economic  fuel of the future. Like oil, data must be refined, cleansed.

Data is the new energy. You consume data irrespective of from which source it came and enriched similar to energy , it come from windmills stored in power station and consumed by end user and pay per use.

Data is the new sunlight. Like solar rays, Data will be everywhere and underlie everything



November 10, 2021

Always On Availability Group - Offloading Reporting Approach

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and corresponding secondary databases which continuously back up databases and their transaction logs on a regular basis.

 The Source database sends transaction log records to secondary database and then applies them to secondary database. Data synchronization occurs between the primary database and its corresponding secondary database so that changes are applied in real-time (or near-real-time) while users are still in the database. The secondary databases server must have the same specs as the primary databases server. AlwaysOn is implemented on a per-database basis. We don't need to add all databases on source database server. Only those databases which need for real time reporting need to configure for AlwaysOn.

This secondary database can be used for real-time reporting purpose. Also, if we would like to prevent read-only workloads from running on the primary database, we can use secondary database to allow only read-write access for running complex queries.

 Benefits

  1. Real time Reporting

  2. Enhanced Performance

  3. Complex queries can run without compromising database performance.

  4. High Availability (HA) with manual failover 

Infrastructure and Software needed for AlwaysOn

  1. Windows Server 2012 or later versions.

  2. SQL Server 2014 Enterprise edition or later versions

  3. Since we can run a query on a secondary replica, all SQL servers need to have license.

  4. Both SQL & OS Editions, Versions should be at same level on all participating nodes.

  5. All replicas in your AlwaysOn group must be in the same windows domain. They must be able to communicate with each other.

  6. SQL Server port must be opened at firewall level for communication between replicas.

  7. Ensure that each computer is a Node in a Windows Server Failover Clustering (WSFC)

Cost and Budget

For setting AlwaysOn, a minimum of 2 SQL servers with Enterprise edition (Source database and Secondary database)  are required.

 Analytics workload or read-only queries running on the primary replica can be run on the secondary replica without requiring any changes.




Comparison: AlwaysOn Vs Database Mirroring



AlwaysOn

Database Mirroring

Reporting

Readable replicas allow real time reporting

The mirror database is always in a state of recovery, which essentially means it can’t be used while mirroring is active. You can create a snapshot of the mirror database to use for reporting, but the snapshot is a point-in-time copy making it unsuitable for real time reporting purposes

Read-only routing

Supports the re-routing of read-intent connection requests to a readable secondary replica. 

This feature in not available in Database Mirroring.

Performance Considerations

Performing highly CPU, I/O-intensive read-only workloads on the secondary databases, it will improve the database performance of Source database.

Synchronous mirroring requires a 2-phase commit to take place which could introduce delays on the principal.

Additional H/W

Integrated with Windows Server Failover Clustering (WSFC)

Database mirroring doesn’t require WSFC but if you want to use it in High Safety mode for automatic failover, then you need to use a third SQL Server system as a witness.

Feature support

Only available with SQL Server Enterprise edition

Available with both SQL Server Enterprise and Standard edition. Synchronous mirroring is available only on Enterprise edition.

Risks

All active workloads must be licensed and requires additional cost of SQL server license.

Database Mirroring is available in SQL2012 and SQL2014 but will be deprecated in future SQL Server releases.

Schema or data changes

You cannot make schema or data changes on the secondary databases which limits creating additional indexes on secondary databases

The mirror database is always in a state of recovery, which essentially means it can’t be used while mirroring is active

Benefits

Provides High availability for SQL Server databases. Capacity of replicate databases to facilitate read only access which makes it possible for them to be used for multiple purposes at the same time. You can use a secondary database for reporting purposes.

Provides a high availability of the database and Do not need any singular hardware.

 


Understanding Relational database scaling

 Many applications today still rely on traditional relational databases like MS SQL, Oracle, MySQL or PostgreSQL for data storage and data processing. With the growing amount of data and new workloads that are made with these database systems, achieving scalability is a huge challenge for relational databases.


In this article, I will discuss addressing the need for database scalability, techniques to scale a relational database.


      

  1.  Addressing the need for database scalability:


  • You’ve reached the capacity number of users that your application is able to handle.

  • Application is beginning to slow down and error out. 

  • Network requests start timing out

  • Database queries are taking a while to execute

. 

  1.  Database scaling solutions:


  • Cache database queries in Memory

  • Replication 

  • Database sharding

  • Denormalization

  • SQL tuning


  1.  Horizontal scalability Vs Vertical scalability:


  • Vertical Scaling -  This process involves adding more power such as CPU and disk power to enhance your storage process.

  • Adv of Vertical Scaling -  Simple and no code change

  • Disadv of Vertical Scaling -  Expensive and Difficult to perform when server exceeds maximum load

  • Horizontal Scaling -  This process involves increasing  the number of machines and dividing the data set and distributing the data over them and improving both read and write performance.

  • Adding read replicas to handle Read-Heavy workloads. Reading from the cache before hitting the primary DB to reduce database load.

  • Adv of Horizontal Scaling -  Cheap and better performance

  • Disadv of Horizontal Scaling -  Making joins is difficult, as it may involve cross-server communication




  1.  Designing the database architecture to achieve scalability:

  • Decentralized data management architecture

  • Each individual microservice have its own separate database

  • Deploy and scale your microservices independently.


  1.  Recent scalability enhancements  in RDBMS

  • Connection Pooling

  • Command Query Responsibility Segregation (CQRS)

  • Multi Primary Replication

  • Data Centre Wise Partition


  1.  Implementing scaling solutions introduces the following complexities:

  • Adding new features takes longer

  • The system becomes more complex with more pieces and variables involved

  • Code can be more difficult to test

  • Finding and resolving bugs becomes harder

September 17, 2021

Azure SQL Database - Encryption at rest




What is encryption at rest?

Encryption is the secure encoding of data used to protect confidentiality of data.

The purpose of encryption at rest

Encryption at rest provides data protection for stored data (at rest). Encryption at rest is designed to prevent the attacker from accessing the unencrypted data by ensuring the data is encrypted when on disk. If an attacker obtains a hard drive with encrypted data but not the encryption keys, the attacker must defeat the encryption to read the data. 

Azure SQL Database

Azure SQL Database currently supports encryption at rest for Microsoft-managed service side and client-side encryption scenarios.

Support for server encryption is currently provided through the SQL feature called Transparent Data Encryption. Once an Azure SQL Database customer enables TDE key are automatically created and managed for them. Encryption at rest can be enabled at the database and server levels. As of June 2017, Transparent Data Encryption (TDE) is enabled by default on newly created databases. Azure SQL Database supports RSA 2048-bit customer-managed keys in Azure Key Vault. 

Client-side encryption of Azure SQL Database data is supported through the Always Encrypted feature. Always Encrypted uses a key that created and stored by the client. Customers can store the master key in a Windows certificate store, Azure Key Vault, or a local Hardware Security Module. Using SQL Server Management Studio, SQL users choose what key they'd like to use to encrypt which column.

For Azure SQL Managed Instance, the TDE protector is set at the instance level and is inherited by all encrypted databases on that instance.

To provide Azure SQL customers with two layers of encryption of data at rest, infrastructure encryption (using AES-256 encryption algorithm) with platform managed keys is being rolled out. This provides an addition layer of encryption at rest along with TDE with customer-managed keys, which is already available. For Azure SQL Database and Managed Instance, all databases, including the master database and other system databases, will be encrypted when infrastructure encryption is turned on.

Transparent data encryption (TDE) helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest. It performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application. By default, TDE is enabled for all newly deployed Azure SQL Databases and must be manually enabled for older databases of Azure SQL Database. For Azure SQL Managed Instance, TDE is enabled at the instance level and newly created databases. TDE must be manually enabled for Azure Synapse Analytics.


TDE performs real-time I/O encryption and decryption of the data at the page level. Each page is decrypted when it's read into memory and then encrypted before being written to disk. TDE encrypts the storage of an entire database by using a symmetric key called the Database Encryption Key (DEK). On database startup, the encrypted DEK is decrypted and then used for decryption and re-encryption of the database files in the SQL Server database engine process. DEK is protected by the TDE protector. TDE protector is either a service-managed certificate (service-managed transparent data encryption) or an asymmetric key stored in Azure Key Vault (customer-managed transparent data encryption).

How to check if existing database is Encrypted?

Below query returns information about the encryption state of a database and its associated database encryption keys. 

select db_name(database_id),key_algorithm,encryption_state_desc 

from sys.dm_database_encryption_keys

May 14, 2020

SQL Server 2019 in Big Data World!!!

SQL Server 2019 provides Scalable big data solution


  • Read, write, and process big data from Transact-SQL or Spark.
  • Easily combine and analyze high-value relational data with high-volume big data.
  • Query external data sources.
  • Store big data in HDFS managed by SQL Server.
Other features:
  • In-memory online transaction processing
  • SQL Server now has the capability to stop an index rebuild operation in progress, keep the work that has been done so far and resume at some other point in time.


January 17, 2020

Data warehouse vs. Data mart vs. Operational Data Stores vs. Data lake


Data warehouse(DW)

A data warehouse is a central repository of subject-oriented, integrated, time-variant, and non-volatile collection of data from one or more disparate sources in support of management’s decision-making process. They store current and historical data in one single place that are used for creating analytical reports.
Subject-oriented implies that the data is organized around subjects such as customers, products, sales, etc.
The data warehouse is integrated in the sense that it integrates data from a variety of operational sources and a variety of formats such as relational database management systems, legacy database management systems, and flat files. It may require data cleansing for additional operations to ensure data quality before it is used in the DW for reporting.Time variant refers to the fact that the data warehouse essentially stores a time series of periodic snapshots. Operational data is always up-to-date and represents the most recent state of the data elements, whereas a data warehouse is not necessarily up to date but represents the state at some specific moment(s) in time.
Non-volatile implies that the data is primarily read-only and will thus not be frequently updated or deleted over time
.

Data mart(DM)



The data mart is a subset, condensed and more focused version of the data warehouse and is usually oriented to a specific business line or team and provide focused content.


Data marts improve end-user response time by offloading complex queries, and therefore workloads and allowing users to have access to the specific type of data they need to view most often by providing the data in a way that supports the collective view of a group of users.
An ODS is a database a designed to integrate data to from multiple sources for additional operations on the data, for reporting, controls and operational decision support . An ODS is still subject oriented, integrated and time variant, but volatile in nature.


An ODS is usually designed to contain real-time or near real- time data as it is received from the respective source systems so that analysis tools can query the ODS data as it is opposed to the data warehouses not containing up-to-date data and time-consuming transformation and loading operations.


The ODS provides access to the current, fine-grained and non-aggregated data, which can be queried in an integrated manner without burdening the transactional systems.

Data Lake

A data lake is usually a single store of all enterprise data including raw copies of source system data and transformed data used for tasks such as reporting, visualization, advanced analytics and machine learning. A data lake can include structured data from relational databases (rows and columns), semi-structured data (CSV, logs, XML, JSON), unstructured data (emails, documents, PDFs) and binary data (images, audio, video).


A key distinguishing property of a data lake compared to Data warehouse is that it stores raw data in its native format, which could be structured, unstructured, or semi-structured. This makes data lakes fit for more exotic and “bulk” data types that we generally do not find in data warehouses, such as social media feeds, clickstreams, server logs, and sensor data.


A data lake collects data emanating from operational sources “as is,” , either no or only very limited transformations (formatting, cleansing) are performed on the data before it enters the data lake and often without knowing upfront which analyses will be performed on it, or even whether the data will ever be involved in analysis at all.


Data Lake Vs Data warehouse:


The data schema definitions are only determined when the data is read (schema-on-read) instead of when the data is loaded (schema-on-write) as is the case for a data warehouse. Storage costs for data lakes are also relatively low because most of the implementations are open source solutions that can be easily installed on low-cost commodity hardware.


Since a data warehouse assumes a predefined structure, it is less agile compared to a data lake, which has no structure. Also, data warehouses have been around for quite some time already, which automatically implies that their security facilities are more mature.


Because the data within data lakes may be uncurated and can originate from sources outside of the company's operational systems, it isn't a good fit for the average business analytics user; rather, data lakes are the playground of data scientists and other data analytics experts. i.e. a data warehouse is targeted toward decision makers at the middle and top management level, whereas a data lake requires a data scientist, which is a more specialized profile in terms of data handling and analysis.



Stop Memorizing SQL Part 3 : A Practical SQL Cheat Sheet for Beginners

 A Practical SQL Cheat Sheet for Beginners When learning SQL, students often struggle to remember the correct syntax during exams or practic...