June 6, 2011 at 6:07 pm
What high availability options are available for those of us that just use the simple recovery model on our databases outside of manually taking a backup and manually restoring the backup to a second server?
June 6, 2011 at 7:26 pm
Triality (6/6/2011)
What high availability options are available for those of us that just use the simple recovery model on our databases outside of manually taking a backup and manually restoring the backup to a second server?
Well, you could build a cluster - that does not require full recovery model. However, that would be a lot more expensive than switching to full recovery model and setting up regular (every 15 minute) transaction log backups.
What are your concerns about switching to full recovery model?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 6, 2011 at 8:13 pm
I think it's just a little more difficult to manage the servers. I am completely new to this whole "DBA" thing but it has become my responsibility in the organization. Trying to keep things as simple as possible to begin with. It's a data warehouse type environment where we load new data a couple of times per month.
I think with clusters you need shared storage correct? The server we are running has 24 internal SAS 600GB drives. I would again rather stay away from shared storage...
June 6, 2011 at 9:26 pm
To answer your question... just clustering.
The other available options:
1. Shipping the transaction log: requires Bulk Logged for Full recovery models.
2. Mirroring: requires Full recovery model.
It's a lot easier to manage database in the FULL recovery model than it is to manage a cluster. A cluster requires hardware and drivers signed for clusters, and as you noted, shared storage (major expense).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 7, 2011 at 7:17 am
There are just scads and scads of articles and books out there on managing log files (I know because I've added to the heap several times). It's not that hard and it's one of the best mechanisms available to you to help ensure your business continuity. My suggestion, ask your business people how much data, in terms of time (1 day, 15 minutes, an hour) they can afford to absolutely lose. Then tell them that the curent set-up only guarantees however often you have the full backups running (daily, weekly?). If there's a mismatch, you should jump on it. That's a RGE (résumé generating event) waiting to happen.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 7, 2011 at 10:40 am
They are right it is much easier to manage your system in full recovery model. If your wanting to keep your hands out as much as possible you can do logshipping or Mirroring. Both will allow sql server to manage everything for you backup to restore to deleting files. You just have to set up some sort of notification for when it fails database mail or whatever works for your circumstance.
June 8, 2011 at 12:24 am
For High Availability you should use
1-Cluster
2-Mirroring
3-Log Shipping
4-Replication
For point in time recovery you should use
1-Full Recovery Model
Databse Model,its depends on the policy of your organization ,how much data loss they can bare
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 8, 2011 at 9:24 am
WayneS (6/6/2011)
To answer your question... just clustering.The other available options:
1. Shipping the transaction log: requires Bulk Logged for Full recovery models.
2. Mirroring: requires Full recovery model.
It's a lot easier to manage database in the FULL recovery model than it is to manage a cluster. A cluster requires hardware and drivers signed for clusters, and as you noted, shared storage (major expense).
i agree!!!
June 9, 2011 at 1:39 am
Triality (6/6/2011)
It's a data warehouse type environment where we load new data a couple of times per month.
If you only have changes to your database that often, then you don't need full recovery. Just schedule a full backup to take place immediately after your batch load.
John
June 9, 2011 at 1:41 pm
Thanks for everyone's input! I am going to go ahead and switch to full recovery model and do some reading on log shipping and mirroring!
June 9, 2011 at 1:47 pm
Make sure you schedule log backups
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
June 10, 2011 at 4:47 am
Triality (6/9/2011)
Thanks for everyone's input! I am going to go ahead and switch to full recovery model and do some reading on log shipping and mirroring!
this links, contents very information:
- http://msdn.microsoft.com/en-us/library/ms187103.aspx for log shipping,
- http://msdn.microsoft.com/en-us/library/ms189852.aspx for mirroring,
- http://msdn.microsoft.com/en-us/library/ms187016.aspx for mirrong with log shipping!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply