Before you Upgrade Your SQL Server – SQL Server Upgrade Webinar Episode 2

This article is part of our SQL Server Upgrades Made Easy series:

• Why Upgrade?
• Before You Upgrade
• During Your Upgrade
• After Your Upgrade
• Recap Chat

Upgrading SQL Server – Week 2 “Before you Upgrade SQL Server”

You can watch week one here on the blog or directly on youtube. Please consider subscribing to the channel as we’ll make it a rule that all webinar content from the free webinar series will be shared on youtube and sometimes here on the blog also.

This is the second video from the series from the webinar last week. Here we chat about the things to consider BEFORE you start your SQL Server upgrades.  We discuss questions you should be asking yourself before any SQL upgrade project starts. We talk about rollback plans, CNAMEs, checking health before, etc.

Sign up for the rest of the series and to get the full cuts from Webinar Ninja with any Q&A each week. Subscribe to the youtube channel to be alerted of the live chat hangouts and to see the upcoming talks in our continuing webinar series. In March, I’ll start with a multi-part series on High Availability/Disaster Recovery options for SQL Server. If you have questions, you may not need consulting. Just check out the series, attend live and ask your questions or come to the Q&As. I’ll try and answer most questions left in the posts also. Yes, I own a consultancy, and if you need in-depth help, I think we can help – and I trust you can figure out how to reach us. But the point of this content and the webinar series is to help encourage you and show you how can you do more than you realize! See you on Thursday 2/21 for part 3 on “Doing your SQL Server Upgrade”!

Links Discussed

• Using PAL to collect perfmon data
• Glenn Berry’s diagnostic queries
• SP_Blitz
• Questions to ask your software vendors
• DBATools.IO – great free powershell modules created by the community
• Download our free Self-Assessment health checklist – (this link is a direct download of the PDF).

Transcript

(I’ve used a transcript service for this and I reviewed most of it. It’s a work in progress and there may still be some errors!)

Welcome back. Last week we sort of talked about why should you upgrade and the real answer is “why not upgrade?” I mean SQL Server has changed so much since the SQL Server 2008 that you’re running or the SQL Server 2012 or 2014 even and hopefully not the SQL 2005 and 2000, hopefully you’re off that. So I think we all understand the why, it’s really why not. But the world has definitely changed as we talked about last week.

So what I want to talk about this week is a little bit more about “the before.” What are some things to consider? And this is going to be not demos, it’s really going to be chats so if there’s questions ask them live in the chat, if it’s germane to this topic we’ll kind of put me off topic and we’ll talk about it. We’ll keep this 30-35 minutes. I have a little more slides than maybe that time so if we need to we might go longer or I might just cut myself off and extend more next week.

So anyway we’ll go in here. So again this is the series. Last week was big picture, kind of a “why why why” and what’s changed. And I gave you some homework to basically analyze the versions that you think you want to be at, analyze why you wanna upgrade, maybe start conversations with your vendors because the vendors are the pains here and they’re the ones preventing you from upgrading in a lot of cases, talk to the company and really see what are you going to do. I mean especially if you’re on SQL Server 2008, 10, 11, 12 year old database technology that’s gonna be end of life, with all those caveats we talked about last week where Microsoft is making some special deals. If you just give them more money they’ll still give you some patches or if you go to Azure they’ll give you the patches for free. And I talked to you about my opinion of that. So it’s time to upgrade. And the homework was really to start getting yourself ready and getting your organization ready.

Today we’re gonna talk about what to do before, how do we plan our upgrade project right. We’re about to do a massive change to our SQL server. It’s not easy and it’s not small taking a SQL Server that’s running really well here for 10, 11, 12, 14, even 7 or 4 years, and move it over here to a totally different SQL Server version, to a completely different infrastructure potentially, upgrading to a version that you’ve never been on. The world’s changed. You’ve been so busy you’ve not learned new stuff. So what are some things we can do to make this successful and that’s what we’re talking about.

Again I’m giving you something that could probably be a 30 or 30 hour course and a lot of consulting. I’m trying to give it to you in a handful of 30 minute webinars. So we’re not going to cover everything but we’ll get to a lot. I already told you who I am. I’m a guy who’s a SQL MVP, I’ve been doing SQL for 20 years, I’ve been consulting for 10. I run a company called Straight Path Solutions. And more importantly that’s my blog. I’ve been blogging there before I was consulting there and so the company name just formed after my blog name. I’m a user group leader and I have a lot of scars from SQL Server upgrades. In fact I used to have a full head of hair before I started upgrading SQL Servers.

So this is a good quote from a poem. It’s, “The best laid schemes of mice and men” and then it’s said in like Old English. But it’s “The best laid schemes of mice and men often go awry.” So we need to be prepared. Louis Pasteur says “Fortune favors a prepared mind.” When we sit down to begin any upgrade project we want to make sure that we can answer a bunch of questions.

And what are the questions? Well, half of SQL Server or more is just basic human nature stuff. So can you answer the who, what, when, where, why and how questions. And if you can answer those questions you’re halfway to having a good upgrade. A lot of times when we go in as consultants and help somebody do an upgrade it’s not really going in and doing a bunch of really insane and in-depth analysis. Yes, we use our SQL Server knowledge but a lot of times it’s really half sort of what I call “business therapy”. We’re just helping them ask these questions. We’re just coaching them along on saying “What are you trying to do, where are you trying to go, why? Who’s in charge? What team do you need to have in place? What are you gonna go to? So what version of SQL are you going to go to? What’s the problem you’re trying to solve.” That’s also the why question and I have a capital Y in the W slide, I’ll have to fix that later. “When do you want to go there and when are you gonna test it and how much time can you be down for? How can you do it with minimizing that? What are the risks? How are you going to do it?”

Ron says, “Why are you still on SQL Server 2000?” Yes, we’ll talk about that in a couple slides. But absolutely, “How do you roll back.” And next week we’ll talk about it.

I absolutely hate, and I’ll use that word, I hate in place upgrades. In place upgrades when they work are OK and it’s like “Phew, it worked.” But even when they work we’re still on the hardware we were on. We’re on the same OS we were on. We’re still living on a bed of the stuff that we did five years ago or 10 years ago, or in Ron’s case, 19 years ago. We’re still, we’re living on that bed and if we learned anything and hopefully you’re learning something every year … I mean I wish I could go back to a company I used to work at full time and say “Oh I wouldn’t have built like this, I wouldn’t have made the config like this, I wouldn’t have done the operating system like this. I would have done 64K drive allocations instead of the default.” Because 15 years ago I didn’t know about that. I would have built with the CPU configured this way. I would have had less CPUs with better performance and I would have potentially had and standard editions of Enterprise. All these mistakes your current environment is built upon. And they’re not mistakes that are bad per se, it’s not that you’re a bad person because of them, it’s just you didn’t know.

So you know much more now. You’re going to webinars with some guy from New Hampshire. You’re in the SQL Server community. You’re taking classes. You’re reading books. You know more this year than you knew last year. As a consultant I do that all time. I’ll go back to clients, “Hey listen, remember that thing I told you do three years ago? That was good three years ago but we can do it better now and here’s how and here’s why I learned it.” So even if an in place upgrade succeeds I don’t like it for that reason. But when in-place upgrades go bad and “best laid schemes of mice and men often go awry.” Well the best laid plans of in place upgrades can go awry and when they go awry they go awry bad and the rollback, that’s not a fun weekend. It’s like an “I hate my job. I’m updating my resume” kind of weekend. So we’ll talk next week about why I prefer migration style upgrades. But the biggest reason is rollback is just as easy as changing your CNAMES and we’ll talk about CNAMES in a few slides actually.

But it’s as easy as changing your CNAMES, pointing back to the old server and saying ‘I don’t know what happened. We had a problem.” It’s Sunday night at 2:00 in the morning or Monday morning at 2:00, I don’t wanna deal with it anymore. We’re rolling back. We’ve made the decision. Because when you start thinking about your who, what, when, where, why and how you’re going to ask yourself questions like how do we know we’re successful, at what point do we say we’re we’re putting the stick in the sand and we’re saying, “This is the line.” Where’s your red line and how do when you’re at it and who’s watching it? Because as technologists, I have a blog post out there, maybe Mike and my team will find it, but I basically talk about don’t forget to fly the plane. And I talk a lot about aviation disasters as well. As technologists and engineering type people we love to get into the nitty gritty and we might get so lost trying to fix a problem that the sun starts coming up on Monday morning and “Oh no, we’re up the creek.” So you have to have somebody watching the time and being willing to say “No, we’re rolling back.”

And with a migration upgrade rolling back as you change your CNAME over you say “OK, we didn’t hit the upgrade this weekend.” Instead of saying “Oh no we’re in trouble” and then feeling bad and sort of getting dejected, you say “We failed. Great. We learned what didn’t work. Now let’s go forward and learn from those mistakes and make it even better try next week.” Yes we had a downtime window. There’s a little opportunity costs or some sunk costs there that you might get worried about, “Oh no they’re going to hate me.” But your users I guarantee you will hate you a lot more if you kept going and going and going and you lost data.

“Do you have any specific steps, plans, tasks in Excel you’ve prepared?” Yes actually. I’m working on a document for that and we’ll probably have that checklist next week. So that’s one of the downloads that we have a slide already talking about. But again I can’t give you all of the steps but I’ll give you some of the steps that I like to use. So that’ll be in a download next week with a session next week on the doing but definitely have a checklist. Checklists are really important and we’ll get to that in that slide. So I’m gonna keep going because if I stay on one slide I’ll get lost but ask questions and let me interact. Again, the whole purpose of why I’m doing this and why I want to continue doing webinars for as long as you keep coming to them is to interact, to ask questions, to challenge me because I’m going to learn from you. So let’s just keep the dialogue going if you have questions.

So this is another good quote. “To remember where you come from is part of where you’re going.” So that quote should scare you. I bet you Ron, like Joseph from last week and judging from you talking about SQL 2000. I bet that quote scares you. You need to know where you are. You need to know what’s good in your current environment, what’s bad in your current environment. So even before you start with a migration plan, even before you pick your hardware or pick your cloud provider or pick your virtualization layer and spec out your servers, you need to know what’s good and what’s bad in your current environment.

So again, I’m not trying to talk about my company to try and sell my company. I’m talking about my company because it’s an example of how I do this all the time for customers. When we do an upgrade and you can do this yourself with tools like Glen Berry’s Diagnostic Scripts, Brent’s Split Script, you don’t have to call us. We don’t need to do your health check. You can do your own health check. You’re here. You figured out how to come here. Send me an e-mail. And in fact I’ll give you a download in an e-mail after this that links to our self-assessment PDF that you can just take on your own and not have to give me your email address for. But the point though is, do some assessments, look at your environment and understand what’s good and what’s bad and then look at your performance.

I have a blog post that talks about using the PAL tool, the performance analysis for logs tool, that’s a free Perfmon based tool that can do some analysis and you can use as a baseline. If you have Century 1 or Red Gate SQL Monitor or any of those third party tools that do monitoring. The point is to start doing some monitoring. Look and see what your resource utilization is. But don’t just look at your resource utilization, also look at your queries, look at your settings are you using more resources than you need to because one of your feet is on the gas and one of your feet is on the brake. So are you hurting yourself and is that making you use more resources than you need to.

So do this health assessment and every time we do an upgrade or migration I do a health assessment first. And sometimes the customer will say, “Well why would you do a health check for us? We’re getting rid of the server.” And I do the health check first because I want to see what you have and I want to see what’s different and what’s unique about your current environment. Because maybe you forgot that you have a default collation of something other than the normal default collation in your instance and you have four databases that have it this way and two that have it this way and you forgot that “Oh yeah I have this linked server that goes to this DB2-AS400 environment or something. And by doing your health assessment you get to analyze what you have and make sure that you’re thinking about all these things. So the health check really gets you in the mindset of sort of doing a, for lack of a better word, a tabletop exercise of thinking about when I do my upgrade what’s going to go wrong, what’s going to go right, where am I hurt today.

So a couple of things you can do is do a health check, do some performance analysis and don’t forget to look at the licenses your using. If you’re on 2000 or like Ron said if you’re on 2008 and you bought your licenses earlier with software assurance the licensing scheme has changed.

So do you need to be on Enterprise? We talked about that last week, I won’t rehash that. But SQL 2016, actually I will rehash, I’m about to. SQL 2016 SP1 gave us a lot of Enterprise features in standard. SQL Server 2014 and higher we can use 128 gigs RAM in standard. I’m in the middle of teaching an HADR class and some of my students are here from that class this week. You can do a failover cluster and then you can do a transactional replication for reading and you can have H.A. and you can have a read only replica through the replication without having to buy Enterprise Edition to use availability groups. You get failover cluster for H.A., you get replication for reading and you can do log shipping for D.R. and log shipping is perfectly respectable. When we do our webinar series on HADR I’ll talk about that. Log shipping deserves more respect. So do you need to be Enterprise Edition.

And don’t forget to look at your licenses and look at what kind of CPUs you can do and don’t just buy. “I don’t know what we’re using and I hope we don’t have a bunch of performance problems so let’s go with the biggest and beefiest box and get 482 cores just in case we need them.” That’s going to hurt you. So we want to try and be a little more spartan.

Now if we’re building on physicals we might want to get it closer to being right because on a physical we can’t just go and change CPUs if we realize that we got it wrong. On a virtual that has proper allocation and has enough headroom of more CPUs we can get it a little bit wrong and add CPUs. So I would start smaller CPU core wise and do some performance testing before you upgrade and watch and see what you need and we can add CPUs.

And then the cloud. If you’re building a VM in Amazon or in Azure you can get it wrong all day long and just reconfigure your server and have a brief blip and change your server from a small box to a gigantic box with minimal effort and minimal downtime. So we can get it a little bit wrong but we want to do this performance analysis to get it kind of right.

So another good quote that I like is, “If you don’t know where you’re going you’ll end up someplace else.” Isn’t that true? I mean and I’m preaching to the choir but how often do our companies do this to us? How often do our managers and our project managers and our vendors give us a marching order, and hopefully nobody from my team says, “All the time.” But give us a marching order and say, “We’re going this way, we don’t really know where we’re going but we’re gonna go this way.” And there’s no plan. There’s no goal. There’s no, “Here’s where we are now, here’s where we’re going. And this is the road to get there. This road can’t get there. This road can’t get there.”

Another saying out there is, “If you don’t know where you’re going you’ll end up there.” So again from the homework last week did you pick a SQL version? Again, I prefer SQL Server 2017 I think for most new environments that are calling me. 2019 is kind of around the corner but just based on my experience I think 2017 is a good place for most people to go. Even 2016 is an improvement over your SQL 2000 and 2008. If your vendors are a little slower and they’re getting nervous about 2017 you have more time before end of life for those two.

Are you going to new hardware? Hopefully you’re not doing an in place upgrade. Is it time to move to a virtual environment? Is it time to look at the cloud? And we’ll do a little sidebar chat at the end here if there’s time for the cloud to talk about that. But ask these questions today. Don’t ask these questions one week before go live, or even one month before go. Ask these questions now and figure out what are we about to do and how are we going to get there and why. And again go back to the questions I asked earlier; who, what, when, why, where and how. And all those questions, as you start asking them over and over again, and you could even ask underneath so like why, why, why, why? Like ask the why and then ask the why about the why and keep asking whys until you get to the discrete tasks and discreet questions and worries you should have and these become your checklist. And again we’ll give you a simple checklist next week. But that checklist is not going to be end all be all. It’s not

going to be end all be all it’s, not going to be your checklist; it’s my checklist and it’s generic, and you have to fill in the details and the gaps, because your environment’s different, and that’s why you have to spend this time not doing work, not troubleshooting problems, but asking these questions and thinking about your upgrade.

I like this quote too, “The most exciting phrase to hear in science, the one that heralds discoveries is not eureka, but now that’s funny.” That’s from Isaac Asimov. We got to test; the point here is we have to test. We shouldn’t … It’s exciting but not really a good exciting, necessarily, when it comes to a SQL server upgrade. That’s a cool discovery, like when you’re playing with moldy bread and you discover antibiotics, it’s cool when you discover how radiation can be used for medicinal purposes and discovery purposes; it’s not a great thing to say at T-1 second, before go live in the middle of an upgrade like “Oo, I forgot about compatibility level. Oops, I forgot about the encryption. Oops, I forgot what the link servers. Oh, no, this version of SQL in this compatibility mode, do not work. I should have known that ahead of time.” You don’t ever want to say “Now that’s funny.” During your upgrade.

So we need to test, we test two things. And really not just test two things, we also need to talk to our vendors and get our vendors to bless us; that’s hard. We talked last week about it, and if you want to vent about vendors here, you can, just remember this is recorded. And I believe your chat’s record it too; so don’t mention your vendors by name.

But our vendors, bless their hearts, they make software and that’s good because we have jobs in our … We have tools and our customers and our jobs are there for us because of these vendors and their tools; vendors can be difficult. So we have to talk with our vendors and say, “Can we upgrade?” And sometimes the answer is “No.” And then you shouldn’t just say, “Okay, I’m sorry.” You should say “Why?” And if it’s because “Well, we don’t know if it works or not.” Well then say, “Well, if I upgrade, will you still support us? Do you have a certification process, when will you be certified in this?”

Get those questions asked. Get somebody who runs the application, get your person whose calling support all the time and has a good relationship with the vendor. Get management, executive management, to champion this idea of compliance, and performance, and reliability, and scale, and saving money and whatever their benefit, whatever their sort of go to mantra is.

Don’t lie, but an upgrade is probably going to help them in these regards, you can save money, sometimes, especially if you go from enterprise to standard. You can save money if you can reduce your core count, because the new processors are much faster. You can save money by moving to VMs that are more efficient, by looking at the cloud, potentially; you can go from CapEx to OpEx.

There’s better compliance, there’s better security, there’s better availability; all these things are available for you when you upgrade. So, don’t lie and don’t be sneaky, but whenever you talk to somebody, you want to figure out what’s in it for them and play that up. “Hey, if we upgrade, remember you talked about how important compliance is and you’re worried about this merger, and you’re worried about the due diligence report? How cool would it be to show them that we’re using these great DevOps methods, and we have a proper security system setup, and our SQL server is way in compliance, and it’s not gonna be out of compliance in July of this year, and we’re doing all these best practices, and we’re doing you know whatever; we’re doing whatever feature, we’re using kubernetes…”

I don’t know … Don’t go there, and I will not have the webinar series on containers, I have not had to play with containers; unless you want to come and host it. But the point is, is get them excited about the benefits that are happening. You’re excited about the upgrade, there must be reasons other than “I hate SQL 2000 or I hate SQL 2008.” There’s reasons you want to upgrade; get your executive team to support that, get them to hammer the executive team at your software vendors, and you will find roads open up.

But again, if the answer is “No.” And then you ask why and they have no good answer, maybe that’s a chance to say, “Well, we’re going to upgrade, will you support us?” And they say “Yeah, I guess you will.” Then say, “Hey, how about you help us do some testing, or we’ll do some testing can we share our results with you?”

Now you’re getting the executive team bought in by the features, you’re getting your vendor bought in now too by helping them do some of their work; and you’re going to finally get your wish of being upgraded.

Now some vendors are going to say “Oh, whatever you’re a small fish, we’re a big customer, or a big supplier, I mean, you know we don’t influence me.” So the size of the vendor matters to you, and the size of your account matters too.

But once you talk to the vendors, once you’re good or it’s in house apps, you can’t just do it; you have to test. And there’s really a few kinds of testing,. You have to first make sure “Will this database work on SQL server x?”

So if I’m going from 2008 to 2017, the only real way to know it is to do a few things. You can run the Upgrade Advisor; the Upgrade Advisor will analyze your database and tell you a lot of things about your instance that won’t work. But it also gives a lot of things that say “You might check this, you might check that, we can’t see your code.” So it’s gonna give you a lot of good things if you’re doing some obvious things that are not supported. So, if there’s obvious deprecated feature problems, you’ll get a bunch of good advice from the Upgrade Advisor; and that’s a free tool from Microsoft.

That’s step one. Step two is if you’re an application that’s mostly view stored proc functions based and not application code being ad hoc SQL coming down, you could actually open up SQL server database tools and create database project, and I’m going to try and get Jack in my company to write a blog post about this, because he’s the star of this for our company.

And he will, I just need to get him some time to do it; we’re too busy But you can use database tools to actually do a build against the target version. So you basically reverse engineer a database, you build a project … I’m a DBA so that’s my impression, Jack will give you a better, more developer friendly impression.

But you’re basically going to put this database into a project, reverse engineer it, and then you’re going to do a build; not a deploy, but you’re going to build and you can build against the target version. So you build against SQL 2017, you build against SQL 2016, you build against Azure SQL. And when you do the build, it will actually give you a pretty good analysis of what will work, and what will not work.

You can then look at your build report or the errors or the warning and see what things will just period not be supported, you cannot upgrade to this compatibility level into this version because of X, Y, and Z. Or warnings, like “Hey, you should probably change this, but you don’t have to.” And that will then give you a good list of knowing “All right, where can we go and what’s the risk?”

A lot of things have been deprecated, which means Microsoft threatens they’re not going to support them. But if you do a Google search and, again, I’ll look for this link and I’m going to make a note here. If you just do a Google search like breaking changes SQL 2016, you’re going to find every version of SQL has this this knowledge base article or this tech support article from Microsoft, that lists what features have been truly deprecated. So what features will be broken if you’re doing this, if you upgrade here, this will not work.

And there’s one for every version of SQL that has documentation; so I think it goes back to 2012 or 2014. And you can look at that list, as well, and know what breaks. Microsoft has done something kind of interesting; they’ve deprecated a lot of features, but they’ve also continued to allow them to be there. And I think it makes sense, because if I was a software company and I wanted people to upgrade and get give me license money for upgrades, I’m going to do my darnedest to make quite a lot of things continue to work. It just makes more sense that way. So here’s an example of one of those, I’ll put in the chat window.

So you can look there at the breaking features and you’ll see “Hey I’m doing this, it’s going to be broken.” And you’ll see the list is a lot smaller than you’ve been fearing, I think; it was for me when I started looking at these a few years ago more regularly. “Oh, well they’ve been threatening that but it’s still there.”

And you have a couple options. If you do this build, and you realize “Oh no, I’m using whatever, and I can’t operate in 2017 mode.” That does not mean you can’t be in SQL server 2017. It just means you have to maybe run in a lower compatibility mode. Now running a lower compatibility mode means you might not be able to use some of the features that have come out for that version of SQL, but you can still for that database. But you can still use the instance feature, so compatibility level can be your friend and that can be a good stopgap measure.

You say “All right, cool, we’ll upgrade but we’re going to keep our database in 2012 compatibility mode because of X, Y and Z that broke in 2017. And then we’re going to work on a project, now that we’re here now that we’re on new hardware, now that we can do some of these security things, now that we feel better about being supported, we have time now to upgrade our database and change our practices.”

Then there’s no way around. Yeah, the DEA tool is actually pretty cool; we use that sometimes, so the DEA … And I’ll put a link up to that. The database experimentation analysis tool is what I think that stands for. Denis here says. And yes, you’re absolutely right, sir, that is going to help you. So the only part is, it’s hard to capture the workload; so maybe I’ll have Joe on my team whose been playing this, write a blog post about that.

It’s hard to get the right workload, because you’re basically doing a trace replay, and trace replays can be hard. You have to get a backup of the right point, you have to get the trace replay, and you have to start it just right; but it can be a cool approach. There’s also extended events if you’re on 2012 and higher that can show you when you have a deprecated feature fire.

But at the end of the day, you have to test. You can’t just do the Upgrade Advisor or run the DEA that Denis here talks about, and I’ll make a point to talk about that in a couple weeks and maybe get good Joe to write a blog post for that too; I’m actually making a note here about that too.

And that will help, and extended events will help, but none of these things replace good old fashioned testing; you have to test it, and if you don’t test, you’re sort of … you’re at risk. And, again, remember compatibility level can be a stopgap help too.

So we’re getting close to the end here, and I’m just blabbering away by I think we’re cool. So another quote is an old Chinese proverb “Ink is better than the best memory.” Yes, we hate documentation as technologists; most of us do, some of you may love it, and if you do, send me your resume. I’m joking, I mean, not necessarily but anyway documentation is a pain, I get it.

We have to document, you have to document what you have. So if you’re going to do an upgrade, you do not want to leave some feature on the floor. Now we’ll talk next week in the doing section a lot about DBA tools. DBA tools are an amazing, bow to them, set of power shell scripts that do everything for you. I mean, you name it, it does it.

You can go to dbatools.io if you don’t know what they are. But they have to start DB migration, and if you look at the code for that, the documentation which is here in the slides … As I’m doing this, I just realized we need to send the slides out, so I’ll probably put the slides online on my website too, so you can click on the links.

But if you look at start DB migration, you’ll see all the a other jobs that it calls, and basically documentation tells you what you should be remembering., “Do I have credentials, do I have.” People forget proxies all the time, what about database mail settings, what about service broker, what about linked servers, what about database encryption certificates?

What about … I’m just trying to go over things that people forget a lot. Agent operators, agent alerts, that you have special alerts you set up, your jobs … I hope you don’t forget jobs. But there’s a lot of big things and little things to take care of. And by the way, when we talk about doing next week, we’ll talk about using DBA tools to do migrations. I’m going to raise my hand and tell you the truth, when Straight Path does your upgrade, we’re going to rely on these tools a lot; we do. So we’re cheating, I guess. But I don’t know, is it really cheating to use a good tool that gets your job done? I don’t think it is.

But these tools can help you, but again they don’t replace you; they don’t replace your documentation, you’re thinking about it. So you have to document what you have. It’s good to get a run book, it’s sort of an idea of what you have. There’s third party tools out there, I think, SentryOne, when they bought Pragmatic Works bought … I forget what the tool was off top my head, but they bought a tool does database documentation.

There’s other tools out there, I think, Red Gate has some great tools for that; there’s a lot of scripts out there with power shell document database scripts all over. Yes, I agree, Stephane, database tools is great.

So, this will help you. And then once you understand what you have, you have to plan your documents. This is what the question that Alis asked earlier, you have to plan your upgrade. How are you going to do it? And these are the basic things and, again, next week we’ll actually have a checklist for you to see. And, again, it’ll be a start of a checklist; you have to finish the rest of it, I’m not going to do all your homework for you.

But what are the order of operations on upgrade night? What are the things you do week … Here’s upgrade days is T-zero. What are you doing T-14 days, T-7 days? What are you doing if you’re doing a log ship style upgrade to be able to do a fast cut over; at what point do you take your final full, at what point do you take your final diff, at what point you take your final log backup, at what point do you confirm the users arare out, how do you know people are on the system, have you got a row count?

I can just use a database tools and as a query can run, I get a row count before and after; make sure row count matches. And if it doesn’t, I say, “Oh no, we have to roll back or we have to figure out why.” We’ll do another backup, potentially. What are your roll back criteria? What’s your roll back plan? And if you’re doing an in place upgrade, I’m sorry, your rollback plan is run away, update your resume and change your name, join the French Foreign Legion and become their DBA, you know it’s going to be ugly.

It’s possible, but it’s going to be ugly. Your rollback plan might be install a new SQL server and migrate backwards to it. But now, your versions are higher and SQL can do forward restores, it can’t do backwards restores. So how does that work?

Think about this, in my spare time, I’m a volunteer … When I’m not teaching, when I’m not working, when I’m not running my farm here, I’m a firefighter / EMT in my local fire department. And sometimes in training, we get lazy and we don’t really want to take out all this stuff, and the CPR dummies, and do physical training. So we’ll sit around … They might Andy, the French Foreign Legion might use SSIS, that beard will give you away every time now.

But we’ll sit around the table sometimes, and we’ll just have tabletop exercises. And we’ll talk about, you know, somebody will call out a scenario and say, “Hey, we just got toned to a 72 year old woman experiencing shortness of breath with nausea; she has diabetes.”

And then we’re going to talk about it and we talk about what can go wrong, and when you get a bunch of EMTs around it can… You have gallows humor, it can get pretty bad pretty quick, actually. But we talk about what can go wrong, and when we’re talking about what can go wrong, we end up thinking about what can go wrong.

So then when we get those calls, we think “Hey let’s check this, it’s never this, but we talked about it, so now we’ll check it.” Or if we get confused, we’ll think about that. We asked questions like “When you get to a car accident, who’s the one that’s looking to make sure there’s only one patient? Is there somebody hidden in the backseat, is there somebody in the woods?”

We think about these things in conversation. So if you talk about your upgrade, get the right people involved, the people who are going to be involved and say, “Hey, what happens if this happens, what happens if that happens?” And have a contest, who can create the craziest fail. Let’s talk about those and then figure what we do with them.

What do you do with your old environment, how long do you keep it up for? Those are the kind of things that go on your checklist. And then as soon as possible, even before your upgrade make, a run book. Document your new environment; if you could hit by

a bus or if you win the Megabucks or if you join the French Foreign Legion and became their SSIS trainer, you want to make sure the people left behind know what to do and know about your environment. So there’s three documents you have to write during your upgrade, I’m sorry there’s no way around it. You can delegate it and hire a project manager or a technology writer, but you have to still come up with content. Have you guys heard about the story about the roasting pan that was too small? I’ll tell a story real quick. It’s a long story, but we’re getting late.

Basically, this husband was watching his wife cook or a wife was watching her husband, doesn’t really matter, somebody’s watching their spouse cook. They’re about the put the roast in the pan and before they did it, they cut the edge off the roast, about that much, not much, and they put it in the pan. The spouse looked at him and said, “why did you do that?” And the person who did it said, “I don’t know, let me call my mom. Hey mom, watching you grow up, you always cut the end off the roast. Why did you do that?” And the mom says, “I don’t know, let me call your grandma, she always did it.” They call grandma finally and grandma starts laughing, she goes, oh sweetheart, I did that because the roasting pan was too small.

So, you could still be cutting the roast off today and the only reason you’re doing it is because that’s how you’ve always done it and you didn’t know anybody. So, you’re going to a new environment, you don’t have to take all the stuff you had before. You don’t have to do all the worst practices. This is your chance to plan slowly and carefully and do right things. Fix security, why is everybody in SA? Why does your vendor have remote DB access? Why does your vendors have SA? You should let your consultants have SA, but why does your manager have SA?

What features are out there that have come out in the past 10 or 15 or 20 years that you’re not taking advantage of? Why are you on enterprise? Why can’t you be on standard? What about the cloud? Do we have to be on prem? Is it time to go into PaaS or IaaS? We’ll have a sidebar chat on enterprise or standard and the cloud probably later at this point. Maybe in the live Q&A on the 7th of March.

What do you think? What can change? Is there somebody here in the comments who knows that there’s something you’d love to change during your upgrade. Go ahead and type it out. What do you want to change that you’re doing? Besides being on SQL server 2000-2008. What’s one feature in 2017 or 2019 or 2016 that has you excited and has you chomping at the bit? Just go ahead and type it if you want. Don’t be shy.

Not it. There’s another quote. I couldn’t find a great quote for this one. My kids, and you have kids you hear it too. Whenever it’s chore time, or whenever something breaks in the house, nobody did it, and nobody’s around. Everybody’s tired. Then they don’t get tired when chore time is ending. You need to know who. That’s right, that’s the extra kid, amen. We have a lot of those extra kids around here.

Who’s doing what? DBAs are not an island. We’re the most important role in the company. Even more important than the SSIS developers and the management. Without us the companies fail. I understand that. I get that. I’m a DBA. But we’re not an island. It pains me to say it, I won’t get into politics or anything, it takes a village. It really does take a village, and you really didn’t build that when it comes to our careers.

It takes a lot of people to do something right. It takes everybody. We have to identify duties and responsibilities like Stephane says. Is your SAN team available? Are they ready? Do they know their steps? Have they confirmed that back ups are offsite? Is your VM team ready? Are they going to be on call the night of go live? Is management ready? Who’s going to test this now, three months before we do the upgrade? Who’s going to test it with the smoke testing that night? Have they built a testing checklist, and do they have a bunch of things they’re going to check? Who’s going to record count check and confirm it?

You want to get other people involved in this with you. Application owners, amen. I didn’t even put that. Great point Ron. Application owners need to be there to do the testing and to be approving.

The vendor. Do you have a vendor support person available? Does the vendor know you’re doing this? Do they monitor the environment and will they get a bunch of emails? What about the compliance people?

Listen, when you do a building project in my small town, if you don’t call the building inspector first, the building inspector, he’ll never see this, he’ll be a real jerk. It’s probably like that in most small towns. If you go to the dump or the transfer station in my town, and you just start trying to dump something that maybe is on the edge or maybe shouldn’t be in there. Maybe you should get a $1.50 charge. If you just start dumping it, the building inspector will get mad at you for your building hands down every time. The dump people, with their limited power, they will get crazy, and they’ll get the whips out and they’ll start … it’s bad. “I’ll take away your privileges.” It really is bad.

But if you ask them first. If you say, “Hey listen, I’m doing this, can you just come by and give me a couple of tips?” They’re going to be great. They’re going to be your friend. Most of the time. Sometimes you can get in trouble and you have to beg for forgiveness instead of ask permission. But most of the time if you ask first, you’ll be good.

Get your compliance people on board early. Tell them what you’re doing. Tell them why your doing it. Tell them what the new features coming, and say, “Hey, what should we do for the migration? Can you help sign off on this?” That can help.

Then, at the bottom, who makes the no-go decision? The go/no-go. Who has authority? Get that authority. Get the steps. Get the criteria. Get your redlines configured before you get into migration week and migration night. Data security, yeah, absolutely. I kind of consider them part of compliance, but data security team, absolutely.

Who makes the go/no-go decision? Will you have a go/no-go meeting? If cutover is going to be the weekend, at four o’clock when everybody’s still in the office on Friday, you should have a go/no-go meeting and get all these roles, just like a shuttle launch, to say, “Yup, we are go. Yes we are go. Yes, we’ve confirmed this, we are go. Yes, the checklist is good. Yes, we’re good. The SAN is ready. Here’s my number. Here’s the call through, here’s the bridge line we’ll;l keep open all weekend.”

There has to be authority. Can’t be a go/go decision. Too many times companies have go/gos. I’m not really a dancer, but I’m dancing. It’s embarrassing that I’m dancing. I’m probably turning red. It’s not a go/go meeting. It’s a go/no-go meeting. And anybody can say, “No, I’m objecting.” It doesn’t mean you’re going to stop, but you’re going to stop and listen.

I’m listening to a book right now called Good to Great. It’s a really good book about business. He has a great idea where everybody, he’s a professor, and everyone of his students has a red flag or a red card, and they can raise the red card at any point in time, once for the course, and when they raise that, they get the floor. They’re listened to. Their idea may not be implemented, but they are listened to completely, and … not just, “Yeah, yeah, yeah, okay.” But they’re listened. Like, “I’m going to sit down and actually absorb what you’re saying and I’m going to listen to you, consider it, and try and do it.”

Everybody at the table at your go/no-go meeting has to have a red flag and they have to be respected. And you may be able to reason it out and say, “Okay, I hear your concerns. Here’s the answer. Here’s how I’ll mitigate that. Are you okay with us being go now?” And they might say, “Yeah, now I’m okay. I just wanted to be heard.” Or “I didn’t know you were doing that, so now we’re all set, and now we’ll amend the checklist.”

No, John, there’s a lot of accidental DBAs here, and that’s one reason I have this whole series is most of us … I started 20 years ago as an accidental DBA. And I still am an a accidental DBA. I’m an accidental DBA, an accidental farmer, an accidental business owner. I’m an on purpose firefighter because I decided to do that. I went to training. But everything else is accidental. Mostly on purpose father. And accidental husband. I’m a by grace husband. It’s like, “Why did she marry me.” So you can call that accidental too.

A couple of miscellaneous things. I just couldn’t think of a clever quote here. If you have any questions go ahead and ask them. And yeah, it’s a great question, I’ll get to that.

CNAMEs and Aliases. I think we’ll talk more about that next week. But when it comes to migrations, I love CNAMEs. I hate a user connected to … if I have a SQL server called PRODSQL01, and on PRODSQL01, I have a couple Citrix apps and I have Great Plains, I’ll just use Great Plains as an example. I might create two CNAMEs. My network might not like me, but I’m going to create two network aliases, basically. One called GPSQL and one called CitrixSQL. And I’m going to try and use this migration to start using the CNAMEs, because if I do that, when I have my next migration in three to five years, because remember Microsoft is releasing new SQL servers every year, every two years. You’re going to be upgrading again in three to five years, especially after you realize how easy it can be.

Now that I use my CNAME, when I do my migration, and I don’t do an in place migration at all. I do a migration not an place upgrade. When I migrate, I’m just going to repoint my CNAME to that new server name. Nobody in my company has to know the server name. Nobody has to care about it. Just us in IT. And now they do their testing and everything’s good, we just keep this name pointed. Oops, we have a mistake, we just point the CNAMEs back. No more going and updating a bunch of Citrix web servers for published apps for those darn thick clients that we still support. No more finding Config strings everywhere, and waiting two to three weeks before we finally capture all the failed login attempts on the old server.

And we’ll talk about that next week too. I’ll keep an old server up for a week or two as sort of a honey pot server. Not really looking for attacks, but looking for bad applications that haven’t their changed your connection strings. You’ll see a lot of login failures for two weeks after a migration. Then you just search the IP address and say, “Hey what’s running in this enviornment, and what data were they trying to connect to or what login are they using?” And then you can fix it. We’ll talk about that next week, why I keep an old server up.

Dan asks a great question. What if you are an island? You’re the DBA, you wear all these hats. Well, you have to have a conversation with yourself. You still have to … it’s the same thing, it’s more work for you because you have to really think about these things through all those lenses. If there’s other people in your company that can help you, you can get them to talk about it with you, but at the end of the day if it’s on you and you are all “these people”, you have to talk with yourself. It’s a pretty small table for the go/no-go. But think about these things.

Don’t just think of it like the DBA. It’s very hard, but you have to break the tunnel vision and say, “Okay, I’m going to step outside of myself.” I’m not talking metaphysics here. I’m not talking any astral projection or anything like that. But step outside of yourself and say, “I’m going to stop and I’m going to look at this. If I hired a security person, what would they say? If I hired a developer, what questions would they ask me?”

Sometimes the best thing to do get out of yourself and to think about these things is really to just sort of ask the questions that you think the person you’re trying to simulate would ask. If you ask yourself questions, and then maybe when you answer a question, ask yourself why. Be annoying. Say, “Hey, what kind of service count should we use? Should we use GMSA? Well, no. Why? Because it’s hard. Why? Because I don’t understand it? Because I haven’t learned it. Why? Because I don’t know where to find information.” Is GMSA the problem there? Not necessarily. Maybe you’re the problem. As yourself those questions.

Dude, where’s my download? I told you I’d have downloads this week. I’m still working on a couple of downloads. I can’t do a … I haven’t figured out Webinar Ninja in. So I’ll send an email through Webinar Ninja to everybody’s who’s been here, and you’ll have a health assessment. We have a free PDF that really just does a simple health check. Honestly, it’s good, but I would just go ahead and look for Glenn Berry scripts or run for SP Blitz. Use one of those tools to grab information about your current enviornment.

Probably by tomorrow I’ll have the PDF of questions. These are the checklists that Alis asked about. These are the questions that you should ask yourself about versions and the questions asked before. So those are some questions to help you just think. I’m a big fan of questions. I think questions help you understand more about yourself. The more questions you ask, and if you ask questions about the questions, and you keep asking questions, you eventually get the real problem.

For some recap and homework. Collect perf data. Whether you use a third-party tool like SentryOne or Redgate or SQL Grease, I’d never heard of them until Brent’s been advertising them. Use a third-party tool. I’m a SentryOne guy. I’m a partner there, I’m unashamed to say it. It’s a great tool. But they’re all great vendors out there for the most part.

Collect your performance data with one of those tools. And a lot of them have free trials. It’s not illegal to use their free trial. It’s a free trial. Use it for that. Use PAL. I have a blogpost about PAL, maybe I’ll get that into an email recap as well. Use performance analysis for logs and perfmon. Just grab perf data.

Do a health check. Again, just use Blitz, use Glen’s Scripts. Use our PDF to help guide you. Again, I’ll get that PDF to you tomorrow. Then start thinking about what could go wrong. Start your document. Start documenting what you have today. Go to DBA tools and look at the script library and look at all the categories. Oh yeah, I forget we built that proxy server. Oh yeah, I forgot we have, we changed colations. Oh we have encryption don’t we? We need to get the master key built ahead of time.

Look at those tools and start building all the things that you have to think about before you do your migration. That’s going to be helpful.

Next week we’re going to go a little more in depth. Again, we’re going to talk about doing your sql server upgrade. How do you install SQL best practices? And actually one of my team members just sent me that link to the PAL tool, so I’ll go ahead and paste it here. Again, it’s an older post of mine, so I make no guarantees about grammar. I type quick sometimes.

Next week, same time, noon, we’ll go 30-40 minutes, 50 minutes as we did today. We’ll talk about how to do the SQL Server upgrade. Best practices for installing SQL Server. We’ll talk about checklists. Surgeons and pilots use checklists. If it’s good enough for them, it should be good enough for us. Practice rollback. What do I really think of in place upgrades? I don’t think anybody’s surprised what I really think. I’ve already told you. I don’t like them at all. In no way, shape or form. In fact I have a really, really, really long answer on DBA.stackexchange. Again, nobody’s probably surprised that it’s a long answer if you know me, or if you’ve heard me talk, you’ve read my emails. But it’s a long answer on why I don’t like them. Let me count the ways.

And then minimizing cutover downtime. You can really do a failover in a couple minutes if you had to, and make it pretty painless on your users, because a lot of you are in 24 by 7 operations. When is the downtime in a hospital? When is a downtime for a 9-1-1 dispatch center? There isn’t one, really. The 9-1-1 is probably Wednesday morning at ten.

Free downloads next week. So next week we’ll have the download about sample items for an upgrade checklist to get your minds started. Again, it’s a sample checklist, so it’s not going to be your complete checklist. It’s going to be a beginning checklist for you to use. We have a SQL server installation guide that we use internally, and we’ve actually PDF’d it and made it a … it’s actually a downloadable, marketable content that we’ll actually probably put in our website and make people give us an email address.

I’ll just give you that PDF next week once we’re done with our final review internally. But that will help you make sure that you’re thinking about your SQL install right. That’s what we’re going to talk about next. Again, that’s all I have. I’ll probably stop the broadcast in a little bit, but I’ll stick around for questions. Thank you so much for showing up. Thank you for being here, thanks for listening.