February 3, 2022 at 10:51 am
Hello!
We have a local SQL Server 2016 instance (soon will be updated to 2019 or newer) with a few databases for our ERP and different other software. We need to have some of the data from these dbs on our Azure SQL database as well. The data on azure can't be changed, so it is a pure Local db to AzureSQL data mirror.
Currently, I have SQL Data Sync Agent running, which created tracking tables and triggers at both sides and moves data just fine. However these triggers somewhat affect performance and create multiple problems on both the azure db (can't export the bacpac file from the db, sync agent objects cause an error, I need to create a copy from the azure db, drop the tracking stuff and than create the bacpac) and the local db as well (makes erp software updates, especially database migration a difficult tasks, I always have to drop the sync agent settings, tracking at both sides. migrate and setup sync agent from scratch again - also recreating the local tables on azure when the schema changed).
What other options I have? I only want specific tables of the local databases to be replicated to AzureSQL tables (can be all columns as well, does not matter).
I found other people mentioning transactional replication. Should I use it for this problem? As of my understanding, I need to setup replication between the local sql and the azure sql db and I can somehow specify certain tables to be replicated.
One thing I like about the data sync agent, is I don't need to open ports on my local server, the sync agent uses a client on the server to push the data. When doing replication, can the distributor be located on the local server and work without opening sql server ports?
Another issue is how much maintenance a replication like this needs? Can it work most of the time without problems or it will break all the time even when there are network connection issues?
Thanks for the help!
Edit: We use DTU based, AzureSQL databases (not the vcore based managed instances)
February 3, 2022 at 2:21 pm
I can't speak to replication from on-prem to cloud as I've not done that, but you ask what other options do you have and there are options. You could use service broker to push across the data you need or if it is a small number of tables, it may make sense to use a trigger with a linked server to push the data across.
I am pretty sure that with replication, and the options I suggested above, you are going to need to open the firewall so that the 2 servers can see each other. In the case of replication, you need a publisher database which would be what you have on prem, a subscriber database which is your azure one, and a distributor. The publisher can be the distributer, but doesn't need to be. BUT the subscriber MUST be able to see the distributer database.
With the sync agent, I expect you still had to open some firewall ports, they may have just already been open. And the firewall ports are not to the database directly, but indirectly through the data sync agent.
As for replication breaking, it shouldn't break, but things can get out of sync if there are network connection issues. USUALLY these should fix themselves on their own, but it can take time for it to complete.
One other option you have (that I can think of) if having 100% uptime and having the most recent data is not important and you have a downtime window in your environment is to do a full backup of the local DB and restore it to the Azure side nightly. You'd need to adjust permissions post-refresh to ensure that the data is read-only, but post refresh scripts are not that hard to do. Alternately, SSIS could do scheduled syncs between the 2 systems.
Now, which is the best solution? it depends on what you need. They all have pros and cons. Service broker runs great once set up, but can be hard to manage if problems arise or if it is configured incorrectly it can cause pretty bad performance hits. But service broker will ensure eventual consistency as long as the network connection comes back up and is asynchronus so the impact to your system is light when configured correctly. Triggers can cause performance impact and if the linked server is unavailable (network issues for example), the whole transaction may be rolled back. The plus side is that they are EASY to set up and maintain. Replication adds overhead of more databases needing to be in place but has the benefit that things should also be eventually consistent. SSIS and the backup/restore solution result in windows where the system may be unavailable and doesn't offer "near-live" data. The plus side is that it is easy to maintain, test, and support and it can be re-run to force a sync at any time with a window where the system will be unavailable.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 4, 2022 at 8:52 am
Thanks!
Sync agent works by installing an application which connects to both the local and the azure database via SQL login (local sql is on the same network, so I don't need to open 1433 on a public IP).
"I am pretty sure that with replication, and the options I suggested above, you are going to need to open the firewall so that the 2 servers can see each other. "
I created a test vm (ony my computer) with a copy of my database + sql server dev and a test basic azure db. I only had to add a publication and a subscription on the local vm. It worked without opening a single port. It should work in push mode. I tried to make DDL changes, adding new tables to the publication and changing data and it replicated to azure without an issue. (I mean, I had issues setting it up, but now it kinda works). I guess it works by simply pushing changes to azure.
"One other option you have (that I can think of) if having 100% uptime and having the most recent data is not important and you have a downtime window in your environment is to do a full backup of the local DB and restore it to the Azure side nightly."
This solution is also interesting, if I have problems with replication I may just go this route. I don't need real time changes.
I followed these guides:
February 4, 2022 at 2:17 pm
If replication from on-prem to the cloud works without firewall changes, I would be suspect of your firewall. I would double and triple check your firewall configuration to make sure it is blocking things the way you are expecting them to be blocked.
Connections from anywhere external to my internal databases are locked in my environment. There is no way for an external system to see my internal system unless we move the VM into the DMZ and I'm not doing that with any SQL instance I care about. Connections from internal to external on the other hand depend on your configuration. I know we have ours locked down so only SOME traffic is allowed to go out so we can download updates and such. But I can't connect (for example) from my SQL Instance Server out to an FTP.
I personally would reach out to your network team to see what restrictions are in place on that machine and reach out to your security team to make sure that those restrictions are what you want and require or if it should be more secure. For me, I want my SQL instances as secure as possible as they contain most of the company product data. This would be bad for the company to get leaked or lost, but if (for example) you are storing PII, that can be a lot of legal trouble if it gets leaked. Thankfully we don't store any PII on our instances.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 4, 2022 at 2:56 pm
We do not have a single port open for our SQL Server. No one should be able to connect from the internet to our server through public IPs.I even tried to login via our public IPs on port 1433 using SSMS on a remote computer and it did not connect.
I specifically blocked all incoming traffic on the VM on port 1433, but the replication still works. I even checked UPnP, but it does not log an entry on our firewall.
Isn't my configuration supposed to be push only? The remote server should not be connecting to my local server, when all I am doing is pushing changes there. The only open port needed is on Azure's side.
The publication and the subscriber are both registered on my side (like this), I did not create the subscriber on Azure.
February 4, 2022 at 3:21 pm
If you configured the publisher and subscriber on the local system, then nothing should be going to Azure.
My understanding of replication is that you need to have a publisher which pushes data to the distributor and then the subscribers pull data from the distributor or the subscribers can have the data pushed from the distributor. That article you linked sets up the publisher as the distributor and the distributor is configured to push data to the Azure side as a subscriber. This will still require 2 firewall rules to be in place or at LEAST not blocking. You would need one outgoing port open to send data from local to the cloud and one on the cloud open to get data from local.
I am hoping that your cloud one is configured to be configured to only allow connections on the port from a specific IP or IP range though.
Where I work, we block all incoming internet traffic to our internal servers and we block MOST outgoing traffic. In your case, it looks like you are allowing the outgoing connection that the distributor is using to push the data to the subscriber. You would need an INCOMING port open on the Azure side and an OUTGOING port open on the local side.
If you have a server that is something that doesn't need outgoing data, like a server that is designed to hold all backup files, you may configure your firewall for no outgoing connections. You likely want to block outgoing traffic on all ports known for virus/malware traffic. That way if your server gets compromised, the virus won't be able to talk back to the control center for example. With some of the code floating around in the wild around the Log4J bug, a server could be compromised and used for crypto mining for example. That is actually a scenario I have seen happen and if the proper ports and IP's are blocked for outgoing connection means that the crypto mining tools don't work so the server that is compromised doesn't get hammered with constant high CPU due to the mining. The mining stuff can't start because it can't talk back to the host.
Just as an example. It may be that your server doesn't need to be as secure or that I am going overkill on mine, but mine were set up that way by our network team and the security team to determine how secure the server needed to be.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 7, 2022 at 6:34 am
"This will still require 2 firewall rules to be in place or at LEAST not blocking. You would need one outgoing port open to send data from local to the cloud and one on the cloud open to get data from local."
On the test windows VM I placed my publisher and subscriber I blocked ALL incoming traffic on all ports and all protocols using windows firewall. Changes made on the DB are still replicated to Azure. It does not seem like Azure is connecting to my local vm at all. So as you said, I will need only an outgoing port for it to work. We by default do not block outgoing connections. Do you think this is an issue? Regarding leaks, for me, It does not seem like blocking outgoing connections for 1433 would count as protecting from data leaks but I am not a security expert. One could use just simple HTTP to send data out and you can't block that.
"I am hoping that your cloud one is configured to be configured to only allow connections on the port from a specific IP or IP range though."
Yes, we configured it that way.
February 7, 2022 at 3:01 pm
My opinion - blocking outgoing traffic is only required if there is a risk of the server being compromised. If you can download and install arbitrary applications on the server or the server has the potential for a security risk, I would lock the server down as much as you can. In the event that the server does get compromised with a RAT, Trojan, cryptomining software, ransomware, etc., if the tool can't talk back to the control server, the compromise MAY not take place. You say it could use simple HTTP to send data out, but you can block that. Port 80 will block HTTP traffic and we have servers on-site that have port 80 blocked for outgoing except on internal networks as MOST of our servers don't need that. We have a WSUS server in place to handle windows and other Microsoft updates and have a 3rd party tool to download application level updates.
ALL network traffic requires a port to communicate on and in a good firewall, all network traffic can be blocked. Do this carefully though because if you just blindly block ports, you may end up blocking RDP and RPC ports and now you have no way to manage the system. Most firewalls also allow for IP filtering for a port so only specific IP's are allowed to communicate on a port.
It really depends on how paranoid you are about hackers getting into that system and what they can do once they are in. SOME systems need access to the internet to send and receive data, but for those, we still block specific known bad or known non-work related websites as well as block specific IP's and IP ranges for known bad hosts (known for hacking purposes). It really depends on how secure you need things to be. If your system contains PII, I would want it to be as secure as possible, which means I would want at a minimum, SSL required for connection to the system (protect data in motion) and TDE enabled (protect data at rest). I would want this for both internal and external communication including syncing to Azure and I'd want both those things set up on the Azure side too. On top of that, I would probably also want row level security in place and some columns to be encrypted to ensure my data is protected. Backups would also need to be encrypted. On top of that, I would want to reduce the incoming ports that could be used to compromise the system to a minimum, and I'd want to restrict the outgoing ports to ensure that if it did get compromised, that I could say I did my best to protect the data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply