SQLTeam.com | Weblogs | Forums

Database replication MS SQL Server

Other SQL Server Topics
#1

Hello,
We have the central office and several stores around the country.
We need to let the stores work even when the Internet is down or poor, so I’m thinking to deploy the same datatabase in the office and in the stores.
I don’t know if sql server replication is suited for this, I read something about it but I am not familiar with it. I can’t figure out how, for example, two different products created with the same ID in two stores will be reconciliated. Not to mention the invoices using those products.
It seems more under control to perform some data update periodically.
Can anyone help me with some ideas, please?

Thank you!

#2

--approach 1

--store1
use muchies 
go

--drop table products
create table products(id int identity(1,1), name varchar(50), storeid int )

insert into products
select 'Corn Pops', 1

go

--store2
use muchies 
go

create table products(id int identity(1,1), name varchar(50),storeid int )

insert into products
select 'Baklava', 2
go

use headquarters
go
create table products(id int, name varchar(50),storeid int )
--productid & storeid would always make things unique.

--via SSIS or some other mechanism, you bring the data from all 
--other stores into the central sql server. Staging includes a 
--products table where you can combine all stores data into
insert into products
select * from Staging.dbo.products
#3

--option 2
--use different identity start point


drop table products

create table products(id int identity(10000,1), 
name varchar(50),
storeid int
)

insert into products
select top 10 name, object_id from master.sys.tables

select * from products

--notice the start point of the id is different,
--but this could be problematic if you forget to adjust the identity start point when
--opening a new store.

image

#4

Hi,
Thank you for answering.
Those were the solutions I was thinking off but I hoped for some groundbreaking ideas I've been never thinking to :slight_smile:
However, I will not use them; for example the Products must be unique for all the locations so I think I will allow Products to be added only in office and then requeryed by the stores.
That means stores must look for new products periodically or on user request.
I think only two tables: Invoices and InvoicesLines will allow records to be added locally and only those will be pushed upwards into the main database.

Thank you.

#5

:slight_smile: no ground breaking stuff

you previously said the above ^ so now if internet is poor and they cannot get new products, they will write down the sale on a napkin and enter it later when internet is good?