| Author |
Topic |
|
MassiveOverkill
Starting Member
20 Posts |
 Posted - 2012-04-09 : 10:39:14
|
| 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. |
|
|
MassiveOverkill
Starting Member
20 Posts |
Posted - 2012-04-09 : 13:10:17
|
| Bueller? |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-04-10 : 05:01:51
|
| Are you using Enterprise Edition? Then Database snapshot is a possibility. Be careful as reverting breaks the log chain.If you set up a separate plan with COPY ONLY , then that shouldn't interfere with your maintenance plan. Although , you may have a problem with maintenance windows interfering with each other.Another option to consider and explore is Filegroup backups - so , for example, if you maintained current data in one filegroup and archived data in another then you could maintain the Archived data and only need to restore the CURRENT dataJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
MassiveOverkill
Starting Member
20 Posts |
Posted - 2012-04-10 : 08:39:44
|
| Thanks Jack, we won't use Enterprise Edition (at least for our low-mid range deployments), so I guess we're going to have to use snapshot replication or something else. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-04-11 : 01:37:01
|
| If you have SAN drives - you could consider SAN snapshots .Very fast. The only problem being managing the snapshot via a SSH. Normally SAN Admins won't allow.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
MassiveOverkill
Starting Member
20 Posts |
Posted - 2012-04-11 : 12:05:22
|
| For our vmware\HA deployments, that would be viable, but for our smaller implementations, there probably won't be a SAN implementation. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-11 : 12:30:45
|
| Cant you just set up log shipping and use it as a read only for reporting ? But for that you will have to disable the Tlog backup's in your maintenance schedule.After Monday and Tuesday even the calendar says W T F .... |
|
|
|
MassiveOverkill
Starting Member
20 Posts |
Posted - 2012-04-11 : 12:41:37
|
| Tlogs are important as we require the granularity for recovery. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2012-04-11 : 12:47:27
|
quote:
Originally posted by MassiveOverkill
Tlogs are important as we require the granularity for recovery.
But log shipping is gonna take care of that.See thishttp://msdn.microsoft.com/en-us/library/ms175480.aspxAfter Monday and Tuesday even the calendar says W T F .... |
|
|
|
MassiveOverkill
Starting Member
20 Posts |
Posted - 2012-04-11 : 13:06:54
|
| Thanks, that helps. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-04-12 : 01:44:01
|
| The one issue I've had in the past with Log Shipping - to create a Read Only copy is : for a Datawarehouse incremental nightly load - the requirement is often from a certain point in time. In other words: the business requirement may be to increment the data from 9am - 7pm . I'd create a snapshot at 7pm Run incremental process at 1am (post backups)In a Log Shipping scenario - you may have transactions after 7pm.Which makes the data inaccurateJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
MassiveOverkill
Starting Member
20 Posts |
Posted - 2012-04-13 : 08:02:03
|
| 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. |
|
|
|
MassiveOverkill
Starting Member
20 Posts |
Posted - 2012-04-18 : 12:14:37
|
| For a single server scenario does it make any sense to install two instances of SQL, one for application databases and one for Reporting Services/Datamining? Doesn't it defeat the purpose. Query the primary databases and upgrade the hardware appropriately. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-04-19 : 01:36:02
|
| Hosting the databases over two instances on one server has benefits regarding isolating the databases . You need to asses whether it's worth the extra overhead of maintaining two separate instances. In your case you're using Standard Edition , which means you can run multiple instances without a licensing overhead. Two instances won't solve your initial problem .Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
MassiveOverkill
Starting Member
20 Posts |
Posted - 2012-04-19 : 08:23:06
|
| The Data Mining scripts re-create the primary databases with the same container names. I don't see why they can't just append a _DM to each of the primary names when they create their datamining copies, that way the same instance can be used. There are too many cons for me having multiple instances on the same server, from patching, to administration, and most-importantly we have many service personnel used to only dealing with 1 instance on a server, and someone making the mistake of restoring a database the wrong instance. From disk I/O perspective, it's not like there's going to be additional spindle sets, so now we have double the number of databases with additional I/O overhead.I really just want to spec the server to use more memory and mandatory SSD cache, in addition to faster SAS drives, and have them query the primary databases in a single server solution. |
|
|
|
|
Backup and Datamining living in harmony