RJ's SQL Server and MySQL Notes

Notes on SQL Server and MySQL

  • Advertisements

Posts Tagged ‘SQL Server’

There’s a dark side to everything.

Posted by rjssqlservernotes on February 19, 2013

The Big Data menace continues to grow as reported by Ryan Gallagher of The Guardian: “A multinational security firm has secretly developed software capable of tracking people’s movements and predicting future behaviour by mining data from social networking websites.”  Raytheon’s system named RIOT is fed from individuals’ voluntary posts to social media sites; during a demonstration Raytheon  was able to identify their “example target. With information gathered from social networks, Riot quickly reveals Nick frequently visits Washington Nationals Park, where on one occasion he snapped a photograph of himself posing with a blonde haired woman. We know where Nick’s going, we know what Nick looks like now we want to try to predict where he may be in the future.”

An amazing, if not Orwellian, example of the use of predictive analytics.

From my soapbox:

Everything you share online on any website may be used against you.


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

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:


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 »

Data Replication Paper published on SQLServerCentral.com

Posted by rjssqlservernotes on December 10, 2012

The goal of this paper is to provide a foundation for understanding data replication as well as a discussion of the criteria for selecting an appropriate replication technology.  Read it on SQLServerCentral.

If you don’t have a SQLServerCentral account then read it here.

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

Data Insecurity

Posted by rjssqlservernotes on October 21, 2012

As a DBA and former software engineer one of my recurring nightmares involves the release of sensitive information from a system for which I am responsible.  Every day it seems I read of another incident of data breach; that is, “the intentional or unintentional release of secure information to an untrusted environment.”  The list below represents the data breach activity that has been reported for the past ten days (source:Privacy Rights Clearinghouse):

October 18, 2012 Blount Memorial Hospital
Maryville, Tennessee
A password-protected laptop was stolen from an employee’s home on August 25.  It contained two groups of patient data.  Patient names, dates of birth, responsible party names, patient addresses, physician names, and billing information for 22,000 patients were on the laptop. An additional 5,000 patients had similar information exposed as well as their Social Security numbers and other non-medical information.
October 18, 2012 Southern Environmental Law Center
Charlottesville, Virginia
Sensitive information from Southern Environmental Law Center was placed online. Credit card, medical, and donor information such as addresses, phone numbers, and client files were exposed. The data was accessible via Google search for an unspecified amount of time. Southern Environmental Law Center is warning people not to open emails about the security failure or click on any links in emails that appear to be from Southern Environmental Law Center.
October 15, 2012 District 202
Plainfield, Illinois
People who applied online for a job in District 202 had their information accessed by a hacker. The hacker sent messages to former and current job applicants and informed them that the Plainfield School District 202 website was breached.
October 13, 2012 City of Burlington, Washington
Burlington, Washington
A hacker or hackers managed to transfer $400,000 in city funds to accounts across the country. The cyber attack occurred sometime between Tuesday night and Wednesday morning.  City employees may have also had their direct deposit bank account information compromised.
October 12, 2012 AutoCarry
North Bergen, New Jersey
An office burglary that occurred on October 10 resulted in the exposure of customer information.  Paper documents that contained credit card numbers, addresses, and other personal information were taken.
October 12, 2012 Korn/Ferry International
Los Angeles, California
A cyber breach affected Korn/Ferry databases.  Names, Social Security numbers, driver’s license numbers, government-issued identification numbers, credit card numbers, and health information may have been exposed.  The information may have been available to unauthorized parties for months before the breach was discovered in August of 2012.
October 11, 2012 Centers for Medicare & Medicaid Services (CMS)
Baltimore, Maryland
The CMS experienced 13 breaches between September 23, 2009 and December 31, 2011.  The CMS failed to notify beneficiaries of seven of the breaches in a timely manner.  The HHS’s Office of the Inspector General (OIG) also alleges that the notifications mailed to beneficiaries did not disclose what type of information had been exposed, the date the breach occurred, or how CMS was working to prevent future breaches.
October 10, 2012 Northwest Florida State College
Niceville, Florida
An internal review revealed a hack of Northwest College servers.  One or more hackers accessed at least one folder in the server between May 21, 2012 and September 24, 2012.  Over 3,000 employees, 76,000 Northwest College student records, and 200,000 students eligible for Bright Future scholarships in 2005-06 and 2006-07 were affected.  Bright Future scholarship data included names, Social Security numbers, dates of birth, ethnicity, and genders.  Current and former employees that have used direct deposit anytime since 2002 may have had some information exposed. At least 50 employees had enough information in the folder to be at risk for identity theft.
October 8, 2012 TD Bank
Cherry Hill, New Jersey
Two data backup tapes were lost during shipping in late March 2012.  The tapes included customer names, Social Security numbers, addresses, account numbers, debit card numbers, and credit card numbers.
“Used with the permission of the Privacy Rights Clearinghouse, http://www.privacyrights.org.”

The fear mongering about data breaches most commonly involves “Identity Theft” where a “Hacker” uses the obtained personal information to assume the identity of an unsuspecting individual whose Social Security Number and other information has been “stolen”. Clearly, data breaches make the process of obtaining personal information about any individual easier; however, the fact remains that anyone’s personal information is obtainable WITHOUT access to any restricted data via a variety of public Internet sources.

Notwithstanding the identity theft hyperbole, as a DBA, it is my responsibility to ensure that my employer NEVER makes the Data Breach list. To that end I am a disciple of, and evangelist for, encryption of all sensitive data stored in my databases. I discuss the various encryption options for SQL Server in the paper Data Insecurity: A Perspective on Data Encryption.

Posted in SQL Server and C#.Net Notes | Tagged: , , , , , , , , | Comments Off on Data Insecurity

Contained Databases in SQL Server 2012

Posted by rjssqlservernotes on September 21, 2012

The process of database migration is now easier than ever with the Contained Database feature in SQL Server 2012.  A Contained Database (properly referred to as a Partially Contained Database) has become a fully encapsulated object providing definitive separation of the database and the instance.  The containment of database objects and configuration metadata allows for moving a database in a single step. The image below demonstrates the encapsulation of database specific metadata in the Contained Database:

Contained databases support both Windows and SQL Server authentication by allowing the creation of Windows authenticated logins using the same process as previous versions of SQL Server as well as Contained Logins created within the Contained Database.  The Contained Login is NOT stored in master.sys.logins.  The authentication process for Windows-based logins uses the same process as previous versions of SQL Server while Contained Logins are authenticated against the Contained database first.  If the Contained login is not found in the Contained database, then the authentication is performed at the instance level; however, if the Contained login fails at the Contained database level because of an incorrect password, then the connection is terminated.

As with any new feature there are benefits and risks associated with the use of Contained databases; this missive includes a brief introduction to the benefits leaving the more detailed discussion to another medium.

Watch my Contained Database presentation.

Posted in SQL Server and C#.Net Notes | Tagged: , , , , | Comments Off on Contained Databases in SQL Server 2012

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 »

When brains are needed, brawn won’t help.

Posted by rjssqlservernotes on December 3, 2011

The truth of this Yiddish proverb has been repeatedly revealed to me in both my personal and professional lives.  Professionally, I cannot begin to count the number of client’s with whom I have worked who have tried to solve a SQL Server performance issue with more hardware – the brawn.  In my experience most performance issues will not be resolved with more memory, faster processors or more disk space.  Yes, these approaches may return short-term performance improvements for a slow database server; though, an improperly configured, passively maintained system with data and query design problems will quickly exhaust the benefits of the new hardware. (Before you flame me…Yes, there are databases that outgrow their hardware environments; however, because of the cost of hardware considering upgrades should, in my opinion, be a last resort.)

My first recommendation to a client with instance performance issues is to get a Health Check of the system.  The Health Check not only collects performance metrics for baselining the system it also validates the configuration of the system against known “best practices”.  Again, in my experience the recommendations developed as part of the Health Check, more often than not, result in performance improvements, and yes, sometimes the recommendations do include hardware upgrades, but only after I’ve squeezed every last ‘bit’ of performance out of the existing hardware.

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


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 »

Database Performance Paper Uploaded

Posted by rjssqlservernotes on November 1, 2011

Read it here.

Posted in SQL Server and C#.Net Notes | Tagged: , , , , , | Comments Off on Database Performance Paper Uploaded

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

Posted a paper on the management of database indexes

Posted by rjssqlservernotes on May 25, 2011

Posted a paper discussing the management of database indexes.  Read the paper here.

Posted in SQL Server and C#.Net Notes | Tagged: , , , , , | Comments Off on Posted a paper on the management of database indexes

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 »