SQL Server 2000 - transferring the database

[aldi]

Just a quickie, say you've got a database of about 12GB on a 2K/SQL2K server and need to move it off a slow-ass single PIII onto a decent server also running 2K/SQL2K - is there another way of doing it apart from just a backup/restore and copying the huge-ass file?

[mattley]

Depends on the complexity of the overall solution, and the requirements of whatever application is actually using the database. I'd probably go for setting up the new server. Detatch the database on the old server, copy the files accross the network, to the new server and then attach them there.

Good luck.

[aldi]

mattley said:

Depends on the complexity of the overall solution, and the requirements of whatever application is actually using the database. I'd probably go for setting up the new server. Detatch the database on the old server, copy the files accross the network, to the new server and then attach them there. Good luck.

Cheers I think I'l need it... The app vendor's guy has been really struggling with it for a few days now, and says it might be a week but without going on a rant I think he knows a lot about databases and not a lot about more general networky windowsy stuff. I don't think the detach/reatach has been tried, just the backup & restore as he seemed to think the detach/reatach would result in a much bigger file to copy. Why that would be or why that's more of a problem I don't honestly know, but I'm not an SQL server expert.

[dilbert]

aldi said:

mattley said: Depends on the complexity of the overall solution, and the requirements of whatever application is actually using the database. I'd probably go for setting up the new server. Detatch the database on the old server, copy the files accross the network, to the new server and then attach them there. Good luck. Cheers I think I'l need it... The app vendor's guy has been really struggling with it for a few days now, and says it might be a week but without going on a rant I think he knows a lot about databases and not a lot about more general networky windowsy stuff. I don't think the detach/reatach has been tried, just the backup & restore as he seemed to think the detach/reatach would result in a much bigger file to copy. Why that would be or why that's more of a problem I don't honestly know, but I'm not an SQL server expert.

I don't know anything about MS SQL Server, but I do understand how a database works. I'd not be surprised if the backup/restore mechanisim doesn't bother compacting, and recalculating the lookup data.

The biggest part of a database is often the lookup data that allows fast queries, rather than the actual content data. It's dead fast to access, even with huge recordsets, but a real pain to refactor. I'd guess that the attach/detatch does all of the refactoring and compaction, and consequently takes ages and/or occupies loads of space on disk.

[aldi]

dilbert said:

aldi said: mattley said: Depends on the complexity of the overall solution, and the requirements of whatever application is actually using the database. I'd probably go for setting up the new server. Detatch the database on the old server, copy the files accross the network, to the new server and then attach them there. Good luck. Cheers I think I'l need it... The app vendor's guy has been really struggling with it for a few days now, and says it might be a week but without going on a rant I think he knows a lot about databases and not a lot about more general networky windowsy stuff. I don't think the detach/reatach has been tried, just the backup & restore as he seemed to think the detach/reatach would result in a much bigger file to copy. Why that would be or why that's more of a problem I don't honestly know, but I'm not an SQL server expert. I don't know anything about MS SQL Server, but I do understand how a database works. I'd not be surprised if the backup/restore mechanisim doesn't bother compacting, and recalculating the lookup data. The biggest part of a database is often the lookup data that allows fast queries, rather than the actual content data. It's dead fast to access, even with huge recordsets, but a real pain to refactor. I'd guess that the attach/detatch does all of the refactoring and compaction, and consequently takes ages and/or occupies loads of space on disk.

Bloody hell you were quick on the draw for 2:18am Anyway yes that sounds like a plausible explanation.
One other question I had was when we were running a bigish test transaction (4-5 minutes) on the new dual Xeon box, only one of the four virtual CPU's was showing load, is it normal for a single query to only occupy a single processor?

[ATG]

yes, that's fairly normal. There's a parameter somewhere that lets you lower the threshold at which SQL Server considers using multiple procs simultaneously for a single query, but in practice it almost always executes a single query on one proc. If you execute two queries simultaneously, then two procs should get used.

Can't understand why your man reckons backup/restore is quicker than de/re-attach ... in my experience that latter is quite a lot quicker. 12 GB is getting fairly hefty to copy btwn discs and across a LAN. If you create a backup, the server is going to have to scan 12GB of data and write 12GB of data ... and knowing SQL Server it probably does one of those twice for good measure. You then copy your 12GB backup file across the LAN, and SQL Server has to scan that file and write another 12 GB to disc during the restore. de/re-attach only required a single copying operation ... and it doesn't require your old knacker SQL Server to do any data processing at all.

p.s. as far as I know, dilbert is correct saying that backup doesn't restructure a SQL Server DB, but neither does a de/re-attach.

>> Edited by ATG on Sunday 20th November 11:56

[futie]

imho detach/attach will be quicker, backup/restore will take longer. But either way we've found that zipping or rar'ing the resultant file/s makes a fair old difference. A lot of padding in there which compresses very well. Also, if you're not bothered about transactions, detach and only compress/copy the mdf - sql will happily reattach without the ldf and depending on how you are configured and how the database was built, the ldf can be a gigantic waste of space.

[aldi]

ATG said:

yes, that's fairly normal. There's a parameter somewhere that lets you lower the threshold at which SQL Server considers using multiple procs simultaneously for a single query, but in practice it almost always executes a single query on one proc. If you execute two queries simultaneously, then two procs should get used.

Cool might go in for a bit of tweakage then once everything's settled down! I read somewhere else that SQL server sometimes performs worse with hyperthreading enabled than without so I'l play with that too.

ATG said:

Can't understand why your man reckons backup/restore is quicker than de/re-attach ... in my experience that latter is quite a lot quicker. 12 GB is getting fairly hefty to copy btwn discs and across a LAN. If you create a backup, the server is going to have to scan 12GB of data and write 12GB of data ... and knowing SQL Server it probably does one of those twice for good measure. You then copy your 12GB backup file across the LAN, and SQL Server has to scan that file and write another 12 GB to disc during the restore. de/re-attach only required a single copying operation ...

Yep I think it's just a case of that being the way its usually done, he's just doing it that way cause its the familiar path kind of thing.

ATG said:

and it doesn't require your old knacker SQL Server to do any data processing at all.

One thing I've learned is that single PIII's with 256MB RAM don't handle 12GB databases very well

[k77-widow maker]

When i've had to do this in the past...last week! all you need to do is run a backup, created the .bak file, then compress it in a rar file, with winrar. This will reduce the file size tremendously and make it more manageable. then transer it to the new server, unzip it as it were, and then create a new database on the new sql server, with exactly the same name as the previous on. once this is done, run the restore.. the bob's your uncle.

if you want more detailed help, feel free to drop me a line

[pdV6]

IME detach / zip / move / unzip / attach is the easiest and quickest way to go about this.

[aldi]

Thanks for the help all, the database is now moved. There's now a whole new ballpark of problems with regards to upgrading the structure of the thing but thats something best left to the vendors guy I think.