RJ's SQL Server and MySQL Notes

Notes on SQL Server and MySQL

Posts Tagged ‘dba’

Big Data: Promise and Menace

Posted by rjssqlservernotes on January 30, 2013

I recently read that big data is “a solution looking for a problem” (Gartner). I’ve also read that many companies are monetizing their Big Data: “An overwhelming majority of companies (73 percent) have already leveraged data to increase revenue. Of those companies that have already increased revenue, 57 percent used data to increase an existing revenue stream.” (Global Survey: Is Big Data Producing Big Returns?) Certainly, the Big Data excitement is building as evidenced by the following chart:

bigdata_mentions_graphic2

As a DBA I see Big Data as a new opportunity. There appears to be real work to be done with Big Data in the medical, pharmacology, and other scientific fields as well as with customer-facing enterprises: new discoveries and services await – that is the promise. Unfortunately, Big Data is also invading our privacy providing data aggregators, data vendors, and the government with alarming details of our lives stripping us of the last remnants of privacy – this is the menace. For example, I can use Big Data predictive analytics to forecast the movement of the equity markets, recommend products you are likely to purchase, or recommend a new favorite movie.  On the other hand as the links below demonstrate Big Data provides the tools for privacy invasion at a scale never before seen:
 
Public Data and the Derivation of Social Security Numbers
Rethinking Privacy in an Era of Big Data
Big data collection collides with privacy concerns, analysts say
Microsoft’s Charney warns of big data privacy, security challenges
Privacy in the Age of Big Data
Big Data Brings Big Privacy Concerns
How “Big Data” Can Predict Your Divorce

Posted in SQL Server and C#.Net Notes | Tagged: , , , | Leave a Comment »

Sordid Sniffing

Posted by rjssqlservernotes on December 7, 2011

While recently conducting a performance analysis for a client I again came across an interesting phenomenon relating to plan caching and the potentially sordid effects of parameter sniffing on performance.

Parameter sniffing allows SQL Server to create an execution plan for reuse by analyzing the parameter values passed during the first execution of a SPROC.  The ‘first execution’, that is the key to this phenomena, results in a cached execution plan based on the statistical distribution of the values of the parameters passed.  Specifically, the selectivity of the parameter values included during the first execution may be dissimilar to the selectivity of parameters passed in subsequent calls resulting in poor performance of the SPROC.  If you’ve determined that a SPROC’s execution plan is being polluted by parameter sniffing, then you have a couple options: use the WITH RECOMPILE option, or assign the parameters passed to the SPROC to local variables within the SPROC thereby blinding the parameter sniffing process.

Discussion of the methods to examine the plans in the cache as well as their performance implications are topics for a paper not this medium so I leave you with this oft repeated pearl of wisdom: “no plan is better than a bad plan”.

Posted in SQL Server and C#.Net Notes | Tagged: , , , , , | Leave a Comment »

Nightmares

Posted by rjssqlservernotes on December 1, 2011

I have two recurring career related nightmares one of which I’ve come to discover is part of the shared consciousness of many DBAs: catastrophic database failure.  Not that we haven’t trained for efficient and timely recovery in preparation for such an event, but that our preparation did not account for Murphy, our nemesis, joining the party with a corrupt backup file.  This nightmare recently became my reality when I received a call from a soon-to-be client that a business-critical database failed and their subsequent attempts to recover from backups also failed.

So, how did I handle the situation?  First, I reactivated my profile on Dice.com because as Steve Larrison suggests this may be the “only way to truly handle that situation.”  Then, after a quick visit to the Espresso machine I called the client and got to work.

I wish I could say that through astounding technical prowess and my magical powers I was able to restore all of their data; alas, my story does not have a happy ending.  Following four and one-half hours of effort all I had to say to the client was that the data in the backup file was inaccessible and the best I could do for them was to recover portions of their data from the corrupted database file.  To my amazement and relief the client was ecstatic.  End result was a new happy client for whom we implemented a robust backup strategy that included redundant backup file storage and weekly testing.

The moral of this tale-of-whoa is that a backup strategy is only as good as the last test of that strategy.

Posted in SQL Server and C#.Net Notes | Tagged: , , , , | Leave a Comment »

Script Manager Tool

Posted by rjssqlservernotes on December 12, 2010

Posted a paper discussing and demonstrating a script repository – read the paper here.

Posted in Uncategorized | Tagged: , , | Comments Off on Script Manager Tool

So you want to be a database administrator – Part III

Posted by rjssqlservernotes on October 19, 2010

Posted in Uncategorized | Tagged: , , , , | Comments Off on So you want to be a database administrator – Part III

So you want to be a database administrator – Part II

Posted by rjssqlservernotes on September 27, 2010

Building software either as a programmer or as a DBA has, in my opinion, a common characteristic that drives most of us in the field; a passion for building things.  The creative aspects of the software industry are what have been my personal motivation for nearly two decades; the excitement during the design phase, the energy of the implementation phase, and the feeling of accomplishment in the rollout have fed my passion.  The variety of interests that I have enjoyed in the database industry have allowed me to experience the roles of database architect, production DBA, and performance optimization DBA; therefore, it is these specific roles and their responsibilities that I will address.  Again, my intent is to inspire and motivate readers who have an interest in becoming a DBA, but who may not have a thorough understanding of what the career choice entails.

Every industry has an entry-level position, as a DBA that position is the production DBA.  My nomenclature may be misleading, by production DBA I am not suggesting that an inexperienced DBA would be assigned responsibility for production systems instead I define a production DBA as that person who monitors, maintains, installs, backs up, restores, and applies patches to the database in the case of the entry-level DBA these tasks would be accomplished in the development and testing departments.  It is in this position with non-critical databases that a junior DBA will pay their dues.  The tasks to be performed reflect those that are performed on the production systems upon which the organization depends; however, a “learning moment” for the DBA in the development environment will only result in the exasperated yelps of the development staff not bring crisis to the business.

The responsibilities of a production DBA may be summarized as follows:

  • Monitoring the database includes checking the error and event logs, using PerfMon, Profiler, and other tools to ensure that the system is operating as it should, and responding to issues reported by users.  The primary goal of database monitoring is to ensure the availability and appropriate use of the database as such the DBA is proactive in monitoring the system to avoid any disruption of service.  SQL Server and the Windows operating system provide the DBA with sever tools for monitoring the database and the underlying operating system, in addition, there are numerous third-party applications available that provide an integrated view of the health of the systems.
  • Where monitoring the database is a real-time activity ensuring the availability and proper operation of the database, maintenance is the collection of tasks performed by the DBA to ensure that the database remains efficiently organized.  The maintenance tasks include the reorganization of indexes, data consistency checks, database log and filegroup management, capacity planning, and; arguably, user administration.
  • The production DBA is solely responsible for the availability and proper operation of the database, this responsibility has a direct and meaningful impact on the business wherein the database is often the heart of operations.  Therefore, a production DBA must absolutely control the direct manipulation of the data, changes to the underlying operating system, changes to the database system, or any changes to any other aspect of the database that may have a potential for causing service disruption.  Unfortunately, there are circumstances that require change to the database system such as Service Pack releases and upgrades of the database system or the operating system.  In these cases the production DBA must ensure that planning for any proposed change include significant testing prior to any changes being applied to the production systems.
  • Database backup and restore operations, in the case of catastrophic system failure, are the lifeline of the business.  The development and execution of a backup and restore strategy are, in my opinion, the most important responsibilities of a production DBA.  A database backup plan should be integrated with the operating system backup plan; that is, a full backup of the operating system should be incorporated into the database backup plan.  The database backup plan development is affected by many business variables including business continuity planning and cost; however, minimally, all data in the database should be backed-up within a business-defined interval with intermediate differential backups executed within the full-backup schedule.  Performing the backups is merely the beginning, a written restore plan is also required and must be practiced to ensure the consistency of the backup media, the process, and its execution in a simulated emergency.

Again, I do not intend for this list to be more than an introduction for those individuals who may be interested in a career as a production DBA.  Each topic discussed can and has, in many cases, been discussed in detail in other readily available publications including papers available on my Papers page.

Posted in Uncategorized | Tagged: , , , , | Leave a Comment »

So you want to be a database administrator – Part I

Posted by rjssqlservernotes on September 25, 2010

I don’t want to address what makes a “good” DBA because any answer may be considered subjective instead I’d like to discuss what to expect as a DBA.  Specifically, I’d like to discuss the responsibilities you, as a DBA, will be expected to fulfill and the knowledge required by those responsibilities.  This discussion should not be considered and is not intended to be the source of all knowledge related to DBA responsibilities, my exposure in the field is limited to my own specialization; however, I hope that this discussion may provide a starting point for someone interested in launching their career as a DBA.

A database administrator is ultimately responsible for providing efficient access to the data in the database as well as the security and integrity of the data.  In support of these overarching responsibilities the DBA role within an organization may be assigned the following specific responsibilities:

  • Database planning, design and implementation
  • Database system monitoring, installation, backup and restore, maintenance, and upgrade
  • Database security
  • Database performance
  • Data warehouse support

In smaller organizations these responsibilities may be assigned to a single individual; however, in other organizations the responsibilities are assigned to specific roles filled by separate individuals within the database support department.  Although all database administrators may share a common set of knowledge, specialization is necessary to effectively accomplish the responsibilities of the specific roles within a database support department.

Posted in SQL Server and C#.Net Notes | Tagged: , , | Leave a Comment »