August 17, 2006 at 9:22 am
I have a system which is currently heavily fragmented both at the disk and the SQL levels.
The system config was Server A publisher,Server B subsriber.
However replication blew up a while ago and requires an outage to restore.
To fix this 'online' will be a long and arduous process so we are considering tuning the system en-masse in one go.
Part of the SQL issue is that there are no clustered indexes but we use identity PKs which would be suitable for these.
In order to do this I would like to do the following:
Copy the data from server A to server B (currently redundant with the same spec as A)
Set B as the primary server and A as the subsriber with replication brought back online
Server B will have clustered indexes over the ID columns and the database files created with enough space specified.
This should solve the fragmentation issue.
What I would like to know is what do people think is the best way to move the data. The two machines are in the same rack with a 10/100mbs LAN.
So far I have experimented with a linked server (as proof of concept) and am wondering if there is a better method? (DTS import wizard?)
The largest table has 27 million rows.
Thanks in advance for any tips or experience.
Richard
August 18, 2006 at 10:39 am
I usually go for detach attach as one of the easiest ways, assuming I don't have lite speed to hand.
I think you'll find dts is the slowest method to move a database.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 21, 2006 at 6:32 am
My understanding was that that would maintain the SQL fragmentation. This data is very heavily fragmented and I am hoping the inserts will sort that by having clustered indexes and then builing the non-clustered afterwards.
There is a schema change (clustered indexes will be new to this db) between the two boxes.
Am I wrong about this?
Thanks for your help
August 21, 2006 at 9:18 am
Yes you're right but you're asking two questions really - I suspect using dts to recreate the tables will take longer then adding the indexes in place.
Having done this a fair number of times, I just switched the database to simple recovery mode and ran in my scripts to create the clustered indexes, in batches.
It was often consdered a method to bcp out and then bcp back in creating the clustered indexes but to be honest it doesn't really work any quicker. Make sure you have sufficient working space in your log and mdf files to avoid autgrow which will slow things down.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply