Using updatable views when moving tables from one DB to another?

[JoshG]

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

[Erland Sommarskog]

JoshG (js*****@gmail.com) writes:

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.

Since you apparently are on SQL 2005, I have a better idea: synonyms.

CREATE SYNONYM LogTable FOR databaseB.dbo.LogTable

and you are done.

In database A, we'd rename LogTable to LogTable-Old, and create a view
called LogTable which points to DatabaseB.dbo.LogTable.

Using the transaction log would require that you have a log reader
like Lumigent's Log Explorer to read the log. But since would be a one-
off, it seems to me that you could use a trigger to pick up the changes
while the copy is running.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[JoshG]

Erland,

Thank you for this response. I appreciate it.

There is one complicating factor I neglected to mention.

In several tables, we have columns currently poorly named "ID_ref" or
"CustomerID_ref". In the new version of the database, we have simply
renamed these to ID or CustomerID.

Using a view, we can easily keep using the ID_ref but map it to the
new physical ID column in the new database.

From looking at synonyms so far, I don't think you can do this kind of
mapping. Do you know if that is the case for sure?

best regards,
Josh

On Dec 3, 5:52 pm, Erland Sommarskog <[e-mail]:

JoshG ([e-mail]) writes:

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.

Since you apparently are on SQL 2005, I have a better idea: synonyms.

CREATE SYNONYM LogTable FOR databaseB.dbo.LogTable

and you are done.

In database A, we'd rename LogTable to LogTable-Old, and create a view
called LogTable which points to DatabaseB.dbo.LogTable.

Using the transaction log would require that you have a log reader
like Lumigent's Log Explorer to read the log. But since would be a one-
off, it seems to me that you could use a trigger to pick up the changes
while the copy is running.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

JoshG (js*****@gmail.com) writes:

There is one complicating factor I neglected to mention.

In several tables, we have columns currently poorly named "ID_ref" or
"CustomerID_ref". In the new version of the database, we have simply
renamed these to ID or CustomerID.

Using a view, we can easily keep using the ID_ref but map it to the
new physical ID column in the new database.

From looking at synonyms so far, I don't think you can do this kind of
mapping. Do you know if that is the case for sure?

Right, if you are also changing column names or anything else, synonyms
are not for you.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx