SQL Server On Linux / Ola Hallengren Scripts

Background

It is silly to install a database software and not have automated backup in place.

Let us address that.

Ola Hallengren

Outline

  1. Please download Ola Hallengren script
  2. Apply against SQL Server Instance

Processing

Download

Please download Ola Hallengren from here.

Apply

Please apply downloaded scripts against targeted SQL Server Instance.

Issues

Outline

  1. SQL Server Agent
    • Jobs
      • JobStep
        • Please change Step Type from “Operating System (CmdShell)” to “Transact SQL Script (T-SQL)
  2. Stored Procedure
    • dbo.DatabaseBackup
      • Parameters
        • @CleanupTime
          • Make sure it is not set

SQL Server Agent

Jobs

JobStep
Outline
  1. Job Step Type
    • In Linux, the lone Job Step type is “Transact SQL Script (T-SQL)
  2. Change Step from Command Shell verbiage to SQL
    • Backup
      • Verbiage
        • OS
          • sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d AdminDB -Q “EXECUTE [dbo].[DatabaseBackup] @Databases = ‘SYSTEM_DATABASES’, @BackupType = ‘FULL’, @Verify = ‘Y’, @CleanupTime = 72, @CheckSum = ‘Y’, @LogToTable = ‘Y'” -b
        • SQL
          • EXECUTE AdminDB.[dbo].[DatabaseBackup] @Databases = ‘SYSTEM_DATABASES’, @Directory = NULL, @BackupType = ‘FULL’, @Verify = ‘Y’, @CheckSum = ‘Y’, @LogToTable = ‘Y’
Images
Before

SQLServerAgent.step.01.before.20181128.1215PM.PNG

After

SQLServerAgent.step.01.after.20181128.1219PM.PNG

Parameters

CleanupTime

Finding

When cleanupTime is specified SQL Server utilizes [master].dbo.xp_delete_file to remove dated files.

While sanitizing its input, xp_delete_file returns

Msg 22049, Level 15, State 0, Line 0
Error executing extended stored procedure: Invalid Parameter
Msg 50000, Level 16, State 1, Line 1
Error deleting files.

It appears that because the Linux File System uses / and MS Windows uses \ , the system barks.

Workaround

Please skip Timestamp for filename


@FileName nvarchar(max) = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'

 


@FileName nvarchar(max) = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{FileNumber}.{FileExtension}'

Cross Reference
  1. Microsoft
    • feedback.azure.com
      • xp_delete_file on SQL Server 2017/Linux
        Link
  2. Ola Hallengren
    • ola hallengren/sql-server-maintenance-solution
      • DatabaseBackup
        Link

SQL Server Agent – Configuration on Linux

Background

Post installation of SQL Server on our Linux Box, it is time to review our SQL Server Agent component.

Lineage

  1. SQL Server – Installation on Linux/CentOS
    Link

Check List

Let us go through our checklist.

Outline

Is SQL Server Configured?

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services 

Output

sys.dm_server_services.pre.20181127.0602AM.PNG

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Stopped
    • Service Account
      • LocalSystem
    • Clustered
      • No

 

Configuration File

/opt/mssql/bin/mssql-conf

List

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to identify configurable elements.

SQL

sudo /opt/mssql/bin/mssql-conf list

Output

mssql-conf.pre.20181127.0607AM.PNG

Explanation
  1. sqlagent.databasemailprofile
    • Database Mail Profile
  2. sqlagent.enabled
    • Is SQL Server Agent Enabled
  3. sqlagent.errorlogfile
    • Error Log File
  4. sqlagent.errorlogginglevel
    • Error Logging Level

 

Configure

/opt/mssql/bin/mssql-conf

Process

Let us utilize the SQL Server Configuration Utility ( /opt/mssql/bin/mssql-conf ) to make configuration changes.

Make Changes

Command

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

Output

mssql-conf.set.20181127.0610AM.PNG

Explanation
  1. sqlagent.enabled
    • Enabled SQL Server Agent

 

Effect Changes

To effect the changes, please use systemctl

Command

sudo systemctl restart mssql-server.service

Output

mssql-conf.set.systemctl.20181127.0732AM

Explanation
  1. Restarted SQL Server Service to effect the changes

 

Configuration

SQL Server

Metadata

sys.dm_server_services

SQL

select *
from   sys.dm_server_services

Output

sys.dm_server_services.post.20181127.0735AM

Explanation
  1. sys.dm_server_services
    • Startup Type
      • Automatic
    • Status
      • Running
    • Process ID
      • 4116 ( pid )
        • Obviously yours will be different
    • Last Startup Time
      • 1600-12-31 16:00:00.0000000 -08:00
        • Not Accurate
    • Service Account
      • LocalSystem
    • Clustered
      • No

Usage

SQL Server Management Studio ( SSMS )

Object Explorer

Images
Images – Before

sqlServerExplorer.pre.20181127.0739AM.PNG

Images – After

sqlServerExplorer.post.20181127.0740AM.PNG

 

SSMS – Scripting – Job – Error – “msdb.dbo.sp_add_jobserver” – “is already targeted at server”

 

Background

Using SSMS, Scripted SQL Server Job so that I can apply it against other SQL Servers, but ran into error.

Scripting

Script SQL Server Agent Job

scriptJob_20180612_0128PM.PNG

 

Error

Error Message


Msg 14269, Level 16, State 1, Procedure msdb.dbo.sp_add_jobserver, Line 101 [Batch Start Line 8]
Job 'SharePublicly' is already targeted at server 'LAB'.

Troubleshooting

When a job is scripted, the code generated includes a portion that invokes sp_add_jobserver.

The sp_add_jobserver procedure binds the job to the targeted server.


EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
         @job_id = @jobId
       , @server_name = N'(local)'

IF (@@ERROR  0 OR @ReturnCode  0)
      GOTO QuitWithRollback

Remediation

Outline

  1. Check msdb system tables
    • dbo.sysjobservers
    • dbo.systargetservers
  2. If matching records
    • Here are the tables that needs to be considered
      • dbo.sysjobservers.jobName ( @jobID )
      • dbo.systargetservers.serverName ( @servername)
    • Conditions satisfied
      • Local variable @skipWhenJobExist is enabled ( set to 1 )
  3. Action
    • Skip invoking msdb.dbo.sp_add_jobserver

Code Snippet


DECLARE @jobId   BINARY(16)
DECLARE @jobName sysname
DECLARE @serverName sysname
DECLARE @serverNameLocal sysname
declare @commit bit
declare @skipWhenJobExist bit

set @jobName = 'SharePublicly';
set @servername = N'(local)'
set @serverNameLocal = N'(local)'

--set @commit = 0
set @commit = 1

set @skipWhenJobExist = 1
--set @skipWhenJobExist = 0

/*
	Check if Job is already attached to Target Server
*/
if not exists
(

	select
			  [src] = 'dbo.sysjobservers'
			, tblSJS.* 

	from   dbo.sysjobservers tblSJS

	left outer join dbo.systargetservers tblSTS
			on tblSJS.server_id = tblSTS.server_id

	where  tblSJS.job_id = @jobId

	and  

		(

			(
					 ( tblSJS.server_id = 0)
				 and ( @servername = @serverNameLocal )

			)

			or
			(
					 ( tblSJS.server_id = tblSTS.server_id)
				 and ( tblSTS.server_name = @serverName )
			)

		)
	and (@skipWhenJobExist = 1)
)
begin

	EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
			  @job_id = @jobId
			, @server_name = @serverName

	IF (@@ERROR  0 OR @ReturnCode  0)
	begin
		GOTO QuitWithRollback
	end

end

Source Control

GitHub

DanielAdeniji/SQLServerScriptingAgentErrorJobAlreadyTargeted
Link

 

Sql Server Agent – Step – “SQL Server Integration Services Package” – Error – “Errors were detected in the command line arguments…”

Background

Trying to edit the parameters on a SQL Server Agent Job, but ran into a hard wall.

 

Error Message

Error

Image

Textual

TITLE: Microsoft SQL Server Management Studio

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

 

Advanced Information

Upon clicking the “Advanced Information” we will click the message pasted below.

Image

Textual

===================================

Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)

——————————
Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.DTSJobSubSystemDefinition.Microsoft.SqlServer.Management.SqlManagerUI.IJobStepPropertiesControl.Save(JobStepData data, Boolean isSwitching)
at Microsoft.SqlServer.Management.SqlManagerUI.JobStepProperties.OnGatherUiInformation(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.OnGatherUiInformation(RunType runType)

Scenario

This problem is reproducible this way.

  1. Start Sql Server Management Studio ( SSMS )
  2. Connect to SQL Server Instance
  3. Access SQL Server Agent \ Jobs \ [Job Name]
  4. Edit a Job Step whose type is “SQL Server Integration Services Package
  5. There are two tabs, Package and Configuration
  6. Access the “Configuration” Tab
  7. Within the “Configuration” Tab there are three Tabs.  The Tabs are “Parameters”, “Connection Managers”, and Advanced
  8. Select the “ExcelConnectionManager_ConnectionString” parameter
    • Click on “ellipse” button
    • The “Edit Literal Value for Execution” window appears
    • Access the “value” textbox and make cosmetic changes
    • Click the OK button
  9. The error, “Errors were detected in the command line arguments, please make sure all arguments are set correctly. (SqlManagerUI)“, aforementioned appears

 

Quick Points

  1. Other Parameters can be modified
    • This error only occurs when we tried amending the Excel File Connection String
    • We were able to modify other parameters; such as SQL Server and SMTP Server
  2. Connection Manager
    • All Connections ( Excel, SQL Server, and SMTP) can be successfully modified via the Connection Manager Tab

 

Honorable Mention

  1. Cody Konior
    • Agent job errors when running an SSIS package
      Published On :- 2015-August-2nd
      Link

 

SQL Server Versions

Here are the versions that exhibit this issue.

SQL Server Engine

Tabulated

Version Version Literal Version#
SQL Server v2014
Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) – 12.0.5540.0 (X64) 12.0.5540.0

 

SQL Server Client

SQL Server Management Studio ( SSMS )

Tabulated

Version Version Literal Version#
SQL Server v2017
v 17.2 14.0.17177.0
SQL Server v2014
SQL Server 2014 SP2 CU10 12.0.5571.0

 

Image

SQL Server v2017

SQL Server v2014

SQL Server Agent – Error – “Failed to initialize sqlcmd library with error number -2147467259”

Background

Experienced error running SQL Server Agent job that we are developing.

Error

Error Image

Error Message

Executed as user: LAB\mssql. @profileName :- DBA Mail [SQLSTATE 01000] (Message 0) @recipientsTo :- daniel@lab.org [SQLSTATE 01000] (Message 0) @subject :- Privilege Users on HRDB [SQLSTATE 01000] (Message 0) Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050) @body :- [SQLSTATE 01000] (Error 0). The step failed.

Explanation

  1. The error message states “Failed to initialize sqlcmd library with error number -2147467259.
  2. Unfortunately the error message is not precise

 

TroubleShooting

SQL Server Profiler

Trace Definition

Events Selection

Image

Trace Captured

Image

Explanation

  1. Event
    • Event Class :- User Error Message
    • TextData :- The EXECUTE permission was denied on the object ‘sp_DBRoleMembersList’, database ‘master’, schema ‘dbo’.
    • Application Name :- SQLCMD
    • Error :- 229

SQL Server Agent

SQL Server Agent Account

Let us get SQL Server Agent Account

SQL


select 
        tblSDSS.[servicename]
      , tblSDSS.[service_account]
from   sys.dm_server_services tblSDSS

Output

Explanation

  1. We have the service accounts that we are using for the SQL Server Engine and Agent

Remediation

Grant “SQL Server Agent” service account execute permission on targeted object.

SQL Server Agent- Execution Error – “Failed to execute IS server package because of error 0x80131904”

 

Background

I have a couple of SSIS packages to deploy from QA to Production.

Deploy the SSIS packages and ran the SQL Server Agent jobs creations, as well.

Ran into errors and one of them is referenced below.

 

Error

Image

Textual

Date        8/9/2017 3:37:21 PM
Log        Job History (DataRefresh_pp_dem_PROD_to_DEV)

Step ID        1
Server        P-D-RED
Job Name        DataRefresh_ppsivr_dem_PROD_to_DEV
Step Name        DataRefresh_dem_PROD_to_DEV
Duration        00:00:01

Message
Executed as user: LAB-DOM\svcProxy. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  3:37:22 PM  Failed to execute IS server package because of error 0x80131904. Server: p-db-red, Package path: \SSISDB\SSISDB\DataRefrehPackage_Ppsivr_Dem_PROD_To_DEV\DataRefresh_Dem.dtsx, Environment reference Id: NULL.  Description: Login failed for user ‘LAB-DOM\svcProxy’.  Source: .Net SqlClient Data Provider  Started:  3:37:22 PM  Finished: 3:37:22 PM  Elapsed:  0.562 seconds.  The package execution failed.  The step failed.

 

Remediation

For us the problem was that the script we used for creating the SQL Server Agent job was hardcoded to look for the SSIS package in the originating SQL Instance’s SSISDB database.

The fix is to change the Server’s text box text to have the deployed SQL Instance name or simply the generic SQL Server Instance Identifier (local); especially if it is the default SQL Instance.

 

JobStepProperties_2A_20170809_0421PM (BrushedUp)

SQL Server Agent Jobs and extraneous contents in logged messages

Background

This morning I needed to dig into a SQL Server Job that was failing, but unfortunately the error was not being exposed\surfaced.

 

Message

Message – Image

Message – Text

Executed as user: SQLADMIN. Jul 11 2017 12:19PM – Transaction Begin …. [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Transaction Began [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Preparing adding records into [UserAcct].[UserActivity] … [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Adding records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Update records into [UserAcct].[UserActivity] 0 [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committing Transaction [SQLSTATE 01000] (Message 0) Jul 11 2017 12:19PM – Committed Transaction [SQLSTATE 01000] (Message 0). The step succeeded.

 

Explanation

  1. If we take a deep look into the messages pasted above, we will notice multiple occurrence of “[SQLSTATE 01000] (Message 0)

 

Q/A

Googled and found a very helpful comment from Tibor Karaszi.

Tibor Karaszi

How do I suppress [SQLSTATE 01000] messages in sp output?

Link

 

Troubleshooting

SQL Profiler

Ran SQL Server Profiler and here is a screenshot:

Image

 

Outline

  1. SQLAgent – Generic Refresher
  2. SQLAgent – Job Manager
  3. SQLAgent – TSQL JobStep ( Job 0x####…. : Step 1)
    • — network protocol: LPC
      set quoted_identifier on
    • set quoted_identifier off
    • select @@microsoftversion
    • select convert(sysname, serverproperty(N’servername’))
    • SELECT ISNULL(SUSER_SNAME(), SUSER_NAME())
    • SET TEXTSIZE 1024
    • print ‘hello’

Detail

Hello

Hello

  1. Application Name :- SQLAgent – TSQL jobstep ( Job 0x )
  2. NTUserName :- SQLServerAgent
  3. LoginName :- NT Service\SQLServerAgent
  4. Client Process ID :- 2636

 

Task Manager

Reviewed “Task Manager” and confirmed that the process name for process ID is SQLAgent.exe

Remediate

SQL Server Agent Step

Current Job Step Configuration

Revised Job Step Configuration

Changes

  1. Type
    • Original :- Transact-SQL Script (T-SQL)
    • Revised :- Operating System (CmdExec)
  2. Command
    • Original :- exec [csdemo].Products.UpdateCertificatesUserDataAutoConfirm
    • Revised :- sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d demo -Q “exec [csdemo].[Products].[UpdateCertificatesUserDataAutoConfirm]” -b

Explanation

We changed from the internal Transact SQL Script tooling built into SQL Server Agent unto sqlcmd.exe

Sqlcmd exposes a lot more functionality.

 

Recap

Logged Messages

Logged Messages – Original

Logged Messages – Revised

 

Dedicated

Can’t go anywhere but to Tibor Karaszi, SQL Server MVP.

Like Eli Manning an MVP.