Best design for a distributed database system

[dgleeson3]

Hello all

I am creating a VB.Net distributed SQL server 2005 application.

Each computer in the system has a database with a table of users and
their telephone numbers.
Each computer has a unique 4 digit identifying code.

The central server runs an application which reads the database table
on each computer.
Once the table is read it is stored in a central database on the
server. The details from any computer
can be displayed on the application, modified therein and sent to the
remote computers to update the tables there.

My question relates to the central database on the central server.
What is the best way, once I have read a remote table, to store it in
the cental database?
Many thanks for any help.

Denis
________________________
Denis Gleeson
http://www.CentronSolutions.com

[Dan Guzman]

My question relates to the central database on the central server.

What is the best way, once I have read a remote table, to store it in
the cental database?

It seems to me that you are rolling your own replication. You might instead
consider leveraging the SQL Server replication features. See the SQL Server
Books Online for more information.

If I understand correctly, each remote site contains a subset of data and
updates to the central database are also pushed to the remote db as they
occur. Remote updates are periodically applied to the central database
using a batch pull process.

If the remote table is the authoritative source and data volumes are modest,
a simple solution is to delete all data in the central database for the site
id and refresh from the remote table. You'll need to ensure no data
modifications for that site id are done to the central copy during the
resync process. SqlBulkCopy is an efficient way to load data into the
central database. After they resync, I recommend that you always update the
remote database before the central database to ensure changes are not lost
during the next reload.

Other scenarios will require that you identify changed data and resolve
conflicts. The actual work can be done on the client side (e.g.
DataAdapter) or on the SQL server side with an ELT process.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<dg*******@eircom.netwrote in message
news:11*********************@50g2000hsm.googlegrou ps.com...

Hello all

I am creating a VB.Net distributed SQL server 2005 application.

Each computer in the system has a database with a table of users and
their telephone numbers.
Each computer has a unique 4 digit identifying code.

The central server runs an application which reads the database table
on each computer.
Once the table is read it is stored in a central database on the
server. The details from any computer
can be displayed on the application, modified therein and sent to the
remote computers to update the tables there.

My question relates to the central database on the central server.
What is the best way, once I have read a remote table, to store it in
the cental database?
Many thanks for any help.

Denis
________________________
Denis Gleeson
http://www.CentronSolutions.com

[dgleeson3]

Hi Dan

Thanks for your response.

a simple solution is to delete all data in the central database for the site
id and refresh from the remote table.

Yes this is the easiest as the data volumes are low.

However, my question is more about the details of the schema design(or
table layout).
This is a more involved table design than I have done before, or maybe
Im thinking about it the wrong way.

Should every remote site be represented by a distinct table in the
central database. This distinct table is created
when new data is first available from the remote site. From then on
it is cleared and re filled. How do I index or identify each
table?

Many thanks for any help

Denis
________________________
Denis Gleeson

http://www.CentronSolutions.com
On Oct 12, 1:46 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.netwrote:

My question relates to the central database on the central server.
What is the best way, once I have read a remote table, to store it in
the cental database?

It seems to me that you are rolling your own replication. You might instead
consider leveraging the SQL Server replication features. See the SQL Server
Books Online for more information.

If I understand correctly, each remote site contains a subset of data and
updates to the central database are also pushed to the remote db as they
occur. Remote updates are periodically applied to the central database
using a batch pull process.

If the remote table is the authoritative source and data volumes are modest,
a simple solution is to delete all data in the central database for the site
id and refresh from the remote table. You'll need to ensure no data
modifications for that site id are done to the central copy during the
resync process. SqlBulkCopy is an efficient way to load data into the
central database. After they resync, I recommend that you always update the
remote database before the central database to ensure changes are not lost
during the next reload.

Other scenarios will require that you identify changed data and resolve
conflicts. The actual work can be done on the client side (e.g.
DataAdapter) or on the SQL server side with an ELT process.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<[e-mail] in message

news:11*********************@50g2000hsm.googlegrou ps.com...

Hello all

I am creating a VB.Net distributed SQL server 2005 application.

Each computer in the system has a database with a table of users and
their telephone numbers.
Each computer has a unique 4 digit identifying code.

The central server runs an application which reads the database table
on each computer.
Once the table is read it is stored in a central database on the
server. The details from any computer
can be displayed on the application, modified therein and sent to the
remote computers to update the tables there.

My question relates to the central database on the central server.
What is the best way, once I have read a remote table, to store it in
the cental database?

Many thanks for any help.

Denis
________________________
Denis Gleeson
http://www.CentronSolutions.com- Hide quoted text -

- Show quoted text -

[sloan]

What is the db type/vendor on the client side?

I guess I wanted to be clear of that fact before offering information.

<dg*******@eircom.netwrote in message
news:11*********************@50g2000hsm.googlegrou ps.com...

Hello all

I am creating a VB.Net distributed SQL server 2005 application.

Each computer in the system has a database with a table of users and
their telephone numbers.
Each computer has a unique 4 digit identifying code.

The central server runs an application which reads the database table
on each computer.
Once the table is read it is stored in a central database on the
server. The details from any computer
can be displayed on the application, modified therein and sent to the
remote computers to update the tables there.

My question relates to the central database on the central server.
What is the best way, once I have read a remote table, to store it in
the cental database?
Many thanks for any help.

Denis
________________________
Denis Gleeson
http://www.CentronSolutions.com

[Rad [Visual C# MVP]]

On Fri, 12 Oct 2007 08:53:02 -0700, dg*******@eircom.net wrote:

>Hi

The database on the client side is proprietary.
The application has a translation layer which will read the remote
data and store in the
central database.

Denis

You might also want to take a look at Sybase ASA. Besides offering
very powerful replication facilities, it also allows you to write your
own logic to synchronize against databases and indeed any storage
mechanism not supported out of the box

--
http://bytes.thinkersroom.com