Mobile SQL

Hi all,
I wonder if someone can help me with a management request that I have, which
I harldy see the point in asking but as they pay my wages I will. I
currently have both a 2000 & 2005 installations of SQL server running, will
remove 2000 once I have everything transfered and the DTS packs re-written i
s
SSIS, but I have been asked the following question by someone who likes to
use Access, what has been asked for is a way that they can run updates and
select query's etc on a local copy of a database, i.e. one that has been put
on their machine and then for me to sync any changes and run minor updates o
n
the server once the machine is back in the office and on the network....I
have suggested using something like VPN or other such software to connect to
the server remotely but the response wasn't very enthuastic. Any thoughts
about how I can go about this and what software I would need, thanks in
advance.
P.Phil wrote:
> Hi all,
> I wonder if someone can help me with a management request that I have, whi
ch
> I harldy see the point in asking but as they pay my wages I will. I
> currently have both a 2000 & 2005 installations of SQL server running, wil
l
> remove 2000 once I have everything transfered and the DTS packs re-written
is
> SSIS, but I have been asked the following question by someone who likes to
> use Access, what has been asked for is a way that they can run updates and
> select query's etc on a local copy of a database, i.e. one that has been p
ut
> on their machine and then for me to sync any changes and run minor updates
on
> the server once the machine is back in the office and on the network....I
> have suggested using something like VPN or other such software to connect
to
> the server remotely but the response wasn't very enthuastic. Any thoughts
> about how I can go about this and what software I would need, thanks in
> advance.
> P.
Hi Phil
You can try to look at Merge Replication as an "out of the box"
solution. If that can't do the job, I think you're left with a lot of
coding in your application, that can keeps track of all changes
happening both on the server and on each mobile user.
I've worked with a CRM system that used this to keep track on all
changes that was done on main, remote and mobile databases. In general
it worked, but it really required a lot of work to keep all the
databases consistent with the correct data.
You'll also have to think about error handling - i.e. what happens if 2
users have changed the same record to with different data? Which of the
changes are going to "survive"? What if a user deletes a record and
another user has changed this record on his system? Will the record than
has to be inserted again and should user 1 that deleted the record be
notified about that? What if he sees the record is back again and then
just deletes it again. What is then the reaction from user 2 that just
made some changes to that record. Now he suddenly hasn't got the record
any longer...etc...etc...etc. There are just so many scenarions you
have to think through if you wan't to set up this kind of "replication".
Of course it's something that can be done, but it's not as easy as it
sounds.
Regards
Steen|||Hi Steen,
Thanks for all your advice, some of the points that you brought up I had
considered but there were quite a few I hadn't. I think I will have another
go at convincing my employer that this is not the best way to go just for on
e
user to make the odd change to the database, it seems a whole lot of work.
Failing that I will begin coding, if nothing else it will keep me busy for a
while.
Thanks again, P
"Steen Persson (DK)" wrote:

> Phil wrote:
> Hi Phil
> You can try to look at Merge Replication as an "out of the box"
> solution. If that can't do the job, I think you're left with a lot of
> coding in your application, that can keeps track of all changes
> happening both on the server and on each mobile user.
> I've worked with a CRM system that used this to keep track on all
> changes that was done on main, remote and mobile databases. In general
> it worked, but it really required a lot of work to keep all the
> databases consistent with the correct data.
> You'll also have to think about error handling - i.e. what happens if 2
> users have changed the same record to with different data? Which of the
> changes are going to "survive"? What if a user deletes a record and
> another user has changed this record on his system? Will the record than
> has to be inserted again and should user 1 that deleted the record be
> notified about that? What if he sees the record is back again and then
> just deletes it again. What is then the reaction from user 2 that just
> made some changes to that record. Now he suddenly hasn't got the record
> any longer...etc...etc...etc. There are just so many scenarions you
> have to think through if you wan't to set up this kind of "replication".
> Of course it's something that can be done, but it's not as easy as it
> sounds.
> Regards
> Steen
>