Friday, March 9, 2012

Newbie designing a database

Hi,
I am currently designing my first SqlSever database and I'm new to
replication.
For now, I'll be using the database for one single location (Shop). But
I'm already planning to use replication in the future for a second shop
(a branch actually).
I have read a lot with the Books Online about replication, but for now,
it's just too much to learn. We first need to be able to start using the
database ASAP, once the design is correct, we will extend it to be used
on other sites also.
So my questiong is: do I need to design the database now to be
replicatable, or will it be easy later to add replication to the database?
Michael,
the main issues related to the schema that I can think of off the top of my
head are:
(1) you must have a PK to use transactional replication
(2) you will not be able to use queued updating subscribers and update BLOB
columns
(3) sometimes you'll need to consider partitioning a table and replicating
only one partition (where colid is large)
(4) in transactional replication it is usual to have triggers fire only on
the publisher (use NOT FOR REPLICATION)
(5) in merge replication, we usually use NOT FOR REPLICATION for identity
columns
(6) in merge replication we usually use NOT FRO REPLICATION for foreign
keys.
After that, we'd need to hear more about your particualr needs to recommend
a replication setup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
It's good to get a start here.
Our company has 2 shops on different location.
We would like to setup an inventory system, so we can use it in both shops.
Having a central database for both shops would be impossible. Our
internet connection is really slow...
So we came up with replication.
Both shops are quite independant despite we sell the same products.
Sometime one shop will have one product and the other not, or will only
have it at a later time.
We will not synchronise the database very often.
I think, a merge replication will do the job here, except for the new
products arriving in one shop before the other. We need a way to declare
the same product on both database (I still cannot figure it out yet how
to do)
So here is just a few thoughts I have now. We still have a long way to
go before being able to fully computerise both shops...
Thank you for your prompt response, I'm very grateful.
Paul Ibison wrote:
> Michael,
> the main issues related to the schema that I can think of off the top of my
> head are:
> (1) you must have a PK to use transactional replication
> (2) you will not be able to use queued updating subscribers and update BLOB
> columns
> (3) sometimes you'll need to consider partitioning a table and replicating
> only one partition (where colid is large)
> (4) in transactional replication it is usual to have triggers fire only on
> the publisher (use NOT FOR REPLICATION)
> (5) in merge replication, we usually use NOT FOR REPLICATION for identity
> columns
> (6) in merge replication we usually use NOT FRO REPLICATION for foreign
> keys.
> After that, we'd need to hear more about your particualr needs to recommend
> a replication setup.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

No comments:

Post a Comment