Best design for a distributed database system

Best design for a distributed database system

am 11.10.2007 23:45:57 von 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

Re: Best design for a distributed database system

am 12.10.2007 14:46:28 von 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

wrote in message
news:1192139157.890033.50810@50g2000hsm.googlegroups.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
>

Re: Best design for a distributed database system

am 12.10.2007 16:51:18 von 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" online.sbcglobal.net> wrote:
> > 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
>
> wrote in message
>
> news:1192139157.890033.50810@50g2000hsm.googlegroups.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 -

Re: Best design for a distributed database system

am 12.10.2007 17:39:19 von sloan

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

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





wrote in message
news:1192139157.890033.50810@50g2000hsm.googlegroups.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
>

Re: Best design for a distributed database system

am 12.10.2007 17:53:02 von dgleeson3

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
________________________
Denis Gleeson


http://www.CentronSolutions.com




On Oct 12, 4:39 pm, "sloan" wrote:
> What is the db type/vendor on the client side?
>
> I guess I wanted to be clear of that fact before offering information.
>
> wrote in message
>
> news:1192139157.890033.50810@50g2000hsm.googlegroups.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 -

Re: Best design for a distributed database system

am 12.10.2007 20:52:25 von sloan

In that case, I don't think a replication situation will work.

What I would do is get your tables right (see below).

I would assign a guid to each unique computer name.

Then your list of UserTable (UserInfo below) will always contain a FK
reference to the ComputerName.

Thus, whenever you insert a list of users, you'll always have to use the
computerName (which you have) to lookup the ComputerUUID (PK for Computer),
before inserting users.

The tables def I give below will allow you to set up your ref integrity
BEFORE you do imports. That's one bonus of using a guid. Be aware there
are some performance factors of guids as PK's.
But for your need I would go that way. (google guid vs identity for
articles about this issue).

I'm a little confused about how why your clients need to get the data back
from the serverDB. Just as a sync up / refresh? Since they hold the data
for their machine to begin with.

Anyway, hopefully this will give you an idea. I figured the clients were
using something besides Sql Server (my guess was Access, but you said some
propietary database.

Good luck:

/*

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.

*/



if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TelephoneInfo]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)

BEGIN

DROP TABLE [dbo].[TelephoneInfo]

END

GO





if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[UserInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN

DROP TABLE [dbo].[UserInfo]

END

GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ComputerName]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)

BEGIN

DROP TABLE [dbo].[ComputerName]

END

GO





CREATE TABLE [dbo].[ComputerName] (

[ComputerUUID] [uniqueidentifier] primary key not null default
NEWSEQUENTIALID() ,

ComputerName varchar(64) not null -- this would hold the friendly computer
name

--like jsmithbox, mjonesbox, stuff like that

CONSTRAINT CN_CName_UNIQUE UNIQUE (ComputerName)

)

GO









CREATE TABLE [dbo].[UserInfo] (

[UserInfoUUID] [uniqueidentifier] primary key not null default
NEWSEQUENTIALID() ,

--FK to the specific computer.

[ComputerUUID] [uniqueidentifier] not null FOREIGN KEY (ComputerUUID)
REFERENCES dbo.ComputerName(ComputerUUID),

UserName varchar(64) not null ,

CONSTRAINT UserInfo_UserNamePerComputer_UNIQUE UNIQUE (ComputerUUID ,
UserName) --THis says that each username is unique ON EACH COMPUTER

)

GO



CREATE TABLE [dbo].[TelephoneInfo] (

[TelephoneInfoUUID] [uniqueidentifier] primary key not null default
NEWSEQUENTIALID() ,

--FK to the specific user.

[UserInfoUUID] [uniqueidentifier] not null FOREIGN KEY (UserInfoUUID)
REFERENCES dbo.UserInfo(UserInfoUUID),

TelephoneNumber varchar(16) not null , -- (000) 000-0000

CONSTRAINT User_TelephoneNum_UNIQUE UNIQUE (UserInfoUUID ,
TelephoneNumber) --THis prevents duplicate phone numbers for the same User

)

GO



INSERT INTO [dbo].[ComputerName] ( [ComputerUUID] , [ComputerName] )

values ( 'C0000000-0000-0000-0000-000000000001' , 'cbarkleyWin98Box')



INSERT INTO [dbo].[ComputerName] ( [ComputerUUID] , [ComputerName] )

values ( 'C0000000-0000-0000-0000-000000000002' , 'jsmithXPBox')

INSERT INTO [dbo].[ComputerName] ( [ComputerUUID] , [ComputerName] )

values ( 'C0000000-0000-0000-0000-000000000003' , 'mjonesWin2000Box')

select * from [dbo].[ComputerName]

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000111' ,
'C0000000-0000-0000-0000-000000000001' , 'andy apple' )

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000112' ,
'C0000000-0000-0000-0000-000000000001' , 'ben banana' )

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000113' ,
'C0000000-0000-0000-0000-000000000001' , 'cindy cucumber' )

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000222' ,
'C0000000-0000-0000-0000-000000000002' , 'henry hippo' )

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000223' ,
'C0000000-0000-0000-0000-000000000002' , 'larry lama' )

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000224' ,
'C0000000-0000-0000-0000-000000000002' , 'mandy monkey' )

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000333' ,
'C0000000-0000-0000-0000-000000000003' , 'frank ford' )

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000334' ,
'C0000000-0000-0000-0000-000000000003' , 'gary gm' )

INSERT INTO dbo.UserInfo ( UserInfoUUID , ComputerUUID , UserName )

values ( 'E0000000-0000-0000-0000-000000000335' ,
'C0000000-0000-0000-0000-000000000003' , 'henry honda' )



select * from [dbo].UserInfo



--You don't have to specify the PK, you can let sql server do it for you.

--I just did this to help with fake populate and data ref integrity

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000111' , '919-000-0001' )

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000112' , '919-000-0002' )

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000113' , '919-000-0003' )

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000222' , '540-001-0001' )

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000223' , '540-001-0002' )

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000224' , '540-001-0003' )



insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000333' , '703-002-0001' )

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000334' , '703-002-0002' )

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000335' , '615-003-0006' )

insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000335' , '615-003-0007' )



insert into [dbo].[TelephoneInfo] ( [UserInfoUUID], TelephoneNumber )

values ( 'E0000000-0000-0000-0000-000000000335' , '615-003-0008' )

select * from [dbo].[TelephoneInfo]

print ''

print ''



Select cn.ComputerName, u.UserName, t.TelephoneNumber from dbo.ComputerName
cn join dbo.UserInfo u on cn.[ComputerUUID] = u.[ComputerUUID]

join [dbo].[TelephoneInfo] t on u.[UserInfoUUID] = t.[UserInfoUUID]

Order by cn.ComputerName, u.UserName, t.TelephoneNumber





The key here, is that you can't add a new user with knowing the computer
they're associated with.

the other key (with the guid solution), means you can build your ref
integrity before pushing the data into the database.

in C#/DotNet you would do

Guid newComputerUUID = System.Guid.NewGuid();

You could also build up a strong dataset (dotnet) to hold all the
information before shipping it to sql server.



See this article:

http://www.sqlservercentral.com/articles/Stored+Procedures/t hezerotonparameterproblem/2283/

Dont' focus on "report paramters", but rather how you can send xml into the
database and do something with it.











wrote in message
news:1192204382.792861.299820@i13g2000prf.googlegroups.com.. .
> 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
> ________________________
> Denis Gleeson
>
>
> http://www.CentronSolutions.com
>
>
>
>
> On Oct 12, 4:39 pm, "sloan" wrote:
>> What is the db type/vendor on the client side?
>>
>> I guess I wanted to be clear of that fact before offering information.
>>
>> wrote in message
>>
>> news:1192139157.890033.50810@50g2000hsm.googlegroups.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 -
>
>

Re: Best design for a distributed database system

am 13.10.2007 13:15:01 von Dan Guzman

> However, my question is more about the details of the schema design(or
> table layout).
< snip >
> How do I index or identify each table?

Assuming schema are identical, I would use a single table with a site id
column to uniquely identify each remote site. This would allow you to
logically partition the table for the reload process or security
requirements. It probably makes sense to include the site id in your index
keys to facilitate queries by site. You would almost certainly want a
clustered index index with site id as the high-order column.

If the central database is running SQL Enterprise Edition, you have the
option to physically partition the table by site.

--
Hope this helps.

Dan Guzman
SQL Server MVP

wrote in message
news:1192200678.961552.113140@e34g2000pro.googlegroups.com.. .
> 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" > online.sbcglobal.net> wrote:
>> > 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
>>
>> wrote in message
>>
>> news:1192139157.890033.50810@50g2000hsm.googlegroups.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 -
>
>

Re: Best design for a distributed database system

am 15.10.2007 19:17:56 von RAD

On Fri, 12 Oct 2007 08:53:02 -0700, dgleeson3@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