Understanding Your Options for Running SQL Server on Azure

Published On: 2019-11-11By:

I was at Microsoft Ignite last week, and I spent the better of part of 3 days working in the “SQL Server” booth. I put SQL Server in quotes, because in 2019 that term is stretched. Our booth covered SQL Server 2019, Arc (which you should read about because it’s really cool, but is the subject of another post), SQL on Edge, SQL Server Big Data Clusters, Azure SQL Database, and Azure SQL Database Managed Instance, and a few other things that I’m too tired to type. And that doesn’t even include Microsoft Spectre (which is the artist formerly known as Azure SQL Data Warehouse)

image_thumb.png

My big takeaway from working the booth, was how most of the customers I spoke to, didn’t understanding the differences between the various platform options. In fact, I think I took the following question over 100 times: “We’re currently on-premises, but moving to the cloud, can you explain the difference between Azure SQL Database, Managed Instance, and SQL Server in a VM?” The fact that we had to answer that so much, isn’t a failing on the part of the customers, as it is Microsoft and us MVPs not communicating the message effectively. So, in order to do my part, I’m going to write a post (I also wrote a Redmond column last month, but this post will go into some more detail). I’m not going to talk about pricing in detail in this post, as it’s effectively the same as the two services.

First Things First—Do you Know What PaaS Means?

Azure SQL Database and Managed Instance are both known as Platform as a Service (PaaS) offerings. This means you the DBA will manage a lot less than you typically would on either an on-premises SQL Server, or even a VM running SQL Server in Azure on the Infrastructure as a Service (IaaS) offering. If you run one of the PaaS offerings, Microsoft is responsible for the following tasks:

  • Backup of your database and transaction logs
  • High Availability within your Azure region, with a financial guarantee (the number of 9s depends on config, but it’s pretty solid)
  • Installation, configuration, and patching of SQL Server software
  • Installation, configuration, and patching of Windows server
  • Consistency checks of your database
  • Performance monitoring of your database (This is configured for you, you still have to react to the numbers)

What tasks does that leave you to do?

  • Configuring disaster recovery across Azure regions—while this is just a few clicks, you get to convince your boss to pay for it, and it’s not turned on by default
  • Performance tuning—getting utilization down by query optimization can save your company money. It’s a quantifiable part of your job now
  • Network architecture—you need to be able to make sure your virtual networks in Azure can all talk to your database services, and in a secure fashion
  • User Management and Security (though there are some security benefits in PaaS)
  • Helping your business derive value from the data you are storing in the database

Ok, Tell Me About Cloud Offerings

I’m going to start out by talking about running SQL Server in an Azure VM. Why would you choose to do this, when the aforementioned PaaS services do all of this magic for you?  The main reason I can think of is vendor support. Your business runs some high quality ISV software that requires you to remain on SQL Server 2012 CU1 (just kidding, you know it’s actually SQL 2005 RTM, and Windows 2003), and you can only run this mission critical conference room projection software on that version of SQL Server.

The other reasons why you might choose to run SQL in a VM besides vendor support (which is common) is that the size of your database doesn’t work well with the current limits in Azure PaaS (which is 8 TB in Managed Instance, and effectively unlimited in Azure SQL Database Hyperscale), or that you are willing to trade off some availability for reduced cost of running in a standalone VM.

What do I mean by costs? While PaaS offerings include high availability, that’s something Microsoft has to include in your pricing (otherwise, their shareholders, including me, would murder them), so compared to a VM that is standalone, the PaaS offering will commonly be more expensive. To get a better cost estimate, you should really multiply your IaaS costs by 3. (Also, VMs have pretty good availability—it can be enough, especially for departmental apps).

Azure SQL Database/Azure SQL Database Hyperscale

This is truly one of the OG Azure offerings, dating back to 2009/10. It is really designed to be a standalone database offering. Think of an app, that needs a single database, and wants minimal effort around spinning that up, and you have the right answer for Azure SQL Database. The cost for getting started is also really cheap at $5/month for a basic DB (it can be way, way more than that, the P15 database is $15k USD/month, and there’s a new M-series offering that will be even more). Effectively, an Azure SQL Database is a single user database running in a SQL Server instance somewhere in the cloud. There’s a feature called elastic pools that confuses this a little bit.

Elastic pools are a group of Azure SQL Databases that share the same resources. This is effectively a SQL Server instance somewhere in the cloud that hosts multiple user databases. The elastic pool feature is mostly designed for applications where each customer gets a copy of the same database, and workload is mixed at different times of the day, so resources are utilized most effectively.

Azure SQL Database has some tools called elastic query, elastic transactions, and elastic jobs, that allow you to perform actions across multiple databases (elastic is a bit of a misnomer here, there’s no direct connection to elastic pools), but in general cross-database queries using classical three part naming database.schema.object do not work in Azure SQL Database, so if you need to joins or complex queries across databases, this probably isn’t your cloud product.

Way back when Azure SQL Database was size limited to 5 GB, then ultimately 4 TB, until the hyperscale architecture was developed. Hyperscale is a completely model for physical database infrastructure design (read more about it here, it’s really cool) and should be able to scale to near petabyte scale. If you’ve got more than that much data in a single database, we really need to talk. The one challenge with SQL DB and database size, is that you can’t restore to it, you have to use a database export and load data using a BACPAC, so it can be very slow for very large databases. My guidance here is that if you have a very large on-premises database that you’d like to move, Managed Instance may be a better choice, as the migration path is easier. If you are building a new application in the cloud, Azure SQL Database is frequently the best choices.

There are a few other limitations of on-premises features that Azure SQL Database doesn’t support. Those are as follows:

  • Common Language Runtime (CLR)
  • Change Data Capture
  • Cross-Database Transactions
  • Database Mail
  • Filestream
  • Linked Servers* (sort of—you can connect to external data sources)
  • Replication Source (you can publish to, but not from an Azure SQL DB)
  • Resource Governor
  • Service Broker
  • SQL Server Agent

Of those features, the biggest one is SQL Server Agent. There are a number of different ways to schedule jobs to run in Azure, the most common being Azure Automation, which allows for PowerShell or Python. You can also have an Agent job on a SQL VM running full-blown SQL Server, though that seems less practical.

I mentioned new application development as a target for Azure SQL Database. Since, SQL DB allows for your database to be a replication target, it can also be a good use case for reporting, particularly for field users who are afar from the home office. You can leverage either geo-replication, or just have multiple replication targets, and allow field users to report against them.

From a network perspective, your SQL Database is on the internet, with a firewall in front of it (either at the server or the database level). You can also elect to only allow traffic from a Virtual Network within Azure, however your database is still on shared networking infrastructure (albeit secured).

Azure SQL Database Managed Instance

This service came much later, being announced at Build in 2017, and going GA last year. Managed Instance was specifically designed to make it easier to move from on-premises SQL Server to a PaaS services. One of the initial differences was that Managed Instances uses a virtual core-based pricing model (Azure SQL Database still offers a legacy DTU based model), so you pay a price per core, depending on your tier.

The two tiers of Managed Instances (and vCore based Azure SQL Database) are general purpose and business critical. The two key differences between these tiers are performance and high availability models—general purpose (GP) uses networked SSDs and will generally have IO latency in the 5-10 ms range. If you are using GP, you need to read this blog post on how to optimally configure your storage, as other it can be really painful.  GP also uses the underlying storage model for its high availability. Business critical (BC) uses the local SSD that’s in each server host for storage, which is lower latency than a network call, and effectively uses an AlwaysOn Availability Group with four replicas for its availability model. You can also query the secondary replica on BC, by using the application intent=read only connection syntax in the SQL client.

In terms of migration, you can restore a backup (from Azure Blob Storage using the RESTORE FROM URL syntax) into Managed Instance. For a more real-time migration strategy, you can use the Data Migration Services tool which will allow for an almost zero-downtime migration. There’s been talk of allowing log shipping, or at least “log shipping” (via an API), but last I checked (and I’ll check again) you had to use DMS.

Features in MI are much closer to on-premises SQL Server. The big gap closers between MI and SQL DB are that the SQL Agent is there, CDC is there, and cross-database queries can be performed. The following list is the features that are included in Managed Instance and not in SQL DB:

  • Copy-only backup
  • CDC
  • CLR
  • Cross-Database Queries/Transactions
  • DBMail
  • Linked Servers (Only to MI and other SQL Servers)
  • Resource Governor
  • Service Broker (within instance)

The gaps to on-premises are as follows:

  • R Services
  • SQL Server Analysis/Integration* (there’s a way to do this with Azure Data Factory, but it costs money)/Reporting Services
  • Filestream

There are a few other differences, but I went for the ones that I see as most commonly affecting customers. One thing to note for both MI and Azure SQL DB is that they rely on Azure Active Directory and not on-premises Active Directory to provide authentication. I hadn’t seen this as a big gap with my customers, however it came up a few times at the booth last week, and Microsoft is researching options in this space.

The other limitation Managed Instance currently faces, is that there is a maximum of 8 TB database total size currently. The Managed Instance team is in the process of implementing hyperscale, and this limit will go away when that happens, which will likely be sometime next year.

So, I’ve almost written 2000 words on this. So therefore it needs a tl;dr

Azure SQL Database—Use It If

  • You are building a new cloud based application
  • You want to build a reporting option for field users
  • You are building a customer facing data platform and you want simplified networking and auth

Azure SQL Database Managed Instance—Use it if

  • You are migrating 3rd party ISV apps to a PaaS model
  • You want a PaaS model, but don’t want to change any of your code
  • Your app depends on SQL Agent, or cross-database queries

SQL Server in a VM—Use It If

  • you are stuck on an old version of SQL Server
  • You have to use on-prem Active Directory for authentication
  • 9% availability is good enough for your application
  • You are one of those people who run SSAS/SSIS/SSRS/R Services/RDBMS/DQS/MDS all on the same server to maximize every penny of your licensing
  • You are the person who uses Filestream

 

 


Contact the Author | Contact DCAC

How to test for query regressions when upgrading

Published On: By:

One of the things that you want to test for when doing an upgrade of SQL Server is query regressions. Regressions happen when the new version of SQL Server makes a poor decision when running the same code that you can before.  Testing an application against the new version should always be done, even though everything is supposed to work perfectly. You never know until you test, and since Microsoft hasn’t tested your specific workload, that leaves it up to you.

Prepping for the test

This testing does require a test machine to run your workload against, but since we have the cloud, doing testing like this is easy, and you can just destroy the VM when you’re done with your testing.

To do this testing, we’re going to need a few things.  The first is a machine to run our test databases on. In the perfect world, it’ll be the same size as the production server that you’re going to be running the new production system on. It doesn’t have to be, but the closer, the better.  We also need a  backup of the production database that we’re going to restore on the test machine.

We also need a SQL Profiler capture from the production system. If possible, you want the profiler capture to have every possible query that can be run.  This could be guaranteed by capturing data for a day or two, or it could be guaranteed by someone clicking all the options in the app while the trace is running.  You need a profiler trace as you need to play it back, and the easiest way to playback a capture is SQL Profiler.  It’ll take a lengthy profiler script to create the trace that it needed, but you can see the script at the bottom of this post.  You will want to change the path that the trace is writing to.  The script will output a number when it runs; you’ll need this number to stop the trace when you are ready to stop it.  The script will create a bunch of trace files that are 1 Gig in size when you run the script.  Make sure that you’ve got plenty of drive space for the capture.

Once you’ve captured the data, stop the trace (the stop script is at the bottom of this post as well) and move the capture to the test server.  On the test server, you’ll want to install the version of SQL Server that we’re going to move to (Hopefully SQL 2017 or newer).  Once SQL is installed, restore your production database then turn on the query store for that database (database properties).  You’ll also want to increase the size of the query store, 1000 Megs should be enough, but if it’s not, go ahead and make it bigger than that.

Once the query store is enabled (do NOT change the compatibility level yet), we’re ready for the first run of our profiler trace.

Checking for regressions

If you open SQL Profiler on the test server and open the first file of the trace.  Once that’s done, click the “Replay” drop-down menu, and then click “Start.” It’ll then prompt you to connect to a server. Enter the name of the test server.  It’s going to give you the option to use multiple threads, and you’ll probably want to switch to this option.

Once you are happy with these settings, click “OK” to start the replay.  Depending on how big the trace is, this might take a while. At a client, when I did this recently, we captured data for about 15 minutes, and it took about 7 hours to replay.

Once the replay is done, change the compatibility level to whatever version you plan to change the database to (usually the newest compatibility level).

At this point, we need to rerun the trace again.  Once the trace is done, we can go into the query store and see if things regressed.  Microsoft has made looking for regressed queries very easy; there’s a query store report you can open up. If you open the “Regressed Queries” report, it’ll show you all the queries that had plans that regressed to do changes in the SQL Server engine.  You can then investigate these queries and hint them or rewrite them to fix them.  Once those queries are all fixed, the system can be upgraded.

In the case of a client system that I was working on recently we had to hint a few queries, but those hints weren’t available in the old version of SQL Server that we were moving off of, so the hints couldn’t be applied until after the upgrade was done, but we just built that into the upgrade plan so there would be a couple more minutes of outage while we added hints to a bunch of procedures, then we opened the gates and let users onto the system.

Depending on the regressions that are found you might need some development time to see what’s going on (hint, we can help you figure out the regressions and what needs to be hinted and what needs to be rewritten).

Denny

 

To start the trace
declare @p1 int
declare @maxfilesize bigint = 1024
exec sp_trace_create @p1 output,2,N'c:\temp\Replay',@maxfilesize,NULL
select @p1
exec sp_trace_setevent @p1,14,1,1
exec sp_trace_setevent @p1,14,9,1
exec sp_trace_setevent @p1,14,10,1
exec sp_trace_setevent @p1,14,11,1
exec sp_trace_setevent @p1,14,6,1
exec sp_trace_setevent @p1,14,12,1
exec sp_trace_setevent @p1,14,14,1
exec sp_trace_setevent @p1,14,3,1
exec sp_trace_setevent @p1,14,35,1
exec sp_trace_setevent @p1,15,3,1
exec sp_trace_setevent @p1,15,35,1
exec sp_trace_setevent @p1,17,3,1
exec sp_trace_setevent @p1,17,35,1
exec sp_trace_setevent @p1,10,3,1
exec sp_trace_setevent @p1,10,35,1
exec sp_trace_setevent @p1,12,3,1
exec sp_trace_setevent @p1,12,35,1
exec sp_trace_setevent @p1,15,11,1
exec sp_trace_setevent @p1,15,6,1
exec sp_trace_setevent @p1,15,9,1
exec sp_trace_setevent @p1,15,10,1
exec sp_trace_setevent @p1,15,12,1
exec sp_trace_setevent @p1,15,13,1
exec sp_trace_setevent @p1,15,14,1
exec sp_trace_setevent @p1,15,15,1
exec sp_trace_setevent @p1,15,16,1
exec sp_trace_setevent @p1,15,17,1
exec sp_trace_setevent @p1,15,18,1
exec sp_trace_setevent @p1,17,1,1
exec sp_trace_setevent @p1,17,9,1
exec sp_trace_setevent @p1,17,10,1
exec sp_trace_setevent @p1,17,11,1
exec sp_trace_setevent @p1,17,6,1
exec sp_trace_setevent @p1,17,12,1
exec sp_trace_setevent @p1,17,14,1
exec sp_trace_setevent @p1,10,9,1
exec sp_trace_setevent @p1,10,2,1
exec sp_trace_setevent @p1,10,10,1
exec sp_trace_setevent @p1,10,6,1
exec sp_trace_setevent @p1,10,11,1
exec sp_trace_setevent @p1,10,12,1
exec sp_trace_setevent @p1,10,13,1
exec sp_trace_setevent @p1,10,14,1
exec sp_trace_setevent @p1,10,15,1
exec sp_trace_setevent @p1,10,16,1
exec sp_trace_setevent @p1,10,17,1
exec sp_trace_setevent @p1,10,18,1
exec sp_trace_setevent @p1,12,1,1
exec sp_trace_setevent @p1,12,9,1
exec sp_trace_setevent @p1,12,11,1
exec sp_trace_setevent @p1,12,6,1
exec sp_trace_setevent @p1,12,10,1
exec sp_trace_setevent @p1,12,12,1
exec sp_trace_setevent @p1,12,13,1
exec sp_trace_setevent @p1,12,14,1
exec sp_trace_setevent @p1,12,15,1
exec sp_trace_setevent @p1,12,16,1
exec sp_trace_setevent @p1,12,17,1
exec sp_trace_setevent @p1,12,18,1
exec sp_trace_setevent @p1,13,1,1
exec sp_trace_setevent @p1,13,9,1
exec sp_trace_setevent @p1,13,11,1
exec sp_trace_setevent @p1,13,6,1
exec sp_trace_setevent @p1,13,10,1
exec sp_trace_setevent @p1,13,12,1
exec sp_trace_setevent @p1,13,14,1
exec sp_trace_setevent @p1,13,3,1
exec sp_trace_setevent @p1,13,35,1
exec sp_trace_setevent @p1,70,1,1
exec sp_trace_setevent @p1,70,9,1
exec sp_trace_setevent @p1,70,11,1
exec sp_trace_setevent @p1,70,6,1
exec sp_trace_setevent @p1,70,10,1
exec sp_trace_setevent @p1,70,12,1
exec sp_trace_setevent @p1,70,14,1
exec sp_trace_setevent @p1,70,3,1
exec sp_trace_setevent @p1,70,35,1
exec sp_trace_setevent @p1,53,1,1
exec sp_trace_setevent @p1,53,9,1
exec sp_trace_setevent @p1,53,11,1
exec sp_trace_setevent @p1,53,6,1
exec sp_trace_setevent @p1,53,10,1
exec sp_trace_setevent @p1,53,12,1
exec sp_trace_setevent @p1,53,14,1
exec sp_trace_setevent @p1,53,3,1
exec sp_trace_setevent @p1,53,35,1
exec sp_trace_setevent @p1,11,1,1
exec sp_trace_setevent @p1,11,9,1
exec sp_trace_setevent @p1,11,11,1
exec sp_trace_setevent @p1,11,6,1
exec sp_trace_setevent @p1,11,10,1
exec sp_trace_setevent @p1,11,12,1
exec sp_trace_setevent @p1,11,14,1
exec sp_trace_setevent @p1,11,3,1
exec sp_trace_setevent @p1,11,35,1
exec sp_trace_setevent @p1,74,1,1
exec sp_trace_setevent @p1,74,9,1
exec sp_trace_setevent @p1,74,11,1
exec sp_trace_setevent @p1,74,6,1
exec sp_trace_setevent @p1,74,10,1
exec sp_trace_setevent @p1,74,12,1
exec sp_trace_setevent @p1,74,14,1
exec sp_trace_setevent @p1,74,3,1
exec sp_trace_setevent @p1,74,35,1
exec sp_trace_setevent @p1,71,1,1
exec sp_trace_setevent @p1,71,9,1
exec sp_trace_setevent @p1,71,11,1
exec sp_trace_setevent @p1,71,6,1
exec sp_trace_setevent @p1,71,10,1
exec sp_trace_setevent @p1,71,12,1
exec sp_trace_setevent @p1,71,14,1
exec sp_trace_setevent @p1,71,3,1
exec sp_trace_setevent @p1,71,35,1
exec sp_trace_setevent @p1,72,1,1
exec sp_trace_setevent @p1,72,9,1
exec sp_trace_setevent @p1,72,11,1
exec sp_trace_setevent @p1,72,6,1
exec sp_trace_setevent @p1,72,10,1
exec sp_trace_setevent @p1,72,12,1
exec sp_trace_setevent @p1,72,14,1
exec sp_trace_setevent @p1,72,3,1
exec sp_trace_setevent @p1,72,35,1
exec sp_trace_setevent @p1,100,1,1
exec sp_trace_setevent @p1,100,9,1
exec sp_trace_setevent @p1,100,11,1
exec sp_trace_setevent @p1,100,6,1
exec sp_trace_setevent @p1,100,10,1
exec sp_trace_setevent @p1,100,12,1
exec sp_trace_setevent @p1,100,14,1
exec sp_trace_setevent @p1,100,3,1
exec sp_trace_setevent @p1,100,35,1
exec sp_trace_setstatus @p1,1

To stop the trace
declare @p1 int = 2 --{Whatever value is outputted from the first query}
exec sp_trace_setstatus @p1,0
exec sp_trace_setstatus @p1,2

The post How to test for query regressions when upgrading appeared first on SQL Server with Mr. Denny.


Contact the Author | Contact DCAC

Rounding Out PASS Summit 2019

Published On: 2019-11-08By:

If you are like me, when certain events end, it is a bittersweet time.  You’ve enjoyed your time with people you consider family, got to learn some new things to help your career, and hopefully have made new connections that will last maybe even a lifetime.

Today marks the last day for PASS Summit 2019.  The time has come for us to stay good-bye and being our journey home.  Some of us might have already left to distant shores, some are still sticking around.  As we start to round out the day in Seattle, Washington, here are some tips to think about as you wind down from the conference.

Notes

I often take a lot of notes while at conferences.  They aren’t organized very well and sometimes are just a jumbled mess.  I have found using applications such as OneNote or Evernote has helped me over the years to be more organized with my note taking, but even then, sometimes they are just out of whack.

Once you get back to your place of origin, make sure to schedule some time to go back through your notes while things are fresh in your mind.   I recommend within a week of getting back to your normal schedule (for me it’s Monday morning following the conference), go back through your notes and reorganize them if needed.

Reorganizing them will help you later when you need to find things or need to make sense of what you were trying to capture.  Clarify any questions you might have jotted down or start those conversations on things you know you wanted to follow up on.

Contacts

In my opinion, one of the greatest values from the annual PASS Summit is the networking.  I can fully and without question say that I am where I am currently in my career due to the networking, I did at previous PASS Summits.  I distinctly remember being involved in a technical conversation with folks like Chris Shaw and Steve Jones while simultaneously being amazed that I was even in the conversation.  Over the years, I’ve had several of these types of encounters and they have been instrumental in my growth.

If you found yourself in one of these types of situations, I hope you got contact information of the individuals who might be in the conversation.  If not, next time this happends make sure you get a phone number, a business card, or an email address and file them away accordingly.

You never know when those contacts will come in handy.  Not only have I been able to expand my own career through them, I’ve been able to help solve enterprise problems with the help of those contacts as well as drive database technology forward in the organizations I’ve worked for.

Share What You’ve Learned

Not everybody on your team can probably attend the conference.  Someone has to stay back and keep the lights on most of the time.  I am a huge fan of the USB stick that you can purchase.  This USB stick has all of the regular sessions on it and it’s not a horrible cost (not thousands of dollars) and it is a great way to share content amongst your teammates.

If you have a team back at the office eagerly awaiting your return to take the on-call pager, take some time to schedule a knowledge sharing meeting.  It doesn’t have to be long, but you can pass along what you’ve learned during your time at the conference.  By doing this, you’ve also helped your organization get additional ROI from sending you.  Down the road this could potentially ensure you get to go back as your manager will know that you’ll share knowledge which helps the company in the long run.

Future Planning

Now that you’ve cleaned up your notes, did some knowledge sharing and touched based with some of your new contacts, take some time and look towards the future.  What technology do you need to research to help your organization move forward?  Who do you need to engage to drive that new solution?  Also, inventory on your career path and see what might excite you.  Do you need to move in that direction?  Is that a shift from what you’ve been doing?  If so, then start working towards that.

Planning for the future doesn’t have to be an intensive, long drawn out thing.  Set some goals, both personally and organizationally, and start making steps towards those goals.

The future is yours to command so don’t squander it.

Relax

Finally, take some time to relax and unwind.   I’m a mix of an introvert/extrovert but I like being home in my own space with my own things.  Conferences can be hard and tiring while at the same time exciting and renewing.  Make sure you take some time to decompress and spend time with your family.  Remember that your family supported you by having your gone for the duration of the conference.  Any time I travel I can’t wait to get back to my kids and see how they have grown during the time that I was gone.  They never fail to surprise me.

Summary

Any conference can be exhausting but what you do with the information after you leave is up to you.  Remember that one you learn something, it is yours to do whatever you’d like to do with it and nobody can take that away from you.  Share what you’ve learned, clean up your notes, reach out to some of the contacts you made, look down the road at bit, and relax.

Safe travels to all of those headed home far and near!  See at the next conference!

© 2019, John Morehouse. All rights reserved.


Contact the Author | Contact DCAC

When to use SQL DB vs SQL DW

Published On: 2019-11-04By:

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.


Contact the Author | Contact DCAC
1 2 3 440

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link