Managing lots of databases
Do you ever feel like you have too many databases to manage? Managing lot of databases can get tricky and in this episode we explore some of the techniques you can use when you need to manage lots of databases. Tracy Boggiano joins us to discuss some of her techniques, as she has managed over 300 instances.
Links to tools recommended by Tracy
dbatools – Instance migrations and best practice implementations
dbachecks – SQL Server Environmental Validation
Telegraf – Collect performance metrics into one system and display that information
First Responder Kit – Brent Ozar’s blitz package
sp_whoisactive – Activity monitoring stored procedure
Central Management Server – administer multiple servers
MSX/TSX – Create a multi-server environment
Query Store – Query Store usage and adaptive plan tuning usage
“Once you do figure out [all of your databases], I recommend having a Central Management Server, where you register them all and keep up with them in groups that you can run queries against them.”
“Collecting data for your monitoring comes in handy, so that you know what you need going forward.”
“I like to be efficient and make things where I don’t have to do things more than once, so if I get asked to do something twice, I try to find a way to do it
Listen to Learn
01:33 Compañero Shout-Outs
02:14 SQL Trail
02:40 Intro to the guest and topic
07:27 Figuring out your full server inventory
08:44 Once you’ve got it figured out, good ways to configure for running queries
10:08 What has the dynamic been for Tracy between system admins and DBAs?
11:12 Use Telegraf to monitor all of your servers
13:26 The kinds of things you’re tracking and how you do it
14:22 Managing Query Store at scale
16:25 Using the master server/target server structure
18:43 Tips on upgrading so many servers
20:32 Keeping track of your server’s ownership with a spreadsheet
22:06 At what number of servers should you be automating things?
24:25 The difference between managing a small number versus a large number of servers
27:19 Statistical sampling for administration purposes
29:28 SQL Family Questions
34:44 Closing Thoughts
Tracy is the Database Superhero for DocuSign. Tracy does many things in the Microsoft Data Platform Community and is Microsoft Data Platform MVP. Tracy has volunteered through the NC Guardian ad Litem program since 2003 being the voice for abused and neglected foster children in the court system. This is her passion outside of SQL Server and favorite, most important job. Tracy also volunteers on the Protection and Advocacy for Individuals with Mental Illness Council for the Disability Rights of North Carolina and has received her Mental Health First Aide certification. When Tracy isn’t volunteering at the places above, she likes to be making a difference somewhere else.
Meet the Hosts
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 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 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.