Backup solution for 2005 upgrade

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Backup solution for 2005 upgrade

Author

Topic

shiloh
Starting Member

48 Posts

Posted - 2007-09-20 : 14:16:21

Hi all

We have a huge terabyte database that we are planning to upgrade to 2005. We are working on realistic backup solutions in case we have to rollback the upgrade. We have over 400 tables out of which about 100 are kind of static. The other 300 tables have very high transaction rate - thousands of batch transactions per sec. Earlier, prior to 2005 SP2 release we tried setting up replication to a 2000 box and replication agents failed to catch up with the transactions. Not sure of SP2 has any improvements. We are thinking of setting up replication for only the 300 or so tables and we will take a regular nightly back up and can use a day's old data for the 100 small tables.

What are your experiences/suggestions on a proper back up solution to have a 2000 server in almost stand by mode that we can switch back to in case of any issues?

Thanks,
don.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-09-20 : 14:36:19

try doing log shipping with a delay of about 60 minutes and logs ,not sure how frequently you back them up.

Regards
Paresh Motiwala
Boston, USA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 14:44:37

quote:
Originally posted by pareshmotiwala

try doing log shipping with a delay of about 60 minutes and logs ,not sure how frequently you back them up.

You can't logship from 2005 to 2000, so this will not help if they need to rollback.

shiloh,

You can not restore a 2005 backup to a 2000 server, so you will not be able to utilize your backups if you need to rollback to 2000.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shiloh
Starting Member

48 Posts

Posted - 2007-09-20 : 15:06:48

quote:
Originally posted by tkizer

shiloh,

You can not restore a 2005 backup to a 2000 server, so you will not be able to utilize your backups if you need to rollback to 2000.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Thats right.. I forgot about that.. We are more worried about a proper solution for the hight transaction tables.. for the smaller ones we can do a quick DTS and get all the info.

Any ideas on that..

thanks once again..
don

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 15:11:23

I don't have any suggestions on how to rollback a database of that size.

I'd recommend a very extensive QA test to ensure that your code works with 2005. Have a team on-hand during the upgrade that can fix any problems that may come up so that you do not need to rollback.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shiloh
Starting Member

48 Posts

Posted - 2007-09-20 : 15:16:17

hi Tara

I am not worried about code breaks since we have already identified almost all places where syntax changes have to be made. And even if we do see any issues we can fix them. Those are the easy ones. The big one is the performance issue. During out load tests we saw about 10% higher CPU. We do have enough room on the new server to accomodate that but our management (and ofcourse me too) feels more comfortable to have a backup plan. I dont know what to put in the roll back plan document on my plate..

don

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 15:37:07

For your load tests, did you set the database compatibility leve to 90, reindex the whole database, and update statistics after you upgraded to 2005?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shiloh
Starting Member

48 Posts

Posted - 2007-09-20 : 15:41:03

quote:
Originally posted by tkizer

For your load tests, did you set the database compatibility leve to 90, reindex the whole database, and update statistics after you upgraded to 2005?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Oh yes.. we followed all the recommended practices..and we noticed some queries have been better too.. we used the INCLUDE option for some indexes.

Its just the fear of the unknown .. if you know what I mean..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-20 : 15:44:22

What I would do is let them know that the rollback is not going to be easy or fast. Give them options such as bcp/DTS/SSIS/T-SQL commands/replication. Let them know that none of these are ideal on the size of database that you have.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

shiloh
Starting Member

48 Posts

Posted - 2007-09-20 : 16:19:35

hmm.. we have tables with over 500 mill rows and BCP/SSIS/DTS'ing them hasnt worked earlier.
Nevertheless.. there dont seem to be any realistic options for large databases..

Thanks Tara for your input on this..

Kristen
Test

22859 Posts

Posted - 2007-09-20 : 19:46:09

Just thinking out loud here ...

Could you build the SQL 2005 solution to "export" changed data to a parallel SQL 2000 database?

So you migrate from SQL 2000 to SQL 2005. The new SQL2k5 solution "duplicates" changes onto a SQL2k database, and you use that if you need to roll back.

You would need some sort of "Update Date" column on all the tables to be able to work out what had changed (or a ROWVERSION datatype column), but you would then be able to do something like:

DELETE SQL2k.dbo.MyTable
WHERE MyPK NOT IN (SELECT MyPK FROM SQL2k5.dbo.MyTable)

UPDATE U
SET MyCol1 = MyCol2, ...
FROM SQL2k.dbo.MyTable AS U
JOIN SQL2k5.dbo.MyTable AS S
ON S.MyPK = U.MyPK
WHERE S.UpdateDate > @LastBatchDateTime

-- Optional SET IDENTITY_INSERT SQL2k.dbo.MyTable ON
INSERT SQL2k.dbo.MyTable
SELECT *
FROM SQL2k5.dbo.MyTable
WHERE MyPK NOT IN (SELECT MyPK FROM SQL2k.dbo.MyTable)
AND S.UpdateDate > @LastBatchDateTime -- May help with performance
-- Optional SET IDENTITY_INSERT SQL2k.dbo.MyTable OFF

You could replace the UPDATE step by using the DELETE to also get rid of anything that has changed - the INSERT will then put it back again (Foreign Keys not withstanding!)

I appreciate that this is poor-mans-replication, but it could run as a batch operation, so could run at slack time [for the few days until you decide that you must roll back] and a final-run just before you roll back - which could be anything from minutes after you go live

to a few days later.

Rather than having to BCP/DTS all the trillions of rows across this would allow just the recently changed data to be "shipped"

The Delete process could be improved by triggers on the SQL2k5 tables storing the PKs and ChangeDate of deleted records in a separate table - that would faster delete of redundant data by not having to JOIN the whole table. The INSERT step would re-insert any that were deleted but recreated. I suppose performance might be improved by using a trigger to store the PKs /ChangeDate of Inserted rows too. Maybe even Updated rows

All this could be made to work on the existing SQL2000 database before migration - i.e. start of by using this to duplicate the SQL2000 database to a second, parallel, SQL2000 database

Its quite a lot of work ... but sounds like the downtime from a roll back would be a big issue for the users too, so the development cost might be justified?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources