Hi Aditi,

I agree on how manageable a custom database is. It also allows you to create custom stored procedures which you are not allowed to do in the K2 database (as per the support agreement).

An advantage of using SmartBox is that K2 manages everything on that table. You don't need to maintain it or write anything to access it. Another advantage is that it is backed up as part of the K2 database so you don't need to ensure that it is "kept in sync" when restoring the environment.

In terms of performance, it really depends what you will be storing in SmartBox.

If you will be using it to store large files it can affect the performance of the K2 server.

If this is a requirement then a custom database would be a good candidate.

When using a custom database an annoyance is that you have to refresh the SQL Service Instance each time you make a chance to anything in the database. This involves refreshing the service instance and then updating or recreating the smartobject based off this service instance. With SmartBox you don't have this annoyance.

I guess a question to ask here is how often will you need to update the data structures. If this happens often, you have to weigh up having to refresh the service instance and smartobjects and how it affects currently deployed solutions against the ease of use of SmartBox.

All in all there is no right or wrong answer here. Start off with your requirements for storing data and work from there. If you want a simple way for users to create data structures, then SmartBox is your option. If you want to store large files or use custom stored procs then a custom DB would be your answer, provided you have a competent DBA which can assist with the administration of the custom DB.

I hope this gave you some guidance.

Let us know if you have any more specific questions.

Regards,

JohanL

I agree with Johan's individual points but in my opinion the pros of using an separate SQL database greatly outweighs the cons. 

I typically recommend unless you're using Smartbox to store temporary that you're much better in the long run to use a separate SQL database.  As you mentioned it provides much more flexibility since its not stored in K2 and you can manage it independently of K2, creating your own stored procedures, views and such that wouldn't be supported when using Smartbox.   As well reporting outside of K2 will be much easier to accomplish if its a separate database.

My 2 cents.

Thanks Tim and JohanL for your inputs. In my organization, we have many (more than 20) applications to be developed. One application may have only one form and another may have 20 forms into it. In such case I thought it's better to use custome database to seperate applications and making it more manigeable. 

For single form applications I have created a common cutom database and for large application (with more than 1 form, mostly 5-10, 20 form or more) create seperate custom database for each of those.

Please suggest in this scenaio what is the best approach and best practise, smartbox or custom sql database?

Thanks,

Aditi

Hi,

It depends on load and volume whether to create a separate DB for each or not.

Also remember that you need to maintain all of these DBs.

If you want to reduce it to one DB you can make use of schemas to separate the different applications' data.

At least then it will only involve one extra DB to be maintained with the K2 database.

Things to also take not of is data sovereignty. If any of these applications need to have their data separately, then a custom DB for that application is the only way.

Hopefully this answered your question.

Regards,

JohanL

Had someone ask a very similar question recently, they were looking for any whitepapers/quick reference guides specifically.  Below was my response.

Since these (Smarbox Objects) are stored in the K2 database, they can impact the size of the database, and performance, however, there are many variables that go into determining how large/how quickly this impact will occur. The number of rows added, and level of interaction, as well as number of simultaneous process instances using the smartbox service all play a role. As a general rule, the Smartbox service is designed for several purposes, but line of business data is not one of them, and we discourage implementations that store LOB data in a smartbox. http://community.k2.com/t5/K2-blackpearl/SmartBox-Best-Practices/td-p/58895 provides some additional information as well.

Good uses of a Smartbox:
1. A temporary process that doesn't need a full implementation. A "secret santa" form/workflow that needs data storage and is run infrequently and highly disposable.

2. In development as a temporary placeholder for a larger more scalable database. In testing an application or mock-up, a smartbox makes it easy to create something that works for now and proves the concept for a later more scalable or available database.

3. A temporary storage vehicle for data in a workflow.

The general rule is if you are worried about the performance impact, availability limitations, or database growth from using smartbox, smartbox is probably not the answer. From a backup and redundancy perspective you would want to back up the entire K2 database, while it is possible to backup individual tables, if you are worried about data integrity, I would turn to SQL as you'll have a bit more control of what gets backed up where.

Given the situation you have described, I would say smartbox probably does not have a (large) role in that solution outside of prototyping or as a temporary place to store data.

I do however, see an excellent use for leveraging K2's smartobject architecture to use both sharepoint and SQL for the tasks they are best suited for. Where you have large bulk data coming in that contains mostly text/numeric fields, SQL can provide an excellent way to access, store, and ensure high availability and reliability. Where you have files, folders, documents, and other "sharepoint" type data, you can use sharepoint to provide an easily accessible store of those documents, files, lists, and related content. Your smartobject can easily connect these two stores of information, and leverage the advantages of both.

Now, lets say you wanted to create a quick mock up of this system, to maybe show off the forms and interfaces you designed to stakeholders before you go creating tables on the SQL side. Smartbox is perfect for this.

Perhaps your data centers around customer records, and you plan to have a brief, one or two month promotion, and need a place to store promotion codes for each individual customer. We will only use this workflow for a month or two,a ton of instances will not need to be created and it's not really worthwhile to drop the customer database in SQL and rebuild the tables to add a simple "promo code" field. A smartbox object might be used in this case to store that data.