RJ's SQL Server and MySQL Notes

Notes on SQL Server and MySQL

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.

Advertisements

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:

ContainedDB
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

Woz weighs in on the Cloud.

Posted by rjssqlservernotes on August 7, 2012

Apparently my evangelism against the Cloud is taking root as evidenced by Steve Wozniak’s recent comments.

Woz says:

“I really worry about everything going to the cloud,” he said. “I think it’s going to be horrendous. I think there are going to be a lot of horrible problems in the next five years.”

He added: “With the cloud, you don’t own anything. You already signed it away” through the legalistic terms of service with a cloud provider that computer users must agree to.

“I want to feel that I own things,” Wozniak said. “A lot of people feel, ‘Oh, everything is really on my computer,’ but I say the more we transfer everything onto the web, onto the cloud, the less we’re going to have control over it.”

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

SQLSaturday #158

Posted by rjssqlservernotes on August 5, 2012

A large crowd and awesome presentations made this free event the place to be on Saturday.  A personal thank you to all who attended my presentation.  See you at the next event.

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

SQL Server Saturday #158 NYC on August 4th

Posted by rjssqlservernotes on July 18, 2012

I am proud to announce that I will be presenting at SQLSaturday on August 4th at Microsoft New York Metro Office, 1290 Avenue of the Americas, Sixth Floor, New York, NY 10104.

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server.  More information is available at the SQLSaturday #158 event page.

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

Posted a paper discussing SQL Server Replication

Posted by rjssqlservernotes on June 18, 2012

Read the paper here.

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

SQL Server Saturday #121 Presentation

Posted by rjssqlservernotes on June 12, 2012

A presentation discussing an easy approach for the creation of a performance baseline for a SQL Server instance that I presented at the SQL Saturday event in Philadelphia.

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

Seeing the light

Posted by rjssqlservernotes on March 27, 2012

After nearly two decades as an application engineer focused on the Microsoft stack I have come to recognize the freedom provided by open source software.  My domain of expertise lies in the business, data-access layers, and database development which, until recently, for me meant the .Net framework and SQL Server.  My professional life changed after I discovered PHP and MySQL during a project.

Open source alternatives can provide robust solutions for most clients without licensing fees; this was a eureka moment for me.  In reflecting on the many projects to which I had contributed over the years I concluded that a very small number of them could not have been realized with open source solutions.  I have not abandoned the Microsoft technologies instead I have augmented my ability to provide solutions to clients; however, I find great pleasure in demonstrating solutions that are based solely on open source technology.

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

Cloud Computing Redux

Posted by rjssqlservernotes on March 20, 2012

Beyond the security, ownership, and other issues surrounding cloud computing comes this missive:

The Hidden Risk of a Meltdown in the Cloud

It has been my opinion that cloud computing has its benefits and weaknesses (see post below); however, those benefits do not extend to data storage or for systems without redundant non-cloud-based backup.

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

IO, IO it’s off to work we go

Posted by rjssqlservernotes on February 6, 2012

When we focus on IO performance for SQL Server it is vitally important to understand the architecture of the disk subsystem. Typically, a DBA will focus on Disk Queue Length as the primary measure of IO performance; however, in a Storage Area Network (SAN) environment the Disk Queue Length metric may not provide reliable results. In a SAN environment a “disk” as recognized by SQL Server, may not be composed of a single physical spindle (physical disk); therefore, the Disk Queue Length metric must be considered in relation to the actual number of spindles. For example, a “Logical Disk” labeled “M:\” may actually be composed of four (4) spindles meaning that a Disk Queue Length value of twelve (12) (which is, in my opinion, the threshold for identifying disk IO issues) must be normalized to the number of spindles because the metric represents the total Disk Queue Length:

Disk Queue Length / Spindles = Physical Disk Queue Length.

In our example resulting in a value of:

12 / 4 = 3.

A Disk Queue Length of three (3) does not represent a negative performance metric.

With the discussion above in mind we address SAN-based SQL Server IO performance using a different metric which may be more reliable – Disk Latency / Data Transfer Time. Using the Disk Latency metric permits the DBA to abstract the underlying disk subsystem allowing direct measurement of the performance of the disks as reported to the operating system. In my opinion, the Disk Latency for a well-configured disk subsystem should be less than four milliseconds (4 ms).

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

SSISy Scripting

Posted by rjssqlservernotes on February 3, 2012

I’ve done a fair bit of work implementing custom ETL solutions with SSIS concluding that the tool provides excellent “out-of-the-box” support for ETL as well as almost limitlessly extensibility with the Script component.  If you have a need to bring data from there to here with transformations along the way then SSIS is the solution; full transactional support, easy to use, fast, and extensible.

I use C# to develop my Script components.  As a simple example, during development I sometimes use a dialog to display running values within the package.

Notice that the plumbing has been generated by the system allowing us to focus on functionality.  All of the variables are addressable within the script with read / write access defined in the Script Component’s properties dialog in BIDS.  Notice the call to display the MessageBox (look familiar?):

public void Main()
{
MessageBox.Show(Dts.Variables[“RowCount”].Value.ToString() + “/” +                        
Dts.Variables[“UnmatchedRowCount”].Value.ToString(), “RowCount”, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

Dts.TaskResult = (int)ScriptResults.Success;
}

There isn’t much that can’t be done with a Script Component; on occasion I’ll simplify a Data Flow by encapsulating multiple data transforms or cleansing operations in a script.  Even if you are a magician with the built-in tasks and components in SSIS I think you’ll find that using the Script Component will simplify and extend the capabilities of your package.

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

Dakety DAC – SQL Won’t Talk Back

Posted by rjssqlservernotes on February 1, 2012

(With sincere respect and admiration of the Coasters.)

Lunchtime and you’re heading out the door
Alerts ring your server is no more
CPU is pegged transactions are stacking
Operations says your response better not be lacking
Dakety DAC (SQL won’t talk back)

An emergency is not the time to plan.  I was recently responding to a client call regarding a unresponsive server when I realized that I had never tested the Dedicated Administrator Connection (DAC) on the server.  I bowed my head, said a quick prayer to the SQL gods, and then tried to connect to the server.  Fortunately, the DAC was enabled and I was able to resolve the issue fairly simply and quickly, but the situation made me think about what I had not planned for.

Remember that the default setting for Remote Administrator Connections (RAC) is DISABLED; not something you want to discover when the server is seized-up because then your only recourse may be to bounce the server.  Changing the default setting of RAC is as simple as running the following script:

SP_CONFIGURE ‘remote admin connections’, ‘1’
GO
RECONFIGURE
GO

Now you’re prepared for any emergency and we can finish our song.

Don’t you give me no dirty looks
Your DBA’s hip; he knows what cooks
Tell my girlfriend she’s outside
I got time to take that ride

Dakety DAC (SQL did talk back)

Posted in SQL Server and C#.Net Notes | 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 »

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 »