|
|
Posted by JoshG on 12/03/07 19:25
Hello,
We are researching whether the following scenario would be possible:
In an upcoming application release, we have to move some tables (Log
tables, look up tables, and a couple of secure tables) from database A
to database B.
Rather than wait and do everything all at once, and have no roll-back
plan should it fail, we'd like to create database B now, and start
moving those tables one by one over to it.
To ensure compatibility with the existing code-base, we'd like to
determine whether we can use updatable views to allow the current code
to continue to run against the existing DB.
Essentially, we would do this:
Given a table named LogTable In database A, we'd copy all of
LogTable's data to database B. (We'd look at the transaction log to
copy any changes made on rows modified after copying started.)
Then, we'd turn off the site for a few moments, and:
In database A, we'd rename LogTable to LogTable-Old, and create a view
called LogTable which points to DatabaseB.dbo.LogTable.
When we turn the site back on, updates and selects to LogTable would
physically pull from database B from now on.
I have already verified that performing selects and updates against a
view that refers to another physical database actually does work in
SQL 2K5.
My question is are there any pitfalls or things we should be aware of
that anyone else has experienced trying to do something like this?
Does it sound feasible?
Thank you,
Josh
Navigation:
[Reply to this message]
|