When to use SQL DB vs SQL DW

There’s a lot of confusion around when to use SQL DB or SQL DW with a lot of people assuming that these are interchangeable; so, I wanted to try to address these.

SQL DW

Let’s talk about SQL DW first. There are some pretty hard and fast rules around SQL DW and when it should be used. SQL DW should only be used for reporting workloads where you have a proper data warehousing design for your database. It also is only going to be effective when you have a data warehouse that at least 2TB in size.

SQL DW came from APS (which used to be called PDW). One of the things that made APS (and PDW) so successful was that you needed consulting hours from an APS consultant to implement it successfully.  With SQL DW you can just buy a SQL DW from the Azure Portal, and off you go. There’s no need to work with a consultant; you can just build your tables and start reporting. However, if you don’t fully grasp data replication, data partitioning, and query processes (and other things) in SQL DW, then odds of a successful implementation to SQL DW are going to be slim to none.

SQL DB

SQL DB is going to be the best use case for a few different workloads.  Any OLTP workloads are going to be a good fit for SQL DB. If you’re coming from an On-Prem solution (SQL Server, Oracle, MySQL, etc.), then you may need to do some architecture changes in your application to get the best performance at scale from your application. Not all applications are going to require changes to the database architecture, but some will. Going into a cloud migration project with the assumption that there will be some database architecture work is a good thing. It will mean that if there is, you’re ready for the architecture work. If there doesn’t need to be any, then great.

SQL DB by itself supports databases up to 4TB in size. There is now a feature called Hyper-Scale which will let your databases in SQL DB go as large as they need to be (there are some additional costs to use Hyper-Scale).

Data warehouses and other reporting workloads can go into SQL DB as well. Smaller data warehouses that simply aren’t big enough for SQL DW (that will be under 2 TB in size) are great to put in SQL DB.  They’ll perform well, and you can use things like PowerBI to report off of them, and the cost for SQL DB will be much more attractive compared to SQL DW.

Some Migration Numbers

Doing some architecture work can potentially save you a ton of money as well.  I was doing a POC recently for a client as they were looking at an Azure Migration. They have ~80,000 databases that they were looking to move.  Phase 1 on the project was going to be to move the existing SQL Server’s to VMs in the cloud.  The cost for this was ~$80k a month.  By moving these databases into SQL DB (as Phase 2) they would be able to reduce their monthly cost to ~$37k a month.  Assuming that they need to spend $20k on developers a month to do this change, and it takes the developers six months to do the work ($120k in development costs), that’s a 3-month ROI before the company starts saving money.

What to use when

What it comes down to is that SQL DW is specialized with specific requirements (data warehousing with a well-defined schema and at least 2 TB in size) which SQL DB is more of a catch-all for everything else.

Hopefully, that helps explain when to use each one.  If you’ve still got more questions, then the team at DCAC would love to help you out.

Denny

The post When to use SQL DB vs SQL DW appeared first on SQL Server with Mr. Denny.