October | 2010 | Thoughts, codes, occasional rants, many others.

We recently moved data centers. Our earlier data center was not in a clustered environment, and although we did not have trusted domain, we utilized pass-through security in order to accomplish the log shipping. However, with our new data center, our SQL Servers are clustered on 2 virtualized nodes. Since clusters are not aware of local accounts, and we could not find any way we could accomplish this w/o trusted domains, we decided to build our own home grown log shipping process. I will describe this and provide some sample scripts. Please bear with me, as this is my first time blogging in a long time.

To start with, we wrote a stored proc which checks whether there is a net use connection established between 2 servers (shares), and run this stored proc every minute. Basic outline is as follows:

DECLARE @cmd1 nvarchar(500)
DECLARE @tbl TABLE (ret varchar(500))
SET @cmd1 = 'net use'
INSERT @tbl
EXEC xp_cmdshell @cmd1
DELETE @tbl
WHERE NOT ret LIKE '%\\[host-server-ip]\%' OR ret IS NULL
IF NOT EXISTS(select * from @tbl where ret like '%OK%')
BEGIN
SET @cmd1 = 'net use \\[host-server-ip] /u:username password /Persistent:yes'
EXEC xp_cmdshell @cmd1

Running this as a job every minute allowed us to ensure that the net use connection was successfully established. Please note that AFAIK, this has to be done from within the SQL Server. Doing this via MSDOS, or putting your password via keychain manager:

rundll32.exe keymgr.dll,KRShowKeyMgr

works good from within Windows, but SQL Server is not aware of what you establish outside the SQL Server environment.

Second step was to create a job which allows us to copy files from the Source to Destination. This was first step of the job:

1
2
3

DECLARE @cmd nvarchar(255)
SET @cmd = 'c:\RichCopy64.exe \\[source-server-created-above]\tLogBackups\logFolder "F:\logFolder\" /P /V /FIF "*.trn"'
EXEC xp_cmdshell @cmd

Another step was to create a table on the destination server:

CREATE TABLE [dbo].[dbName_logRestores](
	[logRestore_id] [INT] IDENTITY(1,1) NOT NULL,
	[database_name] [VARCHAR](50) NOT NULL,
	[last_copied] [VARCHAR](50) NOT NULL CONSTRAINT [DF_dbName_logsRestored_last_copied]  DEFAULT (''),
	[date_created] [datetime] NOT NULL CONSTRAINT [DF_dbName_logsRestored_date_copied]  DEFAULT (getdate()),
	[last_restored] [VARCHAR](50) NOT NULL CONSTRAINT [DF_dbName_logsRestored_last_loaded]  DEFAULT (''),
	[date_restored] [datetime] NOT NULL CONSTRAINT [DF_dbName_logsRestored_date_loaded]  DEFAULT ('1900-01-01')
) ON [PRIMARY]

Last step was to build a script which would go through the files in a given directory, put them in a table (sorted), and do restores one by one. Once your restores are caught up, you need to run this job as frequently as you do log backups.

--Gets the file from a given folder, and puts it in dbName_logRestores table
    SET nocount ON
 
--variables to hold the 'date', 'filename', 'directory cmd', 'path', 'delete command' and the date of the most 'recent backup - max date'
    DECLARE @date DATETIME ,
        @fileName NVARCHAR(75) ,
        @dir NVARCHAR(255) ,
        @loadPath NVARCHAR(500) ,
        @cmd1 NVARCHAR(330) ,
        @maxDate DATETIME ,
        @databaseName SYSNAME ,
        @id INT ,
        @dateRestored DATETIME ,
        @restorePath NVARCHAR(500) ,
        @moveLog VARCHAR(500) ,
        @undoFile VARCHAR(500) ,
        @lastRestored VARCHAR(50) ,
        @errMsg VARCHAR(500) ,
        @errSeverity INT ,
        @errState INT
-- set directory path ex. 'c:\mssql\backup\'
    SET @loadPath = '"F:\dbName"'
    SET @restorePath = 'F:\dbName'
    SET @moveLog = '''dbName_log'' to ''J:\MSSQL\Data\dbName_Log.ldf'''
    SET @undoFile = 'F:\undo_dbName.dat'
--create the dir command to retrieve directory contents
    SET @dir = 'dir ' + @loadPath
--Initialize restore data
    SET @dateRestored = '1900-01-01'
--create table to hold directory contents
    IF OBJECT_ID('tempdb.dbo.#tmpDir') IS NULL
        CREATE TABLE #tmpDir
            (
              id INT IDENTITY ,
              ret NVARCHAR(1000)
            )
    ELSE
        TRUNCATE TABLE #tmpDir
--insert directory contents into temp table using the xp_cmdshell procedure to execute the DOS 'dir' command
    INSERT  #tmpDir
            EXEC master.dbo.xp_cmdshell @dir
--remove unwanted directory content...need just backup files delete all the rest
    DELETE  #tmpDir
    WHERE   NOT ret LIKE '%.trn%'
            OR ret IS NULL
--loop through backup filenames and delete those older than 
    IF EXISTS ( SELECT  *
                FROM    #tmpDir AS tmp
                        LEFT OUTER JOIN DBADatabase.dbo.dbName_logRestores
                        AS lr ON LTRIM(RTRIM(RIGHT(tmp.ret,
                                                   CHARINDEX(' ',
                                                             REVERSE(tmp.ret))))) = last_copied
                WHERE   tmp.ret LIKE '%.trn%'
                        AND last_copied IS NULL )
        BEGIN
            INSERT  DBADatabase.dbo.dbName_logRestores
                    ( database_name ,
                      last_copied ,
                      date_created
                    )
                    SELECT  'dbName' AS database_name ,
                            LTRIM(RTRIM(RIGHT(tmp.ret,
                                              CHARINDEX(' ', REVERSE(tmp.ret))))) AS last_copied ,
                            CASE WHEN tmp.ret LIKE '% PM %'
                                 THEN LEFT(tmp.ret,
                                           CHARINDEX(' PM ', tmp.ret) + 3)
                                 WHEN tmp.ret LIKE '% AM %'
                                 THEN LEFT(tmp.ret,
                                           CHARINDEX(' AM ', tmp.ret) + 3)
                            END AS date_created
                    FROM    #tmpDir AS tmp
                            LEFT OUTER JOIN DBADatabase.dbo.dbName_logRestores
                            AS lr ON LTRIM(RTRIM(RIGHT(tmp.ret,
                                                       CHARINDEX(' ',
                                                              REVERSE(tmp.ret))))) = last_copied
                    WHERE   tmp.ret LIKE '%.trn%'
                            AND last_copied IS NULL
        END
 
--delete the msdb history leaving the most recent backup file in the history tables
--exec sp_delete_backuphistory convert(varchar(10), @maxDate, 101)
    WHILE EXISTS ( SELECT   *
                   FROM     DBADatabase.dbo.dbName_logRestores
                   WHERE    date_restored = '1900-01-01' )
        BEGIN
            SELECT TOP 1
                    @id = logRestore_id ,
                    @lastRestored = last_copied ,
                    @cmd1 = 'restore log ' + database_name + ' from disk = '''
                    + @restorePath + '\' + last_copied
                    + ''' with standby = ''' + @undoFile + ''', move '
                    + @moveLog
            FROM    DBADatabase.dbo.dbName_logRestores
            WHERE   date_restored = '1900-01-01'
            ORDER BY date_created
 
            BEGIN TRY
		--print @cmd1
                EXEC sp_executesql @cmd1
            END TRY
            BEGIN CATCH
                SELECT  @errMsg = ERROR_MESSAGE() ,
                        @errSeverity = ERROR_SEVERITY() ,
                        @errState = ERROR_STATE()
                RAISERROR(@errMsg, @errSeverity, @errState)
                GOTO cleanup
            END CATCH
            UPDATE  DBADatabase.dbo.dbName_logRestores
            SET     last_restored = @lastRestored ,
                    date_restored = GETDATE()
            WHERE   logRestore_id = @id
        END
    cleanup:
    SET nocount OFF

I hope that helps to understand log shipping process where you cannot establish a trusted domain or pass-through security. Please post comments if you have any questions, or can suggest a better way to do it. For one of my other databases, I had to use Idera SQLSafe to do log backup because I could not transfer huge files and restore them over the period of 15 minutes. I will post that in future blog.

Thoughts, codes, occasional rants, many others.

Data.

Monthly Archives: October 2010

Log Shipping w/o Domain Trust or Pass-Through Security