Skip navigation

Geek Speak

8 Posts authored by: jdanton

Unlike most application support professionals, or even system administrators, as database professionals, you have the ability to look under the hood of nearly every application that you support. I know in my fifteen plus years of being a DBA, I have seen it all. I’ve seen bad practices, best practices, and worked with vendors who didn’t care that what the were doing was wrong, and others with whom I worked closely to improve the performance of their systems.

 

One of my favorite stories was an environmental monitoring application—I was working for a pharmaceutical company, and this was the first new system I helped implement there. The system was up for a week and performance had slowed to a crawl. After running some traces, I confirmed that there was a query without a where clause that was scanning 50,000 rows several times a minute. Mind you, this was many years ago, when my server had 1 GB of RAM—so this was a very expensive operation. The vendor was open to working together, and I helped them design a where clause, an indexing strategy, and a parameter change to better support the use of the index. We were able to quickly implement a patch and get the system moving quickly.

 

Microsoft takes a lot of grief from DBAs for their production systems like SharePoint and Dynamics, and some interesting design decisions that are made within. I don’t disagree—there are some really bad database designs. However, I’d like to give credit to whomever designed System Center Configuration Manager (SCCM)—this database has a very logical data model (it uses integer keys—what a concept!), and I was able to build a reporting system against it.

 

So what horror stories do you have about vendor databases? Or positives?

Career management is one of my favorite topics to write and or talk about, because I can directly help people. Something I notice as a consultant going into many organizations is that many IT professionals aren’t thinking proactively about their careers, especially those that work in support roles (supporting an underlying business, not directly contributing to revenue like a consulting firm or software development organization). One key thing to think about is how your job role fits into your organization—this is a cold hard ugly fact that took me a while to figure out.

 

Let’s use myself as an example—I was a DBA at a $5B/yr medical device company—that didn’t have tremendous dependencies on data or databases. The company needed someone in my slot—but frankly it did not matter how good they were at there job beyond a point. Any competent admin would have sufficed. I knew there was a pretty low ceiling of how far my salary and personal success could go at that company. So I moved to a very large cable company—they weren’t a technology company per se, but they were large enough organization that high level technologists roles were available—I got onto a cross platform architectural team that was treated really well.

 

I see a lot of tweets from folks that often seem frustrated in their regular jobs—the unemployment rate in database roles is exceedingly low—especially for folks like you who actively reading and staying on top of technology—don’t be scared to explore the job market, you might be pleasantly surprised.

If you are an Oracle DBA and reading this, I am assuming all of your instances run on *nix and you are a shell scripting ninja. For my good friends in the SQL Server community, if you haven’t gotten up to speed on PowerShell, you really need to this time. Last week, Microsoft introduced the latest version of Windows Server 2016, and it does not come with a GUI. Not like, click one thing and you get a GUI, more like run through a complex set of steps on each server and you eventually get a graphical interface. Additionally, Microsoft has introduced an extremely minimal server OS called Windows Nano, that will be ideal for high performing workloads that want to minimize OS resources.

 

One other thing to consider is automation and cloud computing—if you live in a Microsoft shop this all done through PowerShell, or maybe DOS (yes, some of us still use DOS for certain tasks).  So my question for you is how are you learning scripting? In a smaller shop the opportunities can be limited—I highly recommend the Scripting Guy’s blog. Also, doing small local operating system tasks via the command line is a great way to get started.

Last week at their Build Developer Conference and the week at Ignite, Microsoft introduced a broad range of new technologies. In recent years, Microsoft has become a more agile and dynamic company. In order for you and your organization to take advantage of this rapid innovation, your organization needs to keep with the change, and quickly adapt to new versions of technology, like Windows 10, or SQL Server 2016 . Or maybe you work with open source software like Hadoop and are missing out on some of the key new projects like Spark or the newer non-map reduce solutions. Or perhaps you are using a version of Oracle that doesn’t support online backups.  It’s not your fault; it’s what management has decided is best.

 

As an IT professional it is important to keep your skills up to date. In my career as a consultant, I have the good fortune to be working with software vendors, frequently on pre-release versions, so it is easy for me to stay up to date on new features. However, in past lives, especially when I worked in the heavily regulated health care industry, it was a real challenge to stay on top of new features and versions. I recently spoke with a colleague there and they are still running eight-year-old operating systems and RDBMSs.

 

