Setting up Mssql backups with backup to s3 (Selectel)
This article is not related to HOW-TO, but I will still try to describe in detail the process of creating/connecting a bucket and setting up backups directly from Mssql.
Important, support for uploading to s3 is described for mssql versions from the beginning of 16.0
We register on Selectel, create a bucket (private, cold storage) via the web interface.
We create a user in the “Users” section, be sure to check the “Use this data for access via the S3 protocol” checkbox.
Selectel has an object storage account in the format
It is important to make sure that there is no colon (:) in the password. This is a prohibited character for S3 in login/password, since the format
Далее пользователю нужно выдать права чтение/запись на бакет (контейнер) и применить.
В противном случае будет ошибка доступа.
Далее, в Mssql необходимо создать учетные данные S3 ресурса. Это делается запросом в консоль
CREATE CREDENTIAL
CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>]
WITH
IDENTITY = ‘S3 Access Key’,
SECRET = ‘
[s3://<endpoint>:<port>/<bucket>] – link to S3 resource. In our case, we use a link to the s3 resource selectel and the test1 bucket, without specifying the port.
IDENTITY = ‘S3 Access Key’,
Important, we leave it unchanged – it lets the SQL server know that this is authorization data for S3
Total, for our case –
WITH
IDENTITY = ‘S3 Access Key’,
SECRET = ‘99999_Delaney:HBqXu-1[(<‘;
А в случае ошибок с логином/паролем-
DROP CREDENTIAL [s3://s3.storage.selcloud.ru/test1]
Next, we begin to create a backup plan.
We go into management studio, create a plan, give it a name.
Go to the plan itself, inside the nested plan, open the elements panel, and select “database backup task”, drag it. If desired, RMB the task and you can change the name.
Open the settings, basically select the type of backup, database (one or more), and the item “create a backup copy to a URL address”.
It is important that when selecting several databases, copying will take place on an S3 resource inside one bucket! (features of point 1). I wanted order, so I had to do several tasks in one plan, for each database separately.
In the target object, select the SQL credential (we created above).
Azure storage container – directly the path to the desired folder. In our case, bucket test1, folder test_it. (test1/test_it) The test_it folder will be created automatically
Important. If you edit in this field, the URL prefix will need to be corrected, otherwise it will automatically change to something like this “https://s3 access key.blob.core.windows.net/test1/test_it“, which would be incorrect. I advise you to skip this point, since it will be automatically substituted when specifying a prefix.
in the URL prefix you must specify s3://s3.storage.selcloud.ru/test1/test_it, where s3:// indicates the need to upload to an s3 resource, s3.storage.selcloud.ru is a link to the server, then the path in bucket format /folder.
I advise you to leave the file extension standard.
In the parameters you can specify your own settings, I have these:
After saving we see this picture:
At the current stage, the task will create a complete copy of the IT database and upload the resource to s3.
Next, we create an identical task, only we mainly change the creation of a backup copy to disk. In the target object, select “create a backup file for each database.” And select the folder where you want to save the backups. Let’s say X:\Backup
In this case, backups of the selected databases/bases will be saved directly to the Backup folder.
When you check the box “create a subfolder for each database,” a subfolder with the name of the database will be created in the Backup folder. Those. backups will be saved in X:\Backup\base name\
Next, you need to create a chain with the order of tasks, otherwise difficulties may arise (problems p2). This is done simply by dragging the green arrow from the main task to the next one.
My priority is to save first to s3, and then to a local disk. In your case, you can create absolutely any plan. In my next steps, I configured post-maintenance cleanup, which deletes old backups from the disk.
Important. Unfortunately, I have not yet been able to configure automatic deletion of backups from s3; for now I have to do it manually. But if you give me some advice, I will be grateful.
Next, in the plan designer, you can specify the time for your plan to automatically start. (it is indicated separately for each nested plan)
Next, we save, and we can check in the plans that the plan is working correctly, RMB>execute.
If there are several nested plans, there will be an error. You need to run the task manually inside Agent>tasks>. The task will have a name in the format Plan Name.Nested Plan Name (for example, test1.NestedPlan_1).
In my case, the database backup scheme turned out like this, mostly due to the fact that I didn’t want my s3 bucket to have a chaotic list with all the backups. I don’t pretend to be the only correct option because I myself am only exploring the possibilities. If this is not critical for you, then in general one task will be enough for the s3 backup, and the second for the local one. Perhaps local backups should be combined into one task in order to simplify the scheme. But initially I planned to do not a chain of tasks, but parallel ones.
Peculiarities
1. When downloading via URL within one task, you can download backups only to one bucket/folder. Although, when choosing to save to a hard drive, it is possible to save to a subfolder with the name of the database, which creates a normal and convenient hierarchy.
In general, when saving to a URL, this is tolerable, but when there are more than several databases and a large number of backups, it is extremely inconvenient and loaded, in my opinion.
2. URL prefix can only be used in lower case. Although on the S3 resource itself, case does matter, and if you have a bucket called “Backup” on S3, then when you enter the URL into the task it will look in the format: s3://s3.storage.selcloud.ru/backup/
It seems logical that you need to check that the bucket is in an identical register. But this turned out to be not obvious to me, and I suffered for a long time looking at the error with the incorrect path. Accordingly, I had to use all names in lower case.
3. When enabling logging within an agent task created by a service plan, I encountered a non-obvious, but quite logical phenomenon: in the event of any changes within the service plan, these settings are lost. (if there were edits for a specific task)
Problems
1. For some reason, when logging is enabled in the maintenance plan itself, a log file is created and displays only the first running task. What does the display of the request have to do with it? But there is no more particularly useful information.
Personally, I don’t understand exactly how and in what order tasks are launched if the tasks are not in the form of a chain, but are simply located next door.
What does this have to do with the peculiarity I noticed – if there are more than five backup tasks, then it gives an error for one of the databases
161(The path specified is invalid.)
161 (The path specified is invalid.) Possible reasons for failure: There are problems with this request, the ResultSet property is not set correctly, the parameters are not set correctly, or the connection was not established correctly.”
The most interesting thing is that if you check the task separately, then everything works correctly. If tasks are combined into a chain, then everything works correctly.
In general, it was logical that tasks should either be performed in a chaotic selection, or only one should be performed, or an error. But the error concerns specifically writing to the s3 resource, which is strange.
Error “Operating system error 5 (Access is denied.)”
In this case, the server cannot connect to the path you specified with the specified login/password.
