1

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:

  1. 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.
  2. Enable database mirroring or replication from the old server to the new one. Wait for the databases to copy over.
  3. With all users logged out, shut down old server and rename new server to old server's name.
  4. 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.

| improve this question | |
  • 1
    How big are the databases and what is your maximum downtime window? Log shipping I think it much easier to set up than mirroring, and more applicable than replication. But if your databases aren't large and you have tolerance for downtime (just like any other maintenance), maybe those aren't required, either. – Aaron Bertrand♦ Dec 7 '15 at 19:35
  • For simplicity's sake, I'd go with log shipping. It's easier to set up as you don't have to take care of the full backup to initialize replication, log shipping takes care of that for you all by itself. – Kris Gruttemeyer Dec 8 '15 at 2:35
  • Largest database is 12 gigs. I'll definitely look at the log shipping method. – Tom A Dec 8 '15 at 16:36
3

High level your approach will work but there is an additional step you will need to do along with a gotcha.

First off the gotcha - you will need to ensure that the instance names are identical on each server (although you can have a different server names). This isn't an issue though if you are just using default instances.

Once you cut over the databases you can then rename the new server to the old name. Once renamed you will need to rename the server name within SQL Server (again not to be confused with the instance name) using the steps outlined in this article.

https://msdn.microsoft.com/en-us/library/ms143799.aspx

I would either use log shipping or mirroring for the 5 databases and then manually copy across each instance level object (logins, jobs, linked servers, mail profiles, etc) and goes without saying that the applications should be tested before hand to make sure you are aware of all the instance level components and don't forget anything.

I have used approaches like this before to migrate clustered instances with numerous databases. This approach has more work up front getting everything set up and ready but it makes the cut over relatively quick and painless for the client applications.

| improve this answer | |
  • This is almost verbatim my approach when I do migrations/upgrades. This method is the cleanest for sure, I've used it more than a dozen times. Kudos for pointing out updating sys.servers! I forgot that part the first time I did it. Also,don't forget to check for orphaned users, that seriously happens every time (if you are using SQL auth logins in addition to domain logins/groups). :) – Kris Gruttemeyer Dec 8 '15 at 2:30
  • Thanks for the heads up on that gotcha. I'm not worried about the setup time as that can be done while users are in the system without any downtime on their part, then, like you said, we just minimize the downtime at final cutover. – Tom A Dec 8 '15 at 16:38
  • One way to eliminate orphaned users as mentioned by Kris Gruttemeyer is to use the sp_help_revlogin procedure described in method 3 of this article - support.microsoft.com/en-us/kb/918992 . This procedure makes it nice and easy to migrate sql logins ensuring that both the password and SID match on the new server. – Ian Chamberland Dec 8 '15 at 17:53
1

SQL server instances can't just be renamed. You'd have to remove the instance and then recreate it. If it were me, I would

  1. Setup the new server with the desired SQL Server version
  2. Take a full backup of every database on the "old" instance and restore it on the new instance, leaving it in RECOVERY mode.
  3. Migrate SQL Agent jobs
  4. Kick out all your users/applications with either Single User or Restricted modes and stop/disable all SQL Agent jobs
  5. Take a Transactional backup of every database on the "old" instance and restore it on the new instance with NORECOVERY mode
  6. Add instance Aliases on the new Instance for both 32 and 64 bit, using the old instance name, pointing to the new instance name.

This will minimize your downtime and make sure you get every transaction.

| improve this answer | |
  • He's talking about renaming the new server (as in the Windows name), not an instance. – Aaron Bertrand♦ Dec 7 '15 at 20:00
  • It was unclear to me and I assumed incorrectly. Even so, if not using a named instance, he'll still need to create the alias or update a cname if any applications are referencing the instance. – Steve Mangiameli Dec 7 '15 at 20:09
  • Great method, and I will keep this in mind for the future as I have other projects that will need this later. – Tom A Dec 8 '15 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.