This month’s webinar is all about SQL Server upgrades and migration.
We cover the following topics:
- Our Straight Path Upgrade Migration process with log shipping
- Why upgrade? What are some options?
- Why In Place Upgrades are usually bad.
- Things to think about before, during, and after a migration
- Standard vs Enterprise; can you save?
- Best practices for a successful migration
This topic is part of our monthly SQL Server webinar series. Webinar registrants get access to the entire recording, which includes the office hours Q&A. If you would like to be notified about upcoming webinars, be sure to subscribe below:
Sign Up for Updates
Introduction
Mike Walsh
So hello, welcome to the Straight Pass Solutions monthly webinar this month where our topic is upgrades and migrations. I stole this from farmer’s insurance. I hope they don’t sue me. I don’t have that much, but we know a thing or two about upgrades and migrations because well, we’ve seen a thing or two about upgrades in migrations and good and bad, and we’ve been involved in many migrations that have gone gone well and some that that we helped rescue and some that, you know, fate is still a a cruel master, and they sometimes go the way they go.
So a few people from my team here on camera right now and on audio you see signed, I’m just going to order. I see them. But Sergeant Delaney is here she is is sort of a guru when it comes to clustering HDR. David sees us here, he’s in our support team. He’s one of our junior DBA, so he knows a lot about security, but he’s also involved in many migrations.
Jeff Iannucci is here. Hey, Jeff. Jeff just knows a lot about a lot. He actually he and I both spoke at the past conference just a few weeks ago. We did on video. Some recorded it with varying degrees of earlier than the conference. Jeff actually did his pretty early. I did mine very late. Jack Corbett is here. He was a single interview for a while and he’s really strong and lots of things here.
But he actually helped kind of build some log shipping automation for some clients migrations we do in Sandra’s actually developed on top of that too and Turkey’s Yorkshire terrier is terrorism on audio today and she is one of our performance gurus here. So and there may be other people that show up in the in the channel. If they’re our guys and they say they want to be promoted, just let me know and I’ll promote them if they don’t want to be promoted.
And by promotion, I’m just referring to panelists. I’m not referring to anything else. I am not just giving out blanket promotions today. I wait for April Fool’s Day for that. So we have about an hour for this webinar, the first 30 ish minutes. We’ll see how long we have. But the first 30 minutes will be to talk about a small handful of slides we have.
So the first 30 minutes, we’re going to talk about the slides that we have here. Kind of we’ll talk about our thoughts on upgrades, on migrations, some of the tips we’ve accumulated over the years. And maybe if you’re lucky, you’ll hear some horror stories, maybe.
Maybe not. And then when the 30 minutes are over, we’ll stop recording and we’ll have an office hours. Q&A. So so the office hours in Q&A are not recorded. This presentation will be recorded and shared on the blog, but just want to give people more freedom to ask questions. Live and not not hear their sins being recorded and uncovered for the world to to review that way.
Then we get more more Q&A. Any links that go into the chat here we will have on a blog post that sums us up. We have a blog post that goes out either end of day tomorrow or tomorrow morning that just links all the links to which have quite a lot of content on operating some. We had done a webinar series with four webinars two years ago on upgrades on migration, so I’ll make sure there’s a link to that as well while the content still applies.
But but there’s been a bunch of posts this week from the team about upgrading and migrating, and that’s what we’re trying to do. You’ll see in coming months we have a theme month. So this month is upgrades and migrations. Next month is in D.R and we will have blog content, webinar content, possibly checklists and other things to download in that month about the topic, just to try and keep going back to the community with that.
That’s that. So the agenda is pretty simple. This is going to be us chit chatting here in front of you. This is interactive. So if you are an attendee, please ask questions I will ask maybe David and Jeff or whoever to just kind of watch the watch the Q&A, watch the chat and let me know. Attendees can chat with hosts and panelists will say, Well, yeah, we’ll do that. We’ll say, Everyone, you just, you know, if you’re in the session and you’re chatting your chats, we see everybody. So your name, as you’ve put it into Zoom will be visible to everybody.
So we’ll talk about why we should upgrade and we have lots of lots of reasons on that. And we’ll talk about our our approach to migrations. We should have a multistep process that we tend to follow fairly much with every client. You know, sometimes a client will dictate how migration runs and we’ll try and talk about our approach.
We will talk about what I consider to be the perils of in-place migration some. We’ll talk about the pros and cons of them. It’s mostly cons and the other day will do it if you make us. But but we’ll explain why we really don’t want you to make us. We’ll talk about some tips for working harder, not smarter, working smarter, not harder.
Or rather using like log shipping and DB tools. And really, I’d say since SQL Server 2016, SP1 especially, there’s been a pretty big argument too to not have to be your enterprise edition if you don’t need Enterprise Edition performance or availability. We’ll talk about that some and we’ll talk about the migration process and push migration support. Then we’ll start recording and do the office hours and Q&A.
Why Upgrade?
Mike Walsh
So let’s talk about why upgrade? I didn’t have a slide for that. I just want to have a kind of a discussion here. We’ll talk a little bit about some of our favorite features and inversions in a few slides. But you know, I’m just curious, team, what are some reasons why
Well, what are some reasons that you would have to upgrade? And we have this conversation with clients all the time. In fact, we have quite a few clients who are maybe behind in upgrades because sometimes because of vendors, sometimes because of myths, so on. Unmute and talk. If you have things to discuss about why, why we should be upgrading.
Jeff Iannucci
First thing would be security is the main focus. The patches for SQL Server two out of date and ten years. So currently SQL Server 2012 and earlier doesn’t have any support for security unless you’ve spent a lot of money for it. And then just mainstream, you know, bug fixes go away after five years, I believe. So 2017 is now out of support.
Mike Walsh
Yeah, they do. They do kind of an end and minus one, I think. So we had a client with some egg issues on 2016, even when it was still sort of in support. But basically Microsoft’s answer was, well, hey, this is not a problem in 2017 and we’re not going to prioritize a fix on that. So why don’t you get with the program End Point?
Jeff Iannucci
Yeah, to piggyback on what David said on security, there’s numerous security features in newer versions of SQL Server like the Transparent data encryption, which you may or may not be required to based on your organization’s requirements for compliance. They’re always encrypted. There are other features, like David said, that they’re coming out with patching and whatnot, that that don’t exist because they’re not supported on the older versions.
And, and that can be really problematic when you’re talking with a client who still has SQL Server 2008 and they haven’t even gone up to a version where you can implement some of these features.
Mike Walsh
Yeah, yeah. It is hard to talk about availability groups when that’s not even it’s not even there yet.
Jeff Iannucci
Right.
00:09:03:10 – 00:09:13:13
Mike Walsh
Or features the app the vendors need and all that. Anybody else have any kind of thoughts on you know, I think you’ve hit the big one. Security compliance are huge. But also Jack might be going to unmute. I can’t tell. Go ahead.
Jack Corbett
I’m unmuted now. Again, good for me. And I think Tara would agree with this. If you’re not on 2016 yet, you know, having query store available is a huge reason to upgrade.
Mike Walsh
Yeah, great. Yeah. The performance features, I mean just DMVs have changed now, so absolutely. A lot more HDR options too. I would say that that are out there and they just work great things. Things are buggy and V1 and some bugs are fixed and hot fixes and see use and service packs back when they had service backs. Some bugs are just fix in the next version, you know, but not critical.
Bugs typically would be buggy or rusty or things get get fixed and stuff, you know, they get re redone perfectionism. Yeah makes sense so an email to me your thoughts on why.
Jeff Iannucci
Why.
Mike Walsh
Why upgrade. I think we hit most of them and if you have any in the in the.
Jack Corbett
I’ll throw this and this isn’t directly related to SQL server but hardware going out of maintenance you know getting getting old. And if you’re upgrading the hardware, you might as well upgrade sequel.
Mike Walsh
Absolutely. Absolutely. That’s a huge one for people who are still buying hardware. It’s a huge one. And just because you’re in virtual virtualization, you know, hardware that there’s virtualization support, there’s there’s Windows support. Let’s not forget about that. I mean, we have so many people running old versions of Windows which are out of support and there are security flaws in those and exploits are out there.
So and you know, we talked about security and ransomware a few months ago in a webinar topic or maybe over the summer. And some of the things we do for security and for best practices are protective things. They help your environment so that they need to be done. But I would say another reason to do them. If that’s not enough, do you want to have to explain to the CEO, the CIO, the shareholders, why you were running on this version that had these known issues?
And, you know, let let that scare you into it. It’s a testament that that SQL Server 28 still runs on people’s production environments like that. That’s pretty cool that they built a product that could be 15, 14 years old that still works. But, you know, this is not like driving your car for 72,000 miles because you can you want to put the picture on on Facebook someday or ever.
People are posting pictures these days. You should not wear that as a badge of honor. You should upgrade. So if you were to upgrade, this is the general approach we follow and team. You know, if I’m missing something here, let me know. But our approach is pretty simple, you know, and this is with with some degrees of variance.
We tend to always start with a health check, an inventory, and you might say, why do a health check? So if somebody called me up right now on a sales call and say, hey, can you help us do an upgrade, I would suggest we start with a health check and we do that with existing clients. To all their existing clients.
We kind of watch their environments and sort of check their health as we go. But for the health check, we want to know what sins do you currently have and what bandaids do you have to overcome those sins so we can build your new environment, sort of sin free, if you will, for the few moments before your users are in there.
It’ll be a pristine environment. And we just want to make sure that we, we send you the right hardware, because a lot of people have thrown hardware at problems over the years. And maybe they have all these worst practice settings. They have no indexing, they have no performance. Their queries are running really bad. They never update statistics and they don’t need to.
Of the course they bought, they can get rid of to course they can get rid of some of the memory because they just have their brakes on by not having proper settings. Also during the health check, we would do an inventory injection to do the same thing. You can run report from SQL Server Installer Team. I feel like an old CEO here, not the SQL Server MVP.
Mike I’m sure there’s a better way to do it, but I would just run the installer and get the the installed features report. Is there actually a way to do that through query or PowerShell?
Jack Corbett
It’s not as easily as it is with the with that report, I could there is a DBA tools command to run, but it essentially just runs the installer and gets the report and returns it to you in PowerShell.
Mike Walsh
So I would probably go it because that’s the way I am. You know, I like PowerShell. I’m just not I’m not as good at it as some of the folks on the team here. And while the TB tools are so easy, but yeah, run the install features report or use a DB tuples command and see what features you have.
And if you really want to get extra credit, look and see which features you actually use because a lot of people when they install SQL Server, they don’t. It’s incredible. They press next, they check a bunch of boxes every box. Sometimes they click next, next, next, next, next finish and poof, you have a SQL Server. Congratulations and you have analysis services, reporting services, integration services are you know, it depends on whatever whatever version you have, you have all the stuff and you may or may not be using it.
And we would prefer you to only install what you need on the server. A lower surface area for attacks at lower surface area for bugs it, lower surface area for headaches during migration. And it’s just one less thing for you to worry about. And you can always trying to think I’m sure not speaking out of turn here. I would say 99.99%.
The features that you would want you could install after migration, after upgrade or after installation, I mean, I can’t even think of one feature that you would be really in trouble for not installing at install time in our and nothing you can install at all after the fact might require a restart depending on the feature and all that.
So you want to get that inventory, what do you have? What do you need and do you want to look at all the things you’re using? A SQL Server We actually David has a post that has some stuff to consider, and one of the posts that we we will have coming out will be sort of some of the things we look at.
But, you know, actually, I’ll tell you the best thing to do if you go to DB Tools and PowerShell and you look at start DB migration, we’ll talk about that in a few slides. I’ll let Jeff talk about that more than me or Jeff, Zander or Jack or whoever. But I’m not a fan of you. Start to be migration, but I am a fan of seeing what what commands that tool calls or commands maybe the wrong word, but I’m gonna use it anyway.
What that tool calls and then say what about proxies? What about encryption certificates, all the things that you might forget about until after migration? Oh, yeah, I didn’t bring the link servers service, but that’s why there’s problems. You can use that as sort of your your inventory builder. Then we like to plan the new environment and you’ll notice I keep saying new and I even put it in bold italics and point three here.
But we plan the new environment based on what your current environment is, what your performance characteristics are as part of the health check could be doing some performance analysis. Build your new environment side by side with the right version and do some test migrations. But our goal here is we want to have all of the hard work done during the day.
Most people are at their best during the day. Some people are insomniacs and they’re better at night. But but most people are at their best during the day. There’s less stress. We’re not under the gun to get the release done. So I want to try and do all the setup, all the building, all the test migrations during the day and do log shipping that we will do.
We’ll talk about that in other slides, too, but do your actual log shipping, do the exact same process you’re going to do for production? Yeah, that means you’re going to break log shipping and set it back up again. It’s not the end of the world’s pretty easy. We’ll talk about that when we get to that slide. But do the same steps.
You’ll do and then run through the migration and see how it worked and you’ll know pretty quickly where your kinks and flaws are. And now when you go to the live migration, you’ll have already done it. So that night of cut over, which typically is on a night, can be a weekend day and your environment is much less stressful.
In fact, my goal is to have our team on it on a call, be pretty bored. Like, like we’re just there as an insurance policy and we can kind of be the calm voice of reason while everybody else panics and hopefully they’re not. Hopefully we’ve inspired them not to by doing their said the same thing when they’re testing maps and all.
But we want the database to be very simple and and then we want to do our smoke test. And once the smoke test comes back and once we confirm things are right, then actually open up the tests to the to the users. And one thing that we should have ahead of time is we should have a proper go.
No, go sort of setting or criteria to find ahead of time. You know, I’m not sure if anybody here on video has been in companies before where you might have had go no go meetings, but they were really just go go meetings. It was like a thing they would do just to sort of look like they’re doing the right thing.
But any objections raised were sort of like, Oh, yeah, you don’t know what we’re talking about. We have to get this done because Bill and this and Susie in there and all these people who are over, you are insisting it gets done so because all the sea levels want it done, it has to get done immediately. So but try and define success criteria.
So when we see this will do this the smoke test once that’s good, then open it up to the users up until the open to the users you are clear to just roll back. And because we’re doing a log shoot migration which we’ll talk about in a couple of slides, all you have to do is point your see names, point your connections back to the old server and say, Well, I don’t know what happened.
We’re going to regroup and do this next week once your users are in. Now, it’s hard because once they’ve written data, it’s hard. And then I like to keep the old server up. We don’t have a straight path policy and I don’t think, but I like to keep the old server up as long as the hardware people will let us a couple of weeks so we can sort of keep that server online.
The database is offline or in recovery and see bad connections because invariably there’s some app under somebody’s desk someplace in the in the building or connected remotely that is connected to the old server and you won’t know about it until you do a migration and that person stops working and then they get mad. You can find it before they call you and you can see their IP address.
Do a and it’s look up and find out what server it is and then deal with the problem and hope it’s a minor one and then celebrate. But wait two weeks to celebrate two weeks if you’re a do crazy month and prices are quarter and processes maybe two weeks plus a month, enter a quarter and then celebrate once once you’re good that I missed anything on the approach that that at a high level no
In-place SQL Server Migrations
Mike Walsh
In what? In-place upgrades.
I won’t do a poll and ask who’s done in place upgrades before but I’m sure quite a lot of you have actually. Well this raise hands her who on this video has been part of it in place upgrade before. Yeah and we all have and I would say that the vast majority of the ones I’ve been involved with have been really successful.
99% of the time they’re successful. And when they succeed, there’s no big deal. People. And what was the concern about when they fail, though? They fail spectacularly? You know, we’ve had a couple of clients do in place upgrades against our advice, and we’ve built way more hours fixing things after the fact that we would have if we did a side by side migration in place.
Upgrades are just scary for a whole bunch of reasons. But but I’m not going to stuff the reasons. I have blog post about this and we’ll share them with the links. But as anybody here, I just want to rattle off a few reasons why in place upgrades are bad. Maybe you’ll have one that I didn’t think of.
Jeff Iannucci
So the first thing I would think of is they take forever to roll back from. Yes, you don’t have that instantaneous. Let’s just go back. So whether you’re uninstalling your upgrade, you know, your new version or whatever, whether you’re rolling back databases from restores, that takes a lot of time and a lot of time where people are very nervous because you don’t have operational databases.
That’s probably the biggest downside.
Mike Walsh
Know signals coming off of you like Jack is.
Jack Corbett
Well, I just like the fact, like I said before, you’re either getting new hardware because if your SQL Server version is out of date, your hardware is out of date.
Now. And you know, you’re getting better CPU’s, you’re getting all of that and you’re upgrading Windows because why are you not upgrading Windows when you’re updating sequel, right? You don’t want to be running cycle 2022 on Windows 2012, right? So there’s just it makes sense. And when you start combining and in-place windows upgrade with an in place sequel upgrade, there’s just so many variables that could go wrong.
Mike Walsh
Totally. So yeah, that’s a big one. And the other one I think too for me is you don’t know who built that. And you may even if it was you, you still don’t know who built that. Because if I went back to any company I was at 15 years ago, I would love the chance to redo what I built.
I was a next, next, next, next finish guy 15, 20 years ago. I didn’t know that was bad. I just nobody taught me. I was, you know. Well, I should say that Andy Kelly was my first manager, but he I didn’t install SQL. My second job, I had install sequel and it seemed like the most prudent thing to do is install these things.
I have no clue what they mean and you have bad practices. So this is a chance to start fresh with a clean slate that’s yours. Based on what you know today, did I miss any, any and and the real big reason is as a consultant, if you do an in place upgrade, there’s a there’s a chance. It’s a small chance, but it’s a chance that I would actually probably be willing to gamble on that.
I’ll make more money off of you. Like, if the whole world didn’t place upgrades and nobody ever did migrations, we’d lose migration money. But we probably make more money in cleaning up bad and place upgrades than we would have made in doing migrations. So go ahead and do them. Keep our number and call us or just listen to our advice, watch our videos and do a side by side migration.
Jeff Iannucci
Yeah. I was just going to add, you know, we’re talking about the things that can go wrong, but even if things go right, you’re still off line, right? While you’re installing the new OS. Hopefully you’re not doing that. But even just if you’re installing a new version of SQL Server while you’re waiting 20 minutes, 30 minutes, an hour or whatever your hardware allows you to, to do that in place, upgrade your breath.
Mike Walsh
The whole time.
Jeff Iannucci
Exactly right. And clinging to your rollback plan that hopefully you have. Yeah.
Mike Walsh
But there is a rollback plan. You reinstall someplace and restore re databases.
Jeff Iannucci
Right. So with the side by side, you know, in addition to all the other benefits of not having the bad things, you have the timeliness, right? You can have minimal downtime if almost virtually none if you depending on what your requirements are and how you want to upgrade.
Mike Walsh
You know, Tony brings up a great point, too, and the comments there, it’s it’s really good to be able to do side by side testing because when you do an interface migration, you’re you might have done that in your dev environment. It if you have an exact copy of production in test and pre prod and dev with 100% similarities in data and how it’s used in testing, go ahead and type in that and in the comments because I want to hire you because I don’t think that person really exists.
So it can be it can be tricky. I see a question of there’s other but Peter Schreier asked you a stat or checklist you use for your smoke test or you create this unit for each upgrade? That’s a great question. Back to the that the steps we should have a we don’t have a we don’t a generic checklist.
We use the smoke test, but we we want to make sure they include the same thing. And the reason we don’t is because every everybody’s environment is different, whether they’re using vendor apps, purchased apps, home built apps. So we don’t want to dictate how somebody is testing applications, but we want to make sure that we’re testing as many functionality as we can.
We want to see SQL Agent jobs run. We want to look for like, you know, you name the, the feature and we’ve probably seen the mis whether it’s oops we forgot about the reports descriptions oops this oops that so so really it should be a custom built checklist I would say for, for every, every client as far as what get smoke tested.
Does anybody want to you know, Sandra, I look to you as our our checklist guru when it comes to doing things consistently, are there things that maybe you would standardize.
Sandra Delany
For testing purposes?
Mike Walsh
Yeah, for the kind of smoke test.
Sandra Delany
I always push back on them that they need to push, that they need to test their app because there’s only so much that I can do. And then they say, Well, how do I test it? I’m like, Well, you know, your app, you need to test your own stuff. I can’t tell you how to test your stuff. Yeah, great.
Mike Walsh
So we can look at extended events and error log. So go ahead. David.
Jeff Iannucci
Yeah, I had a client recently that we did a migration for. We did, you know, we’re doing a few databases at a time just because that’s the, the method they wanted and part of the tests that they could have done is gone through documentation because there is a, there was a an ad in that they needed to install into the server that wasn’t working, it wasn’t there on the new server.
And so whenever we migrated it, it was they were saying it was missing it. So what they had the documents, they had the they had the file they needed to install, but that’s something on their side that they could have checked. You know, each each application has a different set of things to consider.
Mike Walsh
So that was absolutely correct.
Jack Corbett
And just from a sequel server side of things, testing, I mean, obviously the obvious is make sure I can connect from the network. So we know network connections are turned on like Mike already said, make sure I make sure backups run successfully because a lot of times we recommend that you back up to a network share most of the time.
So you want to make sure permissions are right. They are a lot of this have already done if we’re doing a side by side. Right. I’ve already done this as part of the process. And then the I my brain just I’ll test database mail if that’s used. Yes. Make sure that works. And sometimes that’s harder than others to test because a lot of SMTP servers now block external domains without having those allowed.
So you need to make sure all of that works and find out that. And then I advise the clients to say, okay, when you connect your applications, you need to test every type of functionality. You need to create a new record, you need to update a record, you need to delete a record. You need to run. You know your most common report in your apps.
But like Sandra said, because we support so many clients, we can’t know everything that they need to do in their app. So we just say test all the functionality in your apps as best you can.
Mike Walsh
Yeah, that’s because and that’s what it is agreed because you know, look like I was joking about typing a chat. If you have a perfect facsimile of production everywhere, you know, typing the chat, if you have a perfect regression testing tool in script, I mean, I’ve been doing this a long time and I haven’t really met those people.
Work Smarter, Not Harder
Mike Walsh
Work smarter, not harder. I am going to let the smarter people I’m a I farm in my spare time as many of you who know me know. So I just believe in full strength where you just sort of keep lifting stuff up until you can’t walk and take a bunch of leave. And so that was stupid is probably a better way to do it.
So I love to hear Sandra, Jeff, Jack, David, just take the floor and talk about our logic and approach migrations. First of all, why? And then let me talk about PowerShell individual. So I’m not sure if Sandra was talking about leadership shipping first and why, why we think that’s the best migration approach.
Sandra Delany
So I do a two approach. I do DBA tools. When I do a side by side and I move everything over and then I’m like, test away, test your staff, make sure everything works. If it does, then I say, if we’re going to do a migration on like a Saturday night, I like to go and do depending on how many databases they are and how big they are.
I like to do it like a week before or several days before I set up log shipping to go from current to New. And then once that set the day of my Grace and even a couple of days before I’ll check log shipping, because I can’t tell you how heartbreaking it is when you’re ready to do everything and somebody did something stupid and broke my log shipping and then I’m scrambling trying to get it.
So I check it a couple of days before I check it, the night before I run through all the jobs, make sure that everything runs. So then when it’s go time, I’m ready and I have everything scripted out. I do know that there is a small bug within DBA tools when you’re log shipping. So when I log ship and AG from an either it’s an existing AG to a new I flagship everything to all nodes so that when I go, my migration can be as short as 15 minutes and as long as maybe 45 minutes, depending on how big the database is, how a but everything is scripted out.
So if it’s a late night, I just go from top to bottom. I don’t have to think about everything. If I overthink things, that’s when I get in trouble. So I trust my scripts. I’ve done it so many times. I go from A to Z and I go all the way down. I’m like, Okay, I’m done. You guys now do your testing and then we test and we fail over, we test, we fail back whatever we need to do, and then it’s done.
So I trust my process because I’ve done it so many times. And so that’s just because I love log shipping. Even if it’s small databases, I don’t want to restore them. I want to just run through a script that I’ve run through everything and it’s super crazy fast. You can add a listener and remove a listener, add databases and using all scripting, it’s great.
So that’s why I like log shipping and DBA tools.
Mike Walsh
I love that. I would add, you know, when we spend more than 45 minutes on a migration weekend call, invariably it just goes back to the testing. It’s somebody didn’t do enough testing of their application. And we’re then taking out heroics to to try and make that thing that never were supported. This new version work that we tried to get tested.
So, you know, using a tried and true process like a log shipping in PowerShell that as Sandra described and spending as much possible time you can and testing will make you all have way better we get in we want to talk about specific things and PowerShell. I mean, we kind of hinted around them but you know, talk about debate tools and.
Jeff Iannucci
Yeah, I had something I want to mention. You you said briefly earlier, if anyone didn’t catch that one of the great things about the star DB migration DB tool, which is I believe a collection of a bunch of different other DVR tools that you can use is that it’s going to look at things that you won’t for, you won’t think about.
You can look on on the description, on the web page and you’ll see, you know, like put that in the in the chat. You can look at the web page and you can see it’s going to look at things like, do you have policy management? Do you have extended events? Do you have, you know, any credentials that you might have forgotten?
Heaven forbid you have linked servers, right? You know, things like that that you might forget to migrate. And because it’s a PowerShell tool, one of the neat things about it is you can execute it with the what if switch and then it will show you. All right, here’s all the things I would copy to your new server, and that can be really enlightening.
It has a DBA, you might say, Oh, I didn’t realize. I had these things. And then you can go and decide, do you want to migrate them or not?
Sandra Delany
Yeah, I don’t use the start DBA migration, I use the individual commands in the right order that I know. The one thing that I have noticed that using the SSI, SDB migration, using the debate is horrendous. So I always say, hey, if you use SSL, SDB, then we’ll just create a blank SSI stub and you’re just going to push the project to it because you end up spending more time troubleshooting, trying to get it to work instead of just creating a blank and then pushing the project to it.
So that’s what I do when I see it at night and I see that there are projects in there. I’m like I’m not messing with this because I know it could be a lot longer to deal with instead of 5 minutes where you guys just re push it.
Jeff Iannucci
So that’s a good point. And within the start, DB migration, you can exclude things. It’s not all or nothing, so you can use your log shipping to migrate the databases. And then there are other things you want to exclude. You can exclude databases from Star DB migration. You can also run that ahead of time or those individual PowerShell, other tools, commands ahead of time so that on your news server you’ve got everything just ready and waiting.
You just are waiting to migrate the data with that last little bit of log shipping.
Sandra Delany
Right? Yeah, I do the debate tool first so that they can test and once everything works, then I set up the log shipping portion of it.
Mike Walsh
Yeah. Gone are the days of playing this b helper of log in and trying to move search manually. Go go ahead Jack.
Jack Corbett
I was you know I was just going to jump on the whole migrating things like SAS, TB and reporting services. Those definitely add a much more complexity to a migration and need to be planned for separately than just something that does not have SAS catalog or reporting services that needs to be moved as well.
Mike Walsh
You can really mess this up, up and reporting services. There’s a bunch of subscriptions. There’s an easy way to do it, like backing up the RC key. Right, right. That’s important to do.
Jack Corbett
I mean, this is this is, you know, talking about planning migrations and why doing migration versus an in-place upgrade and those kind of things. It also allows you to separate out those things as well. Hey, let’s not have reporting services on here. Let’s not have SSA on here. Let’s have a quote by SQL Server that does that for us.
Mean that’s something I tend to recommend to clients. I can, yeah, especially if they have multiple SQL servers and they’ve got reporting services on all four instances or SSA. Yes. On three of the four instances or something like that, we can talk about, okay, can we do this migration, this upgrade and then let’s dedicate a server to that and get out of having it everywhere?
Standard vs Enterprise SQL Server
Can you save? You know, I have an older blog post from a few years ago and Jeff has a very recent one. Jeff, somebody calls up out of the blue and I need a SQL Server Enterprise edition. I’m going to do my migration. What do we say nowadays?
Jeff Iannucci
Well, you mean if someone’s already on standard and they need to upgrade it, or.
Mike Walsh
If somebody is on high praise and they think they need to keep it like, you know, do we just take that as blanket truth.
Jeff Iannucci
Typically these days? Yeah, it depends. What do you need to do? I mean, there’s a lot of people don’t realize that how much you can still do with Standard Edition. Now, obviously, there’s the the limitations on 24 cores and 128 gig memory. And those can be showstoppers, right? You can say I absolutely need one or both of those for my for my VMs or my hardware or whatever it is for my SQL Server instances.
But with Standard Edition and this was the post that I had, I had a conversation with a client who said, Well, we’ve been told we need transparent data encryption, we need our data encrypted at rest. So we have to upgrade there on SQL Server 2016, I believe. And they said, well, we need to upgrade to Enterprise Edition. And I said, Well, you could just upgrade to 2019 side by side, upgrade that in place, upgrade and plan from that migration.
But then, you know, that’s now a feature. It was all the features are available in Standard Edition as well. So I think a lot of times people see these bells and whistles. I know there are a lot of bells and whistles that were released in or unlocked, so to speak, in Service Pack one and SQL Server 2016. You know, like partitioning, I was talking about SQL Server auditing is another one that, you know, you may find value in them, but a lot of times if you’re not using these enterprise edition features, you should have to pay for them.
Mike Walsh
Will you agree in there, read ahead reads as resource gov online and next build. Those are necessary things but but a lot of what you can do in standard is, you know, we’ll talk next month what should you are on fact I’ll make sure I have a blog post that talks about the upgrades or HDR which SQL Server standard.
There are many options. If you have one database, you can do an availability group, a standard edition. Some people call that a basic e.g. I just checked out her. It is not here. So I’ll say a basic availability group. There are there’s spillover clustering. You can do it with third party tools to move data. There’s like three or four different software replication service data.
So if you just say, Oh yeah, but I need high availability, nope, that’s not enough. So we don’t care which edition of SQL Server our clients buy, we want them to buy the right edition for them. I get frustrated when somebody overspends on licensing because they bought too many course from not tuning their environment. Right? They went with standard enterprise instead of standard because they just didn’t understand.
And or some vendors, oh, you have to be price and look why all because we need partitioning or compression. Well I know if you’ve been around for the past six years but but actually it’s available so yeah. So it’s a really is part of your of migrations you should your migration upgrade which is again not in place you should be evaluating if you need to continue being an enterprise edition or if you need to move to enterprise like you might think you have to do, you should really check and see if standard is right for you, you know,
Time and After-care of SQL Server Migrations
Mike Walsh
We really talked about this already. You know, we talked a little bit and that fact, we talked about all these slides. We talked about why migrate and how to migrate. We talked about the go no go and testing criteria. We talked about doing all the hard thinking work ahead of time span. I really anticipate in my own slide we talked about wash your body connections and I talked about not celebrating.
So this slide is really a pointless slide. Now, the point is, is you’re not done until you’ve been successfully moved over a bit. So David, you have a post kind of talking about the migration process from the GPA perspective. You’ve done some migrations recently and you’ve been around to support them to talk about kind of the, the, the post migration aftercare, that that is necessary if you want it.
Jeff Iannucci
Absolutely. So you’ve sort of touched on it already, but immediately after the migration, if we move the database, it is usually, you know, 10 minutes from start to finish, including, you know, getting logged in and getting the scripts ready. So once once the database on our site has been moved, it’s usually up to the client to, you know, point their application to the new location, find out all those little rabbit holes that that application lives in.
And then we watch things like the logs on the original server to see what’s trying to log in. You know, what what errors are we seeing? It failed to open specifically a defined database because it’s offline on the old server, on the new server, failed logins because the logins weren’t moved to an appropriate time or something like that.
And then after that, it’s, it’s just watching. Maybe it’s some sort of configuration that was missed on the new server, which, if we were involved, probably shouldn’t have happened, but maybe memory wasn’t set appropriately. So just watching the server as we do most now for for all of our clients, we have we watched the alert, the alerts that come in via email and daily reports, watching what’s coming in and responding accordingly.
Mike Walsh
Absolutely. All important things and then celebrate, you know, after two weeks, celebrate and enjoy the hard work you did. And, you know, just like the elections here in the United States, start planning your next one at this point, you know, because there’s going be a new version of SQL Server right around the corner. So maybe don’t start planning right away, but at least start thinking about it and prepare it.
You know, actually, I kind of joke, but but it would be great to be on some sort of a lifecycle where you sort of know when you should be looking to get dev upgraded and queuing upgraded and patching has to happen afterwards. You Microsoft is now in a see you model. We don’t talk about patching too much, but we prefer to be normally see we want to be on the one before the latest or a few months ago if it’s been a while and trying to use because I don’t need to show you all the problems that Microsoft’s had with some use of the past handful of years.
And there’s missing sometimes. And as we use SQL Server in myriad ways, the tests I can’t imagine the testing team on the SQL Server team, all the scenarios you have to test and you probably have an environment that’s not quite like their test harnesses. So there will be bugs that get discovered. So we like to wait a little bit, let somebody else find those bugs.
So speaking of not being bleeding edge, let’s talk about being bleeding edge. You know, I was going to I was asked my team to talk about the favorite features of the latest edition, which is SQL Server 2022. But, but let’s be honest, I bet there are people who are here now who watching this video who are running SQL Server 28 I’m sorry, but but you are some of you are definitely some 2012, 2014, maybe some 2016.
So, so round robin anybody wants to Tara If you want to come off mute, you know, talk about some of the features that you think people should be upgrading for whoever wants to come off mute, I’ll I’ll pick on you.
Jeff Iannucci
I have two that I’m excited for and and being new to this somebody said in our team, I’m reading this book and it said that they are going to include contain databases excuse me, contain availability groups, which a part of having an AG is making sure the jobs, the log ins and things like that are all the same.
Supposedly that was supposed to come out in 2019 as well, but if it comes out in 2022, I think that will make a lot of ag headaches go away. And the second thing I was really excited for is being able to fail over to 20, 20, 22, can fail over to the cloud now have a distributed agent in Azure for disaster recovery purposes and you know, patching purposes, whatever you need.
Mike Walsh
Yeah the big thing there is it up to managed instance, which is kind of the change. So you could theoretically fill over to a vanilla club and now you can actually add a manager since to your ag. So you have like an on prem and then a dart manager or something up in Azure which is pretty cool. So that’s actually 2022 feature.
So you’re just going new school, you don’t even go in 2016 or 2017 features, which is fine. And we’ll see how many of the one talked about.
Jeff Iannucci
I have one that I kind of noted. It was really I didn’t see it mentioned very much at the the big reveal or whatever at the past summit a few weeks ago. And that is in 2019, they improved a lot of the temp DB performance by putting some certain system tables in memory or giving you the option to do that in 2022.
Apparently they’ve allegedly, they’re saying of reduced all last concurrency issues. So what that means is if you’re creating a lot of objects retentive as a place where you create a lot of temp tables and use your table, variables are going to have to be they get created to get destroyed. Destroyed. Great. Destroyed. Right. And by having all these objects, well, they’ve got to be there’s metadata associated with all of them.
And if you’re waiting on that metadata, then everything else that you’re doing is waiting to create those objects and to get access to those objects. So supposedly they’ve again, I’ve just seen it on some blog posts. It had some conversation that I haven’t seen any major announcement. They’re saying that they’ve they’ve really improved the performance of TV to the point that there is almost zero concurrency issues with it.
Mike Walsh
It’s beautiful. You in my eyes. What else? Anybody else have any others want to talk about? And I’ll talk about query stories. So if you’re not an SQL Server, see, the problem is the older I get and the more versions of SQL Server I know about, the more I have to pause and think. Pretty sure Quickstart came out and SQL Server 2016.
I’m not wrong. It wasn’t 2017. Yeah. Okay, so if you’re not in 2016 yet, the query stories are available to you. And why don’t you have that? It’s a really cool tool. I love the tools we use and they’re important, but I love query story. It’s it’s makes parameter stuffing troubleshooting easier and most of the time and it’s just just better.
Jack, what up? What’s for you? I mean, you put your phone back down.
Jack Corbett
Sorry, I was just confirming that. Yes, Query Store was added in 2016 initially. But I mean, every version since has had upgrades and improvements to query store. They had a query weight stats that you can report on and look at. You know, they added the automatic tuning recommendations. You know, I don’t turn on automatic tuning, but, you know, having the recommendations there, that’s the Enterprise edition, but is really a great tool to have as well.
And then for 2022, now being able to use query store on readable secondaries is a great reason to go to 2022 as well if you’re off loading reads. Absolutely, because right now you can’t do anything on the 2020, you know, on query store, on readable secondaries. So that’s that’s a big one.
Mike Walsh
Right. And I’m actually by the way, Tara actually has a pretty cool blog post that was on her blog, I think last month. I’m talking about an approach she built that we use for clients which we don’t like on actually feature, because sometimes it can be a little bit aggressive and it tries to help, you know, sometimes like a mother in law tries to help and don’t really need the help, but, you know, you appreciate the thought.
But 2022, the metadata for that I’m not trying to be to sort of store the metadata that that’s tracked for that is still there so we can see a plan regression and we sort of fail a SQL Server agent job and get notifications when that happens. So we know if somebody is experiencing what appears to probably be parameters, different problems, and then we can have a human say, yep, this is a problem.
What’s been a good plan or for sure good plan and obviously very cool features I want to talk about.
Jeff Iannucci
Yeah, I like the intelligent query platform. I’ve had a few user groups where people have talked about it and add the link to the chat just in the Microsoft Learn article about the different changes, you know, query store was a part of the intelligent query platform in 2016 and 2019, 2022 are going to, you know, 2019 did grow on that and 2022 is going to as well.
Mike Walsh
Sure.
Jeff Iannucci
And a lot of it’s a little bit too mathematical for me. But, you know, hearing people talk about it, you know, it’s going on. It’s exciting because there’s lots of things that schools like. It’s good and bad. Some of the things SQL Server does isn’t the best, but a lot of the small things can go away. A lot of the small performance issues could go away through through these things, which is exciting.
Mike Walsh
Nice. And I noticed Matthew has a question. Hey, Matthew guessing your name. So that’s an old name to see any figures worth mentioning from 2016 to 2019, standard? You know, I think some of the big ones are a lot of 2016. SP1 gave us a lot of the programmatic features like compression and partitioning and in memory and I think query story, it was a crisp one quarter get query story in 2016.
Sandra Delany
Standard.
Mike Walsh
Yeah. Okay. I know which of course I going to call them store that. That’s my bad. But yeah, I think costs are akin to standard and 2016 SP1 to between 19 standards sort of added to that always encrypted and TD TV is a big one because a lot of people need and needed enterprise because some auditor someplace says you must have a transparent database encryption.
So they went to Enterprise just for that feature. You don’t have to be Enterprise in more. So that alone is a is a reason to go from 2016 to 2019, especially for Standard Edition and my mission.
Jack Corbett
The fact that we not that we recommend it too.
Sandra Delany
Much but table partitioning you.
Jeff Iannucci
Get a standard.
Mike Walsh
Yeah that came out in 2016 for sure. That’s a big one.
Jack Corbett
I I’m glad you mentioned it, but index compression as well.
Mike Walsh
Yeah, compression. A huge, big.
Jack Corbett
One that you got with 2016 USB one, USB two and Standard Edition.
Mike Walsh
If think about compression. Compression actually saves you memory to right. So so you’re limited to 120 gigs of memory. Will you have less data in memory. Yes. So that’s good.
Jack Corbett
And and you know, query store is available on all editions. You just don’t necessarily get the automatic tuning with Standard Edition, but then you can do a lot of the stuff the automatic tuning does. You just have to do a little bit more work to get to that information.
Mike Walsh
Absolutely. Yeah.
Jeff Iannucci
Watching this later that they could probably determine that we’re begging people, please at least get up to SQL Server 2016 regardless of which version you’re on or edition on.
Mike Walsh
There’s no reason not to. And if you’re going to 2016, you might as well go up a little bit higher. And that’s all because database compatibility mode supports backwards compatibility. So, so from your application perspective, 99% of the time, there should be no difference on your vendors and your your code supporting 2019 or 2016 or 2022 or 2016, because Microsoft wants you to buy licenses and they want you to upgrade.
So they want to make your applications not break terribly bad when you upgrade. And if you do proper planning, you should see those features. Go ahead, Jack. You’re going to say something to.
Jack Corbett
Yeah, well, I was going to say that I can’t remember what I was going to say, but that’s okay. I was going to say there’s just a lot of features that, you know, at at 2016 just added that there’s really no reason not to upgrade, even if your vendor says they don’t support it like you were saying. I mean, there’s now, you know, they’ve added so many things that are now database scoped configuration.
So you can say this database don’t you know, use the legacy cardinality estimate or this database, don’t this database use maxed out full access? This database is, you know, maxed out for sure, you know, so that, you know, I remember when I first started working with Sequel, one of the big selling points was you don’t have to change as many things as you do with other database assets.
Right. Well, as SQL Server has matured, there’s been more knobs added that you need to know about in order to tune in order to make sure that, you know, your old app still works well on this version. But, you know, it used to be a lot of the stuff was a trace flag that you had to had to set that affected everything or it was a server level configuration.
But you know, in the 2016 timeframe and later, they keep adding more things at the database level.
Mike Walsh
Absolutely.
Jack Corbett
Which is good. And another feature I would like to talk about that is not I can’t remember what version it was added in obviously unfortunately might have been 2014, but is back up to you URL? Yes. So you know, you can back up, you know, through 2019, you could back up directly to Azure Blob Storage. You know, there’s some time and some cost and you know, there’s some things you need to know to make that work well.
But especially if you’re in Azure backing up to you, URL is a great feature and with 2022 back up to you, URL will now support back up to S3 directly. So you know, there’s to me that’s just, you know, a great feature. There’s definitely some things, you know, I love the fact that they added S3 and I’m going to have to check 2022.
I haven’t tried this yet, but I’ve hope they’ve changed back up to you URL, the the API they’re using for backing up to Azure to blob storage so that it supports larger file sizes than it could.
Mike Walsh
Yes. Yes. But the back of the big databases.
Jack Corbett
But yeah. So without having to have 42 backup files.
Mike Walsh
Yeah, that’s right. As you done for somebody.
Jack Corbett
Yes. So, you know, but I mean, you know, with all of the issues with ransomware and malware attacks, having your backups offsite. Yeah. Is huge. And being able to do it directly makes that so much simpler.
Jeff Iannucci
Yeah.
Mike Walsh
In fact, that’s a good segue to the homework slide. But you know, it’s not actually on the homework slide. But Jack’s right, so I think upgrading your SQL Server is really dramatically important. But I think even more dramatically important is backing up your SQL Server databases and not just backing them up. You’re hopefully all doing that, although we do a lot of health checks for clients and you’d be surprised, but you need to be backing up your SQL Server database someplace other than your network.
You need to have it offsite, remote to a cloud. Someplace is not accessible. Like Jack just said, no access to the outside world can’t be deleted. Something just journaled because ransomware is real and we have seen clients call us up in really bad situations and sometimes you can help them. Sometimes, unfortunately, we say, Hey, I don’t feel good taking money from you because it’s too late.
There’s nothing we can do for you because because of where your situation is. So backing up databases and making run books and having a binary document so that homework zero for your developers out there is is going forget everything we just said, make sure your backups are going offline. If they are, do a health check. We can do a health check only just you guys.
No, I’m not trying to sell services, by the way. I don’t care who you use. If you do one upgrade every 5 to 10 years, you should really call a server consultancy. Yeah, we’re bias where people, server consultants. There are many, many firms out there that are just like ours that I would say go work with them. And I don’t care who you use as long as I’ve done this before.
We do a lot of migrations and upgrades and that just means we get to we have enough scar tissue that we know what we can sort of instinctively do things differently. If you do it once every five, ten years, who knows what you’re potentially missing? So find somebody to at least advise you and give you some teaching help and they can walk you through it.
So there’s the sales pitch and I don’t care if it’s just for somebody else, but I consider getting help for them. But do a health check again. That’s going to be important. But again, we can sell you health check, but you can also start your own. When we send the recap, slide off, send you some of the links we say we share about how to do your own health check.
There’s a lot of links on with the video on it too. I’m pretty sure, but do your own health check and see what’s good, what’s bad, and understand your environment. Then research your version and why you should upgrade and then you know, don’t let the vendor scare you. That’s one thing we do with clients sometimes as we talk to the vendor, say, listen, what you’re saying is not true.
And by the way, if you’re a vendor that’s in Microsoft, like your Microsoft certified partner and you don’t support the latest version, you actually are in violation of your partnership agreement. Just so you know, if you’re a vendor, we can be really nice with your vendors and we can ratchet up the pressure as needed to gain compliance from your vendors.
And we’ve done that before. We’ve had some interesting chats every morning for the next and months until you do a migration, please, before you brush your teeth, look in the mirror and just say in place. Upgrades are bad 20 times. That’s just too monitor. You need to do. Plan your upgrade, test it upgrade and joy. But we’re going to wait two weeks before we enjoy it.
So that is that is your homework. Our next office hours will next after this will have up or over time. But we’ll still start recording and do office hours for those who have questions or if there are already. And we will do better with time management next time. Obviously we had a lot of good content, a lot of people involved.
So I’m not apologizing for giving content, but we can save more time for questions better next time. So we’re going to turn recording off in a minute. Next month, we’re talking about high availability, disaster recovery. That’s actually the latest blog post on our blog right now. There’s a registration link for that webinar, if any questions, email me them.
Just mentioned the subject line. You were part of the webinar. Not going to send you a bill to answer a question. If you send me 20 questions in one day, I might get suspicious and send you a bill and I will check IPRs if you change your email address. But thank you so much. Coming. I’m going to stop recording.