Fastest way to move a database from 32bit to 64bit server

Question

Fastest way to move a database from 32bit to 64bit server

DKlein

SSCrazy

Points: 2351
More actions
December 17, 2010 at 2:46 pm

#232543

Hello all,
We are currently replicating a 150+ GB database from a 32bit sql server to a 64bit sql server. The 32 bit is AWE enabled and is using a little over 14 GB of memory. We are pulling the article from the subscriber(64bit). The whole process takes between 14-17 hours to reinitialize, which has made us very hesitant to do so. I would suggest backup\restore, but it needs to be as close to live as possible?
Thanks,
DK

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Per Microsoft, the backup structure between 32 and 64-bit is the same, so you can restore backups between the same version but different architectures at will.

CEWII

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

So you want move the data between servers without downtime? That's harder than a simple backup & restore.

While you can backup your database in 32bit and restore it in 64bit, you will have to have the server offline after the initial backup, or the data will keep changing. To get around that, and still use backup & restore, you could try setting up log backups and then performing a point in time recovery up to the nearest minute or two, but again, you'll have to take the 32bit system offline at some point.

Instead you might try setting up mirroring or replication to get the data moving between the two systems, and then cut over at one fell swoop. The downtime will be extremely minimal then.

"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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Well Grant can correct me if he feels it necessary, but there are very few cases where I would want to setup mirroring, I'm really hesitant about the two-phase commit that is incurred on every write. Except in cases of low latency I wouldn't even consider it..

My first notion was log-shipping with a 5 minute window between dumps. There would be some down time, but it could be limited to a VERY small window like 3-5 minutes.

This kind of goes along with your replication suggestion.

Grant, thoughts?

CEWII

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Elliott Whitlow (12/20/2010)
Well Grant can correct me if he feels it necessary, but there are very few cases where I would want to setup mirroring, I'm really hesitant about the two-phase commit that is incurred on every write. Except in cases of low latency I wouldn't even consider it..
My first notion was log-shipping with a 5 minute window between dumps. There would be some down time, but it could be limited to a VERY small window like 3-5 minutes.
This kind of goes along with your replication suggestion.
Grant, thoughts?
CEWII

Not many. I think It's down to a difference in philosophies versus any real disagreement in mechanisms. I was thinking about log shipping as a possibility too.

I'm not sure that, or mirroring, is what we're looking for here. I'd like to hear from the original poster to see if we're off track somewhere.

"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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

just my 2ct.

With regards to a log shipped db, connections will have to be modified at switch time !

With regards to a mirrored db solution, you prepare all connections to include the failover partner.

At switch time, the apps will reconnect automatically but connections will also get broken.

NO i expressed myself wrong. At reconnect time, the mirrored dbs will get conneted to the correct instance "automatically" via the failover partner def.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

ALZDBA (12/20/2010)
just my 2ct.
With regards to a log shipped db, connections will have to be modified at switch time !
With regards to a mirrored db solution, you prepare all connections to include the failover partner.
At switch time, the apps will reconnect automatically but connections will also get broken.
NO i expressed myself wrong. At reconnect time, the mirrored dbs will get conneted to the correct instance "automatically" via the failover partner def.

Exactly. No matter the approach, there's a moment when the connections go. There just isn't any getting around it.

"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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Grant Fritchey (12/20/2010)
ALZDBA (12/20/2010)
just my 2ct.
With regards to a log shipped db, connections will have to be modified at switch time !
With regards to a mirrored db solution, you prepare all connections to include the failover partner.
At switch time, the apps will reconnect automatically but connections will also get broken.
NO i expressed myself wrong. At reconnect time, the mirrored dbs will get conneted to the correct instance "automatically" via the failover partner def.
Exactly. No matter the approach, there's a moment when the connections go. There just isn't any getting around it.

I admit I like that feature of mirroring but latency and having to commit at both servers are a high hurdle for me, so I am agreeing with Grant, it is just down to styles and preference.

Either technology would work and probably work well, the OP will have to weigh the pros/cons..

CEWII

DKlein SSCrazy Points: 2351 More actions · Answer 8

Disclaimer preface: I am just starting at a new company so details are unraveling as I dig, but here is the situation as it stands now:

Thanks for the responses, yes I was looking for alternatives to the current setup(transactional Replication), but I am afraid it is the best plan for the requirements. Which are as follows 24/7, or as close to as possible, up time on the reporting server instance of the publisher database. We are using transactional replication to ship data from our live web/app database server (publisher ) to our reporting server (subscriber). Our reporting server answers calls not only for internal read queries (client services, developers) but from our live web/app database server, which generate ssrs reports from within the app. from the reporting server subscriber database.

The problem arises as the reinitialization of the transactional replication takes 14+ hours. I need to cut that time down, or reduce the need to reinitialize (schema changes, etc..). I was searching for alternative methods to achieve the same result.

Thanks,

DK

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

You can also get into more arcane, and expensive, approaches that will solve the problem. Are you on a SAN? Most SAN systems these days can do a transactionally aware snapshot, or even replication, at the data level, so you'll be moving the data within the disks, not within SQL Server. A friend who manages hundreds of tb systems uses this approach.

"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

DKlein SSCrazy Points: 2351 More actions · Answer 10

It is going from a raid 5 to a raid 5, from box to box. From 32bit (publisher) to 64bit (subscriber).

Thanks again,

DK

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

DKlein (12/20/2010)
It is going from a raid 5 to a raid 5, from box to box. From 32bit (publisher) to 64bit (subscriber).
Thanks again,
DK

Now would be a good time to get away from Raid 5 if you can.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

paul.knibbs SSCoach Points: 15320 More actions · Answer 12

Out of interest, how fast is the network connection between these two servers? You don't specify.

DKlein SSCrazy Points: 2351 More actions · Answer 13

Jeff, what would you suggest for a small company?

Paul, looks like a 1Gbps card on the Publisher, and 100Mbps card on the Subscriber, and it is a pull subscription.

Thanks to both of you for your feedback,

DK

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 14

Now would be a good time to get away from Raid 5 if you can.

Hi Jeff,

Is Raid 1 more preferred for database files over Raid 5.?

I know raid 5 writes are longer but the reads should be faster no.?

And it is better protected with raid 5 then with Raid 1?

What would you suggest for a setup with 2 databases

Production database :

5Gb,

Tempdb on C:\ Raid 1 15K/rpm

Logfile On D:\ Separate spindels on Raid 1 15Krpm

Datafile on H:\ San disk on Raid 5 15K/rpm, disks also contains 'Steady' files of all sorts, Applicationsoftware, documentations,..(kinda file archive while the space is there..

Testdatabase:

Identical datacopy of production on weekly base with backup/restore

Testdatabase Logfile & Datafile on H:\ San disks Raid 5 15K/rpm

I do not have any issues with slow running queries but database (application) is now reaching a point where it grows faster and faster

before it was 3Mb/day, now its already near 30Mb/day, but that was expected as we put more and more features into action while migration from our old environment.

At top speed we wil get at the point of putting approx. 80Mb/day of data into the database

So if you would suggest a better setup, i will be pleased to here about it, cause while the database is still small it will make it easier to move 😀

Wkr,

Eddy