Our site which is an online shopping site
is migrating the backend from sql server 2005 to 2012. It is using
about 10-11 databases and two of them are of 20-25 gb size.
We are planning to turn off the site for minimal hours so that customer
would be able to use it.
If We are taking the back up from old server X(2005) at midnight today and
restoring to new server Y(2012) by tomorrow morning 10 am, how can we capture and move the
records entered in between back up and restore to the new server database at Y.
provided we can switch off the site for one hour .ie. we will be turning off the site
when we are restoring the database.
OR what is ideal procedure to avoid loss of db entries in migration process
Presumably your databases are in FULL Recovery Model? (If not I would be worried about your Disaster Recovery plans! but you can set them to FULL Recover Model just for the duration of the upgrade).
Take a full backup on Source
Restore to Target using NORECOVERY option
You can do that some-reasonable-time before cutting over. Do NOT take any FULL backups after this point (if you do then repeat the restore)
When you are ready to cut-over:
Take a Transaction Backup on Source (you don't need this, it is just to keep the subsequent DIFF and TLog files as small as possible to reduce copying time)
Take a DIFF backup on Source, copy to Target server and restore (with NORECOVERY again)
Once the DIFF restore is completed (just in case it "takes a while") take a TLog backup and restore that (there might have been additional, scheduled, Tlog backups in the meantime - you need to restore all of them, since the DIFF backup was made, in chronological order. Keeping using NORECOVERY
OK, once all that is done you are ready to cut-over
Put up holding page / whatever on the website
Set the database to READ_ONLY to prevent any further changes. Use the ROLLBACK IMMEDIATE option to terminate any existing connections
Take a final TLog backup, copy to Target server and restore.
If you are happy that everything went well you can use the RECOVERY option (you do NOT have to do this as part of the final TLog restore, you can just use the RECOVERY option on its own (i.e. as part of the RESTORE command syntax) without specifying any actual file to restore.
The database will now upgrade from SQL2005 to SQL2012. This will take however long it takes
(Note that your database will be in READ_ONLY mode at this point, as per the Source Server's setting, so you will need to ALTER it back to READ_WRITE, MULTI_USER)
You also need to freshen up statistics on ALL indexes (because of the SQL Version upgrade) - queries will be dire until you have done this, but you might decide to go live whilst the stats are rebuilding, but it does depend on how long the process takes and how much downtime is acceptable.
We've done with with less than 5 minutes downtime when upgrading a server (without changing version) and have had a holding page that has not even lost the user's sessions - well ... those that didn't "click-off" during the wait!
There is plenty more you ought to do during an upgrade ... hopefully "midnight today" is not TODAY but some future date?? and you are going to do a Dry Run of the upgrade and then a full regression test of the application? otherwise I would recommend you brush-up your CV first ...
At the very least you need to have migrated all SQL Agent scheduled tasks and Logins beforehand. I personally would hand-optimise the files for each database, pre-extend the LOG files with minimum VLFs and so on ...
Looks like they are all covered in the linked thread, except for this one which is a good point - best to move to a 64 bit server by the time you get to SQL 2012 and beyond, I reckon
"SQL Server 2012 does not support AWE. That means if you still have a 32-bit operating system ... there is a limit of 4GB of RAM"
