Wednesday, October 21, 2009

I’m A Square! SQLPass B-I-N-G-O

Here’s something fun going on at the SQLPass Conference in Seattle November 2-6:

(rules and playing cards can be found here)

I’m personally looking forward to meeting people I ‘see’ on Twitter every day. Even though I’m not eligble to play (and win PRIZES), my goal will still be to meet those I haven’t spoken face-to-face with.

Here is a list of the people playing. I’ve marked those whom I’ve met and those who I know from being online and then the remainder is anyone I haven’t ever encountered. They will definitely be at the top of my list. If you find me and need help with anyone I know already, I'll point you in the right direction!

SquareSquareUserNameMy Status
Andy LeonardAndyLeonardNeed to meet
Aaron BertrandAaronBertrandNeed to meet
Aaron NelsonSQLvariantNeed to meet
Adam MachanicAdamMachanicNeed to meet
Allen KinselsqlinsaneoI know Allen!
Andy WarrensqlAndyNeed to meet
Arlene GraywhimsqlNeed to meet
Bill FellowsbillinkcNeed to meet
Bill GrazianobillgrazianoHey! I know Bill!
Blythe MorrowblythemorrowHey! I know Blythe!
Brent OzarbrentoHey! I know Brent!
Brian KelleykbriankelleyNeed to meet
Colin StasiukBenchmarkITNeed to meet
Denny CherrymrdennyNeed to meet
Eric HumphreylotsahelpNeed to meet
Geoff HitenSQLCraftsmanNeed to meet
Grant FritcheyGFritcheyI know Grant!
Jeff RushJeffRushNeed to meet
Jeremiah PeschkapeschkajNeed to meet
Joe WebbJoeWebbI know Joe!
Ken SimmonskensimmonsNeed to meet
Kendal Van DykeSQLDBANeed to meet
Lee Anne PedersenleeannepedersenNeed to meet - my roomie!
Lori EdwardsloriedwardsNeed to meet
Mike WalshMike_WalshNeed to meet
Mike WellsSarasotaSQLIt will be a discovery
Pat WrightSQLAsylumI know Pat!
Peter SchottpaschottIt will be a discovery
Peter ShirePeter_ShireNeed to meet
Ross MistryRossMistryNeed to meet
Rushabh MehtarushabhmehtaI know Rushabh!
Steve Jonesway0utwestNeed to meet
Stuart AinsworthstuartaNeed to meet
Tim BenninghoffbugboiNeed to meet
Tim FordsqlagentmanI know this Tim!
Tim MitchellTim_MitchellNeed to meet this Tim
TJay BelttjaybeltI know TJ!
Todd McDermidTodd_McDermidNeed to meet
Tom LaRockSQLRockstarI know Tom!
Trevor BarkhouseSQLServerSleuthIt will be a discovery
Wendy Pastrickwendy_danceWell, this is me...
Wesley BrownWesBrownSQLNeed to meet
William McKnightwilliammcknightIt will be a discovery

Tuesday, October 13, 2009

SQL University: SQL Admin Part 1 – Database Files and Management

We are going to start our week off by taking a look at where the database files are stored and how to work with them.

Two Types of Files
First, there are two types of files which make up your database:
mydatabasename.mdf -- These are the data files (you may also have .ndf files)
mydatabasename.ldf – These are your transaction log files

Data files store all of the, well, data and metadata associated with your database. Things like data structures, stored procedures, views, etc.

Log files are a transactional history of activity in your database. These should not be confused with other available log files for SQL server operations, however (errors, setup, etc.)

Great – where are these files anyway? There is a lot of information out on the web about determining the architecture for your database storage. Instead of rehashing that here, I will show you where to find existing files.

Default Location
In SQL Server Management Studio (SSMS), right-click on the servername and choose “Properties”. In the left hand navigation bar, click “Database Settings”. Near the bottom, you will see “Database default locations”. There will be a listing for Data files and one for Log files. Most companies have these in separate file directories, if not separate hard drives.

This is where any new database created will store its files by default.

Specific Location
To determine where a specific set of database files is located, right-click on the name of any database, choose “Properties” and then click “Files”. You will need to scroll to the right to see the Path. The File Name will also be shown. Note: there may be more than one .mdf and/or .ldf file for your database.

Now, you can also do this without resorting to drilling through GUI screens. Just use the following command:
select * from sys.master_files

Moving Files
On occasion, there is a need to move your files to another location, or perhaps you need to make sure a database has been taken offline. Using the Sp_detach_db and ATTACH commands are a great tool to have in your arsenal.

Sp_detach_db is used to first disassociate the database from the underlying files. This does NOT delete your database, even though it will no longer show up under Databases in SSMS.

sp_detach_db [ @dbname= ] 'database_name'

Once a database is Detached, you can move the .mdf and .ldf files anywhere you need them. This is also great for copying databases.

Once you have the files in the appropriate locations, use the ATTACH command to re-attach the database:

CREATE DATABASE database_name
ON [ ,...n ]

Refer to the MSDN page if you would like to get more information about additional parameters or dealing with replicated, mirrored, or other database scenarios before Detaching.

Of course, SSMS also has these features availabe. Right-click any database and choose “Tasks” then “Detach”.

To Attach the database, right-click on the heading “Databases” and choose “Attach”. You can then browse to the file location by clicking the “Add” button. The appropriate .mdf and .ldf files should appear and then you can click “OK”.

Refreshing your list of Databases will confirm if your Attach was successful.

A Word of Caution
SQL Server comes with a set of System Databases which are used internally by SQL Server to keep track of data and processes necessary to make the engine function. Here is a short description of each for you:

If you find you need to move any System Databases (master, model, etc), Microsoft’s detailed instructions can be found here:

Go back to the SQL University home page.

Friday, October 9, 2009

How I Became a Database Administrator

Thanks to my buddy Ted for Tagging me. (See his blog here, or find him on Twitter @onpnt)

My journey to the position of DBA starts off much like those of others: I graduate from college (with my degree in Psychology) and start looking for work. As a new graduate with a piece of paper to prove it, all I have to show for experience are the odd jobs I held while in High School and College. Fortunately, for me anyway, those jobs were always in an administrative role instead of fast food. Taking my diploma and my admin/customer service skills into the workforce I found myself employed – as a ‘secretary’ as they were called back then.

Fortune smiled upon me once again, having more than half a brain, and I ended up being the Go-To Person in the office when someone couldn’t get their computers to behave the way they expected: network issues, printing, using applications, you name it and I fixed it. Evenutally, I had the good fortune to take a class on how to use Microsoft Access. As my first introduction to the word ‘database’ and learning about datastorage and querying, I was hooked! As fate would have it, my Administrative Assistant position was no longer needed and I was in search of another opportunity.

I went back to that company where I took the database class. They were hiring! For the next three years, I worked as a Software Trainer. I began teaching VBA and .net classes and had my first taste of SQL Server teaching T-SQL. As I started to talk more with my students, I realized I wanted to get out there and use the skills I had been teaching everyone else. This leads to my first Real Job™ as a developer for a booming Dot-Com and what I like to call the “Sold My Soul” years. As much work as it was, this opportunity did give me the stepping stone to becoming a DBA, though. About 4 weeks into my first full-fledged .net developer position, the DBA walked out. When management looked around the room and said, “who here knows anything about databases? Ah, yes – Wendy! You’re our new DBA” well, the rest is history.

Formal training has not existed since that first Microsoft Access class. Sometimes it reminds me of the saying “Everything I Need to Know I Learned in Kindergarten”; other times it’s a struggle to make sure I keep up and learn whatever I can whenever the opportunity strikes. Fortunately there is a wealth of information on the interenet and many wonderful resource books. However, I learn best in hands on situations. This makes conferences that much more valuable for me. While I have attended a TechEd here and there, my preferred conference is SQLPass ( It’s refreshing to be a contributing member or a professional community.

I still haven’t reached my personal professional goal, and while I can’t reveal what that is, I promise to share it when I do!