1

A co-worker and I are working on some Pharmacy software (in C#) which deals with the management of patient profiles, patient drug prescriptions, etc. All of these different sets of data are stored in a sql server database (we're using 2008 standard but future versions are fine too). Each store has its own sql server instance on a local machine.

Our Goal:

We want to have "Store A" be able to access "Store B's" databases if need be. Basically in the event that perhaps a pharmacy customer is out of town and visits one of the other pharmacy branches.

Things I've thought of:

  1. My initial thoughts were to basically keep an online server instance of sql server which could be accessed through a dns link (or perhaps IP). I was trying to figure out the best way to keep these in sync and I came across sql servers replication. Problem is I was going to use Transactional Replication with updating subscribers but since it's deprecated It's not really a long term option anymore. Microsoft suggests using p2p replication, but that requires enterprise edition and we're really trying to avoid that if we can. I wanted to use a transactional type of replication since it does a much better job of keeping records consistent (not having to wait for something like a merge agent job to run every hour or something like that).

  2. Something I've thought about more recently is maybe having an internet based sql server instance, which would contain nothing but linked servers back to each stores local machine. I wouldn't have to worry about sync problems if other stores just worked directly off each others local machines. But I've read of a lot of people saying that this is a horrible security vulnerability so I'm not sure if this is even a plausible idea but I think maybe there's some way to make this work?

Anyways so this is the basic gist of what we're trying to do. I don't know if replication or linked servers would be the better route to take.

Edit:

What about bi-directional replication? I was reading a little bit about this but I'm a little unsure about if this is what I need or not. I don't want to have to stagger primary keys between servers or anything, since they are pretty important in identifying prescription numbers and stuff like that. But if I could do bi-directional replication, that could be good too.

Vance Palacio
  • 1,280
  • 12
  • 17
  • Does each location need its database locally? Is it an option to have one database server that they all connect to remotely? Are these different stores on a shared network or will they need to go to the internet to make their connection? – liebs19 Jun 06 '13 at 01:53
  • Yea we want each store to have a local database. In the case that the internet goes down, or gets really slow, we don't want that to be able to effect the stores ability to serve customers. But no they'll have to connect through the internet some how, there's no shared network between them. Can you join a shared network between computers in different cities? – Vance Palacio Jun 06 '13 at 02:15
  • You can have a shared network but I believe it requires direct connections by your isp. I'm not 100% sure how this gets setup. I have some experience with linked serves and they work well but I've never done it over the internet so I'm not sure about the security aspect. I'm also not sure how difficult it would be to setup this replication over the internet or if that would have any security risks. I think any of your ideas will work, sorry I can't be of more assistance. – liebs19 Jun 06 '13 at 03:27
  • Hey its all good man I appreciate you taking the time to look over this. – Vance Palacio Jun 07 '13 at 03:32

1 Answers1

0

Not really an answer but I have more space...

SQL Azure is a the 'cloud' version of SQL Server. A VPN is a way of creating your own private network over the internet. Do some research on these terms. Many applications are going cloud nowadays. You should really consider the likelihood that there will be no internet access.

With regards to replication, you can 'roll your own' replication if you own this application and you are happy to support it.

The basic premise is:

  1. Create a trigger on every table which writes the PK of every change to a log table
  2. Create a process which manages copying and merging only changed info (based on the log table) using subscribers and publishers
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Hey thanks for the info man, I'll definitely look into some of that stuff and see if I can figure out a better way than what im doing now. I think the triggers might actually be the best way I could go – Vance Palacio Jun 07 '13 at 03:29