December 11, 2015 at 5:57 am
Hi,
So, I am migrating an AlwaysOn available database to a new datacenter.
I am using SQL Server 2014 Enterprise on all nodes in both DCs.
I execute a BACKUP DATABASE as well as a BACKUP LOG in my first datacenter.
I restore the database in my second DC on an, already configured, AlwaysOn setup.
Now I want to use these same backups for initialising the DB on my secondary replica but I am encountering issues.
Is there any way of using the original database backup when initialising the database on the secondary replica? I have tried to restore the same database backup and log on the secondary replica (with NORECOVERY) but when running the 'Add Database To Availability Group' wizard, it complains about a lack of transaction log backups.
I have been following the steps from https://msdn.microsoft.com/en-us/library/ff878349(v=sql.120).aspx amongst others. This documentation seems to explicitly use a backups from the New Database on the Primary Replica (in my second DC) and does not talk about other backups (even if they are the backups that has been used to restore the DB in the first place)
Anyone has any ideas?
Regards,
Hanslindgren
December 11, 2015 at 7:47 am
In your second datacentre, after you've restored the db, take another backup then try to add the database to the AG.
(Although it sounds as though you specifically don't want to do that?)
December 12, 2015 at 2:42 am
Thanks!
Yes, I specifically would like to try to avoid that. My Full backup is around 1.5 TB and the drives of the new datacenter are slow 🙁
I was thinking, since I already have a full backup and tlog backups, shouldn't it be possible to use these? :/
Regards,
Hanslindgren
December 15, 2015 at 5:58 am
I think (and somebody will no doubt correct me if I'm wrong) that you need to take that full backup again the first time because until you do, that database will be operating in pseudo-simple mode (because msdb on the new instance isn't aware of its backup history)
http://www.sqlskills.com/blogs/paul/new-script-is-that-database-really-in-the-full-recovery-mode/ .
Worth checking anyway!
December 17, 2015 at 6:41 am
Hi, yes, I fear you are right 🙁 I haven't found anything that seems to indicate that I can reuse the original backups and Not take new ones for doing the AlwaysOn Secondary initialisation...
Thanks!
December 18, 2015 at 3:16 pm
When joining a Database to an AlwaysON Group it needs to be in (1) Full Recovery mode (2) have a Full Backup.
When you restore the database to SVR1 you need to do a full backup then you can grab the full backup and move it over to SVR2 and attach it (that is what the wizard does).
You cannot grab the backup from SQL 2005/2008 restore it to SVR1 and that same old DB restore it it to Svr2. SQL will see that same database as two different DBs.
Unfortunately you have the following (which will takea long time).
1. 2005/2008 DB gets a full backup. (1.5 TB time)
2. Copy/Move file over to new Server (1.5 TB time)
3. Restore/attach to 2012/2014 alwaysON (time)
4. Full Backup (1.5 TB time)
5. Use wizard to join to AlwaysON or copy DB to second SVR and use the JOIN only option (1.5 TB)
I know you are trying to go straight to both servers but it does not work that way.
December 18, 2015 at 3:21 pm
AlwaysON includes 2 or more servers. When attaching/restoring the DB, SQL adds some information that is unique. The old file does not have that new information. When you do a backup from Server1 then that backup has the update information required. The old (original backup from old Server does not).
December 19, 2015 at 2:50 pm
AlwaysOn Availability Groups is a technology based on databases mirroring. The following is appropriate for both. It helps if you are familiar with setting up log shipping.
First step is to set to full recovery model. (You have done this already.)
Second is to create a full backup. If you already create full backups at regular time, you can use that, but that recovery model has to be set to full before the full backup is made. (Again, you have done this already.)
Third is to copy your full backup to your second data center. This takes a lot of time. By the time your backup is done being copied, it is already out of date. (Don't wait for your copy to complete, but go directory to the next step.)
Four is to create a transaction log, and keep creating those. At least every 5, 10, 15, 30, or 60 minutes, whatever works best for you. This also prevents the transaction log file to fill up.
Five is restore the full backup with norecovery in the second data center. Again this takes a lot of time. Your restored database is now even more out of date. (Don't wait for your restore to complete, but go directory to the next step.)
Six is to automatically copy those transaction log backups to your second data center.
Size is to automatically restore those transaction log backups with norecovery in sequence. Start with the first one created, and so on, until you have catched up. (This is what is called Log Shipping.)
Seven is to stop restoring transaction log backups and start the mirroring. This will work when the database in the second data center is caught up enough. If SQL Server tells you that the database is not up to date, keep restoring transaction log files. You might want to suspend creating transaction log files if you are very close. SQL Server cannot send transactions over the line that are already backed up and truncated from the transaction log file. Best to this when the database is not to busy.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply