1

I'm looking for ideas here. I've inherited a large SQL estate with multiple servers in multiple configs that has grown over 10-15 years with no specific DBA-skilled individual in charge. I'm the first DBA to join the organisation (and the only one, currently).

As the title says there are ~700 DBs on ~70 servers with different functions, and different approaches to security within them. A mix of home-grown and vendor supplied databases.

I've installed the Microsoft Monitoring Agent across the estate to build an overview in Azure of what's going on - and as it turns out there are quite a few problems.

I'm not really sure where to begin here. I could tackle the high severities first for instance. There is also some mileage in identifying servers that should be decommissioned. I've already upgraded servers that are running unsupported SQL versions (2012 and prior).

But a lot of the recommendations are things like "disable the SA account" which is not always as simple as it sounds, particularly if it's been used to drive certain database functionality.

Where would you start? What would you do?

Azure vulnerabilities screenshot

3 Answers 3

7

Nope

I wouldn’t do anything. I’d bring the list of issues and servers to stakeholders and business owners and ask them what’s important to them, along with any pros/cons/technical challenges with implementing each item.

Once you have a prioritized list from them, you have a few things:

  1. A list of improvements you made as you make them
  2. Approval for all changes, in case anything backfires
  3. Written proof that you told them about these things, in case anything happens as a result of not implementing them

Until then, it’s a meaningless bunch suggestions.

Remember: you don't work for the Microsoft vulnerability report. It doesn't pay you to do anything.

1
  • The problem I'm facing the most is to do with the sheer scale of the number of suggested vulnerabilites, over 4000. I'm trying to find an approach that might reasonably break up the workload. Actually - I've just had an idea. I spotted that you could run the Vulnerability identification query in the Azure console and export the results to a CSV - this might help me prioritise my responses in a much more useful way
    – Potatan
    Oct 11, 2023 at 13:50
1

Where would you start? What would you do?

What are the business objectives?..because that's really what will drive your decision making here on priorities.

In my opinion, 70 servers sounds a little unreasonable to try to manage, without the equivalent staff to scale. That's speaking as a whole for the business when keeping in mind the droll stuff like OS updates, security patches, server level backups and redundancy for disaster recovery, network security implementation, etc. Stuff that hopefully you don't have to manage, but even a small infrastructure team wouldn't be sufficient for.

So if the business had zero objectives, my personal goal would be to reduce that count, to make management easier:

  • Firstly are there any deprecated databases that can be immediately turned off?

  • Then are there any that can be combined?...e.g. 10 different users with 10 copies of the same Test database that could have really be a single database.

  • Then I'd try to consolidate them to the same servers in a way that makes logical sense. I much rather manage 1 database server with 100 databases on it than 10 servers with 10 databases on each. But obviously this will be dependent on a few factors around how heavily the databases are used, the security model of them, and perhaps the size of them.

But if it were doable, 10 servers with under 100 databases each, is more manageable than 70 servers with 10 databases on each, I believe.

Aside from that, database integrity, reliability, and security objectives are typically important to think about, and CYA with, when you're the one responsible for so many servers and databases. So analyzing what's the major issues in each of those aspects, and organizing them into a digestible format with a gameplan for upper management is probably not a bad idea.

2
  • Thanks, some useful suggestions there. One issue is that a lot of the databases are vendor-supplied so use the vendor's own ideas about what constitutes reasonable "security". However, removing of legacy databases/servers and combining others may have some mileage in it as an initial way of approaching the problem.
    – Potatan
    Oct 11, 2023 at 13:53
  • @Potatan For sure, np. I can't imagine that they're using 70 different vendors for supplying their software as well. All software from the same vendor should share the same server instance (without a good reason otherwise specific to that app). And perhaps it's even reasonable to use the same server instance to host databases for software between different vendors. Just depends on the vendor and software.
    – J.D.
    Oct 11, 2023 at 15:01
0

Step 1:

Verify backup and restore procedures.

Having current backups are the key to mitigating data loss and corruption.

Step 2:

Backup all data and db schema to local test servers, if possible. If not prioritize(step 3) the production servers.

The test servers do not need to be large servers, just large enough to run verifications in a reasonable amount of time.

The main benefit is to allow local offline testing of the security fixes. Local testing iterations are much faster and usually cost less than cloud-based testing.

Step 3:

Perform a risk and utilization analysis on each database, is the cost higher in the loss of data or in the release of data?

Log server utilization, memory usage, IO operations and processor utilization during standard use conditions across a week. Note peak times.

At this point the 0% impact loss databases will become apparent and will be archived and taken offline.

Step 4:

In your test environment, correct a single database problem and fully test the application against it.

Once the change passes all tests deploy the changes to the production servers. Verify correctness again.

Move on to the next issue Repeating Step 4.

Step 5

Consolidation and capacity planning.

Analyze the usage reports collected in step 3. Are there any non-overlapping peak times that can be consolidated into one server?

Can you replace a bunch of lower utilized servers with one large one?

Are you duplicating data across databases that could be stored once and shared?

What kind of replication strategy is needed to ensure availability when a failure occurs?

You must understand the trade-offs with each decision made here.

There are simply too many to enumerate here.

1
  • Thanks - I'm not sure I can conjure up the resources to recreate our entire estate in a test lab, but some of your other suggestions are useful. Step 3 - perform a usage analysis of each database: Are you aware of any tools that could help with this? 700 databases is a lot to tackle one at a time. thanks
    – Potatan
    Oct 11, 2023 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.