DP-300 Microsoft Azure Database – Upgrade strategy, configure database authentication, and filegroups

1. 17, 18. evaluate requirements and strategies for the upgrade

Hello and in this section we’re going to be talking about an upgrade strategy for moving to Azure. So what do we mean by upgrade? Well, it’s possible that you’re on SQL Server 2012 and you want to go to a virtual machine with SQL Server 2019, so that would be an upgrade. Now, we’ve previously got this virtual machine that you can see here and this is running SQL Server Server 2019 and you can tell that from the server number. Server 15 is 2019. Now, suppose I didn’t have an SQL Server 2019, suppose I had this virtual machine for quite a while and it was 2016. For instance, could I get the 2019 version on? And the answer is yes.

And there are two ways you can either do an online upgrade or offline upgrade. So if it’s online, then what you need to do is a side by side installation. So you’d have 2016 and 2019 at the same time, and then you would decommission the 2016 version, the previous SQL Server version when it’s no longer needed. You can choose what features to use and you can install a 64 bit instance as your new version, even if your previous version was 32 bit. However, because you are essentially creating a new program, then all of your settings will not automatically be both ported across. So you will need to do that yourself. You can do an offline upgrade strategy, so that just means upgrading SQL Server.

However, there are two things you cannot do an offline upgrade from a 32 bit instance to a 64 bit instance, so it’s got to be the same number of bits and you cannot add new features during the upgrade, but you can always do it afterwards. Why would you want to go from 32 bit to 64 bit? Well, most computers nowadays are 64 bit and the reason for that is it allows better use of the memory, quicker IOR and that sort of thing.

So what can you upgrade? Well, obviously if you’re doing an offline upgrade, you can do anything. So you can go from SQL Server 2000 to 2019. Well, if you’re doing a side by side installation, then you can upgrade from anything to anything. And in fact, you could have an alternate virtual machine if you so wished. Do it completely away from your current on prem or virtual machine.

You do need to make sure you’ve got the right hardware if you’re going to be doing it in the same machine. So you might need to update Compute for instance. Now, if we’re talking about one that is not a side by side installation but an override, then you can upgrade to SQL Server 2016 or 2017 from anywhere from 2008 upwards, you can upgrade to SQL Server 2019 from anywhere from SQL Server 2012 and upwards and databases can be migrated. Now, you can also upgrade to a higher version of the same year. So what do I mean by higher version.

Well, I’m using the developer, so normally I could upgrade to a developer for a later year, but I can also upgrade in SQL Server 2019 only to the Web Standard or Enterprise Edition. I may have an Express edition. I can upgrade to an Express Web Standard or Enterprise edition. If I have an Evaluation Edition, then I can upgrade to an Evaluation, Express, Web Standard or Enterprise Edition. If you’ve got a web edition, then I can upgrade to a web standard or Enterprise edition. If you have the Standard Edition, you can upgrade to Standard or Enterprise. Older versions of SQL 2012 also use technology such as Workgroup and Small Business. They can also upgrade to Standard or Enterprise and SQL Server 2012 or above Business Intelligence can upgrade to Enterprise.

Now, what happens if an application requires a previous version, for example, support? I went from SQL Server 2016 to 2019 and I need to use the features as if it was 2016. Well, that’s a compatibility version and we’ve had a look at compatibility versions before we’ve gone into the database right and click going to Options and into the property options.

We have a compatibility level, so you can select a particular database with a particular compatibility level, but unless you’ve got a particular reason, then I would keep the compatibility level at its maximum that you can do to take advantage of all of the features. So these are requirements for the upgrade and consideration of whether you want to do it offline or online. So in the next few videos, what I have done is I have created new virtual machines which have an SQL Server 2016 2017, and what I’m going to do is upgrade them one offline and one online.

2. 21, 22. implement an online and offline upgrade strategy

In this video we have got a virtual machine with an SQL Server data center. It’s the developer edition. So what I’m going to do is I’m going to upgrade it. So what I’ve done is downloaded the Developer Edition for 2019 and once it has been downloaded, I can say what sort of installation I want. And I chose a custom installation which gets me to this page. So if I click on installation, we have got two options. We have got a new SQL Server standalone installation. So this is the online installation, so this will be adding it to my existing installation. But further down, I have got an upgrade from a previous version of SQL Server. So this is the offline version.

So I’m going to click on that and you can see it is processing the current operation. So I specify what edition, developer, agree the license terms for computer checks for global rules, make sure everything is set up correctly. So now that dialog box disappears, we’re going to minimize this box and now it’s asking me what instance of SQL Server am I going to upgrade? Well, I’m going to upgrade this version, so click Next. Then it’s going to ask me what features I want to install. But as this is an upgrade of the existing SQL Server, I can’t actually change the features here.

So. Next, consent to install Rupin So. I will accept. So now I say what instance configuration do I want? The server configuration? How I’m going to upgrade the full text. If I’m using that, then it’s just going to check that everything is fine, then it will say it’s ready to upgrade. And now I can upgrade. So the upgrade process is happening right now. Now let’s turn to my other server and this time I’m going to do a side by side installation. So again, I have downloaded the same things. So what I’m going to do, instead of doing an upgrade, I’m just going to do a straightforward standalone installation. So click on that and you can see it’s going to start. So just move this down. I’m going to select the Developer Edition, accept the license terms, so it’s just checking the rules.

So I’ve got a warning for Windows Viral, but that’s absolutely fine, so it’s not going to stop me. Click on Next and this is where I can select what features I want. So I couldn’t do that when I was upgrading on top of my existing, but if I’m doing it side by side, it’s a completely separate program. So I’m just going to select Database Engine Services and go on to the Next screen, which it will check what I’ve put. It was very quick and now it’s saying what instance do you want?

Well, I’ve already got a default instance, so I’m going to put this as my new instance. So this is my 2019 version and click on Next. As you can see, I can’t do that. So I’m going to call it instead of version 2019. And then I can go through other things such as server configuration, database engine configuration, we’re going to add the current user as an administrator. It’s then going to check that everything I’ve put in is fine and now I can install it. So I’ve done two installations, one side by side, this one and another an upgrade. So going back to my upgrade, my offline upgrade, you can see that it has worked completed. So I’m going to close this, close everything else, go back into SSMS, use the same server name and credentials as I did previously. And here you can see it is now upgraded. And if I go into properties and you can see we are now on version 15, that’s the 2019 edition, whereas previously we are on version 14, so I’ve upgraded my previous version.

So I don’t have two versions on my machine now, I just have the one. So you can see that is an offline upgrade. Turning now to my other virtual machine, so that has also finished the installation. So this isn’t an offline upgrade, this is a side by side upgrade. So I have my previous database, my previous server, my previous instance, and you can see it is a version 13. But now what I can do, I can disconnect from that. So if I disconnect, you will find that I am still able to connect to that. So it shows it’s still in existence, version 13. But what I can also do is connect to my new named instance. So that is version 2019. And here is my second instance and you can see it is version 15.

So this is a side by side installation, so nothing has been ported across. So I had a demo database that has not been ported across into the new version. So what I can do now is decommission my 2016 version by uninstalling it. So in this video we’ve had a practical look at how you can upgrade an existing version of SQL Server. You can do it side by side to have two installations and then you can decommission one once all the databases have been transferred across, if you so wish.

Or you can upgrade your existing version, in which case you will only have one version at the end. But you can’t do any of the customization that you can do for side by side. If I wanted to, now could go back into the installation media and add additional features if I so wished. And if you’ve been following along, you might want to do the same as me and go into the resource groups and go into your virtual machines and decommission them. So delete everything in those resource groups, so it’s not taking up any more cost.