1

We have a SQL 2014 instance that has some very large databases. We want to move the instance to another machine with SQL 2017. We do not want to wait for the MDF and LDF files to copy and be without an instance for several hours while this happens. What replication/clustering method should I use to create an exact copy over time so we can get to the point of just removing original instance machine and have almost no downtime. I thought about creating a cluster, but I think that would require a SAN.

1 Answer 1

2

I suspect that either database mirroring (which is deprecated) or log shipping would suit your purposes. You would turn off log shipping and issue a restore database example with recovery command.

5
  • Is creating a cluster not the way to go? I'm curious why?
    – Bill Greer
    Feb 22, 2018 at 21:05
  • 2
    clusters/mirrors are no good, they must be running the same SQL server version. Log shipping is the go-to method to accomplish this. Your downtime is limited to taking one last log backup, copying it over, and restoring it. < 5 min should be doable, and as you aren't changing the source database, so you can do several dry-runs to get the timing down. Feb 22, 2018 at 22:49
  • @StrayCatDBA SSMS is forcing me to use the NORECOVERY mode option because the versions of SQL are different. Does that change things considerably? I have learned how to do log shipping using STANDBY and then changing the standby to the primary. I'm not sure what using NORECOVERY changes for me.
    – Bill Greer
    Feb 25, 2018 at 22:28
  • I just read this...... While doing a RESTORE Operation if you restoring database files, always use NORECOVER option as that will keep the database in a state where more backup file is restored. This will also keep database offline also to prevent any changes, which can create integrity issues. Once all backup file is restored run RESTORE command with a RECOVERY option to get database online and operational. So I think I do the NORECOVERY, restore the last one last log backup and then put the database online. Correct?
    – Bill Greer
    Feb 25, 2018 at 22:33
  • 1
    correct. STANDBY is used when you want to do something like run reports off the partially restored database. Feb 26, 2018 at 15:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .