We have an old SQL Server 2008 server that needs to be replaced, and as the sole developer (and by extension DBA), I have been asked to figure out the best way to move the 5 databases to a new server. As someone more familiar with T-SQL than SSIS, I'm not sure the way to go, but have come up with a plan that seems to allow quick and mostly painless (for the users at least) migration to the new server. My high level plan so far is:
- Setup the new server (applications, patches, etc). We may stay with 2008, but I'm going to recommend 2012 or 2014 to bring us up to date.
- Enable database mirroring or replication from the old server to the new one. Wait for the databases to copy over.
- With all users logged out, shut down old server and rename new server to old server's name.
- Shut down replication, go live.
Yes, I realize there are a lot of steps in here, and plenty of caveats (users, security roles, etc), but if I'm thinking about this right, then most of this will be taken care of automatically. My question is this (two part): Is this a valid plan to get everything moved over? If not, what would be better? I'm trying to minimize downtime for the users and overtime for us.