March 24, 2015 at 7:24 pm
I need to virtualize an aging SQL Server 2005 RTM physical box to VMware. One of the issues that I'm faced with is the amount of time I have during the maintenance window. Another issue is the size of the server.
An idea that we've been talking about is potentially decreasing the time that it takes to virtualize the server by detaching the largest (~500 GB) non-production database and copying it over to the new SAN before we even start the conversion process. We would then be virtualizing a much smaller SQL Server. Then we would bring up the new VM and reattach the database that we copied over.
My major concerns are as follows...
1) I just inherited this server and it has never even been patched. The hardware is ~10 years old.
2) The database that we would be copying over has 31 data files and 2 log files.
3) DBCC CHECKDB hasn't been run on the live database in years...although I've run it successfully on my recent restores.
4) I've read about many instances where people have had problems reattaching databases after being detached. I personally have not had any issues with this..but I've only ever tested with smaller databases with 1 or 2 data files and 1 log file.
5) What about reattaching the old physical database files to the new virtual server?
Oh...and did I mention that this is server critical to the operation of the entire business...
Any input would be greatly appreciated.
Thanks!
March 30, 2015 at 4:00 pm
Assuming you're not moving system databases (master, msdb, or model)...
Is the SQL Server patched? If so, can you
1) Install and patch SQL Server on the VM then take a snapshot the VM
2) Restore a backup of the physical instance to a dev/test environment
3) Produce scripts to backup (TSQL), stop SQL Server service (you may have to stop the SQL Server Agent service as well) (CMD prompt), copy data files over to VM (CMD prompt if on same network), reattach to VM (TSQL), then copy security permissions (TSQL)
4) Run scripts in order
5) Rollback to Snap of VM
6) If errors, repeat 3,4,5 until the scripts run without error and the instance is working on your VM
7) Repeat step 5 when ready to move production instance to VM
This will cut down your maintenance window as much as possible. What ever you can move prior to your window you should do. Make a backup first just in case. Copy the data files, do not delete them. If something does happen, you can simply start SQL Server service (and SQL Server Agent service if available) on the physical box and try again later. Note the start and stop time for each step you take (ex: backup of database took x mins, reattach took x mins, etc) so you know how long it should take. The better you test, the more you'll find and fix before hand and the better you'll feel going into the transfer.
Please note I'm not entirely sure if simply stopping the SQL Server service will work once you go attach to the VM. If not, then change step 3 from stop service to "detach user databases (TSQL)". You'll find this out in testing, however simply stopping the service gives you the fastest rollback plan in case if emergency.
If your SQL Server is not patched, you can still try the above with the patches applied only to your VM. The Databases will upgrade themselves to the proper patch level when attached. If you are using this method, be sure to test the applications used by the databases on the VM as well during your testing. This may also be a good time to upgrade from 2005 to a version compatible with the applications that use the databases.
BACKUP, BACKUP, BACKUP
COPY, NOT CUT AND PASTE THE DATA FILES
TEST, TEST, TEST
March 30, 2015 at 4:16 pm
You may also want to review here for the data files unless you have 31 cores on your VM: http://www.sqlservercentral.com/Forums/Topic458542-148-1.aspx
The OS can only read one data file per core. I generally have the number of physical cores per data file (so, 4 cores, 4 data files) once a database reaches a certain size.
Also, assuming space is not an issue, remove one of the .ldf files. Each database only reads / writes from only one .ldf file at a time no matter the number cores I believe.
March 30, 2015 at 5:06 pm
Cpt_Picard (3/30/2015)
The OS can only read one data file per core.
This is not true. Any thread on any core can issue an IO request to any file. Please DO NOT go the path of splitting a user database into a number of files = processor cores
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2015 at 9:14 am
GilaMonster (3/30/2015)
Cpt_Picard (3/30/2015)
The OS can only read one data file per core.This is not true. Any thread on any core can issue an IO request to any file. Please DO NOT go the path of splitting a user database into a number of files = processor cores
Thanks for the correction Gila. I'm guessing my bad memory got jumbled up from this article I read some time ago: https://technet.microsoft.com/en-us/library/ms175527(v=sql.90).aspx
Good for me to review this again so I do not give out (or use) bad information in the future. :blush:
I apologize for the confusion.
brickpack, please let us know how this went / is going for you so we know if your issue is taken care of.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply