Replicating database tables as SharePoint lists

Question

We have a legacey application that is running with a SQL backend. We want to end of life the old application and use SharePoint 2010 instead. In my my mind we have a few options of what we could do.

1. Create an Import process to store the data we need in Lists
2. Create a new Database. Import the data that we need from our application. Then hook that up to SharePoint
3. Point SharePoint at our existing database.

In my mind 2 seems to make the most sense. It means we won't need to write a complex routine to import data. We can drop the tables we no lonnger need.

Would you agree. Is there anything else that I may need to consider.

Answer 1

I think that using the BCS to connect to your database table would be the most effective way. Where that table lives is up to you. You could move it to the SharePoint server or another SQL server in order to remove the old sql server from the network.

If the BCS is not an option, then you will either need to import the data into a SharePoint list or create a custom interface (DataView Web Part, Custom Web Part) to display the content in SharePoint.

Answer 2

It depends on how many tables their are but if there aren't too many I might be tempted to put the data directly into the lists, as so much of SharePoint is built on lists. You could do this using by creating the lists and content types and then importing the data using the datasheet views.

The other benefit of this is it would allow you to look at the data and cleanse it, as if its a legacy system the chances are there is bad data in there.

Answer 3

Another option would be to convert the legacy app to an Access 2010 database and use Access Services in 2010 to manage it. Overall, it's difficult to give you other answers ontop of what Mike and Paul have suggested as there are a lot of things to take into consideration.

Answer 4

Most important question here is: What do you really want from SharePoint?

If you just want to show data from some other source/application with some CRUD operations then answer provided by @Mike covers it. Go with BCS. Be sure to read this article first: BCS Limitations

Most important benefit of importing your data into SharePoint lists is that you have tons of OOTB functionalities available only few clicks away. I will name just two of them: Versioning and Workflows.

Depending on original database complexity migration could be a problem. More problems could arise if you want to replicate your original application business logic. Even if you are using some very simple logic you should be prepared for 'challenges' because in SharePoint nothing is simple.

Personally I would always choose to store data directly in SharePoint lists and consider possibility of using external data only if data is way too complex and/or primarily managed by some other application. But keep in mind that I am SharePoint developer :)

And for the end I will quote myself Does the level of effort justify using SharePoint 2010 as a public facing webportal? :

And in the end if you decide to go with SharePoint be sure that you have some experienced developers near by.