I love Log Shipping

I have found one thing in SQL I do very, very, very well…  Can’t you tell by the title?  There are times when to use and not to though…

Very good times to use it:

  • Migrating a “live” database from one server to another.
  • Moving a cluster from one domain to another while keeping the primary site up.
  • Non-automatic failover DRs
  • Setting up an initial DR system in house or across a WAN.
  • Creating a read-only server where the data is allowed to stale for long periods of time.
  • During setup of the SQL server pair for the HA Availability Group (especially on larger databases) or any other major setup (replication) before switching this DR structure “on”.
  • Where you have two sites where you need to go through some sort of FTP connection to transfer the data.
  • Where you are limited on budget and need to have a warm DR server up, but you are limited to only one DR SQL server. You can easily set it up for a many to one scenario.
  • You are limited to SQL Server Standard only.
  • Where another copy of the database already exists on the server, but you need the one from the other server for DR purposes. Example: we have DBA_MaintenanceDB on every SQL server (every server).  It is meant for the DBA’s to store data in (like old backup information from MSDB, SP_AskBrent results, database inventories, and all sort of other junk out there), but B.M.T. the company had a DBA that thought he would write SPs in there to run against the primary production database.  Well he left and never transferred the knowledge.  Until we get that mess cleaned up and sorted out, we need to have a copy of the DBA_MaintenanceDB on our DR server, so it on the secondary server it is named LS_Prod_DBA_MaintenanceDB  so we still have all of those SPs.

Bad times to use it:

  • For a reporting server that needs to be kept as up to date as possible.
  • For a reporting server where you need different securities on the database than in production
  • You are horrible at remembering the difference between “With Recovery” and “With NoRecovery” (I raise my hand to this, and it’s annoying)
  • Your RPO and RTO for DR is less than 5 minutes each.
  • Remember this: anything that happens in the database on the production server will occur to the log shipped copy on the other server after all of the TRN files have been restored.  So if you need another copy of the DB, yet something has to be different, don’t use log shipping

I have been watching the Logshipping BrentOzar blog like a hawk now, just because it’s fun to try to help out others in the understanding of how Log Shipping works.  In doing this I have learned more than I knew before, but still learn more every time someone asks another question.

I will dig into details on this more as the days go on.

EMC DDBA Version 2

Okay, it seems like I am slamming EMC on their Data Domain Boost Agent since I began this blog.  Oh wait I started three days ago and am talking about something I just learned.

We were led by our noses to change from our old methods of backups (the other DBA and I were kicking and screaming horribly along the way).  I admit it we were bad.  Hey, change something that you have been using for 15 years (started with SQL 2000 remember).  Even backing up to tapes (yes, I was a backup administrator at one point) I always backed up to the bak file (or bck is what I started with) first and then backed them up to tape or external storage.  We knew the bak’s were always (well almost always) good.  I can rely on them.  Well now we are switching to the EMC Data Domain system.  It’s got a lot of neat bells and whistles that you can read about on their site, but the one thing we were sold on was the EMC Data Domain Boost Agent.  This tool was advertised to make our lives easier and faster.  They admitted straight out of the gate that their GUI tool for SMSS was lacking and that we would have to write scripts to restore database to other servers, so we were expecting that.

After figuring out how to write a script (yesterdays post) to make our life easier to use this tool with, I moved to our 2TB backup.  We were scared, especially after reading Denny Cherry’s written experience with a 6TB DB.

I began running the scripts and the first backup took 13 hours. OUCH!! But we had been forewarned that the first backup took longer than normal.  Okay I can live with that.
Day 2: 9 hours and 21 minutes…  Okay, must be settling in.
Day 3: 9 hours and 24 minutes…  Uhm, is this normal?
Day 4: 13 hours and 41 minutes…  Whoa, something has got to be wrong
Day 5: Squirrel!!!! Yes guilty of being sidetracked at this point with more pressing issues… 🙂

Day 20: 20 hours…  Our snap process (we use this on our replicated copy (this was our 2TB test server) to grab DEV environments) could not occur because of this.

Now the backups are creating an issue.  Now the emails really started to fly around that these backups were taking too long.  EMC worked with us but gave us a bad taste in our mouth on how we were to handle this.

“Install the Beta version of DDBA 2.0.”

“Wait you want us to install a Beta copy of a program we are having issues with!”

Doesn’t sound like the smartest thing to do, does it?  Well after some hemming and hawing (and more sidetracking) we finally sat down and got a copy of their 2.0 product.

Night and Day

Yep, in installing Version 2.0 Beta, I went from averaging 13 hours to 5 hours (just like we were before we switched to their tool in the first place)

Also, we could now successfully see our backups from the other servers that we couldn’t before.  This allows me to use their GUI to restore a backup from the SQL1 bucket in the Data Domain to the server SQL2.

Now if only they could fix that pesky UAC issue (you need to start SSMS in Admin mode in order to restore a database using their tool even if you are a Server Admin and DB System Admin).  But otherwise this is a 100% turn around and can’t wait till it is officially released at the end of the month.  🙂

EMC Data Domain backup script based off of Ola Hallengren’s SQL Server Backup script

When I started as an official DBA four years ago, I was taught of the wonders of some of the big names in the SQL community. Brent Ozar and Kendra Little (and the rest of their crew) from http://www.BrentOzar.com, Pinal Dave and his blogs through http://blog.SQLAuthority.com, Denny Cherry at http://itknowledgeexchange.techtarget.com/profile/mrdenny/, and those that I did not mention who I have referred to by a simple Google query.  But the one I was not introduced to until I started working at my current job last year was the one that I think I have used the most since starting here.  That man is Ola Hallengren and his site https://ola.hallengren.com.  It used to be huge chore to setup and re-set up backups through maintenance plans. Ola’s backup script (https://ola.hallengren.com/sql-server-backup.html) became my bread and butter for normal MS SQL backups.  But as my last post pointed out we moved to EMCs Data Domain.

Their initial scripts are horrible.  There is no logic to them.  We were back to setting up individual backup scripts for each database.  If another database was added we would have to set it up again.  If a database was dropped we’d have to remove the script from the job so that we wouldn’t get the error emails. Oh, I could just go on…

The other DBA I work with wanted to take Ola’s scripts and modify it but ran into time constraints (he is the much stronger developer than I am).  So he asked me to hack at it.  AND HACK I DID!!!

It’s ugly, but it works.

It is a stored procedure that I put into our DBA_MaintenanceDB (the DBA’s playhouse DB on every (and I mean every) SQL server that we control).  Here it is in all of it’s imperfection:

USE [DBA_MaintenanceDB]
GO
CREATE PROCEDURE [dbo].[SP_DDDatabaseBackup]
@Print varchar(10) = 'PRINT', --PRINT (prints out the full backup script including the XP_CmdShell, good from running from CMD line) ,CMD (gives you just the SQL command that can be run from the query window),EXEC (Actually executes the script)
@Location varchar (3),-- = '', ' we have two physical units in our two locations.
@Databases nvarchar(max), --ALL_DATABASES,SYSTEM_DATABASES,USER_DATABASES,Individual Database names comma seperated
@Type varchar (50) = 'FULL',
@Stripes varchar(2) = 4, --Up to 8: this is similar to splitting your backups into several files.  It does move faster up to a point.
@Date varchar(50) = ''

AS

DECLARE @Host varchar (50)
DECLARE @User varchar (50)
DECLARE @Path varchar (50)
IF @Location = ''
	BEGIN
		SET @Host = ''
		SET @User = ''
		SET @Path = ''
	END
ELSE
	BEGIN
		SET @Host = ''
		SET @User = ''
		SET @Path = ''
	END

IF @Date = ''
BEGIN
SET @Date = CONVERT(VARCHAR(50), DATEADD(DD,15,GETDATE()),101)+' 00:00:00'--++CONVERT(VARCHAR(50), DATEADD(DD,14,GETDATE()),108)
END
DECLARE @BackupScript varchar(max)
DECLARE @Version numeric(18,10) = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
DECLARE @ServerName varchar (50)
DECLARE @Cluster nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @CurrentDBID int
DECLARE @CurrentDatabaseName nvarchar(max)
DECLARE @CurrentDatabaseNameFS nvarchar(max)

DECLARE @tmpDatabases TABLE (ID int,
                            DatabaseName nvarchar(max),
                            DatabaseNameFS nvarchar(max),
                            DatabaseType nvarchar(max),
                            Selected bit,
                            Completed bit,
                            PRIMARY KEY(Selected, Completed, ID))
DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
                            DatabaseType nvarchar(max),
                            Selected bit)

----  Collect Data
-- Get the cluster name --
IF @Version >= 11
	BEGIN
		SET @Cluster = (SELECT REPLACE(cluster_name,'\','$') FROM sys.dm_hadr_cluster)
	END

---- Select databases

SET @Databases = REPLACE(@Databases, ', ', ',');

WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
(
SELECT 1 AS StartPosition,
        ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
        SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem
WHERE @Databases IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
        ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
        SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem
FROM Databases1
WHERE EndPosition < LEN(@Databases) + 1
),
Databases2 (DatabaseItem, Selected) AS
(
SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
        CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Databases1
),
Databases3 (DatabaseItem, DatabaseType, Selected) AS
(
SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,
        CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,
        Selected
FROM Databases2
),
Databases4 (DatabaseName, DatabaseType, Selected) AS
(
SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
        DatabaseType,
        Selected
FROM Databases3
)
INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
SELECT DatabaseName,
        DatabaseType,
        Selected
FROM Databases4
OPTION (MAXRECURSION 0)

INSERT INTO @tmpDatabases (ID,DatabaseName, DatabaseNameFS, DatabaseType, Selected, Completed)
SELECT [database_id] AS ID,
		[name] AS DatabaseName,
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],'\',''),'/',''),':',''),'*',''),'?',''),'"',''),'<',''),'>',''),'|',''),' ','') AS DatabaseNameFS,
        CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
        0 AS Selected,
        0 AS Completed
FROM sys.databases [db]
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL
AND [state] = 0 AND [db].[is_in_standby] = 0
ORDER BY [name] ASC

UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
WHERE SelectedDatabases.Selected = 1

UPDATE tmpDatabases
SET tmpDatabases.Selected = SelectedDatabases.Selected
FROM @tmpDatabases tmpDatabases
INNER JOIN @SelectedDatabases SelectedDatabases
ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')
AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)
WHERE SelectedDatabases.Selected = 0

---- Check database names
SET @ErrorMessage = ''
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
FROM @tmpDatabases
WHERE Selected = 1
AND DatabaseNameFS = ''
ORDER BY DatabaseName ASC
IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMessage = 'The names of the following databases are not supported: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '
--RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
--SET @Error = @@ERROR
END

SET @ErrorMessage = ''
SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '
FROM @tmpDatabases
WHERE UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases GROUP BY UPPER(DatabaseNameFS) HAVING COUNT(*) > 1)
AND UPPER(DatabaseNameFS) IN(SELECT UPPER(DatabaseNameFS) FROM @tmpDatabases WHERE Selected = 1)
AND DatabaseNameFS <> ''
ORDER BY DatabaseName ASC
OPTION (RECOMPILE)
IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMessage = 'The names of the following databases are not unique in the file system: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '
--RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
--SET @Error = @@ERROR
END
--SELECT * FROM @tmpDatabases
--SELECT * FROM @SelectedDatabases
--SELECT @ErrorMessage

WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)
BEGIN
    SELECT TOP 1 @CurrentDBID = ID,
                 @CurrentDatabaseName = DatabaseName,
                 @CurrentDatabaseNameFS = DatabaseNameFS
    FROM @tmpDatabases
    WHERE Selected = 1
    AND Completed = 0
    ORDER BY ID ASC

	--Select @CurrentDBID, @CurrentDatabaseName, @CurrentDatabaseNameFS
	IF @Version >= 11
		BEGIN
			IF (SELECT sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)) = 1 AND (SELECT [database_name] FROM [sys].[availability_databases_cluster]) = @CurrentDatabaseName
			BEGIN
				SET @ServerName = @Cluster
				SET @BackupScript = (
				SELECT
					'EXEC xp_cmdshell ''ddbmsqlsv.exe '
				+ '-c '+ @ServerName + ' '
				+ '-l '+ @Type +' '
				+ '-S ' + @Stripes + ' '
				--+ '-u '
				+ '-N "'+ @ServerName + '_' + d.name + '_'+ @Type +'_Backup" '
				+ '-b "Daily ' + @Type + ' backups" '
				+ '-y "' + @date + '" '
				+ '-a "NSR_DFA_SI=TRUE" '
				+ '-a "NSR_DFA_SI_USE_DD=TRUE" '
				+ '-a "NSR_DFA_SI_DD_HOST=' + @host + '" '
				+ '-a "NSR_DFA_SI_DD_USER=' + @User + '" '
				+ '-a "NSR_DFA_SI_DEVICE_PATH=' + @Path + '"'
				+ ' "MSSQL:'+  [name] +'"'''
				FROM sys.databases d WHERE d.[database_id] = @CurrentDBID)
				IF @Print = 'EXEC'
					BEGIN
						EXECUTE (@BackupScript)
						PRINT @CurrentDatabaseName + ' has been backed up.'
					END
				ELSE
				IF @Print = 'CMD'
					BEGIN
						PRINT REPLACE(REPLACE(@BackupScript,'''',''),'EXEC xp_cmdshell ','')
					END
				ELSE
					BEGIN
						PRINT @BackupScript
					END
			END
		ELSE
		IF (SELECT sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName)) = 0 AND (SELECT [database_name] FROM [sys].[availability_databases_cluster]) = @CurrentDatabaseName

			BEGIN
				--SELECT sys.[fn_hadr_backup_is_preferred_replica]('IASPRODIMS_Reduced')
				PRINT @CurrentDatabaseName + ' has been skipped because it is not the Secondary server.'
			END
		ELSE
			BEGIN
				SET @ServerName = REPLACE(@@SERVERNAME,'\','$')
				SET @BackupScript = (
				SELECT
					'EXEC xp_cmdshell ''ddbmsqlsv.exe '
				+ '-c '+ @ServerName + ' '
				+ '-l '+ @Type +' '
				+ '-S ' + @Stripes + ' '
				--+ '-u '
				+ '-N "'+ @ServerName + '_' + d.name + '_'+ @Type +'_Backup" '
				+ '-b "Daily '+ @Type +' backups" '
				+ '-y "' + @date + '" '
				+ '-a "NSR_DFA_SI=TRUE" '
				+ '-a "NSR_DFA_SI_USE_DD=TRUE" '
				+ '-a "NSR_DFA_SI_DD_HOST=' + @host + '" '
				+ '-a "NSR_DFA_SI_DD_USER=' + @User + '" '
				+ '-a "NSR_DFA_SI_DEVICE_PATH=' + @Path + '"'
				+ ' "MSSQL:'+  [name] +'"'''
				FROM sys.databases d WHERE d.[database_id] = @CurrentDBID)
				IF @Print = 'EXEC'
					BEGIN
						EXECUTE (@BackupScript)
						PRINT @CurrentDatabaseName + ' has been backed up.'
					END
				ELSE
				IF @Print = 'CMD'
					BEGIN
						PRINT REPLACE(REPLACE(@BackupScript,'''',''),'EXEC xp_cmdshell ','')
					END
				ELSE
					BEGIN
						PRINT @BackupScript
					END
			END
		END
	ELSE
		BEGIN
			SET @ServerName = REPLACE(@@SERVERNAME,'\','$')
			SET @BackupScript = (
			SELECT
				'EXEC xp_cmdshell ''ddbmsqlsv.exe '
			+ '-c ' + @ServerName + ' '
			+ '-l ' + @Type + ' '
			+ '-S ' + @Stripes + ' '
			--+ '-u '
			+ '-N "'+ @ServerName + '_' + d.name + '_'+ @Type +'_Backup" '
			+ '-b "Daily '+ @Type +' backups" '
			+ '-y "' + @date + '" '
			+ '-a "NSR_DFA_SI=TRUE" '
			+ '-a "NSR_DFA_SI_USE_DD=TRUE" '
			+ '-a "NSR_DFA_SI_DD_HOST=' + @host + '" '
			+ '-a "NSR_DFA_SI_DD_USER=' + @User + '" '
			+ '-a "NSR_DFA_SI_DEVICE_PATH=' + @Path + '"'
			+ ' "MSSQL:'+  [name] +'"'''
			FROM sys.databases d WHERE d.[database_id] = @CurrentDBID)
			IF @Print = 'EXEC'
					BEGIN
						EXECUTE (@BackupScript)
						PRINT @CurrentDatabaseName + ' has been backed up.'
					END
				ELSE
				IF @Print = 'CMD'
					BEGIN
						PRINT REPLACE(REPLACE(@BackupScript,'''',''),'EXEC xp_cmdshell ','')
					END
				ELSE
					BEGIN
						PRINT @BackupScript
					END
		END

	UPDATE @tmpDatabases
    SET Completed = 1
    WHERE Selected = 1
    AND Completed = 0
    AND ID = @CurrentDBID
END

I hope one day Ola comes along sees this and somehow incorporates it fully into his scripts, because I tried and failed…  Maybe I will try later on in my career… 🙂

First interesting post I can think of: Nifty way of getting backup times.

Just recently we switched from our old fashioned, yet reliable MS SQL Server backups to EMCs DataDomain backups. In doing this our 2 TB backups went from being 4-8 hours to being greater than 13 hours (had to kill them twice when they were greater than 19 hours).  One of the things that we needed to do to show EMC the issue was to show the times before and after the switch took place.  Thank god for MSDB.  We built this script to find the data quickly and efficiently:

SELECT
	, 'FULL' AS [Type_Of_Backup]
	, CASE WHEN [SBS].[name] = 'DDBMMSSQL' THEN 'DataDomain' ELSE 'SQLBackups' END AS [Backup_Location]
	, [SBS].database_name
	, [SBS].[backup_start_date]
	, [SBS].[backup_finish_date]
	, RIGHT( '00' + CAST(DATEDIFF(HOUR,[SBS].[backup_start_date], [SBS].[backup_finish_date]) AS VARCHAR(3)), 2)
		+ ':' +
		CASE WHEN RIGHT( '00' + CAST(DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date])
						- (DATEDIFF(Hour,[SBS].[backup_start_date], [SBS].[backup_finish_date]) * 60) AS VARCHAR(3)), 2) < 0 THEN '00' ELSE
						RIGHT( '00' + CAST(DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date])
						- (DATEDIFF(Hour,[SBS].[backup_start_date], [SBS].[backup_finish_date]) * 60) AS VARCHAR(3)), 2) END
	AS RunTime
	, DATEDIFF(MINUTE,[SBS].[backup_start_date], [SBS].[backup_finish_date]) AS RunTimeMinutes
FROM msdb.dbo.backupset AS [SBS]
WHERE
	[SBS].database_name = '<;database to measure>;'
	AND [SBS].type='D' --This means the full backups.
	AND([SBS].user_name='<;Service Account>;'OR [SBS].user_name='<;SQL Agent Account>;')

Forgive my programming skills since I pride myself as being an accidental DBA and come from the hardware side and not the development side.  I am still learning and will always make room for improvement.