Episode 161
| February 20, 2019
| 45 minutes
Migrating/Upgrading 8000 Databases
How do you migrate 8,000 databases on a single instance in an environment that doesn’t tolerate downtime? Today’s episode focuses on migration strategy and how our guest Andy Levy faced the challenge of moving all those databases in the time constraints given him by the business. We also talk about some of the strategies Andy employs to administer and monitor all those databases.
Episode Quotes
“When people say, ‘I’ve got a lot of databases on my server,’ somebody will say, ‘Andy, you’re up.’ I SELECT COUNT(*) FROM sys.databases and just screenshot that, paste it and say, ‘is this how many you’ve got?’”
“Our organization had never done a major upgrade of SQL Server like this before, so there was some nerves, there was some trepidation.”
“I wrote this whole process for doing this whole thing, and when it started not working the way it should have, I started taking it really personally.”
“Even though the meat of the process took way, way longer than we anticipated, because our developers and our testers could pivot and get me the information I needed to support them, they were able to test in parallel with the rest of my tasks, and we hit our schedule at the end of the night.”
Listen to Learn
00:38 Intro to the guest and topic
01:54 Compañero Shout-Outs
02:37 SQL Server in the News
04:27 With just once instance, Andy had to migrate over 8000 databases from SQL Server 2008 to 2016
07:55 Their process of deciding what method to use – the methods that wouldn’t work
14:20 The method they settled on using
18:30 Andy’s Thanksgiving to upgrade day job: clean up the supporting cast
19:40 The implementation day plan and how it went
23:43 What happened at 5 minutes past 11pm?
25:34 Processes and steps that would Andy do differently
29:53 Do you use SSMS to get into that many databases?
31:16 Things that you see with large numbers of databases
34:42 DBATools was a lifesaver on this project – you’ve got to be automating things
35:22 SQL Family Questions
44:30 Closing Thoughts
Credits
Music for SQL Server in the News by Mansardian
Our Guest
Andy Levy
Andy is a database administrator, PowerShell fan, former developer, Open Source contributor, RVer, and connoisseur of dad jokes (not in that order). He’s worn a number of IT hats since 1999 before landing in database administration, including web server administration and development, systems integration, and database development. When he isn’t picking queries apart and wrangling unruly herds of databases, he can be found planning next summer's family camping trips or mentoring the TAN[X] FIRST Robotics Competition team.
Meet the Hosts
Carlos Chacon
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene Meidinger
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin Feasel
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.
Want to Submit Some Feedback?
Did we miss something or not quite get it right? Want to be a guest or suggest a guest/topic for the podcast?