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 -
Saturday, October 31, 2009
Thursday, October 22, 2009
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
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
Monday, October 19, 2009
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).
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 |
Thursday, October 15, 2009
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.
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.
Thursday, October 08, 2009
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.
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.
Monday, October 05, 2009
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.
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.
Thursday, October 01, 2009
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!
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!
Wednesday, September 30, 2009
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.
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.
Tuesday, September 08, 2009
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.
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.
Thursday, August 27, 2009
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.
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.
Wednesday, August 26, 2009
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!
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!
Wednesday, August 12, 2009
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.
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.
Friday, July 24, 2009
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.
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.
Monday, June 22, 2009
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)
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)
Monday, June 15, 2009
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.
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.
Monday, June 08, 2009
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.
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.
Monday, May 25, 2009
Memorial Day
My grandpa served in the military, many, many years ago. My father was spared the draft, because I was to be born soon, and he got a bye and didn't goto Vietnam. I don't have uncles or other close relatives that served in the military. I do have a strong desire to support and honor those that have served. I have often reflected on their continued service and protection of our way of life, and felt overwhelmed. I have a neighbor that served in Vietnam, in the motor pool He tells stories of the building next to his, where he slept, being destroyed by enemy fire. The entire building was leveled, while his stood unscathed. We call have stories that we can tell of people we know that served.
Today, as we stayed home from work, and before we went out to play on this vacation day, I logged into the internet and hit Facebook. I saw a couple posts from friends of mine, talking about another friend I went to school with. We were friends. This person, Jason George, was a year younger than me, and we hung in the same circles. After high school, he went on to Westpoint, and more and more education. I only recently reconnected with him over Facebook and we had a few interactions. It was nice to reconnect and see what life had brought him. A while after we reconnected, he was called up to serve again. He spent some time on Facebook trying to get his life here in order, before being sent out to his assignments in Iraq. In April, he arrived in Iraq, and on the 5th of May he arrived in Iraq. While doing foot patrol last week, he, along with 2 other soldiers and 25 Iraqi citizens were killed by a suicide bomber.
This I learned on the morning of Memorial Day. I honor his memory. I will always think of him when I see someone wearing shorts and a sweater. Certain things will always remind me of him, even though we had lost contact these past few years. He bettered himself, and worked hard to gain skills and knowledge. He used this in his professional life as well as his time in the military. He selflessly went over there to assist in the rebuilding of their economy. He hoped to use his skills and knowledge to make it a better place. He put on a soldiers uniform, and tried to make the world a better place. He died doing this. I will always remember him, and especially on this holiday, Memorial Day.
Read his story here.
Today, as we stayed home from work, and before we went out to play on this vacation day, I logged into the internet and hit Facebook. I saw a couple posts from friends of mine, talking about another friend I went to school with. We were friends. This person, Jason George, was a year younger than me, and we hung in the same circles. After high school, he went on to Westpoint, and more and more education. I only recently reconnected with him over Facebook and we had a few interactions. It was nice to reconnect and see what life had brought him. A while after we reconnected, he was called up to serve again. He spent some time on Facebook trying to get his life here in order, before being sent out to his assignments in Iraq. In April, he arrived in Iraq, and on the 5th of May he arrived in Iraq. While doing foot patrol last week, he, along with 2 other soldiers and 25 Iraqi citizens were killed by a suicide bomber.
This I learned on the morning of Memorial Day. I honor his memory. I will always think of him when I see someone wearing shorts and a sweater. Certain things will always remind me of him, even though we had lost contact these past few years. He bettered himself, and worked hard to gain skills and knowledge. He used this in his professional life as well as his time in the military. He selflessly went over there to assist in the rebuilding of their economy. He hoped to use his skills and knowledge to make it a better place. He put on a soldiers uniform, and tried to make the world a better place. He died doing this. I will always remember him, and especially on this holiday, Memorial Day.
Read his story here.
Friday, May 15, 2009
Releasing Database Objects : Release Management Process
All this week, I have been working on two large releases to our database topology. We have a complicated system, at least when it comes to releasing schema changes. We use a third party replication system that doesnt replicate DDL changes. So, we have to go thru some hoops to push changes out to the systems. And there are multiple systems, some being HOT, others WARM, and so on. We also have multiple environments, Prod, Stage, Integration, and so on.
When our Development group pushes changes out, they are in an Agile mode of developement, and often do not know the actual dates they want to push things out. Often, we'll have slippage of dates, and projects leapfrogging one another. Being on the Service Delivery side of the fence, across the field from the Development group, and apparantly speaking an entirely different dialect of the IT language we all speak, we end up having issues. This week was one of those. A lot of time was spent digging into the release requirements, seeing how this could best be applied to our systems, creating release plans to perform the individual steps, and actually testing these steps in a test environment that mimics our multiple server Production environment. It takes a bit of time to prep the environment, and ensure that the release is ready to be performed, then to actually perform it. Once done, reversing what we just did, and doing it again often occurs.
Needless to say, we are always trying to make the process better, cleaner, smoother, and more effective. The whole idea now is to spend this time now, before a release, and test it over and over, to ensure it succeeds on the actual release time. So far, so good. We are making good progress. The last release, was one of the larger ones we've ever done, and we had no failures or rollbacks.
The biggest problem with these releases and processes? Not the Tech. Not the database. Not the data. Its people dealing with other people. I alluded to it earlier with the IT language quip. But this seems to be the crux of any issues. And I do not stand on the fence saying that those people over there are he fault. Its human interactions and communications that is the problem.
Picking a date and time for a release seems to be an almost impossible task. We currently can pick any day but Friday. Prod releases occur after 6pm, while Stage and all other lower environments can happen any day, any time. And they often do, regardless of the time that was set and agreed upon. Things happen, times slip, often dates slip. Every other scheduled time and date subsequent to the first slippage naturally occurs.
If you have read this far, and was secretly hoping that you'd stumble upon the solution to the above issues, i fear i have to dissapoint you. I do not have them. Each time I think I do, I find that I end up introducing issues that others complain about. Often this is an unforseen side effect to my statements. What I do hope that happens is that if you have read this far, you have applied this story to your systems, and have seen discrepancies between mine and yours and have ways to improve ours. Or, maybe you actually had some ideas of how to alter a release management process and want to share.
So, lets talk, discuss and share.
When our Development group pushes changes out, they are in an Agile mode of developement, and often do not know the actual dates they want to push things out. Often, we'll have slippage of dates, and projects leapfrogging one another. Being on the Service Delivery side of the fence, across the field from the Development group, and apparantly speaking an entirely different dialect of the IT language we all speak, we end up having issues. This week was one of those. A lot of time was spent digging into the release requirements, seeing how this could best be applied to our systems, creating release plans to perform the individual steps, and actually testing these steps in a test environment that mimics our multiple server Production environment. It takes a bit of time to prep the environment, and ensure that the release is ready to be performed, then to actually perform it. Once done, reversing what we just did, and doing it again often occurs.
Needless to say, we are always trying to make the process better, cleaner, smoother, and more effective. The whole idea now is to spend this time now, before a release, and test it over and over, to ensure it succeeds on the actual release time. So far, so good. We are making good progress. The last release, was one of the larger ones we've ever done, and we had no failures or rollbacks.
The biggest problem with these releases and processes? Not the Tech. Not the database. Not the data. Its people dealing with other people. I alluded to it earlier with the IT language quip. But this seems to be the crux of any issues. And I do not stand on the fence saying that those people over there are he fault. Its human interactions and communications that is the problem.
Picking a date and time for a release seems to be an almost impossible task. We currently can pick any day but Friday. Prod releases occur after 6pm, while Stage and all other lower environments can happen any day, any time. And they often do, regardless of the time that was set and agreed upon. Things happen, times slip, often dates slip. Every other scheduled time and date subsequent to the first slippage naturally occurs.
If you have read this far, and was secretly hoping that you'd stumble upon the solution to the above issues, i fear i have to dissapoint you. I do not have them. Each time I think I do, I find that I end up introducing issues that others complain about. Often this is an unforseen side effect to my statements. What I do hope that happens is that if you have read this far, you have applied this story to your systems, and have seen discrepancies between mine and yours and have ways to improve ours. Or, maybe you actually had some ideas of how to alter a release management process and want to share.
So, lets talk, discuss and share.
Thursday, May 07, 2009
Social Networking : It does work
Today, as I looked at my RSS feed, I noticed a new tweep (@AdamMechanic) had a funny comment about twitter. He basically said that he had 'swalloed the twitter-colored pill. With reference to the Matrix, and the blue vs red colored pill scene. So, I thought, what colored pill would it be? The first color that came to my mind was my favorite color of all time. Chartreuse. This color has been a part of my life for as long as I can remember. Many things became painted that color. I would hunt it out and purchase items with it. Then it became a fashionable color, and I started seeing others using it. Many more products were available. This too, like all things, waned and I was left alone again with my favorite color.
Back to twitter, and the comment I made about this being the color of the twitter-colored pill. Back and forth a couple comments flew between me and he about this color, and i learned something new. Apparently, Chartreuse is a color halfway between yellow and green that was named because of its resemblance to the green color of one of the French liqueurs called green chartreuse, introduced in 1764. 1764? This, my favorite color for 20 plus years, a color I had tried to learn about and incorporate into my life, has been around that long and was named after a liqueur? I had no idea. I learned something new. I was amazed.
So, even this has nothing to do with SQL, or may career, it does have to do with the power of talking to people, and being open to learning new things. This technique has been applied to my career and SQL in specific. These are the reasons I blog and joined twitter. But be careful. You must do more than just your career and intended goal of twitter communication to make it work. Expand your communication with others, talk about all kinds of things. Not all the time, but often. Form that ever so slight connection with another human on a level that they can share something with you, and vice-a-versa. Repeat the process with this person and many others.
Then, someday, you tool will have in your toolbox the powerful tool that twitter and social networking can be.
As a side note, later that same morning, I had a simple question that was not SQL related. I posed it on twitter, and got 8 responses 6 people withing 15 minutes. Its like a room full of experts that I can stand in front of and ask a question. Some will respond. Others may not. But you will find that when its your turn to the in the audience, and you can answer, and you do, the answers you will soon need will flow your direction when the tables are turned.
So, join in to the conversation. Speak up. Answer questions. Ask questions. Get involved and tell your tales of how your container of knowledge has increased by your interacting with others.
Back to twitter, and the comment I made about this being the color of the twitter-colored pill. Back and forth a couple comments flew between me and he about this color, and i learned something new. Apparently, Chartreuse is a color halfway between yellow and green that was named because of its resemblance to the green color of one of the French liqueurs called green chartreuse, introduced in 1764. 1764? This, my favorite color for 20 plus years, a color I had tried to learn about and incorporate into my life, has been around that long and was named after a liqueur? I had no idea. I learned something new. I was amazed.
So, even this has nothing to do with SQL, or may career, it does have to do with the power of talking to people, and being open to learning new things. This technique has been applied to my career and SQL in specific. These are the reasons I blog and joined twitter. But be careful. You must do more than just your career and intended goal of twitter communication to make it work. Expand your communication with others, talk about all kinds of things. Not all the time, but often. Form that ever so slight connection with another human on a level that they can share something with you, and vice-a-versa. Repeat the process with this person and many others.
Then, someday, you tool will have in your toolbox the powerful tool that twitter and social networking can be.
As a side note, later that same morning, I had a simple question that was not SQL related. I posed it on twitter, and got 8 responses 6 people withing 15 minutes. Its like a room full of experts that I can stand in front of and ask a question. Some will respond. Others may not. But you will find that when its your turn to the in the audience, and you can answer, and you do, the answers you will soon need will flow your direction when the tables are turned.
So, join in to the conversation. Speak up. Answer questions. Ask questions. Get involved and tell your tales of how your container of knowledge has increased by your interacting with others.
Wednesday, May 06, 2009
GoldenGate replication issues - No More?
Before today, we have not had good luck releasing schema changes to our environment without affecting our GoldenGate replication topology adversely. All ill effects have been surmountable, but stressful, nonetheless. We have a Hot, Warm and DR set of servers that has GoldenGate sending the data from Hot to Warm and Hot to DR. Most of the data is replicated in this fashion, and its fast. We have very little latency, compared to Native Replication we have used in the past.
But doing a release has always been a scary proposition. We would turn things back on, only to have massive errors, and time spent fixing the errors. We have tested and tested and performed more tests to ensure that we have a plan of attack that will succeed.
Today was the day! It all paid off!! We now have a process that allows us to block a table, perform a schema change to it, and through appropriate steps, stop and restart replication where needed. Many steps are needed to perform this, and in this case, it was a two man job to perform the release. The bulk was done in about 14 minutes, with us taking our time, being cautious, and validating our changes. The rest of the release proceeded as a simple release should, and went off without a hitch.
This is a happy day. We have a successful release plan to update schema on live tables, being replicated with GoldenGate replication, without bring it down upon our heads. Yeah.
But doing a release has always been a scary proposition. We would turn things back on, only to have massive errors, and time spent fixing the errors. We have tested and tested and performed more tests to ensure that we have a plan of attack that will succeed.
Today was the day! It all paid off!! We now have a process that allows us to block a table, perform a schema change to it, and through appropriate steps, stop and restart replication where needed. Many steps are needed to perform this, and in this case, it was a two man job to perform the release. The bulk was done in about 14 minutes, with us taking our time, being cautious, and validating our changes. The rest of the release proceeded as a simple release should, and went off without a hitch.
This is a happy day. We have a successful release plan to update schema on live tables, being replicated with GoldenGate replication, without bring it down upon our heads. Yeah.
Subscribe to:
Posts (Atom)
