May 11, 2018 at 10:21 am
We have installed a SQL Server 2017 database on an EC2 Ubuntu instance. What will the most efficient DR/HR backup options be? Our SLA's don't require point in time recovery, so was thinking an scheduled EBS snapshot every 30 mins (within recovery limits) will be the best option. Will this be sufficient? What are the benefits of SQL native backup of database and logs compared to other options if point in time recovery is not required?
Post new topic
May 11, 2018 at 11:07 am
If you don't need point in time, then things are simpler. An EBS backup is a snapshot of the entire host + SQL Server. However, when restored, this means that the system is restored to that state, without connections. Meaning that any transactions in flight will need to rollback, just as if a client disconnected. It's possible you might have a large transaciton taking place, which would take time to roll back, but what i'd be more concerned about is internal consistency.
I don't know if the EBS backup is transactionally consistent, meaning that every block is correct and stable at that point in time. Without a guarantee here, you might end up with corrupt SQL Server files.
USing native backups means that you can restore to points in time (if you use logs) or that you ensure you have a consistent backup. This also means that you can restore elsewhere if need be. There are definitely cases of EC2 snapshots not restoring, so having a copy of your data elsewhere is recommended.
May 16, 2018 at 8:17 am
One option is to write the backups (full \ log) on a local EBS volume and subsequently push them to S3. It will make restoration slightly more complex than just spinning up a new instance with attached EBS volumes from snapshots but would give you more flexibility.
As I'm not running SQL on Linux but windows EC2 instances I'm not sure if litespeed is available, however they way I do it is write \ read the backups direct with S3, to enable this my EC2 Instance runs as an IAM role with the required permissions on the S3 bucket, the likes of backup and restores are then as simple as:
BACKUP
exec master.dbo.xp_backup_database
@database = 'MyDB',
@filename = ' MyServer/FULL/MyDB_FULL.BKP',
@backupname = 'MyDB full backup',
@desc = 'Backup of MyDB',
@encryptionkey = N'XXXXXXXXXXXXXXXX',
@cryptlevel = 8,
@init = 1,
@logging = 0,
@throttle = 95,
@compressionlevel = 1,
@CloudVendor = 'AmazonS3',
@CloudRegionName = 'us-east-1',
@CloudBucketName = 'MyBucket'
RESTOREexec master.dbo.xp_restore_database @database = N'MyDB' ,
@filename = N'MyServer/MyDB/FULL/MyDB_FULL.BKP',
@encryptionkey = N'XXXXXXXXXXXXXT',
@CloudVendor = 'AmazonS3',
@CloudRegionName = 'us-east-1',
@CloudBucketName = 'MyBucket',
@logging = 0,
@with = N'STATS = 10',
@with = N'MOVE ''MyDB_data1'' TO ''D:\DATA\MyDB_data1.mdf''',
@with = N'MOVE ''MyDB_data2'' TO ''D:\DATA\MyDB_data2.ndf''',
@with = N'MOVE ''MyDB_log1'' TO ''D:\LOG\MyDB_Log.ldf''',
@with = N'RECOVERY',
@with = N'REPLACE'
Go
MCITP SQL 2005, MCSA SQL 2012
May 17, 2018 at 2:43 am
Thanks for this!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply