My Goals and Theme Word for 2010

I was tagged by Lori Edwards (Blog – Twitter). I am now to produce a set of goals and a theme word.

Before moving forward, I feel I need to take this time to look back at last year’s goal list here. I can tell that a new goal needs to look back at the goal list more often. I do not recall the last time that I referenced this post. It may be the way that I deal with goals. I commit them to paper and memory, then just attempt to direct my life in that direction. Sometimes, they fall into place without my help, as I am already oriented that way. Others fall on the wayside, as they were not in the right direction or maybe not the right definition of task. Still others, I simply forgot to do, meaning I had not fully committed them to paper or memory. If I can orient my direction in the same direction as my goals, and define my goals in such a way that they are accomplishable in that direction, then viola, they tend to accomplish themselves without reference to their original documentation. However, I believe that it’s necessary to revisit them from time to time, to ensure the definition is up to snuff, the direction is proper, and both are meshing well. I will add this to my list this year.

Last Years Goals

Lets start off with a summary of what I documented last year as my goals or resolutions.

1. I want to do more with my local user group.
2. Produce presentations. 4-6 presentations for the year
3. Speak at SSWUG.
4. Perform Regional Mentor Duties.
5. Blog
6. Articles
7. MVP
8. Read
9. Topics
10. #InboxZero

Now, let’s dissect them a bit, explaining where I feel I fell in their accomplishment, or not.

1. I want to do more with my local user group.

I struggled with this this past year, as I was unable to produce interesting content myself, and unable to tap into many others for presentations themselves. We had a couple meetings that were a success. Others were not. Changes are afoot for this goal that will truly help out the local user group.

2. Produce presentations. 4-6 presentations for the year

I actually produced 8 presentations for SSWUG this last year. 2 of which were preexisting presentations. So, inadvertently, I accomplished this goal without actually thinking about it. I was headed in the right direction, and this simply took care of itself as I went there.

3. Speak at SSWUG.

I had the honor and privilege of speaking at both SSWUG conferences this last year. It was a blast, and I learned a lot myself. I hope to continue to be able to do so in the future.

4. Perform Regional Mentor Duties.

I actually stepped up a bit more on these duties this year, and really tried to spend more of my free time performing tasks to assist in this task. It felt good to give, felt good to produce. I will continue to feed from this trough.

5. Blog

I wrote a bit on my career blog, wrote more on my hobby blog, and started apersonal blog for things that didn't fit into either of the other two categories. For my career blog, I had 1 month with a single blog post. 1 month with 2 blog posts, and the rest with 3 or more, and some months as many as 7 posts. Not tons, but I felt good about them. For Christmas, I wrote an incredible amount on the personal blog, ending with this summary. I have had great comments on my ability to write, enough that keeps me going. At first, I thought that blogging meant writing about SQL topics. But I have learned this year that writing is just that, writing. I need to practice more, and get better. I feel that something is looming just out of reach of my awareness that will require this skill in my future. I will continue to strive to perfect this talent and hopefully be ready for the challenge, when and if it comes. In the mean time, I head down the dusty road that many have traversed previously, and simply write.

6. Articles

I did not write as many articles as I had hoped for. With preparations for SSWUG presentations taking up quite a bit of time, and rest time afterwards for other hobbies, I didn't write a ton. I have dedicated myself to writing, and found that I needed to write about many other things, other than SQL. I wrote a couple short stories, produced quite a bit of blogs, and have a couple articles sitting in unfinished states. So writing has occurred, I have just not delivered in the fashion I had anticipated, it’s evolved a bit.

7. MVP

I was nominated again this last year. Again, it was an honor to be nominated. Other than continuing on my course of actions, I cannot actually do anything towards this goal. I maintain my course thru life, and with the other goals I do accomplish, maybe this one will be picked up along the way. Maybe not.

8. Read

I have done this task, not as planned, but differently. I had added a lot of blogs from others, and tried to keep up. I would get distracted and frustrated at my ability to keep up. Sometime ago, I cleared out all my unread blogs, starting at #InboxZero. This was very hard to do, and took me a couple weeks to actually pull the trigger. Once done, a bit of time has passed, and I now am looking down the barrel at over 300 unread blogs already. This teaches me that I may never be able to keep up, so this task needs to be modified in its perceived accomplishment.

9. Topics

I am much more proficient with GoldenGate replication than last year. This is a technology that is specific to my company, and I have practiced with it quite a bit and become much more skilled at it. It was not what I meant when I wrote this particular goal, but along the road of life last year, this one was picked up and accomplished.

10. #InboxZero

All year long, as I imagine the future will most likely continue, I struggled with this task. This was not an accomplishable task in one or even many sittings. This is an ongoing task. Once burned into my brain, its become a hobby I attempt to accomplish on a small scale for a small period of time. The thing I have noticed is that others are doing the same thing and blog or tweet about it. If I was an influence on them, great. They are an influence on me, as we all attempt to perform this repetitive task, and incorporate it into our daily toolboxes of skills.

My goals for the coming year? Similar to last years, with some modifications.

1. Do more with Local User Group.

I have been a leader of this group for quite some time, and have been approached by another member to let him take it over. It may be a good time to step aside, let others perform tasks, and simply help out, instead of run the show. This is the direction I am leaning towards now. Give it some new blood.

2. Produce presentations.

Last year, I came up with a random number. This time, I want to leave it open, and simply keep it in the back of my head to produce as many pieces of presentation content as I can. I hope that its many.

3. Speak.

I am dropping off the goal to speak at SSWUG, and simply making it Speak. I want to simply find good venues where I can share my skills and knowledge with others, and become better at presenting. SSWUG is definitely in there, as well as local user groups. I may have the chance to speak outside of those two locations, and hope that I can.

4. Perform Regional Mentor Duties.

During the SQLPASS summit, I was inspired by our fearless community liaison Blythe Morrow (twitter) to do more. In fact, it was kind of a threat that I took seriously that she was going to pile on more work for this volunteer effort. I welcomed it, and began working. I intend to do more here as time permits.

5. Blog (write)

I am modifying this one to include writing. That’s the goal of my blogging, to practice writing, and hone that skill. Someday, maybe I could write a book? In the meantime, I will write as much as I can about as much as I can. I have been inspired by a lot of SQL folks, as well as others. I have always been an avid reader, more so in my youth. I grew up reading Piers Anthony, Stephen King, Dean Koontz, C. S. Lewis, Robert Ludlum, John Saul, David Eddings, Margaret Weiss, Tracy Hickman, Orson Scott Card, to mention a few. I love to read lyrical prose, in all its forms. Paint me a picture with your words, and I can almost enter your extended reality and see it as you see it. I want to get better at this skill. The SQL folks that have inspired me are Steve Jones (blog|blog|blog|twitter), Grant Fritchey (blog|twitter), Paul Randal (blog|twitter), Brad McGehee (blog|twitter), to name a few.

6. Articles

This goal will be dropped, and added into the above goal of simply writing. Writing an article should be as important a task as writing a blog post, documenting a process for my employer, penning a witty tweet. It’s all words, and my goal is to have the same result with them all. Paint a picture of a reality that I see, so that you can exist in that realm for a period of time of your choosing.

7. MVP

I'm dropping this task from the actual list this year. My direction is such that if this occurs, great. If not, I am not to be detoured from continuing on this path.

8. Read

This one will receive a modification. I will continue to read, as life permits, but not feel like I have to read it all. I will continue to seek out those posts that have relevance to my skills and interests. I will seek out those posts from folks that simply inspire me to be better. I will try out other posts that are suggested and can be added to my toolbox. But I will not feel the crushing guilt of not reading everything that everyone writes as soon as its revealed. I simply cannot read that much.

9. Topics

As the year progresses, I hope to add to this with actual topics. Let’s say 2-3 new items need to be picked up, learned, and somehow incorporated into my life. More on this as time marches along this year.

10. #InboxZero

As this is an ongoing task, and has become part of my habits, I will not continue to have this as a separate task. I believe then that it has been accomplished in a sense, as it has become part of me, and no longer needs to be listed out separately.

The last statement I made is indicative of how I perceive goals or resolutions. They should be added to your list such that they become part of your life, and no longer need to exist in a list that you can check off. They become part of who you are. This frees up your task list to accept new and exciting items yet in your life to be accomplished, while continuing to accomplish the newly incorporated habits from last year’s list.

Themeword

So, thats all fine and dandy with the goals and explanations and wild wordage to explain my quirks, but what about a themeword? That one is a bit harder. As I write this, I have had quite a few words floating around my grey matter (which is a great short story by Stephen King, you should read it someday). Let me list them out here.

• NoDrama
• Relax
• Prioritize
• ReadMore
• Play
• WorkEfficiently

Somehow, I don't think that I can apply a single themeword to my next year. Along with the goals i have listed, along with the general direction I want to head, there are many themewords that I will be working on during the year. I take life as the opportunity to better ourselves, learn as much as we can, serve our fellow man, and ultimately perfect myself as much as I can. Others will help along the way, but I gotta do the brunt of the work, as I have been doing since this realization hit me at some time in my younger years. Unfortunately there will never be a time that I can retire from these tasks. So, its business as usual, working on perfecting myself, and enjoying life along the way with all its quirks.

Tagging

I wish to tag the following folks.

Pat Wright (blog|twitter)

Joe Webb (blog|twitter)

My Christmas 2009 story

This has nothing to do with SQL Server, my job or even my career, except that it has to do with writing. From time to time, i try to write something different, to see if i can. I want to share this here with you folks, because you are part of my personal life, just like my work bleeds into my personal life from time to time. So, I hope you will indulge me with this little deviation, and I hope that you enjoy the story.

My Christmas 2009 story.

funny thing happened while trying to order

Someone in the QA group encountered an issue with a process they were testing. As they dug into the error, they found that a single stored procedure was erroring. The reason they are testing is on a new instance of our core code and databases, running against SQL 2005. This is exciting to us, as we have pieces still running against SQL 2000. At first, the question is how this piece of code made it into the system, not working. Investigation was done by some members, and questions were raised. After a small amount of detective work, we realized that the code in SQL2005 was identical to SQL2000. But it didn't work in 2005. Armed with this realization, we were able to quickly see the query and the problem. It turns out that the original developers had aliased 1 of the fields. The select statement was returning a handful of fields, 2 of which were ID fields, but from different tables. To differentiate these, the second ID field had been aliased. It was no longer called ID, but was FilterConfigID. This is not the issue, but the start of the issue. Everywhere else in the statement, the table was referenced by name, not by alias. So Table.Field was the naming standard for this select statement. Once the developers got to the Order By statement, they simply followed suit, and called the field they were ordering by Table.Alias, using the single aliased field from the select statement. This worked in SQL2000. But ceased working in SQL20005. The query processor was parsing this, realizing that the fieldname (aliased from ID to ConfigFilterID) didnt exist in the table, and returned an error.
This was a fun little exercise in naming conventions, old code, differences in versions and so on. It was a welcome distraction from the current issue i was dealing with, and i believe all DB folks that were involved with is, on multiple fronts, had a fun few minutes digging into this, realizing the issue, and coming up with a solution.

Wanna see some of my favorite GoldenGate scripts?

We use GoldenGate for replication from our hot server, to our warm, search and dr. Lots of data is replicated, using many different GoldenGate processes. From time to time, we will stop a process, halting replicatino for a period. After starting it back up, replication will be latent for a period. Given time, it will catch up. But in the meantime, I get worried about the status. I also need to know if processes are running or stopped.

To aid in this purpose, I have created some standard sql scripts that will provide me the data needed.

To see which items are latent, we can check the ACONPROCSB table within the DirectorDB, and produce latency times for each item that is 3 minutes latent.

--Get Latent > 3:00
SELECT
SUBSTRING(LASTCHECKPOINTLAG, 1, 2) as [hours],
SUBSTRING(LASTCHECKPOINTLAG, 4, 2) as [minutes],
SUBSTRING(LASTCHECKPOINTLAG, 7, 2) as [seconds],
CommonName as [ServerName],
ProcessName,
ProcessType,
LastStatus,
LASTCHECKPOINTLAG
FROM DirectorDB.GGS_DIRECTOR.ACONPROCSB WITH(NOLOCK)
where LastCheckPointLag > '00:03:00'
order by LastCheckPointLag desc, [ServerName], ProcessType, ProcessName


To see which items are not running, we can check the ACONPROCSB table within the DirectorDB, and check the LastStatus value of each process.

--Get Non-Running
SELECT
CommonName as [ServerName],
ProcessName,
ProcessType,
LastStatus
FROM DirectorDB.GGS_DIRECTOR.ACONPROCSB WITH(NOLOCK)
where LastStatus <> 'Running'
and ProcessName not like 'R1T2T%'
order by LastCheckPointLag desc, [ServerName], ProcessType, ProcessName


I have as many of these scripts precoded to the appropriate director database, with the appropriate other 'where' clause values. I can quickly open up these scripts from within a project and execute them where they need to be executed, and tell the status of the systems. The results of this query are from the Director database, and it does contain a bit of latency itself, but its a quick and dirty solution to tell me status. Armed with this knowledge, I can easily tell where we stand.

I have used these queries to ease my mind on many many occasions. setting them up, testing them, and having them handy has helped me tremendously.

SQLPASS 2009 Schedule

As I have been thinking and planning, and hoping to capture all the various events I will be attending during the SQL PASS Summit in Seattle starting soon.
This is mainly for me, so I can reference it when i get confused or lost or sidetracked. It will also serve as a cheatsheet for those playing Twitter Bingo to more easily find me.

Before the whole Twitter Bingo thing took off, I tried to come up with my own similar idea. I fell short. But what I did do was purchase a few items to give away to the first 3 folks that find me that I do not know currently. I thought long and hard about what to give away. Something of value to the recipient, yet indicative of the local where I reside. A bunch of different items came to mind, but i ended up purchasing some simple items to hand out to those winners.

Schedule
------------

Monday

Virtual Chapter breakout 900pm - 1100am Room 208
Chapter meeting 1100pm - 1230am Room 208
Networking Seminar 430pm - 630pm
Welcome reception 630pm - 800pm
SSC party 800pm -


Tuesday

Regional Mentor meeting 100pm - 300pm
Ambassador for PASS 130pm - 600pm
SQL Sentry Party 900pm - 1200am


Wednesday

Quest breakfast 700am - 830am
MS Appreciation 700pm - 1000pm


Thursday

Chapter Lunch 1200pm - 100pm Hall 4B
Red-Gate dinner 730pm -

SQL Query Processing doc

just a few minutes ago, i completed presenting on SQL Query Processing for SSWUG vConference.
One of the attendees was unable to download the word doc that contained the steps. I'm posting them here, so that this attendee, and any others, can grab a text version of it, and keep it for themselves


Logical query processing steps

(8) SELECT (9) DISTINCT (11) Top
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) WITH {CUBE | ROLLUP}
(7) HAVING
(10) ORDER BY

my SSWUG vConference Schedule

I got an email today detailing the schedule of SSWUG. I quickly went over there to see when I am presenting. Even though I am done with my part of the recorded presentation, I still get nervous about it being aired. Worry overtakes me that it will be good, that it wont stink, that it will be helpful. It does help to have recently heard from someone via email that they had recently watched me present some topic about auditing. It turned out to be at the last SSWUG conference, and the person relating it, had recently watched it at their office. That was a huge ego boost.
In any event, its only a couple days away. If you would like to see the schedule, click here. I present on the 21st and 22nd at 9am and 10am. Right there in the morning, right after each other, and bam, im done. I so look forward to being part of the forums and talking with folks, as the presentation is going on. I also look forward to the sure to be entertaining hijinks that will occur in some of the forums, and sponsor booths. Last time, quite the time was had by the SQL Community folks. (as a side note, i had an idea, and googled 'SQL Community' images. Keep traversing through them, and on page 7 you see me. wild. Others show up that I recognize as well).

So, here are the sessions
10/21/2009 9:00	Professional Development / Social Networking
10/21/2009 10:00	On Call Duties
10/22/2009 9:00	SQL Server Logical Query Processing
10/22/2009 10:00	Database Change Process

SSWUG vConference is coming, again!

The SSWUG Ultimate Virtual Conference starts October 21st and runs for 3 days. I had the opportunity of participating last time (April) for the first time. I thoroughly enjoyed the experience, and think that a nitch has been created and filled with a virtual conference, that still lends itself to a lot of social interaction between presenters and attendees, and even between attendees. And all this can be done from the comfort of your favorite chair and computer.

If you have not registered yet, do so. Here is a link

There are many reasons to go. Check out the website for more information.

To plagiarize the SSWUG website,
Learning, learning and more learning

Being able to watch videos of prerecorded sessions, and interact with other attendees and the presenter, from wherever you want to watch the conference, is an incredible opportunity. You can ask questions, get clarifications, even rewatch sessions over and over. There are many sessions, and they are repeated a few times, giving you ample time and opportunity to learn.

It's all about that other stuff

A couple times a year, we take our hot DB system and switch it to the warm DB system. Sometimes this warm DB system is a newer system with better hardware and the like. Its often a step up from where we are currently. We've gotten pretty good at switching from one hot system to a warm system, flipping statuses of these as we go. There are a lot of moving parts, and a lot of people involved, and a lot of prep work and practicing that goes into these switches. Its not for the faint of heart, but like most of our tasks, its not rocket science either. Just takes time, planning, and execution. The more of each we can put against these items, the better the outcome.

We just had a successful switch that we've been prepping for for weeks. Lots of dry runs occur prior to the fateful task. Today, I've been applying these same principals to another project I'm working on. Trying to envision all the possible tasks needed, creating a project plan, seeing thru the cracks, a little fortune telling, and even some guessing all were on the menu today. A Word doc was started to get the day rolling, a Visio diagram was created, and some project plans were also created. Some days, its not about the SQL. Its about the database. Its about the business. Its about, wow, all that stuff we claimed we were interested in when we interviewed for the position, but secretly only wanted to write TSQL.

I just wanted to note that it did happen, and be able to reach back in time, sometime in the future, and remember today.

I'm headed to #SQLPASS 2009

For a while, there, I was not sure if I would be going. After some discussions, some stress, some worry, I was allowed to attend. Excitement coursed through me, and I tweeted about it, and started getting excited to return. So much can be learned at PASS. If one could only attend a single day of sessions, and go home, I bet that person would feel satisfied that they had learned more than expected.

Multiply that by 3 days of intense learning, the chance to sit at the feet of contemporary authors and experts, rub shoulders with the leaders of the SQL Community, participate in learning with tons of other folks with similar experience and knowledge as you, and we come closer to what SQLPASS gives the attendee.

However, we're not done yet. You need to add in the SWAG that you can pickup from the vendors. Even in slow economy years, a couple tshirts makes you feel like a winner. My kids still play with some of the items I have brought back. However, don't just waste time getting SWAG from the vendors. Pick their brains, share with them your systems and see where they can help you. Look at as many booths as you can, and see where they may help you, or you them.

Let's not stop at the vendors. Near the vendors, you'll find experts from Microsoft eager to sit with you and hash out problems, questions, concerns and the like. Make sure that you think long and hard for some examples and ideas you can bring to this table. How often do you sit in your office wishing you had a connection to someone that could help you out with an issue you are experiencing? This is that chance. Bring to the table all the issues you can and take advantage of this opportunity.

Think we're done? No, we are just barely done discussing some of the structured events that will occur. I haven't mentioned the keynotes or any of the misc events like WIT luncheon. I have not mentioned the volunteer training, or the various parties put on by SQLPASS, SQLServerCentral, and vendors. Each of these provides yet more opportunity for knowledge to be gained. In some cases, its just for fun, but do not be naive enough to think that a party is just that. Its like the ogre once said, its an onion, with layers and layers. You must find your way through the layers and the meaning of each one.

One would think that if you crammed all this into 3 days, it would be enough. But it's not all. I'm only describing the goings on that may occur from 8 to 6. There is still time before and after for more. Its up to you to fill in this time. My first PASS, in Florida, I had no idea that I could fill this time with more networking, more knowledge, more. I went back to my room, and did nothing. I've since learned that this is not the time to be wasting either. Each evening there is something better you can be doing. Since this is a once a year event, why not maximize it? Why not fill it in with as much as possible? You'll be going home in a small while anyway, and you will regret not spending every waking minute filling your SQL bucket with as much as possible.

I booked my flight, and am leaving my home Monday morning, and will be in Seattle by 10am. I do this so that I have more time to fill my SQL bucket. I don't know what i will do with the time yet, but will fill it with something, for sure. I don't leave until late Friday evening either, well after the Summit proper has completed, giving myself more time to fill my bucket. While at the summit, I will spend time in my room, but only enough to sleep enough to be able to function the next day. And maybe a shower or two.

Others have blogged about their ideas of PASS. You should read them as well.
Jeramiah Peschka's Random thoughts
Bill Graziano's suggested sessions

Make your list, check it twice, and make sure you bring the properly sized bucket. Fill it with as much as possible. Bring the bucket home, and slowly, over the next few weeks and months, sift thru it, and absorb whatever you can. Use these things to make you work better and easier.

