A Speaker's Nightmare

---

"I am not left handed either!" - Westley, "The Princess Bride"

---

As noted in the profile section, I am a DBA who works with both Oracle and SQL Server. I had never really considered trying to speak at a technical conference. After I started paying more attention to the SQL Server online community, and attended the SQL PASS Summit in 2011, I thought I'd give it a shot.

I know more about Oracle than I do SQL Server and usually attend the Rocky Mountain Oracle User Group Training Days event in Denver each February, so I submitted an abstract during the call for papers.

I have been attending the RMOUG Training Days event an average of about 2 years out of 3 for the last 10 years or so. In all that time, I never remember having seen a session on Oracle Heterogeneous Services. OHS is a feature of Oracle that let's you seamlessly connect to a non-Oracle database via either a fully functional (and separately licensed) target specific gateway or via the more restricted functionality of the (free!) Generic Gateway Agent.

Having needed to put together a real time query link to a SQL Server database in the not so distant past, I thought it would be a good topic to talk about and hoped it served up enough interest to get some backsides in seats.

I was pleasantly surprised, a couple of months later to get an acceptance letter and started preparing my paper and slides.

During the course of the several weeks I worked on the presentation, I researched more into the topic. What I had done to meet my business requirement was only touching the tip of the iceberg. I explored, read, learned and tested. I installed MySQL, SQL Server and Oracle on my laptop and went through demo scenarios and making sure it all hung together.

On conference day, there was a 15 minute break between sessions and I turned up into my allotted room right after the end of the previous session to set up my laptop, make sure the projector was working and my remote was working.

The room monitor came in and we started talking and I joked that I'd had this nightmare that no one would show up.

At the top of the hour, as I'm supposed to start talking, I have 2 people in the room: One person who had walked in, and the room monitor.

Not to be discouraged, I ran through my presentation, took good questions from my small audience and provided answers.

Of course, post presentation, I was somewhat crushed. All that effort into putting the white paper and the slides together. The hours spent practicing the delivery, setting up the demo.

I'm sure that there were several contributing factors to the almost non-existant turn out. There were some big names in the Oracle world speaking at the same time on more main stream topics than mine. If I was Joe Q. DBA and could see a big known name talk about something more mainstream (like the optimizer, performance tuning etc) or go see some unknown talk about a rarely used part of the technology, I probably would have gone and seen big name too.

Also, the reason that I'd never seen a presentation on OHS before is probably because not a lot of people use it or need to know about it. If you see a session on the track about a piece of technology that you never use, the likelihood of attending that session, instead of a session on something that you need to know about and use on a day to day basis is low.

So, why am I writing this post and telling my sob story ?

The real value out of this whole process was the preparation. Even though the time spent researching, writing, building a slide deck seemed to have been wasted (because of the extremely small turnout), the reward wasn't in hearing the applause from a full room. The reward came from what I learned during the process. I now know a whole lot more about distributed transaction processing in Oracle, how it works over a Generic Gateway Agent as opposed to a target specific Gateway Agent and this whole piece of the technology in general.

The real reward was, pure and simply, the knowledge gained.

Does this mean that I'll never put forth another abstract in the future? No. If I can think of a topic with a more general scope that I am somewhat knowledgeable and passionate about then I'll probably submit again.

The real lesson learned here is not to be afraid to put yourself out there and speak about a topic that you feel others could learn something from. Even if your session is poorly attended, you'll accomplish two pretty important things. You'll learn something along the way, and you'll get your name out there.

Who knows, in the years to come, someone's online presence and visibility in the technical conference circuit could play a huge impact in finding you your dream job. (Brent Ozar PLF recently hired their first employee and a large part of their decision making process was just that: see their blog entry about their hiring decision).

So, when it comes to deciding about trying to speak at a technical conference, as those Nike people used to say: "Just Do It!"

By the way, if you're someone who deals with Oracle and want to read the slides / paper, drop me an email (address in the About Me section of the blog) and I'll send the documents your way :)

Architectural notes on SQL Server vs. Oracle

---

"I am not left handed!" - Inigo Montoya, "The Princess Bride"

---

One of the first things to cover under this heading would be the concept of a schema. The difference between a schema in SQL Server and Oracle is distinct.

The Oracle 11g Database Concepts manual defines a schema as follows:

"A database schema is a logical container for data structures, called schema objects. Examples of schema objects are tables and indexes. Schema objects are created and manipulated with SQL.

