Database mirroring between different versions of sql server instances

sql-server-2008-r2 sql-server-2014 database-mirroring alwayson
databash picture databash · Jun 22, 2015 · Viewed 7.3k times · Source

We had two SQL Server 2008R2 instances A and B. We used database mirroring for a 1TB database from A to B - created a database snapshot on B for that 1TB database (which took 5 seconds) - and used that database snapshot for reporting.

We upgraded instance B to SQL Server 2014.

Is there way to mirror a SQL Server 2008R2 database into SQL Server 2014 instance?

Microsoft prerequisite mentions that

"For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server".

Currently we have been doing backup-restore but it takes a 2 hours. We would like bring down the time to take the snapshot of the database from instance A to B.

Are there any other ways?

Answer

jmc picture jmc · Aug 4, 2015

There are provisions for mirroring across versions, but it is only supposed to be used as part of an upgrade. If the mirror ever failed over to the new instance, it wouldn't be able to fail back:

https://msdn.microsoft.com/en-us/library/bb677181(v=sql.120).aspx

Have you looked into transaction log replication?

Related questions

Partition Function COUNT() OVER possible using DISTINCT

I'm trying to write the following in order to get a running total of distinct NumUsers, like so: NumUsers = COUNT(DISTINCT [UserAccountKey]) OVER (PARTITION BY [Mth]) Management studio doesn't seem too happy about this. The error disappears when I remove …

Read More
How to restore SQL Server 2014 backup in SQL Server 2008

Were there any changes in this area with SQL Server 2014? I’ve seen this post Is it possible to restore Sql Server 2008 backup in sql server 2005 and I know that this was not possible as a scenario for 2012 -> 2008 …

Read More
SQL Server xp_delete_file parameters

Who can explain list of xp_Delete_file parameters in sql server. I don't find msdn document for this stored procedure. I got script of xp_Delete_file from maintenance plan but didn't understand first parameter.

Read More