Hot swap new database for old - Questions 
Author Message
 Hot swap new database for old - Questions

Folks -

*SQL 2000*

I support a very large DB (100GB) that is loaded each month from data
extracted from our legacy systems.  We're moving from SQL7 to SQL 2K on
a hefty box with enough storage to have multiple copies of the database
up at a given time.  The idea is to load a new copy of the databases,
then when everything is loaded and summarized, swap it with the previous
copy.  My users that attach via ODBC and SQL QA would have access to the
new data overnight, in theory, and would be able to access two distinct
months of data except when I am loading the new month.

I have looked through the newsgroup and can't see any reference to this
database usage.  I've got some ideas on how to do it, but would love to
hear some advice or comments on these approaches.

1)  Use sp_renamedb to rename the databases.  Sort of like this:
  exec sp_renamedb 'LastMonthDB' 'NextMonthDB'
    [ .. load process ... ]
  exec sp_renamedb 'ThisMonthDB' 'LastMonthDB'
  exec sp_renamedb 'NextMonthDB' 'ThisMonthDB'

2)  Use sp_attach_db to do the same thing:
  exec sp_detach_db 'LastMonthDB'
  exec sp_attach_db 'NextMonthDB' 'DatafileA.mdf' , 'LogfileA.ldf'
    [ .. load process ... ]
  exec sp_detach_db 'ThisMonthDB'
  exec sp_attach_db 'LastMonthDB' 'DatafileB.mdf' , 'LogfileB.ldf'
  exec sp_detach_db 'NextMonthDB'
  exec sp_attach_db 'ThisMonthDB' 'DatafileA.mdf' , 'LogfileA.ldf'

Renaming seems easier as you don't have to track which physical files
are being swapped month to month.

Question:  
A.  Any advantage of one of these over the other?  
B.  I have views in work databases that refer to the tables in my
current month's database.  Will these views be affected if I rename or
detach / reattach?
C.  Will jobs and scripts that refer to a database properly track the
correct instance of the database?  (ex:  BACKUP ThisMonthDB ... )

Any discussion of this would be appreciated.

Thanks in advance...

Rusty



Sun, 04 Jul 2004 00:57:13 GMT
 Hot swap new database for old - Questions

Rusty:

Either the rename or attach/detach will work. However, I would think that
the rename would be a bit easier and faster.

Your views should be fine, however when the database name changes, either
thru rename or attach, any job or script that runs
against the OLD name will fail, unless there is a database that uses that
name.

Hope this helps

Ed Harper
Microsoft SQL Support

========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
========================================================



Wed, 07 Jul 2004 01:53:42 GMT
 Hot swap new database for old - Questions
Yes Ed, it helped.  The RENAME method will be used.  I'll probably
rebuild all my views anyway as I precaution.

Thanks again.

Quote:

> Rusty:

> Either the rename or attach/detach will work. However, I would think that
> the rename would be a bit easier and faster.

> Your views should be fine, however when the database name changes, either
> thru rename or attach, any job or script that runs
> against the OLD name will fail, unless there is a database that uses that
> name.

> Hope this helps

> Ed Harper
> Microsoft SQL Support

> ========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure?  For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> ========================================================



Mon, 19 Jul 2004 01:09:22 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Sql7 server doesn't recognize new 36 gb hot swap disk

2. Transfer one older database backup from older SQL 7 server to a new one

3. Replication and Hot Swap Servers

4. SQL Hot Swap?

5. sql7 server does not recognize newly installed hot swap disk

6. 42 GB Raid 5 Server Hot Swap $4999

7. HOT HOT HOT HOT ORACLE POSITIONS !!!

8. Net.Search Extenders - Hot Swap of Indexes

9. Master, msdb and model (old version and new version) i need old version

10. HOT NEW TOOL -- Database Explorer

11. Trigger Question about the new and old

12. Trigger Question about the new and old


 
Powered by phpBB® Forum Software