Month: February 2010

Introduction

Recently I have been thinking through the best ways to get the people I support trained to the same level in SQL. The constraints I am running into are that I do not have the time or even maybe the skill to develop a syllabus and course materials to launch my own SQL Server course. I also know that getting training dollars appropriated for large scale training is difficult even in good economic times.

The Idea

I see the need for an option that splits the difference.

There is a real un-served niche in the market for DBA delivered community training materials. The idea would be to work together as a community to create common course materials and delivery scripts that would be presented by individual DBAs to the teams they support. Everyone would get roughly the same experience while having a trainer that can speak directly to their specific questions or even look at examples using their specific data.

The First Course

The first course I see in the series would be an introduction to TSQL. I am currently reading Microsoft SQL Server 2008 T-SQL Fundamentals (PRO-Developer) by Itzik Ben-Gan to pick up pointers and it strikes me as to how teachable the text is. I feel like if I could just get people to read the book they would be trained. I think that if a few people came together and developed presentations, reading assignments and homework assignments the result would be a powerful teaching tool that any DBA could use to further their organization. The key is that this would be extremely cheap training owned by the community.

I need to pause here and add that I have never developed courseware so I am not sure of the copyright implications or any other details that go with creating courseware from someone else’s text. I picture an author being happy that books will be bought for each participant, but I am sure there is much more to it than I am thinking of.

I Need Feedback

Does this sound like a good idea?

Would you be interested in working on it?

Do you know of something like this that already exists?

Please let me know. I would have to consider no feedback at all to be strong feedback as well.

Introduction

Recently a coworker was doing some research with SQL 2008 Backup Compression on a very large database. During the testing they noticed that the backup file shrunk rather than grew.

The Facts

The database that the test was being run on was 360GB in size. The backup initially created a file that was 120GB in size. The file then shrank to 43GB by the time the backup was complete. The file only shrinks when the drive has sufficient space for a file that is 1/3 the size of the database. If there is not sufficient space the backup file will grow from 0.

The Concern

The concern that immediately came to mind is that the backup may be writing a file to the file system then performing some compression on it. Compression of the file after it is written would limit the ability to backup to anywhere but a local drive. It would also mean that the local drive would have to be optimized for read-write instead of just write.

The Reality

I recently had a chance to work with someone from Microsoft that was able to get with the right people to figure out what was going on. It turns out that this behavior is a feature meant to enhance performance.

Here is what they had to say:

The issue is that we attempt to pre-allocate the backup files so that they don’t keep getting extended during the backup process, which slows things down locally, and REALLY makes them slow (>1 order of magnitude) if your backup file is on an SMB share. 

For uncompressed backups, we have a pretty good idea of how much data we’ll be backing up.

 For compressed backups, the eventual size depends on how compressible the data is, which we can’t tell beforehand.

 So, we chose a value which is an average result in customer databases, which is 1/3 of the volume of data to be backed up.

 If the backup ends up being larger than this estimate, we’ll grow the file as needed.  If it ends up being smaller, we’ll trim the file back to the size actually used.

Conclusion

My fears of performance issues were unfounded. I was also surprised to find out that the file could grow as well as shrink, although it makes perfect sense when I think it through. The big takeaway I see is making sure that the locations I back up to have free space equal to at least 1/3 the size of the largest database being backed up there to get best possible performance. This sounds like a good excuse to write a PowerShell script.

More generally, the SQL Server community is amazing. The mere act of participating in the community means that at some point you will come into contact with someone that can answer just about any question you could have. I am in awe.

Introduction

I am frequently getting requests for a new server to do this or that. Running through the usual questions to determine scope and scale, the price can get high pretty quickly. Inevitably,  talk turns to ideas on how to cut the cost while still delivering a robust platform. Typically this is when the questions about skipping the Test, QA or Stage environments come out. The logic is usually that a production server is a must have, a Development server is good to have and anything else is gravy.

Applications running third party (off the shelf) tools can get by just fine without even a Development environment while heavily customized applications need many more environments.

My goal here is to lay out the different environments as well as what I see as a the use for each.

Local

The Local environment refers to an individual developer’s desktop. On this desktop there may be a database server, web server and application server. This is the primary Development environment where things are often built and tried out without fear of angering the rest of the project team.

Chances are that if your organization has a developer you have a Local environment.

Development

The Development environment is the place where everyone’s work starts to come together. It is the first environment that would contain any true servers. Depending on application architecture, this environment may be entirely composed of servers or in the case of client applications still include developer desktops.

Servers in the Development environment are typically given the drive layout as production to allow for restores and Testing of scripts but may only have a fraction of the number of processors, memory and spindles that a production server would have.

I like to compare the Development environment to a busy lake on a hot summer day. There are a lot of people doing their own thing, headed in their respective directions at high speed. The size of your boat (project) usually determines who has the right of way and everyone must adhere to the local customs while keeping their eyes open to prevent collisions.

Testing

The Testing environment is much more of a nice to have environment. For all intents and purposes it is a second Development environment with one important difference, change control. The Testing environment is where code is tested before turning it over to the QA department. It is also the environment where builds are rehearsed to minimize downtime during releases to the QA environment. Developers typically do not have access to change any objects in the Testing environment.

It is very important that the drive layouts between all environments match for manageability purposes. It is even more so for the Testing environment due to how often restores are done to quickly get back to a clean state before Testing the next build.

QA

The QA environment is one of the more important environments. This is the environment where the QA team does their magic. The usage pattern here tends to be low volume and slow paced, so hardware for the QA environment can be the same as that used in the Development environment.

The big thing to remember about the QA environment is that it must be as stable as possible. This is not the best environment to rehearse releases because any problems are likely to put the QA team behind schedule but, absent a test environment, it is the only place many organizations have.

Staging

The Staging environment is tied with production for being the most expensive environment. The Staging environment must be run on exactly the same equipment as production. The typical usage of Staging is automated load testing, user acceptance Testing and even training on important new features.

Without a Staging environment careful scheduling would have to be done to prevent collisions in the QA environment. Otherwise QA testers may be entering defects for behaviors caused by user acceptance or load testing activities. User acceptance testers may also report back to their peers that the changes to the application make it slower when in fact it is just the environment they are testing in.

Many organizations house their Staging and Production servers in separate locations for disaster recovery purposes. Attempting to do the same with only a QA environment would make the release process much more complicated in a disaster recovery scenario as changes would have to go directly from Development or Testing to Production.

Production

This is where the magic happens. I think everyone understands the need for a Production server so I am not going to spend a lot of time here. The point I would like to make is that the more money you spend on your production environment, the more environments you should have to back it up. There is no point to spending a small fortune on the best HA cluster you can find without having the capability to do real release management and disaster recovery or worst case: release management in disaster recovery.

Conclusion

I hope I have provided enough information to begin to ask the right questions when working with users to price out servers. Being able to properly educate users either helps them come to a realistic evaluation of their needs or a business case for 6 or 7 brand new servers that you can brag to your DBA friends about.