April 26, 2017 at 3:03 am
I am using SQL 2008 R2, I am seeking your advice to secure my database from any types if risks i.e. not related to security risks like hacking rather like server failures, etc. Though I do run full DB backup at close of business hours every day, this may not be enough in case of any disaster during day I am at a risk of losing data of that day. I also planned for database mirroring but mirroring process may cause delay in transactions posting.
Basically I need real time backup of database either full or transaction log, backup can be on external drives or cloud, I do not have a credit card to azure services.
Please advice.
Thanks
satish
April 26, 2017 at 3:15 am
Do you take log backups during the day and at what frequency are the log backups taken? How much data can you afford to lose in the event of corruption or server failure?
I would recommend taking a look at mirroring and there is really no need to worry about a delay in transaction processing. You can also take a look at replication although I would prefer to use mirroring.
Another option would be to put the database on a clustered server so in the event of your active server going down it will failover to the passive server.
Thanks
April 26, 2017 at 3:21 am
If you want to keep a point in time back up, then doing Full and transaction log back ups is probably going to be your best choice. Doing a full back up everyday is fine, but if you do that at 17:30 every day, and your server fails at 17:25, then you may well effectively lose an entire day's worth of data.
Firstly, it's best to ensure your backing up to a location that isn't your SQL server. If your back ups are on your server then they don't help you in the event of a server failure (they're on the server too, so they're lost as well). Have them store to a different location (another server or a NAS on the network for example). Even better, if you have access, also then back those up to somewhere off site (this may not be as frequently, but it is still very worth while); doing both means you have easy access on site, but in case the building burns down you still have them elsewhere.
How frequently you do transaction backups is up to you, and all depends on what you see as permissible data loss in the event of a system failure. If you feel that losing UP TO an hour's worth of data is ok, then performing a transaction back up every 30 minute more than covers you. If you can't afford to lose more than 5 minutes, then you'll need to consider doing much more frequent back ups of your transaction log. The key thing is you need to have knowledge of your data, and your business' requirements.
If you need more detailed information then ask on specifics; your question, as it stands, is quite sweeping. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2017 at 3:35 am
Also just to add it may be worthwhile looking to upgrade the version of SQL Server you are using to take advantage of some HA features that have been added since 2008 R2. Would this be an option for you?
Thanks
April 26, 2017 at 4:05 am
Thank you Gentlemen for your valuable suggestions,
However specific to the point,
1) data loss can go up to 1 or 2 hours tolerance,
2) currently, backup daily 1 time at close of business hours, from now will schedule hourly transaction log backup & full backup once in a day.
3) What is best source/service available to upload the transaction log on to cloud every day at a automatic scheduled time like late evenings or mid night.
I see above steps as a 3 layer backup i.e. 1) full backup every day 2) hourly transaction log on external drive 3) day transaction log on cloud, I think should be enough.
Coming mirroring I am not sure because I am mirroring on the same network i.e. within the same premises / office, in case of power surge on network(like happened few weeks ago) then I may lose data on main and mirror system too in case of both systems are down.
Thanks
April 26, 2017 at 4:11 am
satish 61764 - Wednesday, April 26, 2017 4:05 AMI see above steps as a 3 layer backup i.e. 1) full backup every day 2) hourly transaction log on external drive 3) day transaction log on cloud, I think should be enough.
Just a quick nit pick here, you can't do a day transaction log back up if you're performing hourly transaction log back ups. Once you do a transaction log back up, those transactions are backed up. If you do another one, you back up the transactions that occurred between the previous one and that point in time. You can't do a hourly transaction log back up, and then do a full day's transaction log backup at the end of the day.
I assume you really meant that you would be uploading the full back up, but just pointing this out in-case you did have this misunderstanding.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2017 at 4:25 am
OIC, understood I can perform only one either hourly transact log or full day transact log. It can be taken care of but question remains what are best resources to upload day transact logs on Cloud. installing Google drive and attaching server backup folder, does it work ?
April 26, 2017 at 4:57 am
Just to be clear you can perform hourly transaction log backups AND daily full backup backups.
Thanks
April 26, 2017 at 5:09 am
Yes i am completely aware of that, confusion was between hourly transact log and full day transact log in which i can perform only one. however day full DB backup always there.
April 26, 2017 at 10:24 am
I'm not recommending this specifically, but Cloudberry is one solution to move files to Amazon or Azure. They have a database item, but you can just use their product (or some other) to copy files to some remote share.
You don't need the cloud, but you do need some offsite backup. Some people use a service like Iron Mountain that takes physical media, some will copy to a vendor offering backup. What works is up to you, but if security is a concern, be careful and certainly encrypt your files. There are plenty of libraries and programs to help here.
This isn't hard to script, or even manage old files. Just decide what you want. Typically someone will do something like this:
On-premises (daily full, hourly log) Keep 2 days available. This means you'll have at most, 3 full backups (never delete the old one until a new one exists) and 71-72 log files. You'll want to manage those.
In the Cloud - copy up full and log backups. The logs don't do any good without a full, and you can decide how many to keep. I'd likely aim to keep 3-4 days at a minimum, so do the math on files. Make sure you delete old ones, and also be careful about where you store your encryption keys. Some people store them with the backups to ensure they are available, some use a separate, secure file storage location.
I'm not sure why you think mirroring will slow things down. Perhaps a touch, but it's a cost to ensure you have a highly available system. If you need up to the transaction backup, use that. If you can tolerate an hour of data loss, set up log shipping locally instead. This will give you a warm standby and re-use the logs you're already backing up.
April 26, 2017 at 10:31 am
Just know that if you are backing up the transaction logs once an hour, you need to have enough disk space to hold an hours worth of transactions in the log. You may find that you need to backup the log more frequently in order to keep the size of the log smaller.
Also, another security thing frequently overlooked is the use of parameterized queries to ensure you avoid SQL Injection attacks. This requires working with the development teams to ensure they're following best practices on data types and query construction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply