SQL 2008 R2 Best backup strategies for standalone

Category: sql server mirroring

Question

nostar38 on Fri, 11 Nov 2016 17:29:33


Hi,

We have an SQL 2008 r2 server that is mirrored. However, our application does not support the failover to the 2nd SQL server. We would like to configure a backup strategy that would allow for a quick restore with min of lost data. Example using snapshots every hour and full backups every 2nd day.

Thank you 

Replies

Hilary Cotter on Sat, 12 Nov 2016 01:45:07


Use differential backups or log shipping. Reindexing may cause havoc with this and you may struggle with large databases.

However, I am confused by your request - you are mirroring. With mirroring you can have client redirection using the alternate failover partner parameter in the connection string. So your mirror should be in close synchronization with your principal. If you are using synchronous replication you will not have any data loss. With asynchronous replication there is a potential to have data loss.

It seems like you want an extra layer of protection on top of your mirror so if something happens you can do a restore of the database, differential backups and logs to get the failover site up to sync quickly.

But if something like this happens - how are you going to point your clients to new destination site?

Or are you interested in getting the principal - the source database back up as quickly as possible.  A full backup, possibly a differential backup and log restores would be a good fix here.

nostar38 on Tue, 15 Nov 2016 04:42:33


The application can't be failed over to the mirrored DB.

We would like to restore the server with the least amount of data lose. 

We were thinking it was possible using a snapshot every hour. Restore from the last full backup and use a snapshot. 

Lin Leng on Thu, 17 Nov 2016 11:51:54


Hi nostar38,

>> The application can't be failed over to the mirrored DB.

Just wondering, does that mean that your application doesn’t recognize failover partner? If so, is that possible to add a try-catch block around the database connection(in your code) to make the application retries using the mirror server? 

And if the application cannot be altered, in your case is that possible to make the redirection at DNS server? If so, I would say you could use a SQL Agent job periodically checks current mirroring role(with the code below) and executes a PowerShell job step that alters DNS record.
DECLARE @MirroringRole int;
SET @MirroringRole = (SELECT mirroring_role
    FROM sys.database_mirroring
    WHERE DB_NAME(database_id) = N'MirroringTest');
	SELECT @MirroringRole;

If you have any other questions, please let me know.

Regards,
Lin

nostar38 on Thu, 24 Nov 2016 19:27:48


Hi Lin,

Yep, the application isn't sql mirroring aware. The team wants to remove the mirroring and keep the DB as a standalone server. They want to setup snapshots hourly to ensure min amount of data loss. I'm not very comfortable with this.