INF: Additional Fallback Considerations

MORE INFORMATION

Here is a list of general steps to set up a fallback server before taking the steps listed in Books Online. Note that some of the steps are suggested to create a transparent change to the fallback server.

1. Acquire any specialized hardware as defined in Books Online
2. Install the same network protocols on the fallback server as are on the primary computer (as listed in Control Panel\Network).
3. Choose the same domain for the fallback server as for the primary computer and place the fallback server on the same physical LAN segment.
4. Install SQL Server on the fallback server, making sure of the following:
   
   
   1. That the drive where you're installing SQL Server is not the shared drive
   2. That the Net-Libraries listed on the fallback server are the same as on the primary machine
   3. That the code page (character set) and sort order are the same as on the primary computer
   4. That the sp_configure option "user connections" are set the same as the primary computer
   5. That licensing is set up with the same mode as the primary computer, and that the number of licenses for Per Server mode is the same as the primary machine. Note that this is legal since only one of the servers will use the licenses at a time.
5. Create SQL Server logons to match the primary computer. The following script will create a script of logons with the following exceptions:
   
   
   1. The passwords are null and need to be corrected by the user
   2. The default database can not be the same as one of the fallback databases
      

         SELECT "exec sp_addlogin " + name + ",NULL," + dbname + "," +
            isnull(language,'NULL') + "," + convert(char(6),suid) FROM syslogins
            WHERE name NOT IN ('sa','probe','repl_publisher','repl_subscriber')
       
      								

      
      In order to correct the default database, run the following script to generate a script. Run the result script after the fallback server has taken over the database:
      

         SELECT "exec sp_defaultdb " + name + ","+ dbname FROM syslogins WHERE
            name NOT IN ('sa','probe','repl_publisher','repl_subscriber')
       
      								

6. If the primary server is using Integrated or Mixed security, perform the following steps:
   
   
   1. Create the same local groups on the fallback server as the primary server using the User Manager for Domains application
   2. Create logon permissions on the fallback server with SQL Security Manager or use the following script to create a script of the logon permissions:
      

            CREATE TABLE #groups
            (accountname     char(255) not null,
             grouptype       char(20)  null,
             privilege       char(5)   not null,
             mappedloginname char(255) null,
             permissionpath  char(255) null)
            insert #groups exec xp_logininfo
            select "exec xp_grantlogin '" + rtrim(accountname) + "','" +
               rtrim(privilege) + "'" from #groups
         
      								

7. Test client connectivity by connecting directly to the fallback server.
8. If you use Remote Stored Procedure calls, then the remote servers and remote logons should also be set up on the fallback server.

Additionally, the following features of SQL Server may require additional work to handle fallback support:

1. Replication
2. Scheduled tasks
3. Alerts
4. SQLMail