Before starting any business, you might think how to equip your data, how to maintain and manipulate your data (as it will be there in abundance). You might go and ask some IT expert what to do, how to do and a bunch of other questions. The IT guy may suggest you to build your own Data Warehouse, to which you will ask if it is cost effective or not, what’s the upfront cost, what will be the maintenance charges, etc.
For this reason Azure has comes up with a solution called SQL Data Warehouse as a Service (DWaaS). DWaaS is one amazing solution for the organizations, which are just starting or in a process to start. DWaaS is the first enterprise class cloud Data Warehouse, which can grow or shrink. It offers full SQL server experience in cloud, which customers expect. The organization should not worry about spending the upfront cost and maintaining the hardware or software resources they buy.
Architecture of SQL Data Warehouse
For users, it’s like sending data to a database, but underneath SQL Data Warehouse runs “Massive Parallel Processing (MPP) Engine”, which helps in dividing the query send by user to Control Node.
Control Node: When a command is passed to Control Node, it breaks down the query for faster computing into set of pieces and passes on to other nodes of the service.
Compute Node: Like Control Node, Compute Node is powered by SQL Databases. The job of Compute Node is to serve as power for the service and underneath data it is loaded in SQL Data warehouse, which is distributed across the node of service.
Storage: The storage media used for SQL Data Warehouse Blobs. The best part for this is, when user will interact with data, it will directly fetch from Blobs ( The blob storage is one of the best storage options in Azure when the data is enormous amount ).
Difference between on-premises Data Warehouse and Cloud Data Warehouse as a Service
On-Premises Data Warehouse
Cloud Data Warehouse as a Service
|Reliable, but not much||More reliable|
|Not scalable||More scalable|
|Faster, but may fail in any point of time (in care of hardware failure)||Faster and will up always (SLA of 98% is provided)|
|It will take time||Within no time it will deploy|
|Lots of capital required to setup on-premises data warehouse as a service||You have to pay what you use|
Why should we go for SQL Data Warehouse as a Service in Azure?
Reason 1: It can handle and scale petabytes of data and is highly reliable for all data warehouse operations.
Reason 2: You can scale up and scale down the services as per your requirement. Suppose if the traffic is high in day time, you can add any number of machines you want and when the traffic is low, you can remove number of machines.
Reason 3: The reliability of SQL Data Warehouse is estimated to be 98% i.e. if 1000 queries are thrown, then there is a slight chance that 20 queries might fail to execute ( SQL Data Warehouse will be in preview, it will grow in the near future and so will the reliability ).
How secure SQL Data Warehouse?
As we know security is a two way process, the users who is using SQL Data Warehouse should secure his/her laptop. If the password is key logged by Man in the Middle attack, then the cloud provider will not be able to do anything. For security reason Azure is providing some measures like:
Connection Security: It’s one of the measures where we can set firewall rules and connection encryption. Firewall rules will be applied for both server and database. Until and unless any IP, which is whitelisted can’t enter into database. We can also set server-level firewall using PowerShell (this can be done in Azure Classic Portal).
Authentication: It refers to how you prove your identity when user enters and getting connected to database. At present SQL Data Warehouse support SQL Authentication with username and password.
Authorization: It refers to what we can do with SQL Data Warehouse database, which will allow user to do anything in database. The best practice will be to limit the access to the user.
Encryption: Azure SQL Data Warehouse provides “Transparent Data Encryption” to secure our data when it is at rest or stored in database files and backups. Here TDE provides file level encryption.
Stay tuned for more blogs on Azure and if you have any queries or comments please feel free to post.