I am looking for suggestions. we have a fairly popular table that will normally carry up to 200000 sessions for most of the day. It has been marked as a '24/7 do not disrupt' critter.
However at the same time we need to rename it and replace it with a different one due to numerous preferred changes. The sessions by the way are persistent. What I am looking for are possible ways to accomplish the change.
(At this time my feeling is to hide it behind server maintenance by placing it in single user in front of the mandatory server restart,with rollback since they are only readers, doing the change and then multiuser>restart and done.
but my thought is perhaps there is a better way. S that is what I am looking for.
Requirements:
must be sql server 2005
the users are all using a vb application. It might be possible to point to a substitute dbname that points back to the tables replacing the tables (view?)
You should consider allowing existing code to continue to referenced the old name for a while; that helps to insure that existing code doesn't break.
To do that, do these steps back-to-back immediately, as part of the same batch/script:
EXEC sp_rename 'dbo.old_table_name', 'new_table_name'
CREATE SYNONYM dbo.old_table_name FOR dbo.new_table_name
After the synonym is created, SQL code using the old_table_name will still reference the new table name when it executes.
>> It might be possible to point to a substitute dbname that points back to the tables replacing the tables (view?) <<
I think the synonym gives you what you're referring to there, if I understand it correctly.
Edit: If the new table structure is not compatible with the old one, you can create a view with the old name instead of using a synonym.