SQLTeam.com | Weblogs | Forums

Migration SQL Server 2005 from Physical Host to VM

sql2005

#1

Hello,

it's my first time here, i need your help if you can :slight_smile:

So we have:
B one physical server[/B] :
OS : Windows Server 2003
Active Directory, DNS, DHCP, File Server, Print Server and [B]SQL Server 2005[/B]

and we will install new (2) two server for [B]High Availability[/B] with VMware vSphere (ESXi) 6.0
and we will have (2) two Virtual Machine :
1st VM :
OS : Windows Server 2012 R2
Active Directory, DNS, DHCP

2nd VM :
OS : Windows Server 2008 R2 (becaure SQL Server 2005 does not work with 2012 R2 [URL="https://support.microsoft.com/en-us/kb/2681562"]link[/URL])
SQL Server 2005 Service Pack 3

My Question is : What is a safe method to Migrate Database's from Physical Host (Windows Server 2003) to 2nd VM ?
this is schema of my futur architectur : picture

[B]Best Regards[/B]


#2

I would just use backup/restore like with any other database migrations. You can use detach/attach method, however I prefer backup/restore as it means less downtime since you can prep a the database on the new server by using backup/restore with norecovery option.


#3

Thank you very mutch for your fast answer :slight_smile:

i think, i will backup/restore database's one bye one :slight_smile:

However, you think i will not have a probleme with SQL Server 2005 SP3 on VM ? it's so old, but we can not upgrade to newest version $$$

Best regards


#4

2005 Works fine on VMs.


#5

hi JeffModen,

can you advice me; how vmdk (disk) i must assign to this VM ?

1 partition système

1 partition per instance

1 partition for databases

1 partition for tempdb

1 partition for transaction log's 

and how mutch RAM and vCPU ?

Thank you per advance :smile:


#6

It's almost not possible for me to make any specific size/qty recommendations in that area because I don't know how the existing machine is used, what the load on it is, nor what the performance on it is.

In general, here's what we've done for a "standard" where I work for OLTP boxes that also do a fair bit of batch work (several million rows per batch).

C: Windows System only. Allow for future growth.

D: Swap File only. Whatever size you use, use it all.

E: Program Files. This includes SQL Server program files and the system databases except for TempDB. Of course, allow for growth because MSDB and other system databases (like reporting DBs for SSRS, etc) will grow

K: Archive drive. This is where we store any read only partitions of partitioned tables or any databases that are in the SIMPLE recovery model (except for the SCRATCH database). We also have separate filegroups here for large audit/history tables that aren't essential during a "get back in business" DR restore. Both MDF and LDF files might live here for the databases in the SIMPLE recovery model. I recommend this drive be twice the size of any current items that fit the description above.

L: This is where only LDF files are stored. I recommend this drive be at least twice as big as the total bytes used by all LDF files if you're doing point-in-time backups ever 15-30 minutes. If you only do log file backups once a day (REALLY, REALLY BAD IDEA) like a lot of people make the mistake of doing, then I recommend this drive be at least 3 times the size of the LDF files. Of course, you should add enough for at least a year or two of growth, as well.

M: This is where only MDF files (except for TempDB) are stored. I recommend this drive be at least twice as big as the total bytes used by all MDF files. Of course, you should add enough for at least a year or two of growth, as well.

S: This is where our "sandbox" database (we call it "Scratch") lives. Make this big enough to actually be useful.

R: This is the drive letter for backups (and restores, hence the letter "R"). I strongly recommend it NOT be on the same SAN as the MDF/LDF files for obvious reasons.

T: This is where TempDB lives. We have 100GB here so that we can catch any runaway queries that might occur but we have 8 - 2GB MDF files and one 2GB LDF file here. Despite the heavy lifting the batch side of the house does, it hasn't grown beyond that for at least 3 years now. The reason why we don't simply allocate it all is because we have it setup to alert us if any file growth occurs, which will likely only occur (because of the long history of size here) if something is in the process of going very wrong.

W;, X:, Y:, These are used for connections to various other systems for things like where document images and other "community" stores may live.

Z: This always points to the user's private drive area on the "community" drive system.

As for memory, the more the better, period. Our Dev boxes have 64GB and our Prod OLTP/BATCH boxes have 256GB. Special purpose prod boxes typically have less and vary based on what they are used for.

As for CPUs, it's similar to memory. The more the better but you do have to take into account licensing of things like CPU. Our Prod OLTP/BATCH boxes have up to 16CPUs and as little as 2 depending on what the box is used for.

Except for some of the smaller special purpose systems, we usually setup our VMs to use rather dedicated hardware as if they were standalone boxes. Of course, the SAN is the big share here but we don't allow for dynamic resizing of the drives.

And I have to make a disclaimer here. Although I'm the one that specified the configuration for each "box", I'm not the one that setup the VMs for them. I'm definitely NOT a Windows or VM expert. I did do the configuration/setup of each SQL Server instance, though.