My columns are goofed!!!

I have a table in production and dev environments that is not getting replicated data via GoldenGate replication properly. I have spent the last 2 weeks on and off the phone with support. We have tried this way and that way to get the data to pump from source to destination. All without fail.

2 days ago, the support tech suggested that we create a new table, and map to it on the destination, and see if we can pump the data from source to destination that way. Something had to be wrong with the original destination table. I thought this was an odd test, but in fairness, figured anything is worth trying. I will often do silly things to test that i may not think would or should work, if for no other reason than to eliminate them from the possible choices.

So, I create another table, and setup replication to map to this new table. I update a record, and viola! it worked... Wild. Ok, so what's the difference between these tables? Where are the discrepancies? I start digging in to see whats different, if anything, and find nothing. Other than the table name, i see no obvious differences. There are some constraint names different between them, but thats a SQL naming thing, and can usually be ignored. So, I drop the table, and recreate it with another script. This one from Enterprise Manager (previously created the table with a script from SSMS). Once the table was created, I altered data on the source, expecting to see it flow to the destination, and no. Was denied. Now it wasn't working.

So, I thought I had narrowed it down to something. Different tools had created 2 distinct scripts, something had to be different between these two scripts for this new table. So I dropped the table, and recreated it with the original SSMS script, and repeated my steps to update data. Nothing flowed. Now it wasn't working again.
Much to my dissapointment, and the support techs, we didnt know what had caused the 1 instance of success.

Back to the drawing board, which had now become a brick wall, upon which i had bloodied my forehead through repeated banging. None of which seemed to help, but did help me forget my problems for a moment as I furtively impacted my head to wall.

Later that day, the support tech suggested i execute a query to pull some info out about the Identity field. Guess I should explain about the oddness of this table first...

The table has a primary key of UserName, which is a varchar. It also has an ID field in the field list that is an Identity int field. Why this field is not the PK, i do not know. It is the way it is. This seemed to be causing our problems, having an identity field, not the PK, and another the PK. So, when we mapped this with replication, we needed to use KeyCols and include both these fields, to ensure we had unique constraints for GoldenGate replication to properly apply the data.

Ok, back to the query the support tech requested.


select
col.colid,
col.name,
ColumnProperty(col.id, col.name, 'IsIdentity')
from syscolumns col
where col.id = Object_Id('dbo.users')


This was going to prove to them and me, what fields were Identity fields once and for all. This has caused quite a bit of confusion during the process, as we were constantly confused at the UserName being the PK, and having an ID as an Identity.
So, I run the query on dev environment, and give them the results from both the source and destination table. I did this quickly, as I didnt see the need for it, not the interest. I knew and had repeated what the PK was, and what the Identity was, well, repeatedly. Why the support tech was so worried about this, I do not know. But I am happy he was covering all his bases. For out of this query result, we found an interesting thing.

Look back at the query, you'll notice that we return the colid field with each field. Nothing special about this in the query, in fact, it really didnt even need to be there for the purpose of finding the Identity field. But the numbering, the ordering of the results were key. Colid 1 is UserName. This we expected. Its the first column. The second column in the table is FirstName. Its colid should be [2]. It is not!!! It is [3]. From there on out, the number is consistent.

So, in our table of 16 fields, we have colid 1,3-17, missing colid 2 entirely. A quick check show that this is true in dev as well as in production. A quick check of a new table I create shows the colids in order 1-16. I attempt a new test, and sure enough, the data moves to this new table, with the correct colid numbering sequence.

It would appear that GoldenGate replication needs this colid to be in sequence properly to function properly. This one table seems to have been goofed at sometime, colids all out of whack, and this could be the cause for why replication is having a hard time moving data to it.... Yeah!

The story ends here, as this is as far as I have gotten. I actually stopped working on it to document it as it was fresh in my mind. As I find solutions to this issue, I will update this or post another entry to cover it.

Yeah, though. Up until now, we had no idea what was causing our issue. Tons of attempted tests and supposed resolutions have all proved fruitless. Along the way, I have learned a lot of valuable information about the replication system with GoldenGate. I have been able to gain experience with a lot of different aspects of it, so the entire process has been positive, even though frustrating.

Now to figure out how this happened to this table... search for a solution to it, and even investigate other tables, see if they have missing number sequences... maybe other tables are having issues that we do not know about.

Presenting for the Auditing & Compliance SIG

On April 1st at 12PM MST, that’s 11AM to those of you on the West Coast, and 2PM for those on the East Coast, I will be giving a presentation for the Auditing & Compliance SIG about SOX (Sarbanes Oxley), and the DBA perspective.

Many years ago, I was tasked with bring a system into compliance that I was DBA for. We started at scratch, and built up policy, procedure and a few systems to assist us. I later used this knowledge setting up another shot. Now I want to share this with you.


Live Meeting URL: http://www.sqlpass.org/Community/SIGs/AuditingandComplianceSIG/tabid/84/Default.aspx

Audio Dial-In Information:
Conference Dial in: (866) 379-8990
Conference code: 6489756


More information locate on the SIG page:
http://www.sqlpass.org/Events/ctl/ViewEvent/mid/521.aspx?ID=103


A few articles I've written on the subject.

http://www.sql-server-performance.com/articles/audit/Automate_Audit_Requests%20_p1.aspx

http://www.sql-server-performance.com/articles/audit/Audit_Data_Modifications_p1.aspx

http://www.sqlservercentral.com/articles/Auditing/63249/

PASS Call for Speakers Now Open

Hey, is that a bandwagon i see? Wonder if I should hop on? Yeah, it will be a fun ride. I know lots of the guys on there, and it seems they are having fun. So, here I go!!!


I rarely blog these types of infos, since others typically do before me.
But today, i was in a weird mood, and wanted to do a fun blog for a Friday, and this was all i could come up with.

Since others have already done this, i'll just list their blogs, and count them as my own... hehe

http://sqlbatman.com/2009/03/pass-call-for-speakers-now-open/

http://wiseman-wiseguy.blogspot.com/2009/03/pass-summit-2009-call-for-speakers.html

Well, it turns out, thats it. Those are the only ones so far that have blogged about it. Crud, I shoulda jumped on the bandwagon and blogged about it. Wait, thats what I'm doing, but under the disguise that I am doing a funny blog about it, and not actually doing the call for speakers... so, you decide.

I was just bored on a Support call, saw a few tweets come across my screen, and got a funny notion in my head. Its probably only funny to me, but in the end, that all that really matters. Did I make me laugh? If so, blog it. Check and mate.



So, i hit Search.twitter.com and looked for more. I did find more folks talking about it there...


sql_joker: SQL Batman - PASS Call for Speakers Now Open: Did you want to speak at the upcoming PASS 2009 Commun.. http://tinyurl.com/cf3egu (expand)
23 minutes ago from twitterfeed

AndyLeonard: RT @SQLBatman: [Blog] PASS Call for Speakers Now Open:Did you want to speak at the upcoming PASS 2009 Community Summit? http://s3nt.com/d8rb
34 minutes ago from TweetDeck

SQLBatman: [Blog] PASS Call for Speakers Now Open: Did you want to speak at the upcoming PASS 2009 Community Summit? Well, now ... http://s3nt.com/d8rb
36 minutes ago from Ping.fm

unclebiguns: New blog post: http://tinyurl.com/cv4k9e (expand) - PASS Summit 2009 Call for Speakers Open
about 2 hours ago from web

SqlAsylum: RT @sqlpass: PASS Summit 2009 Call For Speakers Opens Friday 3/20 and will close on Friday 4/10. Check out the PASS Home Page for details.
1 day ago from TweetDeck

AndyLeonard: RT @sqlpass: PASSSummit2009 Call For Speakers Opens Friday 3/20 and will close on Friday 4/10.Check out the PASS Home Page for more details.
2 days ago from TweetDeck

BetsyLM: RT @sqlpassPASS Summit 2009 Call For Speakers Opens Friday 3/20 and will close on Friday 4/10. Check out the PASS Home Page for more details
2 days ago from web

sqlpass: PASS Summit 2009 Call For Speakers Opens Friday 3/20 and will close on Friday 4/10. Check out the PASS Home Page for more details.
2 days ago from TinyTwitter

SqlAsylum: RT @sqlpass: PASS Call For Speakers opens 3/20, closing on 4/3! Get your abstracts together folks. it's going to be a great Summit!
7 days ago from TweetDeck

peschkaj: RT @sqlpass: PASS Call For Speakers opens 3/20, closing on 4/3! Get your abstracts together folks.
7 days ago from TweetDeck

statisticsio: Retweeting @sqlpass: PASS Call For Speakers opens 3/20, closing on 4/3! Get your abstracts together folks.
7 days ago from OutTwit

AndyLeonard: RT @sqlpass: PASS Call For Speakers opens 3/20, closing on 4/3! Get your abstracts together folks... it's going to be a great Summit!
7 days ago from TweetDeck

sqlpass: PASS Call For Speakers opens 3/20, closing on 4/3! Get your abstracts together folks. With your help it's going to be a great Summit!
7 days ago from TinyTwitter


So, get out and get your presentations dusted off. Submit them. Prepare yourself for an incredible experience at PASS by becoming a presenter. Its a thrill of a lifetime. Or so i have been told. I'm to scared to do it myself, and who would wanna listen to my half baked ideas anyway. Wait, i should listen to the other advice i just gave, and do it myself. I did submit an abstract a couple years ago... Maybe i still have it somewhere....

Facebook Tag

I was tagged by my wife on Facebook with another quiz of sorts.
This one just asks questions to get to know you a bit more.
I tend to like these types of quizzes and always fill them out.

http://www.facebook.com/note.php?note_id=57073598859&ref=mf

So, I wanted to stick this one on my blog, in case anyone wanted to know more about me. I also always like to try to apply these to my blog interest here, which is career, writing and SQL Server. So, in the next few days, I hope to find a way to apply this.

I do recall something similar to this that I attempted previously with SQL, and got a bunch of responses from others, of questions to ask. So, maybe i need to find that, and reinvent it, now that the thought has crossed my head again.

My Twitter Roll

Since I have seen people doing #FollowFridays via twitter, and listing out the folks that they are following, I thought i would do the same.
However, since twitter only allows 140 chars at a time, that is much to small for a list. My next thought was too pull all the tweeps that i do follow into a list, and categorize it. Why not post it to my blog? Yeah, i thought it was a great idea too. :)


SQLPASS Tweeps (tweeps i know from PASS)
-----------------------------
@sqlpass / PASS
@kekline / Kevin Kline
@SQLBoyWonder / Chuck Heinzelman
@mike_walsh
@NeilDavidson / Neil Davidson
@SQLDBA / Kendal Van Dyke
@SDyckes / Stephen Dyckes
@RachelHawley
@simon_sabin / Simon Sabin
@marcbeacom / Marc Beacom
@jmkehayias / Jonathan Kehayias
@wendy_dance / Wendy Pastrick
@sqlfool / Michelle Ufford
@billgraziano / Bill Graziano
@dbaduck / Ben Miller
@lyndarab
@Kevin3NF
@Bugboi
@blythemorrow
@sqlinsaneo
@SqlAsylum
@GFritchey / Grant Fritchey
@Peter_Shire / Peter Shire
@jessicammoss / Jessica M. Moss
@AaronBertrand / Aaron Bertrand
@debettap / Peter DeBetta
@donalddotfarmer / Donald Farmer
@peschkaj / Jeremiah Peschka
@brianknight
@sqlgirl / Mindy
@joewebb / Joe Webb
@SQLShaw
@douglasmcdowell
@stuarta / Stuart R Ainsworth
@DevSQL / Todd Robinson
@auntkathi 
@SQLBatman
@JeffWharton / Jeff Wharton
@BrentO / Brent Ozar
@TimFord
@SQLDiva / Melissa Demsak
@AndyLeonard
@rushabhmehta / Rushabh Mehta
@way0utwest / Steve Jones
@sqlagentman / Tim Ford
@statisticsio / Jason Massie
@greglow / Greg Low
@wardy / Peter Ward
@PaulNielsen
@bradmcgehee / Brad M McGehee
@drsql / Louis Davidson


SQL Tweeps
-----------------------------
@alibro / Ali Brooks
@kekline / Kevin Kline
@SQLBoyWonder / Chuck Heinzelman
@lotsahelp / Eric Humphrey
@SQLCraftsman / Geoff Hiten
@camassey / Chris Massey
@carpdeus
@Adam_Godfrey / Adam Godfrey
@mike_walsh
@rhyscampbell / Rhys Campbell
@whimsql / WhimSQL
@Tim_Mitchell / Tim Mitchell
@NeilDavidson / Neil Davidson
@BenchmarkIT / Colin Stasiuk
@SQLDBA / Kendal Van Dyke
@SDyckes / Stephen Dyckes
@RachelHawley
@simon_sabin / Simon Sabin
@h4ppyd4y / Jason Strate
@marcbeacom / Marc Beacom
@coffegrl
@jmkehayias / Jonathan Kehayias
@wendy_dance / Wendy Pastrick
@BetsyLM / Betsy Mendenhall
@mrdenny
@unclebiguns / Jack Corbett
@SQLServerLog / Neil Watkins
@SQLSocialite / Scott Stauffer
@sqlfool / Michelle Ufford
@ilkirk
@billgraziano / Bill Graziano
@dbaduck / Ben Miller
@lyndarab
@Kevin3NF
@Bugboi
@blythemorrow
@sqlinsaneo
@jcumberland / Jason Cumberland
@sstranger / Stefan Stranger
@DamonRipper / Damon Clark
@sh3n3rd / Mitsi McKee
@SqlAsylum
@GFritchey / Grant Fritchey
@Peter_Shire / Peter Shire
@RobPaller / Rob Paller
@alangham / Arthur
@jessicammoss / Jessica M. Moss
@AaronBertrand / Aaron Bertrand
@Phil_Factor
@debettap / Peter DeBetta
@wharrislv
@dfravel / Dave Fravel
@Andrew_Pfeiffer
@jmarx / Jeremy
@JamesMarsh / James Marsh
@donalddotfarmer / Donald Farmer
@peschkaj / Jeremiah Peschka
@plevexier
@brianknight
@sqlgirl / Mindy
@joewebb / Joe Webb
@SQLShaw
@swynk
@douglasmcdowell
@russjohnson / Russ Johnson
@Vendoran
@alent1234
@stuarta / Stuart R Ainsworth
@DevSQL / Todd Robinson
@auntkathi 
@MisterDBA
@SQLBatman
@ocjames / James Cornell
@wendyneu
@rob_farley / Rob Farley
@JeffWharton / Jeff Wharton
@arcanecode / Arcane Code
@BrentO / Brent Ozar
@TimFord
@SQLDiva / Melissa Demsak
@AndyLeonard
@redondoj / Jose Redondo
@rushabhmehta / Rushabh Mehta
@cfrandall / Chris Randall
@sqlpass / PASS
@way0utwest / Steve Jones
@kbriankelley / K. Brian Kelley
@sqlagentman / Tim Ford
@hallidayd / Dan Halliday
@statisticsio / Jason Massie
@mistersql
@greglow / Greg Low
@DenisGobo / Denis Gobo
@larsra / Lars Rasmussen 
@wardy / Peter Ward
@PaulNielsen
@bradmcgehee / Brad M McGehee
@drsql / Louis Davidson


SQL Groups / Companies
-----------------------------
@Simple_Talk
@SQLServerCentrl / SQLServerCentral
@redgate
@PerfSig
@sqlteam / SQLTeam.com
@sqlblog / SQLblog
@PASS_UCSSUG 
@TulsaSQL
@Quest_SQLServer


SQL Silly Tweeps
-----------------------------
@dbahole
@SQLJackBauer
@SQLAgenthottie
@SQLBatgirl
@SQLDumbass



Companies
-------------------------------------
@Rockler / Rockler Woodworking
@TillamookCheese / Tillamook Cheese


Friends / CoWorkers
------------------------------------
@Emad_ / Emad Sarraj
@marcuslyon / Marcus Lyon
@michellecoon / Michelle Coon
@christyleonard
@michaeldroz / Michael Droz
@VJensen / Valerie Jensen
@ksondere
@Kybug 
@bryanschmidty
@atarihomestar 
@christybelt


Celebrities / Well known folks
-------------------------------------
@gtdguy / David Allen
@levarburton / LeVar Burton
@BrentSpiner / Brent Spiner
@donttrythis
@roadrider57 / Robin Williams
@wilw / Wil Wheaton


Random interests of mine
-------------------------------------
@Racquetball / Knight Racquetball
@USAALL / USA-ALL
@twit / This Week in Tech
@breakroom
@UtahGov / Utah.gov
@TheRayGroup / Josh Ray
@billgates / Bill Gates


Twitter specific stuffs
-----------------------------
@tweetsql
@MrTweet / Mr Tweet



As you can tell, these will not fit in 140 chars.
I know that there are more people following me than I am following. As a rule, when people start engaging me directly via Twitter on SQL topics, or things I care about, I add them. I rarely seem to get the time to go searching for new tweeps to add. But if you come across my plate and are interesting to me, I will add you. So, as time marches on, I will be adding more folks to follow. And I hope to have a larger list in the future.

I often wish there was a way to data mine twitter, so i could grab everything someone says, and follow their conversations, pulling in what other tweeps say, and following the entire conversation.
Or just a way to catch up on what my favorite tweeps have said.

Maybe someone will write something like that....

Are You Being Treated Fairly?

Chris Shaw posted a question on his blog and tagged me:

Do you feel like you are being treated fairly at your current or past employers? The question stems from the fact that very few people today stay at a company 20 to 30 years like they did when I was growing up. Do you feel like the company feels a loyalty toward the employee or do you think that they look at you just as head count? No reason to get yourself in trouble, so you can refer back to past employers.

My first job was right out of College. It was kinda a sweatshop for developers. But we were all glad to work nearby the school we had just left. We were all happy to be in the real world. I learned a lot of things at this company. Not all of them good. One of my team leads would typically pack up everything he needed between 430 and 5pm, and sit quietly at his desk. I noticed this once, when I turned around, he was just sitting there, staring at a blank screen. His machine was off. He awaited that 5pm bell; and as soon as he heard it, he stood up, and walked out. This was a habit I learned I didn't want. Amongst all the habits that I learned there, some where good and have helped in my career, but I was fired from that place. Not exactly sure why, but I was. This let me realize that no matter how hard I worked, or how I thought I worked harder than others, I was always expendable.

This has left a rather large scar on me that has been with me for a long time now. I don't know if I will ever truly be rid of it. But from this scar, I have tried to make myself stronger because of it. (did you know that wood that is glued together with a wood glue, is often stronger than the rest of the wood surrounding it). I realize now that no matter what the employer says, it can change at a moments notice. My second job was with a small company, a family run business. My boss once stated that I had a job for life with him. 5 years into working there, this was no longer true. It happened on a Friday afternoon, and I no longer worked there, with very little explanation as to why. I recently ran into him (10 years later) and asked him why. He could not remember. We had a great chat, I got caught up on the company, his family, it was like it was when I worked for him. Cozy, friendly, and familiar. But he had fired me. Was I treated fairly by him? By the company? I did learn a lot there, and some of the skills were used elsewhere to better myself. So, remember, no matter what the employer says, it can change at a moments notice.

This is the same for me. I may be entirely loyal to one employer, and in an instant, this can change. I have to be true to myself. I have to be true to the employer too, but ultimately, I am the only one that truly wants to keep my best interest at heart. For short and hopefully long periods of time, both you and your employer will have your best interest at heart. But this will not be an absolute. So, beware of the signs. Set yourself goals. Realize the direction you want to head. If you and your employer can go there together, than you are truly blessed. If not...

This leads me to another point. I worked for a small group within a large at one time, where I was the most technical person on the team. Since it was a semblance of an IT shop, I stood out with knowledge that most others didn't have. This lead my manager to treat me differently. You would think it would be well, but it wasn't. The helpdesk people were treated often above all else. And this endeared them to the manager. I say 'helpdesk people', not in a derogatory term, but in this case, they were the lowest paid people in the group. I don't expect that since I made more than them, that I should be treated better. What I did expect was that we would all be treated with the same respect, regardless of our position or station. I do not consider myself better than another. Regardless of pay rate, years experience, job title, etc. We are all along this path of life and career, and are at different, rotating stations. One day, I am the noob, the next, I have more experience than most. But in either station, I deserve respect, just like the others on the team do. Long story short, I ended up leaving this job of my own will, because of the lack of respect I received, and lack of growth I currently perceived. In retrospect, I could have sat back, gotten an office, and chilled in this job for many, many years. But it didn't satisfy me. Did they treat me fairly?

So, in this last example, I was the highest paid employee. Yet, I didn't get treated fairly in other areas. In my first job, I didn't get treated fairly in areas other than money; I was a noob and made little money. It's not all about title, money, whether you have an office, and so on. There arealways many facets that make up a job, and you can be treated unfairly in or many of them.

Unfortunately, each time i have felt i have not been treated fairly, I have had to really dig in and look. Sometimes, it is entirely my perception. Other times, its entirely my fault. Other times, it is the employer's fault. But these are rarely absolute across time as well. They peak and valley.

Its important to review your station, position, demeanor, mood and so on, on a regular basis. Not too often, but often enough to make the slight course corrections needed to get back to where you need to be. And realize that no matter what it feels like, it may be your fault. I often wonder why the guy on the road cut me off. But in looking at that statement, I am attributing intent and foreknowledge to a complete stranger. Why would they cut me off? It has nothing to do with me, it just happened. But I feel slighted. This same scenario holds true often in the workplace. You may feel slighted, but are not, its just you think you are. So, be honest with yourself.

Have these little one-on-ones with yourself from time to time, and evaluate your place in life. If its time to move on, then do it. If you can fix some small habits or even large ones, then by all means, work on these and get them fixed. But its rarely as cut and dried as "I'm not being treated fairly". Get all the variables in the open and evaluate them. You may be surprised at what you find out.