Comparing Azure SQL Database and SQL Server in a Virtual Machine
Articles,  Blog

Comparing Azure SQL Database and SQL Server in a Virtual Machine

[Music] In this topic, we are going to look at SQL
databases and in a particular kind of comparing and contrasting looking at SQL with using
IaaS or Infrastructure as a Service and SQL with PaaS or Platform as a Service. So the
cool thing about using SQL in Azure is that pretty much everything kind of looks and feels
the same that it did before for the most part. There are some things that are different and
we will show you some in that in the portal, but a lot of your familiar tools, the way
you connect to databases, a lot that is very, very similar, but when you go from on-premises
to up in the cloud, so now we move these workloads or can connect and communicate to our IaaS
instance or PaaS instance. So what we want to do before we get kind of real specific
is look at the big picture. So on the slide here we have, this is kind of the large continuum.
Now, we are going to focus on the IaaS and PaaS part, which are these two here right,
but going from the left to the right, so first you have your physical server right which
you may have already had on-premises and in some organizations we will actually set up
a private cloud, so they could have virtual servers in their private instance as well
okay, but most of us are probably using the public cloud, so we could do IaaS or PaaS. Now if we do IaaS, so this is where you would
fire up a brand new VM, and in that virtual machine that you built, then you would go
ahead and install SQL. Now of course cool thing about that is, is that you can install
any flavor of SQL you want, any version you want right because it is just another server
that is being built up there. If you use PaaS, then you are using what they have up there
in the cloud already for you. So, it is kind of a two-part process right. So you create
a SQL Server and then once you do that, then you can add a database to it and then as you
add more databases, you can then manage these all from your Azure Portal, which is very,
very cool, and the third option we are not going to go into specifically here, but we
might briefly mention, is that we have Software as a Service. So there are offerings out there
that you can grab from the marketplace right. Is there something you knew apart from that?
>>No, I just wanted to comment that when we are using IaaS SQL, so when you go to spin
those up, you could go create or script out as we talked about in other topics, you could
actually put that in an ARM template or even something like a chef recipe and have your
SQL installed the way that you want, but if you can go actually up to the marketplace
when you are in the portal and you see the virtual machines up there that have SQL preinstalled,
so we have ones that are specific for transactional workloads or analytic type workloads. There are a bunch of different ones that are
built up there in the marketplace, but keep in mind that those are billed in a different
way, so you can bring your own license if you are going to build your own VM or you
can if you use one of those from the marketplace just be billed for that VM and the SQL cost
is baked in. So that is an important point to remember if you are going to bring your
own licenses or if you already have an account with Microsoft and have the software licenses
for SQL Server, you probably want to bring those along. You do not want to go provisioning
ones in the marketplace and pay for SQL again. So, it is something key there to think about.
>>It is good to be cost-effective there. Alright, so in the next part here we have, we are looking
at specifically kind of the topic at hand and that is you build an Azure VM and install
SQL versus doing an Azure SQL Database, which we configured in the portal. So, let us look
at these in comparison. So we talked about on the left-hand side, we build a VM, a SQL,
it is more management, just like we said with kind of the continuum of doing IaaS to PaaS. PaaS can be faster, cheaper, better, but IaaS
you can have more control, you can do anything you want, but the flipside is you have to
do more management, maintainability and controlling and patching and updating the lesser stuff.
Also let us look at some other points here. So as we mentioned, you can write any SQL
Server version you want in there. You have full on-premise compatibility, so you can
connect back with your on-premises and vice-versa. You can choose different VM sizes and of course
if we do availability sets, we can get our SLA. We can also configure SQL AlwaysOn, which
is an option. Actually that is a built-in option right, that does it automatically for
you.>>Yes, it is important to point that out. So AlwaysOn Clustering is our failover clustering
technology in SQL Server and that is one of the few workloads that you can run in actual
Windows cluster inside Azure.>>Yes. I tell you from what I have seen from a lot of customers,
they hear about that and they are like, wow! We really want to do that, but then they go
to try to build that on-premise and they do not. It is kind of confusing. It is a little
bit tricky, but I mean Azure makes it much, much easier with just optioning and selecting,
it will build up for you automatically. Additionally with the Azure VM, you can reuse your existing
Active Directory on-premises infrastructure as well. Now in comparison on the Azure SQL database,
you access a DB, a database, which you create up in Azure and I will show this in a second
and it is fully managed, high available, backups, patching all that sort of stuff. You run the
latest SQL version based on Enterprise edition and there is even, we will take a look at,
we will see there is a V12 that gives you all the latest greatest things that you can
get if you want. Incomplete on-premise compatibility, so there are no jobs, link service, file streams,
so that is something you need to think about in your design and architectural decisions.>>So I am just going to interject here a second.
Yeah, so at customer engagements, this is probably one of the biggest things to think
about when you are thinking about moving to Azure PaaS SQL or Azure SQL database. So it
is a great option because it kind of eliminates or mitigates the need for so much backups
and redundancy because we are going to go ahead and write. Every time we write something
to that primary database, it is going to get written three times inside that same data
center in that same region and then if you turn on geo-replication, it will actually
asynchronously get replicated to another location, so that kind of really helps you out from
a DR standpoint even the HA standpoint, but the thing to think about is you know things
like file streams, that is something people always run into because users have implemented
that over the years and that is not complete. We cannot have that feature in Azure. Now
typically what we do is migration then and try to move those file streams out to something
like Blob Storage and do a migration like that, a little bit of code involved, a little
bit of work, but once you do it, you get a lot a benefit from the PaaS and so.>>Excellent,
very good point, thanks. So some other final points here on the slides
looking at the different database sizes, you can get Basic, Premium and of course there
is different criteria that are used to manage those, make a nice little chart we will show
you and then of course at the bottom is a great point here is that we get an SLA of
99.99%. Now let us switch over to the portal just real quickly.>>While you are you switching over the one last thing I will point out from that compatibility sense, right now we only support
SQL logins if you are using Azure SQL Database. So you cannot use Windows Integrated Authentication
if you are using an Azure SQL Database. A lot of times that is baked into the application,
so something to consider and it is definitely for the security guys in your network, they
probably want to know about that.>>Okay, great. Now in the portal here, in the new Azure Portal,
I just want to show you, I had created just a couple of real quick samples and I am not
going to get into the how-to configuration, we can look at that later on, but so in the
top-right hand corner here I had created a SQL Server and then in the middle so you have
a pane here that I append on here for SQL databases. So if you have a whole bunch of
databases, it will show online here as you can see the list of all the databases, but
the nice thing here in the new portal is I can just right-click on this one, that is
actually the specific one, and when I go to settings here, I can look at all the different
aspects of it. So I see all the properties, the pricing tier that we are in that we selected,
the collation, there is my database connection string so we need to connect to that, my location,
server admin and then also if we have geo-replication setup, how that is set, and also something
is really cool that you can do is for the audit team if I built the server and have
multiple databases, you see here that I can inherit those audit settings from the server,
so those settings will apply to all of them or if I deselect that, then I can kind of
break that inheritance okay. So from the lift and shift perspective, you know here is kind
of again the comparison on the left-hand side if we have a SQL instance in Azure VM, we
can migrate existing apps, you just can really kind of for a few SQL Servers, but if you
really need to scale, build-out and get the elasticity and the hyper skill that Azure
provides, the Azure SQL Database is really a better solution, where you can build new
applications and go to ten to hundreds of databases at once. And that concludes our
topic in doing a comparison of SQL databases in Azure IaaS versus Azure PaaS.

Leave a Reply

Your email address will not be published. Required fields are marked *