December 12, 2014 at 9:34 am
Scenario:
26 databases, this works.
1 database, this intermittently works but I can get mirroring up without adjusting recovery mode.
1 database I cannot get mirroring up.
I am trying to get all databases up with a mirror from 2005 standard to 2008 R2 for an upgrade. However, in doing so, I am trying to not break log shipping. For log shipping I use a third party tool (RedGate) that when it backs up the transaction logs it truncates after the file is shipped to the DR server.
The transaction log shipping process is disabled, but I don't want to have to re-establish the log shipping if I don't need to - too much time. I have more ease to do this now, but was rushing to establish everything for this weekend so didn't want to break log shipping if not needed (hey, it worked on 27 databases, why not this one I figured I'd just check to see if anyone has seen this before, hence; here).
Backup code:
BACKUP DATABASE [DB] TO DISK = N'\\server\InboundBackups\DB.bak'
WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'DB-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [DB] TO DISK = N'\\server\InboundBackups\DBLog.bak'
WITH NO_TRUNCATE, COPY_ONLY, INIT, NAME = N'DB-Transaction Log Backup'
GO
Restore code:
RESTORE DATABASE [DB] FROM
DISK = N'M:\InboundBackups\DB.bak' WITH
FILE = 1, MOVE N'DB_LogicalName' TO N'F:\DataFiles\DB.mdf',
MOVE N'DB_Log_LogicalName' TO N'G:\LogFiles\DB_Log.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [DB] FROM
DISK = N'M:\InboundBackups\DBLog.bak' WITH
FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
Then, I just use the GUI in SSMS to establish the mirror configuration - start the mirror, and presto, I have not broken log shipping AND I have established a mirror!
Alas, there is one database that I just can't get to go. It is different from the others in 3 ways.
1) It is 300 GB, where the others are smaller. The largest other one is 200 GB in size, and that one is the intermittent working one. All others, no issues ever.
2) It has more transactions - timeline scenario ---- [[I start the tail log backup (there are then 0 pages difference from what is to be restored and the current log), the tail log takes 2 minutes to complete backup, the restore immediately initiated takes between 2 and 4 minutes to restore, the mirror takes a button push to attempt to start, but if another transaction log backup is made, the difference in number of pages is about 10K - as in 10K new log pages are there for mirroring to go through to bring online, just between backup of tail and restore of tail time.]]
3) It has a secondary database file (.ndf) file. The restore code is just a little different:
RESTORE DATABASE [DB] FROM
DISK = N'M:\InboundBackups\DB.bak' WITH
FILE = 1, MOVE N'DB_LogicalName' TO N'F:\DataFiles\DB.mdf',
MOVE N'DB_Archive_LogicalName' TO N'F:\DataFiles\DB_Archive.ndf',
MOVE N'DB_Log_LogicalName' TO N'G:\LogFiles\DB_Log.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [DB] FROM
DISK = N'M:\InboundBackups\DBLog.bak' WITH
FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
The problem is here that I can't get this one, bigger, more transaction heavy DB to mirror. 27 other databases I can get, but this 1 single DB is an issue.
The error for this 1 database is:
"The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been restored on the mirror database. (Microsoft SQL Server, Error 1478)"
The only thing I could attempt is breaking log shipping, which I would prefer to avoid. Or, kill maintenance for a night to slow all transactions to speed the backup process and minimize the pages pushed to the database in the tail log backup and restore intervals... but I was trying to rush before this weekend's maintenance so I could cut over this weekend (postponed for later due to this issue where the 1 database just didn't want to go quietly).
Am I missing something I could try that will not break log shipping?
Has anyone seen this issue previously with connecting a mirror?
December 30, 2014 at 2:36 am
Hi,
Firstly, please note
1.For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.
2.The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.
Secondly,you have to restore all log backups followed by full backup taken on principal should be restored on mirroring database in LSN sequence - If you miss any log backup before setup mirroring you will get this Error 1478.
Steps
1. Take full backup on principal
2. Take one Log backup on principal ( If you take more than on log backups on principal you should restore them on mirroring server)
3. Copy them across mirroring server
4. Restore first Full then log backup(s) in sequence with NORECOVER mode
5. Establish mirroring thru Wizard
Thirdly, refer to this article to set up mirroring and log shipping together http://technet.microsoft.com/en-us/library/ms187016.aspx
December 30, 2014 at 2:44 am
matt.newman (12/12/2014)
The problem is here that I can't get this one, bigger, more transaction heavy DB to mirror. 27 other databases I can get, but this 1 single DB is an issue.
The error for this 1 database is:
"The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been restored on the mirror database. (Microsoft SQL Server, Error 1478)"
The only thing I could attempt is breaking log shipping, which I would prefer to avoid. Or, kill maintenance for a night to slow all transactions to speed the backup process and minimize the pages pushed to the database in the tail log backup and restore intervals... but I was trying to rush before this weekend's maintenance so I could cut over this weekend (postponed for later due to this issue where the 1 database just didn't want to go quietly).
Am I missing something I could try that will not break log shipping?
Has anyone seen this issue previously with connecting a mirror?
Leave the LS jobs running, just ensure that the mirror is initialised from a full backup and all log backups to your chosen point in time, sounds like you've missed some.
994388426 (12/30/2014)
Hi,Firstly, please note
1.For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.
This is not completely true.
You can setup a mirror session between different versions of sql server, for example 2005 to 2008 R2, but the failover if it occurs is a one time switch.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 30, 2014 at 2:45 am
matt.newman (12/12/2014)
The only thing I could attempt is breaking log shipping, which I would prefer to avoid. Or, kill maintenance for a night to slow all transactions to speed the backup process and minimize the pages pushed to the database in the tail log backup and restore intervals... but I was trying to rush before this weekend's maintenance so I could cut over this weekend (postponed for later due to this issue where the 1 database just didn't want to go quietly).Am I missing something I could try that will not break log shipping?
It means that a log backup was taken on the principal after the full backup but that log backup wasn't restored to the secondary. You don't need to break the log shipping, though stopping the log backup job for part of the setup might be helpful.
Take a full backup, copy, restore with no recovery
Stop the log backup job job the principal (disable it)
Copy ALL log backups since the full backup was taken, restore all of them WITH norecovery
Create the mirroring session.
Re-enable the log backup job on the principal (make very sure you don't miss this step)
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
December 30, 2014 at 2:46 am
994388426 (12/30/2014)
1.For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.
Not entirely true. Mirroring up-version is supported solely for the purpose of a rolling upgrade, which is what the OP is doing.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply