Sean Cleaver - Software IT Consultant, Hastings

Locally Run Ouvvi without IIS

So if you just have a development machine (no access to a server) and want to run the Ouvvi Web Console without IIS you can use the Visual Studio Development Server instead.

Install Ouvvi as normal but skip the last configuration step at the end of the install.

Then you need to start the Development Web Server like this.

"C:\Program Files (x86)\Common Files\microsoft shared\DevServer\9.0\WebDev.WebServer.EXE" /port:4040 /path:"C:\Program Files (x86)\Simego\Ouvvi Website\Web"

Make sure you choose the right TCP port here I am using 4040.

Then you can finish the Ouvvi configuration however I had to re-start the server after provisioning the database as it didn’t pick up the web.config file changes but after that it was fine.

Remember Ouvvi only requires the web console for setup, configuration and status reporting the actual project/step processing occurs via the Ouvvi Service so you don’t need to run the Web console all the time.

Synchronising Data from a Simple Http Service

We have created a new Data Provider for Data Synchronisation Studio 2.0 which accepts a CSV formatted response from a Http Stream.

This means you can easily integrate any system in which you can expose data as a Http response formatted as CSV with SharePoint or any of the other data providers which support writing back to the data store.

For example if you create a simple service that returns a response similar to this

You can then load this in Data Sync 2.0 by specifying the Uri to the Http response like this using the new CSV File/Http File Data Provider.

This can then use this as the source data in Data Sync to be synchronised to SharePoint.

 

You can download a simple example below that demonstrates how to create a Simple CSV Http response or Simple SOAP 1.1 Web Service to use with Data Sync.

Download Sample Code

Backup your SQL Express Databases for Free

Did you know with Ouvvi Express edition you can easily schedule a SQL Backup of your departmental databases and compress them on a daily basis.

The setup is so simple and only takes a few minutes to get going, and as you create more databases all you need do is login to the web console and add more steps to the backup schedule.

You can see here every day were backing up 4 SQL databases in our Office.

To configure the step for the actual backup is quite easy you just create a SQL Backup Step and configure it like this.

 

This gives us a daily backup file like this on our server

 

You can then add notifications to the pipeline if the process fails and keep an eye on things via the RSS Feed.

Data Synchronisation Studio 2.0 and Ouvvi Beta out

We have released to the Simego Website new Beta installs for the following :-

Data Synchronisation Studio 2.0

• Supports Ouvvi
• Side by Side install with Data Sync 1.0
• UI Improvements
• Select which rows to sync from comparison results
• All fixes since 1.0.188 rolled up into 2.0
• Experimental support for SharePoint 2010
• Strong named assemblies
• GAC installed main assemblies.

Available here www.simego.com/beta

DS 2.0 will be a free upgrade to all those customers who purchased data sync in the last 12 months. Outside of this there will be a small upgrade fee to extend your support and maintenance for a further 12 months.

Ouvvi Express 1.0

Free Web based Process Automation and scheduler system, supports backup of SQL Server Databases, Running SQL Commands, Starting SQL Jobs, Pinging Web Sites, Running Data Sync Projects, Email, SMS, RSS etc

See http://www.simego.com/Products/Ouvvi-Console

Install guide here Ouvvi Install & Setup

Data Synchronisation Studio 2.0

We have a new feature for Data Synchronisation Studio 2.0 which we think you might like when you’re trying to control better which data to migrate between systems. You can see in the screenshot below we have added a new “Sync” column to the compare results this allows you to specify which rows to actually sync across to the destination system.

 

You can also now preview the data from each side as the schema map by clicking on the “Preview A” or “Preview B” mini-toolbar buttons. This allows you to see the data exactly as Data Sync will see it when it compares the data. It also prevents downloading all columns from the source system which can speed things up a little.

Other additions to the Schema Map include :-

• Double Click Column in data source to add it to the Map.
• Multi-select Schema Map Rows and delete with one click.

Also Data Synchronisation Studio 2.0 can be installed side-by-side with DS 1.0 and Data Sync 2.0 integrates with Ouvvi.

Using Ouvvi to Schedule SQL Database Backup

We have just added a new Step Handler that runs a backup of a SQL Server Database and additionally you can ZIP up the backup image to save on disk space.

Below is a screenshot of the Step configuration and you can see here were using the Token expansion system for the backup filename which makes it easy to create a unique filename.

Tokens

This is a list of the static token values that we have added to Ouvvi

INSTANCEID
PROJECTID
DAY
MONTH
YEAR
FILEDATE
DATE
FILETIME
TIME
HOUR
MIN
SEC
MACHINE
USERNAME
DOMAIN
APPPATH
PERSONAL
SYSTEM
LOCALAPPLICATIONDATA
APPLICATIONDATA
COMMONAPPLICATIONDATA
PROGRAMFILES
TEMP
NEWGUID

Ouvvi Express Edition

Were going to create an Express Edition of Ouvvi which will be a Single Instance, Single User version of the Ouvvi software. This Express edition will be FREE yes FREE the only limitations will be the number of users i.e. 1 and this version will not be supported in the same way the Standard and Professional editions will be.

Using the Ouvvi Status Feed

So in Ouvvi we have created an RSS/Atom feed that returns the current status of all the projects in the console. This has a variety of uses including the ability to advertise to other business units that everything is all running correctly.

For example I am consuming the Status Feed here in Outlook and it allows me to see that the jobs are all running and I get an update each time it’s run.

 

Ouvvi – Running for another Week

So we left the current build of Ouvvi running all last week (since 29th Jan) on our Server processing it’s jobs and it’s just running perfectly, no increase in Memory usage, low CPU usage and all services still running perfectly!

 

 

 

New Ouvvi Step Types

We’ve added a few new Step Handlers to Ouvvi to extend the integration capabilities.

HttpWebRequest – Allows you to call an external web page and validate the result with a Regular Expression. This can simply be used to check that a remote service is running or used to notify an external service that an Ouvvi project has completed.

SQL Job – Allows you to Start a Job in SQL Server Agent typically this might be used to start an SSIS Package.

SMS Message – Like the Email Message handler you can also send SMS’s via the www.csoft.co.uk SMS Gateway Service.

Ouvvi Project – You can Start another Ouvvi Project.

Testing Ouvvi Uptime

So were running Ouvvi internally here on our server to check the reliability of leaving the service completely unattended we have been running for several days now and all is working very well. We have two projects setup one that backs up our database from the Internet on Start of Day and End of Day, the other runs every 30 minutes and synchronises our Exchange Timesheet data with SQL and SharePoint.

Ouvvi Service(s) List here were running 3 instances of the Step Service on the same Server and you can see that the last service checkpoint was a few seconds ago and it’s been running since 29th Jan.

Task Manager is showing that Memory usage has not climbed up at all even when running several data sync projects every 30 minutes.

And CPU usage is pretty much flat which is really good.

Server Performance Running Ouvvi

Were quite keen that Ouvvi can be deployed onto existing infrastructure and not impact normal server operations. In fact were running Ouvvi on our DC which is also running SQL Server, SharePoint and Team Foundation Server.

Below shows a Server CPU Graph running the Ouvvi Service. Here you can see that running all the Ouvvi Services in the background doesn’t impact server CPU load also the actual Memory usage is quite low at 16MB this value will increase depending on the size of the Data Sync projects your running but should drop down to around this figure when Ouvvi is idle.

Using Ouvvi to Schedule and Manage your Data Sync ETL

Here’s a quick overview of how we are using Ouvvi internally to manage our Data Sync projects.

We have a series of Steps that basically download our Timesheet Data from Exchange Server which is then synchronised into a SQL Table. Then we create a View that returns statistics about the time spent on each project which is Synchronised into a SharePoint List with Data Sync.

So this project consists of the following Steps

• Get Exchange Records
• Update Job Codes (2 way sync of SharePoint List containing Job Codes)
• Update Job Stats
• Email support if there was an error.

In Ouvvi this is really easy to setup we just create a new project and add the steps like below.

Adding a Data Sync Step is easy too we just enter the path to the Data Sync Project like so.

We want this process to run at “Start of Day” and every 30 minutes so we just add the Triggers for “Start of Day” and 30 Minutes.

For the Failure report we just add a SMTP Step which only runs on the Run Condition “Failure” like so.

And that’s it all setup and running every day, logging the status to the Ouvvi logs and reporting any error to our HelpDesk. (Ouvvi has an automatic retry pattern so we need 3 failures in a row to initiate an actual error).

Anytime we need to kick this process off again Ad-Hoc we can just hit the Start button without having to log onto the server.

Coming Soon – Ouvvi for DataSync / ETL

So we have been busy putting a new solution together to better manage Data Sync projects and ETL along with any solution that requires a series of steps to be followed. This could be an STP system, Order Processing, Data Import/Extract or anything really.

The solution involves a Web Front End application for Management and a Windows Service that handles the actual processing.

The system involves creating a project and adding steps to the project which are processed in turn, each step has a run condition which allows for example running a step when the batch fails such as a step to send an email/sms alert.

Projects and Steps are added to a Queue for processing to ensure that the system remains consistent between failures, re-starts etc and can be easily backed up via SQL Server backup. 

Features

• Scale-out across Processes/Servers/Machines for both Web Application and Service processing.
• Resilient against failures via Retry Pattern and Queue System to maintain consistency.
• Redundancy/Failover via running multiple services on separate machines.
• Unified Logging where all messages from all projects/steps are captured into a single logging table
• Logging clean up where expired logs are deleted (could also be archived)
• Queue clean up where failed/unknown state messages are removed after retry count fails.
• Start of Day/End of Day Triggers
• Easily Develop new Step Handlers for integration of any system i.e. Execute SSIS Package, Run SP, BCP, Backup Database, FTP etc.
• Easily Develop new Triggers such as File Drop, Web Service etc.
• RSS Status Reports
• Content Management to enable Project/Step documentation within the console.

Prototype Step Handler

Creating new Steps is as simple as creating a class that derives from StepHandler like so.

public class SqlStepHandler : StepHandler
    {
        public override void Execute()
        {
            LogService.LogEvent(InstanceID, ProjectID, StepIndex, LogSeverity.Information, "SqlStepHandler.Execute", "Start", this.Name);
        }
    }

Screenshot

System Requirements

We have chosen not to use the very latest software to build this solution so that the requirements are pretty minimal and therefore could be installed easily onto existing infrastructure or virtualized with Hyper-V or VMWare.

For the Web Application

Windows Server 2003+, Server 2008, Vista, 7
IIS 6.0 +
.NET Framework 3.5 SP1
SQL Server 2005+ (and Express)

For the Processing Service

Windows Server 2003+, Server 2008, Vista, 7
.NET Framework 2.0+
SQL Server 2005+ (and Express)