Atlassian to make big announcement!

On October 25, 2011, Atlassian is hosting a world-wide party to announce a new product launch. You can sign up to attend the Edmonton party on the Facebook event page. Come out and meet other Atlassian users in Edmonton!

There will be snacks, pool and prizes.

A long time coming

Hurrah! I’m very happy today. After studying, on and off, for years, today I finally wrote and passed the Implementation and Maintenance exam for SQL Server 2008 (70-432).

While on spring vacation, I made a plan to set a date in June, but by the time I checked availability in May, June was already booked up. I’m glad now that it was, because I certainly didn’t feel ready by the end of June. I was a lot more comfortable going into the exam today.

I prepared for the exam by:

  • reading and rereading the Microsoft Press self-study book,
  • took the 5-day classroom training on Maintaining a SQL Server Database,
  • bought the practice exam from MeasureUp, and
  • read Books Online.

There were things on the exam that I now have to look up, because I didn’t run across them in my studies (default trace files and tracking deprecated code usage).

I’m celebrating my new title Microsoft Certified Technology Specialist with a beer and some high-carb snacks. Then, onward! to the next certification, while I’m in the habit of studying regularly.

SSMS Configuration Tips

Recently my install of Management Studio decided to reset itself back to the defaults. After a bit of hunting around for the blog posts that listed some great configurations, I decided to make note of my preferred settings here, for future reference.

Open a new query page at Startup

Tools | Options |  Environment | At startup: – Select Open Object Explorer and new query

Create DMV shortcuts

Tools | Options |  Environment | Keyboard | Query shortcuts:

I add “select * from sys.dm_exec_sessions” and “select * from sys.dm_exec_requests” to Ctrl keys.

Display line numbers

Tools | Options |  Text Editor | All Languages | Display | Line Numbers

Know where you are through colour coding

Save yourself some grief by making group connections an obvious colour (bright red!) so that you don’t execute some script on a bunch of servers accidentally.

Tools | Options |  Text Editor | Editor Tab and Status Bar | Status Bar Layout and Colours | Group Connections

I usually move the status bar location to Top to make the colour even more obvious.

I also find Mladen Prajdić’s SSMS Tools Pack to be invaluable for colour-coding windows of specific servers.

More readable tabs

I find the tabbing in SSMS to be frustrating because it’s so hard to see what’s on a specific tab.

Tools | Options |  Text Editor | Editor Tab and Status Bar | Tab Text – Set all to false except Include file name.

Reduce wait times

Tools | Options | SQL Server Object Explorer | Table and View Options | Value for Select/Edit Top <n> Rows

I rarely need to see 1000 rows when I do a select on a table. It’s usually just a shortcut to the query text so I can start modifying it for other purposes.

Avoid some costly mistakes

Set up a Query Template (via SSMS Tools Pack) such as “BEGIN TRAN/ROLLBACK”.


Want more than you see here? Check out Kimberly Tripp’s Favorite SSMS Options – and some gotchas and Brent Ozar’s Fixing SQL Server Management Studio’s Tab Text. Have a favourite? Let me know in the comments.

These are simply my preferences, I’m not recommending them.

Count down

Well, I didn’t expect a full year to go by before making a new SQL blog post. My bad.

I’m looking forward to heading to Seattle in a few days for another mind-blowing SQL PASS conference. This year I will pass on the Monday workshop and instead go on the Photowalk. I didn’t get to see much of Seattle last year, but I hope to change that. The photos will likely end up on the SQLPASS-tagged Flickr photostream.

I am far better prepared to make best use of the conference this year. I have some outstanding work issues that I am pursuing solutions for and I hope I can meet someone at the conference who can advise me on some aspect of them.

I also am working on interview questions for a new position within my department. Can anyone help me with that? I’ve never been on the employer side of the interview table before.

Family Reunion Cookbook

cookbook cover

Cookbook cover

Have you received your copy of the Lapointe Family cookbook? The cookbook, with recipes contributed by family members, compiled by Eileen Scobie, edited and printed by me, was available for pickup at the 2010 family reunion, held at the farm of Marthe Pedersen.

It contains over 90 pages of wholesome, traditional family recipes such as Mixed Beet Relish (Kathleen Lapointe), Mom’s Baked Beans (The Wilsons), and Butter Tarts (Karen Karasiuk). As a bonus, there are 18 pages of Lapointe family genealogy, starting with Alfred and Emilda and stretching back 12 generations, to Jacques Guyon, born in 1520 in France.

The soft-cover book is coil-bound for easy use in the kitchen. The second edition, reprinted to fix some typos, can be printed on demand and shipped anywhere. The cost is $10 plus shipping.

Family members can also contribute recipes for reprintings for future family reunions.

Looking forward, with renewed enthusiasm

With SQL PASS Summit 2009 now over, it’s time to return to the real world. Fortunately I booked a few extra days off; it’s time needed to recover from the cold I picked up in Seattle and gather my thoughts about all of the things I learned and people I met in the past week.

The five days were a flurry of scurrying from room to room, chatting with SQL Bingo players and squares, talking to vendors and meeting as many people as I could.

Some quick learns:

  • Use Alt-Shift in SSMS to select text vertically.
  • Send SQL failsafe alerts to the receptionist, who is most likely always to be at the desk.
  • Hover over a column title in SSMS Activity Monitor to see which DMV is being used.
  • Don’t use ORDER BY unless absolutely necessary.
  • Use sp_executesql instead of EXEC for dynamic SQL.
  • Use an OUTPUT parameter instead of result set wherever possible for minimal columns.
  • Using a function in the WHERE clause will force SQL to do a scan insteadof a seek.
  • Don’t run DBCC FREEPROCCACHE in production!
  • The first use of a temporary object in a stored procedure forces a recompile of all temporary objects.
  • Add indexes to all foreign keys.

There are so many new things that I want to try out when I get back to work this week that I’m torn about what to do first! I do know that I have to start setting aside time for reading blogs. Blogs are usually a source for troubleshooting issues for me, but I need to be more proactive and learn before the problems surface.

Probably the single item of greatest value that I received last week was the revival of my passion for working with SQL Server, a passion that I hadn’t even realized that I’d lost. I can see now how I’ve let my work priorities slip, and will start to address that with the planning for next fiscal year that’s about to kick off.

I was a bit disappointed by how few people approached me wrt SQL Bingo. By Thursday, I was actively seeking people out; if I saw someone with a bingo card in hand, I asked if they had my square filled in yet. I helped two people finish off their blackouts by directing them on where to find other squares. It sounds like Bingo will be tried again next year and I hope to be a part of it, but it really needs more visibility and advertising.

A big shout-out to all those people I met at Summit (by no means a complete list): Wendy Pastrick, Blythe Morrow, Jeremiah Peschka, Jack Corbett, Kendal Van Dyke, Tim Mitchell, Ron Wildt, Tom LaRock, Wes Brown, Jason Strate, Mike Wells, Rushabh Mehta, Merrill Aldrich, Arlene Gray, Jen and Sean McCown, Stuart Ainsworth, Michelle Ufford, Todd McDermid, Tim Ford and Pinal Dave.

It was also a thrill to meet many authors, mostly of SQL Server MVP Deep Dives: Paul Nielsen, Kalen Delaney, Greg Low, Adam Machanic, Paul Randal, Kimberly Tripp, Itzik Ben-Gan, Aaron Bertrand, Louis Davidson, Bill Graziano, Kathi Kellenberger, Kevin Kline, Andy Leonard, Brad McGehee, Gail Shaw, Erland Sommarskog, Scott Stauffer, Joe Webb, Allen White, Grant Fritchey and Joe Celko.

The whole conference was such a blast that, when the URL for 2010 Summit discount pricing was tweeted on Friday, I immediately forwarded it to my boss, asking him to put it on the training list for next fiscal year. Regardless of whether or not my employer pays next time, I still plan to attend. You should too!!

PASS Summit 2009

PASS Summit 2009

A Summit newbie’s plans

Only a few more days until my first PASS Summit! Time to print out the session materials and stress over what to pack, and realize that I never got around to replacing my suitcase after the vacation two years ago when the zipper broke.

stressball

As a SQLPASS newbie, I’m rightfully paranoid that I’m going to miss out on something important, either because I don’t know about it or am paralyzed with too many choices at the same time in my schedule. I’ve never been to an event this big before. I’m arriving in Seattle on Sunday afternoon, and staying at the Sheraton, so if anyone has plans for Sunday evening, I’d love to hear about them!

I’ve put together my session list with at least two options per time frame so that I have an alternate picked out if my first choice is too full. I am giving myself permission to not go to sessions if a more valuable networking opportunity presents itself. Can anyone tell me if *all* sessions are being recorded? The DVD says technical….

Then there’s all of the extracurricular activities. I plan to attend pre- and post-conference sessions, but I’ve not yet signed up for any (dratted Canadian dollar dropped in value in the past week). I think I’ll keep my options open for now, just in case I hear of something else that piques my interest, like a photowalk? I won’t have much opportunity to see Seattle with such full days.

Sunday

Arriving at 1550, cab sharing anyone? If not, I’ll see how easy it is to make it by public transit. This is when packing light is ideal. Anyone getting together Sunday evening for a bite?

Monday

Pre-conference session (optional).
Getting together with EDMPASS veterans to get the lay of the land for the Summit.
Networking to Build Business Contacts.
Welcome Reception and Quiz Bowl.
Also SQLServerCentral party.

Tuesday

Keynote.
Birds of a Feature Lunch oh which to pick…

6-8pm Exhibitor reception.

Wednesday

Quest breakfast.
Women in Technology Lunch.

7-10pm Microsoft Gameworks at 7th and Pike.

Thursday

Keynote.
Chapter lunch.

Friday

Post-conference session (optional)
Any suggestions for what to do on a Friday night? I have to fit the Sci Fi Museum in somewhere.

Saturday

Whew! Departing 0950.

General to do, before and during

  • Put together a list of questions to ask people who know more than I do.
  • Print out session material.
  • Get a SQLServerCentral sticker from Steve Jones.
  • Pick up my FREE copy of Microsoft SQL Server 2008 Standard Edition at the Microsoft Product Pavilion (while supplies last).
  • Collect DevBucks by asking SQL Server developers technical questions (closes Wednesday at 4:30).
  • Get PASS to Prizes card stamped by participating Expo vendors.
  • Pick up Deep Dives book and get signed by as many MVPs as possible.
  • Meet people!

Networking

The last thing I want is to end up like this guy and leave the Summit despairing at lost opportunities for networking. I am ridiculously shy and find it very difficult to insert myself into conversations, and that’s a big part of the reason why I signed up to be a square in SQLBingo! What could be a better way to meet people than to have them come looking for you? And though I can’t enter the contest, I still plan on searching out the SQL experts on my cards and handing out my new business cards to everyone I meet.

My greatest concern is the lack of a portable communication device. I do have a cell phone, but it’s not one of these fancy data plan things that everyone seems to have these days, and my roaming/texting charges outside of Canada are an arm and a leg, neither of which I have to spare. My laptop weighs about 10 pounds and only has about 30 minutes of battery life, so it’s not very practical to lug outside of a hotel room. I hope to figure out a means of staying tuned in by Sunday!


 

 

I’m a Square in a SQL World

bingoAre you going to the 2009 PASS Summit in Seattle in two weeks? If so, then you really should go to the SQLServerPedia Bingo page and print out three bingo cards to bring along! Just refresh the page to change the squares. Read the rules and come ask for my code word!

This is my first time going to PASS, and I am so looking forward to meeting all of the other squares. As you can see from the list below, I’ve only met one of the other squares in person, but I’m guaranteed to meet at least Wendy, as we’re sharing a room at the Sheraton.

As one of the squares, I’m not able to enter the contest, but just meeting some of these SQL Server experts will be a prize alone. Only 9 more days until PASS!

The list of squares:

Square SquareUserName
Andy Leonard AndyLeonard
Aaron Bertrand AaronBertrand
Aaron Nelson SQLvariant
Adam Machanic AdamMachanic
Allen Kinsel sqlinsaneo
Andy Warren sqlAndy
Arlene Gray whimsql
Bill Fellows billinkc
Bill Graziano billgraziano
Blythe Morrow blythemorrow
Brent Ozar brento
Brian Kelley kbriankelley
Colin Stasiuk BenchmarkIT (hey, I know you! you’re from Edmonton)
Denny Cherry mrdenny
Eric Humphrey lotsahelp
Geoff Hiten SQLCraftsman
Grant Fritchey GFritchey
Jeff Rush JeffRush
Jeremiah Peschka peschkaj
Joe Webb JoeWebb
Ken Simmons kensimmons
Kendal Van Dyke SQLDBA
Lee Anne Pedersen leeannepedersen (that’s me!!)
Lori Edwards loriedwards
Mike Walsh Mike_Walsh
Mike Wells SarasotaSQL
Pat Wright SQLAsylum
Peter Schott paschott
Peter Shire Peter_Shire
Ross Mistry RossMistry
Rushabh Mehta rushabhmehta
Steve Jones way0utwest
Stuart Ainsworth stuarta
Tim Benninghoff bugboi
Tim Ford sqlagentman
Tim Mitchell Tim_Mitchell
TJay Belt tjaybelt
Todd McDermid Todd_McDermid
Tom LaRock SQLRockstar
Trevor Barkhouse SQLServerSleuth
Wendy Pastrick wendy_dance (this one’s a freebie, she’s my roomie)
Wesley Brown WesBrownSQL
William McKnight williammcknight

Thanks for the community

It seems that taking care of the database servers is a responsibility that has always fallen to me wherever I’ve worked. Though I’ve never formally held such a position, I consider myself to be a database administrator, because of all of the different things I’ve been asked to do, the database work has been the most enjoyable and fascinating. Working with SQL Server since version 6.5, I still don’t consider myself to be anything more than an amateur DBA, because I’ve never been able to dedicate the amount of time it takes to develop a well-rounded understanding of SQL Server. And though I’ve worked along side some very experienced database people, I’ve never had a mentor to help me on the journey.

twitter_logo_headerIt was sheer coincidence that I decided to check out the SQL PASS website on a day when new chapters were being featured, and I discovered that the Edmonton Chapter of PASS had been recently formed. I signed up for notification of the first meeting, with great hopes of getting involved in the Edmonton SQL community right from the start. I met Colin Stasiuk at that first meeting, and credit him with opening up a whole new world for me. He’s a very busy guy on Twitter and as a result my follow list exploded to include Tim Ford, Jeremiah Peschka, Thomas LaRock, Brent Ozar and many others.

If I thought I had a lot to keep up with before, it’s nothing compared to all of the information that floods out of this group.

Through unfortunate circumstances, my chances of going to SQL PASS on behalf of my employer evaporated, but I’ve made the decision to go to PASS 2009 regardless, realizing that this opportunity is simply too good to pass up. Not only will I be able to attend world-class presentations, but I will be able to establish an in-person connection to some of the many people that I’ve learned from and communicated with online.

Someday I would like to be able to give back to the community as much as what I have gained from it. So, thanks everyone, and I look forward to meeting many of you in person in November!

What’s your access level?

Too often developers and administrators alike find an easy solution to providing data access is to give users more permissions than are truly required. In the development environments I’ve worked in, developers always worked with full local and server admin rights and full database rights. This usually resulted in deployment issues with the expected response of “well, it worked fine for me.”

Your key to data

Your key to data access

Whenever possible now, I work under the principle of least privilege, giving users the minimum amount of permissions needed to complete their tasks. This extends right to the service account that runs SQL Server – it’s a Domain User account with no elevated rights on the server beyond what the SQL Server installation process conveys.

My first task after completing a new SQL Server 2000 installation would always be to remove the BUILTIN\Administrators group in SQL that gave anyone with membership in the local Administrators group full access to the SQL Server. Why in the world was it assumed that server and server application administrators were the same people? I was so pleased to not see that group in SQL Server 2008.

I found application roles in SQL Server 2000 to be frustrating. I liked the concept of granting permissions to a single role and then have users access the application through the role, but I lost all ability to keep an audit trail. The next best option was groups through Windows Authentication, but it enabled anyone with access needed for an application  to also get to the database through other means.

The improvement to SQL Server is loginless users. Users authenticate to the server instance with their own credentials, but by accessing the database, the user then impersonates the loginless user for permissions via EXECUTE AS. The server can still audit activity because the user authenticated with their own credentials, but do not have direct access to the database with those credentials.

To create a loginless user:

CREATE USER AppUser WITHOUT LOGIN
GO

To grant a group or user permission to impersonate the loginless user:

GRANT IMPERSONATE ON USER::AppUser TO [MyDomain\LeeAnne];
GO

To impersonate the loginless user and gain all permissions granted to it:

EXECUTE AS USER = 'AppUser'
GO

This feature will be invaluable with new development. It is my hope when I eventually migrate databases from SQL Server 2000 to 2008 that I can find someway of implementing loginless users for current Microsoft Access-based applications that currently use linked tables for database access.