Re: Microsoft Sync Services - good solution for me?
- From: "Paul G. Tobey [eMVP]" <p space tobey no spam AT no instrument no spam DOT com>
- Date: Thu, 29 Jan 2009 12:49:57 -0700
I'll just note that you've posted this to a group concerned with SQL Server
Compact Edition (note the .ce at the end of the group name,
microsoft.public.sqlserver.ce). I think you'll get more server-side
information if you post to a regular SQL Server group...
Paul T.
"CraigHB" <craighb@xxxxxxxxxxxxx> wrote in message
news:A3AD8491-A568-4810-99F7-43F9AE0F3201@xxxxxxxxxxxxxxxx
We upload sales transactions from our stores to the headoffice server. At
the
moment, we use DTS (SQL Server Data Transformation Services), but we're
planning on replacing that with Microsoft Sync services for ADO.NET, as
this
seems to be Microsoft's preferred solution for this type of setup and we
want
to follow the standard (that will be hopefully be around for a long time).
Here are the details of our setup and what we're planning. I'm looking for
some advice, especially about whether Sync Services fits into our
solution.
SituationEach store has a 3rd party EPOS system which stores sales in a Microsoft
Access 2000 database, which we can access. Our headoffice database is SQL
Server 2005, but will be upgraded to 2008. The headoffice is not on a VPN
with all the stores, but we can open up our firewall to the stores' IP
addresses, so that they can send data directly to SQL Server. The stores
are
always connected to the internet via ADSL, although they do lose
connection
and we don't want to lose sales data.
We are only uploading transactions from the store - definitions do not
need
to be downloaded.
Current solutionWe have written a Windows service that runs on the store PC. This service
downloads a DTS package from the server (which contains all the details of
the upload) and runs it in the store - and this will upload sales to our
server.
We chose DTS, because it is free when you install MSDE. We can't use SSIS,
because that would require a SQL Server licence at every store.
Another reason we chose DTS is that the details of the upload (i.e. which
tables and fields to include) are stored on our headoffice server, so if
we
need to change things we can do that centrally and don't need to install
anything new at the stores. This isn't a showstopper, but would be nice to
have this ability in our new solution.
Potential solution - Microsoft Sync services for ADO.NETWe are currently building a proof of concept with Microsoft Sync services
for ADO.NET. The idea is to put SQL CE (SQL Server Compact 3.5) in each
store
(client) and sync that to the headoffice SQL Server 2005 database
(server).
We'll get the data into the SQL CE database either by (1) syncing it with
the
Access 2000 database or (2) getting the EPOS system developers to write
sales
straight into the SQL CE database - probably (2). But our main concern is
getting the data from the store to the headoffice server. This method
seems
to be Microsoft's preferred solution for occasionally connected systems
and
that is what made us look seriously at Sync Services.
I'm hoping that using this will mean that most of the work needed to
upload
the sales will be built into Sync Services and we won't have to re-invent
the
wheel.
Potential solution - Upload to a custom webserviceThere is also the possibility of uploading the sales transactions to a
custom web service on our headoffice server and then into our SQL Server
database. This means that we will have to build our own mechanism for
determining which rows are new, and as well as caching for when the
systems
are disconnected. Also, we might be missing out on other functionality
that
will come built into Sync Services.
Please let me know if you have any advice that will help, especially : "Is
Sync services the right solution!". The problem that we are trying to
solve
seems very generic (uploading sales from stores) - and I'd like to solve
it
with a generic solution.
.
- Follow-Ups:
- Re: Microsoft Sync Services - good solution for me?
- From: CraigHB
- Re: Microsoft Sync Services - good solution for me?
- References:
- Microsoft Sync Services - good solution for me?
- From: CraigHB
- Microsoft Sync Services - good solution for me?
- Prev by Date: Microsoft Sync Services - good solution for me?
- Next by Date: Re: Microsoft Sync Services - good solution for me?
- Previous by thread: Microsoft Sync Services - good solution for me?
- Next by thread: Re: Microsoft Sync Services - good solution for me?
- Index(es):
Relevant Pages
|
