1

This is more like an advise question.

--SQL SERVER 2008/SQL SERVER 2005/HAMACHI/ DELPHI 2010--

Im developing a POS system for few restaurants that we own (4), each of the locations have their own SQL Server database, just 2 days ago i could create a conection using HAMACHI for a VPN and created liked servers (Father Google helped me out with all of this), i can now acces all of the data in the remote locations. I also have all of the databases in this computer (I will build a real server computer). I created a database in the "server" for each of the locations so it would be easier to create reports and all.

I didnt create a client-server model and went for a thick one because internet is very unstable and i dont really need to update at real time.

I want to create an update into the server every 30min or every hour, im still wonrking on it.

I have few questions.

  1. (if you know it) Is hamachi a reliable VPN, does it has its problems (wich ones), or do you recomend another way and wich one?

  2. When doing the update (by update i mean an insert of the new records into the server), should i execute the update from the client or from the server?

  3. I am using MERGE to update when matched and insert when not matched, but i dont know if it is the best way to do it as it scans all the records and a table with only 243,272 records takes like 12mins to complete, or if i should select the recods where the PK is higher than the last PK in the server and do a merge. Based on your experience wich way would be the best (even without using merge)...

This is a merge code im using:

SET IDENTITY_INSERT pedidos ON

MERGE INTO pedidos C
USING(
    SELECT id, id_pedido, id_articulo, cant, fecha, id_usuario, [local], estado
    FROM [SENDERO].[PVBC].[DBO].[pedidos]) TC
ON (C.id =TC.id)
WHEN MATCHED THEN
UPDATE 
    SET C.id_pedido=TC.id_pedido,
        C.id_articulo=TC.id_articulo,
        C.cant=TC.cant,
        C.fecha=TC.fecha,
        C.id_usuario=TC.id_usuario,
        C.[local]=TC.[local],
        C.estado=TC.estado

WHEN NOT MATCHED THEN
    INSERT (id, id_pedido, id_articulo, cant, fecha, id_usuario, [local], estado)
    VALUES (id, id_pedido, id_articulo, cant, fecha, id_usuario, [local], estado);

SET IDENTITY_INSERT pedidos OFF

Any recomendations are welcome, remember that im new with all of this remote conections thing but im willing to keep learning. Thank you!!

Edgar Holguin
  • 179
  • 1
  • 4
  • 11
  • There are many ways to do what you want. I suggest you do some research on SQL Server replication. This is a 'built in' way of making databases copy (publish) themselves to a central area (subscriber). It is a little complicated but does not require custom code and it should make adding more databases easier. There are many ways to implement it, you just have to keep in mind your requirements - 30 minute latency over a VPN - when selecting which method. i.e. you do not need to use mirroring as you don't need your data to be that up to date. – Nick.Mc Feb 26 '14 at 22:59
  • Thank you so much, i was waiting for other opinions but yours really helped me, i searched for server replication and im starting to work on it. Could you make your comment as an answer so i can mark it as a correct answer?, Thank you!! – Edgar Holguin Mar 13 '14 at 16:45

1 Answers1

1

There are many ways to do what you want. I suggest you do some research on SQL Server replication. This is a 'built in' way of making databases copy (publish) themselves to a central area (subscriber). It is a little complicated but does not require custom code and it should make adding more databases easier. There are many ways to implement it, you just have to keep in mind your requirements - 30 minute latency over a VPN - when selecting which method. i.e. you do not need to use mirroring as you don't need your data to be that up to date

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91