June 17, 2021 at 12:11 pm
I have a situation where client gets data feed from another system and then has to replicate the data to an air gapped server. Due to the nature of the setup the current solution is to get a full backup, burn it to media, and restore it to the air gapped server. This accomplishes their goal to update the air gapped server, but the backup from the main system is quite large. Due to the full backup size they only update the air gapped system on an as needed basis. Moving forward they would like more frequent updates. I've considered differential backups but the system that provides the feed has other clients that request backups. So, there is a high probability that additional full backups have been done between the time we get another differential. Does anyone have ideas to get more frequent and smaller backups? I'm thinking about a transactional backup solution. Any ideas would be appreciated.
June 18, 2021 at 1:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 22, 2021 at 9:28 pm
I think the first step is to define user requirements.
My understanding of an "air gapped" system is that the system is on an isolated network with limited access to it including but not limited to having NO access to the internet. This means that your clients would need to be on-site in order to access the air gapped system.
The reason this understanding is important is that it sounds like your clients have access to the air gapped system. If this is the case, can the system be accessed off-site? This is important because if it can, the system isn't truly air gapped as a user could potentially connect from external to both systems. BUT if that is possible, it should be possible to do data replication between the 2 servers without needing physical media to transfer things back and forth by using change data capture and SSIS, or replication, or log shipping.
Now if it truly is air gapped, is it possible to simulate and replicate the data feed data? If you can, then you could replicate that onto the air gapped system.
If that isn't possible, having audit tables on the external facing system that capture changes that you can use to build up scripts to generate what changed in the system. Having a bunch of INSERT, UPDATE, and DELETE statements may be faster than doing a backup and restore and you could likely even have the audit table generated by the stored procedure that processes the data feed. The final statements that happen (INSERT, UPDATE, and DELETE) get written to a second table that can be used to replay what was executed on the base system. Then you have a stored procedure that generates the script to replicate the data over to the air gapped system.
Now that last approach has a lot of assumptions. From the assumption that the data feed is calling stored procedures, to you being allowed to modify these stored procedures, to you have disk space for this additional data, to using that method will be faster than a backup/restore (if it is TONS of data changing per second, the backup/restore approach may be faster). And probably more than I am not thinking of off the top of my head.
As for your differential backup idea, if that is happening on the SQL Server side, are you having multiple clients all writing data to the same database OR do they each have their own databases? If it is a single database, then differential backups may not help, but they still could. The issue with differential backups is they only help if you have a lot of LOG backups to get to a good state. Differential backups eliminate the need for all log backups between the most recent full and most recent differential. You will still be restoring a FULL backup as your starting point.
Another thought is how tight of an air gap is required? What I am thinking here is if you have a secure SSIS server that can connect to your data source, 1 or 2 systems for administration purposes, and to the air gapped system, you could use SSIS to push the data across. Here though you are relying on firewall rules (hardware and/or software firewall) and VLANs to make sure that network traffic is as restricted as possible while still allowing SSIS to pull data from the source and push it to the air gapped system.
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.
June 23, 2021 at 2:48 pm
Thank you for your input. This is a true air gapped system/network and connection is strictly limited to machines on that network. Opening it up to external access, no matter what rules are in place is out of the question. So in terms of updating the sql server involves burning data to disk and physically moving the disk over. I've looked into audit tables as a possible solution.
June 23, 2021 at 4:17 pm
Thank you for your input. This is a true air gapped system/network and connection is strictly limited to machines on that network. Opening it up to external access, no matter what rules are in place is out of the question. So in terms of updating the sql server involves burning data to disk and physically moving the disk over. I've looked into audit tables as a possible solution.
If the database on the air-gapped system needs to be available (online) then one option you have to make that happen and to perform incremental type updates is log shipping. This would allow the database on the air-gapped system to be made available in STANDBY mode (read only).
However - this would require *every* transaction log backup to be copied to the air-gapped system and a process that switches out of STANDBY, restores the log backups and switches back.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply