Azure SQL Database - Simply Explained

0:00 Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. What Azure SQL database actually is. Let's start with the plain version. Before any of the acronyms get involved. Azure SQL database is a relational database that Microsoft runs for you in their cloud. You don't buy a machine, you don't install the software, and you don't keep it patched. All of that now sits on Microsoft's side of the line. You create a database, point your application at it, and the rest happens quietly in the background. The engine underneath is the same SQL server engine that has been running business databases for decades. Your t-SQL still works, your tools still connect, and the skills you already have carry straight over. It isn't some stripped down cousin of SQL server at all.

0:40 It's the same animal living in a new home. That's the whole idea behind the phrase database. As a service, you aren't renting a box and doing the work yourself. You're renting an outcome and someone else keeps the lights on. Microsoft describes the service as evergreen. That word means it stays on the latest stable version automatically, so you never hit an end of support wall. Azure gives you three ways to run that engine, and knowing which is which clears up a lot of confusion as your SQL database is the fully managed option, and it's the one we're talking about today. Azure SQL managed instance is the near-complete version built for lifting existing SQL server applications into the cloud. Almost unchanged. SQL server on an Azure Virtual Machine is the one where you run everything yourself, exactly like a server in your own office. Inside Azure SQL database, you deploy in one of two shapes.

1:23 A single database has its own dedicated set of resources. They're managed through something called a logical server, which is really just an administrative home for your databases, logins, and firewall rules. An elastic pool is a group of databases that share one set of resources, and will come back to why that matters so much. What you're really buying with all of this is delegation. Microsoft patches the engine, takes the backups, keeps it available, and keeps it current. There are no virtual machines to watch and no operating system for you to babysit. For a beginner, that's the entire appeal. The parts you'd normally have to learn the hard way are simply someone else's job. Whether that trade is right for you is a question we'll settle near the end when we put it head to head with running SQL server on a virtual machine.

2:04 Microsoft frames the whole decision as a single choice, and it's worth repeating. You either manage the database yourself, applying the patches and taking the backups, or you hand those jobs to Azure. If your honest answer is hand them over, you're in the right place. And if you're worried about the bill, Microsoft's own documentation puts the starting point at about five dollars a month on the basic tier. The two ways you pay for it. Now that you know what the thing is, the first real decision is how you pay for it. Azure SQL database sells itself through two purchasing models, and picking one is the choice that shapes everything else. The first is the DTU model and it's the bundle. Compute, storage and input and output are wrapped together into one number. A DTU is a blended measure of CPU memory reads and writes, so you don't have to think about any of those separately.

2:48 You pick a size and the size sets all of them. If you like a simple package with a predictable monthly number, this is the set menu. Its tiers are basic, standard, and premium. The scaling is roughly linear, which makes it easy to reason about double the dtus, and you roughly double the resources available to that database. A large premium database can carry hundreds of times the compute of the smallest basic one. The second is the V core model, and it's the build your own. A V core is a logical processor, and this model lets you choose compute and storage separately. You also pick the hardware generation and you match the price to the workload instead of guessing. Microsoft recommends this model and its tiers are general purpose, business critical, and hyperscale. One difference here matters for your wallet. Because the V core model adds three extras the DTU bundle doesn't have.

3:30 There's Azure Hybrid Benefit, which lets you reuse existing SQL server licenses for a discount. And there's serverless where a database can pause whenever nobody's using it. Hyperscale is the third, and it's built for serious scale. That gap is a big reason teams tend to start on V core. There's one more thing that should put you at ease. You aren't locked in forever, because Microsoft lets you convert a database from one model to the other online, your application stays up the whole time. That makes the switch closer to changing a setting than rebuilding anything. So this is a decision, not a life sentence. If you want the simplest possible start and a fixed number, DTU is a reasonable place to begin. If you want control, savings or room to grow. V core is where you'll end up. And there's one tear hiding in here that can make a quiet little database almost free while it sits idle.

4:12 We'll get to it in a minute. Picking your service tier. Choosing a purchasing model gets you into the right store. Choosing a service tier is picking the thing off the shelf. This is where the size and the plumbing live, and it's less intimidating than the name suggests. On the V Corps side, general purpose is the default and the sensible starting point. It's budget friendly and handles most everyday workloads just fine with storage that goes up to four terabytes. Business critical steps up to much faster local storage built for applications that need quick responses on heavy transactions. It costs roughly two point seven times more than general purpose, and the reason is simple it keeps three extra copies of your data warm and ready, so a failure barely slows you down. Hyperscale is the big one, with storage that scales up to one hundred and twenty eight terabytes.

4:54 Microsoft now calls it the recommended default for new and modern workloads. On the DTU side, the tiers run from basic through standard to premium. Basic is genuinely tiny, with five Dtus and two gigabytes of storage, and it's perfect for learning or a small side project. Standard covers a wide middle ground and premium adds the speed and the in-memory features you'd want for something busier. One small thing is worth knowing the smallest D2 sizes give you less than a single V core of processing power, so if your workload leans hard on the processor, you'll want to move up a size or two. That brings us to the tier I promised you. Inside the V Corps General purpose tier, you can choose serverless. A serverless database scales its compute on its own. By default. It pauses after an hour of no activity, so you're billed only for storage while it sleeps.

5:36 The moment someone connects, it wakes up and gets back to work. That makes it ideal for the intermittent project, the demo, or the database that sits idle most weekends. It isn't the right pick for a workload that runs around the clock. It also isn't available in the business critical tier or anywhere in the DTU model. The rule of thumb that saves beginners a lot of stress is this start small, watch the numbers and grow later. Changing size is an online operation, so you're not stuck with the choice you made on day one. Start cheap, measure honestly and let the metrics tell you when to move up. Elastic pools the timeshare trick. So far we've been talking about one database at a time. The money problem shows up when you have dozens. It's worth understanding because this is where a lot of teams quietly overspend. Picture twenty small databases, each one busy for a few minutes a day and idle the rest of the time.

6:21 If you give each one its own resources sized for its peak, you're paying for power that sits unused almost all day. If you size them for the average. Instead, they'll choke the moment a spike arrives. Neither one feels good, and the elastic pool fixes this by turning it into a timeshare. You put a group of databases on one server, and they share one fixed pool of resources for one price. The busy ones borrow while the idle ones give back. The shared resources are measured in E, dtus or vcores, but you can think of it as one tank of fuel that everyone draws from. Microsoft's own example makes it concrete. Twenty databases that each spike to ninety Dtus, but average under five can share one hundred editors between them. That's a twenty times reduction in resources and about a thirteen times reduction in price compared with sizing each one separately.

7:02 The intuition is that those peaks never line up, so the pool rarely serves more than a fraction of the total demand at once. The pattern that benefits is specific, and this is the part people get wrong. Pools suit databases with low average use and short, infrequent spikes that don't all happen at the same time. Savings can show up with as few as two standard tier databases, and the more you add, the better the math usually gets. But if your databases are busy all day, every day, don't pool them. They'll fight each other for the same resources. And you'll wonder why performance got worse. What Azure handles so you don't have to. We've saved the part that should let you sleep at night. This is where the managed service really earns its keep. Backups happen automatically and you can't accidentally switch them off. Azure takes a full backup every week, a differential backup every twelve to twenty four hours, and a log backup roughly every ten minutes.

7:48 That gives you something like an undo button point in time. Restore lets you roll a database back to a specific moment within the retention window. That window is seven days by default and configurable up to thirty five. When someone drops a table on a Friday afternoon, this feature is the difference between a bad day and a disaster. If you need more than that, there are two more layers. Geo restore lets you rebuild in a completely different region. When the primary one is having trouble and it's switched on by default. Long term retention keeps full backups for up to ten years, which is the kind of thing compliance teams ask for. You just have to turn it on because it isn't on out of the box. There's a small catch that trips people up, so it's worth saying plainly you can't download these automatic backups like a file they live in. Microsoft managed storage and exist to be restored, not exported.

8:30 If you genuinely need a copy of your data outside Azure, you export a portable package instead. And the odd part is that deleting a database doesn't delete its backups right away. Deleting and recreating a database to save money can actually cost you more. High availability works the same way, and it mostly stays invisible. By default, your data is copied three times inside a single data center, and committed data is never lost. If a machine running your database fails, Azure moves you to a new one automatically. For something stronger, you enable zone redundancy, which spreads your database across two or three zones in a region. That way, a whole data center going dark doesn't take you with it. Just know it isn't available on the basic or standard tiers at all. Though it's available on general purpose, business, critical, hyperscale, and premium.

9:12 The availability guarantee climbs along with all of that, Microsoft's published service level agreement puts zone redundant business critical and premium databases at a guarantee of at least ninety nine point nine nine five percent, and business critical includes one more nice bonus. It gives you a free read only copy of your database, so your reports and analytics can run there and leave your main workload alone. Security without the jargon, backups protect you from accidents, and security protects you from people. It helps to see it as layers moving from the outside. In the outer layer is the front door and by default it's locked. Nothing reaches your server until you explicitly allow it. Firewall rules let you permit access by IP address. You can restrict everything to your own virtual network so only your own systems can lock.

9:53 The next layer is who you are. You can sign in with Microsoft Entry ID, the identity system that used to be called Azure Active Directory, or with a plain SQL username and password entry is the better choice when you can use it. It brings multifactor sign in conditional access rules, and a way to stop hard coding passwords into connection strings. Then there's what you're allowed to do once you're inside. The guiding idea is least privilege, which just means giving people the smallest amount of access they need to do their job. If you need to go further, row level security can hide individual rows. That way a user only ever sees their own records, which is handy when many customers share one database. Encryption shows up in three places, and the good news is that the most important one is already on. Data in transit is always encrypted with TLS.

10:34 Data at rest is protected by transparent data encryption, which is switched on by default on every new database. And for a specific sensitive column, like a credit card number, always encrypted can keep even your own database administrators from reading it. The key never reaches the servers at all. Finally, there's watching for trouble auditing records who did what into your own storage and defender for cloud raises a flag on things like SQL injection attempts and strange sign in patterns. You can also classify and label the sensitive data in your database, which gives you a map of where your risk actually lives. The plain takeaway is that the safe settings are the defaults. Your main job is deciding who gets in and what they can touch, Azure SQL database or a virtual machine. Everything so far assumes you want a managed database, but there's a real fork in the road here, and it's worth saying out loud, you can hand Microsoft the keys, or you can keep them.

11:20 Choose the managed Azure SQL database when you want patching, upgrades, backups, and high availability taken off your plate. Choose it when you want that higher availability guarantee. And when you're building something new and want to move quickly. It's built for cloud first applications and for teams that would rather ship than administer. It also lets one person manage far more databases than they ever could by hand, which matters once your project starts to grow. Choose SQL server on an Azure Virtual Machine when you need operating system level access, or when you want full control over the engine, choose it when you have to match an existing on premises setup. Exactly, or when you're lifting an old application into the cloud with no code changes. With a virtual machine, you decide when updates run and you get every feature. You also own the patching, the backups, and failover plan.

12:03 If your application depends on the operating system or on scheduled jobs inside SQL server itself, the virtual machine is often the only option that won't fight you. There's a middle option worth knowing about. Azure SQL Managed Instance gives you close to full SQL server compatibility while keeping the managed benefits, and Microsoft recommends it for most migrations. So if you're moving an existing system and full control isn't actually required. That's often the sweet spot. The trade off in one line is this. Managed means less work and a stronger guarantee, but fewer knobs to turn. A virtual machine means every knob you could want and the responsibility that comes with them. If you're new to all this, the honest default is to start managed and only reach for the virtual machine when you have a specific reason. The mistakes beginners make knowing the right choice is half the battle.

12:46 The other half is not tripping over the same handful of things that catch almost everyone, so let's run through them. The first is paying for peak you never use, or the opposite, starving a database to save a few dollars and then wondering why it's slow. What's your real usage for a couple of weeks and size to that? Not to a guess. Next comes pooling the wrong databases. Elastic pools are for spiky, mostly idle databases, not for workloads that run flat out all day. Put the busy ones in a pool and they'll compete for the same resources. A third one is choosing serverless for an always on application, and then getting annoyed by the pause and wake delay. Serverless is for intermittent use, full stop. Another is forgetting that backup retention defaults to seven days. If you need to keep history longer or a compliance rule demands it. You have to turn on long term retention yourself.

13:27 One more is assuming zone redundancy comes included on the basic and standard tiers. It doesn't exist at all. So you have to pick a supporting tier and switch it on. And the last is treating the firewall as an afterthought. Nothing gets in until you say so, and the safest habit is to keep it that way, allowing only the addresses and networks that genuinely need access. None of these are exotic mistakes. They're just the ones that quietly cost people money and sleep. Wrapping up what to remember. Let's pull this together into something you can actually hold on to. Azure SQL database is the SQL server engine running as a managed service in Microsoft's Cloud, you choose how you pay either the DTU bundle or the build your own Vcore model, and then you pick a tier and a size. You can share resources across many databases with an elastic pool, or let a quiet database pause itself.

14:12 With serverless backups, high availability, and encryption all come switched on, and you reach for it instead of a virtual machine whenever you'd rather not run the server yourself. So I'll leave you with one question to sit with. If Microsoft took patching backups and failover off your plate tomorrow, what would you actually spend that time on?