Offline Synchronization

Hi All,
Sorry for a long post but I'm not very good in english and so cannot
describe my problem in any other way. Please bear with me.
I'm trying to implement a solution that requires data synchronization
across multiple locations. My application will be running on various
branches of a company. These branches are not connected to each other.
Each branch will have their own local SQL Server with the complete
data. Each branch will have full access to data (insert /
update/edit/delete). Periodically the data across all branches will
need to be synchronized (offline via export and import). Any ideas on
how I can achieve this?
Right now what I have in mind is this:
My Application is generating the primary keys for all the tables (like
customers, orders etc.) Here I have ensured that the primary across all
branches will remain unique by prefixing the branchcode (2 characters)
to the primary keys in all tables. All tables also have a CreatedOn
and UpdatedOn fields (datetime). Now for synchronizing data from
Branch A to Branch B, I will export the data from branch A into a MS
Access database. And then I will import it into branch B. While
importing, the application will check each record in the SQL Server. If
the record is not present then it will insert it into SQL Server. If
the record is present and the SQL Server data is older, it will update
with the Access record. If the data in SQL Server is newer then
the record will be skipped.
I hope I have been able to convey what I'm trying to do.
In theory, this seems to be an OK solution but I think a lot of work
will go into this (specially for conflict resolution).
Is there any other (better) way to implement what I'm trying to do? I
have heard about replication, but I'm not sure if it will be useful in
my case. Are there any built-in features in SQL that I can use to for
this kind of offline synchronization?
Any help or pointers would be very appreciated.
Thanks in Advance,
Anoushka
replication requires a link between the two nodes.
You will have to cobble together something like what you are talking about,
if you don't have a network connection.
"Anoushka" <anoushka.jones@.gmail.com> wrote in message
news:1095829976.948995.199280@.h37g2000oda.googlegr oups.com...
> Hi All,
> Sorry for a long post but I'm not very good in english and so cannot
> describe my problem in any other way. Please bear with me.
> I'm trying to implement a solution that requires data synchronization
> across multiple locations. My application will be running on various
> branches of a company. These branches are not connected to each other.
> Each branch will have their own local SQL Server with the complete
> data. Each branch will have full access to data (insert /
> update/edit/delete). Periodically the data across all branches will
> need to be synchronized (offline via export and import). Any ideas on
> how I can achieve this?
> Right now what I have in mind is this:
> My Application is generating the primary keys for all the tables (like
> customers, orders etc.) Here I have ensured that the primary across all
> branches will remain unique by prefixing the branchcode (2 characters)
> to the primary keys in all tables. All tables also have a CreatedOn
> and UpdatedOn fields (datetime). Now for synchronizing data from
> Branch A to Branch B, I will export the data from branch A into a MS
> Access database. And then I will import it into branch B. While
> importing, the application will check each record in the SQL Server. If
> the record is not present then it will insert it into SQL Server. If
> the record is present and the SQL Server data is older, it will update
> with the Access record. If the data in SQL Server is newer then
> the record will be skipped.
> I hope I have been able to convey what I'm trying to do.
> In theory, this seems to be an OK solution but I think a lot of work
> will go into this (specially for conflict resolution).
> Is there any other (better) way to implement what I'm trying to do? I
> have heard about replication, but I'm not sure if it will be useful in
> my case. Are there any built-in features in SQL that I can use to for
> this kind of offline synchronization?
> Any help or pointers would be very appreciated.
> Thanks in Advance,
> Anoushka
>
|||Hello Hilary,
Thanks for the reply. I am ready to "cobble together" the solution
that I described The reason for me posting here is that I just
wanted to make sure that it is absolutely the most elegant way (if we
can call it that!) to do it.
I have never implemented replication of any kind so I wasn't sure.
Thanks once again,
Anoushka

ODBC problems

I have next to no experience with databases, so please bear with me. We have an application that requires setting up a system DSN with a SQL Server driver. I am able to do so on one computer. When I go to another I start the process to add the DSN, I type in the server name and then I have to select the default database on that server. In the dropdown list of databases, only 5 or 6 appear... none of which is the one that I need. I am going through the same steps on each computer and do not know why it would not be recognizing it. Does anyone have any ideas?By the way, I have SQL Server 2000 on Windows Server 2003. All machines are Windows XP with Windows firewall disabled.|||More than likely it is a permissions issue. You would have to grant access to the target database for the login that you are using.

In Enterprise Manager, expand the server node, expand the security node, click on the logins node. In the right-hand pane, select the login you are using and double click. On the database access tab, check the databases to which the login will have access. Also check the db_owner item in the right-hand pane**.

** Note that this is a BAD practice. However, you are indicating that you have no db experience and this will ensure that you get your users up and running quickly with minimal issues which good security can sometimes cause. You are essentially exposing your data to the user community and they may be able to update, delete data or even whole tables with the db_owner permission. Read up on user security and tighten the security back down when you understand better what your user requirements are.

Regards,

hmscott