SQL Server v.Next—Linux Preview and Ola Hallengren’s Jobs
November 16, 2016 5 Comments
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
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).
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.