Simple-Talk

Encryption Alternatives

Johnm — Fri, 10 Oct 2008 13:24:40 GMT

Databases often contain sensitive information and cell-level encryption is a very effective method to protect this information from those who should not have access to it. Although, encryption is not without its challenges. There is a performance cost. There also is additional administrative tasks required with its introduction into the database. That is why there are some instances where encryption is restricted, by the powers that be, from being utilized.

When the DBA or Developer finds themselves faced with the requirement of protecting sensitive data while not being able to utilize encryption they must seek alternatives to this feature. There are many alternatives available and some, when appropriately implemented, can be as effective as encryption itself. Below are a few examples of these alternatives:

Store Only The Data You Need
In this day and age storage media is relatively inexpensive. This is a good thing, but it does enable lax storage strategies. When determining the schema of your table consider the use of the columns that are being added, especially ones that contain sensitive data. Try to avoid the "We may need it someday" mind set.

For example: The Social Security Number is an identifying number provided by the Federal Government for individuals in the United States. This piece of data is often found in tables that contain other information about an individual. If this piece of data is not being used functionally by the applications that access the database its inclusion in the schema should be reconsidered and challenged.

If it is determined that the sensitive data must be stored in the table, consider only storing a portion of the data rather than the entire data. For example: If you must store the Social Security Number, storing the last four digits may suffice for its use in your database.

Masking Plain Text
We are all familiar with the standard method of displaying the text of a password as it is being typed into a text box; all letters are replaced with an asterisk (*). Most are also familiar with the masking methods used on a credit card receipt: the last four digits of the credit card number are displayed in plain text while the remainder of the number are replaced with an asterisk.

These two examples demonstrate how sensitive data within the database is hidden at the point of disclosure from the eyes of those who should not see it. While this approach is very effective it does not necessarily suggest that the sensitive data that is stored in the database is similarly masked. According to PCI Compliance Standards, Requirement 3.4, credit card numbers (PAN) cannot be stored in a database in its entirety. Masking this information within your database is a valid means of meeting these requirements.

When implementing masking, please remember that the storage of data that is entirely masked is rather useless since it cannot be read or searched by anyone regardless of the security level. Determining the extent of masking that is to be applied to the sensitive data is a fine balance between maintaining the value of the data for authorized users while rendering it useless to those who are unauthorized for its use.

Coding Data
Utilizing numeric or alphanumeric values to represent the real values of data is often a practice that can be found in normalization efforts. Placing a value of "16" to represent the State of Indiana or "01" for the State of Delaware is a very efficient means of storing this data. It also allows for the represented text to change without having to modify all of the rows that are associated with that value.

This same practice can be utilized to protect sensitive data. Consider a financial planner who maintains records of their client's financial history in a database. Knowing that certain aspects of their information is sensitive the financial planner might store the value of "1250" to indicate that his client has declared bankruptcy or "3427" to indicate the beneficiary of the client's assets. Without access to the data that provides the interpretation of this code, the sensitive data remains a mystery.

Hashing
SQL Server offers the HashBytes method. This method uses a specified algorithm to turn plain text into a binary value that then can be stored in the database masking the sensitive data it represents. The specific syntax of using the HashBytes method can be found at:
http://msdn.microsoft.com/en-us/library/ms174415.aspx

There is a fine line between hashing data and encrypting data. Encrypting data involves the process turning plain text into cipher text as well as decrypting the data back into plain text when needed. Hashing converts the plain text into a binary value and is only compared with other hashed values to produce validation of plain text; thus removing the key management aspect of encryption as well as a portion of the performance affects.

Here is a great blog entry that talks about using the HashBytes method to secure sensitive data:
http://blogs.msdn.com/sqlcat/archive/2005/09/16/469257.aspx

While these options have their own benefits and vulnerabilities they do offer some creative approaches to protecting the sensitive data that has been entrusted to your organization.

All Your Kitchen Are Belong To Us

red@work — Thu, 09 Oct 2008 10:42:00 GMT

Apropos of nothing, we’ve just moved into a new office to house our ever-burgeoning workforce, and naturally there are a few important tweaks that need to be made to make the place fit for use. Like upgrading how much sweet, sweet caffeine the new coffee machine will dispense in one go, for a start. Until 2 weeks ago, it gave precisely half a cup of coffee every time you pressed the button. Ok, not a problem, just press the button twice, yes? Except that you now have a mug so full that surface tension is the only thing keeping the coffee inside it. And its temperature is best described as ‘volcanic’, so sipping it down to more practical levels is not really an option.

One of the more prosaic benefits of having seriously technical people in the building is that they are very good at tinkering with anything electrical. In this case, they managed to convince the coffee machine to give us a decent cup of coffee at a temperature more tolerable to human beings without fire-proof throats (and for that we are all eternally grateful). They’ve also had a go at reconfiguring one of our fridges, though I was too afraid to ask exactly what they hoped to achieve *. Email in the desserts, perhaps.

It amused me to walk through the kitchen and hear this hushed conversation:

“Ahhhh…”

“So this is…”

“Yup, and then you need to press …”

“Right… so how do you get the next…?”

“Just press it again”

“Oh, ok.”

… and then turn around and find Nigel and Rob peering into the innards of the refrigerator. When I finally got the courage to ask them about it later, their only comment was:

“We are 133t h4x0r5”

I'm too scared to ask what’s next on their hit-list. Has anyone else hacked / modded an appliance?

post by Chris Massey

* I subsequently discovered that they were trying to get the top shelf of the fridge to cool down, as it was, at the time, actually hotter than the ambient room temperature.

Power to the (Right) People

red@work — Wed, 08 Oct 2008 10:45:00 GMT

The best way of leaning to swim is to get thrown in the Deep(er) End of the pool. Preferably by someone with the inclination to come rescue you if you start to drown. You either learn to tread water remarkably fast, or you get fished out, calmed down, and promptly thrown in again. So that’s exactly what Red Gate does; it takes people, and sends them barreling, head-first, into the educational pool:

“I thought everyone was really enthusiastic about being here. Like there must be something in the air conditioning. People really loved being here. I’ve never worked anywhere where people have been so happy to work and so proud of their company.

I remember Andras, my manager, saying
“Do you know about SQL Server?”
“No”
“Well then you can read up about it and give us a presentation about it. And do you know about C#?”
“No, not really”
“Well you can give us a presentation on that, too. So is there anything else that you don’t know?”
“I know everything else. All other knowledge.”

That was quite frightening. It’s good way to learn, when you’ve got that sort of pressure on you.”
- Alice Easey

“Pretty much the first thing that happened after I got in was that Neil gave me a massive, thick book on SQL Server and said “Read that, and at the end of the week, I want you to do a presentation on how you’d create a set of command line tools for our SQL Comparison tools”. There was no sense of easing in; although I did get told I had to go home at 5.”
- Bart Read

I was just told, “Write a program, get yourself used to the language”, because I haven’t used C# a lot before.
But I wasn’t told “write this particular thing” which I knew was meaningless, I was told to make up a meaningless job for myself, just to get the training done. And that’s exactly what I want to do, because I’m perfectly capable of thinking up a meaningless job to get myself into the language.
-Alex Davies

Clearly this ‘Deep-End’ style of learning is effective; people keep wanting to buy our products, and we keep growing as a company. Yet just as clearly, venturing into unknown waters is not an induction process that will work for everyone. Many people prefer to be eased into a new role, and shown the proverbial ropes one at a time. And there’s nothing at all wrong with that, it’s just a different style of learning. But it’s a style of learning that makes the ‘Deep End’ system seem like a kind of nightmare.

One of my colleagues mentioned to me that “Deep End” implies ‘drowning’ and ‘overload’, and that’s very much NOT what I’m suggesting is the case here. I’m not saying that, as a new starter, you’re given a desk and PC, told “Go get ‘em, tiger!” and that’s the end of your human contact. Not by a long shot – The open atmosphere in the office means that I can ask anyone for help if I need it (I’ve even had one of the directors lend me a hand on occasion). It’s all about learning-by-doing and getting involved immediately; I have to be self-motivated and passionate if I want to keep up with the energy here. There’s no time for spoon-feeding or hand-holding, only for jumping in, both feet first, with enthusiasm and (we all like to think) creativity.

So should you come prepared with a life-jacket and inflatable dinghy? Absolutely not, but bring a snorkel. Or maybe scuba gear. To thrive in this kind of environment, you need to be someone who gets the best out of themselves when they get stuck in. And to keep the open culture alive, you also need people who are receptive to that kind of collaborative and generous atmosphere. When you find these people, they create a workplace which is both energizing and entertaining. As an added bonus, they also create a strong sense of a welcoming community; importantly, a sense which endures as that community swells and outgrows its original boundaries.

The timeline of Red Gate’s non-stop growth.

All Work and No Play?

If you’re going to have these semi-mythical, self-motivated and creative people as your employees, you have to give them space to play. You try and chain someone like that to a desk and just see how far you get. Recruit the right people (and think carefully about who they are, first). Nudge them into the thick of activity and let them get on with it. Give them NERF guns.

Yes, I’m being silly; but my point is no less meaningful for it. If people are going to thrive in the Deep End, they’re also going to need to act like big kids occasionally.

So, take one portion of motivated and intelligent people, drop into the deep-end with a generous helping of fun, and mix well. The result? A crucible of creative ideas, fuelled by people who are (or are rapidly becoming) incredibly skilled in their chosen fields, and which encourages individuality. Sounds like a recipe for success...

The right people. Empowered.

Post by Chris Massey

Understanding Master Keys

Johnm — Fri, 03 Oct 2008 14:57:56 GMT

At the heart of encryption are keys. These allow you, the authorized user, to unlock the subsequent key in a key hierarchy or the cipher text contained within a database. This hierarchical relationship in SQL Server 2008 is illustrated at http://msdn.microsoft.com/en-us/library/ms189586.aspx. For SQL Server 2005, the hierarchy is illustrated at http://msdn.microsoft.com/en-us/library/ms189586(SQL.90).aspx

Within SQL Server, the head of the encryption family is the Service Master Key. This key is a Symmetric key that is based off of the service account credentials as well as the machine key from the Windows Data Protection API (DPAPI). There is only one Service Master Key per instance of SQL Server and is created at setup of that instance.

The Database Master Key is a Symmetric key that is unique to each catalog (database) within the SQL Server instance. This key is encrypted using the Service Master Key of that instance. When a catalog is created the Database Master Key is not automatically generated. These keys are created using the CREATE MASTER KEY command.

Once the Database Master Key is created, additional keys can be created to increase the granularity of of the encrypted data. For example a table or cell can be encrypted with one key while another table or cell could be encrypted with a separate key. Both keys are therefore encrypted by the Database Master Key. These keys can fall into the following types:

Asymmetric Keys are the combination of a private key and a public key. The plain text is encrypted using the public key, which is distributed to others, and is decrypted by a corresponding private key which is very limited in its distribution. An Asymmetric Key is created using the CREATE ASYMMETRIC KEY command.

A Symmetric Key is a single key that is used to encrypt and decrypt data. A Symmetric Key can only decrypt the data that was encrypted by itself. As previously noted the Service Master Key and Database Master Key are considered this type of key. A Symmetric Key is created using the CREATE SYMMETRIC KEY command.

Certificates are private or public keys that are digitally associated with an individual or device. The use of a Certificate is very similar to Asymmetric Key. These can be created externally from SQL Server and can offer expiration management. To create a Certificate within SQL Server you will use the CREATE CERTIFICATE command.

When a database is backed up through the standard means, the keys that are applied to the instance and subsequent catalogs are not included. Each key must be individually backed up using the BACKUP SERVICE MASTER KEY, BACKUP MASTER KEY or BACKUP CERTIFICATE commands. A best practice is not to store these key backups on the same device as the database backup in the event that the device is compromised.

While a mile of text could be used to provide more details regarding keys, this cursory review should provide the novice cryptographer with some basic information that will help begin to lift the fog in the implementation of this valuable feature of SQL Server.

And so it came to PASS...

Tony Davis — Tue, 30 Sep 2008 11:10:00 GMT

On 18-21 November this year, in the great city of Seattle, PASS will be holding their 10^th annual conference. If you work with SQL Server, you should attend if you possibly can. It's an independently-organized summit, but with the hand of Microsoft firmly up its sweater. This means plenty of real-world technical sessions from community DBAs who are prepared to "tell it like it is" rather than just give the rose-tinted Microsoft view. It also ensures a healthy attendance of Microsoft developers (one for every ten attendees this year, apparently!) who you can grill for inside information on SQL Server. You can even get SQL Server 2008 certified while at the event, if you so wish to spoil the experience.

PASS, unfortunately, seem to be difficult to deal with and short on information. The PASS conference has a reputation for being poorly organized. For example, there is still only very sketchy session information on the website. Several authors and speakers I've talked to have started comparing unfavourably the experience and expense of the PASS summit with that of emerging community events such as IndyTechFest and SQLSaturday, which are free to attend and often very well-organized. Luckily, PASS has been undergoing some fairly major changes over the past year, with a new management structure, new website, a new package to help people start their own local user groups, free PASS membership, and so on. So, things may well improve, and the PASS summit remains a compelling event, as long as you know how to get the most out of it.

Set Specific Goals for improving the performance of specific parts of your current system, using knowledge you'll gain at the conference. And then go home and implement them.

Talk to People! I've met people who go to all the keynotes, diligently mark out all the sessions they want to attend and spend the week, heads down, trudging between each one. It is not enough. Talk to Microsoft SQL Server developers about specific technical issues. Talk to the tool vendors about possible alternative solutions. Most of all, talk to your fellow DBAs over lunch, in birds-of-a-feather sessions and at the bars and parties. It can help you develop a far-reaching support network.

Don't be ashamed to Party. In my experience, when you bring together, in a relaxed environment, a group of like-minded professionals, they can produce some of the best insights into the real problems DBAs face, and how to handle them. A meeting in the bar with a few fellow DBAs can be just as productive as a conference session or keynote.

Have you had dealings with PASS as a conference attendee, volunteer or speaker? Or in setting up a local users group? What they do well? Where they need to work harder? Have you got any advice on how to get the most benefit from the conference? Please let us know. As usual, the best comment on the blog will win a $50 Amazon voucher.

Cheers,

Tony.

Simply different: ingenious!

red@work — Tue, 30 Sep 2008 11:00:00 GMT

Having seen Chris’ post yesterday, I thought I’d bring another perspective to the table. I joined Red Gate a few weeks ago, and from all I’d learned before joining I thought it was a cracking company, doing things in a different way. In particular I think it’s taking the consumer-led approach to products and business and applying it in a space which has traditionally been addressed in a very different manner.

So, people in organisations are being sold to as if they were consumers, with products which have a quality, an intent, and hopefully a pervasiveness which mirrors them as consumers in the working place. That interests me an awful lot.

I’ve spent a lot of time with more ‘enterprise-y’, more classical ways of offering, making and developing products (both hardware and software), almost always in leading high-tech early markets. But this is the first organisation which I’ve been exposed to which is approaching those problems in a very different way, and that interests me and attracted me - and the more I’ve learned, the more that instinct has become validated.

So I’m intrigued by that approach, I’m intrigued by the fact that it’s different, I’m intrigued by the fact that the image and execution of the company is distinct, and I’m intrigued by the mantra of ‘simplicity’.

If I were to describe the culture of Red Gate, I think two words would spring to mind. One of them is ‘Different’, and the other is ‘Friendly’, or ‘People-Oriented’. I’ve never before worked for a company where, after accepting the job offer, I was sent a helium balloon as part of a package of goodies as a way to say ‘Hi’. I think that’s very interesting, because I think at one level it says “people are people”, and the organisation promotes its ‘people-ness’, as much as anything else.

And that principle I’ve seen in the last few weeks in spades; People have approached me, they’ve looked after me, they’ve said ‘hi’… all that stuff. It’s a very different experience from many (particularly larger) organisations, where you’re dumped at a desk, told “here’s your email, here’s your phone, and off you go”. It’s very different from that, very welcoming; but I think it’s indicative of a principle, a theme, a trend in the company which is very strong.

When I say ‘different’, I mean it in a good way. Little things are done differently and, most importantly, cleverly differently. Just to give you an example, on the website, there is a list of references. Many companies have references. Often times those references are highly contrived, very strongly marketed and they say “the product’s good, it works, it does what it says on the tin”. What I noticed of Red Gate was that the recommendations still say the products are good; but they - or rather we - say it by having lots of verbatim comments from many, many people. And my initial reaction was,

“That’s odd”

My next reaction was,

“You know what, there’s a lot of content there”

And my third reaction was,

“It’s different, and it’s different in a positive and clever way”

And now I’m inside the building I’m beginning to see what I think is an underlying theme; that:

A) It’s ok to be different and -

B) Difference could be a strength, a discriminator, an opportunity.

I’ve seen differences accepted. I’ve seen real consensus-led meetings, and that, in a sense, shows a culture which is valuing difference when applied objectively. And I suspect I will see other instances where being different has been taken to become a virtue, for very good and thinking reasons.

Another difference, and I’ve never had this before: on my first day there was an envelope on my desk with a card, signed by a bunch of members of the team, and with a picture on the card as I recognised (on about day 1-&-a-half) as caricatures of people in the company. I thought that was an extraordinarily nice thing to do, which was also very, very welcoming.

I think the thing I remember the most about my first day here was the efficiency. I could see my arrival had been planned to more than just having a desk and a chair. I was taken by the HR department into a meeting, and run through all the practical stuff, which was good because it’s a good chance to ask all those very boring questions about where everything is. But that was done very quickly.

One of the other things that impressed me was the briefing paper I was given, which was almost a mini project-planner with the sorts of things I should be doing on day1, day2, week2, week3 etc. There had been significant thought given to that plan, and work doing it, because it was for ME, not just a new person. It was personalised, it was directed, and I’ve found that very useful, because it’s given me something to hang on to and do. And I’ve never had that anywhere else. I’ve been in meetings where people have verbally said, “Here’s a bunch of things”, and I’ve been in situations where someone has said “well, the ‘project manager’ (or whoever) left, here’s his departure notes”…not the same thing at all.

Paul Galwas

Data In The Cloud

Johnm — Tue, 30 Sep 2008 02:06:00 GMT

Back in 2001, I attended a seminar in which Microsoft introduced the .NET Framework. I distinctly remember the discussion surrounding the concept of software being offered as a service via the Internet. Over the years there has been many offerings in this regard and more potential customers of this option have grown accustomed to and interested in the concept.

As the .NET Framework matured and the tools to create web applications became more powerful, the appearance of applications being offered on the Internet became more frequent. The straight-forward term of "Software as a Service" became a more visual and marketable "Computing in the Cloud" which could very well draw The Rolling Stones in again for someone's marketing campaign.

The database world has not been left out of this brave new world. There are many on-line database service offerings that are making their claim in the cloud such as Intuit, Amazon.com, IBM and Google. Most interesting to the SQL Server crowd is Microsoft's offering called SQL Server Data Services (SSDS). This product offers on-line data storage, query and manipulation. While SSDS will not offer the entire suite of features of SQL Server when it is released, there will be many features that will make it a serious contender in the current "database in the cloud" market.

The marketing information indicates that SSDS will have a "flexible data model" and further describes it as "no schemas required". With elements such as entities, containers and "flat scalar property bags" developing in the SSDS environment may require a bit of a learning curve for the Database Developer; but it is a very exciting opportunity for those willing to go off into the "wild blue yonder".

There is much more to learn about SSDS and I am looking forward to getting my hands on it. This product is not yet in Beta; but Microsoft is taking applications for participating in the Beta program. You can sign up to participate in the Beta as well as learn more about SSDS at the following link: http://www.microsoft.com/sql/dataservices/default.mspx

Keep Up-to-Date on SQL Server with These RSS Feeds

bradmcgehee — Mon, 29 Sep 2008 13:52:09 GMT

The Internet includes a huge amount of SQL Server content, but much of it is hard to find. One of the ways I try to keep up is to follow a large number SQL Server-related RSS feeds using a RSS Feed Reader.

Some of these feeds are very active, while others only add content on an irregular basis. In any event, you can find a wealth of information if you take a little time out of your day, each day, and review what is new.

Below are the RSS feeds that I follow. They are not listed in any particular order. If you know of some SQL Server-related RSS feeds that I haven't included below, please add your comment below.

SQL Server SSIS Team Blog (Microsoft)

SQL Server Policy-Based Management Blog (Microsoft)

SQL Server Reporting Services Team Blog (Microsoft)

SQL Server Security Team Blog (Microsoft)

Carpe Datum (Microsoft's Buck Woody's Blog)

Dan Jones Blog (Microsoft Lead Program Manager)

SQL Server Manageability Team Blog (Microsoft)

The Data Platform Insider (Microsoft)

SQL Server Query Processing Team (Microsoft)

Microsoft SQL Server CAT Team Best Practices

Microsoft SQL Server Development CAT Team

SQL Server Service Broker Team Blog (Microsoft)

PSS SQL Server Engineers (Microsoft)

SQL Server Engine Tips (Microsoft)

MSDN: SQL Server (Microsoft)

SQL Server Performance (Microsoft)

All Things SQL Server (Microsoft)

SQL Server Express Blog (Microsoft)

SQL Storage Engine (Microsoft)

SQL Server Engine Tips (Microsoft)

PerfGuru Performance Blog (Microsoft)

SQL Server Samples (Microsoft)

Euan Garden's Blog (Microsoft)

SQLBlog.Com (Multiple Blogs From SQL Server Experts)

Database UnderGround (Sean McCown, InfoWorld)

SQL Server Magazine Blogs

sqlCrunch Links to SQL Server Articles and Blogs

StatisticsIO Blog by Jason Massie

Bit Bucket (Greg Low)

Kalen Delaney Blog (SQL Server MVP and Author)

Tibor Karaszi Blog (SQL Server MVP)

SQLSkills Team Blog (Kimberly Tripp & Paul Randal)

Joel on Software (Joel Spolsky)

SQL-Server-Performance.Com News

SQLTeam.Com Weblogs

Simple-Talk Blogs

SQLServerCentral.Com Blogs

Brad McGehee's Blog (Focus on SQL Server)

Share this post :

New Kid on the Block

red@work — Mon, 29 Sep 2008 13:50:00 GMT

I only recently joined Red Gate, and when I found out I was being offered the job I was already pretty excited. After all, they’ve actually got awards for being a great place to work. But then, something happened which completely changed how I thought about the company. Specifically, I stopped thinking about it as a company, and started thinking about it as a collection of people. That might sound small, but to an individual it makes a world of difference to be joining a group of human beings, rather than a faceless corporation. Two weeks before I actually joined the company, I got a call from a courier service asking me when it would be convenient for them to drop off my parcel.

“Huh? What parcel?”

“It’s from ‘Red Gate Software’… I think it’s a uniform or something.”

Now, having been to the company offices for my interviews, I already knew that Red Gate was not a “matching jumpsuits” kind of place, but I HAD seen a few people walking around with branded T-shirts. Ah, a little welcoming gift – that’s nice of them.

What actually arrived on my doorstep was a huge gift wrapped box containing, amongst other things, a helium balloon, a bottle of wine and a card with a hand-written message welcoming me to the company. And a T-shirt.

Now, I realise this makes it look I’m easily bought by anyone with a helium balloon and a bottle of wine, but that’s not the point. The point is that before I’d got anywhere near my desk, my team or my work, they were already taking steps to make sure I felt welcomed, appreciated and anticipated. And it worked; when I finally came into the office, spoke to people here and saw the (second) card on my desk, I realised that they were all genuinely glad to meet me. This is a place where people are not just employed, they’re celebrated. What is interesting is that I’ve discovered that there are shelves rammed full of the stuff used to fill the goodie boxes, so they clearly aren’t that expensive or difficult to organise. Yet nobody I’ve spoken to in other companies can share a similar experience with me. If this stuff is so easy and cheap to do, why aren’t more companies taking this small step to make their new employees feel that little bit more welcomed?

I think it fundamentally boils down to the company culture as a whole. Red Gate seems to value its people as people, not just money-making cube-monkeys, and by doing so it gets the best performance it can from them. And as a culture, it’s shared by each individual, as well as the whole. I’ve had people I’ve never met stop and ask me how I’m getting on, and inviting me to go and chat to them whenever I have a question they can help with. Sure, I could probably go and do that anyway, but I’m constantly being invited to do so, and that just makes it easy for ideas to move around.

However, it’s not JUST all friendly and fluffy here - Red Gate subscribes to the ‘Deep-End’ style of learning, which is exactly as challenging as it sounds. That doesn’t mean it’s not rewarding, though – far from it. I’ve learned a huge amount about developing code, marketing and publishing, often from the same people, and I know I’ve got a huge amount more to take in. But because I’ve had to find that information out for myself, it’s infinitely more satisfying to me. Let’s just say that being thrown in at the deep end isn’t the only way to learn, but it’s a very effective way to learn.

If I’m going to sum up what it’s like to be the new recruit at Red Gate in a few words, I’d say these are the words with the most mileage:

Exciting, rewarding, challenging, relaxed, fun.

Even now, months after I got my box of freebies, whenever I speak to anyone about this place the first thing I say is

“The people are awesome!”

I know that I sound like a walking endorsement, but let’s check the facts:

A company culture completely orientated towards valuing people as people.
- Check

Working with people who are friendly and collectively motivated to achieving the best they can.
- Check

Work which challenges me, and allows me to develop.
- Check

Seriously, what’s not to like?

- Posted by Chris Massey

Can Social Networking Help Your DBA Career?

bradmcgehee — Sun, 28 Sep 2008 19:30:53 GMT

In previous blog entries, I have talked about the importance of managing your online brand. One important part of this is participation in social networking websites. If you are not yet familiar with the term, social networking refers to websites that are designed to make it easy for people to share information about themselves, and at the same time, connect with others who have similar interests.

Social network websites come in many different forms to meet the diverse needs of their members. According to Wikipedia, the 10 most popular social networking websites are:

MySpace
Facebook
Windows Live Spaces
Habbo
hi5
Orkut
Friendster
Flixster
Classmates.com
Bebo

While you may be familiar with some of these social networking websites, many of them may be new to you. This is because many of these top 10 sites are popular in countries other than the United States.

While social network websites can be fun and bring people with similar interests together, what role do they play for DBAs? While this list is not all inclusive, some of the benefits for DBAs joining social networking websites include:

Finding and connecting with DBAs locally who you didn't know exist. This may be a way to find out how many DBAs are in your local area so that you can get together and perhaps start a local user's group.
Connect with DBAs throughout the world who share common interests. For example, if you are into SSIS packages, perhaps you might want to find other DBAs who find SSIS packages as fascinating as you do.
To keep up with your DBA friends, no matter where they are.
To make it easy for people to find and connect with you. This can be very important if you are a consultant or are looking for a new job.
To promote yourself and manage your online brand. Many social network websites are in effect mini-websites you can share with others who you are and what you know.

As you might imagine, some social networking websites are more applicable to DBAs than others, especially if you want to use them for self-promotion or for business-related relationships. In a recent poll on SQLServerCentral.com, DBAs were asked what social networking websites they belong to. The top 6 results included:

LinkedIn
Facebook
MySpace
Twitter
Del.icio.us
Plaxo

As you can see, there is not a lot of overlap between the list of the top 10 most popular social networking websites on the Internet and the top 6 used by DBAs who visit SQLServerCentral.com. I think this is because most DBAs, who do participate in social network websites, choose those that are more business-oriented, not the ones that are more social-orientated.

Personally, there are three social networking websites that I use extensively, all of which happen to be in the top 6 list above. They include LinkedIn, Plaxo, and Twitter. I want to share a little about each of these website, and why I use them.

LinkedIn is a social networking website dedicated to developing professional relationships, and currently has over 25 million members. Basic membership is free, and allows you to create a profile (essentially a resume) that can be shared with the public, and also allows you to link with others who you have some direct relationship with, such as a friend or business acquaintance. LinkinIn can be used to find new clients, service providers, subject matter experts, business opportunities, connect with industry insiders, ask questions, find a new job, find a job candidate for a new position, and much more. In addition, if you use the free Xobni add-in to Microsoft Outlook, Xobni can display information from LinkedIn profiles as part of your contacts list.

I use LinkedIn because it allows you to identify new contacts from your currently existing contacts. For example, I currently have 213 direct connections in LinkedIn. This are people who I have some sort of direct relationship. But because each of my direct connections have connections of their own, I am one connection away from about 25,000 other people, and two connections away from over 2 million people. So if I want to make a contact with someone who I am not directly connected with, all I have to do is to contact one of my direct connections and ask them if they would introduce me to the person who I want to meet, and so on. This allows me access to a huge number of people with an almost infinite variety of knowledge and interests.

You can see my LinkedIn profile at:

http://www.linkedin.com/in/bradmcgehee

Plaxo

Plaxo started out as a service to help you automatically update your Outlook contacts list. For example, if a contact in my Outlook contacts list is also a Plaxo member, then if they change their contact information, then Plaxo (through an Outlook add-in) automatically update the information in my Outlook contact list. More recently, it has morphed into a business social networking website that offers many other features besides just keeping track of your contact's address and other contact information. I use Plaxo mainly for its automatic contact update feature, but I also use it for my resume (similar to LinkedIn). It is just another way that people can find me.

You can see my Plaxo profile at:

http://Brad_McGehee.myplaxo.com

Twitter

Twitter is a somewhat odd social networking website. Essentially, it is a mini-blog that allows you to enter short (up to 140 characters) entries about what you are doing. Many people use it like a public diary, sharing their daily activities. Personally, I use it to promote myself, such as when I have blogged, written a new article, or spoken at a users group. As a Twitter member, you have the ability to follow others, and others can follow you. In other words, you develop a network of people who you are interested in and want to follow, and others who want to follow you.

You can see my Twitter feed at:

http://twitter.com/bradmcgehee

Should You Join a Social Networking Website?

Is social networking something you should do as a DBA? If you aren't interested in promoting yourself or developing online contacts, or if you don't have enough time to actively engage in social networking, then not participating won't hurt your DBA career.

On the other hand, if you want to promote yourself and develop new contacts, and you have the time to participate, then do so. It's a great way to not only stay in contact with your current network of friends, it is a great way to expand that network.

If you decide to participate, the hardest part is deciding which social networking websites best meet your needs. This will depend on you, and your interests. I suggest you check out multiple social networking websites and see what they have to offer. Start with one, and if you enjoy it, then expand and join several others. Each social networking website has its own pros and cons, and the more you join and engage in, the greater your ability to manage your on-line brand.

Share this post :

SQL Server 2008 Cumulative Update Package 1 Available

bradmcgehee — Fri, 26 Sep 2008 09:53:52 GMT

It seems it was almost yesterday when SQL Server 2008 went RTM. And now, less than 60 days after 2008 was released, Microsoft has released Cumulative Update Package 1, which includes 78 hot fixes. You can see the entire list here.

As is the general rule of thumb, it is not recommended that you install hot fixes unless you know you really need them to fix a specific problem you are having. On the other hand, this requires that you take the time to review all 78 of the hot fixes to see if they are applicable to you. If one or more of the hot fixes are applicable to you, then you will probably want to install this update (after considerable testing, of course) on any SQL Server 2008 servers you have in production.

On the other hand, if you don't have any of the problems covered by the hot fixes in this update, then save your time and skip this update, and wait for the first service pack, which should be released in late 2008 or early 2009.

Share this post :

SQLPASS Has New Website

bradmcgehee — Thu, 25 Sep 2008 19:11:21 GMT

After a very long wait, SQLPASS has introduced its new website (www.sqlpass.org). If you were a previous member of PASS, you should have received an e-mail with your new username and password. If you thought you were a member, but did not receive the e-mail, you will need to register again.

The website sports a new, modern look, but on the other hand, still doesn't have a lot of content. More on this later.

One of the biggest differences between the old website, and the new website, is that there is no longer the concept of free and paid membership. Now, all memberships are free, which means that everyone who belongs to SQLPASS can access all the content on the website. This is a refreshing change from the older website, where paid membership didn't really offer you much more than the free membership did.

As I mentioned earlier, there site doesn't have much content on it yet. If you go to the Learning Center, you will see the outline of many good things to come.

And this is where you can get involved. As a member of SQLPASS, you have the opportunity to contribute content to the website. As I have mentioned in past blog entries, and in my book, "How to Become an Exceptional DBA," contributing to the greater SQL Server community is not only a great way to become recognized for your skills, but to also share your knowledge with others. I highly recommend you take this opportunity to share you knowledge with others. Besides being fun, it also provides you with great learning opportunities.

The events page on the website is especially useful. It lists the many various local and national SQL Server events that are available. This is a great way to discover events that may be happening close to where you live.

Besides the event page, there is a web page devoted to all of the local SQLPASS-affiliated chapters.

The sample above doesn't show very much, but the page lists many, many user groups from all over the world. If you aren't a member of a SQL Server users group, now is the time to join. If your area doesn't already have a user's group, start your own. SQLPASS offers a startup kit if you want to take the initiative and start a group.

This is just a small sampling of what the new SQLPASS website offers. Check it out today.

Share this post :

How to Script a Profiler Trace for Use as a SQL Trace Data Collection Set for the SQL Server 2008 Performance Data Collector

bradmcgehee — Thu, 25 Sep 2008 15:37:35 GMT

In SQL Server 2008, a new feature called the Performance Data Collector was added. It has the ability to collect SQL Server-related performance data, store it in a database called the Management Data Warehouse, and then produce various performance reports based on the collected data.

By default, the Performance Data Collector includes three different data collection sets (Disk Usage, Query Statistics, and Server Activities) that are used to collect a wide variety of SQL Server 2008 performance data. One of the features of the Performance Data Collector is that you can create your own data collection sets, which allow you to decide what kind of data you want to collect and store in the Performance Data Collector’s Management Data Warehouse. For example, if you want, you can create your own custom data collection set to collect and store Profiler trace data. Once the data has been collected and stored in the Management Data Warehouse, you can then create queries or Reporting Services reports to analyze the data you have collected.

There are two ways to create your own custom data collection set to collect and store Profiler trace data. You can either write a custom Transact-SQL script from scratch that creates the custom data collector, or you can let Profiler write the script for you. Let’s see how Profiler can do this for us.

First, ensure that the SQL Server 2008 Data Collector has been properly configured and is running properly. Next, start SQL Server 2008 Profiler and load an existing Profiler trace definition, or create a new Profiler trace definition. Keep in mind that the SQL Trace Data Collection Set will be based on your trace definition, so choose an appropriate one.

Figure 1: Export a Profiler Trace to a Performance Data Collector SQL Trace Collection Set.

The next step is to export the Profiler trace definition into a Transact-SQL file that can be executed later to create the SQL Trace Collection Set. To do this, From the SQL Server 2008 Profiler, select “File|Export|Script Trace Definition|For SQL Trace Collection Set,” and a “Save As” dialog box appears, allowing you to name the file and to save it in any folder you prefer. Once the script has been saved, it can be opened in SSMS. For example, the Transact-SQL script that is created looks similar to this (it has been truncated because of its length).

Figure 2: Profiler can create a SQL Trace Collection Set for you automatically.

Before you can run this script and create the SQL Trace Collection Set for the Performance Data Collector, you need to make two changes in the code. If you look through the generated code, you will see these place holders:

'SqlTrace Collection Set Name Here'

'SqlTrace Collection Item Name Here'

You will need to replace these place holders with descriptive names. Once the code is changed and executed, you will see the new SQL Trace Collection Set in Performance Data Collection portion of SSMS, along with the three default collection sets that come with SQL Server 2008. At this point, you can enable the new custom collection set, and it will begin collecting Profiler trace data based on the criteria you specified in the Profiler template you used as the basis for creating it. If you like, you can create as many different SQL Trace Collection Sets as you want, with each one based on a different Profiler template.

Unfortunately, there are no built-in reports available to view the Profiler data you have collected. Because of this, you will need to create your own Transact-SQL scripts, or to create Reporting Service reports, to view and analyze the Profiler trace data.

One warning about creating your own custom SQL Trace Collection Sets, and that is they can use a lot of SQL Server resources and disk space when enabled. To minimize this impact, ensure that the Profiler trace definitions that you use to create your SQL Trace Collection Sets only collect the minimum number of events and data columns you need. In addition, only enable SQL Trace Collection Sets as needed; don’t run them all the time. If this is the first time you have tried this, you might want to first test this on a test SQL Server before implementing it on production server.

Share this post :

ASP .NET: when a crash is not a crash

Brian Donahue — Wed, 24 Sep 2008 09:39:00 GMT

In the mysterious viscera of Microsoft Internet Information Server, nothing is as it seems. When applications are designed to work over the web, they must bristle with all sorts of bits designed to make the stateless stateful and many users become one inside the web server's inner sanctum.

What I am talking about are the fundamental differences in behavior between a bit of .NET code and ASP .NET code, and the processes that keep ASP .NET applications capable and highly available.

In traditional desktop applications, an unhandled exception would cause an exit with an error condition and probably log it to the application event log. If this were to happen in a web application, it would become unavailable without the end-user having the ability to restart it. For this reason, ASP .NET web applications have a number of features that allow it to recover almost seamlessly from an unhandled exception or a lack of resources on the server. Unfortunately for troubleshooting purposes, this means that your web application can exit gracefully before you even hit the problem you're trying to debug.

The Application Domain, or AppDomain, can isolate many "applications" which are actually running in the same process. If one of these applications misbehaves, it can be "unloaded" from the process, and in many cases the objects inside can be recovered and copied into a new AppDomain. The ASP .NET Worker Process, which is the process that can host one or more of these AppDomains, has many functions that can determine when to load and unload an application, and to detect whether or not the application is "healthy". In many cases, the parameters for determining application health are set by the administrator, whether he knows it or not!

An AppDomain recycle can occur for many reasons:

The application is using too much memory
The application has serviced a set number of requests
The application is taking a long time to respond
The application has remained idle for a long time

In the case of newer versions of IIS, particularly versions 6 and 7, the Worker Process Isolation model will log the reasons for an application recycle, but not for all reasons. In IIS 5, your worker process simply exits if there are no more AppDomains loaded into it. The fact that the worker process can just exit without leaving an event log entry or throwing an unhandled exception makes debugging incredibly frustrating.

I have not found a foolproof method for stopping an ASP .NET worker process from recycling, so I rely on ASP .NET 2.0 health monitoring to log any "conventional" application restarts due to a lack of memory or other environmental factors and then adjust my machine.config processModel and web.config to try to prevent the recycle while I am trying to debug the application. All that is needed is one line added to the master web.config, which you will find in the .NET Framework configuration folder (%systemroot%\microsoft.net\framework\v2.0.50727\config). Adding a rule to log every application recycle will actually allow you to see the reason why an application has shut down for a recycle.

First, look for the healthMonitoring node, then rules, and add the following:

<healthMonitoring>
...
<rules>
<add name="Application Lifetime Events Default" eventName="Application Lifetime Events" provider="EventLogProvider" profile="Default" minInstances="1" maxLimit="Infinite" minInterval="00:01:00" custom="" />
...

Once this has been saved, ASP .NET will leave behind a friendly reminder that it has recycled the application pool:

Event Type: Information

Event Source: ASP.NET 2.0.50727.0

Event Category: Web Event

Event ID: 1305

Date: 2008-09-23

Time: 13:33:19

User: N/A

Computer: XXXXXXX

Description:

Event code: 1002

Event message: Application is shutting down. Reason: Configuration changed.

In this case, I would recommend disabling any anti-virus software, as it may be modifying some of the ASP .NET application source code. There are many other reasons for an application shutdown that can usually be solved by modifying the Framework's machine.config file.