April 9, 2012 at 11:46 am
OK, I have a SQL2008R2 maintenance plan that essentially does nightly full backups of all databases, hourly appended differential backups of user databases, and then 15 minute transaction log backups of user databases. This has worked well for us for the past four years, and we need the 15-minute disaster recovery granularity.
During this time datamining/reporting was done through our application which directly queried the main database(s). Our new product now uses reporting services and now I have the fun task of trying to copy the primary databases over to the Reporting SQL Server/Database.
The Data Mining peeps want to change my nightly backups to 'Copy Only', which I immediately shot down. Their strategy is to do a Full weekly backup with nighly differentials in order to reduce the time to restore backups. We are using compressed backups and instant file initialization. Keep in mind this is a new project and we have no IOPS specs for it.
I want to look into snapshots, but am new to them. Will a snapshot backup interfere with my current Maintenance Plan or does it do it in a 'Copy Only' fashion? If I set up a seperaate Maintenance Plan for datamining with their own full and differential backups, will they interfere with my existing maintenance plan, or do they have their own unique GUID so they won't recognize/interfere? This way I don't have to modify my Maintenance plan and differential schedule, and they can have their own customized one.
Thanks.
April 9, 2012 at 12:06 pm
Since you are doing a nightly full backup, why not just restore that backup for your BI/Datamining people?
April 9, 2012 at 12:19 pm
Lynn Pettis (4/9/2012)
Since you are doing a nightly full backup, why not just restore that backup for your BI/Datamining people?
That's fine for when our database is small, but when it grows large, doing a nightly restore could take awhile. There are mutliple scenarios here as well. We may deploy everything on one server (probably with the datamining database set up on dedicated LUNS), or deploy a seperate server for those deployments where budget allows.
Thanks for your response. I'm not getting much feedback and I'm wondering if it's a stupid question or just to complex to answer.
April 9, 2012 at 12:23 pm
A weekly full backup for datamining and daily differentials would be interfered by your nightly full backups. Each differential backup contains all changes since the LAST full backup.
What ever process you put in place needs to satisfy both requirements, backups for DR and for restoring to a BI/Datamining server.
April 9, 2012 at 12:31 pm
Lynn Pettis (4/9/2012)
A weekly full backup for datamining and daily differentials would be interfered by your nightly full backups. Each differential backup contains all changes since the LAST full backup.What ever process you put in place needs to satisfy both requirements, backups for DR and for restoring to a BI/Datamining server.
That's why I wish I could implement 2 maintenance plans, each only recognizing their own backups, or simply do snapshots, but I don't know if snapshot backups will interfere with the jobs on my existing maintenance plan, or if they can be done independently in a 'copy only' fashion.
April 9, 2012 at 12:37 pm
rbarrera (4/9/2012)
Lynn Pettis (4/9/2012)
A weekly full backup for datamining and daily differentials would be interfered by your nightly full backups. Each differential backup contains all changes since the LAST full backup.What ever process you put in place needs to satisfy both requirements, backups for DR and for restoring to a BI/Datamining server.
That's why I wish I could implement 2 maintenance plans, each only recognizing their own backups, or simply do snapshots, but I don't know if snapshot backups will interfere with the jobs on my existing maintenance plan, or if they can be done independently in a 'copy only' fashion.
That would require that SQL Server be able to maintain multiple backup chains for multiple maintenance plans. In addtion, you will find that many experienced DBA's no longer use Database maintenance plans when supporting large databases.
I'm not sure what you are talking about with regard to snapshot backups (unless you are talking about replication).
You need to develop a plan that can support all your requirements.
April 9, 2012 at 12:47 pm
Snapshot as it applies here: http://nirajrules.wordpress.com/2008/12/08/snapshot-vs-logshipping-vs-mirroring-vs-replication/
April 9, 2012 at 12:48 pm
rbarrera (4/9/2012)
Snapshot as it applies here: http://nirajrules.wordpress.com/2008/12/08/snapshot-vs-logshipping-vs-mirroring-vs-replication/
April 9, 2012 at 12:49 pm
Database snapshot, now I see.
April 9, 2012 at 1:42 pm
A database snapshot won't interfere with your backup chain, but it does have to reside on the same server as the original database. That sounds like it might be a problem in your case. A snapshot can be used in conjunction with a database mirror since the mirror could reside on another server. In any of these cases, no changes can be made to the database by the data mining product/team. Typically you'd flatten out your DW reporting tables and build out dimension and fact tables.
MWise
April 9, 2012 at 2:09 pm
Mhlewis (4/9/2012)
A database snapshot won't interfere with your backup chain, but it does have to reside on the same server as the original database. That sounds like it might be a problem in your case. A snapshot can be used in conjunction with a database mirror since the mirror could reside on another server. In any of these cases, no changes can be made to the database by the data mining product/team. Typically you'd flatten out your DW reporting tables and build out dimension and fact tables.MWise
Thanks MWise. that's what I needed to know. For the budget solution, we could just use a database snapshot, but for more performance, we could use mirroring in conjunction with database snapshots. They'll only need read-only access from what I gather.
This gives the fastest restore\access that I can Google so far.
April 10, 2012 at 6:28 am
Well, you need Enterprise Edition for database snapshots, so now that leaves me with snapshot replication.
April 13, 2012 at 6:03 am
For our datamining, they're accepting being able to only query data up to 1 day old for their reports.
I'm thinking of going with their backup\restore plan and going with a weekly full backup (eliminating my daily's), create a new differential evey day, and then append the transaction log every 15 minutes, with the transaction log expiring daily.
They would restore the full backup and then every night, restore the differential. They would not use our transaction log backups.
April 13, 2012 at 7:41 am
rbarrera (4/13/2012)
For our datamining, they're accepting being able to only query data up to 1 day old for their reports.I'm thinking of going with their backup\restore plan and going with a weekly full backup (eliminating my daily's), create a new differential evey day, and then append the transaction log every 15 minutes, with the transaction log expiring daily.
They would restore the full backup and then every night, restore the differential. They would not use our transaction log backups.
I don't think that will work. To restore a differential, you always have to start with the last full backup and then apply the differential. So every night they would take the weekly full back up and then apply the most recent differential. If you are worried about the time that it takes to restore the full backup every night, then I don't think this solution will gain you any time. Unless the issue is that the total time that it takes to do the night full and then restore is longer than your maintanence window, perhaps you'd gain some time on the back up portion of the process in which the nightly differential backs up in a shorter amount of time than a full nightly.
MWise
April 16, 2012 at 6:07 am
Mhlewis (4/13/2012)
I don't think that will work. To restore a differential, you always have to start with the last full backup and then apply the differential. So every night they would take the weekly full back up and then apply the most recent differential. If you are worried about the time that it takes to restore the full backup every night, then I don't think this solution will gain you any time. Unless the issue is that the total time that it takes to do the night full and then restore is longer than your maintanence window, perhaps you'd gain some time on the back up portion of the process in which the nightly differential backs up in a shorter amount of time than a full nightly.
MWise
After discussing with them, that's exactly the reason..........to save time on backing up, not restoring.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply