RJ's SQL Server and MySQL Notes

Notes on SQL Server and MySQL

A Fool and His Data Soon Part

Posted by rjssqlservernotes on April 18, 2014

Data breaches are making news every day. Retailers Michaels, Target, and Neiman Marcus recently made the front-page news as did Marriott, Holiday Inn, Sheraton, and other hotels due to large-scale data breaches that released sensitive customer data. In some cases the data was stolen at the point-of-sale terminal prior to storage; nonetheless, these incidents should be a wake-up call for all of us responsible for securing our company’s data.

The Open Security Foundation’s graphic below suggests that 34% of data theft is the result of an insider while 58% are external attacks. I have long argued that database encryption can mitigate the damage caused by a data breach; yes, the attackers have your data, but little good it will do them without the encryption keys. For those who suggest that a data thief could mount an unrestricted brute-force attack against the encryption keep in mind that it took 5 years and 2,700 distributed computers to crack a 64-bit symmetric encryption key (how long would it take for a 128 or 256-bit key?).

OpenSecurityFoundationAttackVectors

 Source: Open Security Foundation

I’ve written in the past about encryption, in general, and Transparent Database Encryption with SQL Server, specifically, in support of my mantra regarding data security.   The performance and implementation costs of using TDE are negligible providing excellent protection against loss while the data is at-rest. Another benefit of TDE is that the backup files for the database are also protected eliminating another attack vector.

Are there drawbacks?  Yes, using any encryption on the database will increase the backup file size and empirical data has demonstrated an average 10% performance penalty when using TDE.

Backup files are notoriously susceptible to theft providing easy access to the data with a simple restore command; however, SQL Server 2014 allows us to encrypt the backup even if we chose, foolishly, not to encrypt the database itself by including a simple with encryption to our backup script.

Along with the new Server Roles for DBAs and database backup encryption now available in SQL Server 2014 allows us to augment our protection against insider attacks.

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

The 1% Solution

Posted by rjssqlservernotes on March 19, 2014

The new SQL Server 2014 In-Memory OLTP feature is intended to provide a solution to a very specific problem, it is not intended to be a generic performance enhancement for general database environments (this is not your daddy’s PINTABLE command). The specific “targeted scenarios” for the use of In-Memory OLTP were:

  • High-End OLTP workloads with extreme high write rate, like ticker data in stock trading, session state for very large websites or other applications with high change rates to data causing synchronization issues on block/page level
  • Applications where the business logic is realized as stored procedures running on SQL Server. We usually look at Web applications using such type of architecture.
Source: Juergen Thomas, Microsoft

The In-Memory database engine is a distinct component that runs in parallel with the traditional database engine and supports the execution of precompiled stored procedures. Precompiled stored procedures have many advantages with which come limitations:

No Cursors
No multi-row inserts
No CTEs
No sub-queries
Can only access In-Memory OLTP tables

In-Memory OLTP is available for commercial use only in Enterprise Edition and requires servers configured with large amounts of memory (the entire In-Memory configured table and its projected growth must fit into available memory along with everything else that’s already using memory) and significant CPU horsepower.

The new SQL Server 2014 In-Memory OLTP feature is exciting for a PTO DBA to ponder; however, implementation will (and should) be limited to very intensive OLTP environments with big budgets – the SQL Server elite.

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

Size Does Matter

Posted by rjssqlservernotes on February 12, 2014

SQL Server 2014 with BPE (Buffer Pool Extension) allows, as its name implies, for the expansion of the working set onto SSDs essentially creating an enlarged buffer. The Buffer Manager will treat the configured SSDs as nonvolatile RAM (NvRAM) “significantly increasing I/O performance”.

Configuring the server to utilize the SSDs as BPE is accomplished by executing an ALTER SERVER command:

alter server configuration

set buffer pool extension

on (filename = ‘<your SSD drive letter>:<filename>.bpe’, size = 32GB)

The maximum size of the BPE is limited to [32 * (amount of primary memory)]; however, Microsoft recommends a ratio of 1:16 or less.

Verify the configuration with:

select * from sys.dm_os_buffer_pool_extension_configuration

In the past, I have recommended using solid state drives (SSD) when adding mechanical drives is not an option yet a client wants to increase read performance. Keep in mind that SSDs are not designed for write-intensive workloads and are classified by their Write Endurance Rating indicating the volume of writes before “failure”; therefore, careful consideration must be given to selecting the data to store on the SSD. Restructuring the data to segregate the read-only data for placement on the SSDs is trivial often significantly improving read performance; however, be forewarned this solution is not without its perils.

The SQL Server 2014 BPE feature is one of the most exciting new developments for performance optimization specialists allowing us to make our buffer bigger. This is a good solution when the server’ primary memory configuration is already maximized or there are budget constraints limiting the expansion of the server’s primary memory.

Remember, there are issues associated with using SSDs with a database whether as secondary or primary memory.

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

Web-Based Training Courses

Posted by rjssqlservernotes on December 31, 2013

New page added to the blog containing links to samples of the web-based courses I’ve produced to help anyone interested in retraining for entry into the web development field.

The intent of the courses is to provide anyone with the motivation to retrain themselves the tools to do so.  The courses are short; approximately 7 hours each, with ample hands-on demonstrations and, in some cases, exercises.

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

MySQL

Posted by rjssqlservernotes on December 5, 2013

As a SQL Server DBA I have been reserved in my enthusiasm for MySQL thinking the technology immature and incompatible of competing in an environment of terabyte-size databases; notwithstanding my reservations I have watched MySQL mature over the past decade into a system with significant functionality and performance characteristics.  As a start-up the decision to implement a database solution using MySQL may be dictated by budgetary constraints; however, every business would do well to investigate the use of MySQL as an alternative to Microsoft’s ubiquitous SQL Server database system.

In response to client demand I have performed MySQL migrations to SQL Server, and; interestingly, a migration in the reverse direction which led me to appreciate the power and simplicity of the MySQL system. Although the tools differ, as a DBA the administration and development of databases are, in principle, similar in both environments providing a easy path for cross-training that I encourage other DBAs to undertake.

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

Wake Up Call

Posted by rjssqlservernotes on November 9, 2013

A couple years ago I authored a paper entitled Entrenched Mediocrity suggesting that the IT workers can easily be divided into three categories: the motivated-doer, the do-what-I’m-told, and the do-nothing.  It has been my experience in 25 years in the IT industry that the vast majority of IT workers fall into the second category performing at a level that protects them from admonishment from management but never contributing new ideas or energy to the company.  Furthermore, I believe that 10%-20% of the industry’s workforce are useless or worse.

Yahoo, since Marissa Mayer, has decided to purge their ranks using a system of ranking employees similar to the categories defined in my paper albeit with somewhat more politically correct nomenclature:

hires640Read the source article.

I suggest that this form of employee evaluation should be the standard providing incentive for every employee to constantly improve themselves, to commit to self-education, and to maintain a high level of motivation.  Is the system perfect? No.  But, it works to cull the dead-weight from the ranks and allow those employees that can to produce.

The result of Yahoo’s program to re-energize innovation has been predictable: whining.

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

SQL Server 2014 Security Against the Insider

Posted by rjssqlservernotes on September 15, 2013

As a DBA having followed the Snowden saga detailing his ability as an administrator to access data to which he was not authorized (no comment on the underlying social implications of his actions) I applaud Microsoft’s decision to augment SQL Server’s Server Roles to include the ability to limit DBA access under the “principle of least privilege”.  These new Server Roles:

  • CONNECT ANY DATABASE
  • IMPERSONATE ANY LOGIN
  • SELECT ALL USER SECURABLES

allow DBA permissions to be limited to those necessary to perform their duties while protecting the data.  Will this solve the data breach “insider threat”? Probably not, but it is a step in the right direction.

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

Californication

Posted by rjssqlservernotes on July 4, 2013

It is often said that California leads the nation, even the world, with technological and social innovation. However arguable this distinction, California’s push to require the adoption of data encryption is laudable. According to InfoWorld‘s Ted Samson Kamala Harris, California’s Attorney General, has become an advocate of data encryption “threatening greater scrutiny of companies that suffer data breaches but don’t use encryption, Harris recommended that the California Legislature should consider enacting a law requiring organizations to use encryption to protect personal information.”

I applaud the attitude of holding companies responsible for securing the personal identifying information they store often preaching from my soapbox that data encryption should be required to minimize the impact of data breaches. Encryption and; data security, in general, should not be construed as a barrier to performance or functionality as discussed in Data Insecurity: A Perspective on Encryption (local) rather as a responsibility. A responsibility that, hopefully, companies will now begin to recognize and act upon.

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

Getting Hard

Posted by rjssqlservernotes on June 11, 2013

It surprises me to read the conclusion of a recent study characterizing most data breaches are easily avoidable; the targets were most often not “pre-identified for attack; 79 percent of victims were targets of opportunity, and 96 percent of attacks were not highly difficult.”  I’ve ranted from my soapbox on many occasions regarding the need for DBAs to be proactive about data security to avoid becoming the target of opportunity for some hacker. As a professional DBA implementing data encryption represents the final line of defense in data security (Data Insecurity A Perspective on Data Encryption) on a server that has been properly secured.

Taking simple steps to harden SQL Server would thwart most opportunistic attacks – basic steps include:

  • Disable the SA account. There is no reason to have an active SA account on any SQL Server.
  • Remove Built-in Administrators group login.
  • Perform monthly security audits of server logins and database users. Create a SSA job to run the script and send you an email with the results. Limiting the number of accounts with elevated privileges can be easily accomplished with regular reviews.
  • Change the default TCP/IP Port.
  • Turn off SQL Server Browser.

There are many excellent references available discussing server hardening via a simple Internet search to which the reader is referred.

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

Data Encryption paper published on SQLServerCentral

Posted by rjssqlservernotes on March 7, 2013

The focus of this paper is on the protection of PII data stored within a database using encryption technologies.  Read it on SQLServerCentral.

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

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

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:

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 »

Education: A Lifelong Path

Posted by rjssqlservernotes on January 10, 2013

It is; and, has been, my belief that we are individually responsible for maintaining our capabilities in an ever-changing employment landscape in order to avoid obsolescence (i.e. unemployment). Continuous training is much more relevant for technical professionals where rapid technological evolution is the norm lest we find ourselves pigeon-holed into a dying niche.  The tools for continuous training can be found in reading a well-written book, participating in a web-based course with a live instructor, attending an in-person instructor-led class, or watching a well-constructed computer-based training module (Supporting Life-Long Learning with Constructivist Web-based Instruction).  The key principle in any method of delivery is that the participant is learning, expanding their understanding, and maintaining their relevancy in an ever-changing world.  Moreover, training is an investment, an investment with an enviable return-on-investment (ROI) as evidenced by research findings: “Companies in the top quarter in training expenditure per employee per year ($1,500 or more) average 24% higher profit margins than companies that spend less per year.”(HR Magazine)

So, what does all this mean for a SQL Server professional?  Recently, I was asked to rate my SQL Server abilities there was an awkward silence when I said “7″, maybe “8″ on a good day.  When my counterpart described himself as a “10″ I had to explain my response by saying that I believe the SQL Server domain is so vast that I did not feel that I could judge myself an expert in all aspects of the system.  I think this may be true for many technology professionals (obviously, my counterpart is the exception) which provides even those of us who work in current technologies with opportunities to learn.

Posted in Life-long learning, training | 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

 
Follow

Get every new post delivered to your Inbox.