November 29, 2016 at 8:07 am
I am putting together a "recovery" plan if hardware fails, and since the business allows 120 minute recovery window,
I plan to do full and log backup restores of the production databases instead of using an HA technique. I know,
I can use log shipping, but cost of additional hardware prevents that, so here is my question.
The production databases are on version 2008 R2. The plan is to do restores from the 2008 R2 backups to version 2012
that is on a standby server. I will definitely not change the compatibility setting on the databases. Users will then
utilize those databases until the hardware is fixed. Can I perform backup of the databases on the standby server (version
2012 but compatibility set to 2008 R2) and restore that backup to the 2008 R2 database on the production (now fixed) server?
November 29, 2016 at 8:14 am
trhorner (11/29/2016)
I am putting together a "recovery" plan if hardware fails, and since the business allows 120 minute recovery window,I plan to do full and log backup restores of the production databases instead of using an HA technique. I know,
I can use log shipping, but cost of additional hardware prevents that, so here is my question.
The production databases are on version 2008 R2. The plan is to do restores from the 2008 R2 backups to version 2012
that is on a standby server. I will definitely not change the compatibility setting on the databases. Users will then
utilize those databases until the hardware is fixed. Can I perform backup of the databases on the standby server (version
2012 but compatibility set to 2008 R2) and restore that backup to the 2008 R2 database on the production (now fixed) server?
No, it's not possible. Once you go on higher version you cannot go back on lower.
The compatibility level is for the query optimizer how to treat the programmable objects plans.
Igor Micev,My blog: www.igormicev.com
November 29, 2016 at 8:15 am
The best way to be sure is to test it, but I think the answer is no - you can only restore in one direction, regardless of compatibility level.
John
November 29, 2016 at 8:19 am
No, not possible. A database attached to SQL 2012 is a SQL 2012 database no matter what the compat mode, and databases cannot be restored to earlier versions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2016 at 8:43 am
trhorner (11/29/2016)
I am putting together a "recovery" plan if hardware fails, and since the business allows 120 minute recovery window,I plan to do full and log backup restores of the production databases instead of using an HA technique. I know,
I can use log shipping, but cost of additional hardware prevents that, so here is my question.
The production databases are on version 2008 R2. The plan is to do restores from the 2008 R2 backups to version 2012
that is on a standby server. I will definitely not change the compatibility setting on the databases. Users will then
utilize those databases until the hardware is fixed. Can I perform backup of the databases on the standby server (version
2012 but compatibility set to 2008 R2) and restore that backup to the 2008 R2 database on the production (now fixed) server?
Why on earth would you ever want to do that? Why not create a virtual machine instance of the current production environment and if things do go bonkers, fire those up on the spare hardware?
😎
Further, if the system can work on 2012 then why not simply upgrade? The improvements from 2008R2 to current 2016 version are huge?
November 29, 2016 at 9:29 am
Upgrade the instance if possible. A everyone suggested you can upgrade the database and cannot downgrade it back. Compatibility mode does not have any effect in this scenario. The first restore upgrades the database.
But in your case you can consider installing a named instance of 2008 on the second server. Prior to 2012 I understand you do not require a separate licence for a standby. I am not an expert on licencing, so please do a bit of research if you take that route.
November 29, 2016 at 10:27 am
Well, looks like I put way too much "stock" into what compatibility mode does for you. My thanks for your answers. Looks like the named instance may be the way to go, since this standby server is not really a "spare" (used for other databases that will be on the SQL 2012 version). I will check into the licensing issues also. If I had more hardware this would be a lot easier!:-)
November 29, 2016 at 1:16 pm
trhorner (11/29/2016)
Well, looks like I put way too much "stock" into what compatibility mode does for you.
Compatibility mode is just that, for compatibility. The SQL 2012 server behaves like a 2008 R2 server for the purposes of some features that have changed across the versions (not new features)
Be careful installing 2008 R2 onto a server that already has 2012 on it, I've never known MS products to like having older versions installed on top of newer ones.
Long term plan, see if you can upgrade both to something modern, like SQL 2016. 2008 R2 is already out of support, 2012 will be out of support in July, and 2016 has some *nice* features in it (Query Store, Query Store, Query Store!!!)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2016 at 3:36 am
I think Gail has a point there.
I have had side by side many times but I can remember always it was the lower version first.
It would be good to test on your lab first ,or at least have a snapshot before you start.
Also please remember to reserve some memory for the second instance, so that in case you need to use it, the server should not be locked down by the first instance.
EDIT: And as I mentioned in the first reply, please convince your people for the upgrade as long term solution. Risks are low and benefits are high.
November 30, 2016 at 3:39 am
GilaMonster (11/29/2016)
Long term plan, see if you can upgrade both to something modern, like SQL 2016. 2008 R2 is already out of support, 2012 will be out of support in July, and 2016 has some *nice* features in it (Query Store, Query Store, Query Store!!!)
Agree with Gail, SQL Server 2016 is awesome!
If you choose for SQL Server 2016, don't forget to install it with the latest SP (currently SP1).
Igor Micev,My blog: www.igormicev.com
November 30, 2016 at 8:17 am
Again, thanks for the input. I guess I was "smoking the cheap stuff" when I came up with the original plan. I knew all along that upgrading is the way to go, but was hoping for a quick and dirty (and cheap) solution for an interim solution. I will see what I can do to get permission to do upgrade, and this idea's failure will help that discussion. Application folks and management are very "nervous" to do in-place upgrade, and new hardware is tough to get (which is the way you should do upgrade as Brett Ozar recommends). I think the cost of the licenses for the new versions (2014 or 2016) is probably the issue, as we do not have the license to do "free" upgrades, and we have 4 production servers on SQL 2008 R2. Each license is about $50K, a lot of money for the company I work with.
Back to the drawing board. No need to respond any more - I have what I need. Again, thanks!
November 30, 2016 at 3:31 pm
trhorner (11/30/2016)
Application folks and management are very "nervous" to do in-place upgrade
For good reason. Not something I would do with a prod database myself. Side-by-side (new instance as a named instance) can work if you can't get hardware and the OS will support the new version of SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply