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.
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.
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.