Why Schema transfer?

To make the SQL Server 2005 database available all the time to target users while data transfer is taking place. Most of the time data ware house information is built by extracting information from different data sources and is based on several business rules. Our target is to make the database available most of the time while data transfer is taking place.

Schema transfer Steps:

The database has to be in single user mode when data/schema transfer took place. This data transfer was scheduled during off times to avoid the peak traffic. The below process is followed to make sure database is in single user mode for least possible amount of time.

            1    The first step is transfer Old Schema to New Schema.

            2    Fill with data in New Schema

            3    Bring the Database (TestDatabase) to Single User mode.

            4    Transfer Active Schema to Old Schema

            5    Bring the New schema to Active schema.

            6    Bring database Back to Multiuser Mode

Moving Database to Single user mode:

----To set the user to single user and Kill existing connections 

Alter database TestDatabase

Set Single_User

with rollback immediate

----To set the user to restricted user (only allow ADMIN users)

Alter database TestDatabase

set Restricted_User

with rollback immediate

-- To remove the previous users in   cache.

DBCC FREESYSTEMCACHE ('TokenAndPermUserStore')

--Schema Transfer

Alter database TestDatabase

Set Multi-user

Advantages:

·         This will avoid the users to connect to the database while schema transfer takes place. The difference between Single_User mode and Restricted_User mode is that in restricted user mode, only members of the db_owner, fixed database role, dbcreator and sysadmin fixed server roles can connect to the database.

Risks/Known Issues:

·         Only Users having DBA access can issue any database commands when the Database is in Restricted_User mode. This is the one possible scenario which might cause failure as it block s the DATABASE to put back to Multi user mode.

·         Any maintenance activities running during the job execution leads to Job failure.

References:

http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3B935997