Synchronising more than one database [solved]

vasilis6669

Hi,

What is the best solution to synchronise offline data for Point of Sale system without exporting and importing text files?

The scenario is that the central nav program will sync master data to 3 disconnected Point of sales and will receive daily transactions at non working hours.

Any ideas or existing solutions will be great.

Regards,
Vasilis

garak

Are the PoS ever offline (no WAN / LAN communication) or online at night?
These PoS separete databases with a separate serverengine?
If yes, is this serverengine SQL? If yes, is the master server also a SQL server?
If yes, can both servers (POS/master) connect (PSO <-> Master)?
If yes, use SQL replication or SSIS packaged.

vasilis6669

Both POS and master have SQL server as database engine.

The Pos will be offline as our public network here in Cyprus is not guaranteed to be 24/7 online. The POS will be online at night if the network is working.

I thought about SSIS packages. I also thought about calling a webservice and do all the work.

How can i call SSIS package using C/Side?

Thanks,
Vasilis

garak

To transfer the data from "Master" (not the sql master db) to PoS use SQL trans. Replication.

To receive the Data from POS to "Master" you can also use replication on the PoS or you create a simple SSIS package on your Repl. Server machine.
The SSIS are time sheduled on SQL Management Studio.

Why you need to call the SSIS with C/AL (you can do this with ADO and SQL commands like EXEC)?
To work with the datas from POS (like sales, new customers, etc.) you muste also create a batch jon in your master DB (for example to genereate the correct inventory if needed)

vasilis6669

I agree with what you are saying but if i want to run SSIS from the POS machine i would have to isntall standard edition to run the package.

Also, i know that i can call a dts package from ADO and cmd using dtexec but this will mean i have to call an ADO exe from C/side?

I want the solution not to be complecated and be cost effective.

I have not worked with replication. What type of replication should i use?

Thanks,
Vasilis

garak

The SSIS packages can run on the same machine where also the transact. replication run.
For the SSIS you need only a user with enough permissions to read the datas in the POS systems.

Structure:

SQL Master Server (also as replication plattform)
|
| Master transfer Datas (send) per Transactional replication
|
V
POS


SQL Master Server (the same machine like the Repl. machine)
^
|
| Master rec. Datas from POS sheduled by time with SSIS
|
|
POS

garak

Follow the Link to msdn

Replication
http://msdn.microsoft.com/en-us/library/ms151706.aspx

SSIS
http://msdn.microsoft.com/en-us/library/ms141026(SQL.90).aspx

Regards

David_Singleton

No need to invent Square wheels when round one work just fine

Take a look at LS Retail.

vasilis6669

LS Retail is an option but as usual there is a always an existing solution.

Thank you all for your help.
Vasilis