Mirroring from SQL 2005 to SQL2008 R2

Question

question

Håkan Winther asked Sep 30, '13

Mirroring from SQL 2005 to SQL2008 R2

Are there any problem to take a backup from SQL 2005, restore the backup on both the principal and the secondary server running SQL2008R2 and activate mirroring between them. It feels like there can be problem as the SQL2005 database will be upgraded when the database is taken online (WITH RECOVER), but the mirror is not taken online. Do you have to take a backup from SQL2005, restore it on the principal, take backups from the principal and restore it on the mirror? It seems to work for most of the database, but we have got one database with inconsistency error and we suspect the issue is caused by this version differences.

sql-server-2005 sql-server-2008-r2 mirroring

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Oct 04, 2013 at 10:19 AM

You guys know the drill, vote, mark, blah, blah, blah. Get it done.

0 ·

Håkan Winther commented · Oct 04, 2013 at 03:47 PM

It would have been nice to know if the corruption occured due to the different internal versions in the mirror or due to something else. It was awesome to see the page repair feature of the mirror in action.

0 ·

Answer 1 · 2013-09-30T13:16:44Z

Kev Riley answered Sep 30, '13

Interesting question. My understanding is that the version has to be the same, so given that the principal would be SQL2008R2, and the mirror still at SQL2005, then mirroring would fail to start. But I must admit, I'm tempted to test this, just have to try and dig out a copy of SQL2005 to install....

8

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Oct 01, 2013 at 02:38 PM

Just tested it out and I had it wrong. The update of the database version does not happen as part of the restore, it happens as part of recovery after the restore. Never second guess @Håkan Winther :)

2 ·

Håkan Winther commented · Oct 01, 2013 at 02:45 PM

I doubted my experience after your comment @KenJ, so I had to try to restore a database with recovery and no recovery to make sure, and I still doubted as no one was telling you otherwise. :)

1 ·

Håkan Winther commented · Sep 30, 2013 at 01:19 PM

The principal and the mirror are both SQL 2008R2, but the backup used to initialize the databases are from SQL server 2005.

0 ·

Kev Riley ♦♦ commented · Sep 30, 2013 at 01:32 PM

Sorry yes, a wording issue. The server version is 2008R2, but the database version (the internal version) on the mirror will still be 2005. And when I say database version, I mean as in : http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/28/database-version-vs-database-compatibility-level.aspx

0 ·

Håkan Winther commented · Sep 30, 2013 at 01:44 PM

You're right, as the database on the mirror is not "upgraded" in the SQL 2008R2 instance. Internally it's still sql 2005 on the mirror, but 2008 on the principal. Or will the changes in the principal be applied to the mirror?

0 ·

Show more comments

Answer 2 · 2013-09-30T14:45:12Z

Håkan Winther answered Sep 30, '13

I have removed the mirroring and recreated the mirroring from the SQL 2008R2 backup to be sure that the internal version doesn't cause any issues with corruption.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2013-10-17T07:04:05Z

ramesh 1 answered Oct 17, '13

To COnfigure mirroring it is adviced to have database with same edition

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question