A database user has a password and various database privileges. Each user owns a single schema, which has the same name as the user. The schema contains the data for the user owning the schema. For example, the hr user owns the hr schema, which contains schema objects such as the employees table. In a production database, the schema owner usually represents a database application rather than a person.

Within a schema, each schema object of a particular type has a unique name. For example, hr.employees refers to the table employees in the hr schema. Figure 2-1 depicts a schema owner named hr and schema objects within the hr schema."

A schema does not necessarily need to contain tables. It contains schema objects - be they triggers, synonyms, views or whatever. Note that the schema is the same name as the user the owns it.

In SQL Server things are a little different. SQL Server Books Online tells us how from SQL Server 2005 and on:

"The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable."

The AdventureWorks database (the demo database that comes with SQL Server) shows this. Inside the one database, there is a schema called Sales, containing all the Sales tables, another called Product for the Products, one called HR for employee data. One user can be granted access to all of these tables (much like in Oracle or any other RDBMS) but they are each contained in their own schema. The schema is just a corral to hold these database objects.

The next piece of the architecture to discuss is how the physical data is stored. In Oracle, the database block size is determined at database creation and can be any multiple of 2K from the smallest of 2K up to the maximum of 32K (although each operating system may have a narrower range that restricts that). In SQL Server, the database block size, call a page, is always set at 8K (although there are exceptions to this, for data types such as varchar(max), varbinary(max), xml, image etc). In SQL Server, 8 x 8K pages forms an Extent which is the smallest allocation unit and should be 8 contiguous pages. Extent allocation in Oracle is handled differently depending on if the tablespace is defined with uniform extent sizes (each allocation is the same, based upon a value set by the DBA at tablespace creation) or is left as system managed (where Oracle decides how large the next extent needs to be based on internal algorithms).

Both Oracle and SQL Server store data in datafiles. In Oracle, one or more datafiles are assigned to a tablespace. (More accurately, when a tablespace is created, a datafile is defined to that tablespace. Later, that datafile can be expanded, or more datafiles can be added to it to increase overall space). Each tablespace has at least one datafile but may have more than one.

In SQL Server, the Filegroup is the concept that most closely matches the tablespace. A filegroup is a logical construct containing datafiles that can be used to spread datafiles out over multiple physical disk devices. When a database is created, a PRIMARY filegroup is mandatory Other user defined filegroups can also be created containing data files on various physical devices. Unless otherwise defined, the primary file group is the default and new objects created without the FILEGROUP keyword are assigned to the default filegroup.

Once again, SQL Server Books Online has an excellent reference to the subject, that I will not replicate here.

The final thing I want to mention in this post is the difference between the transaction logging models.

Oracle instances are defined with redo log file. The redo log contains two or more groups contains 1 or more online redo log files. It is common practice to multiplex the redo log files to protect against media failure. As changes are made to data, logging information is written to the currently active redo log group. When the current group fills, a log switch occurs and log information is written to the next member of the group. This cycle continues through the group as data is generated. If ARCHIVELOG mode is enabled, after a log group is filled a copy of the log is written to the log archive destination. These sequentially numbered archive logs are used for point in time recovery in the event of a failure.

The SQL Server Transaction Log on the other hand is one or more log file per database that is written to continually in a wrap around fashion. In a database in full recovery mode, the transaction log is written to continually and will continue to grow unless a transaction log backup is done. Once the backup is done, the transaction log is free to be written over again from the beginning. In simple recovery model, changes are still logged to the transaction log, but once a transaction is completed, the space in the transaction log is able to be overwritten, so a chain of changes cannot be maintained (point in time recovery is impossible in this mode).

Oracle has two options for logging: ARCHIVELOG mode and NOARCHIVELOG mode. Transactions are still logged to the online redo logs, but the difference here is whether or not the redo logs are archived out to disk when full. The Oracle Documentation discusses this in more detail.

SQL Server has three different logging modes: Full, Bulk Logged and Simple. Full and Simple respectively correspond to the two Oracle methods. SQL Server Books Online covers these in more detail.

SQL Server and Oracle are two different Relational Database Management Systems and hopefully these posts will help explain some of the differences between the two for those people, like me, who regularly are faced with using both.

Quick and Dirty Data Extract

---

"You've been mostly dead all day!" - Fezzik, The Princess Bride

---

I was recently tasked with extracting data from a couple of dozen tables, tilde delimited, with column headings.

So, I spent a little time coming up with this:

set nocount on
select 'select '
select ''''+a.name+''''+'+''~''+' 
from syscolumns a, sysobjects b
where a.id = b.id
and b.name = 'EXTRACT_TABLE'
order by colid;

