Best to way to populated external database?

Question

I have a requirement where I need to populated a SQL Server database (tbl_Customer) with sharepoint List. I am not sure about the best approach.

approach 1: bcs to external database and scheduled content migration (if possible)

approach 2: Create store proc in the external sql database and call sharepoint web services to populate the tbl_customer table

approach 3: may a powershell script to read sharepoint list and write to sql table

Guys, I really need your judgement to I know the best approach to take. (unfortunately, they gave me 2 days to come up with the best solutions).

I would truly APPRECIATED it.

Answer 1

There's several ways to skin this cat, but maybe the easiest way would be to move the list from SharePoint, into your SQL table, then use BCS to consume that table so you can use it in SharePoint. Not sure on the complexity or requirements, but this would solve the issue of having the data in SQL, and making it available in SP.

otherwise, some sort of a routine (timer job, PowerShell tasked) can handle copying (duplicating) the data. Syncing at that point get's a little iffy, unless you're doing a complete overwrite each time.

HTH