John Magnabosco

Leadership for the DBA

2010-01-23T16:22:10Z

Recently I read a book titled "The 21 Irrefutable Laws of Leadership" by John Maxwell. In this book, Maxwell categories the qualities that creates a great leader. As I read this book I thought about the many managers and leaders that I have encountered in my life, including the ones that we all have read about in the history books or seen on the evening news. I saw how these qualities were exhibited in some leaders and how they resulted in exceptional leadership. I also saw how these qualities were disregarded which resulted in disaster.

The reading of this book would be incomplete without a fair share of introspection. I saw qualities that come to me naturally; but could use some refinement and intentional growth. I saw qualities in which I really struggle to be average. It was a great comfort to read in the book's introduction that even the author recognizes that he is weak in some of these qualities. It is also comforting to read that all of these qualities can be learned.

As I concluded the reading of this book, I thought about the role of a DBA in light of these qualities. There are some DBAs, such as myself, who are in a role or position of leadership within their organization. There are other DBAs whose role is more of a solid technical one without any clear designation of leadership. One of the statements that Maxwell emphasizes in this book is that everyone has the opportunity for leadership even if their organizational position does not call for it. There is a clear difference between the mindset of leadership and positional leadership. The former can exist without the latter. The latter cannot be effective without the former.

I would love to write about each of the 21 laws and how they relate to the DBA role; but it would be very likely that most reading this will not get past the first few before heading on to other blogs. There is only so much time in the day to read, right? So, for the benefit of brevity I will pull out two of these laws and provide some quick thoughts:

The Law of the Lid: "Leadership Ability Determines a Person's Level of Effectiveness"
As a DBA you can possess all of the technical skills that can be acquired. You can take a poorly performing database and optimize the heck out of it. You can author the most effective and efficient T-SQL and speak to its minute intricacies. You can design an awesome database schema that is appropriately normalized and indexed. All of this can make you very effective technically; but if you cannot express your thoughts in a way that the non-technical managers of your organization can understand and be convinced to fund your efforts financially or resources you will not realize your full effectiveness.

A DBA who has put as much effort into refining their leadership skills as they have their technical skills will flow into the category of being an exceptional DBA. Brad McGehee has often spoken and written about "How to become an exceptional DBA". Many of the qualities that he speaks of are a great representation of the balance of technical skills and leadership qualities.

The Law of Connection: "Leaders Touch a Heart Before They Ask for a Hand"
The DBA role is often characterized as a fascist dictator who guards the database realm with a pack of rabid Doberman Pincers. He carries a big stick and uses it quickly leaving Developers cowering in the corner paralyzed. This certainly is not the picture of an exceptional DBA with leadership skills.

An exceptional DBA with leadership skills will make the effort to understand the task that the Developer has been charged with and take the time to discuss solution options. When the DBA can elevate the skills and morale of the Developers then they are exhibiting leadership qualities. When the DBA is busy tearing down the Developers they are a destructive element. Think about how you speak of the Developers that you work with when you are conversing with other DBAs.

Imagine the culture that I described at the beginning of this section where the DBA and Developers are infected with the "us vs. them" spirit. What will the response be when the DBA is in need of the Developer's assistance? Would the DBA even approach them for help? How effective will the DBA be in their role?

"Leaders define reality." - John Maxwell
"Be the change you want to see in the world." - Mahatma Gandhi

Comparing WHERE and FROM Filtering

2010-01-04T11:47:28Z

I am kicking off the new year with a quick jaunt back in time through Mr. Peabody's WABAC Machine. The blog entry below was published on April 30, 2008, during my pre-Simple-Talk blogging days. The topic below is something that I have found to be a valuable consideration in optimizing queries. Enjoy!

As we work with other Professionals in our industry, we quickly learn that there are many ways to accomplish a given task. When presented with multiple options in a situation, there are times where one option stands out as the optimal choice based upon our general knowledge of the database engine.

There are also times where the optimal choice may not be so obvious and will vary depending on many considerations such as database architecture, level of use, indexing, hardware configuration or general best practices. Any given approach may perform superbly with one database and drag on another.

A sample case might be that we are given two versions of a seemingly simple query that filters the data. One option is the common use of a WHERE clause. The other is utilizing the filters within the JOIN clause. The examples below illustrate the syntax differences between these options.

WHERE Filter Option

SELECT
    [TABLE1].[FIELD1]
FROM
    [TABLE1]
    INNER JOIN [TABLE2]
        ON [TABLE1].[FIELD1] = [TABLE2].[FIELD1]
    INNER JOIN [TABLE3]
    ON [TABLE2].[FIELD2] = [TABLE3].[FIELD2]
WHERE
    [TABLE1].[FIELD1] = 700
    AND [TABLE2].[FIELD2] > 1000
    AND [TABLE3].[FIELD2] > 1000

JOIN Filter Option

SELECT
    [TABLE1].[FIELD1]
FROM
    [TABLE1]
    INNER JOIN [TABLE2]
        ON [TABLE1].[FIELD1] = [TABLE2].[FIELD1]
        AND [TABLE1].[FIELD1] = 700
        AND [TABLE2].[FIELD2] > 1000
    INNER JOIN [TABLE3]
        ON [TABLE2].[FIELD2] = [TABLE3].[FIELD2]
        AND [TABLE3].[FIELD2] > 1000

When these are executed, the Query Optimizer evaluates the query and determines the best execution plan for the statement. In this case, Query Optimizer determined that both options should utilize the same execution plan; but since these statements are syntactically unique they are cached separately.

Execution Plan

Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([TABLE2].[FIELD2]) WITH PREFETCH)| |--Clustered Index Seek(OBJECT:([TABLE2].[TABLE2_P]),SEEK:([TABLE2].[FIELD1]=700 AND [TABLE2].[FIELD2] > 1000) ORDERED FORWARD)| |--Clustered Index Seek(OBJECT:([TABLE3].[TABLE3_P]), SEEK:([TABLE3].[FIELD2]=[TABLE2].[FIELD2]), WHERE:([TABLE3].[FIELD2]>1000) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([TABLE1]))
|--Index Seek(OBJECT:([TABLE1].[IX_TABLE1_2]), SEEK:([TABLE1].[FIELD1]=700) ORDERED FORWARD)

In review of SQL Server Profiler, the duration of the T-SQL batch appear identical (both reflected the value of 20 in my sample). A review of the sysprocesses system table reveals some interesting information about these two options. The CPU, Physical IO (Reads) and Memory usage are significantly different.

JOIN Option
CPU: 200
PIO: 5
MEM: 15

WHERE Option
CPU: 19110
PIO: 707
MEM: 38

According to this information, the performance of the filtering that occurred within the JOIN clause performed much better than the WHERE clause option. To ensure that the initial differences in the performance was not unique to the first execution, I ran the same queries multiple times, obtaining a cache hit for each execution. The results were consistent.

It is important to note that these numbers and results are specific to my test environment. The results reflected in your specific environment may differ; but through the sample above the process of evaluating and selecting the most optimal solution is illustrated.

NCDM Conference

2009-12-13T22:16:31Z

Last week I jetted of to Las Vegas, Nevada with my co-workers to attend the National Center for Database Marketing (NCDM) Conference. This conference was not like the conferences that we as database professionals typically attend. Rather than offering sessions about database maintenance, performance and design the offerings were focused on its use by Marketing Analysts and how it can be used more effectively to manage their campaigns.

At first I was not sure how much I would get out of the conference since it was focused in an area to which I was unfamiliar; but as I attended each session I learned how my customers in the Marketing Department use data. I learned about their needs. I learned about the questions that they ask when they approach data. I saw how the tools and methods that I have offered in the past, while currently effective, could be improved. I saw opportunities for me to anticipate their needs when provisioning data for their analysis.

I learned some of the marketing-speak so that I can better understand the requests that come my way. In the Marketing world the term "Business Intelligence" is referred to as "Customer Intelligence". The term "Marketing Database" is their portion of the "Data Warehouse" that stores data that is important to the Marketing Analyst. Understanding these terms and concepts will aid me in building a Business Intelligence solution for the company.

At the NCDM Conference, I purchased a book titled "Strategic Database Marketing" authored by Arthur Hughes. This book further discusses the terms and concepts that were presented at the NCDM Conference. I look forward to reading it and gaining a better understanding of the data needs of those that my technical services support.

There were many sessions that discussed the phenomenon of social media and how marketing efforts can take advantage of it. It was interesting to see not only how Twitter, Facebook and blogging can be leveraged in a Marketing campaign; but also see how these tools can utilized as a data source for analysis. These sessions were presented not from people who are theorizing about how these can be used; but from Marketers who have successfully leveraged social media in their campaigns.

As I boarded the plane back to Indianapolis I reflected on my experience at the NCDM Conference. While there were some sessions that were better than others, it was a very positive and worthwhile experience. I am thankful that I had the opportunity to attend. I would encourage all database professionals to seek to understand data from the perspective of their customers. Attending conferences such as NCDM go a long way in that effort. Compliment your technical education with the understanding of the business that you support. It makes you a well-rounded and effective IT professional.

Shades of Schemas

2009-11-21T17:22:31Z

I am sure that some of the database nomenclature that is used might be a bit confusing to those who are entering into the wonderful world of SQL Server. An example would be the varying use of the word "schema".

In a general database terms a schema is the organization and definition of the tables within a database, their relationship to one another and the columns that are contained within them. In layman's terms: it is the design of the database.

In SQL Server 2000, the term "schema" was directly associated with ownership of database objects (tables, views, stored procedures, etc.), which is a slight deviation from the aforementioned definition. The ownership of database objects is important because the object owner (user or role) is implicitly granted all privileges to the object. If the ownership is not explicitly defined when the object is created, the default owner is the same as the database owner; which is defined by the "dbo" default schema.

In many ways, the difference between users and ownership schemas were synonymous in SQL Server 2000. With the release of SQL Server 2005, the database object schema provided a new definition of a "schema". In this case a schema is a physical database object which serves as a logical grouping of database objects. For the .NET Developer, it is very similar to the concept of namespaces. While the ownership aspect still remains it has been separated from the principal in a such a way that it is much more flexible and scalable.

Database object schemas are a great addition to SQL Server and in many ways is more consistent with the original definition of a schema than it's SQL Server 2000 counterpart.

Sensitive Data in Memory

2009-11-06T11:52:36Z

SQL Server offers many cryptographic methods such as one-way encryption (hashing) and cell-level encryption. These methods all are designed to protect data while it is stored in the database. To utilize encrypted data it must be decrypted. In doing so, the formerly protected sensitive data is captured into the process memory in plain text.

Below is an example of a value, "ThisIsPlaintext", that is stored in the database using cell-level encryption and appears in plain text in the process memory after querying:

's e l e c t t h e w h o l e r o w . . 0 ' ' ' ' . . . 0
' ' ' . . . . ' ' ' ' ' 0 . ' . . ' ' 3 . * . T h i s I s
P l a i n t e x t . . . . . . . . . . . . . . . . . . . . .

With the HashBytes method, decryption does not occur. Instead, a value is hashed and the resulting hash is compared to the stored hashed value. If there is a match, then it is confirmed that the correct value was compared. When the HashBytes method is executed, it requires the sensitive data to be passed as a parameter in plain text. It is at this point that the plain text value is captured into the process memory. The text below illustrates this occurrence in the process memory:

s e l e c t . . . C A S E W H E N C i p h e r _ T e x t
= H a s h B y t e s ( ' S H A 1 ', ' M y S e n s i t i v e
V a l u e ' ) T H E N ' T r u e ' E L S E ' F a l s e '
E N D . . . f r o m . . . d b o . H a s h b y t e s _ S a m

A similar occurrence can be seen when a user gains access to SQL Server through a SQL Server Login. The authentication process is very similar to the HashBytes method in that it does not perform decryption of the stored password, instead it creates a hash value of the password that was submitted and compares it to the hash value that is stored in the database. When a positive match occurs authentication is granted and the submitted password is stored in plain text as a part of a connection string in the process memory, as illustrated below:

S Q L _ _ _ _ _ _ P r o c e s s _ _ _ _ _ _ A v a i l a b l e .
. . ' ' ' . - . s e r v e r = ' M y S e r v e r '; u i d = '
T e s t L o g i n ' ; p a s s w o r d = ' T h i s i s m y p a
s s w o r d ' ; A p p l i c a t i o n

TIP: Utilize Windows Authentication instead of SQL Server Logins to grant access to a SQL Server instance. Windows Authentication does not store passwords in plain text in the process memory.

At first glance this vulnerability may seem quite alarming; but it is not one that is unmitigated. To view the process memory in a Microsoft Windows operating system requires administrative privileges to the server. If a hacker, or malicious individual, were to gain this level of privileges to the server there is very little they cannot do. One of which is to disable or uninstall any software that was installed on the server to protect the process memory. Additionally, the ability to read the process memory is the least of your concerns if a hacker had gained this level of control over your server.

The exposure of sensitive data in the process memory is not unique to the Microsoft world of products. There are methods in Oracle that require plain text arguments, such as ALTER USER, which result in the sensitive values to be cached. The decryption process in Oracle also results in plain text values being stored in memory. Other operating systems, such as Unix, also reveal sensitive data in its process memory.

The protection methods that are available in SQL Server are designed to protect data while it is stored in the database. Once it is queried and cached there is a dependency upon the operating system of the server as well as the application performing the query to provide additional layers of protection. In this example, the restricted access to the process memory is managed by the operating system.

There is no magic bullet that one application can provide that mitigates all possible vulnerabilities. It is the security methods of all components, including the people factor, working in concert and in layers that achieves a secure environment.

Louisville SQL Saturday

2009-10-25T21:41:23Z

It was early morning on Saturday, October 24th when some of my friends and I piled into my Jeep, with coffee in hand, for an enjoyable road trip from Indy to the Louisville SQL Saturday event. Enroute we all enjoyed great conversations ranging from the pros and cons of virtualization to the upcoming Colts football game.

Upon arrival we signed in and began our day of learning and fellowship. There were two tracks available for the SQL hungry: The Database Administration track and the Business Intelligence track. With a fresh cup of Joe in my hand I proceeded to attend the Database Administration track. The following sessions were the ones that I attended:

- Demystifying Transact-SQL by Jason Follas
This session was a very good "back to the basics" session on T-SQL. It is always good to revisit the basics no matter how advanced you are in any discipline. Coverage of UNION/UNION ALL would have been a valuable addition to the presentation; but time was certainly a restriction.

- The XML Capabilities of SQL Server 2008 by Jason Follas
This session was also very good. It covered the XML data type and the functionality that is available in using this data type. It was good to hear more details about this powerful feature.

- Comparing Clustering Methods by Alex Prusakov
This session was presented at IndyPASS back in March, 2009. While the March presentation was very good I felt that this version was even more interesting. This presentation solidified my understanding of clustering and how I may approach this need in my own environment.

- SQL Server Consolidation and Virtualization by Sarah Barela and Ryan Jones
This session was a perfect follow-up to the topic presented by Alex. The tag team approach to the presentation was an interesting approach. At the end I was not sure that I had much to take with me and apply to my world; but there was some good questions presented by the audience.

- Powershell in SQL Server 2008 by Arie Jones
This session was presented at IndyPASS back in November, 2008. This session was fast paced. The power of this feature was successfully demonstrated. The challenge with this feature is that it requires some hands-on to fully grasp its scope. Arie's energy kept the audience going as the day was heading for a close.

In addition to attending the above sessions I engaged into some great discussions with the event organizer and Louisville PASS Chapter leader, Malathi Mahadevan. She successfully organized a quality event. One in which many in attendance look forward to a sequel in the near future. I am also very pleased to see the SQL Saturday events begin to be popping-up in the mid-west.

Less Than Desirable Reads

2009-10-14T10:44:53Z

Once again, firing up Mr. Peabody's WABAC Machine unearthed a valuable blog entry that I posted before my Simple-Talk blogging days. The post below was originally published on March 2008 and maintains its relevance. Enjoy!

I was watching a very interesting movie quite a few months ago. In this movie, the primary character hopped into his sports car in the middle of the day, backed out of his driveway and proceeded to travel down a deserted street in the middle of New York City. I have never visited the "Big Apple" but I would suspect that this occurrence would be quite an anomaly.

It would be reasonable to assume that having only a singular transaction occur within a SQL Server database would be an equivalent anomaly to the one presented in the movie previously mentioned. To this, the delicate balance between data concurrency and data consistency begins.

Transaction isolation level in stored procedures is the traffic cop on the street of transactions. They prevent those nasty collisions that backup the flow of data. While a detailed description of all transaction isolation levels is another blog entry, I wanted to explore the Evil-Read Trio that can occur if careful consideration of these transaction isolation levels does not occur.

THE DIRTY READ: These are not the ones that are found in the foot locker of your neighborhood teenager. These are transactions that read uncommitted data modifications that are made by another transaction. This can wreak havoc if aggregated calculations occur while transactions are being added or modified and especially of these modifications are rolled back. Using the READ COMMITTED isolation level will prevent the occurrences of dirty reads.

THE NON-REPEATABLE READ: Complex stored procedures may execute a SELECT statement more than once within the same transaction. If another transaction modifies the data prior to the completion of the first transaction different results may occur when the second occurrence of the SELECT statement is run. The utilization of REPEATABLE READ isolation level will prevent the occurrences of non-repeatable reads as well as dirty reads.

THE PHANTOM READ: Much like a non-repeatable read, this type of read depends upon a multiple execution of a SELECT statement within the same transaction and another transaction modifying the data in which the SELECT statement is querying. In the case of the phantom read the modification of the data is an INSERT or DELETE which causes the row in question to appear and/or disappear within the reading transaction. While Ghostbusters may not have a good solution for this, the use of SERIALIZABLE READ isolation level will prevent phantom reads as well as non-repeatable and dirty reads.

The question may arise to how it can be determined that these types of reads are occurring. The ol' handy-dandy SQL Server Profiler can be employed to expose these reads. Include the SQL:StmtStarting , SQL:StmtCompleted, SQL:BatchStarting and SQL:BatchCompleted event classes in
the trace to catch the occurrence.

SQL Server MVP

2009-10-10T20:05:15Z

On July 13th I received an e-mail that stated "I wanted to let you know that you have been nominated for an MVP award for your exceptional contribution to the Microsoft technical community!". I was thrilled with this prospect; but knowing that there are likely thousands of candidates across the globe that are nominated each quarter I was prepared to be happy with just the nomination.

It was late on October 1st when I arrived at home from my day of work. After dinner, I migrated into the family room and began my nightly routine of catching up with the daily chat on Facebook and Twitter. Upon checking my e-mail I saw e-mail from Microsoft that read, "Congratulations! We are pleased to present you with the 2009 Microsoft® MVP Award!". the rest of the evening is a blur.

The next morning, I gave Jimmy May, my buddy who nominated me for the award, a phone call to inform him of the good news. In his admirably enthusiastic manner he exclaimed "Way to go El Magnifico!", which is his long time nickname for me. I was on cloud nine.

In my conversation with Jimmy, he asked me what would I do with my MVP status. I replied with "Keep doing what I'm doing." My participation and contribution to the technical community was not driven by the passion to receive the award; but rather the passion to share knowledge and to aid in the building of a strong technical community that has a collaborative spirit and is accessible to all who are interested. That has not changed with this honor.

Another question that has been asked of me lately is how to become a Microsoft MVP. There is no such thing as a check list of things to accomplish which results in the MVP designation. My answer to this question is to participate in the technical community, find your passion and be its advocate.

Strong Password Generator

2009-09-30T10:21:56Z

Frank, the DBA, has been asked to create a series of passwords that will be used to protect a collection of symmetric keys. These keys will in turn be used to protect some sensitive data in a database.

Frank knows that the expectation is that these passwords will need to be strong passwords. Using the names of his cat's new born litter will not suffice. He made that mistake at his last job. Reviewing his notes to remind himself of the definition of a strong password he identifies the following:

1) The length of the password must be 8 characters or greater.
2) Does not include a series of letters that make a word or phrase.
    For example: "Password", "BettyIsHot", "FrankRocks".
3) The series of passwords that will be created are not sequential.
    For example: "Password1", "Password2", "Password3".
4) Contains a variety of characters including uppercase letters, lowercase letters,
    numeric values and special characters.

With a sigh, Frank makes his first attempt at his series of passwords. It wasn't long until his human nature creped in and his so-called random series of characters didn't appear as random they should be. The injection of significant dates (08131969, 12071941 and 05291453), his favorite bands (U2, UB40 and B52s) and pseudo-words (M0V31T, R4d1c@L and P@sSw0rd) kept sneaking in.

After a few minutes of contemplation, a bag of beef jerky and a bottle of Yoo-Hoo, inspiration descended upon Frank like a Marcel Duchamp painting. He opened Management Studio and began typing furiously. The result was the following T-SQL script that assembles a series of characters in a loop that ultimately produces a strong password:

-- Variables
DECLARE @PassLen int;
DECLARE @Pass varchar(50);
DECLARE @LoopCt int;
DECLARE @Rnd int;

-- Password length
SET @PassLen = 10;
-- Starting value of password
SET @Pass = '';
-- Starting value of loop
SET @LoopCt = 1;

-- Loop to generate the password
WHILE @LoopCt <= @PassLen
    BEGIN
        -- Generate a random number to select a character
        SET @Rnd = Convert(int,(1 + RAND() * (9-1)));

        -- Build the password using the selected character
        SELECT @Pass = @Pass +
            CASE
                -- This option is weighted
                WHEN @Rnd >=1 AND @Rnd <=3 THEN
                    -- a-z
                   CHAR(Convert(int,(97 + RAND() * (122-97))))
                 -- This option is weighted
                WHEN @Rnd >=4 AND @Rnd <=6 THEN
                    -- A-Z
                   CHAR(Convert(int,(65 + RAND() * (90-65))))
                WHEN @Rnd = 7 THEN
                    -- 0-9
                    CHAR(Convert(int,(48 + RAND() * (57-48))))
                ELSE
                    -- #$%& (Special Character)
                    CHAR(Convert(int,(35 + RAND() * (38-35))))
             END

       -- Advance the loop
        SET @LoopCt = @LoopCt + 1;
    END

-- Return the password
SELECT @Pass;

GO

In his code, Frank thought it wise to weight the uppercase and lowercase letters so that they would be selected more often than the numeric and special character to prevent the occurrence of passwords that look like "1234567%9#", "#$%&%$#%$1". A sampling of the passwords that Frank generated through this code are: DqOC0bPkqF, Q3t6%mQk%l, UAFdK%gvvq and Rrs#qm%#f$.

After a few executions of this code Frank completed his task at hand. He handed his list of generated passwords to Betty, the Junior DBA, to implement. The data was once again safe. Betty was happy to no longer see Frank's lame flirting attempts through passwords like "W1llUg02th3M0v1eZw1thM3?".

Protecting SQL Server Data

2009-09-12T17:51:35Z

It was late in 2008 and I had been blogging for over a year in my dusty corner of the Internet on various SQL Server topics when an opportunity arose to move my blog to Simple-Talk.com. With this opportunity I decided to focus my blog topic to security of sensitive data which had become a passion of mine. Through this opportunity another was born: to author a book.

Writing a book, technical or otherwise, has been a bucket-list (items to do before I "kick the bucket") item of mine for as long as I can remember. I have attempted to begin the process on various topics throughout the years. Some attempts we no more than an idea scribbled on a napkin; others actually got as far as a first draft of an introduction. There was even a failed attempt to author a fiction novel that would be something similar to the stories written by Dashiell Hammett.

It was early 2009. My latest attempt of fleshing out of a book idea was an outline on the subject of securing sensitive data. This outline had been periodically molded and reshaped over the past year. It was beginning to look somewhat interesting. So much so, I began to seek some advice from other authors in regard to the publishing process. It was through a conversation with Brad McGehee in which I expressed an interest in authoring a book. I mentioned my outline to him and he encouraged me to contact Simple-Talk Publishing to see if they would be interested in pursuing it. To my surprise they were very interested!

With the encouragement from my editor, Tony Davis, and valuable feedback from Brian Kelley the outline was finalized and the writing process began. Over the next six months I spent many hours in the evening and on the weekends researching, writing, scripting and revising. There were times where the sentences flowed like water; other times like molasses. There were chapters submitted for first draft review that were completely re-written on the second draft. There were chapters that were spawned from other chapters and the book was re-organized a time or two. I thoroughly enjoyed the entire process.

There were a few versions of the title for my book. Originally, it was the overly lengthy "A Pocket Guide to Protecting Sensitive Data in SQL Server". Later it was shortened to "Protecting Sensitive Data in SQL Server". Some early notifications of my book listed this version of the title. Finally, the search-optimized and more concise "Protecting SQL Server Data" was selected as the final version of the title.

There were a few concepts tossed to-and-fro for the image that would be presented on the cover of the book. Simple-Talk Publishing uses a general theme of images of gates which is a play off of their affiliation with Red Gate Software. An early suggestion that I provided was the ironic image of a man locking a gate while getting pick-pocketed. The wisdom of the artists at Simple-Talk Publishing presented an alternate option of a gate at the Bodleian Library at the University of Oxford. It was an image that was complimentary to the topic. The image to the left is the final cover for my book. It is one in which I am very pleased.

On September 21, 2009 an eBook version of my book will become available for download. Shortly afterwards a hardcopy version on my book will be available at Amazon.com for purchase. It is my hope that you enjoy reading this book as much as I did writing it.

Below is the list of chapters for my book Protecting SQL Server Data:

Chapter 1: Understanding Sensitive Data
Chapter 2: Data Classification and Roles
Chapter 3: Schema Architecture Strategies
Chapter 4: Encryption Basics for SQL Server
Chapter 5: Cell-level Encryption
Chapter 6: Transparent Data Encryption
Chapter 7: One-way Encryption
Chapter 8: Obfuscation
Chapter 9: Honeycombing a Database
Chapter 10: Layering Solutions

Let's Do The Time Warp

2009-09-03T02:24:27Z

Back in November of 2008, I blogged on the topic of Transparent Data Encryption (TDE) and its affects on the tempdb database. Within that post I mentioned that the tempdb is dropped and recreated when the instance of SQL Server is restarted. Through the process of restarting an instance and reviewing the tempdb and its accompanying database encryption key, an anomaly was discovered. Here is the scenario:

On an instance of SQL Server that contained a database that had TDE enabled, I stopped the instance by right-clicking on the instance within SQL Server Management Studio (SSMS) and clicked on the "stop" option. Once the instance stopped, I started it again by right-clicking the instance and clicked the "start" option.

I queried the sys.databases catalog view to reveal the create date of my tempdb:

SELECT
    name,
    create_date
FROM
    sys.databases
WHERE
    name = 'tempdb';

The results of this query revealed that the current version of the tempdb was created on:
2009-09-01 21:16:26.623

I then queried the sys.dm_database_encryption_keys dynamic management view (DMV) for the database encryption key that is used to protect the tempdb database:

SELECT
    database_id,
    create_date
FROM
    sys.dm_database_encryption_keys
WHERE
    database_id = 2;

The results of this query revealed that the current version of the tempdb database encryption key was created on: 2009-09-02 01:16:18.583

At first glance, the difference in the date might give appeared to contradict the understanding that the database encryption key is recreated as the tempdb is recreated during an instance restart; but with closer inspection I noticed that the create date of the encryption key is in the future!

I thought to myself "It's astounding. Time is fleeting. How could this be?"
My recent encounter of converting my time zone, Eastern Standard Time (EST), to Greenwich Mean Time (GMT) revealed that this five hour difference was familiar.

With a jump to the left, and a step to the right, this baffling time warp was cracked. The sys.databases catalog view reflects the time based off of the time zone that has been defined on your server, in my case EST, while the sys.dm_database_encryption_keys DMV utilizes GMT; therefore, the understanding is confirmed: the tempdb database encryption key is recreated along with the recreation of the tempdb database when the instance is restarted.

Varchar and NVarchar

2009-08-21T10:31:17Z

Another trip with Mr. Peabody's WABAC Machine unearthed a valuable blog entry that I posted before my Simple-Talk blogging days. This one is somewhat of a "Back to the basics" style blog entry that I thought would be worth sharing once again. Enjoy!

If you peruse quotes and philosophies of very successful people it will not take long to see that revisiting the basics of a discipline is something that is critical to excelling; thus, I thought that it would be a good idea to comment on the varchar and nvarchar data types.

According to SQL Server 2005 Books Online the varchar(n) data type is described as:
"Variable-length, non-Unicode character data. n can be a value from 1 through 8,000."

According to SQL Server 2005 Books Online the nvarchar(n) data type is described as:
"Variable-length Unicode character data. n can be a value from 1 through 4,000."

The "n" referred to above is the defined maximum size of the data type. For example declaring the column in your table as varchar(50) will mean that the column will store up to 50 characters. For the untrained eye the immediate impression may be that varchar can store twice the data of nvarchar. In actuality both data types store the same number of bytes - it is the number of characters that differ. Within the BOL definitions above the key word to pay attention to is "Unicode".

Non-Unicode characters are stored in a single byte. For example: "A" would be encoded as an ASCII value of "65". These single byte values range from 0 - 255 which are represented in an ASCII table. The following link is a good resource for these values: http://www.asciitable.com/

In reviewing the ASCII table you will find that if you are using languages that utilize characters that are not included in the ASCII table (such as Japanese, Chinese, Korean, etc.) it can be rather limiting.

Unicode characters are encoded in two bytes (double-byte). For example: "A" would be stored as a value of "A". These double-byte characters have 65,536 combinations which accommodates majority, if not all, languages in the world. A good tool to convert ASCII text to Unicode text can be found at this link: http://www.industrialtrainer.com/Unicode.shtm

If the database that is being designed does not need to consider character sets beyond the ASCII set the use of varchar would be the more efficient storage option; but in this world of ever growing globalization and need to accommodate various character sets it may be wise to consider the strategic utilization of nvarchar for select columns.

Random Numeric Variance

2009-07-29T10:57:41Z

In the efforts of preventing unauthorized access to sensitive data, scrambling production data for testing, staging and development environments is a recommended practice. There are many ways to approach data scrambling. One option for numeric values is a process called "numeric variance".

Numeric variance is a process in which the actual numeric values contained within a specified column are increased or decreased by a given percentage. For example, an original value of a bank account balance for a customer is $500. With a numeric variance of 10% applied, the new value for the account balance will be changed to $550.

The addition of a randomizer will strengthen the results of the numeric variance. This strength is that each row of the affected column will be increased or decreased by a different percentage than the other rows.

The RAND function can be utilized to generate a random number. By leaving out the seed argument, SQL Server will dynamically determine the seed that will be used. If a hard-coded value is entered into the seed argument the value returned will be the same for each execution.

Below is some sample code that can be used to perform a randomized numeric variance:

-- Set original value
DECLARE @OrigVal int
SET @OrigVal = 500

-- Set variance to 10%
DECLARE @VarPct numeric (5,2)
SET @VarPct = 10

The preceding code simply provides us with variables to store our original value ($500) and our maximum variance percentage (10%). This process can be created either as a stored procedure or a user defined function; if created as such, these can be used arguments.

-- Set number range for random number
DECLARE @Range int
SET @Range = (((0-@VarPct)+1)-@VarPct)

-- Set random element
DECLARE @Rand int
SET @Rand = CONVERT(int,(@Range * RAND() + @VarPct))

The preceding code uses the RAND function to create a randomized value. When you attempt to create a user-defined function with RAND function contained within it you will receive the following error:

Msg 443, Level 16, State 1, Procedure <UDF NAME>, Line <LOCATION OF RAND>
Invalid use of a side-effecting operator 'rand' within a function.

A work around this issue is to create a simple view that can be referenced to obtain the RAND value. In our specific example, the resulting @Rand variable will contain the value of -19. This value represents the range from -9% to 10%, including 0%.

-- Determine numeric variance
DECLARE @Variance int
SET @Variance = CONVERT(INT,((@OrigVal*@Rand)/100))

-- Return new value
SELECT @OrigVal + @Variance
GO

The preceding code uses our randomized value and generates a new variance with each execution. For illustration purposes let's say that the @Variance value returned is 35. This value is then added to our original value to change 500 to 535. If @Variance returned -45 the adding of a negative number is the same as subtracting a positive number; therefore 500 + -45 = 455.

This process is repeated for each row. At the end, our result is a successfully scrambled set of numeric values that protects the sensitive data that is contained within these columns.

Storage Allocation for NULL

2009-07-10T15:12:00Z

A friend of mine approached me the other day and asked a great question: "When a NULL value is stored in a varchar data type, how much memory is allocated in its storage." My friend, being of the scientific mind that he is, then added "...and how can I verify it."

The slice of pepperoni pizza in my hand provided a great device to allow a pause to contemplate a response without giving the impression that I did not know the answer to his question. Unfortunately, the bite that I took was not sufficient because I was still working on my response when I was done chewing. I defaulted to the response of "Alex, that is a great question." and politely promised that I would research the answer and get back to him.

The following is the results of my research and the test I used to verify my answer:

The Sample Tables
For our test, we will create two simplified tables that contains a single column and three records. The difference between these tables is the data type that is used for our column:

-- Uses the CHAR data type
CREATE TABLE MyCharTest (testCol CHAR(10) NULL);

-- Uses the VARCHAR data type
CREATE TABLE MyVarCharTest (testCol VARCHAR(10) NULL);

The data that will be used in our test will be three values: "1234567890", "" and NULL. For later reference, we will make note of the physical sizes of these values through the DATALENGTH method. This provides us a measurement without the influence of the data type in which they are stored.

SELECT DATALENGTH('1234567890');
-- Returns a value of 10 bytes

SELECT DATALENGTH('');
-- Returns a value of 0 bytes

SELECT DATALENGTH(NULL);
-- Returns a value of NULL

The final preparation for our sample tables is to populate the with our values:

-- Inserts into MyCharTest table
INSERT INTO MyCharTest (testCol) VALUES ('1234567890');
INSERT INTO MyCharTest (testCol) VALUES ('');
INSERT INTO MyCharTest (testCol) VALUES (NULL);
GO

-- Inserts into MyVarCharTest table
INSERT INTO MyVarCharTest (testCol) VALUES ('1234567890');
INSERT INTO MyVarCharTest (testCol) VALUES ('');
INSERT INTO MyVarCharTest (testCol) VALUES (NULL);
GO

The Test Script
The following is a script to obtain a page dump of a given table. This script uses DBCC statements to obtain the page dump information. Prior to executing the script below, replace the @DB and @TBL variables with the name of your database (@DB) and table (@TBL) that you are evaluating. Also, set the output of the script to be presented in text (In the menu bar of Management Studio, click on: Query. Results To. Results To Text); otherwise the DBCC PAGE information will not be presented.

We will execute this script once for our MyCharTest table and a second time for our MyVarCharTest table:

USE MyDatabase;

-- variables to make the script flexible
    DECLARE @DB varchar(50);
    DECLARE @TBL varchar(50);
    SET @DB = 'MyDatabase';
    SET @TBL = 'MyCharTest';   --

-- enables the specified trace
-- 3604 is trace flag output is returned to the application
DBCC TRACEON(3604);

-- create table variable to capture DBCC IND results
    DECLARE @Ind TABLE (
                    PageFID tinyint, PagePID int, IAMFID tinyint, IAMPID int,
                    ObjectID int, IndexID tinyint, PartitionNumber tinyint,
                    PartitionID bigint, iam_chain_type varchar(30), PageType tinyint,
                    IndexLevel tinyint, NextPageFID tinyint, NextPagePID int,
                    PrevPageFID tinyint, PrevPagePID int
                    );
    -- execute DBCC IND and capture in @Ind temp table
    INSERT INTO @Ind EXEC('DBCC IND(' + @DB + ', ' + @TBL + ', -1);');
    -- snag the PagePID from the @Ind temp table
    DECLARE @PagePID int;
    SELECT @PagePID = PagePID
        FROM @Ind
        WHERE PageType = 1 and PrevPageFID = 0 and PrevPagePID = 0;

    -- execute DBCC PAGE to see the memory dump
    DBCC PAGE (@DB, 1, @PagePID, 3)
    -- disables the specified trace.
    DBCC TRACEOFF(3604);
GO

The CHAR Test
The first execution of the script above is against our MyCharTest sample table that contains a column data type of CHAR(10). The results were:

For the first row, containing the value "1234567890", the page dump appears as follows:
Slot 0, Offset 0x60, Length 17
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 17
Memory Dump @0x6198C060
00000000:   10000e00 31323334 35363738 39300100 ?....1234567890..
00000010:   00???????????????????????????????????.
Slot 0 Column 1 Offset 0x4 Length 10 Length (physical) 10

For the second row, containing the value "", the page dump appears as follows:
Slot 1, Offset 0x71, Length 17
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 17
Memory Dump @0x6198C071
00000000:   10000e00 20202020 20202020 20200100 ?....          ..
00000010:   00???????????????????????????????????.
Slot 1 Column 1 Offset 0x4 Length 10 Length (physical) 10

For the third row, containing the NULL value, the page dump appears as follows:
Slot 2, Offset 0x82, Length 17
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 17
Memory Dump @0x6198C082
00000000:   10000e00 88e18308 00000000 00000100 ?.....á.........
00000010:   01???????????????????????????????????.
Slot 2 Column 1 Offset 0x0 Length 0 Length (physical) 0

The VARCHAR Test
The second execution of the script above is against our MyVarCharTest sample table that contains a column data type of VARCHAR(10). The results were:

For the first row, containing the value "1234567890", the page dump appears as follows:
Slot 0, Offset 0x60 Length 21
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 21
Memory Dump @0x61EEC060
00000000:   30000400 01000001 00150031 32333435 ?0..........12345
00000010:   36373839 30??????????????????????????67890
Slot 0 Column 1 Offset 0xb Length 10 Length (physical) 10

For the second row, containing the value "", the page dump appears as follows:
Slot 1, Offset 0x75, Length 9
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 9
Memory Dump @0x61EEC075
00000000:   10000400 01000000 00?????????????????.........
Slot 1 Column 1 Offset 0x0 Length 0 Length (physical) 0

For the third row, containing the NULL value, the page dump appears as follows:
Slot 2, Offset 0x7e, Length 9
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 9
Memory Dump @0x61EEC07E
00000000:   10000400 01000108 00?????????????????.........
Slot 2 Column 1 Offset 0x0 Length 0 Length (physical) 0

The items highlighted in red represent the plain text values as well as the bytes allocated for its storage. The "record size" value in orange identifies the allocated storage space for the entire row (all columns plus metadata) in which the value is stored. In our case our row consists of only a single column. The "length (physical)" value, also in orange, identifies the physical length, in bytes, of the plain text as it is being stored.

The Evaluation
With the MyCharTest table we see that in all three rows the record size remains the same; while the MyVarCharTest table varies. This dynamic shows that the MyCharTest is allocating the full amount of physical storage for the value regardless of the physical length of the value being stored.

Another interesting observation was how the "" value was handled in these tests. In our setup for this test we evaluated the length of our values without the influence of the table column data type. The "" value returned 0 bytes in length in this evaluation. The page dump for the MyCharTest table shows that the physical length for the "" value is 10 bytes; while the MyVarCharTest table page dump shows the physical length for the "" value as 0 bytes. The physical length of the "1234567890" and NULL values did not vary in physical length. interesting.

The Answer
After all of the above, both aspects of my friend's question was finally answered.
"When a NULL value is stored in a varchar data type, how much memory is allocated in its storage.":
No memory is allocated for the storage of a NULL value. (Beyond the row's metadata)
"...and how can I verify it.":
Check out my blog.

... and now it's time for a second slice of pizza. ;D

Encryption and Backup Files

2009-07-01T09:24:00Z

In the efforts to protect sensitive data there are some considerations that expand beyond its storage within database; more specifically, the backup files that are generated through the database backup process. Backup files are often kept on devices or media that are removed from the database server and stored in a separate location. If not properly secured these devices can be stealthily snatched away, exposing the backup files contained within.

If you were to open a backup file in a program like Notepad, your first impression is that the file contains a bunch of garbley-gunk resulting in the false perception that any plain text sensitive data in the backup file cannot be disclosed by simply scanning the raw backup file. In reality, the data in the database that is stored in plain text is not modified and appears in the backup files as plain text.

To see this for yourself, obtain a plain text value that has not been encrypted or hashed in your database, such as a credit card number or Social Security Number. Open your most recent backup file in an editor like Notepad, Wordpad or Textpad and perform a search for that value. You will discover that the sensitive data that is in plain text within the database also is in plain text in the backup file.

Microsoft SQL Server 2008, Enterprise Edition, introduced Transparent Data Encryption (TDE) as a new feature that was designed to address this issue. This feature encrypts the physical files of the database. This includes any backup files that are generated through the native backup functionality.

The Database Encryption Key (DEK) is a key that was introduced to the encryption key hierarchy in support of the TDE feature. The DEK encrypts the physical files using one of the following algorithms:

Advanced Encryption Standard (AES) using key length options of 128, 192 or 256 bit.
Triple DES 3 Key which is an expanded key length version of Triple DES using a 192 bit key.

For a more in depth exploration of TDE and backup/recovery, check out my blog entry with the title "TDE: Under The Hood With Backup".

For those who do not have Microsoft SQL Server 2008, Enterprise Edition, or do not wish to implement TDE, the need to protect your database backup files remains. There are many third-party backup products available on the market that provide encryption to the backup files that they generate. Three popular options are:

LiteSpeed for SQL Server (Quest Software: Price not published)
This product encrypts backup files using:

Advanced Encryption Standard (AES) which uses a 256 bit key.

The encryption feature of this product has been available for many versions. The current version of this product is 5.1 which added features such as the ability to define when to perform a differential or full backup, support for SQL Server 2000/2005 Standard Edition, native log shipping conversion and more. For more details on the latest release check out Quest Software's release information page.

SQL Safe Backup (Idera: $1,195 - estimated)
This product offers the user a choice of the algorithm used to protect the backup files. The available options are:

Data Encryption Standard (DES) which uses a 64 bit key. (not recommended)
Triple DES which uses a 168 bit key.
RC2 which uses a 128 bit key.
Advanced Encryption Standard (AES) which uses a 256 bit key.

The AES 256 bit key encryption option is new with its current version, 5.0, which became available on June 29, 2009. This version also provides log shipping as new functionality. For more details on the latest version of this product, check out Idera's "What's New" page.

SQL Backup Pro (Red-Gate Software: $795 - estimated)
This product encrypts backup files using:

Advanced Encryption Standard (AES) algorithm with an option of 128 or 256 bit key length.

The encryption features of this product have existed since its initial release. Version 4 introduced the 256 bit key option. At the time of this blog entry the current version is 5.4. Version 6.0 is slated for release this Summer. This new release will include features such as handling of network outages during backup, "self-healing" log shipping, advanced compression algorithm and much more. For more details check out Red-Gate's fact sheet for the release of version 6.0.