Name of the blog

Short description of the blog

Microsoft Sql Server 2008 R2 System Databases

For a majority of software developers little time is spent understanding the inner workings of the database management systems (DBMS) they use to store data for their applications.  I personally place myself in this grouping. In my case, I have used various versions of Microsoft’s SQL Server (2000, 2005, and 2008 R2) and just recently learned how valuable they really are when I was preparing to deliver a lecture on "SQL Server 2008 R2, System Databases".

Microsoft Sql Server 2008 R2 System Databases
So what are system databases in MS SQL Server, and why should I know them? Microsoft uses system databases to support the SQL Server DBMS, much like a developer uses config files or database tables to support an application. These system databases individually provide specific functionality that allows MS SQL Server to function.

Name Database File Log File
Master master.mdf mastlog.ldf
Resource mssqlsystemresource.mdf mssqlsystemresource.ldf
Model model.mdf modellog.ldf
MSDB msdbdata.mdf msdblog.ldf
Distribution distmdl.mdf distmdl.ldf
TempDB tempdb.mdf templog.ldf

Master Database
If you have used MS SQL Server then you should recognize the Master database especially if you used the SQL Server Management Studio (SSMS) to connect to a user created database. MS SQL Server requires the Master database in order for DBMS to start due to the information that it stores.

Examples of data stored in the Master database

  • User Logins
  • Linked Servers
  • Configuration information
  • Information on User Databases

Resource Database
Honestly, until recently I never knew this database even existed until I started to research SQL Server system databases. The reason for this is due largely to the fact that the resource database is hidden to users. In fact, the database files are stored within the Binn folder instead of the standard MS SQL Server database folder path. This database contains all system objects that can be accessed by all other databases.  In short, this database contains all system views and store procedures that appear in all other user databases regarding system information. One of the many benefits to storing system views and store procedures in a single hidden database is the fact it improves upgrading a SQL Server database; not to mention that maintenance is decreased since only one code base has to be mainlined for all of the system views and procedures.

Model Database
The Model database as the name implies is the model for all new databases created by users. This allows for predefining default database objects for all new databases within a MS SQL Server instance. For example, if every database created by a user needs to have an “Audit” table when it is  created then defining the “Audit” table in the model will guarantees that the table will be located in every new database create after the model is altered.

MSDB Database
The MSDBdatabase is used by SQL Server Agent, SQL Server Database Mail, SQL Server Service Broker, along with SQL Server. The SQL Server Agent uses this database to store job configurations and SQL job schedules along with SQL Alerts, and Operators. In addition, this database also stores all SQL job parameters along with each job’s execution history.  Finally, this database is also used to store database backup and maintenance plans as well as details pertaining to SQL Log shipping if it is being used.

Distribution Database
The Distribution database is only used during replication and stores meta data and history information pertaining to the act of replication data. Furthermore, when transactional replication is used this database also stores information regarding each transaction. It is important to note that replication is not turned on by default in MS SQL Server and that the distribution database is hidden from SSMS.

Tempdb Database
The Tempdb as the name implies is used to store temporary data and data objects. Examples of this include temp tables and temp store procedures. It is important to note that when using this database all data and data objects are cleared from this database when SQL Server restarts. This database is also used by SQL Server when it is performing some internal operations. Typically, SQL Server uses this database for the purpose of large sort and index operations.

Finally, this database is used to store row versions if row versioning or snapsot isolation transactions are being used by SQL Server.

Additionally, I would love to hear from others about their experiences using system databases, tables, and objects in a real world environments.

@CodeStock 2012 Review: Rob Gillen ( @argodev ) - Anatomy of a Buffer Overflow Attack

Anatomy of a Buffer Overflow Attack
Speaker: Rob Gillen
Twitter: @argodev

Honestly, this talk was over my head due to my lack of knowledge of low level programming, and I think that most of the other attendees would agree. However I did get the basic concepts that we was trying to get across. Fortunately most high level programming languages handle most of the low level concerns regarding preventing buffer overflow attacks. What I got from this talk was to validate all input data from external sources.

@CodeStock 2012 Review: Jay Harris ( @jayharris ) - XCopy is Dead: .Net Deployment Strategies that Work

XCopy is Dead: .Net Deployment Strategies that Work
Speaker: Jay Harris
Twitter: @jayharris

This talk focused on new technologies built in to deployment packaging through Visual Studios 2010.  Jay showed various methodologies in deploying web sites, and focused on features specifically for Visual Studios 2010. He covered transforming config files based on environmental constraints, the creation of deployment packages, and deploying packages via command line or importing into IIS 7.

@CodeStock 2012 Review: Leon Gersing ( @Rubybuddha ) - "You"

Speaker: Leon Gersing
Twitter: @Rubybuddha

I honestly had no idea what I was getting in to when I sat down in to this session. I basically saw the picture of the speaker and knew that it would be a good session. I was completely wrong; it was the BEST SESSION of CodeStock 2012.  In fact it was so good, I texted another coworker attending the conference to get over and listen to Leon. Leon took on the concept of growth in the software development community. He specifically referred David Hansson in his ability to stick to his beliefs when the development community thought that he was crazy for creating Ruby on Rails. If you do not know this story Ruby on Rails is one of the fastest growing web languages today. In addition, he also touched on the flip side of this argument in that we must be open to others ideas and not discard them so quickly because we all come from differing perspectives and can add value to a project/team/community.

This session left me with two very profound concepts/quotes:

“In order to learn you must do it badly in front of a crowed and fail.” - @Rubybuddha

I can look back on my career so far and say that he is correct; I think I have learned the most after failing, especially when I achieved this failure in front of other.

“Experts must be able to fail.” - @Rubybuddha

I think we can all learn from our own mistakes but we can also learn from others. When respected experts fail it is a great learning opportunity for the entire team as well as the person who failed. When expert admit mistakes and how they worked through them can be great learning tools for other developers so that they know how to avoid specific scenarios and if they do become stuck in the same issue they will know how to properly work their way out of them.

CodeStock 2012 Review: Michael Eaton( @mjeaton ) - 3 Simple Things for Increased Productivity

3 Simple Things for Increased Productivity
Speaker: Michael Eaton
Twitter: @mjeaton

This was the first time I had seen Michael Eaton speak but have hear a lot of really good things about his speaking abilities. Needless to say I was really looking forward to his session. He basically addressed the topic of distractions and how they can decrease or increase your productivity as a developer. He makes the case that in order to become more productive you must block/limit all distractions. For example, he covered his top distractions as a developer.

Top Distractions

  • Social Media(Twitter, Reddit, Facebook)
  • Wiki sites
  • Phone
  • Email
  • Video Games
  • Coworkers, Friends, Family

Michael stated that he uses various types of music to help him block out these distractions in order for him to get into his coding zone. While he states that music works for him, he also notes that he knows of others that cannot really work with music. I have to say I am in the latter group because I require a quiet environment in order to work. A few session attendees also recommended listening to really loud white noise or music in another language other than your own. This allows for less focus to be placed on words being sung compared to the rhythmic beats being played. I have to say that I have not tried these suggestions yet but will in the near future.

However, distractions can be very beneficial to productivity in that they give your mind a chance to relax and not think about the issues at hand. He spoke highly of taking vacations, and setting boundaries at work so that develops prevent the problem of burnout.

One way he suggested that developer’s combat distractions is to use the Pomodoro technique. In his example he selects one task to do for 20 minutes and he can only do that task during that time. He ignores all other distractions until this task or time limit is complete. After it is completed he allows himself to relax and distract himself for another 5- 10 minutes before his next Pomodoro. This allows him to stay completely focused on a task and when the time is up he can then focus on other things.

CodeStock 2012 Review: Eric Landes( @ericlandes ) - Automated Tests in to automated Builds! How to put the right type of automated tests in to the right automated builds.

