Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.
Course Of The Month
3 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
ways to rename/replace tables with minimal disruption
Posted on 2014-02-20
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?)
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?)
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3 Comments
Active 1 day ago
You don't do a server restart. That is the most disruptive way. If you want you can create a second table form the original one and then simply rename it when the usage is less possible, during the night maybe:
Lets say your table name is my_table. You will create a new one called my_table_new and then:
Lets say your table name is my_table. You will create a new one called my_table_new and then:
EXEC sp_rename my_table, my_table_old
GO
EXEC sp_rename my_table_new, my_table
GO
I think you need to consider how the code will be updated to point to the new table before you worry about the renaming itself. Your strategy for that will probably reveal the most useful way to handle the table changes. In a previous project we did it using a synonym so the code was abstracted from the underlying table and we could manipulate it with impunity, once the code rollout was complete and the number of hits on the 'old' table reduced to near zero we retired the synonym and left the new table in place
http://technet.microsoft.com/en-US/library/ms191230(v=sql.90).aspx
http://technet.microsoft.com/en-US/library/ms191230(v=sql.90).aspx
Active today
You shouldn't need to restart the SQL instance.
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 13 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.