SSWUG October 2009

I recently had the distinct privilege and honor to return to Tucson, Arizona to record 4 sessions for the upcoming virtual conference. This is my second time, and i loved it even more than the first time. This time around, I knew what to expect, and was more at ease. I was able to prepare 3 new presentations and 1 I had had presented previously in User Groups and at work. I spent a fair amount of time preparing these presentations, and worried about how to best present these topics. Knowing that the presentations are combined with forums and the attendees get the opportunity to speak about the topics amongst themselves and the presentor, helped me try to interact more with the audience than the first time thru. I hope that I was able to accomplish this, and bring the audience more into the presentation and forums. Speaking to an audience is always, for me, a scary proposition. Someone once told me that I do it well. This was a long time ago, I was young, and it was at church. I believed them, and have tried to improve this ability over time. I do enjoy it, and its such a learning experience for me, that I am selfish in pursuing it.
The presentations I chose were, SQL Query Processing, On Call Duties, Release Process and Professional Development and Social Networking. The first presentation is a book report about chapter 1 of Itzik Ben Gan's book on SQL Server 2005. Each time i present this, I remember all the things I learned and forgot and relearned. On Call Duties brings to light the tasks we should think about and prepare for if this task lands in our lap. There are many things that can be prepared and practiced to make yourself more ready to perform these duties. Release Processes have been around in each company I've been at. Each company dealt with these in different ways, and I hope to share these ideas with you thru this presentation. The last topic is near and dear to our hearts as we have all embarked on this new fangled social networking fad, but i attempt to show that this is just a new way to do an old trick.

There is a code that can be used to sign up for the vConference that will get you a discount. If you have not attended this conference before, you should really check it out. Being able to sit at your own desk, and participate in a well organized conference is a great boon to your career development. Being able to join in on the forums with other attendees as well as the presentor allows you the chance to interact with folks as well as you do in a physical conference. There are many sessions available, and unlinke a physical conference, most are re-presented if you could not make an earlier session. Check it out. Learn, interact, grow!

Security Comparison

We have a hot system, and a warm system. Replication keeps the data in sync. But at some point in the not too distant past, we had to do some work to create this warm system. We commission hardware to be purchased, configured and setup for us to create the databases needed. Besides the databases, we need jobs, dts packages, logins, and so on. When we create these other systems, our offline systems, we need to match it up to the existing hot system. Copying databases and restoring them is fairly easy. Database users are restored, but may be orphaned from logins. Jobs may not exist, dts' may not exist. All these pieces need to exist, and match the hot system.

One by one, we create the bits and pieces needed, then do comparisons to ensure all pieces are in their appropriate locations. We work closely with QA to compare our systems. Tasks are created to cover each type of comparison needed.

For DTS packages, we will extract each dts package from the two systems, dump them to file, and bring them down to another system. Once in another non productino system, we can restore them and compare them. I use a Red-Gate tool to compare them, and do a lot of manual checking of the comparison. Ensuring that each DTS has the appropriate paths, connections, and so on.

For Jobs, I use a handy SQL script that grabs several important fields, and performs a checksum on those combined values. This checksum is compared to the matching job on the other side. This will show off immediate discrepancies. Another way to compare these is to script them all out, from each system, and do a file based comparison. Using Beyond compare, I can compare the entire folders together, and look at differences.

For Security, I have struggled with db users permissions comparisons and login comparisons. I have a handy script that can extract both these into a lot of data columns. Manually comparing these can be cumbersome and possibly error prone. I typically dump out the data to excel, to store for historical purposes, and do manually comparisons. But yesterday and today, I finally created some tables and scripts that would let me more easily compare the data via SQL. I import the excel document into tables, then one by one database, dump side 1 into a table, and side 2 into a table. These are then compared, and any discrepancies are revealed. I was happy to finally take the time to create this as it came in handy the next couple comparisons I needed to perform.

I have been doing a write up of the details of the 'how to' steps for these comparisons. I hope to publish this at some point. The document has come in handy for myself each time i have had to perform these comparisons, as well as others I work with. These comparisons occur twice annually, as we perform db switches from hot to warm, and when we introduce new systems to the environment. Having these things documented, how we performed them in the past, has been extremely helpful.

I just wanted to write these thoughts down and share them with you.

backups, yeah yeah, we've done that before

So, it was the end of the week, and the beginning of the weekend. I was attempting to do one of the loneliest tasks a DBA does. Backups. I have done these tons of times, and been successful in my sleep. This is a task we all learn, and worry over, and mess up on occasion. Such a simple thing, but ever present.

My thoughts here today are to remind you to simply take your time, plan ahead, and execute successfully. That can be a generalized statement for most anything, but in this case, it holds particular interest to me for backups.

Like I said, it was the end of the week. I was not thinking straight, and wanted to get this simple task done, so I could continue with non-SQL life. I had to restore a series of backups to a test machine. I should have this scripted out somewhere, but didn't. In a pinch, I had to rely on my own grey matter to perform the task adequately. I stole 1 sample that I did have laying around, and attempted to apply it to all 12 different databases. I quickly copied and pasted the sample, and renamed the dbs, the filenames, the internal names. Some of the internal filenames are DB_1.mdf, while others are DB_data.mdf. So copy and paste failed me, and just produced a bunch of TSQL that looked like it should work, yet did not. So, I had to run a bunch of Restore FILELISTONLY commands to ensure that I had the right quantity of internal files, as well as names. This simply took time that I didn't have.

Back to the filenames. I did this quickly, without thinking. I ended up copying the wrong path. The restoration location ended up being c:\mssql\backups, not the typical c:\mssql\data. This wasnt noticed until I had done about 5 databases restored. So, when I was done, I went back down the list and restored them again. More time.

Along the way, I missed a couple of the 12 databases, and had to go back to square
one, and generate the TSQL to restore them, with the correct filenames, and paths.

So, in the end, a task that we all believe we can do in our sleep, and have done many times over in the waking hours, proved to be a thorn in my side. Not because of the nature of the task was difficult, but my hurried approach, and lack of planning caused me to spend much more time than necessary performing said task. Needless to say, I now have a script that contains all the dbs to restore. This, I have already used, to perform a restore of one of these dbs in another test environment. It has already saved me time.

Suggestion. Instead of reading updates on Facebook, or talking with coworkers, or any other task that seems more fun, I should take the time to script out these items that I take for granted, so that I'm ready in a pinch to perform the task quickly, efficiently and correctly.

a Bus drove through my neighborhood. Really! THROUGH it!

Not SQL related. But ME related.

I came home from work yesterday, and noticed the neighbors at the end of the cul-de-sac were all standing around a hole in their fence. I don't remember there being a hole in the fence... Beyond the hole, I could see bits and pieces of wood. Seems odd that they would be tearing out a small section of fence and replacing it with vinyl. So I wandered over, and met another neighbor along the way. Asking him what happened, he smilingly teased me with various options that could have occurred, slowly revealing the story as we approached the carnage. Into my neighbors back yard we walked, noticing a trail of destruction. Grass was chewed up, bits of concrete and old fence wood littered the area as we traversed the backyard. People were milling about, as if they had all become afternoon construction workers. Stuff was everywhere. Piles had started to form and I could only slightly make out what had occurred. Apparently, a bus crashed through my neighbors' yards in our cul-de-sac earlier in the afternoon. The driver had finished dropping off kids, made a wrong turn, and then, well, we don't actually know why, but went into my neighbors yard, thru their fence, down their hill, taking out their obviously offensive shed minding its business in the corner of their property. After dealing with the shed, the bus decided it hated the fence on the back of this neighbors property, and went thru it. Now it had more area to hate, and proceeded to hate a couple trees, and the corner of that property behind my neighbors. Was the hating bus done? No! It saw another fence that it hated, and killed it too. There, it stopped hating, and sat on its haunches, waiting to be pulled out of its newly acquired parking spot. The driver exited and entered the bus a number of times, and no one knows yet why this bus or its driver apparently hated all these fences.


Here's the local news report.

No children were on the bus. No children were playing in any of the 3 yards where the hating bus traversed its seeds of destruction. No one was hurt. Just stuff.

We were blessed.

Who owns my database?

We often get database generated by a TestLab group, or QA group. They can have VMs cranked out with cookie cutter databases on them. We will get these dbs, apply pieces, and hand them on to the next group. This is a process that one becomes accustomed too, and one ends up not noticing the small things, like who owns the database.
So, we often get to verify that the correct owner in fact owns it. However, its usually a quick task that forces one to simply rely on the UI to perform the verification and subsequent alterations. Using the UI is not a bad thing. But i was taught early in my career to not trust the UI, but to learn what is being done underneath, dig into it, understand it, and if possible script out the actions.
I do love that management studio allows for much more scripting than previous UI tools did. I tend to use this and profiler to perform the snooping, then i massage the results into a script or job or something that i can use to repeat.

Today, i finally got tired of using the UI to verify the owner of the database. I have previously used the wonderful proc sp_changedbowner to alter the ownership. And often, simply cheated, and assigned it to 'sa' without even checking if it was owned or not by 'sa'. I dug into some procs, traced some actions of Management Studio, and saved me a simple script that can validate the owner, and even spit out for me the subsequent change sql needed to alter owner. I'm only sharing this with the rest of you because, if you are like me, you like having these scripts handy. Maybe they will help you out. Someday, you'll remember this, look for it, and viola! save yourself precious seconds or minutes.

So, here is it.

select
sd.name as [DatabaseName],
suser_sname(sd.sid) as [Owner],
case
when suser_sname(sd.sid) <> 'sa' then
'use ' + name + ' exec sp_changedbowner ''sa'''
else ''
end as ChangeDatabaseOwner,
sd.*
from master.dbo.sysdatabases sd
where suser_sname(sd.sid) <> 'sa'


run this with or without the where clause. I like to do both. Take the results, which look like this...


use db1 exec sp_changedbowner 'sa'
use db2 exec sp_changedbowner 'sa'
use db3 exec sp_changedbowner 'sa'
use db4 exec sp_changedbowner 'sa'


and execute them. Not elegant. Not a fancy UI. But little thought needs to go into it. Quick, dirty, and works. I'm saving this into a file in my [_helpers] folder for future use. already, I've quickly altered 50+ databases on 4 db servers. Blamo!

SQL Quiz 5: SANs Mirroring

My turn to answer Chris Shaw’s questions “Do I feel I have a reliable SAN solution?” and “Describe Database Mirroring for the layman?” Thanks Grant for tagging me. More important to me than the sharing of knowledge, is the building the community through our interactions. Besides, I'm not an expert or even novice in these subjects. So be forewarned before proceeding.

SAN:
I recall in the beginning of my DBA career, a Database server was a box I could see, touch and if necessary, kick. One day, it became a smaller box that was in a backroom somewhere, in a 'rack'. With access to this special room, I could still kick it. Now, we use SANS. What the heck are these things? I don't have access to them. I can't see them. I cant even find the room where they are at. I'm definitely no longer allowed to kick them.
Now we have to go through folk with magic fingers and pixie dust to get some space for a db server. They do all kinds of things that I don't understand. But if they do it right, I get an IP address or name of a DB server, and its so much better than it used to be, with much more stuffs than I could ever hope for previously. Tons of space, and it's uptime is so much better. When something does break, its usually not the DBA's fault. Like when converting from generator power to real power, and forgetting to connect the SAN your standby topology is on at the same time. Fun stuff! But I do miss the days I could walk into a room and kick my DB server and it was all in 1 box. Mainly I liked to see it sitting there and often, kick it.



Mirroring:
Many years ago, in the beginning of our marriage, we went to a car dealer. They had a special on two cars at the time. We almost bought them both, and would have had two identical cars. If we kept them both tuned and in good working order, I could go out in the morning and take one of them. If something happened to that one, I could take the other, and not really notice the difference. My tasks would still be accomplished in the same way, and if I didn't pay close attention, I'd be none the wiser.
This is how I imagine mirroring functions. There are two dbs out there, that are identical. Something keeps them running the same. If one has an issue, we start using the other. Quite magically. You, the end user, simply gets in the DB, and drives off, returns it to its parking space, and viola, repeats when necessary.
You don't need to know that behind the curtain there are any number of processes, people and other stuffs switching things out, configuring things, setting things up, and so on. What you see is a functioning item on your side of the fence, and its available when you need it.


To sum this all up in my own words wouldn't be as much fun as realizing my conclusion is the same as Grant's. So to plagiarize Grant, "So to sum up, the answer to both questions is, I don’t know. Pretty pathetic, but honest." -Grant Fritchey

Now tagging Pat Wright and Andy Leonard.

So much to do!

A couple weeks ago, we had an incident at work, that caused some long days of work, over the weekend. Since then, its slowed down a bit, but its still busy, as we lost time dealing with an incident that wasn't planned. This is akin to taking a vacation. The work doesn't simply get done, it waits; waits for you to return. This seems to make it harder to take any time off, or enjoy time off; knowing that that creature is simply waiting for you, waiting to consume your time again, as soon as it's let lose.

That wasn't the point I was trying to make, but out it came anyway. Writing is like that for me. It's a bit cathartic and allows my brain to simply output whatever is in it. Sometimes for good; oft times, not.

So, the point, was that we need to realize when we are in a down time. By down time, I mean that our head is on straight, we can see into the future, even if its only a short distance, and we can seem to see clearly. These moments pop up at the rarest times. I've had them on a Monday afternoon. Rarely on a Friday. They tend to be elusive and are controlled by some puppet master who's schedule is random and haunting. Make a practice of watching for the signs of that moment approaching, and take advantage of it. When the moment is upon you, shirk everything, see clearly into your future, and have some tasks that you can perform while experiencing this short period of clearheadedness. It might be a good idea to keep a journal or even sticky note of tasks. When you realize you are living in the proper moment, grab your list, and start cranking out tasks.

The things that should be on your list are those things that will make your life easier. In your environment, you have hurdles that occur all the time. Something that helps you identify, monitor, measure and overcome those hurdles are highest priority. Let me make a simple suggestion. We have replication in our environment. 2 distinct types of replication. This is monitored by another group via tools like sitescope. However, for me to know the exact level of latency, can greatly improve my time and direction of action. I created a script that would report to me latency for a given threshold. However, we have 4 servers that can report latency for a few different systems. I finally made 4 scripts, hardcoded the sql to work for each environment, added them to a SSMS project, with the connections to each set. Now, I can open script 1, and execute it. It tells me whats going on. I then can repeat this for 3 other scripts. All very similar, but no need to change connections, copy 1 script to run elsewhere. Simple. This is just one idea that has helped me out, because I do not have to think about it, and can quickly produce data to measure statuses, allowing me to jump into action quicker.

The reason that this comes to light now, is that when we had an incident, I had some of these things already prepped, and was more nimble. However, there were some I still do not have, and these caused struggle. In a perfect world, we'd never struggle. We'd always have all the tools on hand to assist us.

Make your list. Keep it handy. When the winds of mood hit you, act upon them. Share these with others, and build your toolbox. When something happens, you will be ready, more ready than you have ever been. Each iteration of this will propel you forward to more successes.

Spend a month with me on my deserted island

I was tagged by Grant Fritchey for this latest viral quiz.

I welcome him to come visit my current home state anytime. I've been having so much fun at work lately and trying to play when not at work, that I just barely got to answering the quiz.


Here are the Quiz details

“So You’re On A Deserted Island With WiFi and you’re still on the clock at work. Okay, so not a very good situational exercise here, but let’s roll with it; we’ll call it a virtual deserted island. Perhaps what I should simply ask is if you had a month without any walk-up work, no projects due, no performance issues that require you to devote time from anything other than a wishlist of items you’ve been wanting to get accomplished at work but keep getting pulled away from I ask this question: what would be the top items that would get your attention?”

This is an interesting little question. What would I do if I could do whatever I want? Let's assume that I have a brain lapse and do not take any of this time to enjoy the island i'm on. Nor do I decide to spend any time with my family, as I assume that I am alone here. for some reason, all that I want to do is work. Now that we are in the correct frame of mind, let's proceed.

One of the things that I want to do more of is write. It's why I blog, but I often don't take enough time to blog/article/so on. Given time, I'd like to spend more time doing this task. However, I know I would not be able to spend the entire month doing this. Let's say that I would carve out some needed time to do this task.

When the "fit hits the shan", I have often felt unprepared with my scripts and tools to dig into the source of the problems. I have built quite an arsenal of favorite scripts that assist me in this endeavor. But I do find myself adapting them to situations on occasion. I would like to spend some time cleaning up these scripts, making some more, even making some other tools/apps/and so on. Prepping these items, documenting them, practicing with them, are all valuable ways to spend my time.

We use a third party replication tool called GoldenGate. I have dealt with this tech a lot, but not on a very deep level yet. I would love to spend quite a bit of time digging into the tool. Making some new tools that interfaced with it, to gather specific statuses, perform pre-determined tasks. There are ways that I would like to make our jobs easier when we deal with this tech that we currently do not have. Spending time with this task would ensure that I have more time in the future.

Learning new things. I would love to learn some new things that I simply have not gotten under my belt yet. Powershell. DMV's. New features of 2008. Performance monitoring. Basically, all the things I dont know yet. This list is pretty massive, so I should simply take it one item at a time, and get one under my belt as it comes along.

The list above is obviously an odd one, with distinct items that have little cross-over. I would spend a bit of time on each. In reality, I should apply percentages to each, and be anal about how much time I do spend on each, to maximize my effectiveness. Or maybe, each day, i'll simply work on whichever one I want. after all, its my list, my priorities and my desert island.


I will tag the following fine folks to continue on with this viral storyline.

Andy Leonard (@AndyLeonard)
Chris Shaw (@SQLShaw)
Pat Wright (@SqlAsylum)

What I Learned at PASS

There’s a contest going on at PASS looking for answers to “Best Thing I Learned at PASS”. I have been busy enough that I kept putting this on the back burner. But it's time to put in my 2-cents.

Since the contest has a size limit, I am partitioning the story below from the rest of this blog entry.


Contest Entry starts here
--------------------------

Let’s start at the beginning. I wanted to learn more about SQL Server, and found a local User Group, and attended.

After a few meetings, I got to know a few people, including the President. Having these contacts, and being able to ask questions of these local ‘knowledge stores’ when I needed help was priceless. If the story ended there, it would already be a success story. I had networked and learned. I was a better DBA for it.

But that’s not the end. The President told me that I should go to PASS. Since I trusted him already, I went. My family came with me to Florida, and went to DisneyWorld, as I went to PASS. I remember being alone with people all around me. But I soon noticed tons of people, just like me, wanting to learn. During the conference, I saw a few people that I knew, and recognized some names from books I had read. Soon, I realized that all the people attending and speaking, were just like me. This is the 1st great thing that I learned; there are no experts that are better than me, they are people just like me, they just learned this stuff earlier. Now they are sharing with the rest of us. I too can learn what they know. The quantity of knowledge and contacts I have gained because I attended PASS cannot be measured. And that’s why we are all here at PASS. To learn and to network.

--------------------------
Contest Entry ends here


I just wanted to say a few more words in regards to PASS summits that wouldnt fit in the contest.

I have been to many PASS summits, as many as my employer has allowed. Some times, my employer has not allowed it. This has been a source of frustration to me. This annual event is where I get to join up with others of my kind, and learn from them. This has increased my knowledge so much. I hope that i get to go this year, and in future years. Regardless of attendance, I am an avid volunteer in various pieces of the PASS puzzle. This volunteerism gives me a satisfaction that i cannot gain elsewhere, especially at work. Its a great opportunity. All should attend and learn and network and better themselves. Then return and repeat.

Turning off / on SQL Server Agent

Today, i was writing a script to detach and attach some databases. We have a set of dbs that are in 2 modes now, with 2 copies of dbs to support those modes. I needed to quickly be able to 'toggle' between them. In 1 mode, the SQL Server Agent needed to be disabled, while in the other mode, it needs to remain on.

So, I searched my local drive for a solution, some script or document. Nothing.
I turned to google. Simultaneously turned to Twitter. I ended up at SQL Server Central, and found some folks talking about it.

Ultimately, i opened up Profiler and turned a local machine db's SQL Agent on and off, while tracing the activity. After a few failed attempts, i narrowed it down to these pieces of code.


--off
EXECUTE master.dbo.xp_sqlagent_monitor 'STOP', NULL, 0, 1
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 0
reconfigure with override

--on
EXECUTE master.dbo.xp_sqlagent_monitor 'START', N'', 1
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 1
reconfigure with override

I would execute the off, while Agent was on, and then have to refresh SSMS to see the change. Sure enough it was off. I'd then execute the on code, refresh, and saw it was on. Viola! I exclaimed in a loud voice my manliness and heorism to all around me. Thats when a small voice from the next cube reminded me that the server I was to 'toggle' was a Cluster, and that the SQL Agent needed to be altered from Cluster Administrator.

Oh well. I learned something. Now I want to know if it will work with Clusters. But i do not have the means to test that thought, since the box I was toggling is live, and i shouldnt be testing on it. Someday, I'll get a better test, or find out the answer, and post it here.