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.

 

[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

 

[sloan]

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

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

 

[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

 

[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/thezerotonparameterproblem/2283/

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

 

[Dan Guzman]

However, my question is more about the details of the schema design(or

table layout).

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.

 

[Rad [Visual C# MVP]]

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