February 2023

It is a typical Wednesday afternoon. A call with your manager is scheduled around this time to discuss updates. As you enter the conference call, you don’t see anyone from your team, yet you see a familiar face in one of the window screens. “Isn’t that the Database Administrator who tendered his resignation letter a week ago?” you say to yourself.

And it dawned upon you. This call is not your typical meeting but a reception to your new role. You, my friend, are now a DBA.

As an accidental DBA, your first instinct would be to google “How to be a Smarter DBA”. Google will provide you an extensive list of searches: from SQL guides in SQL guides in database management, database security, and database monitoring to MySQL guides on the same topic.

As you open article by article to know more about your role, you must have realized something – “what system am I administering”? Sure, these SQL guides and MySQL guides are helpful, but if your company uses Oracle, you’re off on a bad start.

It is also common to scour the internet for resources on basic SQL and advance SQL topics. Although that is a part of the skillset a DBA needs, therefore, you will still need to understand the core components of how databases work and function.

Moreover, you might find yourself browsing through tutorials. If you are using SQL Server, Documentation on SQL server management studio might be a good starting point. Exploring the IDE will provide a better understanding once you begin Database Monitoring and Database Management.

For non-SQL Server DBAs, there are free IDEs available online, and they’re much more robust and reliable than the recommended IDE of a vendor. In essence, you can choose to settle with SQL server management studio or find an IDE that will be much more familiar to you.

What can you do?

As a DBA, your role is to maintain and optimize the database. You need to understand database management, database monitoring, and database reporting. Sure, backend development and setting up servers and clusters will also be under your responsibility. But as an accidental DBA, the management will not expect you to handle those tasks at once. So, rest assured that you will not need to dive into those topics yet.

The more critical aspect of being a DBA is to know more about performance tuning. In any database server, High-Performance MySQL databases or high-performing database servers will be your end goal. The speed of database read and write is not just the sole measurement for performance; it encompasses database server uptime and disaster recovery.

With an established system, your job is to maintain it. You want the system to work efficiently. Hence, you will focus your energy on focusing on things you can control and handle. Even if you are an accidental DBA, chances are database backups and recovery will be a task you will do.

Understanding backups, for one, is a good one to begin your DBA journey. You need to understand how each type of backup is aligned with your company’s guidelines on disaster recovery. A good tandem with database backups is learning recovery models. Restoring backups is the ideal next step in your journey.

Testing your backups is an absolute must. Untold numbers of DBAs have been left in the cold when it turns out their meticulously executed and stored backups fail to restore. So check – and check again.

Testing backups requires you to save a backup file and restore it in point in time. A secondary server for testing will be required if your database is currently being utilized in production. Use dbcc checkdb after restoring the backup to verify no corruption or issues were encountered.

Next, familiarize yourself with indexes and how they will affect your search queries. Indexes can have an enormous impact on your database’s performance when adequately implemented. During searches, this lessens the fetch time of queries reducing the overall load in the process.

Finally, learn how to optimize SQL queries. Structuring how your write queries is another way to unburden the database resources. The correct use of LEFT and INNER JOIN can impact the speed of returning data, especially when indexes are involved. Always remember not to bloat your data upon return.

There is a lot to talk about when you want to be a DBA. Entry to this role requires learning and experience before you can properly call yourself a bona fide DBA. Plus, you need to always keep in mind database performance. As much as possible, you want to avoid database performance degradation.

But, there is a much easier route rather than overwhelming yourself with these concepts.

Tools can make a difference

Even an accidental DBA, such as yourself, will be depending on database monitoring tools. Nowadays, Professional DBAs use tools for their daily operations. They prefer a working script rather than build it from scratch. Procedures from Brent Ozar or Ola Hallegren gives a much more holistic approach to database monitoring, making monitoring an entire instance much easier.

These solutions are excellent. But won’t you prefer to use a tool that has a pre-built feature of analytics, automated monitoring, and user interface? Yes, there exists such a product; that’s dbWatch Control Center. dbWatch Control Center is a monitoring and management solution that can monitor different databases anytime, anywhere.

dbWatch Control Center has three significant features: Database Monitoring, Database Management, and Database Reporting.

Under the monitoring module, you can monitor hundreds of databases heterogeneously or cross-platform. Whether your organization uses MySQL Server, MS SQL Server, Oracle, or Postgres, Control Center will be able to connect to it, be it in the cloud or on-premises.

In the same module, you will see different dbWatch jobs installed to an instance. Each job is specifically tailored to the monitoring needs of a DBA per each platform. For example, in MS SQL Server, a job called SQL Server Agent status checks if the SQL Server Agent is running or not. You can check more about the different job status by going to our online documentation for Control Center.

Another positive for jobs are they are agentless, meaning dbWatch schedules and triggers those dbWatch jobs. Last, jobs can be categorized as with alert status and without alert status.

Jobs with alert statuses provide alarms and warnings. Once those dbWatch jobs are finished running. They will provide a status color-coded as (see the image above for more details):

By default, they are set by the dbWatch team’s DBAs. If you feel confident in modifying the jobs or your organization has stricter rooms for error, you can set the parameters for each monitoring job. If not, you can leave it as it is.

As a DBA, you want to configure or manage your database instance directly. The management module acts as an IDE to your database server.

The management module also sports a variety of features like history and logs. For example, deadlocks. You can kill sessions using dbWatch Control Center. On top of that, you can see the blocking history of the sessions. These are one of the helpful ways dbWatch’s history and logs are.

You have different options like access control for database security, disk memory management, and backup management. As a matter of fact, managing indexes becomes easier with dbWatch Control Center.

When opening Maintenance Overview, you can see all instances that need your attention, marked with an orange checkmark. With just a click of a button, you can reorganize or rebuild indexes, perform dbcc checks, and remove database fragmentation. Imagine doing that without writing a single line of SQL code.

With this view, you can perform preventative or proactive maintenance. Various jobs in dbWatch will check fragmentation and statistics. This will provide you with the needed indicator that your indexes needs your immediate action.

For example, the image above shows you two different color coding. In the severity column, it shows I need to perform a dbcc check on always1. While SQLKND_16 needs its indexes to be reorganize. Luckily, with dbWatch, you can perform those tasks automatically outside office hours. Furthermore, what’s great about those jobs is that it prevents anyone from executing them carelessly during office hours unless its parameters allow it to be executed during those hours.

Finally, you can generate a database report for your managers and colleagues. With the option to provide a comprehensive one on all registered instances, dbWatch will email it directly to your team or access it in a server environment.

Summary

It might be overwhelming to submerge yourself in the tasks and responsibilities of a DBA. But don’t fret; you are now well equipped with the knowledge to go forth and be the best DBA out there. Always remember a tool does not make you any less of a DBA. A tool like dbWatch Control Center will leverage you into fitting in that role as soon as possible.

The total amount of data in the universe is always growing. A lot of it ends up in our databases. DBAs need database scalability for database instances to cater to both the growth in data volumes and the increased traffic and workload.

This strains our database engines as we push ever-increasing demands on indexes, searches, references, updates, reliability and availability.

Planning Database Scalability

As data and workload grow, the number of server instances in use by businesses grows accordingly, and the importance of stable and reliable IT systems increase, it’s no longer possible for businesses to cope with rigid, non-scalable systems and tools.

Making your databases and applications scalable is not necessarily a simple task. There are two main variations of server scalability to consider: vertical and horizontal scaling.

Working With Vertical Scaling

Vertical scaling, or “scaling up”, involves adding more resources to a server instance. By increasing CPU resources, memory, and storage or network bandwidth, the performance of every individual node can be improved, scaling small servers to handle large databases.

This is the “old school” way of scaling up. You either procure the largest machine you can afford or give your existing server as many resources as you can to fit the workload demand.

Advantages of Vertical Scaling

Vertical scaling is typically easy for application developers as they do not have to do much to support a larger machine. Same for DBAs – usually quite easy to manage. But at some point, you may reach the capacity ceiling for the hardware you have, and you will need to spend an ever-increasing amount of time optimizing the application, SQL, and server instance to wring out every last bit of performance. This will bring you ever-diminishing returns as you get closer and closer to the maximum size you can take a single server. At some point, the cost and effort to scale vertically hit the ceiling, where you no longer can scale up.

Disadvantages of Vertical Scaling

Vertically scaled systems do come with some disadvantages, though. Not only can initial hardware costs be high due to the need for high-end hardware and virtualization, but upgrades can be both expensive and limited – there is, after all, only so much you can add to one machine before it is still outgrown by your database. Normally clustering like Always On or RAC is applied to these large servers to make them reliable and with enough capacity to handle the load.

You may also find yourself quickly ‘locked-in’ to a particular database vendor by following this strategy, and moving away from that vendor later could mean very expensive server upgrades.

In summary, vertical scaling is much easier to establish and administer – as it is just a small number of machines, or even just one. Vertical systems can also offer advantages in terms of stability, reliability and development, and cost savings through being suitable for smaller data centers, and license costs might be lower.

Using Horizontal Scaling

Horizontal Scaling, or “scaling out,” is adding capacity by adding more database server instances. This is done by spreading out databases on more machines to answer the call for increased demand and capacity.

This is now often the preferred way of scaling databases and is often referred to as the “cloud native” way of doing things.

When more capacity is needed in a system, DBAs can add more machines to keep up. In database terms, this means that data must be partitioned across all the machines that make up the cluster, with each individual server holding one part of the database.

Usually, applications need to be written specifically for horizontal scaling. But once built, it becomes very easy to scale the application to meet any workload.

Unless you are certain that your application will fit comfortably on a single server forever, you should write it so it can be horizontally scaled. Even then, consider making it horizontally scalable to make it future-proof and cost-effective to deploy in a cloud.

Horizontally scaled servers can also make use of data replication, whereby one machine holds a primary copy of the entire database while the multiple copies and/or caching are used for read-only load sharing. You often see this in large cloud-based solutions, where data can be distributed across geographic regions, and data centers by use of database read replicas. Consequently, it provides improved response time, better performance, and higher availability.

Advantages of Horizontal Scaling

Horizontal scaling has several key advantages over a vertical approach. The main advantage is that once built, horizontally scalable systems can scale to almost any size and take full advantage of elastic cloud computing – adding more servers to handle peak load. Not only is establishing a scalable system easier, with individual nodes being cheaper than in a vertical set-up but upgrades are also made quicker and more affordable. Maintenance can be easier, too, with faulty instances quickly switched out with minimal disruption to the rest of the system.

A high number of instances adds complexity to a system. It makes monitoring, administering, and troubleshooting more difficult and time-consuming during disaster recovery. When machines are licensed individually, licensing fees would also be higher. Not to mention, the physical space needs to house multiple servers will also bring cost and logistical issues.

A Summary of Scaling

As database requirements continue to grow, your organization will need to adopt a form of scalability to keep up. While horizontal scaling is widely considered to be modern, flexible, and advantageous, it can bring some unwanted challenges that you will eventually need to manage.

Whether you scale vertically or horizontally, you will always need to calculate the optimum hardware and software combination, especially from a cost perspective. You may want to use many small and cheap 4-core blade servers, or one or a few fast and large 24-core or larger servers. Even more important, check the software license cost for the total system based on the number of nodes and cores. Do the math; it is a non-trivial and important cost decision.

Vertical scalability brings complexity, while horizontal scalability brings logistical challenges. In either case, good tools for monitoring, analysis and administration are necessary to monitor performance, reduce the challenges, help you deliver greater productivity, and keep costs in check.

Whichever approach you choose will depend on your business’ requirements. However, regardless of what you opt for now, always keep in mind as your business continues to grow, you will eventually need to keep up with ever-expanding databases.

Month: February 2023

Breaking in as an Accidental Database Administrator