I have a Microsoft 2017 SQL database that needs to sync with a remote user that uses Windows 10. The SQL Database resides on a Windows 10 desktop using Syncthing. I have shared the folder containing the database with the remote user. That looks good on both the main computer and the remote computer.
But I can’t open the database on the remote computer. The database is related to a software package called Datair.
Any suggestions to sync the database is appreciated.
You can’t just copy SQL Server database files from one computer to another and have it “just work,” because the files are open and in use when the database service is running.
Generally speaking, anytime there’s a need to share a database between two computers, the most common options are:
Microsoft SQL Server is network enabled and supports replication, but note that remote access requires additional license fees (aka., CAL = Client Access License).
(Since DATAIR is an application for managing employee pensions and benefits, making the database network accessible requires extra care and maintenance.)
I was able to locate the SQL database location on the computer that hosts those files. It was in c:\program files\Microsoft SQL Server.… folder. I am able to sync all the files except SQL Server Database Primary Data files and SQL Server Database Transaction Log files. What might be the reason why those are the only files I can’t sync?
The first file is used by MSQL to keep track of the other files that constitute the database, while the second file is a running journal of every database transaction that includes writing data, reading, etc.
One of the purposes of the transaction log is to provide a safety net for rolling back changes that weren’t fully committed (e.g. power outage while in the middle of an update).
If MSQL is shut down, then it’s a permissions issue for Syncthing (check Syncthing’s log).
But if MSQL is running while Syncthing is copying, those two files will be locked. Files can be open for exclusive access to prevent other programs from reading it because there’s generally no point in any external program reading a database file while it’s open for writing since there can be data queued up in memory that’s yet to be written to the database. Any copying or reading would more than likely be inconsistent or incomplete.
Just stop what you are doing, syncing databases is an insane thing to do, you WILL lose your data.
Use the tools made by the database vendor to perform backups.