Programmer's Edge

I will be giving a presentation to the PASS DBA Virtual Chapter on Aug 11,2010. Be sure to attend as they are giving away $50.00 Amazon gift card! The details are below so be sure and sign up!

Event Link:   http://www.sqlpass.org/Events/ctl/ViewEvent/mid/521.aspx?ID=457

Date:
8/11/2010

Start Time:    
12:00:00 PM

End Time:    
1:00:00 PM

Timezone:    
(GMT-07:00) Mountain Time (US & Canada)

Short Description

DBA Virtual Chapter Live Meeting Event

Event Description

Topic: Transactional Replication Deep Dive (presented by Arie Jones) and What a DBA Needs to Know About Data Modelling (Sanjay Shirude)

Presenter: Arie Jones

Abstract:

Transactional Replication is often one of those black box features where if you are using the wizard in a standard implementation then you are fine. Yet, what do you do when you have to do something like transactional replication across non-trusted domains. In this session, we will discuss the internals of transactional replication, how it works, and what you can do to manage your implementations more effectively by understanding what going on underneath the covers.

Presenter Bio:

Arie "AJ" Jones is Principal Technology Manager for Perpetual Technologies, Inc. (PTI) in Indianapolis, Indiana. PTI provides mission-critical database and information systems support for SQL Server and Oracle environments nationwide. Arie leads PTI’s team of experts in planning, design, development, deployment, and management of database environments and applications to achieve the best combination of tools and services for each client. He has authored several books on SQL and related topics including Learn SQL in 24 Hours, Learn SQL in 1 Hour a Day, SQL Functions, and Live Lessons: SQL as well as maintaining the popular web blogs: programmersedge.com and sqlsherpa.com

Cheers!
AJ

Following up on my previous post a couple of people have asked me how they could accomplish this same thing because MERGE is not available in SQL Server 2005. So let’s take a look at how you can use the OUTPUT statement which is available to produce sort of the same affect.

So first we will set up our test data once again from the AdventureWorks database. This will act as the data that is coming into our target table

SELECT TOP 1000 [ContactID]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[EmailAddress]
  INTO [AdventureWorks].[Person].[Contact_Temp]
  FROM [AdventureWorks].[Person].[Contact]

Then we’ll put some data into the target table…

  SELECT TOP 10 *
  INTO [AdventureWorks].[Person].[Contact_Base]
  FROM [AdventureWorks].[Person].[Contact_Temp] 

We change the email address to ensure that our T-SQL will in fact update what we are wanting to update

  UPDATE [AdventureWorks].[Person].[Contact_Base]
  SET [EmailAddress]='arie.jones@programmersedge.com'

Now we’ll take a look at the data to make sure it’s what we are expecting…..

So now we are going to write out statement that will mimic the MERGE stuff we did in the previous post. To clarify, we basically want to update the target table and if a particular ContactID exists then we want to update the EmailAddress column. If no such ContactID exists then we would like to insert the row.

Now OUTPUT statement works a lot like an inline TRIGGER in that you have access to an Inserted table. So you have access to the data that is actually being updated/inserted/whatever by the particular statement. So it’s not only good for this instance but also for other scenarios like logging. So we will first go and create as simple table variable to hold the ContactID values that we are going to update with our initial statement. Then we will be using the OUTPUT statement to fill up the table in conjunction with our UPDATE and lastly running an INSERT statement. Kind of a long way around the track when you look at the MERGE statement in the previous post…but hey ya’ gotta work with the tools you got.

 SET IDENTITY_INSERT [AdventureWorks].[Person].[Contact_Base] ON;  

 DECLARE @tblContacts table (ContId int); 

 UPDATE [AdventureWorks].[Person].[Contact_Base]
 SET EmailAddress=b.EmailAddress
 OUTPUT inserted.ContactID INTO  @tblContacts
 FROM [AdventureWorks].[Person].[Contact_Base] a INNER JOIN
 [AdventureWorks].[Person].[Contact_Temp] b ON a.ContactID=b.ContactID
;

INSERT INTO [AdventureWorks].[Person].[Contact_Base]
([ContactID],[Title],[FirstName],[MiddleName]
  ,[LastName],[EmailAddress])
SELECT [ContactID],[Title],[FirstName]
      ,[MiddleName],[LastName],[EmailAddress]
      FROM [AdventureWorks].[Person].[Contact_Temp]
WHERE ContactID NOT IN (SELECT ContId from @tblContacts);

Now when we look at the table with a simple SELECT statement we see that everything is as it should be….

SUCCESS! Hopefully, this helps someone else that may be in a jam because of using an older version of SQL Server ….

Cheers,

AJ

It’s not very often that I feel that Microsoft needs to be beat about the head and shoulders a little about something, but there needs to be a warning label that pops up when going through the properties screen for your databases for the Auto_Update_Statistics option….maybe something akin to a cigarette package:

I don’t know how many times I walk into a database shop that is having performance issues on a system that has transactions flying all over the place. The conversation usually goes something like this after I have had time to look at the system:

AJ: “Okay, so what are you doing about indexing and statistics updates?”

DBA: “We are doing a maintenance plan to update the indexes”

AJ:”What about the statistics?”

DBA:”We have Auto_Update_Statistics turned on.”

AJ:”That doesn’t necessarily guarantee….”

DBA:”Microsoft says blah,blah,blah…”

AJ:”Just humor me and try this..”

DBA:”Amazing it worked!”

How about it Microsoft? Can you throw me this one little bone in the next service pack release?! One less phone call at 2:00 in the morning would really make my day!

Cheers,
AJ

I don’t often write about business things on my site but I encountered a phenomenon a couple of weeks ago at our joint Microsoft/Oracle event. Several people asked me the same question, “How do you guys make money on these events? There was no selling in the sessions?”. Every time, I explain that it is not necessarily a direct one to one correlation. Rather, that we do these free community events in order to indirectly drive sales our way. We are essential selling our best asset, our people. It’s not a hard fast formula but if you show that you are the best of the best then businesses take not of that. Who wants to hire the #2 or #3 company to do a mission critical project?

Invariably, the second question, well more of a statement than a question, “But I don’t see a CIOs, CFOs, or (Insert 3 letter abbreviation here)”. This is true. Technical events are more geared towards the non-management people. The people on the front lines whom actually have to write the code or configure the database. So why do we do it. Simply, it is because any good manager knows that part of managing groups is to not come up with all the ideas yourself. You need to take input from the team…look at all the options…and then take a course of action. What better sales force could I have then a group of 100 or more pumped up IT geeks whom see a need to have the latest and greatest technology but may feel gun-shy about implementing it.

More often than not, the third question is to challenge the consultant, “Why would IT people have you come in to do something, when you would be looking to replace them.”……now this would most likely be true if I were a part of a regular consulting firm. Most times these firms hire in people from job to job with no one ‘really’, meaning they are all 1099, on staff and people are by nature always looking to get an extension of their current contract. It’s not that they are necessarily bad people, unless you count the ones that say “We do everything”, and instead don’t really do anything but wing it, they’re just trying to secure whatever particular lifestyle they have. Why IT people invite me in to help them with their projects is because I am a different type of consultant.

When I left the government arena a couple years back, I made a decision to join Perpetual Technologies,Inc. (PTI) not because one of my friends, Chris Zeis, was one of the owners…but because I was basically told that I would be able to build the Microsoft division of PTI. I had just spent several years in the university arena and the government arena and had soured at the uber political environment so I saw this as an opportunity to build something different. It would be typical for consultants to go out and try to say “Hey, replace these guys with us and we will save you a bunch of money in the long run…” but it would be something more substantial if we were to instead look to augment an existing IT infrastructure. Imagine a consulting firm that actually gave a damn not just about making themselves look good but also making the people that they work for look good.

So 3 years later this is where we stand…the recession is going on but we are as busy as ever. Why? I chalk it up to the fact that we take the time and put in the effort to make our clients shine. It like a cycle. We make client X look good. Client X now is given a promotion to V.P. of something or another because he has shown that she or he can get the job done. Now who do you suppose Client X is going to go back to now that in their new found position of power they have found that they need to get several projects accomplished? It’s not a hard principle to grasp, and we are definitely not using any kind of Jedi mind tricks on people. It’s just the principle that if you treat people fairly and give them 110% then you will be rewarded in the end. That’s why I don’t mind so much when people email me questions like “AJ, I can’t get this thing to work with Reporting Services”. People are often surprised when I drop a piece of code in their lap and say “Here take this ..it will work for you with a few tweaks”. Yeah, they may not have a budget for that project today….but if all you are looking at is the present then you are going to miss the long term picture.

So for those CIOs, CFOs, and CEOs that may be reading my blog, don’t look at consultants merely as a way in which you can replace in house staff and be able to reduce long term costs. Instead, look at firms like mine as a way in which you can stretch your IT dollar in order to get that high stake, high technology projects you know your business needs while at the same time giving your people a valuable asset that they can leverage to give themselves a little breathing room.

Hopefully, this little blog has helped to dispel some of the myths that all consultants are alike…well, at least with my group of people. Hopefully, it will make more people attend our events in the future as they will lose the fear that it is some sneaky sales presentation and no real learning goes on. Hopefully, some aspiring CIO out there if you find themselves under the gun to implement some new technology or have a project that you know your current staff could use a hand on and will give me a call. So feel free the next time to give me a call or drop me an email and maybe I can show you how my team’s services could make your next project shine.

 Cheers,
AJ

At my talk today I ran into a couple of individuals that asked me the same question. “Why can’t I run this package as a scheduled job?”. I run across this many times in my consulting because there is a lot of documentation on deployment out there but none of it really seems to click with people.