select 'select ';
select case when a.xtype = 106 then 'isnull(cast('+a.name+' as varchar), '' '')' 
            when a.xtype = 108 then 'isnull(cast('+a.name+' as varchar), '' '')' 
            when a.xtype =  61 then 'isnull(convert(varchar(8),'+a.name+',112), '' '')' 
            else 'isnull('+a.name+','' '')' END+'+''~''+' 
from syscolumns a, sysobjects b
where a.id = b.id
and b.name = 'EXTRACT_TABLE'
order by colid;

select 'from extract_table';

The output from this query needs a little tidying up - the last column of each generated select statement needs the +'~'+ removed from it (for obvious syntactical reasons) and the spacing of the output can always be cleaned up a little from a prettying it up perspective, but it got the job done.

This was from a SQL Server 2000 system and would need to be ever so slightly modified to use on 2005/2008/2012.

Like I said in the subject line, quick and dirty (but it got the job done) :)

T-SQL Tuesday #028 - Jack of All Trades, Master of None

---

"Certainty of death ... small chance of success ... what are we waiting for ?" - Gimli, Return of the King

---

The second Tuesday of the month invariably means that it's time for T-SQL Tuesday again. Last time I participated was January and I promised all sorts of things for resolutions like attending my local User Group meetings (0/3 so far this year), updating my blog at least once a week (6 weeks since the last post) and reading more books this year than I usually do (doing OK on the fiction front, need to catch up on the tech books though). So, this month I decided to jump back into the pool and at least post something for T-SQL Tuesday.

Argenis Fernandez(blog|twitter) is the Maitre d' of our blogging hotel this month and he's asked us to talk about what we specialize in, if indeed that's anything.

Jack of All Trades, he posted as the title of this month's event. I've been working in Information Technology for over 25 years. Looking back over my career, the title "Jack of All Trades" probably fits me very well.

I started as a mainframe COBOL programmer back in the mid-80's and did that for several years. In 1991 I left my government job and joined the development staff of an Australian ERP Software company. Today, a little over 21 years later, I'm still working with their software, but my role has changed significantly.

It was with this company, after transferring to the USA that I started getting exposed to databases. Our machines were hosted out of Australia, our DBA support was Australian based (which meant if we had problems we got support starting around 3PM) and so I started teaching myself Oracle. After several years of being the "go to" guy for "in a pinch" support earlier than 3PM, my boss gave me the chance that changed my career direction. He was going to let me transfer and become an Oracle Database Administrator.

That was 12 years ago and I've loved working with them ever since.

However, owing to the niche environment I work in (I'm now working for a small consulting firm that supports this ERP product), I've found myself in an interesting place.

Because of the knowledge I have of the ERP product and the environment it runs in, I'm very much a generalist in what I do. Four years ago, I was forced to learn SQL Server. I'd pushed back against that for years but finally had to succumb to the inevitable.

The software package tends not to use a lot of the advanced features of the databases it runs against. The data isn't necessarily considered up to the second critical (such as for banking transactions) and in the event of problems, some down time can be tolerated.

For Oracle, for example, Real Application Clustering (RAC) isn't used in this environment. So I don't have those skills (and the few times I've looked at the job market, just to see what's out there, everyone seems to want it).

For SQL Server, I've never had to deal with clustering, or log shipping or replication.

Because of the specific requirements of my job at the moment, I'm very much a generalist (I do Oracle, I do SQL Server, I do Unix Shell and Windows CMD scripting, I still fall back to my COBOL roots as needed from time to time too) and for me, at this point in my career that's not a bad thing.

Does this mean I don't want to learn these other more specialized aspects of the various RDBMS systems I work with ? No. However, when you have to balance available time with what's needed for day to day operations, sometimes you don't always get what you want.

So for now, I am perfectly happy in what I'm doing. I love my job. The "restrictions" on not being a specialist in any particular field have not held me back any at the moment. Having such a broad, generalized knowledge base, when working in a very niche field has actually been very helpful.

It could actually be said, come to think of it, that I'm a "specialist" in the technical area around this particular product I've been working with for the last two decades, while being a generalist in the underlying technology (the databases, the operating systems etc).

One of the things I have found in working across so many diverse areas is a need to keep some good notes about the things you learn (so that 2 years from now when a problem pops up again, you have a reference), and that there's always something new to learn!

The bottom line is that for me, generalizing across many areas has worked very well. Reading some of the other posts, I've seen that a lot of people tend to end up in one area and that doesn't surprise me. For me, being a Jack of All Trades just hasn't been a bad thing :)