Stormrage

Have you ever had that rare situation where you need log shipping to another server, but have zero access to the primary server? I encountered that issue recently and my solution was two parts.

A Secondary Log Shipping Target without a Primary. Pretty Cool.

I had the need to consume transaction logs into a StandBy(ReadOnly) database, and continue apply logs to keep it in sync. I could then write code to extract data from that database to the Data Warehouse / Operational Data Store. The original Setup was a traditional Log shipping setup, but the decision was made to allow the Vendor to host the data in their own data center instead of on-prem. The vendor agreed to take an initial backup and put transaction log backups into some Azure blob storage for me to pickup and consume.

My solution ended up to use azcopy.exe and PowerShell to grab the files form blob storage featuring a Shared Access Signature to put them in a folder local to the secondary server, and the to configure the “Lonely Secondary Log Shipping”, so that only the secondary was aware of the arrangement. This has a huge advantage, because the log shipping already has the logic to read the headers of the backups, decide which files with the desired lsn needs to be restored, and cleans up older files automatically. There was no need to re-invent the wheel here.

The PowerShell Portion was fairly simple and clean. I put azcopy.exe in a specific folder on my secondary server, and tracked down the specifics I would need:

• Created the Shared Access Signature for the storage account with a ten year expiry so I don’t have to look at it again.
• copied the url for the storage account.
• Identified the specific Blob and sub directory where the files are being placed.

##copied to this location
$azcopy = 'D:\Data\PowerShell\Azure\azcopy.exe'

## provided, unique
$SharedAccessSignature = '?sv=2020-08-04&ss=bfqt&srt=sco&sp=rwdlacupitfx&se=2032-04-27T09:51:47Z&st=2022-04-27T01:51:47Z&spr=https&sig=nzU%2AnRK6iDC1YYehK7HvoZtPxNVTwpnfxhgGboF6lZc%3D'

## Variables and Defaults
$SourceContainer = "https://totallyfakestorageblobname.blob.core.windows.net/allscripts-pm-db-replication/DataSourceDB/*"
$FinalContainer = $SourceContainer + $SharedAccessSignature
$DestinationDirectory = "D:\logshipping\DataSourceDB"
if(![System.IO.Directory]::Exists($DestinationDirectory)){[System.IO.Directory]::CreateDirectory($DestinationDirectory)}

## Copy Command from Blob to Local Disk, only File extensions common to SQL Backups
&$azcopy copy $FinalContainer $DestinationDirectory --include-pattern '*.bak;*.trn' --overwrite=false

For the secondary Log shipping, I adapted code I had already automated via PowerShell to create only the secondary portions, and parameterized it for SQL instead. little improvements over the default log shipping, like customized names, and staggering the restore to be five minutes after the copy are just pieces that make this a bit more robust. the Primary Server name can be totally fake, we never will connect to it, but it should at least imply where it really comes from. I of course had to restore the full backup first, but after that, there are only four @parameters to change to make this work for yourself.

--#################################################################################################
-- leverage Log Shipping Secondary where the primary may be doing full and transaction logs,
-- but no access between the two
--#################################################################################################
--restore the full backup first, in standby read only
USE [master]
RESTORE DATABASE [DataSourceDB] 
FROM  DISK = N'\\mydomain\DataManagement_Backup\SQLBackups\MyProdServer\DataSourceDB\DataSourceDB_backup_2022_04_27_071611.bak' 
WITH  FILE = 1,  
MOVE N'DataSourceDB' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\DataSourceDB.mdf',  
MOVE N'DataSourceDB_log' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\DataSourceDB_log.ldf',  
--NORECOVERY,
STANDBY = N'\\mydomain\DataManagement_Backup\SQLBackups\MyProdServer\DataSourceDB_RollbackUndo_2022-04-27_07-17-25.bak',  
NOUNLOAD,  
STATS = 5
GO
--On the secondary server, execute sp_add_log_shipping_secondary_primary supplying the details of the primary server and database. This stored procedure returns the secondary ID and the copy and restore job IDs.
--On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.
--On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.
--SKIPPED AS No AccessOn the primary server, execute sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server.
--On the secondary server, enable the copy and restore jobs. For more information, see Disable or Enable a Job.
DECLARE 
@LSPrimaryServer NVARCHAR(128) = 'OffDomainSourceServer',
@LSPrimaryDatabase NVARCHAR(128) = 'DataSourceDB',
--@LSSecondaryServer NVARCHAR(128) = @@SERVERNAME,
@LSSecondaryDatabase NVARCHAR(128) = 'DataSourceDB',
--this is the folder where we expect the log shipping files
@BasePath VARCHAR(8000) = 'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\logshipping\'

DECLARE @copyjobname NVARCHAR(128)    = N'LSCopy_' + @LSPrimaryServer + '_for_' + @LSSecondaryDatabase,
        @restorejobname NVARCHAR(128) = N'LSRestore ' + '_for_' + @LSPrimaryDatabase + '_' + @LSSecondaryDatabase
  DECLARE @ErrorSeverity INT,
                @ErrorNumber INT,
                @ErrorMessage NVARCHAR(4000),
                @ErrorState INT,
                @ErrorLine INT;
DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier 
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier 
DECLARE @LS_Secondary__SecondaryId  AS uniqueidentifier 
DECLARE @LS_Add_RetCode             AS int 


EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 
        @primary_server = @LSPrimaryServer 
        ,@primary_database = @LSPrimaryDatabase 
        ,@backup_source_directory = @BasePath 
        ,@backup_destination_directory = @BasePath 
        ,@copy_job_name = @copyjobname
        ,@restore_job_name = @restorejobname
        ,@file_retention_period = 1440 
        ,@overwrite = 1 
        ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 
        ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 
        ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier 
DECLARE @LS_SecondaryCopyJobScheduleID  AS int 


EXEC msdb.dbo.sp_add_schedule 
        @schedule_name =N'DefaultCopyJobSchedule' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 60 --every 60 minutes
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20090505 
        ,@active_end_date = 99991231 
        ,@active_start_time = 000500 --note copy @ 05 min, restore @10 min
        ,@active_end_time = 235900 
        ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 
        ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_Secondary__CopyJobId 
        ,@schedule_id = @LS_SecondaryCopyJobScheduleID  

DECLARE @LS_SecondaryRestoreJobScheduleUID  As uniqueidentifier 
DECLARE @LS_SecondaryRestoreJobScheduleID   AS int 


EXEC msdb.dbo.sp_add_schedule 
        @schedule_name =N'DefaultRestoreJobSchedule' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 60 --every 60 minutes
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20090505 
        ,@active_end_date = 99991231 
        ,@active_start_time = 001000 --note copy @ 05 min, restore @10 min
        ,@active_end_time = 235900 
        ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 
        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_Secondary__RestoreJobId 
        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  


END 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
END

DECLARE @LS_Add_RetCode2    As int 


IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 
        @secondary_database = @LSSecondaryDatabase 
        ,@primary_server = @LSPrimaryServer 
        ,@primary_database = @LSPrimaryDatabase 
        ,@restore_delay = 0 
        ,@restore_mode = 1  --standby read only
        ,@disconnect_users = 1 --hell yes disconnect those guys
        ,@restore_threshold = 180   
        ,@threshold_alert_enabled = 1 
        ,@history_retention_period = 2880
        ,@overwrite = 1

END 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
END

IF (@@error = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_Secondary__CopyJobId 
        ,@enabled = 1 

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_Secondary__RestoreJobId 
        ,@enabled = 1 

END 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
END

Code Performance improvements are a layered approach. I think of it as multiple passes and focii to

• Statistics. slightly out of date stats on huge tables can greatly affect performance.
• Index Optimization: Review missing indexes, remove unused indexes.
• Data Management Views: find what is slow on the whole server, and start digging in. Not just procedures, but ad-hoc, triggers, Scalar functions all have DMVs.
• Execution Plans. Lots of great information about what it is doing, and with experience, you would know what specific operators imply as far as performance enhancement opportunities .
• the Code itself. I have a long list of the things I look for in the code to avoid performance bottlenecks.

Some of the items we check for:
• Joins are Sarg-able, meaning all data types exactly match on both sides of the equals, no functions on columns, no implicit conversions
• WHERE is Sarg-able, meaning all data types exactly match on both sides of the equals, no functions on columns, no implicit conversions
• @Parameter data types and sizes exactly match the size of columns they are used against
• Replace @TableVariables with #Temp tables to leverage statistics, unless the @TableVariable has less than 100 rows in every situation.
• multiple CTEs or cascading CTEs should be replaced with #Temp tables to divide and conquer for better performance
• anything that uses a VIEW or multiple views needs to be replaced with the underlying tables instead to eliminate duplicate or extra tables, remove convenience for performance.
• convert any scalar functions to inline table value functions, and changes to use OUTER APPLY/CROSS APPLY for values.
• Because FOR XML concatenation is performed early, any concatenation statements must come from a #temp table and not the source, as the WHERE statements are applied after FOR XML is performed.
• Relevant recent comment on every deployed procedure or view
• Every table should be fully qualified as SchemaName.TableName
• Every table should have an alias with Table AS AliasName
• Every column should have alias.columnname
• uses SET NOCOUNT ON in first lines of the procedure
• uses SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in first lines of the procedure as appropriate –remove WITH(NOLOCK) hints in favor of this isolation level
• Handles temp tables proactively, and in both try and catch due to connection pooling issues from java drivers
• Try-Catch is being used as a best practice
• Uses ContentManagement.EXCEPTION.spLogException in the catch statement for better error reporting
• ProcedureParameters is populated with relevant data for better error reporting
• No nvarchars if not relevant
• avoid SELECT *(Enumerate columns, avoid unneeded)
• avoid INSERT INTO #temp WITH ORDER BY(Adds unneeded sort operation)
• avoid EXISTS featuring TOP (WHERE EXISTS(SELECT TOP 1 1…..
• avoid UPDATE on OriginalTableName instead of Alias to avoid an implicit accidental cross join of original table

Without touching the code, I have an incredibly helpful script that finds missing index opportunities, and conveniently scripts out the create command, and keeps a comment header with the script so I know just how valuable it was when i did the analysis.

After that, it’s straight to the DMV’s. I have a nice collection to review performance of Procedures, Ad Hoc queries with plans, and other for lesser impact like the dmvs for trigger performance and scalar function performance. All of them are designed to show me the top x poorest performers, and let me see what tuning opportunities there might be.

Hi I am Lowell Izaguirre, and I’ve am a cutting edge Senior Database Administrator for SQL Server. I’ve got the paper pedigree for those who care, two MCSA’s and an MCSE as well, but the key is my experience, deep scripting capabilities, and the tools I use to make myself the fastest, most efficient DBA possible.

My Daily work is using PowerShell , SSIS or jobs and procedures to automate tasks to free up my time for other things; this has had the side effect of eliminating swaths of work that used to be done manually by my other DBA’s or analysts. I am not automating you out of a job, I am automating so you have more time to work on other tasks.

A lot of people get hung up on titles, but I find a title kind of pigeon holes what people think of you. Enterprise Architect is presumptuous and arrogant, Operational DBA makes it seem like you only monitor jobs and push deployments, Performance DBA, seems to say you just code and look at missing indexes , and Developer makes it seem like you don’t know how to DBA. Where does all the experience with Snowflake, Azure and AWS and Aurora/MySQL fit? Cloud Database Administrator? Code Monkey works better for me. I would love to hear what you think. My Job calls me a “Senior Data Engineer” to imply we do more than just SQL server.

Here’s just some of the tools I use to make me one of the top DBA’s in the country.

SQL Server Management Studio, but on steroids. I use a lot of paid third party plug ins to make my code bullet proof and keep my massive 300+ Snippet collection at my finger tips on every work station I touch. SSMS Tools Pack keeps my Snippets at my fingertips, and also helps me capture and save every SQL statement I execute in SSMS, in a nice searchable format, and also build a custom header rich with information about the server connection. It has to be seen to be really understood how valuable that is.

Redgate’s SQL Prompt boosts my productivity daily, as I use it for these things daily:

• Help write code faster with it’s Intellisense enhancements
• Make my code work in case sensitive environments.
• Add square brackets for better code formatting
• Qualify object names for better code formatting
• Export result sets to Excel for fast ad-hoc reports and info dumps.

A Suite of Automatically generated Reports from SSIS package I wrote, as well as PowerShell Open XML scripting I have written. Those reports execute on a regular schedule, and are often reference for the hundreds of various report focus items I need to review or cross check. Each report is well named, so a DBA can know it’s purpose at a glance, and the report contains information about every SQL server in Central Management Servers’ list of servers. Very helpful when you need to go back and find how something was or was not in place over time.

Tripped over a great example yesterday, where an application was recently upgraded, and ran out of temp db space for the tables, and not the log. The TLDR was that an inefficient query was sticking 500MB of data into a tempdb table to satisfy a query, and the query was called hundreds of times, with various parameters.

How it all happened: I had participated in a third party application upgrade over the weekend, so I was a bit more alert for anything related to that newly upgraded application than I otherwise would be for a long owned app stack.

I got an automated space alert about tempdb log running out of space. By the time I connected, the mount point that contains tempdb files and log was already at zero space. Normally, you would look and say “tempdb log out of space, it has gotta be a long running transaction” I look and the mdf/ndf files are 2.5x their normal size, 250GB instead of 90GB, and the log file is a measly 3GB. yes, there was an open transaction, but that was because log space ran out, and it could not write anymore. so the mdf/ndf were bloating like crazy for some reason.

I used my envwho wrapper for Adam Machanic’s sp_whoisactive to review current queries. I found a stack /dozens of queries running effectively the same query from the application, but with different parameters, and all of them had huge numbers related to CPU, tempdb allocations, reads and writes, as seen below.

Grabbing the query itself, it looks very simple, and really implies no indexes, right? I see a table, the columns in the WHERE will map as the columns in the index i need, and two columns in the INCLUDE portion.

<?query --
SELECT DataID, Name FROM DTree WHERE Status = 1 AND SubType = 482
--?>

My next step was to review the indexes on the table, I doubted there was a matching index but maybe there was one that looked the same, and i could modify it. I ran my EXECUTE sp_GetDDLa ‘DTree’ via a keyboard shortcut to get the table definition, which I found was actually a VIEW instead.

IF OBJECT_ID('[dbo].[DTree]') IS NOT NULL 
DROP  VIEW      [dbo].[DTree] 
GO
CREATE VIEW DTree AS SELECT * FROM DTreeCore WHERE Deleted = 0
GO

And now the core issue jumped right into my face. a view, on a column with no selectivity, [Deleted] = 0. I know that requires a table scan, and with the other columns from the WHERE clause, that’s going to spool into tempdb. yuck. how big is the table? 7.8 M rows.

So At this point, the app is now effectively shut down, no queries going through, and I loop in the lead developer to let him see what I see via Teams. My options are ask for more space, maybe fail the instance over to the other node on it’s cluster to clear all bloated connections that have jammed up tempdb, and then fix it via this indexing opportunity. I got our engineering team to add some space, and as soon as that was added, I got a salute of ran index from the Lead developer, and created an index(my initials _LI_ in the index name so I can differentiate it form pre-existing indexes)

CREATE INDEX IX_LI_DTreeCore_Status_SubType ON [dbo].[DTreeCore] (Status,SubType) INCLUDE (DataID, NAME) WHERE (Deleted = 0)

I immediately went back to my previous query, and see the index greatly satisfied the query and performance; CPU very low, often no tempdb, reads and writes near zero.

Problem solved, and I sent a typical email notification tot he business groups about my findings and solution.

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!