Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm working on a very large database (250+ gigs) with well over 225 million records. The database is hard to work with simply from its sheer size. This database will only be used as read-only.

We're looking at getting faster hardware, but either way I'm trying to find the most efficient way to work with the database. This database must be updated nightly from a master database and the downtime must be kept to a minimum. The master database is maintained by a third party.

I'm trying to find the best way to efficiently update the database nightly but I'm not having a lot of luck. I looked into differential and transaction log backups but in order to apply any of those a full database backup must be restored first. In my case, this completely defeats the purpose of the differential backup since it will not save me any [down]time. I might as well have a full backup done on the master database nightly and then simply restore the full backup and that would be faster.

I was hoping to find a solution where I can have a full backup done once, (or maybe once a month), and then from then on simply apply some type of incremental backups (based on the original full backup) that build on each other. This would keep downtime to a minimum, since once the first full backup is done I would only apply the incremental backups nightly. I would simply rebuild the index after every "incremental" backup for speed. I have not been successful in finding any truly workable solution like this.

I have tried doing a full-restore WITH STANDBY on a test database and this way I could query the data and then later still apply transaction log and only a transaction log. This was somewhat of a limited success, since I am not able to do things like adding an index since that is technically writing to the database. However, this is very close to what I am looking for since the data itself will be read-only. Is there any solution meant to work like this? I would rather stay clear of doing it with the STANDBY option since it is not meant to be used this way.

I'm just now diving into and doing a lot of research into database backups and performance, constantly reading MSDN- however it seems this solution is not an option. I thought I would ask as a last resort- surely there are some here managing large databases where it would be impractical to do a restore nightly.

Any suggestions? I'm also open to suggestions/links to pages on performance, since I have never worked with a database quite this size.

I'm afraid replication might be the only answer.

share|improve this question
2  
I suppose this is MS SQL server? –  Peter Eisentraut Jan 25 '10 at 18:14
    
Yes, this is Microsoft SQL Server 2008 –  KTF Jan 25 '10 at 19:14
    
You could try a restore with standby, and then use a secondary database, or a set of views, for any kinds of changes you might want to make. Definitely if you want to be able to both continue to restore partial backups, and modify the database at the same time, you'll get into trouble. –  blueberryfields Feb 1 '10 at 0:11
add comment

1 Answer

Log shipping fulfills our need for keep the main (300 gb) database available with logs shipped to a standby copy on another server. Transaction logs are applied every 15 minutes. Our reporting utilizes the standby copy.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.