If you watched Scott Guthrie’s keynote at Microsoft Connect() this morning, your mouth is probably still on the floor. There was lot of big news:
- Nearly all enterprise edition features in SQL Server 2016 SP1 are in standard edition
- There is going to be a v.Next of SQL Server and you can play with CTP1 of it today
- SQL Server on Linux CTP1 can now be downloaded and installed
The biggest news of the day is the standard edition news—this is going to be huge for independent software vendors who build their applications on top of SQL Server. While this is amazing news, I wanted to talk about something a little more near and dear to my heart—SQL Server on Linux. You can learn how to install SQL on Linux here.
So SQL on Linux
I’ve had the good fortune of being involved in the private preview for a good while now. Here’s the requisite screenshot of @@version you’ve seen in so many demos.
In this case I’m running a VM on my Mac running CentOS. I also have a VM running Ubuntu and SQL Server running in a Docker container. If you want to go full native, you can use Visual Studio code and run without Windows at all. Aaron Bertand has a great post on how to make this work.
Management of SQL Server on Linux
Aside from a couple of DMVs that show you Linux specific performance information, everything in SQL Server on Linux is the same. Some of the HA and DR functionality is not complete, and the SQL Agent is not done, however you can use cron (and if you’re familiar with Linux, you should learn about cron—I’ll have another post on that next week).
Ola Hallengren’s Jobs on Linux
Many DBAs use Ola Hallengren’s jobs to manage backups and maintenance on their servers. The first thing you’ll want to do is download Ola’s scripts to your machine. You can do this using the CURL command in Linux. In this scenario I’m redirecting the output of the CURL command to a file called ola.sql
curl https://ola.hallengren.com/scripts/MaintenanceSolution.sql> ola.sql
Because of the behavior of the SQL Agent (currently) you will need to set the CREATE_JOBS parameters in Ola’s scripts from Y to N. I used VI to do this—you can read a primer on VI here.
After that—you’ll want to install Ola’s scripts on your SQL Server instance. You have sqlcmd on your Linux install and here you will use the input file flag.
sqlcmd -S . -Usa -Pp@ssw0rd! -iola.sql
You can do this from Management Studio on your Windows machine, or you can just do this from the command line—the nice part about the command line is that automation should be easy and straightforward.
The next thing we want to do is put a backup command in a shell script. In this case I’m just going to grab the backup example from Ola’s site. Use your favorite text editor—mine is VI because I hate myself and create a file called userbackup.sh
sqlcmd -S localhost -U SA -P p@ssw0rd!! -d master -Q “execute [dbo].[databaseBackup] @Databases=’USER_DATABASES’,@BackupType=’FULL’, @verify=’Y’, @CleanupTime=48, @CheckSum=’Y’, @LogToTable = ‘Y'” -b
After you save this file, you’ll want to make it executable. I’m going to use the chmod command to do that.
chmod 770 userbackup.sh
Now this file can be executed. You can do this using the ./ syntax. The output will be returned to the screen, if you are automating the process you can redirect the output to file which you can check for errors.
Date and time: 2016-11-16 10:22:47
Command: BACKUP DATABASE [TestingQuerystore] TO DISK = N’C:\Data\helsinki\TestingQuerystore\FULL\helsinki_TestingQuerystore_FULL_20161116_102247.bak’ WITH CHECKSUM, NO_COMPRESSION
Processed 2032 pages for database ‘TestingQuerystore’, file ‘TestingQuerystore’ on file 1.
Processed 2 pages for database ‘TestingQuerystore’, file ‘TestingQuerystore_log’ on file 1.
BACKUP DATABASE successfully processed 2034 pages in 0.090 seconds (176.562 MB/sec).
Outcome: Succeeded
Duration: 00:00:00
Date and time: 2016-11-16 10:22:47
This is quick primer on getting started with Linux—in the coming months, you’ll be learn more about being a DBA on Linux.
Dear friend,
Why is the backup script pointing to C:\Drive ???, should not be a \tmp (example) or whatever linux filesystem route ???
It’s a bit of legacy from some private previews where everything mapped through the C:\ drive.This syntax works fine (I’ll update post later)
backup database master to Disk
= ‘/var/opt/mssql/backup/master.bak’
[1:05:29 PM] Started executing query at Line 1
Processed 464 pages for database ‘master’, file ‘master’ on file 1.
Processed 7 pages for database ‘master’, file ‘mastlog’ on file 1.
BACKUP DATABASE successfully processed 471 pages in 0.530 seconds (6.942 MB/sec).
Total execution time: 00:00:00.735
jdanton,
thank you for your post.
The BACKUP DATABASE instruction you mentioned works fine for me as well.
But Hallengren’s stored procedures do not work for me on mssql for Linux. These stored procedures have directory path format checks which do not match with Linux filesystem paths (“/dir/subdir/filename”) but are specific to MS filesystems (“X:\dir\subdir\filename” or “\\fileserver\share\filename” etc)
Therefore, Hallengren’s backup script results in an error message:
”
Date and time: 2017-03-12 10:48:18
Server: sqlserver0
Version: 14.0.304.138
Edition: Developer Edition (64-bit)
Procedure: [master].[dbo].[DatabaseBackup]
Parameters: @Databases = ‘USER_DATABASES’, @Directory = ‘/var/lib/mssql’, @BackupType = ‘FULL’, @Verify = ‘N’, @CleanupTime = NULL, @CleanupMode = ‘AFTER_BACKUP’, @Compress = NULL, @CopyOnly = ‘N’, @ChangeBackupType = ‘N’, @BackupSoftware = NULL, @CheckSum = ‘N’, @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = ‘N’, @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = ‘N’, @OverrideBackupPreference = ‘N’, @NoRecovery = ‘N’, @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = ‘AFTER_BACKUP’, @AvailabilityGroups = NULL, @Updateability = ‘ALL’
Msg 50000, Level 16, State 1, Server sqlserver0, Procedure dbo.DatabaseBackup, Line 592
The value for the parameter @Directory is not supported.
Msg 50000, Level 16, State 1, Server sqlserver0, Procedure dbo.DatabaseBackup, Line 911
”
The reason is in line 902 of Hallengren’s sql script:
(…)
IF EXISTS(SELECT * FROM @Directories WHERE Mirror = 0 AND (NOT (DirectoryPath LIKE ‘_:’ OR DirectoryPath LIKE ‘_:\%’ OR DirectoryPath LIKE ‘\\%\%’) OR DirectoryPath IS NULL OR LEFT(DirectoryPath,1) = ‘ ‘ OR RIGHT(DirectoryPath,1) = ‘ ‘)) OR EXISTS (SELECT * FROM @Directories GROUP BY DirectoryPath HAVING COUNT(*) 1) OR ((SELECT COUNT(*) FROM @Directories WHERE Mirror = 0) (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) AND (SELECT COUNT(*) FROM @Directories WHERE Mirror = 1) > 0)
BEGIN
SET @ErrorMessage = ‘The value for the parameter @Directory is not supported.’ + CHAR(13) + CHAR(10) + ‘ ‘
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
SET @DirectoryCheck = 0
END
(…)
Does Hallengren’s tsql backup script really work for you on your linux machine?
Pingback: Friday Reading 2016-11-25 | The DBA Who Came In From The Cold
Found a workaround:
Hallengren’s backup stored procedure works if in the @Directory parameter you add a dummy ‘C:\’ in the beginning and replace all ‘/’ with ‘\’, for example pass @Directory = ‘C:\backup\mssql\hallengren_backup’ if the linux path for the backup is ‘/backup/mssql/hallengren_backup’. It seems that sqlserver for linux disregards the leading ‘C:’ and replaces ‘\’ with ‘/’. Do not use any ‘/’ in the @Directory parameter, since then the stored procedure thinks the location is a remote share.
I have read so many articles regarding the
blogger lovers except this post is actually a pleasant paragraph,
keep it up.
Hi, constantly i used to check weblog posts here in the early hours in the dawn, as i
like to gain knowledge of more and more.
I got the following error message:
The value for the parameter @CleanupTime is not supported. Cleanup is not supported on Linux.
What shall I do now?