SQL replication across the internet

I would like to ask for some suggestions here, our scenario is; we have our staging SQL server which is part of our lan and we have a separate development lab remotely located, I have been asked to provide the development team access to our internal SQL stating box without compromising any security from our network. Basically they will need to establish a database replication, what would be the most practical way? I have heard about SQL sync but know nothing about it, does anyone here knows about a good solution. I know VPN is one of them but I was wondering to create something more inclined to database replication. Thank you all
LVL 1
jdffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ted BouskillSenior Software DeveloperCommented:
Replication across network segments is messy.  Have you looked at tools from Red Gate software?  They can synchronize schema, data et cetera otherwise I've been at companies where we write our own daily scripts to restore a backup from production in staging or development daily.  The custom scripts can then scrub data to hide customer details to protect privacy.
SStoryCommented:
I can't imagine other than VPN, how you would safely do so.  There would have to be some point to point tunnel, or all data would have to be highly encryted on one end and decrypted on the other, but without the tunnel it could be intercepted and theoretically, given enough time, decrypted.

Microsoft speaks of Merge Replication by using https
https://msdn.microsoft.com/en-us/ms151763

You may also be able to do encrypted connections by certificates directly through SQL Server:
https://technet.microsoft.com/en-us/library/ms191192%28v=sql.105%29.aspx

Of course certificates cost money each year and VPN connections just cost a little time.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
You create the database in your server and give them dbowner permission only on that database. Everything they can do is mess that database. Nothing else.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SStoryCommented:
Yes, you can limit there SQL Server access to dbowner, but if you hook that server straight to the Internet or port forward without any tunneling, it is sure to be a machine that will be hacked. Also your data, if not encrypted could be intercepted by third parties. Those are some of the reasons for VPNs.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you going to give access to the server or to the database?
Also, SQL Server Replication only needs the SQL Server port to be opened in the firewall. You can also add to the IP from the development machine in the firewall so only that machine is allowed to connect.
By the way, in the Replication which Server will be the Publisher and which one will be the Subscriber?

But if you'll permit them to login in your SQL Server box, then yes, VPN should be solution.
SStoryCommented:
Vitor Montalvão,

I am assuming that he means the two locations are distant and can only communicate over the Internet. That being said, wouldn't you think that an unencrypted link that could be intercepted and hacked would be a bad idea for the OP?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why only by internet? Why not a dedicated connection between the offices?
Anyway with firewalls and the respective rules opened (IP + port) and the restrict access to the database how can they hack the system? Eventually the database can be hacked but is a staging database.
Another option is to invert the Replication and that's why I've asked about which server is the Publisher and which one is the Subscriber.

Will like to be clarified also by the OP.
SStoryCommented:
Vitor,

Well, I assumed by the internet since the OP was concerned with security. Personally, if I had private, dedicated fiber between two buildings, I wouldn't be concerned in the slightest, unless I thought people were going to dig up the fiber or climb a pole and somehow intercept without us knowing it...so I assumed "the internet."  Also because the title of the question is:

"SQL replication across the internet"  I think the assumption is correct.

That being said a VPN is the obvious solution.

I suppose a good firewall would help, but I'd personally want a VPN where the data is encrypted, and the entire channel is encrypted. It seems it would leave less opportunity for hacking.

Hackers exploit software errors to do things we don't even consider doing, like buffer overruns, which often allow code to execute at a greater privilege than intended by overwriting the data section with exectuable opcodes in machine language, etc. I just feel a VPN is safer.
jdffAuthor Commented:
I've installed hamachi and it is working like a charm. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.