-- Stored Procedure: dbo.DBAConfigSLSLogShipping
-- Bill WUnder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBAConfigSLSLogShipping]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DBAConfigSLSLogShipping]
GO




CREATE PROCEDURE [dbo].[DBAConfigSLSLogShipping] 
 @StandbyServer sysname -- network name of the target server.
 , @ConfigFilesPath varchar(255) = null -- UNC path to the scripts for the log shipping procedures
   -- if null skip script deployment
 , @LogShippingUser sysname = 'sa' -- sql server login only
 , @LogShippingShare varchar(255) = null -- location to stage backup files (dft is \\<StandbyServer>\SLS_Tlogs)
   -- use '\\dc-backupp101\nbu_images\restore\' for WSOD one offs
 , @DropExistingTables varchar(5) = 'false'
 
AS
/*******************************************************************************************************
* admin.dbo.DBAConfigSLSLogShipping 
* Creator:       Bill Wunder
* Date:          3-28-2003
*
* Project:       
* Project Mgr:   
* Dev Contact:   
*
* Description:   Configure SQL Server pair (primary and standby servers) for SQL LiteSpeed 
*                      based log shipping. 
* Notes: ONLY A domain account that is a sysadmin on both the Primary and Standby servers can 
*        execute this procedure because it relys on trusted osql calls.
*        
* Prerequisites for this SQL LiteSpeed Log Shipping:
*  A database named admin must exist on the primary and the standby server
* SQL Litespeed must be installed and working on the primary and the standby server
* The @logShippingUser must be an existing SQL login on the primary and the standby SQL Servers
*        note that this login will be made a member of sysadmin on both servers by this procedure   
*   The password for the login must be the same on both servers or the linked server must
*   be configured to map the password between servers (not reccommended)
* SQL Server Agent must be running on the primary server in the Log Shippping pair
* Application Jobs that must begin running on the standby server at fail over should already be 
*  copied to the standby server. They will be started when GetSLSLogPrimary is executed
*               with @RecoveryFalg = 1 
* The primary server SQL Agent service account must have sysadmin access to the standby server 
*  preferably use the same domain service logon account for both servers
* A network share, shared as \\<StandbyServerName>\SLS_Tlogs must exist where the full 
*  backup and all log backup files will be stored
* The primary server SQL Server service , SQL Agent service, and the command prompt proxy (if used) 
*   accounts must have change access to this share
*       The primary and standby SQL Server service, SQL Agent service, and command prompt proxy (if used) 
*  accounts must be able to access [HKLM].[Software].[DBAssociates]
* There must be a reliable network connection between the primary and the standby server
*       There shoud be no white space in the @ConfigFilesPath due to osql challenges
* These procedures must already exist on the primary server
*       This procedure expects to find the following scripts at the @ConfigFilesPath location. 
*  dbo.InitSLSLogShipping.PRC       
*  dbo.GetSLSLogPrimary.PRC       
*  dbo.ApplySLSLogSecondary.PRC
*  dbo.ExpungeUsers.PRC         
*  dbo.log_job_error.PRC
*       
* Usage:
      EXECUTE admin.dbo.DBAConfigSLSLogShipping 
   @StandByServer = 'ARACUS'
   , @ConfigFilesPath = '\\BILLsK\C$\log_shipping'
   , @logShippingUser = 'test'
   , @logShippingShare = '\\BILL2K\restore'
   , @DropExistingTables = 'true'
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
DECLARE 
 @CmdStr varchar(1024)
 , @BackupLocationPath varchar(255)
 , @rc int
 , @ec int
 , @SqlStr nvarchar(1024)
 , @StandByLinkedServer varchar(128)  -- WSOD conforming linked Server name
 , @ErrDesc varchar(8000)
 , @Err varchar(1024)
 , @PreviousLogShippingUser sysname
 , @PreviousLogShippingShare varchar(255)
 , @id int
---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON


if @ConfigFilesPath is not null
 begin
  if substring(reverse(@ConfigFilesPath),1,1) <> '\'
   set @ConfigFilesPath = @ConfigFilesPath + '\'

  print 'Validate configuration files file system location: ' + @ConfigFilesPath
  set @Cmdstr = 'dir ' + @ConfigFilesPath
  print 'Command line to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  select @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Unable to access configuration file location ' + @ConfigFilesPath + ' from ' + @@servername
    goto ErrorHandler
   end

 end

if @LogShippingShare is null
 set @LogShippingShare = '\\' + @StandbyServer + '\SLS_Tlogs'

---------------------------------------------
-- body of stored procedure
---------------------------------------------
print 'Checking user ' + suser_sname() + '''s authority to configure log shipping.'
if not(IS_SRVROLEMEMBER('sysadmin') = 1)
 begin
  set @Err = suser_sname() + ' is not a member of sysadmin fixed server role on ' + @@servername
  goto ErrorHandler
 end

if datalength(suser_sid()) = 16
 begin
  set @Err = suser_sname() + ' is not a Windows login on ' + @@servername + '. SQL logins not permitted here.'
  goto ErrorHandler
 end

if @StandByServer = @@servername 
 begin
  set @Err = 'Cannot ship logs back to ' + @StandbyServer + '. Specify a different server as the standby server.'
  goto ErrorHandler
 end

print 'Assure the log shipping user exists and is sysadmin on ' + @@servername + ' and ' + @StandbyServer
--  required for backup/restore API (virtual device)  
--  the service account will also need access to the    
--  SQL Litespeed registry and the file system locations 
if @LogShippingUser != 'sa'
 begin
  print 'make ' + @LogShippingUser + ' sysadmin on ' + @@servername
  exec @rc = sp_addsrvrolemember @logShippingUser, sysadmin
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add ' + @logShippingUser + ' to sysadmin role on ' + @@servername
    goto ErrorHandler
   end

  print 'make ' + @LogShippingUser + ' sysadmin on ' + @StandbyServer
  set @CmdStr = 'osql -E -S' + @StandbyServer + ' -n -dadmin -Q"exec sp_addsrvrolemember ' + 
    + @LogShippingUser + ', sysadmin" -b'
  print 'Osql command to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add ' + @logShippingUser + ' to sysadmin role on ' + @StandbyServer
    goto ErrorHandler
   end

 end -- LogShippingUser not sa


if @DropExistingTables = 'true'
 begin
  select 'Drop any existing log shipping control tables on ' + @@servername
  if object_id('admin.dbo.SLSLogShippingLog','U') is not null 
   begin
    print char(9) + 'admin.dbo.SLSLogShippingLog'
    drop table admin.dbo.SLSLogShippingLog
   end
  if object_id('admin.dbo.SLSLogShipping','U') is not null
   begin  
    print char(9) + 'admin.dbo.SLSLogShipping'
    drop table admin.dbo.SLSLogShipping
   end
  if object_id('admin.dbo.SLSLogShippingConfig','U') is not null 
   begin   
    print char(9) + 'admin.dbo.SLSLogShippingConfig'
    drop table admin.dbo.SLSLogShippingConfig
   end
 end

print 'Create log shipping control tables on ' + @@servername + ' (if not exists)'
if object_id('admin.dbo.SLSLogShippingConfig','U') is null 
 begin
  print char(9) + 'admin.dbo.SLSLogShippingConfig'
  create table admin.dbo.SLSLogShippingConfig
   (StandbyServer sysname not null
   , LogShippingUser sysname not null
   , LogShippingShare varchar(255) not null
   , ConfigFilesPath varchar(255) not null
   , ConfigDt datetime not null
     constraint dft_SLSLogShippingConfig__ConfigDt 
    default getdate()
   , ConfigUser sysname not null
     constraint dft_SLSLogShippingConfig__ConfigUser 
    default suser_sname()
   , constraint pk_SLSLogShippingConfig__StandbyServer
    primary key (StandbyServer))
  set @ec = @@error
  If @ec <> 0
   begin
    set @Err = 'create table admin.dbo.SLSLogShippingConfig failed on ' + @@servername
    goto ErrorHandler
   end
 end

if object_id('admin.dbo.SLSLogShipping','U') is null 
 begin
  print char(9) + 'admin.dbo.SLSLogShipping'
  create table admin.dbo.SLSLogShipping
   (DatabaseName sysname not null
   , StandbyServer sysname not null
   , StandbyLinkedServer sysname not null
   , PrimaryLinkedServer sysname not null
   , BackupLocationPath varchar(256) not null
   , JobName sysname not null
   , NotifyOnFail varchar(100) not null
   , MinuteInterval tinyint not null
   , HoursToKeep tinyint not null
   , MinutesAcceptableLatency tinyint not null
   , NotifyOnLatent varchar(100) not null
   , BackupThreads tinyint not null
   , BackupPriority tinyint not null
   , InitDt datetime not null
     constraint dft_SLSLogShipping__InitDt 
    default getdate()
   , InitUser sysname not null
     constraint dft_SLSLogShipping__InitUser 
    default suser_sname()
   , constraint pk_SLSLogShipping__DatabaseName
    primary key (DatabaseName)
   , constraint fk_SLSLogShipping__StandbyServer__to__SLSLogShippingConfig
     foreign key (StandbyServer)
    references SLSLogShippingConfig (StandbyServer))
  set @ec = @@error
  If @ec <> 0
   begin
    set @Err = 'create table admin.dbo.SLSLogShipping failed on ' + @@servername
    goto ErrorHandler
   end
 end

if object_id('admin.dbo.SLSLogShippingLog','U') is null 
 begin
  print char(9) + 'admin.dbo.SLSLogShippingLog'
  create table admin.dbo.SLSLogShippingLog
   (Id int identity(1,1) not null
   , DatabaseName sysname not null
   , BackupFileName varchar(256) not null
   , UndoFileName varchar(256) null
   , RecoveryFlag bit null -- if null then NORECOVERY (xp_restore_... default)
   , ShipCompleteDt datetime null -- if null the not restored
   , BackupUser sysname not null
     constraint dft_SLSLogShippingLog__BackupUser
    default suser_sname()
   , BackupDt datetime not null
     constraint dft_SLSLogShippingLog__BackupDt 
    default getdate()
   , constraint pkc_SLSLogShippingLog__Id
     primary key (Id)
   , constraint ukn_SLSLogShippingLog__DatabaseName__BackupFileName
    unique (DatabaseName, BackupFileName)
   , constraint fk_SLSLogShippingLog__DatabaseName__to__SLSLogShipping
     foreign key (DatabaseName)
    references SLSLogShipping (DatabaseName))
  set @ec = @@error
  If @ec <> 0
   begin
    set @Err = 'create table admin.dbo.SLSLogShippingLog failed on ' + @@servername
    goto ErrorHandler
   end
 end

-- sp_executesql to assure compile of DBASLSLogShippingConfig DML because this proc must support any schema changes
-- do this check before creating procs to hopefully avoid proc compile failures if schema changes
print 'Check for existing values in admin.dbo.SLSLogShippingConfig table on ' + @@servername
set @SQLStr = 'select @PreviousLogShippingUser = LogShippingUser
  , @PreviousLogShippingShare = LogShippingShare
  from admin.dbo.SLSLogShippingConfig
  where StandbyServer = @StandbyServer'
exec @rc = sp_executesql @SQLStr, 
 N'@PreviousLogShippingUser sysname OUTPUT, @PreviousLogShippingShare varchar(255) OUTPUT, @StandByServer sysname'
 , @PreviousLogShippingUser OUTPUT, @PreviousLogShippingShare OUTPUT, @StandbyServer
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'Specify @DropExistingTables = ''true'' to change existing schema.'
  goto ErrorHandler
 end

print 'Assure that anyone with access to admin db on ' + @@servername + ' can set ShipCompleteDt.'
/*
-- want to eliminate the use of this login
if suser_sid('NT AUTHORITY\ANONYMOUS LOGON') is null
 exec sp_grantlogin [NT AUTHORITY\ANONYMOUS LOGON]
if user_id('NT AUTHORITY\ANONYMOUS LOGON') is null
 exec sp_grantdbaccess [NT AUTHORITY\ANONYMOUS LOGON]
grant select on dbo.SLSLogShippingLog to [NT AUTHORITY\ANONYMOUS LOGON]
*/
grant update (ShipCompleteDt) on dbo.SLSLogShippingLog to public

-- either (re)create the proces from specified path else verify they all exist
if @ConfigFilesPath is not null
 begin
  print 'Creating procedures on ' + @@servername
  print 'Creating procedure to add a linked server on ' + @@servername
  set @CmdStr = 'osql -E -S' + @@servername + ' -n -dadmin -i"' 
    + @ConfigFilesPath + 'dbo.AddLinkedServer.PRC" -b'
  print 'Osql command to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add procedure admin.dbo.AddLinkedServer to ' + @@servername
    goto ErrorHandler
   end

  print 'Creating Log Shipping Initialization procedure on ' + @@servername
  set @CmdStr = 'osql -E -S' + @@servername + ' -n -dadmin -i"' 
    + @ConfigFilesPath + 'dbo.InitSLSLogShipping.PRC" -b'
  print 'Osql command to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add procedure admin.dbo.InitSLSLogShipping to ' + @@servername
    goto ErrorHandler
   end

  print 'Creating Log Shipping Backup Procedure on ' + @@servername
  set @CmdStr = 'osql -E -S' + @@servername + ' -n -dadmin -i"' 
    + @ConfigFilesPath + 'dbo.GetSLSLogPrimary.PRC" -b'
  print 'Osql command to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add procedure admin.dbo.GetSLSLogPrimary to ' + @@servername
    goto ErrorHandler
   end

  print 'Create error notification procedure on ' + @@servername
  set @CmdStr = 'osql -E -S' + @@servername + ' -n -dadmin -i"' 
    + @ConfigFilesPath + 'dbo.NotifySLSLogShippingFailure.PRC" -b'
  print 'Osql command to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add procedure admin.dbo.NotifySLSLogShippingFailure to ' + @@servername
    goto ErrorHandler
   end

  print 'Creating procedures on ' + @StandbyServer
  print 'Creating procedure to add a linked servers on ' + @StandbyServer
  set @CmdStr = 'osql -E -S' + @StandbyServer + ' -n -dadmin -i"' 
    + @ConfigFilesPath + 'dbo.AddLinkedServer.PRC" -b'
  print 'Osql command to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add procedure admin.dbo.AddLinkedServer to ' + @StandbyServer
    goto ErrorHandler
   end

  print 'Creating Log Shipping Restore Procedure on ' + @StandbyServer
  set @CmdStr = 'osql -E -S' + @StandbyServer + ' -n -dadmin -i"' 
    + @ConfigFilesPath + 'dbo.ApplySLSLogSecondary.PRC" -b'
  print 'Osql command to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add procedure admin.dbo.ApplySLSLogSecondary to ' + @StandbyServer 
    goto ErrorHandler
   end

  print 'Creating SPID killing procedure on ' + @StandbyServer
  set @CmdStr = 'osql -E -S' + @StandbyServer + ' -n -dadmin -i"' 
    + @ConfigFilesPath + 'dbo.ExpungeUsers.PRC" -b'
  print 'Osql command to execute : ' + @CmdStr
  exec @rc = master.dbo.xp_cmdshell @CmdStr
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Failed to add procedure admin.dbo.ExpungeUsers to ' + @StandbyServer
    goto ErrorHandler
   end
 end
else
 begin
  -- verify that log shippin procedures are in place
  print 'Checking for procedure to add a linked server on ' + @@servername
  if object_id('dbo.AddLinkedServer','P') is null
   begin
    set @Err = 'No configuration path provided and procedure admin.dbo.AddLinkedServer not found on ' + @@servername
    goto ErrorHandler
   end    

  print 'Checking for Log Shipping Initialization procedure on ' + @@servername
   if object_id('dbo.InitSLSLogShipping','P') is null
    begin
     set @Err = 'No configuration path provided and procedure admin.dbo.InitSLSLogShipping not found on ' + @@servername
     goto ErrorHandler
    end 

  print 'Checking for Log Shipping Backup Procedure on ' + @@servername
   if object_id('dbo.GetSLSLogPrimary','P') is null
    begin
     set @Err = 'No configuration path provided and procedure admin.dbo.GetSLSLogPrimary not found on ' + @@servername
     goto ErrorHandler
    end 

  print 'Create error notification procedure on ' + @@servername
   if object_id('dbo.NotifySLSLogShippingFailure','P') is null
    begin
     set @Err = 'No configuration path provided and procedure admin.dbo.NotifySLSLogShippingFailure not found on ' + @@servername
     goto ErrorHandler
    end 

  -- since AddLinkedServer is known to exist locally make a temporary linked server to check procs on standby
  exec admin.dbo.AddLinkedServer 'lnkCheckStandBy', @StandByServer

  print 'Checking for procedure to add a linked server on ' + @StandByServer
  exec sp_executesql N'select @id = id from openquery(lnkCheckStandBy, ''select object_id(''''admin.dbo.AddLinkedServer'''') id'')',
    N'@id int output', @id output
  if (@id) is null
   begin
     set @Err = 'No configuration path provided and procedure admin.dbo.AddLinkedServer not found on ' + @StandbyServer
     goto ErrorHandler
    end

  print 'Checking for Log Shipping Restore Procedure on ' + @StandbyServer
  exec sp_executesql N'select @id = id from openquery(lnkCheckStandBy, ''select object_id(''''admin.dbo.ApplySLSLogSecondary'''') id'')',
    N'@id int output', @id output
  if (@id) is null
   begin
    set @Err = 'No configuration path provided and procedure admin.dbo.ApplySLSLogSecondary not found on ' + @StandbyServer
    goto ErrorHandler
   end

  print 'Checking for SPID killing procedure on ' + @StandbyServer
  exec sp_executesql N'select @id = id from openquery(lnkCheckStandBy, ''select object_id(''''admin.dbo.ExpungeUsers'''') id'')',
    N'@id int output', @id output
  if (@id) is null
   begin
    set @Err = 'No configuration path provided and procedure admin.dbo.ExpungeUsers not found on ' + @StandbyServer
    goto ErrorHandler
   end

  exec master.dbo.sp_dropserver 'lnkCheckStandBy'
 end

print 'Verify log shipping file share from ' + @@servername
set @CmdStr = 'dir ' + @LogShippingShare
print 'Command line to execute : ' + @CmdStr
exec @rc = master.dbo.xp_cmdshell @CmdStr
set @ec = @@error
if @rc <> 0 or @ec <> 0
 begin
  set @Err = 'Share \\' + @StandbyServer + '\SLS_Tlogs not found. Create before proceeding.'
  goto ErrorHandler
 end

-- sp_executesql to assure compile of DBASLSLogShippingConfig DML because this proc must support any schema changes
print 'Maintain admin.dbo.SLSLogShippingConfig table on ' + @@servername
if @PreviousLogShippingUser is not null 
 begin
  print 'Updating admin.dbo.SLSLogShippingConfig on ' + @@servername + ' using statemnet:'
    print @SQLStr
  set @SQLStr = 'update admin.dbo.SLSLogShippingConfig
   set LogShippingUser = ''' + @LogShippingUser + '''
   , LogShippingShare = ''' + @LogShippingShare + '''
   , ConfigFilesPath = ''' + isnull(@ConfigFilesPath,'not used') + '''
   where StandbyServer = ''' + @StandbyServer + ''''
  exec @rc = sp_executesql @SQLStr 
  set @ec = @@error
  if @rc <> 0 or @ec <> 0
   begin
    set @Err = 'Update of admin.dbo.SLSLogShippingConfig failed.'
    goto ErrorHandler
   end

  print 'Log shipping had already been configured from ' + @@servername + ' to ' + @StandbyServer 
   + char(13) + char(10) + 'Existing configuration has been updated with files from '
   + char(13) + char(10) + char(9) + @ConfigFilesPath    

  if @PreviousLogShippingUser <> 'sa' and @PreviousLogShippingUser <> @LogShippingUser
   print 'You may want to remove sysadmin access for previous log shipping user "' 
    + @PreviousLogShippingUser + '" on ' + @@servername + ' and ' + @StandbyServer + '.'
  if @PreviousLogShippingShare is not null and @PreviousLogShippingShare <> @LogShippingShare
   print 'You may want to remove the previous log shipping share ' + @PreviousLogShippingShare 

  if @DropExistingTables = 'true'
   print 'Because option to drop existing log shipping tables was specified it is necessary ' 
    + ' to execute admin.dbo.InitSLSLogShipping before log shipping will resume.'
   
 end
else
 begin
  set @SQLStr = 'insert admin.dbo.SLSLogShippingConfig
   (StandbyServer
   , LogShippingUser
   , LogShippingShare
   , ConfigFilesPath)
  values (''' + @StandbyServer + '''
   , ''' + @LogShippingUser + '''
   , ''' + @LogShippingShare + '''
   , ''' + isnull(@ConfigFilesPath,'not used') + ''')'
  print 'Adding admin.dbo.SLSLogShippingConfig row on ' + @@servername + ' using statement: '
    print @SQLStr
  exec @rc = sp_executesql @SQLStr 
  set @ec = @@error
  if @ec <> 0 
   begin
    set @Err = 'Insert to table admin.dbo.SLSLogShippingConfig failed.'
    goto ErrorHandler
   end
 end

print 'Log shipping configuration complete.'
return

ErrorHandler:

 set @ErrDesc = 'admin.dbo.DBAConfigSLSLogShipping error:  
 Return Code ' + convert(varchar(10),isnull(@RC,'-0')) + ' 
 Error Code ' +  + convert(varchar(10),isnull(@EC,'-0')) + ' 
 occurred on server %s
  @StandbyServer = %s
  @LogShippingUser = %s
  @ConfigFilesPath = %s
  @DropExistingTables = %s

 ' + isnull(@err,'no additional text available') 
 raiserror (@ErrDesc,16,1,@@servername,@StandbyServer,@LogShippingUser,@ConfigFilesPath,@DropExistingTables)
 return -1





GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO