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
Comments
Join the conversation! Your thoughts help the community grow.