I have an older SQL 2005 box on Windows Server 2003 (32-bit). I would like to upgrade to a new machine. What are the checklist items for recommendation on new hardware/configuration/data migration plan?
RAM, RAM, RAM, and More RAM. I have upgraded hundreds of databases from SQL 2000 to 2005 and 2008. Going from 32-bit to x64. After consulting with the application vendor or running upgrade adviser against the DB I have always had a successful migration. The main points to have to consider are connections to your databases and make sure they are all updated when you migrate to new hardware. When a machine name changes it can have a huge impact to your environment unless you are using aliases. You will need to check all your jobs, DTS, SSIS, etc and make sure they can all connect to the new server. Also make sure you migrate all the users. This is really an open ended question as this could be a very simple task or you could be looking at months worth of work depending on your environment. From a hardware perspective you just really need to make sure you have enough iron to handle your load and that it is configured to best practices. You want LOTS of RAM, plenty of CPU, and FAST disks. You also want enough disks to split logs, data, tempdb, and backups to separate disks with as many spindles as you need to handle your load. I literally could type for hours on this subject.
This all depends upon load of the current system and expected load/growth of the new system. If it is a one to one swap and no growth is anticipated, I would go for a server with similar spec to the old machine. RAM, CPU and Disks will all be better by default through hardware advancements since the last system was built, so it is a matter of taking your current baseline and seeing that it will be fulfilled. You have not mentioned what the current spec is, or what system you are looking at implementing. Are you wanting to upgrade the OS and SQL Server too, or just put a new engine inside the car?? If you are just replacing aging hardware and cannot upgrade the rest, you would be best off installing a fresh box and taking a backup of all user dbs and restoring them on the new box. Don't forget to take any jobs/permissions/ssis packages/reports/cubes with you if necessary. Do you have a dedicated hardware supplier? DELL, IBM, HP? Do you have a central OPS Team that is responsible for hardware orders? You could start by talking to them about what you are wanting to do and go from there. If you can, post what you have and what you want to upgrade in a little more detail and we will help where we can.
As both WilliamD and TRAD have said, this is a topic that could fill chapters, but let me add just a little to what they have already said (all of which I agree with, incidentally): Plan for the downtime. If you have a large dedicated maintenance window this may not affect the rest of the company very much, but if this is a critical production database then even short periods of downtime may have a very large impact on the company and may need extensive planning to mitigate. Similarly, be generous in estimating how long you be down. Maybe it is just me, but personally I very frequently underestimate how long a task will take. How significant this server is and how long it can be down may also affect your migration plan. If you can declare a stop time for working with the server then data migration may be (almost) as simple as making and restoring backups. If the server downtime needs to be minimized and new records will be added the entire time on the other hand, you may have to do something more elaborate.
One question that you will get asked, is *why* you would like to upgrade to a new machine? What are the issues facing you at the moment? Are they likely to increase, or is it a static problem? What will you gain? What's the benefit, financially? What's the cost, financially, direct & indirect? What are the risks of upgrading? What are the risks of not upgrading? Oh, and if you're supporting 3rd party applications, make sure that they support the version of SQL Server you propose. Bear in mind that you will have to justify your proposal / recommendation to the bean-counters!