March 8, 2011 at 11:16 pm
Hi Everyone
Our company has finally decided that it is time for a upgrade of our SQL, so our IT department has bought new servers set them up and are waiting for the upgrade.
Let me explain our setup. We have a main db running SQL 2000 with a backup machine which is log shipped to also SQL 2000. Then we have new new servers that are running 2008.
Some things that restrict me are:
1. We cannot be down for more that 30 mins (bosses orders)
2. The new machine has to be tested first and that means paralleled data.
3. DB size is 400GB
4. I cannot install 2008 on the current main db because of space and hardware issues.
What would be the best way getting the data over to the new machine, and once that is done switching the 2008 instance to production.
Log shipping could be the answer but as far as I could gather you cannot have the new server up for testing etc.
Transactional replication is my second option, but would I be able to stop the subscription and just switch to production?
Any advice, comments, suggestions are welcome.
Thanks
March 9, 2011 at 8:23 am
You're in a great position for a side-by-side upgrade. It's the better approach for upgrading anyway. Install the 2008 server, restore the the 2000 databases to it and then do log backups and restores until you're ready to switch over. You should have a down time well below 30 minutes if you do it correctly. This approach will give you plenty of time to validate the system, ensure you've moved the security correctly, set up backups & maintenance, etc..
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2011 at 12:29 pm
Log shipping would be OK for transitioning, but isn't useful during a testing phase as you can't access the DB until you break the log shipping and upgrade it to the 2008 format.
If you don't have to keep the 2008 server current, I would just restore a current backup from production and run all your tests against that, if the 2008 has to be kept current I think you are stuck with transaction replication. (But then you can't really test any process on the 2008 box that process data if it was already processed on the 2000 box.)
March 10, 2011 at 2:24 am
Thanks for the replies.
As I also understood it log shipping from 2000 to 2008 can be done using no recovery, but the 2008 db would not be accessible.
What I have done is restore a backup to 2008, that will then be used for stress testing. Once everyone is happy I will restore a new backup and set up transactional replication to get 2008 up to date.
Bring all the applications down, finish the last replication. Then switch all the applications to the 2008 and start that up.
Would it be as simple as that? I am a bit concerned about the switch over. If i stop the replication would I need to do anything to the db for it to change to production?
March 10, 2011 at 4:48 am
Ask following questions to application team before migration.
1.Does application upgraded to use SQL Server 2008 version database?
2.If your application is not ready to use Database in compatibility 100 than what is the purpose of up gradation?
3.In which compatibility level you have planned to run your database on new SQL server 2008 Instance?
4.Once Database migrated from 2000 version to 2008 you cannot revert? Except Export/Import.
Find following answers:
1.How long time is taking to copy Database backup from your existing server to new server?
2.How you will migrate your master database configurations?
3.What about SQL Server Agent jobs?
Side by side Up-gradation & Migration (Final Migration)
1.Take the migration/downtime date & time from your manager.
2.Send email notification to concern persons like “Database is not available from xx:xx AM/PM to xx:xx AM/PM due to some maintenance”
3.Stop your regular backup plan before 10 hrs of actual migration.
4.Take the full backup and move it on new server.
5.Keep taking Tran. Log Backup (if your database not in Simple Recovery model)
6.Restore Backup on new server with NORECOVERY
7.At the time of actual downtime time. Change SQL Server TCP/IP Port so that users/application cannot connect Database. Stop SQL Server Agent Service.
(Or Stop connection to SQL Server by other option as per your suitability)
8.Take the differential backup
9.Copy diff backup to new server.
10.And now restore differential backup with RECOVERY on new server.
11.Change Database compatibility as per your requirement. Recommended latest.
12.Map database users to appropriate logins.
13. Check database Consistence (both Physical and Logical)
14.Configure old IP to new Server so that Application connection string not needs to change.
15.Configure [Page Verify] to CHECKSUM
16.Now your new SQL Server is ready to serve and ready to accept new connections within 30min.
Your complete process depends on how long time taken to copy backup from old to new server. Because your database size is around 400GB
Ram
MSSQL DBA
March 14, 2011 at 5:26 am
Thanks for the advice Ramdas. That would be the route that we will follow.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply