We're moving our non-trivial SQL Server database [65Gib] to a new server.
Added in the mix, we're moving to SQL Server 2008 R2 from SQL Server 2005, and ideally need to take some time doing ALTERs to move various tables into different filegroups.
The traditional knife over would be :
- Down OLD Server
- Copy off DB files (Just realized I'll have to find some other way of copying over user logins from masterdb)
- Copy them over GigE to new DB
- Import DBs into NEW Server and allow any upgrades to process.
- Complete ALTERs
- Enable NEW Server
I'd love to pre-load as much of the transfer as possible; is there a good [simple] way to copy over a live version of the database and continue updating the target server until all the prep work is done? It occured to me that I could use replication, but I wasn't aware if I could then promote the Replication Slave to Master...
Or am I making a mistake trying to switch servers and software versions at the same time?