SQL Server database backups to AWS-S3

[spenumatsa]

What is the problem you are having with rclone?

Hello, I am trying to configure rclone to mount a S3 bucket and do a backup of a SQL Server database. We have servers in different domains and have service accounts that are specific to them. We are able to mount the S3 backet, but can't backup the database. Below is the entire block of commands that we are using.
...
F:\rclone\rclone mount test:crico-sql-prod z: --vfs-cache-mode writes --allow-other --config=c:\temp\rclone\rclone.conf --log-file f:\rclone\rclone.log --log-level DEBUG
timeout /t 10
mkdir z:\backup
sqlcmd -S CWIPDB1.rmfprod.local -E -d sqlmonitor -b -i "H:\MSSQL\backup.sql"
...

We created a SQL Server agent job and ran the job. The issue seems to be that SQL Server cant know that the drive has been mounted and is waiting. We tried to use a batch file with the mount command in it and then tried to do a backup from SQL Server Management Studio. Seems the OS cant find the Z: drive even though we see it under windows explorer. I also want to know if rclone works when the the user account and the service account are in different domains. Any help is very much appreciated.

What is your rclone version (output from rclone version)

V1.53.2

Which OS you are using and how many bits (eg Windows 7, 64 bit)

Microsoft Windows 2013, 64 bit

Which cloud storage system are you using? (eg Google Drive)

AWS

The command you were trying to run (eg rclone copy /tmp remote:tmp)

start E:\rclone\rclone mount test:crico-sql-prod z: --vfs-cache-mode writes --allow-other --config=c:\temp\rclone\rclone.conf --log-file e:\rclone.log --log-level DEBUG

The rclone config contents with secrets removed.

[test]
type = s3
provider = AWS
env_auth = false
access_key_id = ####################
secret_access_key = ##########################################
region = us-east-1
acl = private
storage_class = STANDARD

A log from the command with the -vv flag

Paste  log here

[asdffdsa (jojothehumanmonkey)]

hello and welcome to the forum,

if you run the rclone mount as system user, then all process should see the mount.
use can use task scheduler to do that.

another option that i use is to mount to a folder, not a drive letter.
rclone mount remote: b:\mounts\remote

--allow-other does nothing on windows

and this post might be helpful, from @ookla-ariel-ride

Sql Server backup to S3 (vfs-cache-mode)

I am actually a SQL server DBA in real life. My recommendation is to enable compression for your backup and also enable trace flag 3042 in SQL Server to use a different backup pre-allocation algorithm that uses less disk space. see https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15 for additional info. Backup compression is simply an argument in the BACKUP command, so very easy to implement. the streaming to s3 sol…

[spenumatsa]

Thank you for your response.

Running the mount command through the scheduler as your mentioned is not an option for us due to security concerns. But it works.

I was able to use the command below to mount the drive through the SQL Server Agent job and do a backup of the database.

F:\rclone\rclone mount --fuse-flag --VolumePrefix=\aws\backups test:crico-sql-prod z: --vfs-cache-mode writes --allow-other --config=c:\temp\rclone\rclone.conf --log-file f:\rclone\rclone.log --log-level DEBUG

However the issue is that the mounted drive remains mounted. We want to unmout the drive once the backup is done. Currently we just kill the process by killing it in the taks manager.

We tried to mount the drive and backup the database by running a batch file with the same command i mentioned above. The content of the batch file is below.

start F:\rclone\rclone mount --fuse-flag --VolumePrefix=\aws\backups test:crico-sql-prod z: --vfs-cache-mode writes --allow-other --config=c:\temp\rclone\rclone.conf --log-file f:\rclone\rclone.log --log-level DEBUG
timeout /t 10
mkdir z:\backup
sqlcmd -S servernamel -E -d database -b -i "H:\MSSQL\backup.sql"

We tried to run the batch file, I can see the drive mounted, the backup folder created but no database backup. Seconds later the command prompt window disappears. When i mount the drive again, i don't see the backup folder. I also noticed the rclone process in the Windows Task Manager running. I was under the impression that when the command prompt window closes, it also closes the rclone processs which does not seem to be the case.

I will keep trying but hope to get some help here.

Thank you.

[asdffdsa (jojothehumanmonkey)]

sure, glad to help,

this is what i do and works well.

1. create a batch file, just for the rclone mount command
2. create a task in task scheduler to run that batch file.
3. use that task to start and end that rclone mount task.

to start a task via command line
schtasks /run /tn "mountx"
to end the task
schtasks /end /tn "mountx"

i do not use sqlcmd, but is that pointing to H: whereas the rclone mount is Z:?

also, if i had a choice, i would create a sql backup to local storage and rclone move that backup to cloud, not using rclone mount

-allow-other does nothing on windows, so i would remove that.

[spenumatsa]

Thank you for the response.

The H: drive in the sqlcmd is where the T-sql script for the backup is located.

If i want to unmount the drive after the backup is done, what is the exact command to do that. I want to try that from within a SQL Agent job.

Thank you once again for your time.

[asdffdsa (jojothehumanmonkey)]

@spenumatsa

unmount the drive after the backup is done

to end the task
schtasks /end /tn "mountx"

[spenumatsa]

We are now able to mount and unmount the S3 backet. However when we try to backup the databases, it would only allow us to backups a certain number of databases before the backup job fails with the below error.

Operating system error 1117(The request could not be performed because of an I/O device error.).

I read that Amazon recommends splitting using multipart uploads for uploading files that are 100mb or more. I could not find anything about multipart upload for rclone. Any help is very much appreciated.

[asdffdsa (jojothehumanmonkey)]

good, you are making progress.

i am curious, about the mount:
are you using schtasks and running the mount as system user?
if not, what was your solution to that?

does the backup always fails that the exact same point in he backup progress or a random point each time?

as for multipart:
https://rclone.org/s3/#multipart-uploads

what is the total size of the backup file?

[spenumatsa]

We are using a SQL Server Agent job to mount and unmount the drive. We however have to make it work through our Job Scheduler.

The backup is failing exactly after a specific number of database backups. When we try it a second time, it fails after doing a couple of database backups.

We are doing backups of multiple databases but the biggest database backup is 800MB.

[asdffdsa (jojothehumanmonkey)]

for what it is worth, for such a small backup size,
i would backup to local, then rclone move it to cloud.

[ncw (Nick Craig-Wood)]

@spenumatsa

Operating system error 1117(The request could not be performed because of an I/O device error.).

There should be a corresponding ERROR in the rclone log - can you post that? (Search the log for ERROR and post those lines).

[spenumatsa]

Here is what we see in the log.

\C:\Users\svc_h12xmapsdevdb1_s\AppData\Local\rclone\vfs\test\crico-sql-prod\DEV\H12XMAPSDEVDB1\RMFPROD\mart_rmf_AON\mart_rmf_AON_FULL_20210211_085602.bak: There is not enough space on the disk.

Does this mean that rclone is using the C: drive for caching purposes?

Thanks.

[Animosity022]

Right, you told rclone to use vfs-cache-mode writes so it'll store locally first and upload it.

You can change the location by using

--cache-dir

[spenumatsa]

Oh Ok. That would be a big issue for us as we would need a lot of space. Our database backups are big and a couple of them are between 300-400GB. Can we backup the databases directly by removing the cache-mode and if so how would that affect the backup uploads?

Thank you very much for your help.

[ncw (Nick Craig-Wood)]

@spenumatsa

Can we backup the databases directly by removing the cache-mode and if so how would that affect the backup uploads?

You can try with --vfs-cache-mode off but unless the backup script writes the file sequentially without seeking back and forth in it, then this will fail.

[spenumatsa]

We disabled the cache mode and tried to backup the database. And our backup script backups the database one after the another. However the backup failed with this error.

2021/02/12 06:07:44 DEBUG : DEV/H12XMAPSDEVDB1/RMFSPROD/DIFDB/DIFDB_FULL_20210212_060730.bak: aborting in-sequence write wait, off=500178944
2021/02/12 06:07:44 DEBUG : DEV/H12XMAPSDEVDB1/RMFSPROD/DIFDB/DIFDB_FULL_20210212_060730.bak: failed to wait for in-sequence write to 500178944
2021/02/12 06:07:44 ERROR : DEV/H12XMAPSDEVDB1/RMFSPROD/DIFDB/DIFDB_FULL_20210212_060730.bak: WriteFileHandle.Write: can't seek in file without --vfs-cache-mode >= writes
2021/02/12 06:07:44 DEBUG

Seems rclone is still looking for the cache mode to be available.

Suresh

[ncw (Nick Craig-Wood)]

@spenumatsa

Seems rclone is still looking for the cache mode to be available.

Yes, it seems that the backup script seeks in the files when reading so --vfs-cache-mode writes is necessary.

Does the backup tool have a streaming write capability? For example in the unix world you might use tar - this can stream its output to stdout which you can then pipe into things. Rclone can take output like this and upload it, eg tar cvzf - /backup | rclone rcat remote:backup.tar.gz or if you have a streamed output then you can use --vfs-cache-mode off.

Failing that you'll have to find temporary disk space for one backup.

If you set --vfs-cache-max-size quite small, then rclone will delete the files as soon as they have been uploaded (after --vfs-cache-poll-interval anyway).