November 10, 2008 at 3:12 pm
I'm trying to mirror from a 2005 to 2008 sql server.
It sets the mirror up fine. I can change records on the source, break mirroring and recover on the mirror server and changes are reflected.
but won't let me take a snapshot. Is this just because of the version difference? I thought when you did the restore on the mirror to get the mirroring set up it will convert it to the SQL2008 version, is that not correct?
It fails with the following error...
Msg 946, Level 14, State 1, Line 1
Cannot open database 'HOMER_SNAP' version 611. Upgrade the database to the latest version.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
The Redneck DBA
November 10, 2008 at 3:55 pm
Jason,
This isn't an answer but just an observation based on some playing around that I have done over the last couple of days. From what I have seen the database does not go through the version upgrade until after you complete the recovery process and since you have to restore with norecovery it doesn't actually complete that.
With that being said, I was wondering if you were to restore the backup to the mirror server with recovery, take a backup of the database now in 2008 version, and then restore the database again with the 2008 backup with norecovery then finally re-create the mirror, if that would work completely.
Again, not really an answer but I would be curious to hear if that would work and please feel free to let me know if I am way off base.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 11, 2008 at 2:18 am
Sorry, no answer here either, only want to comment on David's suggestion. David if you recover the beackup on 2008 so that the version upgrade succeeds and when take another backup which you want to use for mirroring, that won't work because mirroring would detect an invalid LSN because some transactions took place which are not on the source database. So that's no option.
[font="Verdana"]Markus Bohse[/font]
November 11, 2008 at 6:35 am
Another thought occured to me last night also...if you were to have a failure of the principal (2005)database and recovered the mirror (2008) database so people can use it, you would then be forced to upgrade the principal server to SQL 2008.
The Redneck DBA
December 3, 2008 at 11:47 am
SQL Server 2008 Books Online
Database Mirroring Overview
Database Mirroring Support
Database mirroring partners and witnesses are supported by SQL Server 2005 Standard SP1 and later versions, and by SQL Server 2005 Enterprise Edition SP1 and later versions. But the partners must use the same edition, and asynchronous database mirroring (high-performance mode) is supported only by SQL Server 2005 Enterprise Edition SP1 and later versions. Witnesses are also supported by SQL Server 2005 Workgroup SP1 and later versions and by SQL Server 2005 Express Edition SP1 and later versions.
Notice: partners must use the same edition!
I tried to mirror a SQL 2005 database to SQL 2008 without a witness. The wizard started ok, but halted when SQL 2005 could not talk on port 5022! Which is fixable, but I went with log shipping instead of mirror, no problems.
December 3, 2008 at 12:05 pm
I've run into that same edition requirement before. I don't mind them making you have the Enterprise edition for asynch. mirroring, but I found the same edition reqirement for synch. a bit annoying.
I did some good testing, and got 2005 to 2008 working fine as long as you don't do snapshots. After reading more in books online, this is by design so you can do rolling upgrades (upgrade mirror to 2008 first, then principal). I don't know if it's really supported to mirror that way for long periods of time, but it seemed to work fine in my testing.
The Redneck DBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply