Simple-Talk

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.

Does Your Database Contain Sensitive Data?

Johnm — Wed, 24 Sep 2008 02:47:10 GMT

On the surface, the question of whether or not a database contains sensitive data may seem like a rather simple one to answer. Most people recognize that a Federal identification number or a credit card number is and should be recognized as sensitive data. While these pieces of data get a tremendous amount of attention by the media when data loss is reported there are other pieces of data that are not as easily recognized as being considered as sensitive.

The following categories of data are considered to be sensitive and should be protected:

Government Assigned Personal Identification data
This type of data includes Social Security numbers, tax identification numbers for businesses, driver license numbers and other data that the Federal, State or Local Governments have assigned to an individual or business for the purpose of identification.

Biometric data
As biometrics become utilized more often for the purpose of identification verification the importance of protecting this information becomes more critical. This type includes items such as retinal scan images, facial images, fingerprints and signatures.

Medical data
The Health Insurance Portability and Accountability Act (HIPAA) protects data in regard to medical and insurance information for patients. This includes notations in regard to conversations with your health care professional, physical and mental medical history as well as payment history of medical care. Unauthorized disclosure could result in civil and criminal penalties.

Student Education data
The Federal Educational Rights and Privacy Act (FERPA) protects data in regard to students and their educational records. This includes the student’s name, address, telephone number as well as information specifically regarding their education history. Unauthorized disclosure could affect a school’s Federal funding… not to mention compromising a student’s privacy.

Employment data
Items such as salary information, performance reviews, worker’s compensation claims, benefit information and pension plan details fall into this category. Any HR Professional will tell you that the unauthorized disclosure of such information could result in severe consequences.

Communication data
E-mail messages, telephone records and recordings, fax documents, text messages are all carriers of information that may contain data that would fall into any of these categories; Therefore, this information should be considered sensitive data.

Financial data
Financial data not only discloses information regarding an individual or business’ financial status it also often contains data that is used to gain access to assets. For example: bank account numbers, personal identification numbers and beneficiary information.

Intellectual Property data
Items that fall into this category are source code, schematics, details regarding a new product and also creative works such as images and written documents. The unauthorized disclosure of such information could destroy the competitive edge of a business or compromise the copyright claim by an author or artist.

As the DBA and Developer, we are typically the ones that implement encryption and other security measures in the database. We are often requested to provide extracts of data for use by external systems. We are also often requested to produce printed reports that present data for the use of Business Analysts to review. We are also a target for phishing or social engineering efforts to gain access to sensitive data.

Once data leaves the protected environment of the database the control of its dissemination becomes nearly impossible. The printed report could be passed around and end up in the hands of a person who will use the information for fraudulent activities. The spreadsheet that is generated by a SSIS Package or query could be stored on a laptop that is not password protected or encrypted which is stolen from the person’s automobile. The information could be attached or typed in an e-mail that was accidentally sent to a mailing list that contains hundreds of people.

While you cannot control the further disclosure of the data once it is in the requestor's possession the understanding of the data that is being stored in your database will go a long way in protecting your client's privacy, your employer's reputation and ultimately your job.

The DOS Batch File from Hell: a confession.

Phil Factor — Mon, 22 Sep 2008 11:02:00 GMT

A while back, I was chatting to an IT Support person we’d recently taken on. I discovered that he’d come from a large multinational company that I’d also once worked for.

“It was interesting work.” He said, “There was one curiosity about it, though; The PCs all ran a huge, complex, system for keeping the installations up-to-date. It was based on DOS batch files, for heavens’ sake. When the PCs were switched on, they ran a complex set of batch processes that could run installs, upgrades, or whatever was needed. It all worked fine but it was quite mad. It was very difficult to understand. I’d love to speak to the crazy guy who wrote it and ask him what possessed him to do so.”

“You’re speaking to him. Ask away, matey”.

He was struck dumb with embarrassment. He needn’t have worried; I take the word ‘mad’ as a compliment; in fact, I’m content with almost any human interaction nowadays..

“It was nearly twenty years ago I wrote that. I should explain why."

(cue 'sincerity' music)

"I was charged with coming up with a rational plan for maintaining a network of around a thousand PCs, doubling in size every year. At the time, they had MSDOS, and not much else. How could one check the configurations of the PCs, install software when required, do backups, and maintain a list of who had what software? There were far too many to do by hand, and my budget was vanishingly small.

At that time, there was nothing on the market to do the job. SMS, later called System Centre Configuration Manager, was a long way in the future.

Just to make matters difficult, the IT director had twitched himself awake at some point and decided that nobody in the IT should be allowed to do any programming. Henceforward, all programs would be written by outside companies, and we would merely ‘facilitate’ and manage the projects, write specifications, and supervise acceptance-tests: you know, that sort of thing.

Fine, but these guys from outside companies like to be paid, and I hadn’t the budget.

In your working life, you will often be asked to do impossible things. This is normal, and reflects the fact that life itself is a juggling act that we all eventually fail at.

You may, when you reach the point of being asked for the impossible, become despondent. Not I, for this is a game better than chess, it is referred to colloquially in Britain as the Game of Silly Buggers. (GSB). This is much enjoyed by managers in large companies as it keeps boredom at bay, and defeating this type of management torpidity is the source of great pleasure to those who like to get things done. It is, essentially, the intellectual game of getting around the arcane rules and regulations of large companies, the creeping sclerosis in corporate systems. At work, you generally have to do urgent and important things without being given the time, permission, or resources to do so. The best tactic when trapped in this particular Game of Silly Buggers is to first study the rule-book and exploit the loopholes in the ‘GSB’.

After having read the corporate computer manual cover-to-cover, I began to suspect a flaw in their definition of what comprised an ‘application’, and what therefore constituted ‘programming’.

I phoned the Manager in charge of ensuring compliance.

“Ah, Phil!” (Thinks: what is that fox up to now?)

“Ron, please excuse me, but I’m having some difficulty understanding paragraph 345 sub-paragraph 4: Definition of a computer application. I was hoping for your able assistance”

“I’m only too pleased to assist!” (Thinks: He’s on to some loophole, damn him) “What was the nature of your difficulty?”

“I can understand that an application consists of code, which is subject to all the normal corporate constraints. If I were to write a DOS batch file, would that be deemed to be an application?

“Yes” (Thinks: go away Phil! Hang on a mo. We’ll be swamped with applications!)”No. No, no.”(Thinks: Phew, narrow escape there).

“Excellent. As I tend to get confused, can you just write me an email to that effect?”

“Yes. Was that all?” (Thinks: Go away, Go! Go!)

“It was most satisfactory, Thanks for all your kind help.”

There is little more I need to say. If a DOS batch file was not an application than I would construct a system out of DOS batch files, and escape the ban on developing code, and the almost impossible barriers put in place by the corporate test team. The production managers cursed impotently. They couldn’t prevent it. . The whole IT management structure of the company seemed to focus on stopping the initiative, and the moment it looked like succeeding, announced that it was their doing or idea in the first place.

I discovered amazing things you can do with batch files. Even now I tremble at the potential power offered by SQL Server’s xp_cmdShell As so often in the GSB, the attempt to maintain the day to day equilibrium by preventing any initiatives, actually can lead to a paradoxical breakthrough in the ways of making things happen. I’ll admit, however, that the code looked strange to anyone brought up on Pascal or C++. Although I was proud of the system, and it stood the test of time, I shall take to my grave the memory of that awful look of reproach given me by the unfortunate programmer tasked with maintaining the code after I’d left the department. He had just experienced the start of his own battle with the fates, the Game of Silly Buggers."