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.
