How to distribute data among different servers uniquely?

Question

How to distribute data among different servers uniquely?

nwallaq

Grasshopper

Points: 22
More actions
July 15, 2010 at 12:12 pm

#223401

how can i distribute the future data onto separate computers using ms sql srvr08.
i have made a one database having three tables.
these three tables are common across 20 departments in a college, but the data is not.
problem is each department has its own computer lab, and wants to maintain its own computer where only the particular departments data is stored.
( please ignore the fact that its not needed as sql server can handle millions of....... )
have to do it so,
my solution:-
* install sql server 2008 express (free 🙂 ) on one computer in each department
* copy paste the database schema
* put different connection string code to connect to the respective departments computer to read, insert, delete etc
but i need a better way to achieve this is there a better way such as replication, table partitioning,
or something like
* one main server, other child servers.
* request for data related to a particular department is redirected to the server having the data. using ms sql server?
please please help, i am a beginner and am stuck very badly at this, please friends help.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

we have a handful of tables that require what you are asking;

what we did was add an additional column to the table, where the default value is the location/server/identifier for the location. we also seeded the identity column with higher values so

so where a table might have been defined like this:

CREATE TABLE [dbo].[EXAMPLE] (

[EXAMPLEID] BIGINT IDENTITY(1,1) NOT NULL,

[KEYID] BIGINT NULL,

[EXAMPLETEXT] VARCHAR(30) NULL,

CONSTRAINT [PK_EXAMPLE] PRIMARY KEY CLUSTERED (EXAMPLEID))

--changed to

CREATE TABLE [dbo].[EXAMPLE] (

[EXAMPLEID] BIGINT IDENTITY(1,1) NOT NULL,

[LOCATION] VARCHAR(30) NOT NULL DEFAULT 'Miami'

[KEYID] BIGINT NULL,

[EXAMPLETEXT] VARCHAR(30) NULL,

CONSTRAINT [PK_EXAMPLE] PRIMARY KEY CLUSTERED (EXAMPLEID, LOCATION))

--but in another database it is like this:

CREATE TABLE [dbo].[EXAMPLE] (

[EXAMPLEID] BIGINT IDENTITY(100000,1) NOT NULL,

[LOCATION] VARCHAR(30) NOT NULL DEFAULT ('Fort Lauderdale'),

[KEYID] BIGINT NULL,

[EXAMPLETEXT] VARCHAR(30) NULL,

CONSTRAINT [PK_EXAMPLE] PRIMARY KEY CLUSTERED (EXAMPLEID, LOCATION))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nwallaq Grasshopper Points: 22 More actions · Answer 2

thanks for the answer, but my question is what to use, what is it called replication, partitioning, or the solution i gave is correct???

please note that i am a very very new beginner and i do not understand what your point is by the queries,.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Post suggests each Department wants to run its own version of a generic database in its own server.

If this is the case you have to do nothing but installing SQL Server in each server then deploy the same database in each one of them. Period.

If the project calls for having a consolidated site just build your "master" SQL Server environment then define each Departmental server as a remote server. The easiest way to move data around would be to schedule a job to pull data once a day or so.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.