So how you manage these challenges in your environment? Do you do rogue side projects (don’t worry we won’t share your name)? Or do you just keep your expert knowledge of old software?  Do you pay for training on your own? Attend a SQL Saturday or Code Camp? What do your team mates do?  Do you have tips to share for everyone on staying current when management thinks “we are fine with old technology”?

Back when I used to be an on-call DBA, I got paged one morning for a database server having high CPU utilization. After I punched the guy who setup that alert, I brought it up in a team meeting—is this something we should even be reporting on, much less alerting on. Queries and other processes in our use CPU cycles, but frequently as a production DBA you are the mercy of some third party applications “interesting” coding decisions causing more CPU cycles than is optimal.

 

Some things in queries that can really hammer CPUs are:

  • Data type conversions
  • Overuse of functions—or using them in a row by row fashion
  • Fragmented indexes or file systems
  • Out of date database statistics
  • Poor use of parallelism

 

Most commercial databases are licensed by the core—so we are talking about money here. Also, with virtualization, we have more options around easily changing CPU configurations, but remember overallocating CPUs on a virtual machine leads to less than optimal performance.  At the same time CPUs are a server’s ultimate limiter on throughput—if your CPUs are pegged you are not going to get any more work done.

 

The other angle to this, is since you are paying for your databases by the CPU, you want to utilize them. So there is a happy medium of adjusting and tuning.

Do you capture CPU usage over time? What have you done to tune queries for CPU use?

Throughout my career I have worked on a number of projects where standards where minimal. You have seen this type of shop—the servers may be named after superheros or rock bands. When the company has ten servers and two employees it’s not that big of a deal. However, when you scale up and suddenly become even a small enterprise, these things can become hugely important.

 

A good example is having the same file system layout for your database servers—without this it becomes hugely challenging to automate the RDBMS installation process. Do you really want to spend your time clicking next 10 times every time you need a new server?

 

One of my current projects has some issues with this—it is a massive virtualization effort, but the inconsistency of the installations, not following industry best practices, and the lack of common standards across the enterprise have led to many challenges in the migration process. Some of these challenges include inconsistent file system names, and even hard coded server names in application code. I did a very similar project at one of my former employers who had outstanding standards and everything went as smoothly as possible. 

 

What standards do you like to enforce? The big ones for me are file system layout (e.g. having data files and transaction/redo logs on the same volume every time, whether it is D:\ and L:\, or /data and /log) and server naming (having clearly defined names makes server location and identification easier). Some other standards I’ve worked with in the past include how to perform disaster recovery for different tiers of apps or which tier of application is eligible for high availability solutions.

In the fifteen plus years that I have in worked in information technology organizations, there has been a lot of change. From Unix to Linux, the evolution of Windows based operating systems, and the move to virtualized servers which led to the advent of cloud computing, it’s been a time of massive change. One of the major changes I’ve seen take place is the arrival of big data solutions.

 

There are couple of reasons why this change happened (in my opinion) at very large scale, relational database licensing is REALLY expensive. Additionally, as much as IT managers like to proclaim that storage is cheap—enterprise class storage is still very expensive. Most of the companies that initiated the movement to systems such as Hadoop and its ecosystem, were startups where capital was low, and programming talent was abundant, so have to do additional work to make a system work better was a non-issue.

 

So like any of the other sea changes that you have seen in industry, you will have to adjust your skills and career focus to align to these new technologies. If you are working on a Linux platform, your skills will likely transfer a little easier than from a Windows platform, but systems are still systems. What are you doing to match your skillset to the changing world of data?

I’m in the middle of tough project right now, a large client is trying to convert a large number of physical SQL Servers to virtual machines. They’ve done most of the right things—the underlying infrastructure is really strong, the storage is more than an adequate, and they aren’t overprovisioning the virtual environment.

 

Where the challenge is coming in is how to convert from physical to virtual. The classical approach, is to build new database VMs, restore backups from the physical to the VM, and ship log files until cutover time. However, in this case there are some application level challenges preventing that approach (mainly heavily customized application tier software). Even so, my preferred method here is to virtualize the system drives, and then restoring the databases using database restore operations.

 

This ensures the consistency of the databases, and rules out any corruption. Traditional P2V products have challenges around handling the rate of change in databases—many people think read only database workloads don’t generate many writes, but remember you are still writing to a cache, and frequently using temp space. What challenges have you seen in converting from virtual to physical?

Filter Blog

By date: By tag: