setUsersToHotSite

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.setUsersToHotSite') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.setUsersToHotSite
GO

CREATE PROCEDURE setUsersToHotSite
     @dbName VARCHAR(30) = ' ',         -- database
     @target VARCHAR(30) = ' ',         -- target server 
     @targetPassword VARCHAR(30) = ' ', -- target sa password
     @debug VARCHAR(5) = 'false'        -- debug flag 
AS
DECLARE @CMD VARCHAR(255),      -- working var to build and pass strings
        @CMDstatus INT,         -- working var for return status of  an @CMD 
        @myName VARCHAR(30)     -- name of this function

IF @dbName IS NOT NULL
  BEGIN
    /*
    Procedure setUsersToHotSite is an example of the failover 
    process needed to switch the hot site to the primary role.
    In actuality, failover tends to be application specific, so
    test and modify as needed.
    */
    SET NOCOUNT ON
    SELECT @myName = 'admin.setUsersToHotSite'
 
    /*
    make sure the target database is read/write 
    has checkpoint on recovery enabled
    has select into/bulkcopy disabled
    has truncate log on checkpoint disabled  
    and has log space
    */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'read only',false" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'DUMP TRANSACTION ' + @dbName + 
                  ' with TRUNCATE_ONLY' + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'select into/bulkcopy',false" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'trunc. log on chkpt.',false" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + 'sp_dboption ' + @dbName + 
                  ",'no chkpt on recovery',false" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    /* 
    Turn off all tasks that init logSync or dbSync to this system 
    */
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + "sp_configure 'allow updates',1" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"reconfigure with override"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = "UPDATE msdb.dbo.systasks SET enabled = 0 " +
                  "WHERE name LIKE 'logSync%' " +
                  "OR name LIKE 'dbSync%'" 
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                   ' -dmaster -Q"' + @CMD + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"' + "sp_configure 'allow updates',0" + '"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
    SELECT @CMD = 'isql -Usa -P' + @targetPassword + ' -S' + @target + 
                  ' -dmaster -Q"reconfigure with override"'
    EXEC @CMDstatus = dispatch @CMD, @myName, @debug
    IF (@CMDstatus <> 0) return @CMDstatus
  END
GO