December 3, 2008 at 7:52 am
Hi,
I have developed an application that is used by Optometrists to manage their patient records and their POS/Inventory management. It has been developed to work at one physical location and uses SQL Server Express as the back end database.
I have had a request to allow the system to run at multiple locations, allowing data to be shared between the different locations. The system basically consists of three "types" of data:
1. Control/Lookup data that is mostly static.
2. Patient data that consists of contact and examination data
3. Billing/Receipt (POS) data.
How I imagine it running is:
the Head Office sets up the control/lookup data and that gets replicated to the remote locations whenever a change is made.
The Patient data gets replicated across all sites/locations so that the patient can visit any location and their data be accessed.
With respect to the POS side of things, I have not quite worked it out yet, but I imagine that the POS transactions get replicated from the remote locations to the Head Office, but not necessarily sent to all the remote locations.
From what I have read so far, it looks like I need to use Merge Replication in order to have the Patient data replicated across all sites. It also looks like each site needs to function as a publisher and in some cases as a subscriber too.
Does this sound correct?
I understand SQL Server Express has limitations in terms of replication, can it handle my requirements?
December 3, 2008 at 8:26 am
SQL Server Express can only be a subscriber, not a publisher or distributor. If you need this, you'd have to build your own method of copying the data from one SQL Server to the other, or maybe have the main database be Workgroup or Standard Edition to be the publisher.
What type of user interface is the application, desktop forms or browser/web based? If you have a web based interface, then each office would just need an internet connection to access the application running on servers at the main office.
December 3, 2008 at 11:10 am
Hi Chris,
thanks for jumping in!
The application is a Windows Forms app and I can't guarantee an always on connection, or even a broadband internet connection at all sites.
So if they use Workgroup or Standard version at the head office, then SQL Express at the remote sites, the remote sites can subscribe to get any changes made at the head office, but how would changes made at the remote sites get replicated to the head office?
When you speak of developing my own way of copying the data to the head office, do you mean basically creating my own replication scheme where I copy all the changes at the remote sites to an XML file (for example) and then send that (via email or ftp or whatever) to the head office which will then import the XML and handle conflicts? Or will SQL server be able to read the XML and merge/replicate the data accordingly?
December 5, 2008 at 9:30 am
Does anybody have any ideas about this?
December 5, 2008 at 10:54 am
Frank Cazabon (12/3/2008)
...So if they use Workgroup or Standard version at the head office, then SQL Express at the remote sites, the remote sites can subscribe to get any changes made at the head office, but how would changes made at the remote sites get replicated to the head office?When you speak of developing my own way of copying the data to the head office, do you mean basically creating my own replication scheme... Or will SQL server be able to read the XML and merge/replicate the data accordingly?
Using Merge Replication would allow changes at the head office to get to the remote sites and changes at the remote sites to get back to the head office.
If you stick with Express at the head office and remote sites, then you would have to implement 3 things, the code to coordinate transactions at the head office that need to be sent to or received from the remote offices, the code at the remote offices for transactions that need to be sent to or received from the head office, and the code to determine conflict resolution (i.e. the same record was modified both at the head office and remote site between replications) Using SQL Server's built in replication allows you to more easily define how conflicts are handled or resolved. If there is a way to code something to interact with SQL Server's replication to automatically understand the files from the other server, I'm not aware of it, doesn't mean it's not possible though.
December 8, 2008 at 5:25 am
Chris Harshman (12/5/2008)Using Merge Replication would allow changes at the head office to get to the remote sites and changes at the remote sites to get back to the head office.
If you stick with Express at the head office and remote sites, then you would have to implement 3 things, the code to coordinate transactions at the head office that need to be sent to or received from the remote offices, the code at the remote offices for transactions that need to be sent to or received from the head office, and the code to determine conflict resolution (i.e. the same record was modified both at the head office and remote site between replications) Using SQL Server's built in replication allows you to more easily define how conflicts are handled or resolved. If there is a way to code something to interact with SQL Server's replication to automatically understand the files from the other server, I'm not aware of it, doesn't mean it's not possible though.
Thanks Chris,
could you clarify what would be needed if I have SQL Server Workgroup or Standard at the head office? My guess is that I will need to develop code to send the transactions from the remote sites to the head office and then to import and handle any conflicts, but I could use the publisher replication at the head office and the subscriber at the remote sites to get the consolidated database from the head office to the remote sites?
Does that sound correct?
I guess it would all be much easier if I use Workgroup/Standard at each site, then the replication will be pretty much handled for me. If so how do I determine what licensing would be needed for say one head office and 6 remote sites, each running their own LAN with maybe 5 to 10 workstations connecting to the database at each remote site?
December 8, 2008 at 8:54 am
Frank Cazabon (12/8/2008)
Thanks Chris,could you clarify what would be needed if I have SQL Server Workgroup or Standard at the head office? My guess is that I will need to develop code to send the transactions from the remote sites to the head office and then to import and handle any conflicts, but I could use the publisher replication at the head office and the subscriber at the remote sites to get the consolidated database from the head office to the remote sites?
Does that sound correct?
I guess it would all be much easier if I use Workgroup/Standard at each site, then the replication will be pretty much handled for me. If so how do I determine what licensing would be needed for say one head office and 6 remote sites, each running their own LAN with maybe 5 to 10 workstations connecting to the database at each remote site?
Actually, if you have Workgroup or Standard at the main office, it will handle all the transactions both directions using merge replication, and has some mechanisms for handling conflicts. You would only need to write your own code with the Express version at the main office.
http://msdn.microsoft.com/en-us/library/ms165711(SQL.90).aspx
December 8, 2008 at 9:31 am
Thanks very much, you have been super helpful. I think I grok it now. I had understood that for the remote sites to send their changes back to the head office they needed to be publishers, but apparently this is done even if they are subscribers.
December 9, 2008 at 8:11 am
Another way is use Microsoft Sync Framework and the microsoft sample for MSSQL Express
http://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sync&ReleaseId=1200
December 9, 2008 at 10:44 am
Thanks, I'll check it out.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply