Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identify a checklist for database relocation Expand / Collapse
Author
Message
Posted Sunday, November 11, 2007 12:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:52 AM
Points: 30, Visits: 121

Hi,

Our databases are going to be relocated to a different facility in the coming month and being a Jr DBA, I am trying to come up with a checklist for the move. We use log shipping and do full and transaction log backups, so initially I plan to simply restore the backups (should I simply only do user databases or both user and system?) on to a new server at the new location for a 2 week testing period. Since the multiple user databases will be online after the restore for testing, after the two week testing period, I will need to do the process again but this time will need to make sure that all transactions are applied during the switch over to production. I have already looked at the topic for moving system databases but am trying to outline it fully for our scenario. We use full text search as well. Any help from someone with experience on the topic would be greatly appreciated.

Thanks!
MP
Post #420854
Posted Sunday, November 11, 2007 6:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 2:52 PM
Points: 1,326, Visits: 1,289
Hi,
See the following:
http://www.sqlservercentral.com/articles/Administering/movingsystemdatabasesachecklist/1608/
http://www.webpronews.com/topnews/2004/12/10/sql-server-hardware-performance-checklist
http://www.code-magazine.com/Article.aspx?quickid=070133
Regards,
Ahmed
Post #420875
Posted Monday, November 12, 2007 7:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:52 AM
Points: 30, Visits: 121
Thanks! Is there anything else that I should be aware of during the relocation?
MP
Post #421031
Posted Tuesday, November 13, 2007 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:28 AM
Points: 5,584, Visits: 6,380
MP,

You should be aware of everything.

Are your Servers names actually changing? What about share folder names? Instance names? DNS names / pointers? Permissions?

If you're going to be on different machines, quite likely a lot of that is changing. You need to make a list of every SSIS / DTS package, every job and every program (within your power) that points to old machine names and shares. Those will have to be updated during the move. Also, URL links that might change (SSRS, etc.).

You'll need to verify you can, at need, remote into your database servers (test Remote Desktop). That's assuming you're still administering the servers as usual, of course. Also, you'll want to make sure you get all your jobs/packages/FTP links moved to begin with. And File Shares. Don't forget those.

You'll want to make sure everyone's off the system during your move, then backup & detach your database so no one can log back in and change data while you're moving. When we did a side-by-side upgrade to new boxes, we simply copied the files and logs of all the user databases and re-attached them on the new server. Notice I said "Copy", not "Move". This is important, because if the re-attach fails and corrupts your files, you'll still want the originals around for a while. We kept ours for a couple of weeks, just to make sure.

Keep all backups for a couple of weeks after the move for "CYA" purposes. Additionally, the first 2-3 days, you might want to utilize differential backups to cover yourself. Make sure the old server, while offline, is still available to move back to for at least a week (maybe two). Again, this is CYA. Then go through the old server files (once the move is complete) and make sure you've gotten EVERYTHING off it, or at least archived to another location (tape/CD/etc.).

Check everything 3 times. Even if you've checked it before, check it again. Believe it or not, you will miss something no matter how hard you try. But the more comprehensive your original move sheet is, the less you will miss and the faster your move will go.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #421466
Posted Tuesday, November 13, 2007 8:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:52 AM
Points: 30, Visits: 121

Brandie,

Thanks again for the additional info. Will definately keep these things in mind. I have already scripted out all of the jobs and stored them away. The server and instance names are going to be staying the same. We do use full text catalog and I found an article that mentioned that they are automatically carried over with the backup so it looks like I will not need to do anything manually for those. We are not really using DTS packages, so that aspect looks like it is covered as well.

Thanks for the heads up regarding copying the database vs moving it. I plan on simply restoring a full back up, making sure that no one is chaning data and then doing a differential and applying that on top of the full to ensure that all transactions are captured. I have also scripted out the server logins and will simply run those out as well. I am not sure about the ftp links and file shares but will definately find out about it and ensure those get transfered over as well.

This should be a good learning experience. :)

Thanks again,
MP

Post #421631
Posted Tuesday, November 13, 2007 9:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:28 AM
Points: 5,584, Visits: 6,380
Anything that doesn't kill you is something else you can add to your resume. @=)

Good luck with your move.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #421660
Posted Tuesday, November 13, 2007 9:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 7,105, Visits: 15,455
Brandie Tarvin (11/13/2007)
Anything that doesn't kill you is something else you can add to your resume. @=)

Good luck with your move.


Anything that DOES just gets added to your tombstone:D


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #421662
Posted Tuesday, November 13, 2007 9:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 7:28 AM
Points: 5,584, Visits: 6,380
Matt Miller (11/13/2007)


Anything that DOES just gets added to your tombstone:D


First response: HA!!!!

Second response: Oh no! AD jokes on a SQL forum. Run away! Run away!

@=)


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #421663
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse