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 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 -
>
>