Hyland - OnBase

General Overview

OnBase is primarily an Imaging system (IS) but it also provides hybrid features that come with other ECMS, BPMS, and BRMS systems. Its core strength however lies in Image management and providing components and tool-sets that allow image manipulation.  In this blog I am going to dive into some of the features that OnBase provides. I plan to write another blog for IBM FileNet the competitor for Hyland-OnBase in this space. So let’s get started

The figure below gives 10,000 feet oversimplified view of what an Imaging system provides

The intake of documents (specifically images) to the imaging system occurs through various routes

• Users scanning-in documents  (Point of contact scanning)
• Mail rooms doing batch scanning
• Other systems pushing electronic images into a central imaging system (OnBase) via API (Application Programming Interface)
• Electronic documents (not just images, but Word, PowerPoint, PDF and many more) uploaded into the imaging system
• List is not complete (as there are many other options)

Once the documents are scanned in they are indexed via keywords. Once the documents are indexed with keyword values, they can be retrieved and then assuming the content type is an “image”, image manipulation operations like annotations, redactions, lighten/darken image, rotate/reverse, zoom-in/zoom-out image etc. can be performed on them.

In the following section, I am going to explain how OnBase configuration helps tailor the system to suit your needs (NOTE: Since I have worked for many years in designing software systems using quite a few programming languages from ground-up I will provide hints through-out this blog about how to design a Custom Imaging System (IS) if you had to custom-code the Imaging system using programming languages from ground-up, I will tag my comments within this blog with “Custom-Code:” to help folks search for ideas if they want to design a imaging system from ground up)

Once the documents are scanned in they are indexed via keywords. Once the documents are indexed with keyword values, they can be retrieved and then assuming the content type is an “image”, image manipulation operations like annotations, redactions, lighten/darken image, rotate/reverse, zoom-in/zoom-out image etc. can be performed on them.

In the following section, I am going to explain how OnBase configuration helps tailor the system to suit your needs (NOTE: Since I have worked for many years in designing software systems using quite a few programming languages from ground-up I will provide hints through-out this blog about how to design a Custom Imaging System (IS) if you had to custom-code the Imaging system using programming languages from ground-up, I will tag my comments within this blog with “Custom-Code:” to help folks search for ideas if they want to design an imaging system from ground up)

Glossary

I will try to use this section as a place holder to describe terms that I use within this blog that I do not explain but use it as though it’s crystal clear to everyone; I cannot promise to be very detailed and if I have missed any do point it out to me so I can publish a revision.

• Folders - Folders within OnBase allow us to rearrange documents that are logically related; think of Folders in windows explorer. OnBase uses Folders in a similar manner. Folders are used for various purposes (Workflow, Record Management are just a few examples where Folders are used). Determination of which documents belong to a folder is done based on keywords that are common between the document types that the documents belong to and the keywords that are defined at the Folder Type of the Folder.
• Folder Types – This is just the type that defines the Folder. It allows you to define metadata like user groups; keywords used to associate documents to the Folders of the Folder Type.
• File Cabinets - File cabinet is the highest organization unit within an OnBase folder structure; File Cabinet cannot have a parent. It’s just another hierarchical term used within the context of OnBase Folder structure; nothing fancy.
• Managed components /objects –This is not an OnBase term; I like to define configurable items within any product as managed components /objects. So to all the “OOP” guys I apologies as this might annoy you; technically their meaning is different in the “OOAD” world.
• Metadata/attributes/properties – I tend to treat these terms as synonyms. Components/Objects have metadata/attributes/properties that can change the behavior of the Components/Objects. Once again I apologies to the “OOP” guys for not making a technical distinction within this blog.

Basic Configuration steps in OnBase 

 

Define Document Type Groups (DTG)/Document Types (DT)

OnBase allows you to define categories (two levels of categorization) to categories the documents that are taken into the imaging system. OnBase refers to those two categories as “Document Type Groups (DTG)”, the parent category and “Document Types (DT)”, the sub-category. 

It then allows you to configure attributes (metadata) for the Document Types which allows you to control various aspects like

• What User Groups/Users (UG/U) have access to the Document Type (Security)
• What is the default document content type (Image, Word, Excel, PowerPoint etc), you can define any document content type in order to display the documents in their native program (like MS Word, MS Excel etc), OnBase allows you to associate the content type in a similar manner to how you associate MIME types for your browser.
• What workflows these DT belong to?
• What’s the retention period for the DT
• What products and privileges the UG/U have access to?
• Etc.

Define Keyword Type Groups (KTG)/Keyword Types (KT)

Just like how DTG allows you to group DT into a collection of related items. KTG allow you to group KT into a collection of related items.

KT allows you to define the keyword fields that will be used while indexing the documents. Various data field types like date, varchar, numeric etc. are available. Format mask capabilities are also available. You can specific if a KT is required or optional. Quite a few configuration options are available for KT.

Once you define KTG/KT you can assign them to DT, that way when you try to scan in a document or import an electronic document you will be asked to enter the keyword fields that will be used to index the document. Here is the simple rule to remember when it comes to configuration

• Document maps to DTG/DT
• Index/keyword values maps to KTG/KT

 As simple as that

Define User Groups/Users (UG/U)

Define User Groups and users so that you basically can control authorization based on roles that the users are assigned to. Pretty basic nothing fancy here

Assign User Groups/Users to (DTG/DT) and configure what product components and operations they can access/perform.

Once you define the UG/U; you can assign them to DTG/DT to configure access rights. You can also while defining the UG configure access rights that the UG has to various product components

Access rights examples include

• What actions the UG/U can perform (document retrieval, document re-index, document delete etc.)
• What Product access the UG/U have (Workflow access, Record Management access etc.)

Custom-Code:

If you are planning to custom code an Imaging System (IS), then basically design categories (DTG/DG), and Keyword types (KTG/KT) in your system database and then define relationship between them. Expose User Interface (UI) for defining and creating the categories and keyword types along with their relationship; simple right. Convert this into an administrator function that you can then control through UG/U.

With regards to user groups and users; follow the same thought process. Make sure you provide integration with LDAP, custom table driven authentication/authorization; maybe integration with Active Directory via NTLM (Kerberos) might help.

 
For image manipulations all you need to do is purchase some good UI controls (browser plugins if you want to do it over the web) instead of building these UI controls from scratch; there are quite a few UI controls available in the market that allow you to perform image manipulations like zoom-in, zoom-out, rotate etc.  There you go you have a custom Imaging System

Logical Architectural components of Hyland

NOTE: The logical architectural diagram above does not show all the server components of Hyland. Hyland server components for the most part are scalable horizontally as well as vertically and “yes” Hyland server components can run on Virtualized servers (no more “hasp” – “hasp” is just a USB flash drive that Hyland was using with its earlier versions of OnBase to protect licensing).

Component	Description
Web Client	OnBase Unity Client: It is the Windows application of OnBase that is deployed over the web using the Microsoft ClickOnce technology and has the look and feel similar to Microsoft Office suite. It’s a hybrid solution, as its thick client deployed over the web and uses pure http (or https) traffic to retrieve data from the web server OnBase Web Client: Hyland provides HTML, ActiveX and Java versions of pure web based clients depending on what your needs are. OnBase Reporting Service Client:  This is a Windows application similar to OnBase Unity Client used for running OnBase Reports. Its web-deployed to the end-user workstation using Microsoft's ClickOnce Technology.
Thick Client	OnBase thick client and OnBase Desktop : OnBase provides thick client to access OnBase functionality. OnBase Configuration Client: This is a thick client that allows administrators to configure various components/objects of OnBase
Network Equipment	This is just a place holder I have in the architectural diagram for any of the following network equipment         Router         Load Balancer         Switches Depending on what’s there between your servers and the web client the exact network architecture will change; example if the web clients are public users then the network equipment will most likely be a router and then you will have a load-balancer after that (assuming you have multiple web servers to load balance; maybe you will have a Cisco ASA equipment also who knows)
Web Tier	This will be hosting the Hyland web application component (basically a virtual ASP.NET application) and it can be load balanced. The web server will be IIS.
Firewall	You will have a firewall between your tiers for security. If your web tier is in the DMZ you may also have VLAN configured and a much strict firewall rules; maybe a router in between tiers.
Application Tier	This will be hosting the Hyland Application Server component (basically a virtual ASP.NET application which exposes a web service) and it can be load balanced as well. The web server will be IIS. NOTE: You can merge the web and application tier if you do not have any DMZ zone to save cost.
Data Tier	The data tier has Database servers: Both SQL Server and Oracle databases are supported by Hyland OnBase. Hyland OnBase also supports High Availability (HA) configuration features provided by both databases. SAN/File Server: The database servers only store the metadata of OnBase images (metadata like keywords, pointer to image, user account information etc.). The Actual images/documents are stored in SAN/File Server in their native format (which is TIFF for images). OnBase uses logical units called Disk Groups for storing images. Disk Groups allows us to configure various storage aspects for documents (images).

Custom-Code: If you want to design your own Imaging system all you have to do is define metadata tables and place the images on SAN/File Servers and then use your Application layer to access the images; expose the application function via API. Use the web tier to access these application layer API (Examples of application layer API: WCF for windows or EJB for Java); use ASP.Net or Windows application or Java or JEE (JSP/Servlets) for User Interface.

 

Hybrid Features of OnBase

In this section I am planning to focus on some of the features of OnBase that make it compete with the ECMS, BPMS and BRMS system vendors.

Record Management

Record is defined within OnBase as a collection of related documents and guess how you relate them; “yes” – Folders. Refer to my “Glossary” section within this blog for what “Folders” and “Folder Type” mean within OnBase world.

Here are the steps that you will perform for Record Management (RM)

Define folder structure

Refer to my “Glossary” section within this blog for what “Folders”, “Folder Type”, and “File Cabinets” mean within OnBase world.  You need to define the folder structure for only one thing which is to arrange the various documents into structures that can then be managed. Think of your office file cabinet that’s what you are defining here; as simple as that. 

Define Hold Sets

They just define reasons why a particular folder within Record Management should be kept on “hold”. When a folder is kept on “hold” regular operations like adding or removing a document cannot be performed, Retention plans will also be on hold. Hold Sets let you define the reasons why the folder is kept on “hold”, this allows you to tag and track the reason for keeping the folder on hold.

Define Retention Plans/Retention Plan Sets

Retention plan let you define what “Actions” should be performed when “a certain time has elapsed” or an “Event” has occurred.
Within Record Management a folder transitions through various status; the status values are explained below

Open: Regular operations like adding and removing a document can be performed on the folder. This is the starting status for the folder.
Cutoff: This status signifies that the retention period for the folder has started.
Close: No operation can be performed when the folder is in closed status; except if you are an administrator and want to correct the contents of the folder for whatever reasons.
Final Disposition: In this status the record contents could be destroyed, purged, or retained permanently. There is an option within OnBase to set an approval process before this status and eventual outcome of the documents.
 

• Open: Regular operations like adding and removing a document can be performed on the folder. This is the starting status for the folder.
• Cutoff: This status signifies that the retention period for the folder has started.
• Close: No operation can be performed when the folder is in closed status; except if you are an administrator and want to correct the contents of the folder for whatever reasons.
• Final Disposition: In this status the record contents could be destroyed, purged, or retained permanently. There is an option within OnBase to set an approval process before this status and eventual outcome of the documents.

Retention plan let you control these transitions. When a folder is first created it’s always in the “Open” status which means users can add or remove documents from that folder. The figure below shows the status transitions that happen within a folders life-time

 

Retention Plan Set allows you to assign at runtime different Retention Plan to “Folders” of the same “Folder Type” depending on the folder’s value for a specific Keyword Type. Did I mention that Folder Types can be assigned “Keyword Type” seems obvious right? Remember my windows folder analogy. You need some way of creating the folder hierarchy branching; in windows operating system we have “inodes” as one of the metadata for the folders.  In OnBase, we use Keyword Types to Auto-folder the “folders” and guess how the Documents end up in the folders; based on Auto-folder “Keyword Types” defined at the “Document Type” level with Keyword Types that are common to both the “Folder Type” and “Document Type”

Create Event Sets/Events

Record Management allows you to post events to managed folders so that the status transitions as well as Retention Plan settings can be changed. Event Set is just a way to group related events together. You can post events manually to managed folders or use workflow (life-cycle) actions to post it.
Events within the context of Record Management allow us to change the status of the folder or redefine/override the Retention Plan defined for the managed folder.

Configure Document Types/User Groups

You need to configure “Document Type” for auto-folder and then associate them with managed Folder Type using common Keyword Type. Basically all you are trying to do here is define folder structuring based on Keyword values so folders can be automatically created based on keyword values of the documents that are getting scanned-in or imported and in doing this configuration you basically place those documents in the managed folders for Record Management as those documents are inputted into the imaging system via various input channels.
NOTE: The actual documents are physically located in one place in the imaging system (SAN/File Server) it’s just that their pointers are referenced in the managed folders (this saves disk space); even in the database server, only the image pointers are kept; the actual images always reside in “Disk Groups” which are logical units defined in OnBase with physical location being the SAN/File Server.

You also need to configure User Groups/Users that need access to Records to ensure security measures are in place.


The diagram below summaries what steps have being described so far when it comes to configuring Record Management within OnBase.

Custom-Code: You can define your own terms for Folder Type and Folders; you then proceed to define metadata at the “Folder Type” level to associate various “Document Type” with the “Folder Type”. You can then define retention plans as configurable component/object within your custom Imaging system and then use Timer services in Java EJB or WCF services in C#, VB.NET to monitor Retention policies defined for various Document Types. In fact you can use Java EJB/WCF timer concept for other background operations that you want to perform within your custom Imaging System, and of-course execute these services/components/objects in the application tier please; remember you have more than one tier architecture to design and use for scalability. Who knows you might compete with Hyland and IBM.

Document Retention

The main purpose of Document Retention is to save disk space and also ensure that documents are appropriately purged for legal reasons.

Document Retention is managed by defining Document Retention Processor, which is basically a batch job that can either be executed on-demand or scheduled to run at defined time intervals.

Document Retention can be of the following types

• Static Retention
• Dynamic Retention

Static Retention allows documents to be purged without any evaluation phase. Basically what it means is when the Retention batch job runs and it finds document that have static retention type defined for their Document Types; the batch job will purge the documents if their time-interval has elapsed.

When Document Retention is defined as ”dynamic”, then the batch job goes through two passes, in the first pass it will evaluate the retention, following three evaluation options are available

• Workflow queue is used for evaluation
• VB script does the evaluation
• External program (remember OnBase is Microsoft technology oriented so guess what external programs it can invoke – DLL)

Once the evaluation has occurred and the documents are allowed to be purged; the batch job in its second pass will purge documents that have being identified as being “OK” to purge by the evaluation process and their retention time period has elapsed.
Document Retention is defined at the Document Type level; seems logical.  Document Retention is a separate module from Record Management module although both have retention capabilities; the latter is meant for record keeping purposes while the former is used when you truly wish to purge documents for good reasons.
If a document is in a Record Management folder that is not in “Open” state the Document Retention (DR) processor will exclude the document in its evaluation/purging passes; seems logical; remember the other status for records (Cutoff, Close, Final Disposition) imply that there is a retention plan being executed and hence the documents should not be touched by any other modules besides the Record Management module since they are contained in the folder “managed” by the Record Management module.

Workflow

In my blog titled “ECMS systems, IS systems, BPMS systems and BRMS systems”, I had introduced the terms document-centric versus process-centric workflows. OnBase provides the former. OnBase calls workflows “life-cycles”. So let’s get started

The workflow capabilities within OnBase are typically triggered based on documents that are scanned in or electronically uploaded; hence I refer to the workflow engine as document-centric.

Workflows are configured using the OnBase Configuration thick client (it’s a thick client used for administration of various components and modules of OnBase including workflow, it’s not a web client and is typically accessible to administrators only).

NOTE: OnBase allows you to configure a lot of metadata for components/objects that it maintains; I am not planning to mention every metadata that you can configure for various components/objects defined within OnBase in this blog as that will result in me merging all the system manuals into my blog (not a great idea); I will however give you some examples of metadata to give you some sense of what you can configure

Typically you start with

Define and Configure Life Cycle

Defining and configuring life-cycle (just a fancy term for workflow, it’s just that life-cycle is a managed object within the realm of OnBase with metadata that needs configuration; nothing special). Examples of some of the metadata that you can configure at lifecycle level include User Groups, Work Folders

Define and Configure Queues

Within the life-cycle we then proceed to define the queues. The queues act as place holders for the documents that need to be routed through the life cycle. You can define as many queues as you want within the life-cycle depending on how many stops you want the document to go through before it exits the life-cycle. Queues can be standard or load-balanced. With load balanced queues you have multiple options of load balancing the work load; for example you can load balance based on keyword types, keyword value, rules etc. OnBase allows you to define Rule queues that are used in conjunction with the Business Rule Engine module of OnBase.

Define and Configure Work

Within OnBase life-cycle you can define three types of work

• System work
• User Work and
• Ad-hoc Task

Work can be executed by “Actions” and conditions for executing the actions can be defined by “Rules”. OnBase also allows you to group “Actions” and  “Rules” into “Task List”

Define Document Types/User Groups/Users

You basically have to configure which document types are associated with the life-cycle and then of course associate user groups/users to life-cycles, queues and Document Types.

There you go it’s as simple as that. The figure below illustrates what I have being saying so far.

A few things to remember when it comes to workflow engines that are document centric

• They are document centric (well that’s news right)
• They do provide API to interact with external systems. OnBase provides various API but they are still document-centric not process-centric

Remember the above two points that I keep repeating across my blogs that I write on this topic; in a separate blog I will be speaking about BPMS system products like IBM BPM, Oracle BPM where I will point out the difference to make it clear to you when to use document centric workflows versus process/business centric workflows. 

I also plan to write a blog on IBM FileNet a competing product with Hyland OnBase just to give you a flavor of other vendors in this space. To all those advance users (the techies), here is what it boils down to; if you want to work in an environment that is closer to Java technology stack like Java, Linux, AIX, Oracle etc. then go for IBM FileNet; if you want to work with an environment that is closer to Microsoft and its technology stack like C#, SQL Server, VB.NET, VBScript, Windows Server O.S. etc. then go for Hyland OnBase. 

NOTE: Both databases (Oracle, SQL Server) are supported by either product (Hyland OnBase, IBM FileNet); it’s just that you are better off using one set of  technology stack over the other and that decision depends on what your IT department’s Enterprise IT Architecture (EITA) vision is, which staff skill sets you already have. Remember finally it’s you who will have to maintain the systems not the product companies.

EDM Services

Electronic Document Management module of OnBase allows you to store, change and manage other document types particularly Microsoft Word, Excel and PowerPoint by defining Document Types for the documents and utilizing plug-ins to allow document manipulation directly from Microsoft Office product suite while maintain the documents in a central OnBase Imaging repository. By enabling revisions you can also do version controlling of these documents.

Obviously you can initiate workflows based on document imports. I am not planning to elaborate the EDM Service feature of OnBase as I prefer to explain the same in my blog that is going to be dedicated for Microsoft SharePoint (MOSS). In my mind Microsoft SharePoint is a better solution when you want to do Enterprise Content Management outside of Image content. I prefer an ECMS system for managing generic contents not just Image contents.

API

OnBase has tons and tons of API and it’s due to the fact that their product has gone through series of transformations to keep pace with Microsoft’s changing architectural and technology focus. Microsoft started with windows applications (VC++, VB etc) then shifted to web based applications (ASP, ASP.NET). Its middle ware shifted from MTS/DCOM/COM+ to .NET Remoting and now WCF. Even within its windows application it did some transformation from simple windows forms to WPF. Within the web world, we started with ASP, then ASP.NET, web services, AJAX, SOAP, SOA and now Cloud (Azure)

OnBase is doing catch-up. So its API libraries are spread all over the map. I hope they start phasing out some of their older API sets as well as their product base tied to those older technologies. Easier for me to say that, but I understand how it’s difficult for a product company to do that.

Following diagram shows the various API options available within OnBase

Automation, OnBase Client and Desktop API are provided to allow integration with the OnBase Thick client or the Desktop client.

Pop API (DocPop, FilterPop, FolderPop, ObjectPop) provides you with the fastest integration with OnBase with minimal coding. Example: DocPop basically allows you to retrieve documents via a web URL by passing document retrieval parameters as query strings. Simple and quick integration.

Hyland.Services API is a web service that exposes SOAP request and delivers SOAP responses to get information from OnBase.

Unity API is an object oriented layer on top of Hyland.Services API to abstract out the SOAP handshake complexities from programmers. Other than that there is no real difference between Unity API and Hyland.Service API. I have a suggestion for Hyland; please rename this API to be anything other than “Unity API”. It confuses a lot of folks as Hyland has a client-application called “OnBase Unity Client” which has nothing to do with Unity API.

The following table provides details regarding the programming options available for each API set

API	Programming option
Automation API	VB Script
OnBase Client API	COM
Pop	URL
Desktop API	COM
Hyland.Services	COM, .NET or Java
Unity	.NET

Reports.

OnBase provides quite a few reports that can be accessed via the Reporting Services Client which is a hybrid client application that is deployed over the web via ClickOnce technology (similar to the OnBase Unity Client).

OnBase categories reports into groups, following are some of the per-configured groups within OnBase

• Configuration Reports
• Document Knowledge Transfer Reports
• Document Management
• Document Processing
• Licensing Reports
• Medical Records Reports
• Physical Records Management
• RAC Reports
• Records Management
• Scanning Reports
• Security Reports
• Storage Reports
• Transaction Log Reports
• Usage Reports
• User Reports
• Workflow Reports

Each of these Groups have multiple predefined canned-reports. These predefined  canned-reports exist for Oracle as well as SQL Server database.

Note: if you want to custom code a report using SQL, T-SQL, PL/SQL, stored procedures etc. then OnBase Reporting configuration allows you to do that by making calls to these external database objects through the database drivers; the report layout rendering can then be done from the Report Configuration UI provided by OnBase.

Miscellaneous/etc.

Besides what I have said so far, Hyland likes to create business domain specific modules and there are quite a few of them, some of them are listed below.

 

Summary

In this blog I focused on the basic strengths of OnBase and did not spend time providing specifics on BAM, BPM, and BRE features of OnBase because I think there are better products available in the market for that and I will provide details of those products in my other blogs. I also provided tips on how to Custom-Code an Imaging System, “yes” the tips were too simple but my intent in this blog was to provide pointers, tips and hints to Software designers.

Hope you get to use OnBase

ECMS systems, IS systems, BPMS systems and BRMS systems

Enterprise Content Management System (ECMS), Imaging System (IS), Business Process Management System (BPMS) and Business Rule Management System (BRMS)

So what really are they and how do we decide which one we really need. I am using this blog as an attempt to help folks by sharing my professional experience so they can decide which way they want to go. I will then in a couple of follow up blog(s) speak about some of the products I have used in my projects in the past (IBM - FileNet, Hyland – OnBase, Microsoft SharePoint - MOSS, IBM Lombardi (now IBM Business Process Manager), Oracle BPEL Manager (now Oracle BPM Suite), IBM ILOG, JBoss Drools etc.) I will dedicate separate blogs for each of these Commercial off the Shelf Products (COTS), as these products need their separate spot lights and I will be more “tech-ish” about it in those blogs (if that really is a word)

In Short, this blog is focusing on business audience with limited technical jargons, no promises. So let’s begin with the a few things that will help you

Basic Guidelines, Misconceptions for Business users

• Do not get bogged down by technical acronyms and jargons
• If you as a business user feel that these systems will do the work for you with a wave of a magic wand then just remember one acronym (ERP). Confused, the systems will help you get there half way you still need to walk the rest of the journey
• Do not be under the misconception that technical staff all of a sudden will not be needed (Trust me on this one).
• Do not assume that these systems will completely eliminate any expensive custom coding or need of technical staff.
• Do you want to custom code a solution or use a Commercial off the Shelf Products (COTS), do your “make-versus-buy” decision before you go either route.
• Do some feasibility study and possibly some Proof of Concept (POC); I strongly recommend POC.
• Custom coding with disciplined engineering can give you what you want; Commercial off the Shelf Products (COTS) can give you a jump start. I can make an argument for either approach, it all depends on your requirements and what you want, hence do some feasibility study and possibly some Proof of Concept (POC).
• Take baby steps when using these systems in your organization; Incremental modernization. Start with Pilot projects first instead of big bang unless you already know what you are doing. These products will definitely change your existing business processes and business practices mostly for the better
• These products are means for improving your business practices/processes; you do not have to be enslaved to them. Paper and Pencil still work for many companies. If you do not intent to use them and you do not acknowledge that business practices/processes need iterative process management, monitoring and control, then do not buy them.
• Define some Key Performance Indicators (KPI) and yes use metrics that make sense to the business community not some techies like me. We can help you get to the Well but you have to drink the water.
• The KPI will help you define the Return on Investment (ROI). If you don’t know what you want and how to measure it then just go and burn some cash at least that will keep you warm in winter or donate it to me I can make the money disappear for you.

I already scared you with a lot of don’t but really it’s not that bad, follow some simple rules to get the best bang for your buck

• Whenever you buy any Commercial off the Shelf Products (COTS) remember to use as many features as the product provides out of the box
• Try to adapt your business processes around the COTS products, whenever you want to change anything that the products do not provide out of the box, you end up with custom codes. This does not mean that these products do not provide some customization; the mantra is Configuration and not Customization. The more you steer away from Configuration that these products provide to tweak their components and go towards customization the more technical staff you need.  
• This does not mean that you have to accept any change the product is forcing you to do for your business as far as existing business processes/practices is concerned. But if the product is providing something that you can adapt your business processes/practices to; then do that as it will save you money in the long run. If you cannot do that then you are better off going the custom coding route minus these products. I am not implying one approach is better than the other but just merely suggesting that you do your homework.
• Most of the time in these products, the custom code ends up surfacing in following areas

o   User Interfaces (UI)

o   Integration with existing systems from where you need to pull or push data to.

o   These products do provide you with some configuration tools but you will need some good Graphic and HTML designers to make your User Interfaces look pretty. With regards to integration with other system, think about how tight an integration you want with the other systems, do you need transactions (in short your business data) to flow in real-time, near real-time, overnight. These decisions will impact what middleware you end up with. It can be as simple as a file transfer to as complex as using an Enterprise Service Bus (ESB). You pick. My 20 cents – check to see if your business needs are really real-time and if not try the old school route of file transfer (secured ftp, of course).

This is how I see the four systems in use.

Enterprise Content Management System (ECMS)

ECMS systems primarily focus on managing the content for your organizations and provide workflows and portals that facilitate Content management and collaboration. Content could be anything ranging from Word documents, Excel sheets, Forms, Images, Business Data etc.

Most of the ECMS COTS products provide you with basic workflows, website portals for collaboration, document sharing, wiki etc.

Microsoft SharePoint is an example of ECMS COTS product. I will speak about this product in a separate blog and provide insight into its features, its configuration capabilities as well as what you get out of the box.  Microsoft Office SharePoint (MOSS) provides lots of out-of-the-box templates that help you jump start.

There are SharePoint templates that software firms sell which you can purchase instead of custom coding the templates on your own using C#, VB.NET (basically any programing language provided with Visual Studio.NET), again make sure you have taken your make-versus-buy decision, and you know where I stand on this decision, “everywhere”. Seriously one shoe does not fit all.

Basic features provided by ECMS systems include (but is not limited to)

• Content management User Interface
• Content storing with attributes tied to the content; these attributes can then be used to retrieve the content or channel workflow decisions/paths
• Workflows to manage content
• Dashboard and web portals to present users with various statistical information
• Security based on authentication/authorization models
• Standard templates for various business needs
• Integration with Microsoft Office Suites or OpenOffice (although SharePoint prefers Microsoft Office; I am shocked)
• Reports
• Adapters to connect to other systems
• API (Application programming Interface)

Imaging System (IS)

I basically consider Imaging Systems to be a sub-set of ECMS system. In my mind The  Content – the letter “C” in ECMS acronym can be an “image”. Yes there is more to this simple interpretation of mine when it comes to images but isn’t it all revolving around “Content” (in this case “image”) Management. I have used Hyland OnBase and IBM FileNet for Imaging and I am going to dedicate separate blog for the two Imaging COT products. Some of the basic features provided by IS systems include (but is not limited to)

• Image Management User Interface
• Image manipulation with features like
• Annotation
• Redactions
• OCR
• Bar coding
• There are many other nice features but you got the point
• Image storing with attributes tied to the image; these attributes can then be used to retrieve the content or channel workflow decisions/paths
• Full text Search of images that are OCR
• Workflows
• Security
• Reports
• Adapters to connect to other systems
• API (Application programming Interface)

If you look at the list I have here versus the list I have for ECMS there are many overlaps, it’s just that Imaging systems are tailored for managing Images as their primary “content” versus the ECMS system which deals with “any” content in general.  Because Imaging systems primarily manage “images”, Image manipulation related features are best provided by Imaging Systems versus ECMS systems which are tailored for a generic Content management (not just Images).

Business Process Management System (BPMS)

These systems primarily provide you with tool-sets that allow you to model your business processes (existing or proposed ones), perform simulations to identify potential bottlenecks and then finally do the plumbing by modeling System processes(services). Basically BPMS allows you to do process improvement. I have used IBM Lombardi (now IBM Business Process Manager), Oracle BPEL Manager (now Oracle BPM Suite) and plan to dedicate separate blogs for these products. The real difference between BPMS systems and “ECMS”/”IS” systems is that BPMS systems provide you components and tool-sets that allow you to model the business processes and then define system processes(services) all in one integrated environment (IDE). The idea is business analyst/Functional Subject Matter Expert (SME) create the business process models using the same integrated environment and then developers take that business process model to create system processes/services under the business process models using the same integrated environment (IDE). Since both roles work from the same integrated environment (IDE) and do not pass word documents that define the functional/technical design, there is no Lost In Translation (or at least that’s the goal). Some of the basic features provided by BPMS systems include (but is not limited to)

• Dashboards
• BAM - Business Activity Monitoring
• Simulation capabilities for identifying potential bottlenecks within business processes.
• Security
• Of course workflows
• Business workflows
• System workflows
• Reports
• Adapters to connect to other systems
• API (Application programming Interface)

So the million dollar question is? When do we need BPMS versus the other systems (ECMS/IS) when those systems also have workflows? I am going to introduce two new terms

• Document Centric workflows – use a ECMS/IS system

The workflow engine is typically tailored around the content that is managed by the ECMS/IS system

• Process Centric workflows – use BPMS system

The workflow engine provides you with better capabilities to model business workflows and then system workflows and is definitely much more flexible that the ECMS/IS systems as the primary function of BPMS system is business process improvement and hence its tool-sets and features are not Content/Document centric unlike ECMS/IS systems

 

In the BPMS world there are some open standards which hopefully proprietary BPMS product companies will adhere to

• BPMN - Business Process Modeling Notation a standard that helps business community model business processes in a consistent graphical notation that is well understood by any COTS product
• BPEL - Business Process Execution Language (BPEL) a standard executable language for specifying system actions within business processes. If propriety COTS product can execute BPEL then we can achieve cross product integration.

Just remember my 20 cents, if the proprietary BPMS system is not fully compliant but you as a business community do not care then its fine. Who cares what the engine really does right?

But do make your decision based on document centric versus business centric workflows when you decide to go ECMS/IS route versus BPMS route.

Business Rules Management Systems (BRMS)

Well this is really the odd apple in this bunch as it really can be used in conjunction with the other three. The idea with BRMS systems is that they abstract out the business rules into a business friendly User Interface (UI) so the business community can define the rules and everything is peachy. Seriously, BRMS provides tool-sets that allow business users to define rules and then let the technical folks do the plumbing all of this from the same integrated environment. So in that sense they work just like the BPMS system it’s just that in BRMS system, the primary focus to define, design, built and manage are “Rules” and not “Processes”. I have used IBM ILOG, JBoss Drools and plan to dedicate separate blogs for these products.

The above diagram provides a simple cheat sheet for the business community to use while deciding what systems they really need to address their business needs. Again this is an over simplified cheat sheet; since there are COTS products that provide hybrid features and in fact all of the above mentioned systems are provided as a collective suite by each major vendor in this space.  And when you are faced with a COTS product that provides hybrid sets of features I suggest do some feasibility study and also Proof of Concept (POC) before you pick one (do not be cheap spend some money to do feasibility study and POC as these products may make your business grow efficiently or they may end up being a maintenance nightmare)

Summary

My attempt in this blog is to help you understand the 10,000 feet view of the four systems; what they provide and how they can improve your business efficiency so you can focus on changing business needs by utilizing these systems. In this blog, I tried to steer as far as possible from technical jargons and also did not provide any detailed listing of features each system provide because quite frankly you can just request a Pamphlet, PowerPoint slide from sales representative of any product company (and I mean it when I say “any”) to really understand what these systems provide at a 10,000 feet level.

All the COTS products in this space say the same buzz words; the proof of the pudding is in the eating; I suggest look at Gartner’s report for vendors in this space and then pick the top 5; that’s what I do when I go to BestBuy to buy any new fancy gadgets; instead of Gartner I just look at the Consumer reports or CNET for my research.

SQL Server Backup/Restore – Recovery Models - Replication – Log shipping

In this blog I am going to speak about backup and restore, recovery models within SQL Server, replication choices and then spent some time on log shipping (one of these days I will write a similar blog for Oracle/DB2 database). In this blog, I am going to just scratch the surface as far as Replication choices within SQL Server are concerned. I plan to write another blog dedicated just for Replication choices within SQL Server as it’s a very vast and interesting topic that needs its own blog space. I would however like to mention Replication in this blog as it completes the whole picture.

There are three types of recovery models for SQL Server

• Simple
• Full
• Bulk logged

Before I explain these three recovery models I need to touch base on a concept that is generic across databases and it involves data files, log files and undo files. I am using general terms like data files, log files and undo files as the concept is quite similar across databases; the difference is in the implementation details of how databases store persistent and transactional data across these three files (I knew if I had paid attention during my data structure lectures in college days I would have a database in my name, thinking big).

Data files, log/transactional files and undo files

I am oversimplifying the explanation here, if you really want to get into the details I would suggest buying some books from the author “Thomas Kyte” just Google him, he is a guru in Oracle DB but by reading his books you will gain a lot of insight into what I am about to say in a few sentences. Each database has many background threads called worker threads that write data into one of the three files (data file, log file and/or undo file). In general, whatever is written in data files is committed/persistent information, whatever is written in undo files is information the database can use to perform undo operation and the log files also called as the transaction files hold transaction details of committed as well as uncommitted data. Let me emphasize on the “uncommitted” part of the log files, the log files basically has all the information it needs to bring the database back to a consistent state. Typically when you issue a commit statement from T-SQL or PL/SQL the information gets written into log file first and some background thread then writes it to a data file. Undo file comes into picture only for uncommitted data. But even for uncommitted data the database threads write the “undo” information in the log/transaction files --- so yes the transaction/log file also contains uncommitted data, remember this, it took me a while to understand this minute piece of information. These background threads are named differently and are database implementation specific.

Backup types (Full database backup, differential database backup and log file backups)

Before I start explaining the recovery models we need to know that SQL Server allows full database backup, incremental database backup (also referred to as differential backup) and log backups (it also provides other backup type variations, like file backup, but I am going to leave that from this blog topic as its not as relevant for the concept I am trying to explain here). The full and differential backups do not perform log file backups, they just perform data file backups where as the log backups perform log file backups only.

A full backup is needed before differential and/or log backup can be started. The full backup is referred to as the differential base (basically a starting point) for differential backup. A few rules that you need to know about these three backup types before we get into the recovery models

• A full backup is mandatory for differential as well as log backup
• A differential backup is not mandatory for a log backup but a full backup is mandatory
• During restore, you just need to apply the last successful differential backup as each differential backup contains information that was captured in the previous differential backup, you do however need to restore the full database backup (referred to as the differential base) without which the differential backup is useless
• For database restore, look for the last successful differential backup file and for transactional/log backup you need all the transactional/log files since the last full and or differential backup. NOTE: You always need a full backup as a starting point or base for differential as well as log backups.
• You can restore a database without needing a differential backup as long as you have the last full backup and all the log file backups since then (basically all the log chain intact) and that you haven’t changed the recovery model since then.
• A log backup is not supported for simple recovery model (hence I do not suggest this for production environments)

Typically I would recommend one weekly full backup and one daily differential backup with many log backups during the day with 15 minutes or 30 minutes time interval between each log backup (This is my production environment recommendation). It should be noted that how these three backups’ types are used will help us determine which recovery model we plan to use.

Recovery Models
With this understanding lets get to the recovery models. NOTE: I have left a lot of details out of the data/log/undo file explanation for simplicity reason. Just remember the rule- log/transaction file always has committed as well as uncommitted data and has enough information to recover a database (a concept that you need to digest when I speak about log shipping later on in this blog).

Simple Recovery Model

It is one of the most basic types of recovery model and should never be used beyond testing and development environment. No transaction log is backed up and the recovery is based on last full database backup or last differential backup with a differential base as the starting point.

Full Recovery Model

This is my recommended recovery model for production environments. The recovery model allows data file as well as log file backups. I am going to steal a nice picture from MSDN website which shows how to minimize work loss in a production environment and it aligns with my recommendation for the production environment

Basically this pretty picture is saying the following

Have a full backup at regular intervals (I suggest once a week)

Have a differential backup at regular intervals as well (I suggest once a day)

Have log backups frequently (I suggest every 15 minutes or 30 minutes apart)

Let’s talk of possible server failure scenarios

Scenario 1
Say the server fails right after transactional/log backup – t14; then you can restore the database using one of the following choices
Choice 1

Restore the database using “Full database backup up 1” – t1

Apply all the transaction logs from t2, t3, t5,t6 all the way to t14 and you have recovered the database right up to that point (no need to apply the differential backups in between the transaction logs)

Choice 2

Restore the database using “Full database backup up 1” – t1

After that apply the last differential database backup (Differential backup -3 – t10” in the picture --- NOTE for differential backups you just have to apply the last successfully differential backup (no need to start from differential backup 1 and then 2 and then 3, just do 3)

Apply the remaining transaction log t11, t12, t14

Choice 3

Restore the database using “Full database backup up 2” – t13

Apply the remaining transaction log t14

Of these choices the last choice 3 is the fastest way to recover the database.

Scenario 2
Say the server fails right after transactional/log backup – t14 but there are some active transactions that are not backed up (Example, say your transaction log backup time interval window is 30 minutes and say after the t14 transaction backup your server fails in the next 20 minutes before the next transaction log time interval window – t15, which is 30 minutes apart from t14)
In this scenario all the choices mentioned in the earlier scenario can be used. Here is the slight deviation, in order to recover the active transactions that are not backed up after t14; you will have to apply another transaction log file called the tail-log.

What is tail of the log or tail-log file?

Basically after the server failure if you are able to bring up the server and the SQL server instance on the server then you can backup the current active transaction log file first using the BACKUP T-SQL command with the NORECOVERY option (more on these options for BACKUP T-SQL command later). You do this before you start the restore process. This backup is basically the tail of the transaction log after t14 transaction log backup and if you are able to back up this tail then after performing the restore using any one of the three choices mentioned above, you should just apply this tail-log in the end after successfully applying the t14 transaction log file. By doing this you will be able to restore the database to the point of failure.
If for whatever reasons you are not able to backup this tail backup file then what it means is that all the data/transaction that was active after t14 will be lost and you will not be able to restore the database to the point of failure. Hence it’s important to narrow the time-interval window between two transaction log file backups (I suggest 15 minutes or 30 minutes time interval for production environments).

Bulk logged Recovery Model

I am not going to spend much time explaining this recovery model as it’s a variation of the full recovery model and should be used only if you have many large-scale bulk operations going on in the database. Basically in such environments this recovery model tries to have a trade-off between large log files versus increased work loss exposure as bulk-logged recovery model does not log every transaction into the log file for bulk operations. This also means that you cannot have a point-in-time recovery as the complete transaction is not logged into the log file for bulk operations. It’s recommended to use bulk-logged recovery model just before large-scale bulk operations are performed and then once those operations are completed successfully, to flip the recovery model back to “full recovery model”.

BACKUP and RESTORE T-SQL command options

The BACKUP and RESTORE T-SQL command has so many options that I will be focusing on only those that are relevant to this blog. Visit MSDN for a complete list of options.

If you wish to take a full database backup you can use the following BACKUP command

BACKUP DATABASE SAMPLE_DB TO DISK = @full_bu_fn WITH COMPRESSION

If you wish to take a differential database backup you can use the following BACKUP command

BACKUP DATABASE SAMPLE_DB TO DISK = @diff_bu_fn
WITH DIFFERENTIAL, COMPRESSION

If you wish to take a log backup you can use the following BACKUP command

BACKUP LOG SAMPLE_DB TO DISK = @log_bu_fn WITH COMPRESSION

The COMPRESSION option is available for SQL Server 2008 and above (refers to MSDN for the exact SQL Server editions within SQL Server 2008 that supports this option).

The RESTORE T-SQL command allows the database to be restored to the following state

WITH RECOVERY

The database is available for read/write. Once a RESTORE command is executed with this option it’s not possible to apply any more restores to the database beyond this recovery point. Use this as the last restore option when you are in the process of recovering a database.

Example
RESTORE DATABASE SAMPLE_DB_DR
FROM disk = @fullrestore_fn with file = 1,
MOVE 'SAMPLE' TO @mdf_full_fn, move 'SAMPLE_Log' to @ldf_full_fn, REPLACE, RECOVERY

REPLACE -- will overwrite any existing database schema named --“SAMPLE_DB_DR”.

WITH NORECOVERY

Use this option when you are in the process of performing a database recovery but haven’t applied all the backup files yet to the database

Example
RESTORE LOG SAMPLE_DB_DR
FROM disk = @local_logrestore_fn
WITH FILE = 1, NORECOVERY

FILE – refers to the backup location within a file. If a backup file has only one backup then FILE is always “1”.

WITH STANDBY = @undo_filename

This option can be used in place of NORECOVERY option if you wish to execute select statements on the restored database while you are in the process of restoring the database. This is also used as a cold-standby option for reporting purposes (more on this when I speak on log-shipping). Basically STANDBY and NORECOVERY are mutually exclusive and the minute differences between the two are listed below

• STANDBY allows the users to connect to the database and run SELECT statements against it. NORECOVERY will not allow any SELECTS to be performed on the database. Use STANDBY if you wish to perform some reporting on the database. NOTE: You cannot create any additional tables or stored procedures in the STANDBY database; it’s in a read-only state.
  
• For STANDBY you need to mention a temporary UNDO filename where all the undo information is stored. Basically the reason why the NORECOVERY mode does not allow SELECT is because the database can have uncommitted data after the restore operation is performed on the database with the NORECOVERY option, with STANDBY the uncommitted data is stored in the UNDO file and the database needs this UNDO file for subsequent restores. Since the UNDO information is in the UNDO file this mode allows SELECT statements versus the NORECOVERY mode.
  
  If you have started getting headaches with my explanation of NORECOVERY and STANDBY mode then just remember this STANDBY allows SELECTS and keeps the database in read-only state. NORECOVERY does not allow any SELECT.
  

Example

If you wish to restore the database in STANDBY mode with full backup file then use the following T-SQL command

RESTORE DATABASE SAMPLE_DB_DR
FROM DISK= @fullrestore_fn WITH FILE = 1,
MOVE 'SAMPLE_DB' TO @mdf_full_fn, MOVE 'SAMPLE_DB_Log' TO @ldf_full_fn, REPLACE, STANDBY = @UNDO_FILENAME

The @UNDO_FILENAME will store the uncommitted information. This file will be needed for subsequent restores. The following example shows how to restore the database in STANDBY mode by applying transaction log files.

RESTORE LOG SAMPLE_DB_DR
FROM DISK = @local_logrestore_fn
WITH FILE = 1, STANDBY = @UNDO_FILENAME

Note: Execute this command after the earlier RESTORE DATABASE command and use the same @UNDO_FILENAME otherwise the RESTORE will fail. SQL Server will use the information stored in the @UNDO_FILENAME as well as the @local_logrestore_fn – log file to restore the database in the STANDBY mode as well as write to the @UNDO_FILENAME any uncommitted data which can then be used for subsequent restores (if you have more than one log files just use the same command and @UNDO_FILENAME. SQL Server will know where to pull what information from).

LOG SHIPPING (OR TRANSACTION LOG SHIPPING)

If you are following my blog so far then log shipping is nothing but copying the backup files (specifically the transaction log files) to the standby/backup server and applying the backups to the standby server. Log shipping is a cheap and practical alternative to replication and should be used in case if you are short on budget. The main difference between Log Shipping and Replication is that the target database is not available for read/write operations. You can have read operations in the target database in log shipping if you select the STANDBY mode option from the RESTORE T-SQL command as explained earlier, you can never get write option in the target database with log shipping. Basically log shipping provides an active-passive failover option or active-cold failover option. Replication can provide active-active failover option.SQL Server provides a task to configure log shipping via its SQL Server Management Studio. I prefer to use SQL Server Management Studio for configuring log shipping, however, if you wish to custom code your log shipping solution. Then all you have to do is the following

Restore the target database with the full backup and NORECOVERY/STANDBY option (pick either STANDBY or NORECOVERY option based on whether you wish to do SELECT or not). Perform the full restore operation only once as this will act as a starting point for applying subsequent transaction log files.

Write a routine to copy transaction log files from the primary/source server to the target server (Remember you can recover the database from a full backup as long as you keep applying the transaction log files – Choice 1 in my earlier Full Recovery Model explanation; just do not use the RECOVERY option ever while you are applying the copied transaction log files on the target server. This will always leave the database in NORECOVERY/STANDBY mode.

In case of the primary server failure all you have to do is RESTORE the cold target server with the RECOVERY option, change your connection string in the application configuration file (“web.config” file for ASP.NET applications) to point to this new server and you are all set.

I wrote a few simple SSIS jobs which were deployed as jobs to run from the SQL Server Agent at scheduled intervals to perform log shipping.



The above figure shows the SSIS job that does the database backups (full, differential and log – NOTE: technically differential backup is not needed for log shipping). The time intervals are based on my recommendations for full recovery model which is a full backup frequency of once a week, a differential backup frequency of daily once and a transactional/log backup frequency of 15 minutes or 30 minutes apart.

The above figure shows the SSIS job that does the copy of the backup files from the primary server to the target server. I like to schedule it at intervals close to the transaction log backup interval of 15 minutes or 30 minutes window. This helps you ship/copy the log files as quickly as you can across the wire to the target server and then apply the log files on the target server. I have set this job to run at 20 minutes time interval.



The above SSIS job runs on the target server and basically applies the transaction log files on the target database. I schedule this SSIS job to run every one hour; even though this time interval is longer than the transaction log backup window or the copy transaction log file window. I am fine with this longer time interval as the copy SSIS job has already copied the transaction log files from the source/primary server to the target server directory at a shorter time interval of 20 minutes.NOTE: you do not need a SSIS job to do log shipping but I wanted to try something different so I wrote SSIS jobs. I would recommend using the SQL Server Management Studio’s log shipping task for performing log-shipping versus this elaborate method.

REPLICATION

I am not going to elaborate on replication besides explaining the three basic types as I think this topic needs its own blog and I plan to write one in the future.
The replication concept is based on subscriber and publisher model
There are basically three types of replication

Snapshot replication

It’s the simplest to configure. It’s a one way replication process which means a snapshot of the data is taken at regular intervals at the publisher and then that snapshot is replicated to all the subscribers. Replication from subscribers is not possible back to the publisher. Snapshot replication does not provide real time replication of data as the snapshot interval is what determines how stale the subscriber’s database is. Snapshot replication is suited for small volumes of data replication.

Merge replication

Both Merge and Transaction replication provide near real time data replication in an incremental manner. They both allow data to flow from the publisher to the subscribers as well as from the subscribers to the publishers. As the data flow can be two ways, they both provide conflict resolution options. Merge replication is better suited for updates at the subscribers end than transactional replication and is the preferred choice between the two replication types if you are expecting a lot of updates at the subscribers end. Merge replication is typically used for server to client environment. It relies on triggers to perform replication.

Transactional replication

Transactional replication also provides two-way replication. It is however better suited for one way replication of data in near real time and is used in a server to server environment where there is a lot of updates occurring at the publisher end. Unlike triggers used for Merge replication, Transactional replication relies on SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent to perform replication

General comments

I hope you find this blog informative. SQL server provides so many options for backup and restore as well as replication that you will never be short of ideas. I haven’t even talked about the Maintenance plan wizard within SQL Sever Management Studio, something you should look into as well. It provides you with a wizard to configure certain SQL Server specific maintenance task. I hope to write another blog on replication and a similar blog for Oracle/DB2 database in the near future.