RJ's SQL Server and MySQL Notes

Notes on SQL Server and MySQL

  • Advertisements

Archive for the ‘Uncategorized’ Category

Data Science Calling

Posted by rjssqlservernotes on September 17, 2014

Data science provides an exciting opportunity for database professionals to proliferate into a world of wonder. The field of data science represents the merging of distinct skill sets providing an opportunity for some self-education.


So, what’s a DBA to do? As IT professionals we have innate abilities to quickly adapt to the challenges of a new technical environment encompassing specific business domains; therefore, the ability to gain some level of “Domain Expertise” is assumed as is the “Computer Science” expertise. Most CS degree programs require significant mathematics expertise; however, not generally a serious focus on statistics; therefore, this sub-domain of the data science world may be a challenge as may be developing competency with machine learning algorithms.
However, I don’t believe that either machine learning or statistics would pose a challenge to most database professionals. Yes, extracurricular study will be required, but if your career, like mine, has required developing expertise in multiple programming languages, computing environments, tools, etc. then spending a few months learning isn’t novel. In fact, this self-training should be embraced as a means of enhancing our professional abilities; i.e. marketability.


Posted in Life-long learning, SQL Server and C#.Net Notes, training, Uncategorized | Leave a Comment »

SSIS paper

Posted by rjssqlservernotes on October 3, 2011

Posted a new paper on using SSIS for ETL.

Posted in SQL Server and C#.Net Notes, Uncategorized | Tagged: , , , , , , | Comments Off on SSIS paper

Another Silver Bullet

Posted by rjssqlservernotes on January 25, 2011

Cloud computing is the most recent incarnation of the magic Silver Bullet that will provide for “World-class Service Delivery”, reduction in system costs, fast and easy custom development and deployment, and two of my personal favorites: “Pre-built, pre-integrated apps for cloud computing technology” and “Empowered business users”.  Every time a new software technology emerges the same buzz phrases make the circuit through the management magazines, CIO newsletters, IT conferences, etc. As technologists are we expected to ignore the obvious or play along?

There is no Silver Bullet.  Fred Brooks’ words 25 years ago are as true today, “There is no single development, in either technology or management technique, which by itself promises even an order-of-magnitude improvement within a decade in productivity, in reliability, in simplicity.

Figure 1 Cloud Computing is No Silver Bullet

How many Silver Bullets have you survived?  Tools were to save us from ourselves by allowing us to build software using drag-and-drop Structured or Object-Oriented programming techniques, Computer Aided Software Engineering, Standards, and my favorite Ada.  Or, there was the belief that we, as software engineers, merely lacked the disciple to reliably produce software – I particularly recall Yourdon’s treatise titled “Decline and Fall of the American Programmer” as well as the movement to license software engineers.  Or, the answer might be Formal Methods such as, CMM, ISO, etc.  Or, better yet, management methods such as TDD, Agile, Extreme, etc.  As demonstrated in Figure 2 all of the Silver Bullets applied over my career have not resulted in significant improvement in software project success.

Figure 2 Software Project Failure Rate

Obviously, the software industry continues to experiment with solutions to the problem of building increasingly complex software; this is not my complaint, instead I would prefer to see IT management be more tempered in their zest for new technology, to allow for the technologists within their organizations to experiment and provide feedback, to filter the hyperbole before mandating the implementation of the new technology.

That being said, I am not suggesting that cloud computing does not provide benefits; however, there are serious issues yet to be addressed such as, security, reliability, ownership, intellectual property rights, and regulatory compliance.  These issues along with others may limit the use of this exciting technology by some industries, but let’s remember what cloud computing brings to the table.  Cloud computing provides an “on-the-fly” mechanism for increasing infrastructure without the investment in hardware, software, or personnel.  As with any new technology the challenge is in finding the balance between the promise and the reality.

Posted in SQL Server and C#.Net Notes, Uncategorized | Tagged: , | Comments Off on Another Silver Bullet

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

The difference between monitoring and diagnosis

Posted by rjssqlservernotes on November 21, 2010

Recently, I was running a PerfMon Data Collector on a Production instance that included the “typical” set of coarse-grain counters.  One of the counters Average Physical Disk Queue Length showed that there was a potential problem with IO to the disk subsystem.  The lead DBA responded that Average Physical Disk Queue Length was a “meaningless” statistic when measured in a SAN environment.  I attempted to reason with the lead who sent me an article downloaded from a WWW site as well as a technobabble email from a system engineer who also argued that a SAN-based SQL Server environment renders the Average Physical Disk Queue Length “meaningless”.  Both argued, unknowingly, that the metric did not provide “specific” information about disk latency.  I say unknowingly because neither bothered to read their own references which clearly stated that the average Physical Disk Queue Length is a generic measure of IO latency at the OS level that can not provide details about the specific disk or disks causing the issue; however, the metric does provide an early warning that suggests further investigation.

In the end after several email exchanges  the system engineer clarified that the Average Physical Disk Queue Length measures that I had collected were possibly effected by a disk failure in the SAN that he was already aware of.

The point here is that generic monitoring of a production system is best performed with high-level counters so as not to degrade performance; however, when a potential issue is identified then the more detailed counters should be used for further diagnosis.

Posted in SQL Server and C#.Net Notes, Uncategorized | Tagged: , , , | Comments Off on The difference between monitoring and diagnosis

Began Loading Some of the DBA Quick Guides

Posted by rjssqlservernotes on November 1, 2010

Added a page for the DBA Quick Guides, I’ll load them as time allows.  The Quick Guides are not intended to be detailed discussions of the covered topics.

Posted in Uncategorized | Tagged: , , , , , | Comments Off on Began Loading Some of the DBA Quick Guides

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

Began the formatting and loading of some scripts

Posted by rjssqlservernotes on September 29, 2010

I added a new page to the blog that contains the beginnings of the transfer of my collection of SQL scripts.  Not much yet, but should grow every day as I make the time to document, format, and load the scripts.

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

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 »

Posted Some Papers

Posted by rjssqlservernotes on September 25, 2010

The Papers page has the beginnings of my transfer of content from the WWW site.  A representative collection of papers on a variety of topics is posted.

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