I stopped at a waterfall in Iceland to answer your top-voted questions from https://pollgab.com/room/brento.
Here’s what we covered:
- 00:00 Introductions
- 00:28 Accidental DBA: How do I determine if SQL Server instances are licensed?
- 02:22 Jose: What activities do I need to do if I sell managed SQL Server as a service?
- 03:23 Mr. Griffith: How do I defend backups from ransomware?
- 04:16 Accidental DBA: Will you do Office Hours in San Diego?
- 05:07 Accidental DBA: What are the consequences if my SQL Servers are not licensed?
- 05:50 Random Name: Should I use local accounts or domain accounts for the SQL Server service?
- 07:20 Oleg: Should I use INNER JOIN or a comma-delimited list of tables?
- 08:55 Abner: Activity Monitor shows spikes of waiting tasks. How do I dig deeper?
- 10:01 Mr. SQL Seeks: How do I do an impact analysis before I make a change?
- 11:10 Wrap-up
1 Comment. Leave new
regarding “defend backups from ransomware”:
– I create my backups local (for performance)
– I have a second (backup) server on which a script runs regularly (e.g. every hour – better to often than to seldom), that uses ROBOCOPY (part of Windows) to copy new created backups from my SQL server onto the backup server (and if you want onto third remote server)
– pulling the updates instead of pushing it has the benefit, that in both directions only read permissions are neccessary (the SQL Server has to be able to read from the backup server to perform a restore and the backup server has to be able to read from the SQL Server to gather the created backups)
– of course the backup server must not use the same Active Directory account as the SQL Server uses to run the SQL service – be not lazy and create a separate account only for this purpose (and give it only read permissions and of course use a long, complex, random password which does neither include your companies / product name nor the name of your dog or child :-))
– if a backup is still running, while ROBOCOPY is started on the backup server, it skips the not-ready-backups, because they are still locked by the SQL service, so you don’t have to bother with broken / partial backups
– of course you could use most of the professional backup softwares too to create a file backup from your Sql server and write it onto tapes / DVD / special hard disks / wherever
– drawback is, that this software oftens wants too much permissions (often Admin), so if the backup server / backup software gets an infection it can encrypt the backups stored local on your SQL server and / or a third remote server
Script
— do not use /Z (use a mode that allows restart), since it is VERY slow (30 MB/s vs. > 600 MB/s). Its only benefit would be,
— that it does not need copy the whole 100 GB backup again, when it was canceled at 95%
— do not use /MT = use multiple threads, since it would be slower on big files (only ~half speed)
— /NP = no percentage (would be look ridiculus in log files / select output
— /R:3 /W:30 = up to 3 retrys after 30 seconds
— /S = copy sub directories (\FULL / DIFF / LOG), except empty ones
— /XX = eXclude eXtra files – do not log files that exists only @backup_path but no longer on @remote_path (e.g. older backups)
SET @cmd = CONCAT(‘ROBOCOPY /R:3 /W:30 /NP /S /XX ‘, QUOTENAME(@remote_path, ‘”‘), ‘ ‘, QUOTENAME(@backup_path, ‘”‘))
EXEC sys.xp_cmdshell @cmd