INF: Additional Fallback Considerations

• Microsoft SQL Server version 6.5

SUMMARY

Fallback is a new feature of SQL Server 6.5 which enables machine fault tolerance for a user database by using a shared drive. For more information, see the following topic in Books Online:

   What's New in SQL Server 6.5\Part 3 What's New for Administrators\
   Fallback Support.

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:

   A. That the drive where you're installing SQL Server is not the shared

         drive
   

   B. That the Net-Libraries listed on the fallback server are the same as

         on the primary machine
   

   C. That the code page (character set) and sort order are the same as on

         the primary computer
   

   D. That the sp_configure option "user connections" are set the same as

         the primary computer
   

   E. 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:

   A. The passwords are null and need to be corrected by the user

   B. The default database can not be the same as one of the fallback

         databases
   

   SELECT "exec sp_addlogin " + name + ",NULL," + dbname + "," +

         language + "," + 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:

   A. Create the same local groups on the fallback server as the primary

         server using the User Manager for Domains application
   

   B. 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.

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