What are some sensible approaches for dealing with 4,000 vulnerabilities across 700 databases in 70 servers

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.