asked
Major Hardware Upgrade - Need Experienced Advice
We are currently running SQL2005 x64 on an identical pair of servers running Server 2003 Ent R2 (we'll call them SQL1 and SQL2) in our production environment. All 8 of our OLTP databases are mirrored in high safety mode with witness. The servers each have 8 cores and 16GB RAM, but we have been experiencing some memory pressure and somewhat slow mirroring performance, and this is affecting query performance at peak times, so we need to take off the shackles so these DBs will perform better. We could also use the upgraded query optimizers in the newer SQL products. We are not really having many I/O problems, so we're leaving that alone (for now), but we have approval for funding to upgrade the servers to 16 cores and 256GB RAM. Needless to say, we are going to do it. The plan is to migrate to SQL2012 Ent, running on Server 2008 probably (although I could be talked into Server 2012 if there is a definite advantage). So anyway, fun is in store. So the question is, how do we keep production downtime to a minimum? The servers cannot be install-upgraded in place (neither OS or SQL), since we will be replacing their guts (MB, CPU, RAM) entirely and (presumably) installing the OS from scratch. This will obviously also mean that SQL Server will need to be installed fresh. We have extremely complex (PCI compliant) permissions on procedures, tables, and even columns throughout all the databases, so we need to migrate existing user accounts (including SIDs) to the new SQL servers so we don't have to re-create all those permissions manually. This is the process I'm envisioning: - Fail all DBs to SQL2. - Break mirrors. SQL2 now running exposed. - Export users, passwords and SIDs to a script for migration into SQL 2012 on SQL1. - Replace hardware on SQL1, install OS and SQL 2012. - Create users on SQL1 using script. - Create empty databases on SQL1. - Take entire system offline for maintenance. - Detach databases on SQL2. - Copy current DB and Log files from SQL2 to correct locations on SQL1. This is almost 300GB total and will take forever over SMB file copy. Ouch. - Attach DBs on SQL1. - Bring up SQL1 and bring system back online. - Upgrade SQL2 hardware, OS and SQL Server. - Re-create users on SQL2 using script. - Restablish mirrors using DB and tail log restores. - - Back to normal but with dramatically improved performance! The issues are how to keep those steps in the middle to a minimum - if we are offline for 6 hours a lot of clients will be very irritated, but if that is what it takes then so be it. Do I have the process right? What else should I be thinking of? What am I missing? Any thoughts, warnings, or alternate ideas would be much appreciated. Also, please let me know what you think about Server 2008 vs. Server 2012.
If any answer proves helpful, mark it by clicking on the thumbs up. If any answer provides a valid solution, mark that by clicking on the checkbox next to it. You can mark multiple answers as helpful and one as a solution.
A few questions: * How old are the existing servers? * Why are you looking to upgrade components rather than swap in new servers? * How much downtime would the business be comfortable with?
The servers are about 4 years old. We are upgrading MB/CPU/RAM only because the case for each server is a huge 5U with 26 physical (hot plug) disk drives, and replacing the case is just too expensive. Plus it's really not necessary as these cases are great, redundant power supplies and everything. The business would be most comfortable with NO downtime, of course, but a late night 1 or 2 hour block is probably acceptable.
You might consider doing the database migration using log shipping rather than copying the database files etc over. That would be a lot quicker (in terms of downtime), as the bulk of the data copy would be done during normal production time, just leaving you with the log tail backup/restore.
Do the logshipping right, you wouldn't break anything, and your downtime would be minimal -just as long as it takes to do the tail log backup and restore. I would be *very* wary about moving the hard disks physically between servers.
Your idea regarding log shipping to reduce downtime is an interesting thought ... but we've not ever used log shipping because we use IDENTITY columns for virtually every PK in the system and we can't have confusion in our PKs. So we're not really up to speed on making it work, even temporarily. I'm quite sure we'd break something, especially since it'll be 4:00 in the morning and we won't be thinking straight (you know how it is). I'm thinking that since the servers are 100% identical, and have the exact same RAID card, disk sizes and file locations, maybe we could just physically MOVE the hard disks from SQL2 to SQL1 once the upgrade on SQL1 was completed, and then just attach the files on the RAID arrays to SQL2012. That would only take about 30 minutes probably altogether. A lot less than copying the database files via SMB file copy. Any thoughts on that approach?
When you restore the databases from SQL Server 2005 to 2008/2012 they will be in compatibility mode so the new features won't be available. However the queries will still execute using the new engine, which is largely a good thing but I have been caught out by queries running considerably slower. It maybe possible to switch compatibility to the latest level but you need to check if you have any T-SQL that no longer works. Easier way I have found to do this is to script out the entire database and try and create a new blank database using the script, first by parsing then execution. Scan the code for any using statements particular those that impose use of statistics or indexes as it is likely they are not required in the new version.
17 People are following this question.
