SQL Server – DEDICATED ADMINISTRATOR CONNECTION ( DAC )

Background

Trying to review Extended Stored Procedures, but ran into a stumbling issue.

Recreate

Query

Issued a query against master.sys.all_extended_procedures to get a list of extended Stored Procedures.

SQL


select * from sys.all_extended_procedures

Output

Output – Image

sys.all_extended_procedure.20181205.0857AM

Output – Text

Msg 208, Level 16, State 1, Line 17
Invalid object name 'sys.all_extended_procedures'.

Remediation

SQLCMD

SQLCMD – Connect

Syntax


sqlcmd -A -S [computer]

Sample


sqlcmd -A -S localhost

SQLCMD – Query

SQLCMD – Query – master.sys.all_extended_procedures

Syntax


sqlcmd -A -S localhost -Q"select left(name, 30) as [name], dll_name from master.sys.all_extended_procedures"

Sample

sqlcmd -A -S localhost -Q"select left(name, 30) as [name], left(dll_name,30) as [dll_name] from master.sys.all_extended_procedures where name like 'xp%' order by 1"

 

Output

sys.all_extended_procedure.xp_.20181205.0911AM

Explanation

  1. xp_delete_file
    • dll_name :- xpstar.dll

 

SSMS

SSMS – Query – master.sys.all_extended_procedures

Syntax


EXEC sp_helpextendedproc;  

Sample

EXEC sp_helpextendedproc xp_delete_file;  

 

Output

sp_helpextendedproc.20181205.0915AM

Explanation

  1. xp_delete_file
    • dll_name :- xpstar.dll

 

Summary

To access certain SQL Server functionalities, please connect using Dedicated administrator connection (DAC).

 

References

  1. Microsoft
    • Docs \ SQL \ Relational databases \ System stored procedures
      • sp_helpextendedproc (Transact-SQL)

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

Transact SQL – Extended Stored Procedures

Background

Via Transact SQL, SQL Server is one of the more scriptable database platforms.

Historically, some of that management is provided through Extended Stored Procedures.

 

Functions

File Management

Here is a list some of the functionalities that are useful for managing the OS File System.

 

Function Usage
xp_availablemedia How much storage is available on each drive
xp_create_subdir Create Subdirectory
xp_delete_file Remove Subdirectory
xp_dirtree List Directory Tree
xp_fileexist Checks whether file exist
xp_fixeddrives List logical drives on host
 xp_subdirs List Sub Folders under the given folder

 

 

 

Lab

xp_create_subdir


exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLSERVER\MSSQL\DATA\'

Metadata

List of All Functions

To get a list of all Extended Stored Procedures


exec master.dbo.sp_helpextendedproc 

 

Help For specific Extended Stored Procedure

Syntax


EXEC sp_helpextendedproc [function]

Example


EXEC sp_helpextendedproc sp_server_diagnostics

Output

extendedstoredprocedure-serverdiagnostics

Explanation

The output above basically means that the sp_server_diagnostic is provided natively by Microsoft.

If by 3rd party, it would say so.

 

 

Other Functions

Here are other functions arranged by management class.

Management Class Functions
Active X Object Management sp_OACreate, sp_OADestroy, sp_OAGetErrorInfo, sp_OAGetProperty, sp_OAMethod, sp_OASetProperty, sp_OAStop
Prepared Statements sp_prepare, sp_prepexec, sp_prepexecrpc, sp_unprepare
Server Diagnostics sp_server_diagnostics
Server side Tracing sp_trace_create, sp_trace_generateevent, sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus
Local Computer System Management xp_enumgroups
SQL Server Agent Job Management xp_sqlagent_enum_jobs, xp_sqlagent_is_starting, xp_sqlagent_monitor, xp_sqlagent_notify, xp_sqlagent_param
SQL Mail xp_sysmail_activate, xp_sysmail_attachment_load, xp_sysmail_format_query
Registry xp_regaddmultistring, xp_regdeletekey, xp_regdeletevalue, xp_regenumkeys, xp_regenumvalues, xp_regread, xp_regremovemultistring, xp_regwrite

 

 

Server Diagnostics

sp_server_diagnostics

Syntax


exec sp_server_diagnostics

Output

extendedstoredprocedure-serverdiagnostics-output-201612140148pm