Week Two: Laying the Groundwork

The first week marked the beginning of my journey in this new role. I spent that first stretch getting my bearings meeting the team, setting up my tools and now, with that phase behind me, I'm officially working remotely from the Philippines. Every meeting going forward will happen virtually, which, honestly, will take a little adjusting to. But I’m optimistic.

In the first week, my focus was pretty straightforward: get connected, get familiar. This week, I started digging deeper. I shifted gears toward the actual data specifically, the SQL databases we’re managing in AWS and GCP. The setup spans multiple regions, mainly for privacy and security purposes, which adds a few layers of complexity.

As I dove in, I realized I needed a structured way to understand the environment. So, I built a checklist. Not just a “to-do” list, but more like a roadmap something to guide immediate fixes and also help plan for future improvements.

My Database Analysis Checklist

Here’s the working list I put together. It certainly evolves, but as of that moment, this was my foundation.

Baselining

First step: understand what normal looks like. Without a baseline, it’s impossible to spot real problems.

Tip: There are plenty of tools that help with this. It's worth investing the time upfront to gather solid baseline data.

Monitoring

One of the first things I asked myself was, "Are we actually keeping an eye on the right things?" You can’t make solid decisions without visibility, and that starts with tracking the basics, CPU usage, disk space, and how queries are performing.

There was already a solid monitoring setup in place. Prometheus, Zabbix, and Grafana were all running, and alerts were piped into Slack, sorted by severity across different channels. That covered a lot of ground, So I focused on a few important gaps.

Alerting

Monitoring is great, but it’s only half the picture. You need to be notified when something starts going wrong, ideally before users notice.

So I started working on the missing pieces:

• Alerts for when the SQL Server service or Agent goes down

• Notifications for long-running queries

• Notifications if someone connects to the primary node (when all client traffic should be going to the secondary)

Basically, I’m filling in the blind spots, things that could easily slip by unnoticed but have real consequences if left unchecked.

Data Retention and Archiving

For databases that grow rapidly, managing historical data is critical. Partitioning big tables is on my radar here; it's something I’ll implement as data scales up.

Data Types

It sounds basic, but using the right data types matters a lot more than people think. Optimized types can save storage and make queries noticeably faster.

Indexing & Maintenance

Back then, indexing felt like walking a tightrope. You needed them for performance, but the wrong ones or too many could really bog things down. I started by reviewing the existing index landscape, looking for overlaps, missing indexes or anything that had clearly outlived its usefulness.

Since it was an OLTP system, index maintenance was especially painful. I made a few bad calls early on trying to rebuild indexes on massive tables during off peak hours, only to end up causing serious blocking. Lessons learned the hard way.

After that, I pivoted. Rather than constantly trying to rebuild indexes on heavily used tables, I focused on a longer-term solution: partitioning and data compression. This was especially important for the massive tables, some holding over 800 billion rows.

Partitioning made everything more manageable. Instead of treating tables as a single, indivisible unit, I could target specific partitions for maintenance. That alone reduced locking and fragmentation issues significantly. Even better, truncating data per partition instead of running expensive DELETE operations felt like a cheat code. What used to consume tons of CPU and log space could now be handled almost instantly.

Compression played its part too. Beyond saving disk space, it improved I/O performance and reduced memory usage, especially when dealing with older, less frequently accessed data.

But the real win? With partitioning , I can no longer rely on regular index rebuilds. I could simply focus on updating statistics, which was far lighter on resources and kept the optimizer happy. This shift will make the system more stable and a lot easier to maintain in the long run.

Auditing

Tracking who’s making changes and when is important for both troubleshooting and accountability. I've setup basic auditing using Extended Events and automate sp_whoisactive in an email alert to detect user activity in primary node.

Permissions Review

Who has access to what? "Least privilege" principle was already applied, but I'll be honest, permissions are usually messier than they first appear.

Vulnerability Assessments

Security isn’t a “later” task, even if it's tempting to treat it that way. I’ll start periodic vulnerability scans soon, after the immediate performance work is settled.

Backups

Obviously, backups are in place... but I’m reviewing the redundancy strategy. I’d rather not depend solely on the primary servers for this.

Integrity Checks

I'm checking whether secondary servers are running DBCC checks. They should be. It keeps the primary servers from getting bogged down.

Server and Agent Settings

SQL Server settings were all pretty much standard. A simple diagnostic will do. I'll cover this up on the following blogs.

Priorities: Sorting Urgent from Important

As I worked through the checklist, I found it helpful to categorize tasks by urgency.

Top Priorities

• Monitoring and Alerting: You need to know something’s wrong before someone else does.

• Indexing and Maintenance: Good indexes and regular upkeep keep the system fast and healthy.

Medium-Term Goals

• Data Types and Partitioning: Not on fire, but essential for long-term performance and scalability.

Lower Priority

• Vulnerability Assessments and Permissions Reviews: Important, but they'll wait until after performance issues are addressed.

  
  

Of course, priorities could shift. If I stumble on a giant table that desperately needs partitioning now, that task would jump to the top of the list. It’s not a fixed plan more like a flexible guide.

Skills I Realized I Need

One thing that stood out this week: PowerShell is going to be a must-have skill. I’ll need to automate all sorts of tasks across multiple databases, like gathering reports and checking configurations.

Some specific areas I’m aiming to learn more about:

• PowerShell scripting: For running checks and maintenance jobs at scale.

• dbatools module: This library looks like a real game-changer for SQL Server automation.

• Flyway, dbForge, Jenkins: For database migrations and CI/CD pipelines. I’ll probably be knee-deep in these tools before long.

  
  

Tools & Scripts

Here’s a snapshot of what I’m starting to rely on:

• PowerShell

  • Core language for automation

  • Modules like Invoke-SqlAssessment and dbatools

• Flyway & dbForge SQL Tools

  • Managing schema changes and version control

• Jenkins

  • Automating database build and deployment pipelines

• Ola Hallengren’s Maintenance Solution

  • Trusted option for index and backup maintenance

• sp_whoisactive

  • Real-time monitoring of database activity

• Glenn Berry’s Diagnostic Scripts

  • Establishing and monitoring baseline performance

• First Responder Kit (Brent Ozar)

  • Emergency tools for diagnosing performance problems

• Partitioning and Data Compression

  • Structuring data for scale and squeezing more performance out of less space

    
    

This probably won’t be the final version of my setup. I expect it to change as I run into new challenges or find better ways of working. But for now, it feels like a solid base to build on.

The bigger goal remains the same: keep the databases healthy, secure, and ready to grow without letting day-to-day fires distract from long-term improvements.