I have a Sql2005Express database that needs to be synched up with a Sql2005 database. But, I'd like to be able to dynamically specify the server at run-time. I'd also like to limit the records that are populated in the mobile database. The mobile database is NOT identical to the main one, though they are very very similar. As well, for security reasons, I will likely have to clear the mobile database's records after uploading the changes to the main database.

At this point, I am trying to copy only the records of interest over to the mobile database. Uploading data is already handled via a .NET program. What would be the best way to attack this situation? The "perfect" solution to me at this point is to run a stored procedure on the mobile database which will then copy the data from the main database.

For reference, I'll be using a pre-specified Windows user to authenticate to the mobile database every single time. The work-flow will be as follows:
1) download the data from the main to the mobile
2) offline, make changes to the data in the mobile database
3) upload changed data to the main database using a .NET program
4) clear the mobile database

4/6/2010 3:19:53 PM

half the features. double the price. 8 times the marketing

4/6/2010 4:12:04 PM

half the features. double the price. 8 times the marketing

4/6/2010 4:14:11 PM

how much data are we talking here

4/6/2010 4:17:17 PM

You can't do this at a DB level, and definitely not with SQL Express. You're going to have to write an object model in .NET that handles the rules of syncronization, data transfer, clearing, et al.

4/6/2010 4:29:05 PM

half the features. double the price. 8 times the marketing

4/6/2010 4:37:52 PM

half the features. double the price. 8 times the marketing

4/6/2010 5:10:15 PM

fred, I'm lookin at 20+ tables. for most of the tables, I'd say there are 200 or so records each, max. But, for the main table, we'll be adding on the order of 10,000 records per year, with about 10-20 records overall in associated tables for each record in the main table. The volume seems to be enough to make .NET infeasible for the long-term.

4/6/2010 5:34:44 PM

personally, i hate doing complicated stuff in sql, so i'd be hesitant to recommend doing it in stored procedures

Microsoft's prescribed tools for this type of thing would probably be Sql Server Integration Services, which specializes in data migration tasks. has built in stuff for transformations from one schema to another, etc.

but every time i've tried to use SSIS (or MS's older ETL technology DTS), it's been a pain in the ass.

when i've done these types of projects, i usually end up write it in .net, using LINQ to SQL or Entity Framework. Both LINQ2SQL and EF give you the ability to give a wizard (or command line) a db connection string, select which db objects you want, and it will generate the required code to access the database. imo, MUCH more expressive and easy to work with. might end up looking something like this

            using (MasterDataContext master = new MasterDataContect())
            {
                using (MobileDataContext mobile = new MobileDataContext())
                {
                    var masterUsers = (from u in master.Users where u.IsActive select new Mobile.User() { .Name = u.Name, .Age = u.Age });
                    mobile.Users.InsertAllOnSubmit(masterUsers);
                    mobile.SubmitChanges();
                }
            }

4/6/2010 6:33:58 PM

Quote :

"You're going to have to write an object model in .NET that handles the rules of syncronization, data transfer, clearing, et al."

I have to agree with Noen on this. You need an application layer which will handle this properly ... can't really be done at a database backend / database driver layer. I know IBM has a product which does exactly this (remote giant DB, local small as crap mobile db, two-way sync of data, etc.). Might want to hunt around ... I'd be surprised if somebody doesn't already have a .NET application which does this.

^
Or see fred's post for something quick and dirty if your feature requirements/scope is relatively small.

[Edited on April 6, 2010 at 9:42 PM. Reason : .]

4/6/2010 9:39:51 PM

thx fred. unfortunately, the project is in .NET 2.0 Visual Studio 2005 ftl

4/6/2010 10:43:54 PM

Visual C# and VB 2008 Express are freesauce. Also, project can be done in 2.0, just without LINQ.

[Edited on April 7, 2010 at 11:51 AM. Reason : right?]

4/7/2010 11:50:29 AM

meh, just did it all in a couple stored procedures and will make a .NET function that calls each one, updating a progress bar as it goes along. wasn't that hard, lol

4/7/2010 9:04:05 PM

^^It's worth the upgrade just for LINQ if you do a lot of data work. Seriously.

And I would be pretty hesitant to rely on stored procedures to carry this stuff out, mostly because of the upload scenario. You have no way to know if the data was completely transferred without doing at least validation in .NET.

Quote :

"The volume seems to be enough to make .NET infeasible for the long-term."

Our TFS integration platform handles millions of transactions daily on .NET code to sync and mirror data. It's not going to be a problem handling volume with .NET.

4/7/2010 9:43:10 PM

FREE PLUG!!!

upload is gonna be handled in .NET, so no worries. no point in writing a bunch of stored procedures to do what has already been done.

4/7/2010 10:48:53 PM

^^But the upgrade here we're talking about is from 2005 (2.0) to 2008 (3.5), not from free to paid right? For my personal understanding, is there any limitation to C# Express that limits you from writing database applications?

[Edited on April 8, 2010 at 11:46 AM. Reason : un]

4/8/2010 11:46:07 AM