Automated Tests in to automated Builds! How to put the right type of automated tests in to the right automated builds.
Speaker: Eric Landes
Twitter: @ericlandes

This was one of the first sessions I attended during CodeStock 2012. Eric’s talk focused mostly on unit testing, and that the lack of proper unit testing can be compared to stealing from an employer. His point was that if you’re not doing proper unit testing then all of the time wasted on fixing issues that could have been detected with unit tests is like stealing money from employer. He makes the assumption that that time spent on fixing these issues could have been better spent developing new features that drive the business.

To a point I can agree with Eric’s argument regarding unit testing and stealing from a company’s perspective. I can see how he relates resources being shifted from new development to bug fixes as stealing based on the fact that the resources used to fix bugs are directly taken from other projects.

He also states that Boring/Redundant and Build/Test tasks should be automated because it reduces the changes of errors and frees up developer to do what they do best, DEVELOP!

When he refers to testing, he breaks testing down in to four distinct types.

  • Unit Test
  • Acceptance Test (This also includes Integration Tests)
  • Performance Test
  • UI Test

With this he also recommends that developers should not go buck wild striving for 100% code coverage because some test my not provide a great return on investment. In his experience he recommends that 70% test coverage was a very acceptable rate.

Error Handling Examples(C#)

“The purpose of reviewing the Error Handling code is to assure that the application fails safely under all possible error conditions, expected and unexpected. No sensitive information is presented to the user when an error occurs.” (OWASP, 2011)

No Error Handling
The absence of error handling is still a form of error handling. Based on the code in Figure 1, if an error occurred and was not handled within either the ReadXml or BuildRequest methods the error would bubble up to the Search method. Since this method does not handle any acceptations the error will then bubble up the stack trace. If this continues and the error is not handled within the application then the environment in which the application is running will notify the user running the application that an error occurred based on what type of application.

Figure 1: No Error Handling

public DataSet Search(string searchTerm,  int resultCount)

           DataSet dt = new DataSet();
           dt.ReadXml(BuildRequest(searchTerm, resultCount));
           return dt;

Generic Error Handling
One simple way to add error handling is to catch all errors by default. If you examine the code in Figure 2, you will see a try-catch block.

On April 6th 2010 Louis Lazaris clearly describes a Try Catch statement by defining both the Try and Catch aspects of the statement.

“The try portion is where you would put any code that might throw an error. In other words, all significant code should go in the try section. The catch section will also hold code, but that section is not vital to the running of the application. So, if you removed the try-catch statement altogether, the section of code inside the try part would still be the same, but all the code inside the catch would be removed.” (Lazaris, 2010)

He also states that all errors that occur in the try section cause it to stops the execution of the try section and redirects all execution to the catch section. The catch section receives an object containing information about the error that occurred so that they system can gracefully handle the error properly.

When errors occur they commonly log them in some form. This form could be an email, database entry, web service call, log file, or just an error massage displayed to the user.  Depending on the error sometimes applications can recover, while others force an application to close.

Figure 2: Generic Error Handling

public DataSet Search(string searchTerm,  int resultCount)

           DataSet dt = new DataSet();

               dt.ReadXml(BuildRequest(searchTerm, resultCount));
           catch (Exception ex)
             // Handle all Exceptions

           return dt;

Error Specific Error Handling
Like the Generic Error Handling, Error Specific error handling allows for the catching of specific known errors that may occur. For example wrapping a try catch statement around a soap web service call would allow the application to handle any error that was generated by the soap web service. Now, if the systems wanted to send a message to the web service provider every time a soap error occurred but did not want to notify them if any other type of error occurred like a network time out issue. This would be varying tedious to accomplish using the General Error Handling methodology. This brings us to the use case for using the Error Specific error handling methodology. 

The Error Specific Error handling methodology allows for the TryCatch statement to catch various types of errors depending on the type of error that occurred. In Figure 3, the code attempts to handle DataException differently compared to how it potentially handles all other errors. This allows for specific error handling for each type of known error, and still allows for error handling of any unknown error that my occur during the execution of the TryCatch statement.

Figure 5: Error Specific Error Handling

public DataSet Search(string searchTerm,  int resultCount)
           DataSet dt = new DataSet();

               dt.ReadXml(BuildRequest(searchTerm, resultCount));
           catch (TimeoutException ex)
               // Handle Timeout TimeoutException Only
           catch (Exception)
               // Handle all Exceptions

           return dt;

Advantages and Disadvantages of the Waterfall Methodology

In my personal opinion I believe the waterfall method is one of the worst methodologies to use when developing larger systems because it leaves is no room for mistakes. As the name implies the waterfall methodology does not allow  for projects to go back up stream to recover from design errors, missing and/or limited requirements. In addition, hidden bugs are not usually found until the testing phase. This can prove to be very costly and time consuming to the developer and the client.

According to, the waterfall methodology structures a project into separate stages with defined deliverables from each phase.

  • Define
  • Design
  • Code
  • Test
  • Implement
  • Document and Maintain

The advantages found by to this methodology are:

  • Ease in analyzing potential changes 
  • Ability to coordinate larger teams, even if geographically distributed
  • Can enable precise dollar budget
  • Less total time required from Subject Matter Experts

The disadvantages found by to this methodology are:

  • Lack of flexibility
  • Hard to predict all needs in advance
  • Intangible knowledge lost between hand-offs
  • Lack of team cohesion
  • Design flaws not discovered until the Testing phase


SOA Implementation Challenges

Why do companies think that if they put up a web service that they are doing Service-Oriented Architecture (SOA)? Unfortunately, the IT and business world love to run on the latest hype or buzz words of which very few even understand the meaning. One of the largest issues companies have today as they consider going down the path of SOA, is the lack of knowledge regarding the architectural style and the over usage of the term SOA. So how do we solve this issue?

I am sure most of you are thinking by now that you know what SOA is because you developed a few web services.  Isn’t that SOA, right? No, that is not SOA, but instead Just Another Web Service (JAWS). For us to better understand what SOA is let’s look at a few definitions.
Douglas K. Bary defines service-oriented architecture as a collection of services. These services are enabled to communicate with each other in order to pass data or coordinating some activity with other services.

If you look at this definition closely you will notice that Bary states that services communicate with each other. Let us compare this statement with my first statement regarding companies that claim to be doing SOA when they have just a collection of web services. In order for these web services to for an SOA application they need to be interdependent on one another forming some sort of architectural hierarchy. Just because a company has a few web services does not mean that they are all interconnected.

SearchSOA from states that SOA defines how two computing entities work collectively to enable one entity to perform a unit of work on behalf of another. Once again, just because a company has a few web services does not guarantee that they are even working together let alone if they are performing work for each other.
SearchSOA also points out service interactions should be self-contained and loosely-coupled so that all interactions operate independent of each other.

Of all the definitions regarding SOA Thomas Erl’s seems to shed the most light on this concept. He states that “SOA establishes an architectural model that aims to enhance the efficiency, agility, and productivity of an enterprise by positioning services as the primary means through which solution logic is represented in support of the realization of the strategic goals associated with service-oriented computing.” (Erl, 2011)

Once again this definition proves that a collection of web services does not mean that a company is doing SOA. However, it does mean that a company has a collection of web services, and that is it.

In order for a company to start to go down the path of SOA, they must take  a hard look at their existing business process while abstracting away any technology so that they can define what is they really want to accomplish. Once a company has done this, they can begin to factor out common sub business process like credit card process, user authentication or system notifications in to small components that can be built independent of each other and then reassembled to form new and dynamic services that are loosely coupled and agile in that they can change as a business grows.

Another key pitfall of companies doing SOA is the fact that they let vendors drive their architecture. Why do companies do this? Vendors’ do not hold your company’s success as their top priority; in fact they hold their own success as their top priority by selling you as much stuff as you are willing to buy. In my experience companies tend to strive for the maximum amount of benefits with a minimal amount of cost. Does anyone else see any conflicts between this and the driving force behind vendors.

Mike Kavis recommends in an article written in that companies need to figure out what they need before they talk to a vendor or at least have some idea of what they need. It is important to thoroughly evaluate each vendor and watch them perform a live demo of their system so that you as the company fully understand what kind of product or service the vendor is actually offering. In addition, do research on each vendor that you are considering, check out blog posts, online reviews, and any information you can find on the vendor through various search engines.

Finally he recommends companies to verify any recommendations supplied by a vendor.

From personal experience this is very important. I can remember when the company I worked for purchased a $200,000 add-on to their phone system that never actually worked as it was intended. In fact, just after my departure from the company started the process of attempting to get their money back from the vendor. This potentially could have been avoided if the company had done the research before selecting this vendor to ensure that their product and vendor would live up to their claims.

I know that some SOA vendor offer free training regarding SOA because they know that there are a lot of misconceptions about the topic. Superficially this is a great thing for companies to take part in especially if the company is starting to implement SOA architecture and are still unsure about some topics or are looking for some guidance regarding the topic. However beware that some companies will focus on their product line only regarding the training.

As an example, claims that companies providing deep seminars disguised as training, focusing more about ESBs and SOA governance technology, and less on how to approach and solve the architectural issues of the attendees.

In short, it is important to remember that we as software professionals are responsible for guiding a business’s technology sections should be well informed and fully understand any new concepts that may be considered for implementation. As I have demonstrated already a company that has a few web services does not mean that they are doing SOA.  Additionally, we must not let the new buzz word of the day drive our technology, but instead our technology decisions should be driven from research and proven experience. Finally, it is important to rely on vendors when necessary, however, always take what they say with a grain of salt while cross checking any claims that they may make because we have to live with the aftermath of a system after the vendors are gone.




Enterprise Service Bus (ESB): Important architectural piece to a SOA or is it just vendor hype?

Is an Enterprise Service Bus (ESB) an important architectural piece to a Service-Oriented Architecture (SOA), or is it just vendor hype in order to sell a particular product such as SOA-in-a-box?

According to, an ESB is a flexible connectivity infrastructure for integrating applications and services; it offers a flexible and manageable approach to service-oriented architecture implementation.

With this being said, it is my personal belief that ESBs are an important architectural piece to any SOA. Additionally, generic design patterns have been created around the integration of web services in to ESB regardless of any vendor.

ESB design patterns, according to Philip Hartman, can be classified in to the following categories:

  • Interaction Patterns: Enable service interaction points to send and/or receive messages from the bus
  • Mediation Patterns: Enable the altering of message exchanges
  • Deployment Patterns: Support solution deployment into a federated infrastructure

Examples of Interaction Patterns:

  • One-Way Message
  • Synchronous Interaction
  • Asynchronous Interaction
  • Asynchronous Interaction with Timeout
  • Asynchronous Interaction with a Notification Timer
  • One Request, Multiple Responses
  • One Request, One of Two Possible Responses
  • One Request, a Mandatory Response, and an Optional Response
  • Partial Processing
  • Multiple Application Interactions

Benefits of the Mediation Pattern:

  • Mediator promotes loose coupling by keeping objects from referring to each other explicitly, and it lets you vary their interaction independently
  • Design an intermediary to decouple many peers
  • Promote the many-to-many relationships between interacting peers to “full object status”

Examples of Interaction Patterns:

  • Global ESB: Services share a single namespace and all service providers are visible to every service requester across an entire network
  • Directly Connected ESB: Global service registry that enables independent ESB installations to be visible
  • Brokered ESB: Bridges services that are reluctant to expose requesters or providers to ESBs in other domains
  • Federated ESB: Service consumers and providers connect to the master or to a dependent ESB to access services throughout the network