This month’s TSQL Tuesday event is being hosted by Paul Randal (Blog|Twitter), and for November is a week early so that it doesn't clash with PASS. For this month, Paul picked the topic "why are DBA skills necessary?" which is quite an interesting question and one that I am sure will generate a wide variety of responses that I look forward to reading. To keep from having an impact on my own response I intentionally avoided Twitter and my blog reader today so that I could respond to this topic without having any other responses affect the outcome.
My last two jobs have been with companies that didn’t have a skilled SQL Server DBA on staff prior to my arrival. In both cases, I inherited an environment that had problems on many fronts. Some systems were backed up using a third party tool like ComVault or Netbackup, others went to local disk using Maintenance Plans, some weren’t backed up at all. The environments were both fairly large, and had a lot of SQL Server bloat since every application got its own SQL installation, often times on the same server as the application. Local Administrator rights were granted to the servers to lists of users. In some cases vendors that had support contracts for the software products had shared accounts that were Local Administrators and could VPN into the environment to fix problems, perform upgrades, or god only knows what else. In both of these places, system outages, performance problems, and supportability issues lead to the creation of a new position for a true on staff SQL Server DBA.
Why are DBA skills necessary? A number of thoughts come to mind when i think about this question. A few of those are to ensure protection of critical company data, to provide solutions to complex business problems that relate to the data inside of their environments, and to have authority in your environment when it relates to data.
Protection of Critical Company Data
There are plenty of shops out there that operate without an on staff DBA and many of them have competent server administrators that are backing up their company databases using third party tools and protect the data from loss. In fact it has been my experience that some shops would have been better off if they didn’t have their onsite DBA and simply relied on their server administrators to protect their data. At least then they might have had a weekly or nightly backup of the database in whatever enterprise wide backup product they relied on. While it is certainly true that DBA’s should have the skills to backup the databases in their environment, my own experiences, sadly enough, have been that many DBA’s don’t understand the basics of the SQL Server recovery models, leading to backups that don’t support the business SLA, OLA, RTO, or RPO’s.
However, protection of critical data doesn’t just involve taking a backup, as Paul Randal says, “You don’t have a valid backup until you have tested restoring.” Beyond validating your backups, there is much more to protecting critical company data. Recently I had a vendor deliver two Desktop computers to our data center that were supposed to act as the servers for their application. These were older single processor Pentium systems with 1GB RAM and a single SATA drive in them and both were supposed to have an instance of SQL Server on them. These were for an additional test environment, but the testing is a critical part of a long term project that is ongoing at our hospital and to lose time because of a crash of either of these machines could prove detrimental to the project. The lack of any redundancy here was a huge risk and after much debate, this was implemented on VM’s in our data center. I wish I could say that this kind of thing was isolated or rare, but all over there are SQL Express and Desktop/Personal editions running on a workstation under someone’s desk that holds critical data and is just a misplaced foot or power spike away from total loss, and don’t get me started on all the business critical Excel workbooks, and Access databases that exist inside of most business environments.
It is the job of the DBA to find this business critical data and protect it from loss. If it is held in Excel or Access, it can be architected to use a backend SQL Database to secure and protect the data behind standardized backup processes. Using tools like the Microsoft Assessment and Planning Toolkit with the server administrators a DBA can scan the entire environment to identify all of the SQL Server instances that exist and then begin to ensure that the databases are hosted on appropriate hardware with appropriate backups and security implemented.
Providing Solutions to Complex Business Problems
Any business that has data, eventually finds a need to use that data in ways that the original application and or database design just doesn’t support. One of the most common of these is reporting, which can cause of performance issues in OLTP systems due to poorly written or complex reporting queries leading to blocking of data changing operations. When this happens, it is the DBA that is called upon to troubleshoot the problems. There are a number of different possible solutions to this kind of problem, for example a common one I’ve seen is to take a full backup of the production database and restoring to another server nightly and only running reports against this restored copy. While this kind of solution might work for smaller databases, as database size increases the time take a full backup and then subsequently restore it to another server can take longer than feasible.
One of the key skills of a DBA is gaining an understanding of the business that they support so that they can provide solutions to support those business needs. For example, one of the systems I inherited at my current job uses a SQL database that is just under 250GB in size. Some quick analysis of this database would show that 248GB of the data stored in this database exists in a single table in a image data typed column that is never used for reporting. For a system like this, using replication to synchronize data to a reporting database makes much more sense than using backup/restore or other options like database mirroring to create a reporting database.
Have Authority When it Relates to Data
This is something that only became apparent to me when I started doing consulting work on the side, but actually having good DBA skills and being able to make the decisions that solve problems builds a level of authority with the people you work for and work with (well most of the time, there are those crappy environments where this just isn’t the case). Having a strong set of DBA skills allows you to intelligently discuss problems with end users, as well as explain the benefits and ROI associated with changes to how things are designed, or configured. In my current job I had to gain the trust of a majority of the IT staff as well as the business end users after accepting the job. The problems in the past had not been addressed timely or correctly, and many of the groups had created their own workarounds and methods of managing SQL Server and were very cautious about letting someone new come in and change anything. It only took resolving two or three major problems to start bringing the sentiment around that changes were necessary to properly stabilize the environment.