Tag: administration

One of the research areas at the Redgate Foundry is in estate management, trying to better understand how people manage an estate of servers. These could be physical servers you own, VMs in a hosted or cloud situation, or even a platform service like Azure SQL Database or AWS RDS. In today’s world with a myriad of choices, it’s easy to lose control of your estate of servers.

I saw a quote recently from someone that was struggling with their estate. They said: “The moment it goes red, if you don’t know what it should be, then you’re clutching at straws.”

This particular person was struggling with rebuilding systems after a failure. If one of your VMs dies or gets removed, something that is easy to do in the cloud, do you know all the settings to rebuild it? Not just the CPU and RAM, but all the SQL Server configuration settings you might have changed? These days there are lots of database settings, which ought to be in those backups, but there are plenty of other items that could be hard to recover.

Most of us don’t experience large disasters in our instances, but we do get regular calls, tickets, and complaints about performance. We might even find out that settings get changed in a team environment that we are not aware were made. Monitoring systems might catch this, but not necessarily every little setting that we care about. Building your own system is complex, and more importantly, I find that ensuring all new instances and databases that get deployed are in your system is hard.

I didn’t think much of this project when it started, but I realized this is more of a problem for people when I attended a session at SQL in the City London in 2019, where our Foundry presented on a few projects. I had assumed that most people would be thrilled with the Spawn project, but most were more interested in estate management. Lots of interest in having software to ensure you not only know what your settings are, but when they might change and how to get them back.

Part of building software, especially with DevOps, is ensuring you know how well it is, or isn’t, performing and if the things you change are useful and valuable. Certainly this is important for those DBAs and system administrators, but I think it’s also important to ensure you share those settings with developers. Having all your systems configured in the same way through the software process helps ensure more consistent performance.

Some sort of estate management is important, and no matter how you might monitor systems, ensure that you are including the various configuration settings as a part of that.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

I’ve never been interested in working on large systems. I know some people relish the challenge, and certainly there is a lot to learn, but I’ve always valued my sleep and time with family. I like getting away from work, seeing my wife and kids, and I’ve found enough after hours work on (relatively) small systems to keep me occupied. To be fair, I’ve often dealt with large in terms of numbers of systems, rather than a large single system. For some reason, managing 200-300 instances that have 50GB databases is much less stressful than a 10TB single instance, at least for me.

In 1999, I was offered a job managing a 13TB database on SQL Server v6.5. I declined that job, and was glad I did. Running that system would have been a nightmare.  I think a 40TB SQL Server 2017 instance is likely easier to manage, though maybe Taryn Pratt would have taken either challenge. I was interested to see her writeup on migrating a 40TB database recently, though I’m glad I don’t manage this system..

Even if you don’t have a large multi-TB system, the challenges Taryn faced are similar to ones I’ve had on smaller systems, where I was still space constrained. In some places, a 50GB system might be limited in storage, and you might encounter some of the same issues. You also might have the same moving target problem, where the information you are moving keeps changing and your processes struggle to catch up.

This is the type of documentation and evaluation that I’d like to see more people produce about their daily (or weekly or yearly) challenges. Having examples of what worked, maybe what didn’t, and the thoughts behind solving problems helps others learn and grow their skills. Even if you only write this for internal co-workers, it’s a good learning experience.

It’s also good practice for your communication skills, which are going to be very important in the new normal of pandemic work environments.

I’d love to publish more stories like this, of how you solved the challenges you face as a developer or DBA. If you can write about things, send me a draft. If you need help anonymizing things for your employer, I’m happy to help. If you don’t want to do this publicly, at least consider documenting this type of effort internally. Others might learn, and you’ll have a nice item to present to your boss at review time.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

Many of us have worked in environments that have a number of SQL Server instances. Different versions, editions, and mostly different hardware profiles. We deal with different applications and software that places different demands and requirements on our database systems. Some of us are even dealing with multiple datastores, trying to integrate SQL Server with Oracle, PostgreSQL, Elasticsearch, Redis and more.

Keeping an inventory of instances and patches can be a challenge. I hadn’t thought this was a big problem, but I’ve been surprised how many people like the Estate Management Versions page in SQL Monitor. This gets a lot of use, as people work to keep track of their systems. With that in mind, I wonder how many people have to support the “average” SQL Server that Brent Ozar supports. He runs the SQL ConstantCare® service for his customers, and regularly reports on average data every quarter.

In his latest report, which contains data from over 3000 servers, we get a picture of what various organizations run for their instances. The version breakdown is what I expect, and I’m not surprised that 2016 is the most popular. This was a major release, with lots of changes. I think 2008 R2, 2014, 2017 were relatively minor releases. Few changes, and not good timing relative to other releases.

The data shows most people have supported versions, and I think some of the licensing rules and breaks to lead many people to think about getting Software Assurance to upgrade once in awhile. I also think it makes sense that many people manage relatively small databases, well under 125GB. If you do the math, 47% of these instances are these relatively small sizes. However, it is interesting to see nearly 15% of his clients are > 1TB. That’s quite a spread.

With data sizes like this, you see lots of smaller hardware sizes. I don’t know how this might compare with your organization, but it’s interesting to think about how you might fit into these averages. Is your organization doing a better or worse job of giving you resources for your data size? Keep in mind, there isn’t correlation here, so you don’t know if the 30GB database has 4 cores or 24 cores.

I think having data like this is interesting. I do wish Microsoft would release more specific stats, like the version counts and database sizes, with hardware averages for those data sizes. I know why this doesn’t make sense for them to release the data, but I still wish they would let us know more. As Brent says, they tend to present on really recent technology, which many of us might not use. I’m sure many of you have a variety of instances, but you’ll easily be able to see if your spread of instances looks like Brent’s client base.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.