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

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.

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.

Operational data store (ODS) 

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.

Amazon Athena

Overview of Athena
Amazon Athena is an interactive query service, which developers and data analysts use to analyze data stored in Amazon S3. Athena’s serverless architecture lowers operational costs and means users don’t need to scale, provision or manage any servers.

Amazon Athena users can use standard SQL when analysing data. Athena does not require a server, so there is no need to oversee infrastructure; users only pay for the queries they request. You don’t even need to load your data into Athena, just need to point to their data in Amazon S3, define the schema, and begin querying.

To get started, just log into the Athena Management Console, define your schema, and start querying. Amazon Athena uses Presto with full standard SQL support and works with a variety of standard data formats, including CSV, JSON, ORC, Apache Parquet and Avro. While Amazon Athena is ideal for quick, ad-hoc querying and integrates with Amazon QuickSight for easy visualization, it can also handle complex analysis, including large joins, window functions, and arrays.

Some Athena Facts

• Athena supports only EXTERNAL tables, when you drop a table in Athena, only the table metadata is removed; the data remains in Amazon S3
• Athena uses an approach known as schema-on-read
• Athena does not modify your data in Amazon S3
• Athena uses Apache Hive to define tables and create databases, which are essentially a logical namespace of tables
• Athena can only query the latest version of data on a versioned Amazon S3 bucket, and cannot query previous versions of the data.
• Athena does not support querying the data in the GLACIER storage class
• Athena performs full table scans instead of using indexes
• Athena supports ACID-compliant.
• Athena is case-insensitive and turns table names and column names to lower case.
• Athena table, view, database, and column names cannot contain special characters, other than underscore (_)

How e-commerce companies can handle stampede of shoppers on Annual Sale Day?

Most of the world top  e-commerce companies host annual sale like Amazon - Prime Day, Walmart-  Black Friday Online Deals and Flipkart - Big Billion sale. No matter how much these marketplaces prepares, the full scale of consumer activity can only be known when the anticipated day arrives, often shocking expectations causing spike in transactions in span of a single second.

To handle such spike, these marketplace uses distributed database designed to serve online transaction processing. But it is again limited to individual machine capacity of database storage engines to handle a spike in transactions.

One solution is  effective use of caching and  shared storage design to improve its scalability and apply machine learning methods to predict spike in transactions to emulate the workload and analyzing QPS (queries per second) performance in the performance testing.

How do I uninstall and reinstall MySQL?

You did MySQL install of Ubuntu 16.04 Server and due to some reasons MySQL is not starting up and you want to uninstall and reinstall it.


Pls follow below procedures for MySQL reinstallation.

apt-get remove -y mysql-*
sudo apt-get purge mysql* sudo apt-get autoremove sudo apt-get autoclean
sudo apt-get remove dbconfig-mysql

Then upgrade MySQL distribution sudo apt-get dist-upgrade

Then install MySQL sudo apt-get install mysql-server
Grant All permissions
https://avishkarm.blogspot.com/2017/04/how-to-allow-mysql-remote-access-in.html

That's all.

MongoDB Data Migration, Backup and Restore

MongoDB is one of the most popular NoSQL database engines. Managing MongoDB production environment requires back it up, restore data, etc.  In case of converting MongoDB to SSL, move data from MongoDB on one server to another server, we can use import/export or backup/restore.

Importing and exporting a database means dealing with data in a human-readable format, compatible with other software products. In contrast, the backup and restore operations create or use MongoDB-specific binary data, which preserves not only the consistency and integrity of your data but also its specific MongoDB attributes. Thus, for migration its usually preferable to use backup and restore as long as the source and target systems are compatible.

Import/export:

MongoDB uses json and bson (binary json) formats for storing its information. Json is the human-readable format which is perfect for exporting and, eventually, importing your data.Json does not support all the data types available in bson and there will be the so called 'loss of fidelity' of the information.

Export:

sudo mongoexport --db mydb -c collections--out newdbexport.json

2019-05-06T15:47:30.931-0700    connected to: localhost

2019-05-06T15:47:31.931-0700    [........................]  mydb .collections  0/10234  (0.0%)

2019-05-06T15:47:32.932-0700    [#######.................]  mydb .collections  6100/10234  (31.5%)

2019-05-06T15:47:33.827-0700    [########################]  mydb .collections  10234/10234  (100.0%)

2019-05-06T15:47:33.828-0700    exported 10234 records

Import:

sudo mongoimport --db mydb --collection collection --file  newdbexport.json

While importing json file,you don't have to worry about explicitly creating a MongoDB database. If the database you specify for import doesn't already exist, it is automatically created. In MongoDB the structure is automatically created upon the first document (database row) insert.

 Backup/Restore:

Importing your data. using export/import json file have possibility of 'loss of fidelity' of the information.J son does not support all the data types available in bson and tit is advised to use 

mongodump and mongorestore to take (and restore) a full binary backup of your MongoDB database.

Backup:

Dump a collection to a BSON file.

mongodump -h hostname -d dbname-c collectionname-o

If you want to dump all collections in one go, simply omit the "-c collectionname" argument in the invocation below.

mongodump -h hostname -d dbname-c collectionname-o

Restore:

For restoring MongoDB we'll be using the command mongorestore which works with the binary backup produced by mongodump.

mongorestore -d mydb  /root/dump/mydb/collections.bson

Use with --drop to make sure that the target database is first dropped so that the backup is restored in a clean database.

sudo mongorestore --db newdb --drop /var/backups/mongobackups/01-20-16/newdb/

mongorestore -d mydb  /root/dump/mydb/collections.bson

2019-05-06T15:43:26.403-0700    checking for collection data in /root/dump/mydb/collections.bson

2019-05-06T15:43:26.435-0700    reading metadata for mydb.collections from /root/dump/mydb/collections.metadata.json

2019-05-06T15:43:26.452-0700    restoring mydb.collections from /root/dump/mydb/collections.bson

2019-05-06T15:43:27.280-0700    restoring indexes for collection mydb.collections from metadata

2019-05-06T15:43:27.284-0700    finished restoring mydb.collections (10234 documents)

2019-05-06T15:43:27.284-0700    done

root@ubuntu:~#

Verify the Collections Exists